CTR и конверсия в заказ по кампаниям за последний месяц
SQL, по которому посчитано
WITH camp AS (
SELECT
source,
campaign,
sum(impressions) AS impressions,
sum(clicks) AS clicks,
sum(cost) AS cost,
sum(orders) AS orders,
sum(revenue) AS revenue
FROM ads
WHERE date >= (SELECT date_trunc('month', max(date)) FROM ads)
GROUP BY source, campaign
),
src AS (
SELECT
source,
sum(clicks) * 1.0 / nullif(sum(impressions), 0) AS source_ctr,
sum(orders) * 1.0 / nullif(sum(clicks), 0) AS source_cr
FROM camp
GROUP BY source
)
SELECT
c.source,
c.campaign,
c.impressions,
c.clicks,
round(c.clicks * 1.0 / nullif(c.impressions, 0), 3) AS ctr,
round(c.orders * 1.0 / nullif(c.clicks, 0), 3) AS cr,
round(s.source_ctr, 3) AS source_ctr,
round(s.source_cr, 3) AS source_cr,
round(c.cost, 0) AS cost,
round(c.revenue, 0) AS revenue
FROM camp c
JOIN src s ON c.source = s.source
WHERE c.clicks >= 100
AND c.clicks * 1.0 / nullif(c.impressions, 0) > s.source_ctr
AND c.orders * 1.0 / nullif(c.clicks, 0) < s.source_cr
ORDER BY ctr DESC, c.clicks DESC
LIMIT 30