Анализ сохранения — одна из самых критических метрик в маркетинговых данных. Чтобы понять, какая группа пользователей остаётся активной и какая кампания создаёт долгосрочную ценность, нужны таблицы когорт. Проблема в том, что классические запросы когорт на десятках миллионов строк событийных данных запускаются каждый раз заново, и стоимость запроса становится астрономической. Построить архитектуру когорт в production — такую, которая обновляется каждое утро, отвечает аналитику за 3 секунды, но при этом минимизирует затраты через правильную стратегию партиционирования — это отдельная инженерная задача. В этой статье мы пошагово разберём конкретную архитектуру таблиц когорт в BigQuery и dbt, стратегию материализованных представлений и оптимизацию стоимости запросов.

Почему когорт-таблица должна быть отдельной

Расчёт retention нельзя выполнять каждый раз с нуля на сырых данных событий. Если компания электронной коммерции генерирует 50 миллионов событий в день, то на вопрос "Какой процент пользователей, зарегистрировавшихся в январе 2026, вернулись на 30-й день?" BigQuery должна будет просканировать 1.5 миллиарда строк. Такой запрос занимает 10-15 секунд и обрабатывает 200-300 ГБ. Если аналитик в день делает 20 таких запросов по разным сегментам, месячная стоимость легко превышает $500.

Таблица когорт решает эту проблему: вы заранее агрегируете событийные данные по группам, предварительно вычисляете метрики каждой когорты на каждый день и сохраняете результаты. Когда аналитик делает запрос, BigQuery сканирует только таблицу когорт, а не сырые данные событий. 1000 когорт × 90 дней × 5 метрик = 450 000 строк. Запрос такой таблицы выполняется за 200 мс и обрабатывает всего 5 МБ.

Но такой подход создаёт новую проблему: как обновляется таблица когорт? Каждый день, когда приходят новые события, вы пересчитываете всю историю заново или используете инкрементальное обновление? Какая стратегия партиционирования оптимизирует одновременно производительность запросов и стоимость обновления? Ответы на эти вопросы кроются в дизайне материализованных представлений и инкрементальных dbt-моделей.

Стратегия партиционирования: cohort_date или observation_date?

Выбор ключа партиционирования для таблицы когорт критичен. Есть два кандидата: дата образования когорты (cohort_date) и дата наблюдения (observation_date).

Партиционирование по cohort_date: разбиение по дате первой активности пользователя. Когорта января 2026 — один раздел, февраля — другой. Преимущество: при появлении новой когорты вы пишете только в её раздел, старые разделы не трогаете. Недостаток: для получения 90-дневного retention одной когорты BigQuery должна просканировать 90 разных разделов. Производительность падает.

Партиционирование по observation_date: разбиение по дате наблюдения. Каждый день — отдельный раздел, содержащий метрики всех когорт на этот день. Преимущество: запросы вроде "тренд retention за последние 7 дней" сканируют только 7 разделов. Недостаток: каждый день нужно обновлять все когорты, что дорого обходится инкрементальной обработке.

Правильный ответ — гибридная архитектура с двумя таблицами: таблица снимков (observation_date партиционирована) и таблица агрегированных данных (cohort_date партиционирована). Таблица снимков обновляется каждый день и питает dashboard-ы аналитиков. Таблица агрегированных данных обновляется еженедельно для глубокого сравнения когорт. Эта архитектура соответствует best practice BigQuery: разделение узких и широких таблиц.

-- Схема таблицы снимков (партиционирована по observation_date)
CREATE TABLE `analytics.cohort_retention_snapshot`
PARTITION BY observation_date
CLUSTER BY cohort_date, channel, device_category
AS
SELECT
  observation_date,
  cohort_date,
  channel,
  device_category,
  cohort_size,
  day_n,
  active_users,
  retention_rate
FROM ...

Материализованные представления vs инкрементальные модели dbt

В BigQuery материализованное представление (MV) автоматически делает инкрементальное обновление — при поступлении новых событий перезапускает базовый запрос и кэширует результат. Но у MV есть три ограничения: максимум 5 join'ов, отсутствие window function, отсутствие ручного управления партициями.

Расчёт когорт обычно требует 3+ join'ов (таблицы пользователей, событий, подписок) и window function вроде LAG(), FIRST_VALUE(). В таком случае MV не подходит. Альтернатива — инкрементальная модель dbt.

Инкрементальная модель dbt позволяет определить пользовательскую стратегию merge. Каждый день вы обновляете только разделы последних 7 дней (WHERE observation_date >= CURRENT_DATE() - 7). Это снижает стоимость запроса на 85%. Пример dbt-модели:

{{ config(
    materialized='incremental',
    partition_by={
      "field": "observation_date",
      "data_type": "date"
    },
    cluster_by=['cohort_date', 'channel'],
    incremental_strategy='insert_overwrite'
) }}

WITH daily_cohorts AS (
  SELECT
    DATE(first_seen_at) AS cohort_date,
    user_id,
    acquisition_channel AS channel
  FROM {{ ref('users') }}
  WHERE first_seen_at IS NOT NULL
),

daily_activity AS (
  SELECT
    DATE(event_timestamp) AS activity_date,
    user_id,
    COUNT(*) AS event_count
  FROM {{ ref('events') }}
  WHERE event_name IN ('page_view', 'purchase')
  {% if is_incremental() %}
    AND DATE(event_timestamp) >= CURRENT_DATE() - 7
  {% endif %}
  GROUP BY 1, 2
)

SELECT
  a.activity_date AS observation_date,
  c.cohort_date,
  c.channel,
  DATE_DIFF(a.activity_date, c.cohort_date, DAY) AS day_n,
  COUNT(DISTINCT c.user_id) AS cohort_size,
  COUNT(DISTINCT a.user_id) AS active_users,
  SAFE_DIVIDE(COUNT(DISTINCT a.user_id), COUNT(DISTINCT c.user_id)) AS retention_rate
FROM daily_cohorts c
LEFT JOIN daily_activity a
  ON c.user_id = a.user_id
WHERE a.activity_date >= c.cohort_date
{% if is_incremental() %}
  AND a.activity_date >= CURRENT_DATE() - 7
{% endif %}
GROUP BY 1, 2, 3, 4

Когда такая модель запускается ежедневно, она перезаписывает только разделы последних 7 дней. Обрабатываемый объём падает с 20 ГБ в день до 2 ГБ. Годовая экономия на стоимости запросов — $2400.

Выбор ключа кластеризации

Партиционирования недостаточно, нужна ещё кластеризация. Таблица когорт фильтруется по трём измерениям: cohort_date (время), channel (источник), device_category (устройство). В BigQuery порядок ключа кластеризации важен — поле с наибольшей кардинальностью должно быть первым.

Анализ кардинальности:

  • cohort_date: 365 значений (1 год)
  • channel: 15-20 значений (organic, paid_search, social, email...)
  • device_category: 3-4 значения (desktop, mobile, tablet)

Правильный порядок: CLUSTER BY cohort_date, channel, device_category. Такой порядок ускорит запросы вроде "какой процент пользователей из Instagram на мобильных в Q4 2025 вернулись на 30-й день?" в 10 раз.

Оптимизация стоимости запросов: глубина предварительной агрегации

Уровень гранулярности таблицы когорт определяет баланс между стоимостью и производительностью. Храните ли вы отдельную строку для каждой комбинации когорта × канал × устройство × день, или только общие итоги?

Вариант 1: гранулярная таблица — каждая комбинация когорта × канал × устройство × день_n отдельной строкой. Общее количество строк: 365 когорт × 20 каналов × 4 устройства × 90 дней = 2.6 миллиона строк. Преимущество: аналитик может делать pivot по любому сегменту. Недостаток: высокая стоимость хранения ($50/ТБ → ~$0.15 в месяц).

Вариант 2: агрегированная таблица — только когорта × день_n, без разделения по каналам и устройствам. Общее количество строк: 365 × 90 = 32 850 строк. Преимущество: минимальная стоимость хранения и запросов. Недостаток: невозможно сделать разбор по каналам.

Правильный подход — двухуровневая архитектура: core metrics — гранулярная (с разделением по каналам и устройствам), extended metrics — агрегированная (только когорта × день_n). Это оптимизирует хранилище и даёт аналитическую гибкость. Core metrics питает dashboard-ы, extended metrics используется для ad-hoc анализа.

Кроме того, определите политику истечения разделов в BigQuery: разделы старше 90 дней автоматически удаляются. Анализ retention редко требует данных старше 90 дней, и эта политика снижает годовую стоимость хранения на 60%.

Решение проблемы разрешения идентичности на уровне когорты

Самая тёмная сторона анализа когорт — столкновения user_id и разрешение идентичности. Если пользователь зарегистрировался на десктопе и совершил покупку на мобильном, создаются два разных user_id. Если таблица когорт не объединит эти идентичности, retention будет недооценён на 20%.

Решение: перед созданием таблицы когорт объедините таблицу графика идентичности. В процессе First-Party Вори и архитектура измерений вы уже создали столбец canonical_user_id — используйте его здесь. В dbt-модели используйте view users_unified вместо таблицы users.

WITH unified_users AS (
  SELECT
    canonical_user_id,
    MIN(first_seen_at) AS cohort_date,
    ARRAY_AGG(DISTINCT acquisition_channel IGNORE NULLS ORDER BY first_seen_at LIMIT 1)[OFFSET(0)] AS channel
  FROM {{ ref('users_unified') }}
  GROUP BY 1
)

Такой подход правильно вычисляет cross-device retention. В production это даёт разницу 15-25% в метриках retention. Когда таблица разрешения идентичности обновляется, таблица когорт также должна переписываться — определите зависимость в DAG dbt:

models:
  - name: cohort_retention_snapshot
    config:
      materialized: incremental
    depends_on:
      - ref('users_unified')

Production-чеклист: мониторинг и алертинг

Когда таблица когорт запущена в production, непрерывно отслеживайте 3 метрики:

  1. Freshness: когда последний раздел обновлён? Определите тест freshness в dbt-core, отправьте alert в Slack если раздел старше 24 часов.
  2. Drift количества строк: если cohort_size сегодня отличается от вчерашнего на 30%, в pipeline данных есть проблема. Используйте запрос BigQuery с STDDEV() для контроля.
  3. Spike стоимости запросов: если средняя стоимость запроса к таблице когорт выросла с $0.01 до $0.10, partition pruning не работает. Проверьте таблицу INFORMATION_SCHEMA.JOBS.

Создайте Google Cloud Monitoring dashboard для этих