Аналітика креативів — analytics API

Три ендпоінти /analytics/creatives* над ClickHouse creative_performance — каталог вимірів і метрик, збірка SQL, кеш фільтрів і відомі розходження зі скоркардом.

Сторінка /analytics у фронтенді не ходить у Mongo взагалі: вона читає ClickHouse kumquat_market_analytics.creative_performance через три ендпоінти під /api/v1/analytics/creatives. Це другий із трьох споживачів тієї самої таблиці — поряд зі скоркардом креативів (kumquat-creative-metrics, пише в Mongo) і analyst-агентом (генерує SQL сам). Усі троє читають однакові рядки й можуть розходитися у визначеннях, тому розділ «Відомі межі» тут не косметика.

Джерело правди — kumquat_api/analytics/: columns.py (каталог вимірів і метрик), schemas.py (контракт запиту й відповіді), service.py (збірка SQL і збірка відповіді), client.py (клієнт ClickHouse), cache.py (Redis-кеш).

Звідки беруться дані

MySQL {app}.top_creo_for_tableau        MySQL {app}.tt_creatives_data_country
        │  dag_kumquat_import_creative_performance, 3 рази на добу  │
        ▼                                                           ▼
ClickHouse …creative_performance        ClickHouse …tiktok_creative_performance
        │                    │                      │               │
        │ analytics API      │ kumquat-creative-    │ analyst-agent │
        │ (ця сторінка)      │ metrics → Mongo ◄────┼───────────────┘
        ▼                    ▼                      ▼
   /analytics          картка креатива         чат-відповіді

Ця сторінка читає тільки creative_performance. Друга таблиця — власний звіт TikTok, і її читають лише скоркард (hook/hold і реакції для карток із source = TIK) та analyst-agent. Що саме в ній лежить і чим вона не є копією першої — Архітектура analyst-агента і tables.yaml семантичного шару.

Наповнення таблиці, розклад і asset-ланцюг — Airflow DAG-и. Скоркард, який рахує по тих самих рядках, але пише в Mongo, — README пакета kumquat-creative-metrics. Семантичний шар агента — Архітектура analyst-агента.

Ендпоінти

Усі три — GET, під Depends(get_current_user) і WriteAccess. Ролі member гейт WriteAccess не блокує: він відмовляє за HTTP-методом, а тут лише читання.

Ендпоінт	Що віддає
`/analytics/creatives`	Рядки, згруповані по обраних вимірах, плюс `totals` по всьому вікну і `meta` з пагінацією
`/analytics/creatives/daily`	Ряд по днях за вікно, `totals` і `previous_totals` за попередній період тієї ж довжини
`/analytics/creatives/schema`	Каталог вимірів і метрик, значення фільтрів, напрямки сортування — усе, з чого фронтенд будує тулбар

Спільні параметри фільтра (AnalyticsFilterParams): date_from, date_to, created_from, created_to, search і по одному повторюваному параметру на кожен фільтрований вимір (?platform=ios&platform=android → IN (...)). Вікно за замовчуванням — 30 днів по date.today(); date_from > date_to — це 422, а не порожня відповідь.

created_from / created_to — це інше вікно, ніж date_from / date_to: перше каже, які креативи брати (за днем їх створення в kumquat), друге — яку витрату по них рахувати. За замовчуванням воно не обмежене: відсутній параметр означає «будь-який креатив», а не «створений за останні 30 днів» — дефолт тут мовчки ховав би всю історію. Інвертована пара — теж 422.

Додатково для таблиці: group_by (кілька вимірів, дублікати схлопуються), sort_by, sort_dir (asc/desc), limit (1–500, дефолт 50), offset. Дефолт group_by — ["creo_name"], дефолт сорту — spend_usd desc.

Сортування по виміру, якого немає в group_by, відхиляється як 422 у валідаторі: такої колонки немає в зовнішньому SELECT, і без перевірки помилка вилізла б із ClickHouse як 500.

Виміри й метрики

Каталог оголошено один раз у columns.py — і /schema, і збірка SQL читають його, тож новий вимір додається в одному місці.

Виміри: creo_name, campaign_name, adset_name (пошукові, у дропдауни не йдуть — висока кардинальність), product, platform, media_source, country, ad_type, reels, designer, predesigner, creo_marketologist, geo, language (фільтровані), created_date (ані пошуковий, ані фільтрований — див. нижче).

geo і language — атрибути самого креатива з creatives_current, короткі коди (US, PL, EN, CA-FR). geo — це не country: перше — локаль креатива, як її веде kumquat, друге — вільний текст країни, під якою MySQL звітує витрату (United States). Обидва лишаються в каталозі, бо відповідають на різні питання; лейбли взяті з dictionaries/creative-columns.json, щоб чіп тут читався так само, як поле в картці креатива.

created_date — день створення креатива, toDate(cr.created_at) з creatives_current. Це не колонка date_creative у creative_performance (та не читається взагалі, див. «Відомі межі») — джерело тут kumquat, а не MySQL. Три речі про нього:

Це єдиний вимір, який не є голою колонкою. Dimension.expression тримає його SQL; sql віддає вираз замість table.key. Вираз лишається константою модуля, тож allowlist-захист від ін’єкції не змінився.
toDate обов’язковий: created_at — це DateTime64(3), і групування по ньому як є дало б рядок на кожну мілісекунду.
nullIf(..., toDate('1970-01-01')) прибирає епоху. JOIN тут LEFT, а join_use_nulls = 0, тож рядок creative_performance, чий creo_name не має креатива в kumquat, отримує не NULL, а дефолт колонки — епоху. Без nullIf такі рядки датувалися б 1970-м у колонці й потрапляли б у будь-яке вікно created_from, що сягає так глибоко. З ним вони читаються як «немає креатива» («—» у таблиці) і випадають із фільтра, як і будь-яке порівняння з NULL.

Фільтрованим він навмисно не є: дата — це діапазон, а не список значень. У FILTER_DIMENSIONS він коштував би groupUniqArray по кожному унікальному дню створення на кожному запиті /schema.

Базові адитивні міри — impressions, clicks, video_views, hooks, installs, purchases, trials, spend_usd, ltv. Похідні — spend_pct, roas, romi_pct, cr_pct, ctr_pct, c2i_pct, i2l_pct, hook_rate_pct, hold_rate_pct, ipm, cpm, cpc, cpi, cpa.

Просумувати міру — не те саме, що показати її. video_views, hooks і trials сумуються лише як знаменники (hold rate, hook rate, i2l, cr) і колонками не є. Що саме малює таблиця — це окремий кортеж METRICS, і він же задає порядок колонок: /schema віддає цей список, а фронтенд малює metrics у тому порядку, в якому вони прийшли. Порядок виписаний руками, а не виведений із BASE + DERIVED, бо це продуктове рішення — і закріплений тестом, щоб перестановка робилася свідомо, в рев’ю.

hooks — єдина міра, чиє ім’я не збігається з колонкою: під нею video_views_15 (перегляди 15+ секунд). Мапінг живе в _BASE_MEASURE_COLUMN, щоб решта коду працювала з ключами, а не з іменами колонок.

duration_ms не є мірою взагалі. Це тривалість відео з creatives_current, а отже стала величина в межах креатива — її не можна ні сумувати, ні усереднювати по рядках creative_performance: креатив, що крутився в 20 країнах × 30 днів, перетягнув би середнє на себе в 600 разів сильніше за креатив з одного дня. Тому колонка рахується лише коли creo_name є в group_by (тоді рядок — це один креатив, і any() точний за визначенням), а інакше віддає NULL. З тієї ж причини вона сама тягне за собою JOIN, якого жоден вимір не просив, — і лише тоді, коли справді рахується.

unit кожної метрики (int / usd / ratio / percent) — це підказка форматування для фронтенду; сервер віддає числа, а не рядки.

Чому саме так

Жоден ідентифікатор у SQL не приходить із запиту. Усе, що інтерполюється в рядок запиту — колонки групування, колонка сортування, — береться з allowlist-ів columns.py; значення фільтрів ідуть параметрами ClickHouse. Це і є захист від ін’єкції: динамічний запит будується виключно з констант модуля.

Похідні метрики — відношення сум, пораховані після агрегації. Внутрішній CTE сумує базові міри по групі, зовнішній SELECT ділить суми. avg() порядкової ставки зважив би креатив зі 100 показами так само, як креатив із 10 млн. Кожне ділення йде через nullIf(x, 0): spend_usd і ltv — Decimal, а ділення Decimal на нуль ClickHouse не пробачає, це помилка, а не inf.

ORDER BY завжди має тайбрейк по решті колонок групування. Значення в колонці сортування повторюються, а недетермінований порядок ламає OFFSET-пагінацію: той самий рядок з’являвся б на двох сторінках і зникав із третьої.

Аліаси в запиті totals навмисно не збігаються з іменами колонок (total_spend, а не spend_usd). Аналізатор ClickHouse підставляє аліас із SELECT у сусідні вирази, тож sum(ltv) поруч із round(sum(ltv), 2) AS ltv перетворюється на sum(round(sum(ltv))) — вкладений агрегат, помилка 184.

spend_pct рахується через sum(spend_usd) OVER () — частка групи у всій вибірці, а не в сторінці. Саме тому його немає в денному ендпоінті: там вікно запиту навмисно ширше за запитаний період (див. нижче), і OVER () змішав би поточний період із попереднім.

Два запити таблиці йдуть паралельно. query_metrics збирає рядки і totals одночасно через asyncio.gather + asyncio.to_thread: клієнт clickhouse-connect синхронний, і без to_thread він блокував би event loop.

Клієнт один на процес і без session id. autogenerate_session_id=False — сервер лочить сесію, тож спільний клієнт із закріпленим session id серіалізував би паралельні запити й падав з «concurrent queries within the same session». Створюється на старті застосунку; якщо ClickHouse недоступний, застосунок усе одно піднімається, а залежність намагається під’єднатися на першому запиті.

Порівняння з попереднім періодом

/analytics/creatives/daily сканує вдвічі ширше вікно, ніж запитано: date_from - length … date_to. Один скан покриває і поточний період, і рівний йому попередній, з якого рахується дельта в картках. Дні без рядків добиваються нулями вже в Python, щоб графік не мав дірок, а meta віддає prev_date_from / prev_date_to — фронтенд не має перераховувати межі сам.

Підсумки періоду — теж відношення сум (PeriodTotals), а не середнє по днях.

Кеш значень фільтрів

filter_options у /schema — це groupUniqArray по кожному фільтрованому виміру, тобто повний скан таблиці. Значення змінюються рідко, тому відповідь лежить у Redis 5 хвилин під ключем analytics:filter_options:v1.

Дві речі, які тут легко зламати:

Суфікс v1 версіонує форму payload-у вручну. Змінилася структура — підняти суфікс, інакше свіжий деплой прочитає з кешу несумісний об’єкт.
Кеш деградує в міс, а не в помилку. AnalyticsCache ковтає і помилки читання (повертає None → перерахунок), і помилки запису. Аналітика не має падати через недоступний Redis.

Новий фільтрований вимір з’явиться в дропдауні із запізненням до 5 хвилин — ключ той самий, поки не піднято суфікс. Саме тому додавання geo й language підняло ключ до v3: без цього перші 5 хвилин після деплою обидва чіпи мовчки не рендерилися б, бо закешований payload не має їхніх ключів, а тулбар ховає вимір із порожнім списком значень.

Фронтенд

Модуль frontend/src/modules/analytics — деталі в Архітектурі фронтенду. Одна річ важлива саме тут: model/dimensions.ts дублює каталог вимірів із columns.py не як плейсхолдер на час завантаження, а як allowlist для URL-параметрів (group_by, sort_by живуть у query string через nuqs). Вимір, доданий на бекенді й не доданий туди, рендериться кнопкою, яка мовчки не пише ?group_by=. Каталоги мусять збігатися.

Відомі межі

Скоркард на картці креатива рахує те саме, агент — ні. Картка перейшла на purchases (DEFN_VERSION = 3), тож усі спільні метрики збігаються число в число на однаковому вікні. Семантичний шар агента досі рахує CPA і trial_to_purchase_cr по purch — вужчому лічильнику перших покупок, — тож розходиться з обома поверхнями. Що лишається різним і після зведення: вікно (тут довільний діапазон, на картці d7/t7/life) і watermark (картка закінчується вчорашнім днем, ця сторінка бачить і сьогоднішній, напівзалитий).
filter_options не звужується ні датою, ні рештою фільтрів. Дропдаун показує всі значення за всю історію таблиці, у тому числі ті, яких у вибраному вікні немає.
Значення media_source — fb, tiktok, applovin. Ні facebook, ні google, ні organic у таблиці не існує; фільтр по них поверне порожньо.
TikTok-рядки тут і TikTok-цифри на картці креатива — з різних таблиць. media_source = 'tiktok' у цій таблиці — це атрибуція, і саме її ця сторінка показує. Але відео цієї таблиці не міряє: video_views_15 для TikTok-рядків малоінформативний, бо TikTok 15-секундних переглядів не публікує. Тому картка креатива бере покази, старти відео й довгі перегляди TikTok-креатива з tiktok_creative_performance, а гроші й конверсії — звідси. Наслідок: hook і hold TikTok-креатива на картці не відтворюються з цієї сторінки, а його покази там і тут — різні числа.
Частина колонок таблиці не читається взагалі: robby (її навіть не заповнює імпорт), base_purch, all_purch, repurched_users, landing_impressions, web_installs, web_purchases, date_creative. Вони є в схемі й доступні агенту, але в analytics API не виведені.
duration_ms порожній, поки creatives не перелито. Колонку додано і в таблицю, і в flatten.py, але наявні рядки заповняться лише після ресинку: TRUNCATE TABLE kumquat_market_analytics.creatives ON CLUSTER 'ml', після чого наступний батч kumquat-ch-sync затягне колекцію заново (його вотермарка — max(updated_at), тож порожня таблиця означає «синкати все»).
designer / creo_marketologist у creative_performance — це короткі коди, і тільки з того моменту, як їх почав дописувати import-DAG. Для креативів, старших за перехід на новий неймінг, їх розпарсив із назви апстрім; для новіших їх пише kumquat із users.initials (див. Airflow DAG-и). Обидва джерела дають ту саму двобуквену абетку, але рядок без картки в kumquat лишається порожнім. Для розрізу по людях у самому API все одно беріть assignee / task_owner з creatives_current — це повні імена й поточний стан картки.
Тест-кампанії не виключаються. Так само, як у скоркарді: рахується джерело цілком. Маркер тесту стоїть усередині назви кампанії, тож фільтр LIKE 'test_%' не спрацював би в жодному разі.
Експорту немає — ні CSV, ні вивантаження всієї вибірки: limit жорстко обмежений 500 рядками на сторінку.