1. Marketing ROI Analysis
/*
Query 1 — Marketing ROI Analysis
Required canonical query:
SELECT mc.name,
COUNT(DISTINCT wv.visitor_id) AS visitors,
COUNT(DISTINCT c.conversion_id) AS conversions,
SUM(c.value) AS total_revenue,
mc.budget_spent,
(SUM(c.value) - mc.budget_spent) / mc.budget_spent * 100 AS roi_percent
FROM marketing_campaigns mc
LEFT JOIN website_visitors wv
ON DATE(wv.session_start) BETWEEN mc.start_date AND mc.end_date
LEFT JOIN conversions c
ON wv.visitor_id = c.visitor_id
GROUP BY mc.campaign_id;
Date-range-filtered version used in this app:
SELECT mc.name,
COUNT(DISTINCT wv.visitor_id) AS visitors,
COUNT(DISTINCT c.conversion_id) AS conversions,
SUM(c.value) AS total_revenue,
mc.budget_spent,
CASE
WHEN mc.budget_spent = 0 THEN NULL
ELSE (SUM(c.value) - mc.budget_spent) / mc.budget_spent * 100
END AS roi_percent
FROM marketing_campaigns mc
LEFT JOIN website_visitors wv
ON DATE(wv.session_start) BETWEEN mc.start_date AND mc.end_date
LEFT JOIN conversions c
ON wv.visitor_id = c.visitor_id
WHERE mc.start_date <= @end_date
AND mc.end_date >= @start_date
AND (wv.session_start IS NULL OR DATE(wv.session_start) BETWEEN @start_date AND @end_date)
AND (c.conversion_date IS NULL OR DATE(c.conversion_date) BETWEEN @start_date AND @end_date)
GROUP BY mc.campaign_id;
*/
2. Funnel Conversion Rates
/*
Query 2 — Funnel Conversion Rates
Required canonical query:
SELECT source,
COUNT(DISTINCT visitor_id) AS total_visitors,
SUM(CASE WHEN conversion_type = 'signup' THEN 1 ELSE 0 END) AS signups,
SUM(CASE WHEN conversion_type = 'purchase' THEN 1 ELSE 0 END) AS purchases,
(SUM(CASE WHEN conversion_type = 'purchase' THEN 1 ELSE 0 END) * 1.0 / COUNT(DISTINCT visitor_id)) * 100 AS conversion_rate
FROM website_visitors wv
LEFT JOIN conversions c
ON wv.visitor_id = c.visitor_id
WHERE session_start >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY source;
Date-range-filtered version used in this app:
SELECT wv.source,
COUNT(DISTINCT wv.visitor_id) AS total_visitors,
SUM(CASE WHEN c.conversion_type = 'signup' THEN 1 ELSE 0 END) AS signups,
SUM(CASE WHEN c.conversion_type = 'purchase' THEN 1 ELSE 0 END) AS purchases,
CASE
WHEN COUNT(DISTINCT wv.visitor_id) = 0 THEN 0
ELSE (SUM(CASE WHEN c.conversion_type = 'purchase' THEN 1 ELSE 0 END) * 1.0 / COUNT(DISTINCT wv.visitor_id)) * 100
END AS conversion_rate
FROM website_visitors wv
LEFT JOIN conversions c
ON wv.visitor_id = c.visitor_id
WHERE DATE(wv.session_start) BETWEEN @start_date AND @end_date
AND (c.conversion_date IS NULL OR DATE(c.conversion_date) BETWEEN @start_date AND @end_date)
GROUP BY wv.source;
*/
3. Cohort Analysis + Heat Map
/*
Query 3 — Cohort Analysis
Required canonical query:
SELECT DATE_FORMAT(session_start, '%Y-%m') AS cohort_month,
COUNT(DISTINCT visitor_id) AS visitors,
AVG(pages_viewed) AS avg_pages_per_session,
COUNT(DISTINCT conversion_id) / COUNT(DISTINCT visitor_id) * 100 AS conversion_rate_percent
FROM website_visitors wv
LEFT JOIN conversions c
ON wv.visitor_id = c.visitor_id
GROUP BY DATE_FORMAT(session_start, '%Y-%m')
ORDER BY cohort_month;
Date-range-filtered version used in this app:
SELECT DATE_FORMAT(wv.session_start, '%Y-%m') AS cohort_month,
COUNT(DISTINCT wv.visitor_id) AS visitors,
AVG(wv.pages_viewed) AS avg_pages_per_session,
CASE
WHEN COUNT(DISTINCT wv.visitor_id) = 0 THEN 0
ELSE COUNT(DISTINCT c.conversion_id) / COUNT(DISTINCT wv.visitor_id) * 100
END AS conversion_rate_percent
FROM website_visitors wv
LEFT JOIN conversions c
ON wv.visitor_id = c.visitor_id
WHERE DATE(wv.session_start) BETWEEN @start_date AND @end_date
AND (c.conversion_date IS NULL OR DATE(c.conversion_date) BETWEEN @start_date AND @end_date)
GROUP BY DATE_FORMAT(wv.session_start, '%Y-%m')
ORDER BY cohort_month;
*/
Low
Medium-low
Medium
High
Very high
4. Customer Lifetime Value by Source
/*
Query 4 — Customer Lifetime Value by Source
Required canonical query:
SELECT wv.source,
COUNT(DISTINCT wv.visitor_id) AS unique_customers,
AVG(c.value) AS avg_order_value,
SUM(c.value) / COUNT(DISTINCT c.conversion_id) AS lifetime_value
FROM website_visitors wv
JOIN conversions c
ON wv.visitor_id = c.visitor_id
WHERE c.conversion_type = 'purchase'
GROUP BY wv.source;
Date-range-filtered version used in this app:
SELECT wv.source,
COUNT(DISTINCT wv.visitor_id) AS unique_customers,
AVG(c.value) AS avg_order_value,
SUM(c.value) / COUNT(DISTINCT c.conversion_id) AS lifetime_value
FROM website_visitors wv
JOIN conversions c
ON wv.visitor_id = c.visitor_id
WHERE c.conversion_type = 'purchase'
AND DATE(wv.session_start) BETWEEN @start_date AND @end_date
AND DATE(c.conversion_date) BETWEEN @start_date AND @end_date
GROUP BY wv.source;
*/