SQL'de Kohort Tablosu ve Tutundurma Oranı Hesaplama

Kohort analizi SQL'de neyi hesaplar?

Kohort analizi, kullanıcıları ortak bir başlangıç olayına göre gruplar ve her grubun sonraki dönemlerde nasıl davrandığını yan yana koyar. Başlangıç olayı genellikle ilk satın alma, kayıt ya da uygulamaya ilk giriştir. Tutundurma (retention) ise bu gruptaki kişilerin sonraki dönemlerde yeniden aktif olma oranıdır.

SQL tarafında iş şu parçalara ayrılır: her kullanıcının kohort dönemini bulmak, kullanıcı etkinliklerini dönem düzeyine indirmek ve ikisini birleştirip her kohort için aktif kullanıcı sayısını kohort büyüklüğüne bölmek. Bunun için tek bir sihirli fonksiyon yoktur; birkaç basit sorgu ortak tablo ifadeleriyle (CTE) zincirlenir.

Sonuç, satırlarda kohortların, sütunlarda dönemlerin yer aldığı üçgen bir tablodur. Üçgen olmasının nedeni basittir: yeni kohortun henüz yaşanmamış dönemleri boştur.

Başlamadan önce veriyi hazırlamak

Sorgu yazmadan önce şunları netleştirin. Birincisi kullanıcı kimliğidir: aynı kişi farklı cihazlarda ya da misafir olarak farklı kimliklerle görünüyorsa kohortlar şişer ve tutundurma olduğundan düşük çıkar. Sonra "aktif" tanımı gelir. Sipariş vermek mi, oturum açmak mı, belirli bir özelliği kullanmak mı aktiflik sayılacak? Tanım değişince tablo da değişir.

Genel olarak ihtiyaç duyacağınız veri şudur:

• Kullanıcı kimliği
• Etkinliğin zaman damgası (sipariş tarihi, oturum zamanı)
• Gerekirse etkinlik türü; iptal edilmiş ya da test siparişlerini dışlamak için durum alanı

Zaman damgalarını tek bir saat dilimine çevirin. Gece yarısına yakın etkinlikler saat dilimi farkıyla bir önceki ya da sonraki aya kayarsa kohort sayıları tutarsızlaşır. Dönem için aylık ya da haftalık bir kesit seçin; çok az kullanıcısı olan ürünlerde haftalık tablo seyrek ve gürültülü kalır.

Adım adım örnek sorgu

Örneğin siparişleri tutan bir siparisler tablosu olsun; sütunları kullanici_id ve siparis_tarihi olsun. Aşağıdaki iskelet PostgreSQL söz dizimine yakındır; DATE_TRUNC yerine kullandığınız veritabanının tarih kesme fonksiyonunu koymanız gerekebilir.

WITH ilk_siparis AS ( SELECT kullanici_id, DATE_TRUNC('month', MIN(siparis_tarihi)) AS kohort_ayi FROM siparisler GROUP BY kullanici_id ), aylik_etkinlik AS ( SELECT DISTINCT kullanici_id, DATE_TRUNC('month', siparis_tarihi) AS etkinlik_ayi FROM siparisler ), kohort_boyutu AS ( SELECT kohort_ayi, COUNT(*) AS cohort_size FROM ilk_siparis GROUP BY kohort_ayi ) SELECT f.kohort_ayi, e.etkinlik_ayi, COUNT(DISTINCT e.kullanici_id) AS active_users, CAST(COUNT(DISTINCT e.kullanici_id) AS NUMERIC) / b.cohort_size AS tutundurma_orani FROM ilk_siparis f JOIN aylik_etkinlik e ON e.kullanici_id = f.kullanici_id JOIN kohort_boyutu b ON b.kohort_ayi = f.kohort_ayi GROUP BY f.kohort_ayi, e.etkinlik_ayi, b.cohort_size ORDER BY f.kohort_ayi, e.etkinlik_ayi;

Burada MIN ile ilk siparişi buluyor, DISTINCT ile aynı ayda birden fazla siparişi tek etkinliğe indiriyor ve kohort büyüklüğüne bölerek oranı elde ediyorsunuz. Çıktının ilk dönemi, yani kohortun kendi ayı, kural olarak tamamı olur; bunu doğrulama olarak kullanın. Değilse ilk sipariş mantığınızda hata vardır.

Dönem numarasını eklemek ve tabloyu yatay çevirmek

Takvim ayları yerine "kayıttan sonraki ay" sütunlarını görmek istiyorsanız, etkinlik ayı ile kohort ayı arasındaki farkı hesaplayan bir dönem numarası üretirsiniz. Bu fark için kullanılan fonksiyon veritabanına göre değişir: her veritabanı tarih farkını kendi fonksiyonuyla alır. Kullandığınız sistemin belgesine bakın.

Dönem numarası geldikten sonra yatay tabloya şu yollarla geçilir. Birincisi koşullu toplulaştırmadır: her dönem için COUNT(DISTINCT CASE WHEN donem_no = ... THEN kullanici_id END) sütunu yazılır. Diğeri sorgunun uzun halini bırakıp pivotu Excel ya da bir BI aracında yapmaktır. Sütun sayısı değişkense BI yolu daha sürdürülebilirdir; sorguya yeni dönem eklemek zorunda kalmazsınız.

Isı haritası gibi görselleştirme, sayıların anlamını okurken en büyük kolaylıktır: renk koyulaştıkça tutundurma artar, sütun boyunca kopan bir çizgi çoğunlukla ürün ya da süreçteki bir kırılmaya işaret eder.

Farklı tutundurma tanımları

Her tutundurma aynı şey değildir. Klasik tanımda kullanıcı belirli bir dönemde aktiftir (bounded retention). Sınırsız tanımda ise o döneme ya da sonrasına ait herhangi bir etkinlik yeterlidir (unbounded). Abonelik ürünlerinde bunun yerine abonelik sürekliliği ölçülür; burada etkinlik tablosu yerine abonelik başlangıç ve bitiş tarihleri kullanılır.

Özellikle pencere fonksiyonları, ardışık dönemleri karşılaştırmak için işe yarar. LAG ile bir kullanıcının önceki aktif ayını bulup "yeniden aktifleşen" ve "devam eden" kullanıcıları ayırabilirsiniz. Bu ayrım, yeni müşteri kazanımı ile elde tutma çabasını birbirinden ayrı okumanızı sağlar. Hangi tanımı seçtiğinizi tablonun başlığına yazın; farklı ekipler aynı kelimeyle farklı şeyleri ölçüyor olabilir.

Sık yapılan hatalar

• Kullanıcıyı değil satırı saymak: DISTINCT olmadan sipariş sayısı kullanıcı sayısı yerine geçer ve oranlar olabilecek üst sınırın üzerine çıkabilir.
• Kohort büyüklüğünü etkinlik tablosundan hesaplamak: Hiç geri dönmeyen kullanıcılar sonraki dönemlerde görünmez, bu yüzden payda ilk siparişten gelmelidir.
• Yarım kalmış son dönemi tamamlanmış gibi yorumlamak: Ayın ortasındaki veri düşük görünür; son dönemi ya grafikten çıkarın ya da kesik olduğunu işaretleyin.
• İptal, iade ve test kayıtlarını dışarıda bırakmamak.
• Farklı veritabanlarının tarih fonksiyonlarını birbirine karıştırmak.

Bir kontrol alışkanlığı edinin: kohort büyüklüklerinin toplamı, sistemdeki benzersiz kullanıcı sayısıyla uyuşmalı. Uyuşmuyorsa birleştirme anahtarlarını ya da filtreleri gözden geçirin.

Bu beceriyi Mindset programlarında nerede çalışırsınız

Kohort sorgusu pencere fonksiyonları, CTE zincirleri ve toplulaştırma bilgisini birlikte kullanır. Mindset'in SQL ile İleri Veri Dönüşümü ve Modelleme programında pencere fonksiyonları, ortak tablo ifadeleri ve olgu tablolarında toplulaştırma modülleri bulunur; kohort tablosu bu yapı taşlarının doğal bir uygulamasıdır. SQL ile Veri Sorgulama (İleri) programı ise alt sorgular, ortak tablo ifadeleri ve analitik vaka çalışmalarını işler.

Bu eğitimler ürün, pazarlama ve CRM ekiplerinde raporu elle Excel'e taşıyan analistler için uygundur. Kurumsal ekipler, kohort sorgusunu paylaşılan bir görünüm ya da şablon olarak standartlaştırıp herkesin aynı tutundurma tanımını kullanmasını sağlayabilir.