**Как собрать когорту по первой покупке и считать retention в BigQuery за 15 минут**

**Как собрать когорту по первой покупке и считать retention в BigQuery за 15 минут**

Когортный анализ — базовый навык маркетолога в эпоху, когда первая покупка перестала быть главной метрикой. Покажу схему, которую сам использую в проектах для e-commerce.

**Исходные данные:** таблица `orders` с полями `user_id`, `order_date`, `revenue`. Подойдёт и выгрузка из CRM, и сырые данные из GA4 в BigQuery.

**Шаг 1. Определяем дату первой покупки для каждого пользователя**

```sql
WITH first_orders AS (
SELECT
user_id,
MIN(DATE(order_date)) AS first_order_date
FROM `project.dataset.orders`
GROUP BY user_id
)
```

**Шаг 2. Помечаем каждый заказ когортой пользователя**

```sql
orders_with_cohort AS (
SELECT
o.user_id,
o.order_date,
DATE_TRUNC(fo.first_order_date, MONTH) AS cohort_month,
DATE_DIFF(DATE(o.order_date), DATE(fo.first_order_date), DAY) AS days_since_first
FROM `project.dataset.orders` o
JOIN first_orders fo USING(user_id)
)
```

**Шаг 3. Считаем размер когорты и активных пользователей по месяцам**

```sql
SELECT
cohort_month,
COUNT(DISTINCT user_id) AS cohort_size,
COUNT(DISTINCT CASE WHEN days_since_first BETWEEN 0 AND 30 THEN user_id END) AS m0,
COUNT(DISTINCT CASE WHEN days_since_first BETWEEN 31 AND 60 THEN user_id END) AS m1,
COUNT(DISTINCT CASE WHEN days_since_first BETWEEN 61 AND 90 THEN user_id END) AS m2
FROM orders_with_cohort
GROUP BY cohort_month
ORDER BY cohort_month
```

**Шаг 4. Считаем retention в процентах**

Делим `m0`, `m1`, `m2` на `cohort_size` — получаем классическую когортную таблицу. Для визуализации выгрузите в Looker Studio, выбрав heatmap-диаграмму: по оси X месяцы жизни когорты, по оси Y — когорты по месяцу первой покупки.

**Что это даёт в 2026:** вместо споров «откуда брать новых пользователей» сразу видно — когорта июня удержалась на 22% к третьему месяцу, а когорта августа только на 14%. Дальше копаете: что изменилось в продукте, в источниках трафика, в офферах. Маркетинг в логике RevOps (общая ответственность за выручку) начинается с таких вот честных цифр, а не с last-click отчётов.

**Что делать на этой неделе:** соберите когорту за последние 6 месяцев, найдите 2-3 самых слабых и сравните с сильными по источникам, среднему чеку и категориям товаров. Ответ почти всегда находится именно там.

— @BigQuery4MarketingPro