SQL'de Dönüşüm Hunisi Sorgusu: Adımları Sayma ve Kayıp Noktasını Bulma

Huni analizi SQL'de ne üretir?

Huni analizi, bir hedefe giden ardışık adımlarda kullanıcıların kaçının bir sonraki adıma geçtiğini gösterir. Alışveriş sitesinde bu zincir ürün görüntüleme, sepete ekleme, ödeme başlatma ve sipariş tamamlama olabilir. Her adımda kalan kişi sayısı ve bir önceki adıma göre oran, kaybın nerede yoğunlaştığını gösterir.

SQL sorgusunun çıktısı genellikle kısa bir tablodur: adım adı, o adıma ulaşan benzersiz kullanıcı sayısı, bir önceki adıma oranı ve ilk adıma oranı. Sorguyu yazmadan önce karar verilmesi gereken şey, sayma biriminin kullanıcı mı, oturum mu, sipariş mi olduğudur. Aynı kişi aynı gün birden fazla oturum açabilir; birimi netleştirmezseniz oranlar tutarsız çıkar.

Veri modeli: tek etkinlik tablosu

En kolay başlangıç, her satırın bir etkinlik olduğu bir tablodur. Genelde kullanici_id, etkinlik_adi ve etkinlik_zamani sütunları bulunur. Ürün analitiği araçlarından aktarılan ham veri çoğunlukla bu biçimdedir.

Başlamadan önce şunları kontrol edin:

• Etkinlik adları tutarlı mı, aynı adım farklı adlarla yazılmış mı?
• Test ve dahili kullanıcılar ayıklanmış mı?
• Zaman damgaları aynı saat diliminde mi?
• Tekrarlanan etkinlikler (sayfa yenileme, art arda tıklama) ayıklanacak mı?

Huni için dönem aralığını da baştan belirleyin. Bir kullanıcı sepete bugün ekleyip siparişi haftalar sonra verebilir; bir dönüşüm penceresi (örneğin aynı oturum ya da aynı gün) tanımlamazsanız farklı dönemlerin kullanıcıları birbirine karışır.

Sıralamayı dikkate almayan basit huni

Adımların sırasına bakmadan "kim hangi adımı en az bir kez yaptı" sayan yöntem en basitidir. Her adım için benzersiz kullanıcı sayılır:

SELECT COUNT(DISTINCT CASE WHEN etkinlik_adi = 'product_view' THEN kullanici_id END) AS goruntuleme, COUNT(DISTINCT CASE WHEN etkinlik_adi = 'sepete_ekleme' THEN kullanici_id END) AS sepet, COUNT(DISTINCT CASE WHEN etkinlik_adi = 'odeme_baslatma' THEN kullanici_id END) AS odeme, COUNT(DISTINCT CASE WHEN etkinlik_adi = 'siparis_tamam' THEN kullanici_id END) AS siparis FROM etkinlikler WHERE etkinlik_zamani >= :baslangic AND etkinlik_zamani < :bitis;

Bu sorgu hızlıdır ve genel resmi verir, ancak bir kusuru vardır: sipariş veren ama görüntüleme etkinliği kayıtlı olmayan kullanıcı da siparişe sayılır. Veri eksiği ya da sonradan başka kanaldan gelen kullanıcılar yüzünden alt adımlar üst adımdan büyük çıkabilir. Katı bir huni istiyorsanız sıralamayı doğrulamanız gerekir.

Sıralamayı doğrulayan sıkı huni

Her kullanıcı için her adımın ilk gerçekleşme zamanını bulup bunların sırayla artıp artmadığını denetlersiniz. Önce adım başına ilk zaman çıkarılır, sonra yalnızca sıraya uyan kullanıcılar sayılır:

WITH adimlar AS ( SELECT kullanici_id, MIN(CASE WHEN etkinlik_adi = 'product_view' THEN etkinlik_zamani END) AS t_goruntuleme, MIN(CASE WHEN etkinlik_adi = 'sepete_ekleme' THEN etkinlik_zamani END) AS t_sepet, MIN(CASE WHEN etkinlik_adi = 'odeme_baslatma' THEN etkinlik_zamani END) AS t_odeme, MIN(CASE WHEN etkinlik_adi = 'siparis_tamam' THEN etkinlik_zamani END) AS t_siparis FROM etkinlikler WHERE etkinlik_zamani >= :baslangic AND etkinlik_zamani < :bitis GROUP BY kullanici_id ) SELECT COUNT(t_goruntuleme) AS goruntuleme, COUNT(CASE WHEN t_sepet >= t_goruntuleme THEN kullanici_id END) AS sepet, COUNT(CASE WHEN t_sepet >= t_goruntuleme AND t_odeme >= t_sepet THEN kullanici_id END) AS odeme, COUNT(CASE WHEN t_sepet >= t_goruntuleme AND t_odeme >= t_sepet AND t_siparis >= t_odeme THEN kullanici_id END) AS siparis FROM adimlar;

NULL karşılaştırmaları sonucu bilinmeyen yaptığı için etkinliği olmayan adımlar CASE koşulunu geçemez ve sayılmaz. Bu davranış istediğiniz şeydir ama kontrol etmeden varsaymayın; küçük bir örnek veriyle deneyin.

Adımlar arası dönüşüm oranı ve kayıp noktası

Sayıları aldıktan sonra şu oranlar hesaplanır: bir önceki adıma göre oran (adım dönüşümü) ve ilk adıma göre oran (toplam dönüşüm). Sorgunun üzerine sarılan bir dış SELECT ile bölme işlemleri yapılır. Bölenin sıfır olabileceğini unutmayın; NULLIF ile böleni korumak yaygın bir alışkanlıktır.

Sonucu okurken en büyük düşüşün olduğu adıma bakmak doğal görünür, ancak yanıltıcı olabilir. Ödeme sayfasındaki düşüş ürün fiyatından, kargo ücretinden ya da formun uzunluğundan kaynaklanabilir; SQL size nerede kaybettiğinizi söyler, nedenini söylemez. Nedeni bulmak için aynı hunileri kırılımlarla çalıştırın: cihaz, trafik kaynağı, yeni ya da tekrar eden kullanıcı. Bir kırılımda düşüş diğerlerinden belirgin biçimde büyükse sorun orada aranır.

Dönemler arasında karşılaştırma yaparken kohort mantığı da işe yarar: kampanya sonrası gelen kullanıcıların huni oranı, öncekilerden farklı mı?

Pencere fonksiyonlarıyla sonraki adım yaklaşımı

Etkinlik sayısı çok büyükse ya da sıkı bir yol analizi gerekiyorsa LEAD ve LAG pencere fonksiyonları kullanılır. Her kullanıcının etkinlikleri zamana göre sıralanır ve bir etkinlikten sonra gelen etkinlik bulunur. Sonra "sepete ekleme sonrası hangi etkinlik geldi" sorusu bir GROUP BY ile cevaplanır. Bu yaklaşım kullanıcıların huni dışına nereye saptığını da gösterir.

Dikkat edilecek nokta, ardışık etkinlikleri sıkı biçimde aramanın gerçek davranışı yansıtmamasıdır: arada başka bir sayfa görüntülemek huniyi bozmamalıdır. Bu yüzden çoğu ekip "bu adımdan önce ilgili adım gerçekleşti mi" mantığını tercih eder; yukarıdaki sıkı huni bunu yapar.

Sık hatalar ve kontrol listesi

• Etkinlik sayısını kullanıcı sayısı sanmak: DISTINCT unutulursa oranlar şişer.
• Dönüşüm penceresi tanımlamamak ve farklı dönemlerin etkinliklerini karıştırmak.
• Kimlik birleştirmeyi atlamak: kayıt öncesi ve sonrası aynı kişi farklı kimliklerle huniden birden fazla kez geçebilir.
• Alt adımın üst adımdan büyük çıkmasını normal saymak; çoğu zaman bu veri hatası ya da etkinlik eksikliğidir.
• Yalnızca genel huniye bakmak; kırılım olmadan sorunun yeri belirlenemez.

Kontrol listesi olarak şunu kullanın: her adımın sayısı bir üstüne eşit ya da küçük mü, test kullanıcıları ayıklandı mı, pencere aralığı raporda yazılı mı, bir kullanıcı elle izlenip sonuçla örtüşüyor mu?

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

Huni sorguları koşullu toplulaştırma, ortak tablo ifadeleri ve pencere fonksiyonlarının birleşimidir. Mindset'in SQL ile İleri Veri Dönüşümü ve Modelleme programı pencere fonksiyonlarını ve toplulaştırmayı ayrı modüllerde işler; SQL ile Veri Sorgulama (İleri) programı ise ortak tablo ifadeleri, alt sorgular ve analitik vaka çalışmalarını kapsar.

Pazarlama, e-ticaret ve ürün ekiplerinde rapor hazırlamak için sürekli veri ekibine bağımlı kalan analistler bu içeriklerden yararlanır. Kurumsal tarafta, huni sorgusu ortak bir şablon olarak yazıldığında farklı ekiplerin aynı dönüşüm tanımını kullanması mümkün olur.