ROAS кампаний по источникам за последний месяц
SQL, по которому посчитано
WITH last_month AS (
SELECT date_trunc('month', max(date)) AS month_start
FROM ads
),
campaign_stats AS (
SELECT
source,
campaign,
SUM(cost) AS cost,
SUM(revenue) AS revenue,
SUM(orders) AS orders,
CASE
WHEN SUM(cost) > 0 THEN SUM(revenue) / SUM(cost)
ELSE NULL
END AS roas
FROM ads
WHERE date >= (SELECT month_start FROM last_month)
GROUP BY source, campaign
)
SELECT
source,
campaign,
source || ' / ' || campaign AS campaign_label,
round(cost, 2) AS cost,
round(revenue, 2) AS revenue,
orders,
round(roas, 2) AS roas
FROM campaign_stats
WHERE cost > 0
ORDER BY roas DESC
LIMIT 30