Konuyu Açan
#1
SQL Retention Analizi İçin Sorgular
Retention analizi, kullanıcıların belirli bir zaman diliminde uygulamaya geri dönüş oranlarını ölçmek için kullanılan temel metriklerden biridir. Bu analiz, özellikle SaaS, mobil uygulamalar ve e-ticaret platformlarında kullanıcı bağlılığını ve ürün performansını anlamak adına kritik öneme sahiptir. SQL kullanarak retention oranlarını hesaplamak, genellikle kullanıcıların ilk etkinlik tarihleriyle takip edilmeleri ve zaman bazlı geri dönüşlerin detaylandırılmasıyla gerçekleştirilir.
İlk adımda, kullanıcıların ilk etkinlik tarihlerini belirlemek gerekir. Bu, genellikle "cohort" kavramını temel alır: kullanıcının ilk kez kaydolduğu veya etkinlik gösterdiği tarih. Ardından, zaman dilimine göre (gün, hafta, ay) geri dönüşleri inceleyen sorgular geliştirilir.
Örnek olarak, kullanıcıların kayıt tarihleriyle ilk etkinlik tarihlerini eşleştirdiğimiz ve ardından 7 gün, 14 gün ve 30 gün sonrası tekrar giriş yapan kullanıcı oranlarını hesapladığımız bir sorgu seti düşünelim:
Bu sorgu, kullanıcıların ilk etkinliklerinden itibaren geçen gün sayısına göre geri dönüş oranlarını gösterir. Daha detaylı analizler için, hafta veya ay bazında da gruplamalar yapabilir, dönüşüm oranlarını farklı segmentlerde karşılaştırabilirsiniz.
Gelişmiş retention analizleri, segmentasyon ve zaman dilimi ayarlamalarıyla daha derin hale getirilebilir. Bu temel sorgu yapısı, kullanıcı davranışlarını anlamada ve stratejik kararlar almada oldukça faydalıdır. Kendi veritabanınızın yapısına uygun olarak bu sorguları adapte etmek, analizlerin doğruluğu ve kullanılabilirliği açısından kritik önemdedir.
Retention analizi, kullanıcıların belirli bir zaman diliminde uygulamaya geri dönüş oranlarını ölçmek için kullanılan temel metriklerden biridir. Bu analiz, özellikle SaaS, mobil uygulamalar ve e-ticaret platformlarında kullanıcı bağlılığını ve ürün performansını anlamak adına kritik öneme sahiptir. SQL kullanarak retention oranlarını hesaplamak, genellikle kullanıcıların ilk etkinlik tarihleriyle takip edilmeleri ve zaman bazlı geri dönüşlerin detaylandırılmasıyla gerçekleştirilir.
İlk adımda, kullanıcıların ilk etkinlik tarihlerini belirlemek gerekir. Bu, genellikle "cohort" kavramını temel alır: kullanıcının ilk kez kaydolduğu veya etkinlik gösterdiği tarih. Ardından, zaman dilimine göre (gün, hafta, ay) geri dönüşleri inceleyen sorgular geliştirilir.
Örnek olarak, kullanıcıların kayıt tarihleriyle ilk etkinlik tarihlerini eşleştirdiğimiz ve ardından 7 gün, 14 gün ve 30 gün sonrası tekrar giriş yapan kullanıcı oranlarını hesapladığımız bir sorgu seti düşünelim:
KOD
12345678910111213141516171819202122232425262728293031
-- Kullanıcıların ilk kayıt tarihleri ve cohort oluşturma
WITH cohorts AS (
SELECT
user_id,
MIN(activity_date) AS first_activity_date,
DATE_TRUNC('week', MIN(activity_date)) AS cohort_week
FROM user_activity
GROUP BY user_id
),
-- Gün bazında geri dönüşleri hesaplama
retention AS (
SELECT
c.user_id,
c.cohort_week,
DATE_DIFF('day', c.first_activity_date, a.activity_date) AS days_since_first,
COUNT(DISTINCT a.activity_date) AS activity_count
FROM cohorts c
JOIN user_activity a ON c.user_id = a.user_id
WHERE a.activity_date >= c.first_activity_date
GROUP BY c.user_id, c.cohort_week, days_since_first
)
-- Gün bazında retention oranları
SELECT
cohort_week,
days_since_first,
COUNT(DISTINCT user_id) AS retained_users,
(COUNT(DISTINCT user_id) * 100.0) / (SELECT COUNT(*) FROM cohorts WHERE cohort_week = c.cohort_week) AS retention_rate
FROM retention c
GROUP BY cohort_week, days_since_first
ORDER BY cohort_week, days_since_first;
Bu sorgu, kullanıcıların ilk etkinliklerinden itibaren geçen gün sayısına göre geri dönüş oranlarını gösterir. Daha detaylı analizler için, hafta veya ay bazında da gruplamalar yapabilir, dönüşüm oranlarını farklı segmentlerde karşılaştırabilirsiniz.
Gelişmiş retention analizleri, segmentasyon ve zaman dilimi ayarlamalarıyla daha derin hale getirilebilir. Bu temel sorgu yapısı, kullanıcı davranışlarını anlamada ve stratejik kararlar almada oldukça faydalıdır. Kendi veritabanınızın yapısına uygun olarak bu sorguları adapte etmek, analizlerin doğruluğu ve kullanılabilirliği açısından kritik önemdedir.
