Кликабельность и доля заказов из клика по источникам рекламы
SQL, по которому посчитано
WITH ad_totals AS (
SELECT
source,
sum(impressions) AS impressions,
sum(clicks) AS clicks,
sum(orders) AS orders,
sum(cost) AS cost,
sum(revenue) AS revenue
FROM ads
WHERE date > (SELECT max(date) FROM ads) - INTERVAL 12 MONTH
GROUP BY source
)
SELECT
source,
CAST(impressions AS BIGINT) AS impressions,
CAST(clicks AS BIGINT) AS clicks,
CAST(orders AS BIGINT) AS orders,
round(clicks / nullif(impressions, 0), 4) AS ctr,
round(orders / nullif(clicks, 0), 4) AS click_to_order,
round(orders / nullif(impressions, 0), 5) AS impression_to_order,
round(cost, 0) AS cost_rub,
round(revenue, 0) AS revenue_rub,
round(cost / nullif(orders, 0), 0) AS cost_per_order
FROM ad_totals
WHERE impressions > 0
ORDER BY impressions DESC
LIMIT 30