Dönüşüm hunisi (funnel) analizi

Ham olay kullanıcı değildir

web_events adımları: visit → product_view → add_to_cart → checkout → purchase. Kullanıcı siteye yeniden gelir, aynı sayfaya tekrar bakar, sepete ikinci ürünü ekler. Üstelik izleme kodu bazı olayları iki kez gönderir. Bu yüzden ham sayım huniyi şişirir: 1.005 visit satırı 600 kullanıcıdan, 75 purchase satırı 67 kullanıcıdan gelir.

SELECT event_type, COUNT(*) AS olay, COUNT(DISTINCT user_id) AS kullanici
FROM web_events
GROUP BY event_type;

Çift gönderimi bulmak için aynı kullanıcı, tür, zaman ve cihazda birden fazla satır ara. Bu veride tam 40 fazla satır var, 8'i purchase. Satın alma adedi raporlarken bunları ayıkla:

SELECT SUM(adet - 1) AS fazla_satir
FROM (SELECT user_id, event_type, event_time, device, COUNT(*) AS adet
      FROM web_events
      GROUP BY user_id, event_type, event_time, device
      HAVING COUNT(*) > 1) t;

Huni adımları

Her adıma ulaşan farklı kullanıcı sayısını say:

SELECT
  COUNT(DISTINCT CASE WHEN event_type = 'visit' THEN user_id END) AS ziyaret,
  COUNT(DISTINCT CASE WHEN event_type = 'product_view' THEN user_id END) AS urun_gor,
  COUNT(DISTINCT CASE WHEN event_type = 'add_to_cart' THEN user_id END) AS sepet,
  COUNT(DISTINCT CASE WHEN event_type = 'checkout' THEN user_id END) AS odeme,
  COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) AS satin_alma
FROM web_events;

Adım dönüşüm oranları (uzun format)

WITH adim AS (
  SELECT event_type,
         CASE event_type WHEN 'visit' THEN 1 WHEN 'product_view' THEN 2 WHEN 'add_to_cart' THEN 3
                         WHEN 'checkout' THEN 4 WHEN 'purchase' THEN 5 END AS sira,
         COUNT(DISTINCT user_id) AS kullanici
  FROM web_events GROUP BY event_type
)
SELECT sira, event_type, kullanici,
       ROUND(100.0 * kullanici / LAG(kullanici) OVER (ORDER BY sira), 1) AS onceki_adima_gore,
       ROUND(100.0 * kullanici / FIRST_VALUE(kullanici) OVER (ORDER BY sira), 1) AS basa_gore
FROM adim ORDER BY sira;