Анализ эффективности маркетинговых каналов. SQL кейс

🎯 Цель

Показать практическое применение SQL для анализа маркетинговых данных: источников трафика, поведения пользователей, устройства, географии и конверсий.

📦 Описание данных

В кейсе использовались три таблицы:

• sessions — данные о сессиях:
  • session_id, client_id, utm_source, utm_medium, device, geo_city, visit_date
• hits — события в рамках сессии:
  • session_id, event_time, event_type, page_url
• conversions — данные о целевых действиях:
  • session_id, conversion_type, revenue

🧩 Бизнес-задачи

• Оценить конверсию по каналам (utm_source, utm_medium)
• Определить города и устройства с лучшими CR
• Оценить вклад каналов в выручку
• Построить SQL-воронку

🛠 Примеры SQL-запросов

SELECT 
  s.utm_source,
  COUNT(DISTINCT s.session_id) AS total_sessions,
  COUNT(DISTINCT c.session_id) AS conversions,
  ROUND(100.0 * COUNT(DISTINCT c.session_id) / COUNT(DISTINCT s.session_id), 2) AS conversion_rate
FROM sessions s
LEFT JOIN conversions c ON s.session_id = c.session_id
GROUP BY s.utm_source
ORDER BY conversion_rate DESC;
    

SELECT 
  s.device,
  COUNT(DISTINCT s.session_id) AS total_sessions,
  COUNT(DISTINCT c.session_id) AS conversions,
  ROUND(100.0 * COUNT(DISTINCT c.session_id) / COUNT(DISTINCT s.session_id), 2) AS cr
FROM sessions s
LEFT JOIN conversions c ON s.session_id = c.session_id
GROUP BY s.device
ORDER BY cr DESC;
    

SELECT 
  s.utm_source,
  SUM(c.revenue) AS total_revenue
FROM sessions s
JOIN conversions c ON s.session_id = c.session_id
GROUP BY s.utm_source
ORDER BY total_revenue DESC;