Kohort ve elde tutma (retention) analizi

Kohort nedir?

Kohort, aynı dönemde başlayan kullanıcı grubudur. En yaygını: ilk sipariş ayına göre kohort. Sonra her kohortun sonraki aylarda yüzde kaçının tekrar sipariş verdiği izlenir.

Üç adım

1. Her müşterinin kohort ayını bul (ilk sipariş ayı).
2. Her siparişin kohort ayından kaç ay sonra olduğunu hesapla (ay_farki).
3. Kohort × ay_farki için aktif müşteri sayısını say, kohort büyüklüğüne böl.

WITH ilk AS (
  SELECT customer_id, MIN(order_date) AS ilk_tarih
  FROM orders GROUP BY customer_id
),
akt AS (
  SELECT o.customer_id,
         strftime('%Y-%m', i.ilk_tarih) AS kohort,
         (CAST(strftime('%Y', o.order_date) AS INTEGER) - CAST(strftime('%Y', i.ilk_tarih) AS INTEGER)) * 12
         + CAST(strftime('%m', o.order_date) AS INTEGER) - CAST(strftime('%m', i.ilk_tarih) AS INTEGER) AS ay_farki
  FROM orders o JOIN ilk i ON i.customer_id = o.customer_id
),
tablo AS (
  SELECT kohort, ay_farki, COUNT(DISTINCT customer_id) AS musteri
  FROM akt GROUP BY kohort, ay_farki
)
SELECT t.kohort,
       MAX(CASE WHEN ay_farki = 0 THEN musteri END) AS boyut,
       ROUND(100.0 * MAX(CASE WHEN ay_farki = 1 THEN musteri END) / MAX(CASE WHEN ay_farki = 0 THEN musteri END), 1) AS ay1,
       ROUND(100.0 * MAX(CASE WHEN ay_farki = 3 THEN musteri END) / MAX(CASE WHEN ay_farki = 0 THEN musteri END), 1) AS ay3,
       ROUND(100.0 * MAX(CASE WHEN ay_farki = 6 THEN musteri END) / MAX(CASE WHEN ay_farki = 0 THEN musteri END), 1) AS ay6
FROM tablo t
WHERE kohort >= '2023-01'
GROUP BY t.kohort
ORDER BY t.kohort;