SQL ile veri analizi

İş metriklerini SQL ile hesapla: ortalama sipariş değeri, kohortlar ve elde tutma, huni dönüşümü, medyan ve yüzdelikler; sonunda da alıştırma verileri üzerinde bir ABC analizi projesi.

  • Üniversite
  • 30 dk
  • 10 soru
  • Ders 27 / 28

Bu derste öğreneceklerin

  • Ortalama sipariş değeri, elde tutma ve dönüşüm formüllerini SQL sorgularına dönüştürmek
  • Bir kohort tablosu ve bir huni kurmak, sonuçları istatistiksel sınırlarını göz önünde tutarak yorumlamak
  • Pencere fonksiyonlarıyla medyan, yüzdelik ve ABC analizi hesaplamak

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

AOV = R ÷ Nₒ

Burada

AOV
ortalama sipariş değeri (average order value), manat
R
dönem geliri: Σ miktar · fiyat, manat
Nₒ
aynı dönemdeki sipariş sayısı
SQL
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

Okumaya devam etmek için ücretsiz kaydol

Dersin geri kalan bölümleri, test ve notlar Educora hesabıyla açılır. Kayıt bir dakika sürer; ilerlemen tüm cihazlarında saklanır.

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ı.

r = A ÷ C₀ · 100%

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
SQL
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
SQLite'ta 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

1) C₀ = 1 + 2 + 2 + 1 + 1 = 7 müşteri, A = 1 + 2 = 3.
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.

cᵢ = nᵢ ÷ n₁ · 100%

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.

SQL
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
En büyük kayıp 2. ve 3. aşamalar arasında: tekrar siparişi teşvik etmek (indirim kuponu, hatırlatma) en yararlı adım olabilir.

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.

k = ⌈ p ÷ 100 · n ⌉

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.

SQL
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
Ortalama (82,8) medyanın (86,5) altında: birkaç düşük puan (58, 64, 68) ortalamayı aşağı çekiyor.

Ö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

1) k = ⌈90 ÷ 100 · 20⌉ = ⌈18⌉ = 18.
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.
SQL
-- 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)
PostgreSQL, çalıştırılamaz. 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.

SQL
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.

Alıştırma · SQL
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?

Alıştırma · SQL
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 ise EXISTS ya da koşullu toplama ile sayılır.
  • Huni UNION ALL ile aşamalardan kurulur, dönüşüm ise FIRST_VALUE ve LAG ile hesaplanır.
  • Medyan uç değerlere dayanıklıdır; SQLite'ta ROW_NUMBER ve COUNT(*) OVER () ile, PostgreSQL'de percentile_cont ile hesaplanır.
  • Bir yüzdenin yanında her zaman mutlak sayıyı göster ve kohortları aynı gözlem penceresiyle karşılaştır.
Bu derste bir hata ya da yanlışlık mı gördün?

Kendini test et

10 soru. Her doğru cevap XP kazandırır.

Soru 1 / 10

Soru 1 / 10. Dönem geliri 3942,06 manat, sipariş sayısı 12. Ortalama sipariş değeri nedir?