BI-модуль: функциональная спецификация и архитектура (Cohorts, Funnel, Churn, GMV, CAC vs LTV)¶

Цели¶

Единая BI-ready архитектура для продуктовой, маркетинговой и финансовой аналитики
Быстрые, консистентные витрины без тяжёлых ад-хок пересчётов
Стандартизованные определения метрик и единый семантический слой

Область¶

Cohort analysis (регистрация, первый платёж и другие события)
Conversion funnel (настраиваемые шаги, конверсии, дриллдауны)
Churn/Retention (активность/подписки, удержание, отток)
GMV dashboard (основные деньги: GMV, Net Revenue, Margin, AOV, ARPU)
CAC vs LTV (сопоставление маркетинговых затрат и жизненной ценности)
Материализованные витрины и инкрементальные сборки
Оркестрация, тесты качества данных, производительность и безопасность

Архитектура DWH¶

Слои данных
raw: приземление из источников без значимых трансформаций
staging: нормализация, дедупликация, приведение типов и идентификаторов
core: звёздная схема (факты и измерения), единые определения метрик
marts: специализированные витрины для BI-дашбордов и отчётов
Хранилище
Postgres/BigQuery/ClickHouse (в зависимости от требований по объёму/стоимости/латентности)
Обновление
Инкрементальные модели в dbt + расписание (Airflow/Prefect/GitHub Actions/dbt Cloud)
Материализованные представления для тяжёлых агрегаций

Домейн: маркетплейс (ядро)¶

Измерения (dim)
dim_date: календарь и производные поля
dim_user: пользователи, атрибуты, канал/кампания привлечения
dim_merchant: продавцы, атрибуты
dim_product: товары, категория, принадлежность продавцу
dim_campaign: каналы/кампании, UTM-метки
Факты (fact)
fact_events: события поведения (визиты, регистрации, корзины, покупки)
fact_orders: заказы (статусы, суммы, даты)
fact_order_items: позиции заказа (товары, количество, цена, выручка)
fact_marketing_spend: рекламные расходы, клики, показы
Вспомогательные core-таблицы
user_cohort: принадлежность пользователей к когортам (type/date/month/channel/campaign)
user_activity_periods: активность по периодам (месяц/неделя/день)
dim_funnel_step: конфигурация воронки (шаги ↔ события)
funnel_user_steps_daily: фактическое прохождение шагов пользователями по дням

Витрины (marts)¶

Cohort Retention
Ввод: user_cohort, fact_events, dim_date
Вывод: retained_users, cohort_size, retention_rate, revenue_per_user, периодность (месяцы/недели)
Conversion Funnel
Ввод: dim_funnel_step, fact_events/funnel_user_steps_daily
Вывод: users_entered_step, users_completed_next_step, conversion_to_next_step, конверсии по каналам/датам/продуктам
Churn/Retention
Ввод: user_activity_periods (или подписки)
Вывод: users_active, users_retained_next_period, users_churned, churn_rate, retention_rate
GMV Daily
Ввод: fact_orders
Вывод: gmv, net_revenue, margin, orders_count, unique_buyers, aov, arpu, сегменты
CAC by Cohort/Campaign
Ввод: user_cohort, fact_marketing_spend
Вывод: acquired_users, marketing_cost, cac
LTV by Cohort/Campaign
Ввод: user_cohort, fact_orders, dim_date
Вывод: period_number, revenue, revenue_per_user, cumulative_ltv

dbt‑проект¶

Структура
sources: определения источников (prod/events/marketing)
staging: нормализация сырых таблиц
core: dim_, fact_, user_cohort, activity, funnel
marts: mv_* витрины для отчётов
Конфигурации
BigQuery: partition_by (по timestamp/int date_key), cluster_by (user_id, event_name, order_id, campaign_id)
ClickHouse: MergeTree с PARTITION BY месяц, ORDER BY по ключам фильтрации/джоинов
Postgres: партиции по дате, btree индексы на ключах (date_key, user_id, campaign_id)
Пайплайн
dbt deps → dbt seed → dbt build (или dbt run/test по тегам: staging → core → marts)
Инкрементальные модели для больших витрин, полные пересборки при изменении логики

Тесты качества данных¶

Schema tests
not_null, unique, relationships (fact ↔ dim)
Data tests
gmv_non_negative: суммы не отрицательные
net_revenue_le_gmv: чистая выручка не превышает GMV
churn_rate_in_0_1, retention_rate_in_0_1
no_duplicate_events: уникальность event_id
Freshness
контроль задержки поставки данных из источников и SLA обновления витрин

BI‑семантика и дашборды¶

Semantic layer
Единые меры: GMV, Net Revenue, Margin, AOV, ARPU, CAC, LTV, Churn, Retention, Funnel Conversion
Роли доступа (страна/бренд/мерчант)
Дашборды
CEO Overview: GMV, CAC vs LTV, Funnel, Churn, ключевые когорты
Growth: Acquisition, CAC/LTV, когорты
Product: Retention, события, воронки
Finance: GMV/Net/Margin, возвраты, сравнение с планом

Производительность¶

Индексация/кластерация
Ключи по самым частым фильтрам: date_key, user_id, event_name, order_id, campaign_id
Партиционирование
По дате события/заказа; ограничение диапазонов при запросах
Инкрементальные сборки
Пересчёт только новых периодов; периодические full refresh для консистентности
Материализация
Тяжёлые витрины как materialized view/table; контроль веса и времени

Безопасность и доступ¶

RBAC
Разграничение прав по слоям: raw/staging только инженерным ролям, core/marts для BI‑аналитиков
PII
Минимизация и псевдонимизация пользовательских данных в DWH
Секреты
Хранение учётных данных в секрет‑менеджере; запрет логирования ключей и токенов

Наблюдаемость¶

Логирование
Логи оркестрации, длительность задач, объёмы данных
Метрики
SLA обновления витрин, стоимость запросов (BQ), нагрузка (CH)
Алармы
Падение тестов, задержки поставки данных, аномалии в метриках

API/Интеграции¶

Чтение
BI‑инструменты подключаются к marts/core
Автоматизация
Webhooks/Jobs для принудительного пересчёта витрин при крупных импортах или смене определения метрик

Роадмап доработок (видение)¶

Атрибуция маркетинга
Модели атрибуции: first‑touch, last‑touch, position‑based; конфигурируемые окна
Identity Resolution
Сведение user_external_id, device_id, email/phone в единый user_id; граф связности, приоритеты источников
SCD Type 2 для измерений
История изменений категорий, кампаний, мерчантов
Валюта и курсы
Нормализация мультивалютности, таблица курсов, пересчёт в базовую валюту
Возвраты и корректировки
Флаги возвратов, сторно, промо; согласованные правила GMV/Net/Margin
Конфигурируемые воронки
Разные funnel_id по продуктам/странам/платформам, динамическая смена шагов без переписывания кода
Семантические определения
Централизованный каталог метрик (Data Catalog), версионирование и аудит изменений
Когортные ревенью‑модели
Cumulative LTV с дисконтированием, удержание по revenue‑кохортам
Кросс‑DWH переносимость
Макросы dbt для унификации логики под BQ/CH/PG; единые тесты
Стоимость и оптимизация
В BigQuery: кластеризация, денормализация витрин, safe_divide, ограничения bytes billed
В ClickHouse: грамотные ORDER BY, материализация промежуточных агрегатов, TTL для сырых партиций

Definition of Done¶

Реализованы слои raw/staging/core/marts
Наполнены dim_ и fact_; созданы вспомогательные core‑таблицы
Собраны витрины cohorts/funnel/churn/gmv/cac_ltv
Настроены dbt tests (schema+data), проходят без ошибок
Оркестрация ежедневно/ежечасно; мониторинг SLA
BI‑дашборды подключены и отражают согласованные определения метрик

Точные определения метрик (оцифровка)¶

GMV: сумма order.gmv для статусов paid/finished за период/сегмент
Net Revenue: сумма order.net_revenue для статусов paid/finished
Margin: сумма order.margin для статусов paid/finished
AOV: GMV / количество заказов
ARPU: Net Revenue / количество уникальных покупателей
Cohort Size: количество уникальных пользователей в когорте
Retained Users (period n): пользователи когорты с активностью/покупкой в период n
Retention Rate (n): Retained Users(n) / Cohort Size
Churn Rate (n): 1 − Retention Rate(n) для периода n+1 относительно n
CAC (по когорте/кампании): Marketing Cost / Acquired Users
LTV_N: суммарная Net Revenue пользователей когорты за первые N периодов / Cohort Size
Funnel Conversion (step i→i+1): Users Completed Next Step / Users Entered Step(i)

Готовые шаблоны dbt для BigQuery (структура и конфиг)¶

Корневой проект: analytics_dwh
Конфигурация:
seeds: схема staging
sources: схемы staging/raw_* (для теста источники указывают на seeds)
core: таблицы с partition_by/cluster_by
marts: инкрементальные модели с partition_by/cluster_by
Пайплайн:
dbt deps → dbt seed → dbt build
или по тегам: staging → core → marts

ClickHouse specifics и dbt-шаблоны¶

DWH
Таблицы фактов: MergeTree или ReplicatedMergeTree
Партиционирование: PARTITION BY toYYYYMM(event_timestamp/order_created_at)
ORDER BY: по ключам фильтрации и джоинов (date_key, user_id, event_name, order_id, campaign_id)
Профиль dbt
Адаптер: dbt-clickhouse
Тип подключения: native или http
Пример профиля
name: analytics_dwh_clickhouse
type: clickhouse
schema: analytics
host: clickhouse
port: 8123
user: default
password: пусто или из секретов
Примеры моделей
fact_orders: MergeTree с PARTITION BY месяц и ORDER BY (order_date_key, user_id, order_id)
fact_events: MergeTree с PARTITION BY месяц и ORDER BY (event_date_key, user_id, event_name, event_timestamp)
fact_marketing_spend: MergeTree с PARTITION BY date_key и ORDER BY (campaign_id, date_key)
Seeds и tests
Используются те же CSV seeds, что и для BigQuery
dbt tests: schema tests и data tests на тех же моделях

Тестовый набор данных и data tests¶

Seeds (CSV): пользователи, заказы, события, маркетинг
Примеры data tests:
gmv_non_negative: нет отрицательных сумм
retention_rate_between_0_and_1: ретеншн в пределах [0,1]
Запуск:
dbt deps; dbt seed; dbt build
или dbt test --select tag:marts

Доработки для полноценного сервиса¶

Атрибуция маркетинга с конфигурируемыми окнами и моделями
Identity Resolution и единый user_id
Историзация измерений (SCD2)
Мультивалютность и курсы
Возвраты/сторно/промо в Net/Margin
Конфигурируемые воронки и семантика в каталоге метрик
Макросы dbt для кросс‑DWH переносимости
Оптимизация стоимости и производительности (BQ/CH/PG)