Sıfır Gecikme
veri bilimi · türkçe
Hakkımda🗺️ Haritam
AnasayfaTüm İçerikler
Ana sayfarehber

SQL ile cohort analizi

2025 · 15 dakika okuma · PostgreSQL / BigQuery uyumlu

Ürünün büyüyor — kayıt sayıları her ay artıyor. Ama aynı kullanıcılar geri geliyor mu? Retention mı artıyor, yoksa sürekli yeni kullanıcı kazanıp eskiyi kaybediyor musun? Bu soruya cevap vermeden büyüme yanıltıcıdır.

Cohort analizi tam bu soruyu yanıtlar. Kullanıcıları belirli bir özelliğe göre (çoğunlukla kayıt tarihi) gruplara ayırır ve o grupların zaman içindeki davranışını takip eder. Ürün kararlarının en kritik girdisi.

Cohort nedir?

Cohort, ortak bir deneyimi aynı zaman diliminde yaşayan kullanıcı grubudur. En yaygın kullanım: kayıt ayı cohortu.

Bu analizi bir matris olarak gördüğünde — satırlar cohortlar, sütunlar aylar, hücreler retention oranları — güçlü bir hikâye ortaya çıkar.

Cohort retention matrisi — örnek veri

CohortBoyutAy 0Ay 1Ay 2Ay 3Ay 4Ay 5Ay 6
Oca 2024412%100%42%31%26%22%20%18
Şub 2024389%100%38%28%23%19%17—
Mar 2024451%100%45%34%28%24——
Nis 2024367%100%40%29%24———
May 2024502%100%48%35————
Haz 2024478%100%44—————
Tem 2024523%100——————
Retention oranı:100%%40+%30–40%20–30%10–20— = henüz veri yok

Yukarıdaki tabloya bak. Ocak cohortunun Ay 1 retention'ı %42 iken Mayıs cohortunda bu oran %48'e çıkmış — ürün 5 ayda daha iyi tutunmaya başlamış. Bu trendi toplu metriklerden okumak imkânsızdır.

Senaryo: e-ticaret şirketi

İki tablo üzerinden gideceğiz. Gerçek dünyada bunlar CRM, analitik veya uygulama veritabanında bulunur.

-- Kullanıcı tablosu
CREATE TABLE kullanicilar (
  id           BIGINT PRIMARY KEY,
  kayit_tarihi DATE NOT NULL
);

-- Sipariş tablosu
CREATE TABLE siparisler (
  id              BIGINT PRIMARY KEY,
  kullanici_id    BIGINT REFERENCES kullanicilar(id),
  siparis_tarihi  DATE NOT NULL,
  tutar           DECIMAL(10,2)
);

Adım 1: Her kullanıcının cohort ayını bul

Cohort ayı = kullanıcının ilk sipariş tarihi (veya kayıt tarihi). İlk sipariş tarihini kullanmak genellikle daha anlamlıdır — kayıt edip hiç alışveriş yapmayan kullanıcıları cohorta dahil etmez.

WITH ilk_sip AS (
  SELECT
    kullanici_id,
    MIN(siparis_tarihi)                        AS ilk_siparis,
    DATE_TRUNC('month', MIN(siparis_tarihi))   AS cohort_ayi
  FROM siparisler
  GROUP BY kullanici_id
)
SELECT * FROM ilk_sip LIMIT 5;

-- Sonuç:
-- kullanici_id | ilk_siparis | cohort_ayi
-- 1001         | 2024-01-05  | 2024-01-01
-- 1002         | 2024-01-12  | 2024-01-01
-- 1003         | 2024-02-03  | 2024-02-01

DATE_TRUNC('month', tarih)tarihi o ayın ilk gününe yuvarlar. 2024-01-15 → 2024-01-01. Bu sayede tüm Ocak kullanıcıları aynı cohort değerini alır. BigQuery'de DATE_TRUNC(tarih, MONTH) sözdizimi kullanılır.

Adım 2: Her sipariş için period numarasını hesapla

Period = siparişin gerçekleştiği ay ile cohort ayı arasındaki fark (ay cinsinden). 0. period = ilk sipariş ayı, 1. period = bir sonraki ay, vb.

WITH ilk_sip AS (
  SELECT
    kullanici_id,
    DATE_TRUNC('month', MIN(siparis_tarihi)) AS cohort_ayi
  FROM siparisler
  GROUP BY kullanici_id
),
siparis_cohort AS (
  SELECT
    s.kullanici_id,
    i.cohort_ayi,
    DATE_TRUNC('month', s.siparis_tarihi)   AS siparis_ayi,
    -- Ay farkı = period numarası
    (
      EXTRACT(YEAR  FROM AGE(DATE_TRUNC('month', s.siparis_tarihi), i.cohort_ayi)) * 12 +
      EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', s.siparis_tarihi), i.cohort_ayi))
    )::INT AS period
  FROM siparisler s
  JOIN ilk_sip i ON s.kullanici_id = i.kullanici_id
)
SELECT * FROM siparis_cohort LIMIT 5;

-- Sonuç:
-- kullanici_id | cohort_ayi | siparis_ayi | period
-- 1001         | 2024-01-01 | 2024-01-01  | 0
-- 1001         | 2024-01-01 | 2024-02-01  | 1
-- 1001         | 2024-01-01 | 2024-04-01  | 3

Adım 3: Cohort boyutunu hesapla

Her cohorttaki benzersiz kullanıcı sayısı — retention oranlarının paydası.

WITH ilk_sip AS (
  SELECT
    kullanici_id,
    DATE_TRUNC('month', MIN(siparis_tarihi)) AS cohort_ayi
  FROM siparisler
  GROUP BY kullanici_id
)
SELECT
  cohort_ayi,
  COUNT(DISTINCT kullanici_id) AS boyut
FROM ilk_sip
GROUP BY cohort_ayi
ORDER BY cohort_ayi;

-- Sonuç:
-- cohort_ayi  | boyut
-- 2024-01-01  | 412
-- 2024-02-01  | 389
-- 2024-03-01  | 451

Adım 4: Retention matrisi

Her cohort × period kombinasyonunda kaç benzersiz kullanıcı sipariş vermiş? Cohort boyutuyla bölerek oran elde ediyoruz.

WITH ilk_sip AS (
  SELECT
    kullanici_id,
    DATE_TRUNC('month', MIN(siparis_tarihi)) AS cohort_ayi
  FROM siparisler
  GROUP BY kullanici_id
),
siparis_periodlar AS (
  SELECT
    s.kullanici_id,
    i.cohort_ayi,
    (
      EXTRACT(YEAR  FROM AGE(DATE_TRUNC('month', s.siparis_tarihi), i.cohort_ayi)) * 12 +
      EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', s.siparis_tarihi), i.cohort_ayi))
    )::INT AS period
  FROM siparisler s
  JOIN ilk_sip i ON s.kullanici_id = i.kullanici_id
),
cohort_boyutlari AS (
  SELECT cohort_ayi, COUNT(DISTINCT kullanici_id) AS boyut
  FROM ilk_sip
  GROUP BY cohort_ayi
),
periyodik_aktif AS (
  SELECT cohort_ayi, period, COUNT(DISTINCT kullanici_id) AS aktif
  FROM siparis_periodlar
  GROUP BY cohort_ayi, period
)
SELECT
  pa.cohort_ayi,
  cb.boyut,
  pa.period,
  pa.aktif,
  ROUND(pa.aktif * 100.0 / cb.boyut, 1) AS retention_yuzde
FROM periyodik_aktif pa
JOIN cohort_boyutlari cb ON pa.cohort_ayi = cb.cohort_ayi
ORDER BY pa.cohort_ayi, pa.period;

-- Sonuç:
-- cohort_ayi  | boyut | period | aktif | retention_yuzde
-- 2024-01-01  | 412   | 0      | 412   | 100.0
-- 2024-01-01  | 412   | 1      | 173   | 42.0
-- 2024-01-01  | 412   | 2      | 128   | 31.1

Adım 5: Pivot — matris görünümü

Uzun formattaki veriyi satırlar = cohort, sütunlar = period olan matrise çevirmek. Analiz araçlarında (Metabase, Looker) pivot table ile yapılır; SQL'de CASE WHEN ile elle yazılır.

-- Önceki CTE'ler aynı, son SELECT değişiyor:
SELECT
  cohort_ayi,
  boyut,
  MAX(CASE WHEN period = 0 THEN retention_yuzde END) AS ay_0,
  MAX(CASE WHEN period = 1 THEN retention_yuzde END) AS ay_1,
  MAX(CASE WHEN period = 2 THEN retention_yuzde END) AS ay_2,
  MAX(CASE WHEN period = 3 THEN retention_yuzde END) AS ay_3,
  MAX(CASE WHEN period = 4 THEN retention_yuzde END) AS ay_4,
  MAX(CASE WHEN period = 5 THEN retention_yuzde END) AS ay_5,
  MAX(CASE WHEN period = 6 THEN retention_yuzde END) AS ay_6
FROM retention_sonuc   -- önceki CTE adı
GROUP BY cohort_ayi, boyut
ORDER BY cohort_ayi;

BigQuery'de PIVOToperatörü bu işi otomatik yapar. Snowflake'de de benzer sözdizimi var. Ama CASE WHEN her motorda çalışır.

Bonus: Revenue cohort

Kullanıcı sayısı yerine gelir üzerinden cohort — hangi cohort daha değerli müşteri getirmiş?

WITH ilk_sip AS (
  SELECT
    kullanici_id,
    DATE_TRUNC('month', MIN(siparis_tarihi)) AS cohort_ayi
  FROM siparisler
  GROUP BY kullanici_id
)
SELECT
  i.cohort_ayi,
  (
    EXTRACT(YEAR  FROM AGE(DATE_TRUNC('month', s.siparis_tarihi), i.cohort_ayi)) * 12 +
    EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', s.siparis_tarihi), i.cohort_ayi))
  )::INT                                           AS period,
  COUNT(DISTINCT s.kullanici_id)                   AS aktif_kullanici,
  ROUND(SUM(s.tutar), 0)                           AS toplam_gelir,
  ROUND(SUM(s.tutar) / COUNT(DISTINCT s.kullanici_id), 2) AS kullanici_basi_gelir
FROM siparisler s
JOIN ilk_sip i ON s.kullanici_id = i.kullanici_id
GROUP BY i.cohort_ayi, period
ORDER BY i.cohort_ayi, period;

Sonuçları nasıl yorumlarsın?

Pratik notlar

Cohort analizi veri analistinin temel araçlarından biri. SQL'e hâkimsen aynı mantığı Pandas'ta da uygulayabilirsin — groupby + transform(min) kombinasyonu aynı sonucu verir.

Bu içerik:

Faydalı bulduysan paylaş

X'te paylaşLinkedIn'de paylaş

💬 Yorumlar