Теория: Расходы на рекламу

Следующим этапом в нашей сквозной аналитике является оценка затрат на рекламу. Мы уже провели предварительные шаги, включая анализ клиентов, их платежей, дат и каналов привлечения. Теперь наша задача - добавить информацию о затратах на рекламу.

На текущем этапе мне хотелось бы поделиться информацией о текущем состоянии рынка рекламы в России. Самыми крупными рекламодателями являются крупные компании, такие как финтех, банки, розничные сети, платформы, телекоммуникационные компании и IT-ориентированные предприятия. Мы видим, что рекламные бюджеты этих компаний составляют миллиарды рублей ежегодно. Это огромный рынок, который основным образом оценивается в сфере онлайн-рекламы.

Из графика справа видно, что интернет-реклама занимает самую большую долю, составляя 324 миллиарда рублей ежегодно. За ней следует наружная реклама, транзитная реклама и цифровые рекламоносители (телевизионная реклама). Доля радиорекламы немного увеличилась, в то время как пресса сильно снизилась, так как люди предпочитают электронные и онлайн-медиа бумажным изданиям.

С учетом того, что интернет в настоящее время является основным источником трафика и рекламы, и даже старшее поколение переходит в онлайн, рекламные бюджеты компаний переключаются на интернет-рекламу.

Практика

К нашим данным добавим еще одну таблицу ads с затратами на каждый источник рекламы. Для источника Direct полагаем затраты равными нулю.

SELECT * FROM ads;

https://www.db-fiddle.com/f/2gR99rUuRhS4FftgVAwZWo/8

Соберем сводную таблицу

SELECT *
FROM registrations AS r
LEFT JOIN payments AS p
ON r.user_id = p.user_id
LEFT JOIN visits AS v
ON v.cookie_id = r.cookie_id
LEFT JOIN ads AS a
ON a.ad_id = v.ad_id
LIMIT 3;

https://www.db-fiddle.com/f/2gR99rUuRhS4FftgVAwZWo/9

Теперь посчитаем сумарные затраты на рекламу по каждому платному каналу. Заменив LEFT JOIN на INNER JOIN, уберем канал Direct с нулевыми затратами.

SELECT
    v.ad_id AS source,
    COUNT(DISTINCT p.user_id) AS users_count,
    SUM(amount) AS source_amount,
    SUM(amount_spent) AS source_spent
FROM registrations AS r
LEFT JOIN payments AS p
ON r.user_id = p.user_id
LEFT JOIN visits AS v
ON v.cookie_id = r.cookie_id
INNER JOIN ads AS a
ON a.ad_id = v.ad_id
GROUP BY 1;

https://www.db-fiddle.com/f/2gR99rUuRhS4FftgVAwZWo/10

Наконец, посчитаем ROI каждого канала

SELECT
    v.ad_id AS source,
    COUNT(DISTINCT p.user_id) AS users_count,
    SUM(amount) AS source_amount,
    SUM(amount_spent) AS source_spent,
    (SUM(amount) - SUM(amount_spent)) * 100 / SUM(amount_spent) AS roi
FROM registrations AS r
LEFT JOIN payments AS p
ON r.user_id = p.user_id
LEFT JOIN visits AS v
ON v.cookie_id = r.cookie_id
INNER JOIN ads AS a
ON a.ad_id = v.ad_id
GROUP BY 1;

https://www.db-fiddle.com/f/2gR99rUuRhS4FftgVAwZWo/11