Bir mağaza sahibi analiste dört soru soruyor: “Bir sipariş ortalama ne kadar tutuyor? Müşteriler geri geliyor mu? Onları hangi aşamada kaybediyoruz? Gelirin çoğunu hangi ürünler getiriyor?” Cevaplar zaten veritabanında; yalnızca doğru sorguyla çıkarılmaları gerekiyor. Bu derste kursta öğrendiğin her şeyi (JOIN, GROUP BY, CTE, CASE, pencere fonksiyonları) gerçek analitik görevlerde bir araya getireceğiz.
Temel metrik: ortalama sipariş değeri
Burada
- AOV
- ortalama sipariş değeri (average order value), manat
- R
- dönem geliri: Σ miktar · fiyat, manat
- Nₒ
- aynı dönemdeki sipariş sayısı
SELECT COUNT(*) AS orders,
ROUND(SUM(o.quantity * p.price), 2) AS revenue,
ROUND(SUM(o.quantity * p.price) / COUNT(*), 2) AS aov,
COUNT(DISTINCT o.customer_id) AS buyers
FROM orders AS o
JOIN products AS p ON p.id = o.product_id;Beklenen çıktı
orders | revenue | aov | buyers 12 | 3942.06 | 328.5 | 7
Kohortlar ve elde tutma
Kohort, aynı dönemde aynı olayı yaşamış kullanıcılar grubudur; örneğin ilk siparişini Şubat 2025'te verenler. Müşterileri hep birlikte değil kohortlara göre izlemek, yeni müşterilerin davranışını eskilerinkinden ayırır. En önemli gösterge elde tutmadır (retention): kohortun sonraki dönemlerde yeniden aktif olan payı.
Burada
- r
- kohortun elde tutma oranı
- C₀
- kohort büyüklüğü — ilk dönemde gelen müşteriler
- A
- sonraki dönemlerde yeniden sipariş veren müşteriler
WITH firsts AS (
SELECT customer_id, SUBSTR(MIN(order_date), 1, 7) AS cohort
FROM orders
GROUP BY customer_id
),
flags AS (
SELECT f.customer_id, f.cohort,
EXISTS (SELECT 1 FROM orders AS o
WHERE o.customer_id = f.customer_id
AND SUBSTR(o.order_date, 1, 7) > f.cohort) AS returned
FROM firsts AS f
)
SELECT cohort,
COUNT(*) AS customers,
SUM(returned) AS returned,
ROUND(100.0 * AVG(returned), 1) AS retention_pct
FROM flags
GROUP BY cohort
ORDER BY cohort;Beklenen çıktı
cohort | customers | returned | retention_pct 2025-01 | 1 | 1 | 100 2025-02 | 2 | 2 | 100 2025-03 | 2 | 0 | 0 2025-04 | 1 | 0 | 0 2025-06 | 1 | 0 | 0
EXISTS 1 ya da 0 döndürür; bu yüzden SUM geri gelenleri sayar, AVG ise onların payını verir.Örnek 1: kohort tablosunu yorumlamak
Problem
Yukarıdaki sonuca dayanarak genel elde tutma oranını hesapla. “Mart kohortu kötü, ocak ve şubat ise mükemmel” sonucu ne kadar sağlamdır?
Çözümü gösterÇözümü gizle
Çözüm
2) r = 3 ÷ 7 · 100% ≈ 42,9%.
3) Kohortlarda 1–2 kişi var: tek bir müşteri oranı 0'dan 100'e değiştirir; istatistiksel bir sonuç çıkarılamaz.
4) Sonraki kohortların geri gelmek için daha az zamanı oldu (haziran kohortunu yalnızca temmuz izliyor); bu “sansürlü” veridir. Adil bir karşılaştırma için her kohortta aynı pencereye (örneğin ilk siparişten sonraki 60 gün) bakmak gerekir.
Huni ve dönüşüm
Bir huni, müşterinin geçtiği ardışık aşamaları gösterir; her aşamada insanların bir kısmı “düşer”. Alıştırma veritabanında olay günlüğü olmadığı için bir sadakat hunisi kuruyoruz: kayıt → en az 2 ürün → en az 2 sipariş → 900 manattan fazla harcama. FIRST_VALUE başlangıca, LAG ise önceki aşamaya oranı hesaplar.
Burada
- cᵢ
- i. aşamaya kadarki dönüşüm
- nᵢ
- i. aşamaya ulaşan müşteriler
- n₁
- hunideki ilk aşama
Adım dönüşümü ise nᵢ ÷ nᵢ₋₁ · 100%'dür; “en çok nerede kaybediyoruz?” sorusunu cevaplar.
WITH per_customer AS (
SELECT c.id,
COUNT(o.id) AS orders,
COALESCE(SUM(o.quantity), 0) AS items,
COALESCE(SUM(o.quantity * p.price), 0) AS spent
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
LEFT JOIN products AS p ON p.id = o.product_id
GROUP BY c.id
),
funnel AS (
SELECT 1 AS step, 'registered' AS stage, COUNT(*) AS customers FROM per_customer
UNION ALL SELECT 2, '2+ items', COUNT(*) FROM per_customer WHERE items >= 2
UNION ALL SELECT 3, '2+ orders', COUNT(*) FROM per_customer WHERE orders >= 2
UNION ALL SELECT 4, 'spent > 900', COUNT(*) FROM per_customer WHERE spent > 900
)
SELECT step, stage, customers,
ROUND(100.0 * customers / FIRST_VALUE(customers) OVER (ORDER BY step), 1) AS pct_of_start,
ROUND(100.0 * customers / LAG(customers) OVER (ORDER BY step), 1) AS pct_of_prev
FROM funnel
ORDER BY step;Beklenen çıktı
step | stage | customers | pct_of_start | pct_of_prev 1 | registered | 7 | 100 | NULL 2 | 2+ items | 6 | 85.7 | 85.7 3 | 2+ orders | 4 | 57.1 | 66.7 4 | spent > 900 | 3 | 42.9 | 75
Medyan ve yüzdelikler
Ortalama uç değerlere duyarlıdır: 1450 manatlık tek bir dizüstü bilgisayar ortalama fiyatı epey yukarı çeker. Medyan, sıralı bir listenin ortasıdır ve uç değerlerden pek etkilenmez. PostgreSQL'de bunun için hazır percentile_cont fonksiyonu vardır; SQLite'ta ise medyan ve yüzdelikleri pencere fonksiyonlarıyla hesaplarız. Bu yüzden gelir, fiyat ve süre gibi çarpık dağılımlarda rapora medyanı da ekle.
Burada
- k
- yüzdeliğin sıralı listedeki konumu (1'den başlayarak)
- p
- yüzdelik, örneğin 90
- n
- değer sayısı
En yakın sıra yöntemi: p. yüzdelik, değerlerin en az %p'sini kapsayan en küçük değerdir.
WITH ordered AS (
SELECT score,
ROW_NUMBER() OVER (ORDER BY score) AS rn,
COUNT(*) OVER () AS n
FROM enrollments
)
SELECT AVG(score) AS median_score,
(SELECT AVG(score) FROM enrollments) AS mean_score
FROM ordered
WHERE rn IN ((n + 1) / 2, (n + 2) / 2);Beklenen çıktı
median_score | mean_score 86.5 | 82.8
Örnek 2: 90. yüzdelik
Problem
20 puanın 90. yüzdeliğini en yakın sıra yöntemiyle bul. Sıralı puanlar: 58, 64, 68, 70, 72, 75, 77, 79, 81, 85, 88, 88, 90, 91, 92, 93, 94, 95, 97, 99.
Çözümü gösterÇözümü gizle
Çözüm
2) 18. değer: 95.
3) Denetim: 95'ten küçük ya da ona eşit 18 değer vardır; bu da 20'nin %90'ıdır.
4) SQL'de:
rn = (9 * n + 9) / 10 = (180 + 9) / 10 = 18 (tam sayı bölmesi). PostgreSQL'in percentile_disc(0.9) fonksiyonu da 95 döndürür.-- PostgreSQL: built-in ordered-set aggregates
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY score) AS median,
percentile_disc(0.9) WITHIN GROUP (ORDER BY score) AS p90
FROM enrollments;Beklenen çıktı
median | p90 --------+----- 86.5 | 95 (1 row)
percentile_cont komşu değerler arasında ara değer hesaplar, percentile_disc ise var olan değerlerden birini döndürür.Mini proje: ürünlerin ABC analizi
Pareto ilkesine göre gelirin büyük kısmını genellikle ürünlerin küçük bir kısmı getirir. ABC analizi bunu ölçer: ürünleri gelire göre azalan sırada dizer, kümülatif payı hesaplar ve bir sınıf veririz: önceki ürünlerin payı henüz %80'e ulaşmadıysa A, %95'e ulaşmadıysa B, geri kalanlar C. Adımlar: 1) bir CTE'de her ürünün geliri; 2) pencerelerle kümülatif toplam ve genel toplam; 3) CASE ile sınıf.
WITH revenue AS (
SELECT p.name, ROUND(SUM(o.quantity * p.price), 2) AS revenue
FROM orders AS o
JOIN products AS p ON p.id = o.product_id
GROUP BY p.id, p.name
),
running AS (
SELECT name, revenue,
SUM(revenue) OVER (ORDER BY revenue DESC, name
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum,
SUM(revenue) OVER () AS total
FROM revenue
)
SELECT name, revenue,
ROUND(100.0 * cum / total, 1) AS cum_pct,
CASE WHEN (cum - revenue) < 0.80 * total THEN 'A'
WHEN (cum - revenue) < 0.95 * total THEN 'B'
ELSE 'C' END AS abc_class
FROM running
ORDER BY revenue DESC, name;Beklenen çıktı
name | revenue | cum_pct | abc_class Smartphone | 1799.98 | 45.7 | A Laptop | 1450 | 82.4 | A Headphones | 361.5 | 91.6 | B Notebook | 96 | 94 | B Desk lamp | 69.98 | 95.8 | B Backpack | 55 | 97.2 | C Chess set | 42 | 98.3 | C Water bottle | 36 | 99.2 | C Pen set | 31.6 | 100 | C
Sonuç: satılan 9 üründen ikisi (%22) gelirin %82'sini getiriyor; klasik bir Pareto tablosu. A sınıfı için stok ve tedarik özel olarak izlenmeli, C sınıfı için ise ürün yelpazesini sadeleştirmek ya da birlikte satış kampanyaları düşünülebilir. Unutma: 12 sipariş üzerinde bu yalnızca yöntemin bir gösterimidir; gerçek bir karar için aylar boyunca toplanmış veri gerekir.
Alıştırma
Her ülke için sipariş sayısını (orders), geliri (revenue, 2 ondalık basamak) ve ortalama sipariş değerini (aov = gelir ÷ sipariş, 2 ondalık basamak) hesapla. Sonucu gelire göre azalan sırada sırala.
SELECT c.country
-- orders, revenue, aov
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN products AS p ON p.id = o.product_id
GROUP BY c.country
ORDER BY c.country;Beklenen çıktı
country | orders | revenue | aov Azerbaijan | 7 | 2843.49 | 406.21 Türkiye | 3 | 973.59 | 324.53 United Kingdom | 1 | 69.98 | 69.98 Russia | 1 | 55 | 55
Alıştırma
Pencere fonksiyonlarıyla ürün fiyatlarının medyanını (median_price) bul ve yanında 2 ondalık basamağa yuvarlanmış ortalama fiyatı (avg_price) göster. Medyan neden ortalamadan bu kadar küçük?
WITH ordered AS (
SELECT price
-- row number and total count
FROM products
)
SELECT AVG(price) AS median_price
FROM ordered
-- keep only the middle row(s)
;Beklenen çıktı
median_price | avg_price 48.5 | 293.56
Unutma
- Metrikler formüllerle başlar: ortalama sipariş değeri = gelir ÷ sipariş, elde tutma = geri gelen ÷ kohort, dönüşüm = nᵢ ÷ n₁.
- Kohort ilk olayın dönemine göre kurulur (
MIN(order_date)), geri gelenler iseEXISTSya da koşullu toplama ile sayılır. - Huni
UNION ALLile aşamalardan kurulur, dönüşüm iseFIRST_VALUEveLAGile hesaplanır. - Medyan uç değerlere dayanıklıdır; SQLite'ta
ROW_NUMBERveCOUNT(*) OVER ()ile, PostgreSQL'depercentile_contile hesaplanır. - Bir yüzdenin yanında her zaman mutlak sayıyı göster ve kohortları aynı gözlem penceresiyle karşılaştır.