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

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

Агрегация — это преобразование детальных записей в сводные показатели по нужным разрезам и периодам. Консолидация — объединение агрегатов из разных источников/юридических лиц/продуктов в единый отчет с корректировками (валюта, политика учета, элиминации). Ниже — системный подход: от проектирования витрин до эксплуатации отчетности.

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. Следуя описанной архитектуре, вы превращаете разнородные данные в надежные своды для управления продуктом, финансами и рисками.