難易度: 上級 / 使用テーブル: ad_clicks, campaigns, orders
問題
ad_clicks、campaigns、ordersを使い、広告クリック日から7日以内に発生した注文をキャンペーン別に集計してください。
スターターSQL
SELECT
c.name,
COUNT(a.id) AS clicks,
SUM(a.cost) AS cost
FROM ad_clicks a
JOIN campaigns c ON a.campaign_id = c.id
GROUP BY c.id, c.name
ORDER BY cost DESC解答例
SELECT
c.name,
c.channel,
COUNT(DISTINCT a.id) AS clicks,
SUM(a.cost) AS cost,
COUNT(DISTINCT o.id) AS attributed_orders,
SUM(o.total_amount) AS attributed_revenue,
ROUND(SUM(o.total_amount) * 1.0 / NULLIF(SUM(a.cost), 0), 2) AS attributed_roas
FROM ad_clicks a
JOIN campaigns c ON a.campaign_id = c.id
LEFT JOIN orders o
ON a.user_id = o.user_id
AND julianday(o.order_date) BETWEEN julianday(a.click_date) AND julianday(a.click_date) + 7
GROUP BY c.id, c.name, c.channel
ORDER BY attributed_roas DESC見るべきポイント
広告接触と購入を期間条件付きでJOINする練習です。
実務ではアトリビューション期間、重複クリック、媒体IDと自社DB IDの対応が論点になります。