Стоимость нового покупателя
SQL, по которому посчитано
WITH first_purchase AS (
SELECT customer_id, min(date) AS first_date
FROM deals
WHERE status = 'Выиграна'
GROUP BY customer_id
),
newcomers AS (
SELECT date_trunc('month', first_date) AS month, count(*) AS customers
FROM first_purchase
GROUP BY month
),
spend AS (
SELECT date_trunc('month', date) AS month, sum(cost) AS cost
FROM ads
WHERE source IN ('Яндекс Директ', 'VK Реклама')
GROUP BY month
)
SELECT spend.month, round(spend.cost / newcomers.customers) AS cac
FROM spend
JOIN newcomers USING (month)
WHERE spend.month > (SELECT max(date) FROM ads) - INTERVAL 12 MONTH
ORDER BY spend.month