Web funnels — API над solid_funnels_kumquat

Два ендпоінти /web-funnels над ClickHouse solid_funnels_kumquat — виміри, формули метрик, зв'язок із картками креативів і відомі межі.

Сторінка /web-funnels у фронтенді читає ClickHouse kumquat_market_analytics.solid_funnels_kumquat через GET /api/v1/web-funnels і GET /api/v1/web-funnels/schema. Як влаштована таблиця і хто її наповнює — коментар у db/init.sql і докстрінг web_funnel_analytics/solid_funnels.py; розклад — Airflow DAG-и. Опис для користувачів — Web funnels: колонки й формули.

Джерело правди — kumquat_api/web_funnels/: columns.py (виміри, SQL сум і формул, порядок колонок), schemas.py (контракт запиту й відповіді), service.py (збірка SQL, підтягування карток креативів, кеш фільтрів). Клієнт ClickHouse і Redis-кеш спільні з аналітикою креативів. Фронтенд — frontend/src/modules/web-funnels/.

Чому саме так

Окремий модуль, а не ще одна таблиця в analytics/. У двох поверхонь немає спільних вимірів і метрик: там кампанії, ad set-и й дані з картки креативу, тут воронка, план підписки й довжина тріалу. Спільний конструктор SQL з’явився б лише заради форми запиту WITH per_group … SELECT … OVER (), а її простіше повторити, ніж параметризувати. З analytics перевикористано тільки те, що від домену не залежить: клієнт, кеш, DimensionDef/MetricDef/SortDirectionDef, KEY_SEPARATOR.

Метрика ніколи не лежить у рядках обох видів одразу. Імпорт пише два види рядків, і їхні метрики не перетинаються:

product_name = 'unknown' — spend, impressions, clicks, app_users, app_revenue і чотири кроки воронки;
product_name = <план підписки> — leads, users_attepmts, first_purchases, LTV, upsells, upsells_revenue.

Тому групування без product_name зводить їх в один рядок, а групування за product_name або trial_days дає рядки плану з нульовим spend і один рядок unknown / порожній, де лежить увесь spend. Це властивість джерела, не баг запиту.

Вартісні метрики над нульовим spend — NULL, а не 0. cpa, cpm і cpc ділять nullIf(spend, 0). Інакше в розрізі за планом CPA був би $0, і це читалося б як безкоштовна покупка. roi_pct отримує NULL через nullIf(spend, 0) у знаменнику.

Гроші — Float64 з першої суми. spend, LTV, upsells_revenue, app_revenue у таблиці — Decimal. При Decimal / Decimal результат має масштаб лівого операнда, а ділення на нуль кидає виняток. toFloat64(sum(...)) у внутрішньому запиті знімає обидві проблеми.

trial_type — вимір trial_days, а не сума. Це довжина тріалу в днях (0, 3, 7, 28, 30, 84, 90), тож сума не має сенсу. Групується і фільтрується як if(sf.product_name = 'unknown', '', toString(sf.trial_type)). Рядок, бо всі фільтри передаються як Array(String). Порожній для рядків spend і воронки, бо вони теж несуть trial_type = 0: інакше весь spend потрапив би в кошик «0» до планів без тріалу, і той показував би CPA у тисячі доларів. Сортується як число (toInt32OrNull(trial_days), порожній рядок — в кінці), і значення у /schema теж: 7 іде перед 28.

Як це працює

Один запит на сторінку рядків і один на тотали, паралельно. Внутрішній CTE сумує базові величини по group_by, зовнішній рахує з цих сум формули, сортує й пагінує. count() OVER () дає meta.total без окремого запиту. Тотали — той самий CTE без GROUP BY: агрегат без групування завжди повертає рядок (нульові суми, NULL-відношення), тож порожнє вікно — не окремий випадок. Порядок доповнюється рештою колонок group_by, щоб пагінація через OFFSET була детермінованою.

Базові суми (`BASE_SQL`)

Ключ	SQL
`spend`	`toFloat64(sum(spend))`
`impressions`, `clicks`	`sum(...)`
`payment_attempts`	`sum(users_attepmts)`
`purchases`	`sum(leads)`
`matured_purchases`	`sumIf(leads, dateDiff('day', date, today()) > trial_type)`
`matured_first_purchases`	`sumIf(first_purchases, dateDiff('day', date, today()) > trial_type)`
`ltv_revenue`	`sum(toFloat64(LTV) * leads)`
`upsells`	`sum(upsells)`
`upsells_revenue`, `app_revenue`	`toFloat64(sum(...))`
`funnel_opens`, `second_screens`, `paywalls`, `checkouts`	`sum(...)`

LTV у таблиці — це LTV однієї покупки, а не виторг рядка. Тому ltv_revenue множить його на leads у кожному рядку до суми. Сума самих LTV додавала б середні значення, а не гроші.

Псевдоніми навмисно не збігаються з назвами колонок (ltv_revenue, а не LTV). Аналізатор ClickHouse підставляє псевдонім раніше за колонку, тож sum(LTV) AS LTV поруч із виразом, що читає LTV, дав би помилку вкладеного агрегату.

Формули (`DERIVED_SQL`)

Ключ	Колонка	Формула
`cpa`	CPA	`nullIf(spend, 0) / nullIf(purchases, 0)`
`ltv`	LTV	`(ltv_revenue + upsells_revenue) / nullIf(purchases, 0)`
`roi_pct`	ROI	`100 × (ltv_revenue + upsells_revenue + app_revenue − spend) / nullIf(spend, 0)`
`profit`	Profit	`ltv_revenue + upsells_revenue + app_revenue − spend`
`trial_cr_pct`	CR from trial	`100 × matured_first_purchases / nullIf(matured_purchases, 0)`
`conversion_pct`	Conversion	`100 × purchases / nullIf(funnel_opens, 0)`
`first_screen_to_paywall_cr_pct`	CR 1st screen → paywall	`100 × paywalls / nullIf(funnel_opens, 0)`
`first_screen_cr_pct`	CR 1st screen	`100 × second_screens / nullIf(funnel_opens, 0)`
`paywall_cr_pct`	CR paywall	`100 × paywalls / nullIf(second_screens, 0)`
`checkout_cr_pct`	CR checkout	`100 × checkouts / nullIf(paywalls, 0)`
`purchase_cr_pct`	CR purchase	`100 × purchases / nullIf(checkouts, 0)`
`payment_success_pct`	Payment pass rate	`100 × purchases / nullIf(payment_attempts, 0)`
`cpm`	CPM	`1000 × nullIf(spend, 0) / nullIf(impressions, 0)`
`ctr_pct`	CTR	`100 × clicks / nullIf(impressions, 0)`
`cpc`	CPC	`nullIf(spend, 0) / nullIf(clicks, 0)`

Усі відношення — це відношення сум за рядок (ratio of sums), а не середнє з денних значень. Округлення — 2 знаки.

LTV і Profit рахуються по-різному навмисно. Апсейли входять в LTV однієї покупки, а виторг застосунку (app_revenue) додається лише до Profit і ROI. Таке визначення дала аналітика.

Profit без spend не NULL, на відміну від вартісних метрик. У розрізі за планом spend нульовий, тож Profit там дорівнює виторгу рядка. Так вирішив продукт.

Порядок колонок у таблиці задає METRICS у columns.py. Це продуктове рішення: спершу гроші (Spend, Purchases, CPA, LTV, ROI, Profit, CR from trial, апсейли, App revenue), далі конверсії й кроки воронки, а рекламні метрики (CPM, CTR, CPC, Impressions, Clicks) у самому кінці.

CR from trial рахує лише дозрілі когорти. Рядок потрапляє в чисельник і знаменник, тільки коли від його дня минуло більше днів, ніж триває тріал. Без цього свіжі тріали, які ще не мали шансу сконвертуватися, тягнули б метрику вниз. Рядки з trial_type = 0 дозрівають наступного дня. today() береться за часовим поясом сервера ClickHouse.

Виміри

date, creo_name, app, funnel, platform (Device), country (Geo), media_source, product_name (Subscription), trial_days. Пошук (positionCaseInsensitive) іде по creo_name і funnel. Фільтри — по одному повторюваному query-параметру на вимір (?platform=ios&platform=android). Значення фільтрів для /schema — один groupUniqArray по всій таблиці, з Redis-кешем на 5 хвилин (web_funnels:filter_options:v1; при зміні форми — підняти версію).

date групується з кроком date_grain (day, week, month, quarter, year; за замовчуванням day). Тому його SQL обирається на кожен запит (DATE_GRAIN_SQL у columns.py), а не лежить у Dimension. Значення — текст, який сортується як рядок: 2026-10-06 (день; для тижня — його понеділок, toMonday), 2026-10, 2026-Q4, 2026. Перший і останній кошик бувають неповними: в них лише дні з вікна date_from..date_to. Фільтра за датою немає, бо його роль виконує саме вікно.

Картка креативу

Коли creo_name є в group_by, сервіс одним запитом CreativesRepository.find_by_names бере картки для назв поточної сторінки і кладе в рядок creative: {id, preview_url}. preview_url — це media.preview_url картки. Join creatives_current у ClickHouse не підходить: у тій view немає медіа. Сервіс знає репозиторій лише через протокол CreativesByName, а конкретний клас підставляється в kumquat_api/dependencies/services.py (get_web_funnels_service).

Доступ

Роутер змонтовано з get_current_user і WriteAccess. Обидва ендпоінти — GET, тож сторінку бачать усі ролі, member включно.

Відомі межі

У розрізі за планом немає spend і кроків воронки — див. «Чому саме так». Розкласти spend за планом з цих даних неможливо: рекламний кабінет не знає, що купить користувач.
Не всі назви збігаються з картками. На старті ~41% creo_name мали картку в Kumquat. Решта — сирі ad_id, які імпорт не зіставив через solid_funnels_kumquat_ad_info, і рядок unknown (spend без назви креативу). Такі рядки показуються, щоб тотали сходилися, але без посилання.
Історія — тільки те, що завантажив імпорт. DAG переписує останні 5 днів, рядки покупок (product_name != 'unknown') — trial_type + 28 днів; глибше вікно треба окремо завантажити CLI-командою import-solid-funnels з більшим --days.
Немає графіків по днях, діалогу креативу й експорту в Google Sheets — на відміну від /analytics.