Агрегація та консолідовані звіти

Агрегація та консолідовані звіти

Агрегація - це перетворення детальних записів у зведені показники за потрібними розрізами і періодами. Консолідація - об'єднання агрегатів з різних джерел/юридичних осіб/продуктів в єдиний звіт з коригуваннями (валюта, політика обліку, елімінації). Нижче - системний підхід: від проектування вітрин до експлуатації звітності.

1) Завдання та види консолідації

Операційна/продуктова: DAU/WAU/MAU, конверсії, виручка по країнах/каналах/платформах.
Фінансова (упр. і регуляторна): P&L, Cash Flow, Balance Sheet, GGR/Net Gaming Revenue, податки, звіти по юрособах.
Ризик/комплаєнс: антифрод, RG-індикатори, SLA/доступність, дрейф даних/моделей.
Стратегічна: зведені KPI по холдингу, портфелю брендів, регіонах.

2) Архітектура шару агрегацій

Шари даних: 'raw'→'staging'→'core'( факти/вимірювання) →'marts'( агрегати під звіти).
Семантичний шар: визначення метрик, календар, валюти, роллап-правила.
Зерно і time-grain: 'event','user _ day','txn','brand _ country _ day'→ подальші ролапи до тижня/місяця/кварталу.
Ідентичності: стабільні ключі (user/brand/legal_entity), таблиці відповідностей (mapping).

3) Ролапи та ієрархії

Ієрархії вимірювань: «Гра → Категорія → Провайдер», «Канал → Кампанія → Креатив», «Місто → Регіон → Країна → Кластер».
Правила підсумовування: адитивні (сума), напівадитивні (snapshot - середнє/останнє на період), неадитивні (коефіцієнти/відсотки).
Дедуплікація: унікальні користувачі та події по зерну; уникайте подвійного обліку при злитті джерел.

4) Мультивалюта, таймзони, календар

Час: зберігайте'event _ time _ utc'і локальні зрізи; календар свят/раб-днів.
Валюта: зберігайте «сиру» суму +'base _ ccy'за курсом на дату операції (або середній курс періоду із застереженням).
Нормалізація: показуйте явні одиниці/валюту/дату курсу.

sql
WITH fx AS (
SELECT date, from_ccy, to_ccy, rate
FROM dim_fx_rates
WHERE to_ccy = 'EUR'
),
tx AS (
SELECT t. txn_id, t. amount, t. ccy, t. brand, t. country, t. event_date
FROM fact_tx
)
SELECT brand, country, DATE_TRUNC('month', event_date) AS month,
SUM(amount COALESCE(fx. rate, 1)) AS revenue_eur
FROM tx
LEFT JOIN fx
ON fx. date = tx. event_date AND fx. from_ccy = tx. ccy
GROUP BY 1,2,3;

5) Консолідація за джерелами/юрособами

Mapping планів рахунків: єдиний CoA (Chart of Accounts) + меппінг з локальних планів.
Політики обліку: IFRS/GAAP/податкові правила → шар трансформацій (наприклад, виручка брутто/нетто, визнання бонусів/комісій).
Елімінації внутрішньогрупових обертів: виключення транзакцій між юрособами холдингу.
Методи консолідації: повна, пайова (proportionate), облік за пайовою участю (equity method).
Міноритарії: виділення частки неконтрольованих акціонерів (NCI).

sql
WITH interco AS (
SELECT a. txn_id
FROM fact_tx a
JOIN dim_counterparty b ON a. counterparty_id = b. id
WHERE a. legal_entity IN (SELECT id FROM dim_legal WHERE group_id = 1)
AND b. legal_entity IN (SELECT id FROM dim_legal WHERE group_id = 1)
)
SELECT
FROM agg_pl_month
WHERE txn_id NOT IN (SELECT txn_id FROM interco);

6) Якість і звірки (reconciliation)

Звірки за джерелами: сума за джерелом = сума у вітрині (з допуском за курсом/часом).
Інваріанти: 'DAU ≤ MAU', сума по днях = місячний підсумок (для адитивних метрик).
Контроль повноти: частка NULL, частка дублікатів, відставання по свіжості.
Звіт про розбіжності: список невідповідностей і кроки усунення.

7) Продуктивність і SLO

SLO свіжості: оперативні агрегати - лаг ≤ 15 хв; денні - до 06:00 локального часу; місячні - до T + 1/T + 3.
Оптимізація: пред-агрегації, інкрементальні перерахунки, партіонування (за датою/брендом/країною), кеш для популярних зрізів.
Обмеження UI: ≤ 12 категорій на графік; пагінація таблиць; lazy-load.

8) Управління визначеннями та версіями

Словник метрик: код, визначення, формула, джерело, одиниці, власники, guardrails.
Версіонування: `METRIC_vN`; будь-які правки → нова версія + backfill і changelog.
Семантичний шар: одне джерело істини для BI/експериментів/відвантажень.

9) Безпека та доступ

RLS/CLS: доступ за ролями (країна/бренд/юрособа), маскування PII.
Аудит: хто і який звіт вивантажував; контроль експорту (терміни, токени).
Мінімізація: видавати агрегати, а не сирі PII-дані.

10) Типові агрегати та візуалізації

Продукт: воронки (step-bars), когорти (heatmap), утримання D7/D30, ARPU/ARPPU, GGR/Net.
Фінанси: P&L по ієрархії, водоспад (bridge) факторів, структура виручки по регіонах, динаміка G&A.
Операції/ML: SLA, latency p95/p99, PR-AUC/Recall@FPR≤x%, PSI-heatmap дрейфа.

11) Паспорт консолідованого звіту (шаблон)

Код/версія: `CONSOL_PNL_v3`

Призначення: управлінський P&L по групі брендів, мультивалюта → базова валюта EUR

Покриття: всі юрособи групи; метод - повна консолідація; NCI - виділяється

Джерело/шар: 'mart _ fin _ pnl _ v3'( засновано на'fact _ tx _ v2','dim _ legal','dim _ fx _ rates')

Гранулярність: місяць (ролап з дня)

Елімінації: 'intercompany = true'- виключені внутрішньогрупові оберти

SLO: T+1 06:00 лок.; доступність ≥ 99. 9%

Звірки: звіт бухгалтерії'BK _ PNL _ T + 1', розбіжність ≤ 0. 3%

Власники: Finance Analytics, Data Platform

Guardrails: FX-таблиця не старше 24 год; coverage транзакцій ≥ 99. 5%

12) Часті помилки і як їх уникати

Тихі зміни формул: завжди через версії і changelog.
Подвійний облік: дублі джерел/джойнів - контролюйте ключі та зерно.
Змішування часових зон: централізований календар і UTC-зберігання.
Неправильні відсотки: агрегуйте чисельник/знаменник, а не «середнє середніх».
«Сирі» курси: явна дата/джерело FX, консистентна політика округлень.
Відсутність елімінацій: міжкомпанійні обороти спотворюють звіти.
Непрозора свіжість: завжди показуйте «оновлено N хвилин тому».

13) Псевдо-SQL: інкрементальний місячний агрегат

sql
-- Recalculate only affected days/months
WITH changed_days AS (
SELECT DISTINCT DATE(event_time_utc) AS d
FROM fact_tx_delta -- new/modified per day
),
daily AS (
SELECT
DATE(event_time_utc) AS d,
brand, country,
SUM(net_revenue_eur) AS net_eur
FROM fact_tx
WHERE DATE(event_time_utc) IN (SELECT d FROM changed_days)
GROUP BY 1,2,3
)
MERGE INTO agg_month_brand_country m
USING (
SELECT DATE_TRUNC('month', d) AS month, brand, country, SUM(net_eur) AS net_eur
FROM daily
GROUP BY 1,2,3
) s
ON (m. month = s. month AND m. brand = s. brand AND m. country = s. country)
WHEN MATCHED THEN UPDATE SET m. net_eur = s. net_eur, m. updated_at = NOW()
WHEN NOT MATCHED THEN INSERT (month, brand, country, net_eur, updated_at)
VALUES (s. month, s. brand, s. country, s. net_eur, NOW());

14) Процеси та експлуатація

1. Дизайн: цілі/аудиторії, метрики, ієрархії, валюта/таймзони.
2. Дані: контракти джерел, схеми, тести якості.
3. Побудова вітрин: семантичні об'єкти, ролап-правила, елімінації.
4. Звірки: автоматичні звіти розбіжностей, тікети на виправлення.
5. Реліз: версія, документація, навчання користувачів.
6. Моніторинг: свіжість, completeness, дублікати, час відгуку, інциденти.
7. Ревізія: квартальна перевірка визначень, меппінгів, FX-політики.

15) Чек-лист перед випуском консолідованого звіту

• Визначення метрик та ієрархій задокументовані в семантичному шарі
• Налаштовані конверсія валют і таймзони; показані одиниці та дата курсу
• Реалізовані елімінації внутрішньогрупових оборотів/NCI (якщо застосовується)
• Інваріанти та звірки з референс-джерелами проходять у допусках
• Інкрементальні перерахунки та партіонування включені
• SLO свіжості/доступності задані; статуси оновлення відображаються
• RLS/CLS і маскування PII налаштовані; аудит експорту включено
• Версія/changelog і власники вказані; є runbook інцидентів

Підсумок

Агрегація і консолідація - це не просто «GROUP BY», а цілісна система: узгоджені визначення, коректні ролапи, мультивалюта і календар, елімінації і звірки, спостережуваність і SLO. Слідуючи описаній архітектурі, ви перетворюєте різнорідні дані в надійні склепіння для управління продуктом, фінансами і ризиками.