Okul.com.tr — Eğitim Ekosistemi

Lead Funnel Analiz Sorguları

3 dakika okuma süresi

Proje: Okul Database · Hub: Okul Database — Queries

Lead Funnel Analiz Sorguları

Temel Lead Sayımları

-- Toplam lead (test hariç)
SELECT COUNT(*) FROM leads
WHERE deleted_at IS NULL AND (is_test = 0 OR is_test IS NULL);
 
-- Aylık lead trendi
SELECT DATE_FORMAT(created_at, '%Y-%m') as month,
       COUNT(*) as lead_count
FROM leads
WHERE deleted_at IS NULL AND (is_test = 0 OR is_test IS NULL)
GROUP BY month
ORDER BY month;

Funnel Aşamaları

-- Onay durumu dağılımı
SELECT approved_type,
       COUNT(*) as count,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) as pct
FROM leads
WHERE deleted_at IS NULL AND (is_test = 0 OR is_test IS NULL)
GROUP BY approved_type;
 
-- Okul cevap durumu
SELECT school_answered_deal_status,
       COUNT(*) as count
FROM leads
WHERE deleted_at IS NULL AND (is_test = 0 OR is_test IS NULL)
GROUP BY school_answered_deal_status;

Kaynak Analizi

-- Kaynak bazlı lead dağılımı
SELECT source,
       COUNT(*) as count,
       AVG(CASE WHEN approved_type = 'approved' THEN 1 ELSE 0 END) as approval_rate
FROM leads
WHERE deleted_at IS NULL AND (is_test = 0 OR is_test IS NULL)
GROUP BY source
ORDER BY count DESC;
 
-- source_action (2026-04-02 sonrası için)
SELECT source, source_action, COUNT(*) as count
FROM leads
WHERE deleted_at IS NULL
  AND (is_test = 0 OR is_test IS NULL)
  AND source_action IS NOT NULL
GROUP BY source, source_action
ORDER BY count DESC;

Response Time Analizi

-- Müşteri görme gecikmesi (saat cinsinden)
SELECT school_id,
       COUNT(*) as lead_count,
       AVG(TIMESTAMPDIFF(HOUR, created_at, customer_viewed_at)) as avg_view_lag_hours,
       MIN(TIMESTAMPDIFF(HOUR, created_at, customer_viewed_at)) as min_lag,
       MAX(TIMESTAMPDIFF(HOUR, created_at, customer_viewed_at)) as max_lag
FROM leads
WHERE deleted_at IS NULL
  AND customer_viewed_at IS NOT NULL
  AND (is_test = 0 OR is_test IS NULL)
GROUP BY school_id
ORDER BY lead_count DESC
LIMIT 20;
 
-- Henüz görülmemiş lead'ler (müşteri tepkisizliği)
SELECT COUNT(*) as unseen_leads,
       AVG(TIMESTAMPDIFF(HOUR, created_at, NOW())) as avg_age_hours
FROM leads
WHERE deleted_at IS NULL
  AND customer_viewed_at IS NULL
  AND (is_test = 0 OR is_test IS NULL)
  AND approved_type = 'approved';

Okul Bazlı Performans

-- Okul başına lead → onay dönüşümü
SELECT l.school_id,
       sf.city_name,
       COUNT(*) as total_leads,
       SUM(CASE WHEN l.approved_type = 'approved' THEN 1 ELSE 0 END) as approved,
       ROUND(SUM(CASE WHEN l.approved_type = 'approved' THEN 1 ELSE 0 END)
             * 100.0 / COUNT(*), 1) as approval_rate_pct
FROM leads l
LEFT JOIN school_filters sf ON sf.school_id = l.school_id AND sf.deleted_at IS NULL
WHERE l.deleted_at IS NULL AND (l.is_test = 0 OR l.is_test IS NULL)
GROUP BY l.school_id, sf.city_name
HAVING total_leads >= 5
ORDER BY approval_rate_pct DESC;

Grafik Görünümü

Backlinkler