Retention-матрица за 10 минут: SQL-запрос для BigQuery

Retention-матрица за 10 минут: SQL-запрос для BigQuery

Строить удержание по когортам (cohort retention) — базовая задача, если вы работаете с LTV и retention в E-com или B2B SaaS. В BigQuery это делается одним запросом без выгрузок. Вот готовый шаблон.

**1. Подготовьте данные.**
Убедитесь, что у вас есть таблица событий с полями:
- `user_id`
- `event_date` (DATE)
- `event_name` (например, `'purchase'` или `'session'`)

Если данных нет — используйте GA4-экспорт в BigQuery (таблица `events_*`).

**2. Определите дату первой активности для каждого пользователя.**
Это будет когорта (первый день, когда пользователь совершил целевое действие).
```sql
WITH cohort AS (
SELECT
user_id,
MIN(event_date) AS first_date
FROM `project.dataset.events`
WHERE event_name = 'purchase'
GROUP BY user_id
)
```

**3. Присоедините все последующие активности и рассчитайте день когорты.**
```sql
SELECT
cohort.first_date,
DATE_DIFF(events.event_date, cohort.first_date, DAY) AS day_number,
COUNT(DISTINCT events.user_id) AS users
FROM `project.dataset.events` AS events
JOIN cohort ON events.user_id = cohort.user_id
WHERE events.event_name = 'purchase'
GROUP BY first_date, day_number
```
На выходе получите строки вида: `2026-01-15, 0, 1000` — это 1000 пользователей, совершивших первую покупку в день когорты; `2026-01-15, 1, 200` — те же пользователи, купившие на следующий день, и т.д.

**4. Превратите в retention-матрицу.**
Чтобы видеть проценты, добавьте знаменатель — общее число пользователей в когорте (на day_number=0).
```sql
WITH base AS (
...предыдущий запрос...
),
cohort_size AS (
SELECT first_date, users AS size
FROM base
WHERE day_number = 0
)
SELECT
base.first_date,
base.day_number,
ROUND(SAFE_DIVIDE(base.users, cohort_size.size) * 100, 2) AS retention_pct
FROM base
JOIN cohort_size USING(first_date)
ORDER BY first_date, day_number
```

**5. Визуализируйте.**
Готовый результат можно сразу вывести в Data Studio (Looker Studio) или через `EXPORT` загрузить в электронную таблицу. В BigQuery также можно использовать `ARRAY_AGG` для транспонирования строк в столбцы — если нужно получить классическую таблицу «дни по горизонтали».

**Почему это работает:**
- Минимум движений — только один проход по таблице.
- Считается удержание для любого события: покупка, визит, заполнение формы.
- Легко адаптировать под LTV (подставить сумму вместо COUNT).

Попробуйте на своих данных

— @BigQuery4MarketingPro