Доля рекламного бюджета по группам ROAS за последний месяц
SQL, по которому посчитано
WITH month_ads AS (
SELECT campaign, source, cost, revenue
FROM ads
WHERE date >= (SELECT date_trunc('month', max(date)) FROM ads)
),
campaigns AS (
SELECT
campaign,
source,
sum(cost) AS cost,
sum(revenue) AS revenue
FROM month_ads
GROUP BY campaign, source
),
groups AS (
SELECT
CASE
WHEN coalesce(revenue, 0) / cost < 1 THEN 'ROAS ниже 1'
ELSE 'ROAS 1 и выше'
END AS roas_group,
cost
FROM campaigns
WHERE cost > 0
)
SELECT
roas_group,
round(sum(cost), 0) AS cost,
round(sum(cost) / sum(sum(cost)) OVER (), 4) AS budget_share
FROM groups
GROUP BY roas_group
ORDER BY budget_share DESC