Вычисляемые поля

Бран · продюсер «Хроник Пограничья»

На дашборд нужны ARPPU и доля платящих по каналам и по странам. Маркетинг говорит, что Германия у нас самая платящая страна, и хочет туда бюджет. Проверь.

В прошлом уроке меры были готовыми агрегатами: сумма, количество. Но заказчики спрашивают про отношения: выручку на платящего, долю платящих, конверсию. В BI-системах для них заводят «вычисляемое поле» с формулой. У нас формула живёт прямо в SELECT, и запрос остаётся единственным местом, где метрика определена.

Формула в SELECT, а не в голове

Вычисляемое поле это выражение над агрегатами. Определения метрик берём из урока про метрики, выручку чистую, как в боссе Пограничья:

WITH clean AS (
  SELECT p.*
  FROM purchases p
  JOIN players pl ON pl.id = p.player_id
  WHERE p.status = 'paid'
    AND pl.email NOT LIKE '%@studio.dev'
    AND p.id IN (SELECT min(id) FROM purchases GROUP BY receipt_id)
),
per_player AS (
  SELECT player_id, sum(amount_rub) AS revenue
  FROM clean
  GROUP BY player_id
)
SELECT pl.channel,
       count(*) AS players,
       count(pp.player_id) AS payers,
       round(100.0 * count(pp.player_id) / count(*), 1) AS paying_share,
       round(sum(pp.revenue) / count(pp.player_id)) AS arppu
FROM players pl
LEFT JOIN per_player pp ON pp.player_id = pl.id
WHERE pl.email NOT LIKE '%@studio.dev'
GROUP BY pl.channel
ORDER BY pl.channel;

  channel   | players | payers | paying_share | arppu
------------+---------+--------+--------------+-------
 ads_vk     |     804 |    102 |         12.7 |  1244
 ads_yandex |     588 |     68 |         11.6 |   721
 bloggers   |     712 |     55 |          7.7 |   658
 crosspromo |     419 |     62 |         14.8 |  1932
 organic    |    1554 |    204 |         13.1 |  1597

Два места, где формула ломается чаще всего.

Знаменатель. Доля платящих делится на всех игроков канала, а не на платящих. Поэтому начинаем с players и присоединяем покупки через LEFT JOIN: игрок без покупок остаётся строкой, у которой pp.player_id равен NULL. count(pp.player_id) считает только платящих, count(*) всех.

Порядок деления. Сначала агрегат, потом деление: sum(выручка) / count(платящих). Если поделить внутри строки, а потом взять среднее, получится среднее отношений, и у маленьких групп оно гуляет сильнее, чем у больших.