BigQuery для маркетологов

Как собрать в BigQuery отчёт по удержанию клиентов за 30 минут

Если у вас в маркетинге ставка смещается с первой покупки на LTV и повторные продажи, удержание нужно считать не в таблицах вручную, а прямо в BigQuery. Ниже — рабочая схема, которую можно сделать за одну неделю.

1. Определите 3 сущности: `customer_id`, дата первой покупки, дата каждой следующей покупки.
2. Соберите единый факт заказов в одну таблицу: `orders` с полями `customer_id`, `order_id`, `order_date`, `revenue`.
3. В BigQuery создайте базовый запрос первой покупки:

```sql
WITH first_order AS (
SELECT
customer_id,
MIN(order_date) AS first_order_date
FROM `project.dataset.orders`
GROUP BY customer_id
)
```

4. Добавьте к каждому заказу возраст клиента в днях от первой покупки:

```sql
SELECT
o.customer_id,
o.order_date,
DATE_DIFF(o.order_date, f.first_order_date, DAY) AS days_from_first
FROM `project.dataset.orders` o
JOIN first_order f USING (customer_id)
```

5. Постройте когортную таблицу по месяцам первой покупки и повторным визитам:

```sql
SELECT
DATE_TRUNC(f.first_order_date, MONTH) AS cohort_month,
DATE_DIFF(DATE_TRUNC(o.order_date, MONTH), DATE_TRUNC(f.first_order_date, MONTH), MONTH) AS cohort_age,
COUNT(DISTINCT o.customer_id) AS active_customers
FROM `project.dataset.orders` o
JOIN first_order f USING (customer_id)
GROUP BY 1, 2
```

6. Для расчёта удержания добавьте размер когорты и делите активных клиентов на него.

Что получится на выходе:
— retention по месяцам жизни клиента;
— сравнение когорт по каналам привлечения;
— поиск провалов: где первый заказ есть, а повторного нет.

**Практика на этой неделе:**
— подключите 1 источник заказов;
— соберите 1 когортный запрос;
— выведите 3 когорты по последним месяцам;
— сравните retention по каналам и предложите 1 гипотезу для теста.

Такой отчёт уже помогает маркетингу и RevOps видеть не только трафик, но и выручку после первой сделки.

— @BigQuery4MarketingPro
Этот пост опубликован в Telegram-канале BigQuery для маркетологов (@BigQuery4MarketingPro). Все его публикации — на странице канала.
Открыть в Telegram

Свежее в разделе «Сервисы и инфраструктура»

Все каналы раздела →

реклама в сети

Готовы запустить рекламу через сеть public.tg?

Новый оффер, продукт, GEO, кейс, событие или партнёрский запуск — соберём маршрут под задачу и отдадим медиаплан.

Telegram для медиаплана: @AFFtop_connect. Быстрый тест: $20 за канал, $1000 за пакет по сети.