Если у вас в маркетинге ставка смещается с первой покупки на 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
BigQuery для маркетологов
@BigQuery4MarketingPro
Как собрать в BigQuery отчёт по удержанию клиентов за 30 минут
Этот пост опубликован в Telegram-канале BigQuery для маркетологов (@BigQuery4MarketingPro). Все его публикации — на странице канала.
Открыть в Telegram