Когортний аналіз утримання: як читати таблицю і порахувати в SQL

Середня частка повторних покупок — оманлива метрика. Якщо цього місяця прийшло багато нових клієнтів з розпродажу, вона просяде, хоча з утриманням усе гаразд. Когортний аналіз розділяє клієнтів за «датою народження» і показує, як кожна група поводиться з часом.

#Що таке когорта

Когорта — група клієнтів з однаковою стартовою подією в одному періоді. Найчастіше:

• місяць першої покупки (магазин);
• місяць першого візиту (сервісний бізнес);
• тиждень встановлення або реєстрації (застосунок, SaaS);
• місяць старту підписки (SaaS).

Додатково когорти можна ділити за першим каналом, першим товаром, містом — це найцінніше.

#Як виглядає таблиця когорт

Умовні дані: частка клієнтів, які зробили хоча б одну покупку в місяць N після першої.

#Як читати

• По рядку — як утримується конкретна когорта з часом.
• По стовпчику — чи кращає утримання для нових когорт (М1 росте з 17% до 20% — добре).
• Аномалії — червневої когорти більше, а утримання гірше. Типово для розпродажу або агресивної знижки: прийшли «мисливці за акціями».

#Види когортних метрик

Накопичений LTV — найкорисніший для власника: поставте поруч CAC, і видно, на якому місяці когорта окупилась (LTV до CAC).

#SQL: когорти утримання

Для PostgreSQL, таблиця orders(customer_id, order_date, status):

WITH first AS (
  SELECT customer_id, DATE_TRUNC('month', MIN(order_date)) AS cohort
  FROM orders WHERE status = 'paid'
  GROUP BY 1
),
activity AS (
  SELECT DISTINCT o.customer_id, f.cohort,
         (EXTRACT(YEAR FROM AGE(DATE_TRUNC('month', o.order_date), f.cohort)) * 12
        + EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', o.order_date), f.cohort)))::int AS m
  FROM orders o JOIN first f USING (customer_id)
  WHERE o.status = 'paid'
)
SELECT cohort, m,
       COUNT(*) AS active,
       ROUND(100.0 * COUNT(*) / FIRST_VALUE(COUNT(*)) OVER (PARTITION BY cohort ORDER BY m), 1) AS pct
FROM activity
GROUP BY cohort, m
ORDER BY cohort, m;

Для BigQuery замініть AGE на DATE_DIFF(month_of_order, cohort, MONTH).

#Когорти з GA4

Для поведінкових когорт (повернення на сайт, а не покупки) використовуйте user_pseudo_id і first_visit з експорту GA4 у BigQuery. Пам'ятайте: без логіна один клієнт на двох пристроях — два «користувачі», тому для грошових когорт краще брати CRM.

#Які рішення дає когортний аналіз

• Якість каналів: когорти з TikTok утримуються гірше, ніж з пошуку → враховувати це в допустимому CAC.
• Ефект акцій: клієнти з розпродажу купують рідше → обмежити глибину знижок або змінити оффер.
• Онбординг і перший досвід: якщо падіння М0→М1 різке, проблема в першому замовленні (доставка, якість, спілкування).
• Ритм повторних покупок: коли більшість других покупок відбувається на 2-му місяці — саме тоді має йти нагадування або персональна пропозиція.

#Як показувати когорти в дашборді

• теплова карта (heatmap) з однією шкалою кольору;
• перемикач метрики: клієнти / виручка / накопичений LTV;
• фільтр за першим каналом і першою категорією товару;
• лінія CAC на графіку накопиченого LTV;
• підпис розміру когорти — маленькі когорти шумні.

Для ширшої картини економіки клієнта — гайд з юніт-економіки. Для сегментації вже утриманих клієнтів — RFM-аналіз.

#Типові помилки

• Брати середнє утримання без когорт.
• Порівнювати незавершені місяці з завершеними.
• Ігнорувати розмір когорти.
• Рахувати повернення (refund) як активність.
• Будувати грошові когорти на user_pseudo_id з GA4.

#Чекліст

• Визначено стартову подію і період когорти
• Клієнт ідентифікується однаково (телефон/email/ID CRM)
• Враховано лише оплачені замовлення
• Є три режими: клієнти, виручка, накопичений LTV
• Фільтр за першим каналом
• Поточний незавершений місяць позначено

#Приклад: читаємо правдоподібний трикутник

Ілюстративна місячна таблиця утримання для магазину (частка когорти, що зробила ще одне замовлення в місяці N):

Як читати:

• Рядки показують, як поводиться одна когорта з часом. Утримання травневої когорти вирівнюється на 6–7% з M3 — це лояльні клієнти.
• Стовпці порівнюють когорти в однаковому «віці». M1 липня вдвічі нижчий за інші.
• Липневий викид: на 60% більше клієнтів, але значно нижче утримання. Це типовий слід великої акції чи широкої кампанії залучення, що привела одноразових «мисливців за знижками». Врахуйте це в рішеннях щодо CAC: вартість утриманого клієнта з тієї кампанії була значно вищою, ніж показував CAC.
• Серпень і вересень у тому ж віці кращі за травень — щось покращилось. З'ясуйте, що змінилося (ланцюжок листів після покупки, нова лінійка, кращий таргетинг), і збережіть це.

#Накопичена виручка на клієнта: погляд на LTV

Утримання показує, чи повертаються клієнти. Накопичена виручка на клієнта показує, скільки вони варті. Той самий ілюстративний магазин:

Якщо травневі клієнти за пів року досягають ~1 690 грн виручки, а маржинальність — близько 30%, кожен вартий приблизно 507 грн маржі за цей період. Це верхня межа CAC для схожого залучення ще до будь-якого прибутку. Липневі клієнти з 1 150 грн за 3 місяці й вирівнюванням виправдовують значно нижчий CAC. Ці числа прямо йдуть у рішення щодо CAC і LTV.

#SQL: накопичена виручка на клієнта за когортами

WITH first AS (
  SELECT customer_id, DATE_TRUNC(MIN(order_date), MONTH) AS cohort
  FROM clean.orders WHERE status = 'paid' GROUP BY 1
),
rev AS (
  SELECT f.cohort,
         DATE_DIFF(DATE_TRUNC(o.order_date, MONTH), f.cohort, MONTH) AS m,
         SUM(o.revenue_net) AS revenue
  FROM clean.orders o JOIN first f USING (customer_id)
  WHERE o.status = 'paid'
  GROUP BY 1, 2
),
size AS (SELECT cohort, COUNT(*) AS customers FROM first GROUP BY 1)
SELECT r.cohort, r.m, s.customers,
       SUM(r.revenue) OVER (PARTITION BY r.cohort ORDER BY r.m) / s.customers AS cum_revenue_per_customer
FROM rev r JOIN size s USING (cohort)
ORDER BY r.cohort, r.m;

Два застереження. Не показуйте клітинки за місяці, що ще не завершилися, — поточний місяць завжди виглядає як провал. І когорта менша за приблизно сотню клієнтів дає відсотки, які «стрибають» через кількох людей, тож об'єднуйте малі місяці в квартали.

#Когорти за каналом залучення

Найкорисніший розріз — когорта × канал першого замовлення. Ілюстративні частки повторних покупок за 90 днів:

Якщо платні соцмережі дають 40% нових клієнтів, але ті повертаються вдвічі рідше, «дешевий CAC» цього каналу не такий уже й дешевий. Клієнти з маркетплейсу часто належать маркетплейсу, а не вам, — це варто знати, перш ніж вкладатися в його зростання.

#Тижневі чи місячні когорти?

Період когорти обирайте за тим, як часто купують клієнти:

Занадто дрібний період дає шумні, розріджені таблиці. Занадто великий — ховає ефект кампаній і змін, які ви хочете оцінити. Якщо сумніваєтеся, почніть з місячних когорт і змініть, якщо таблиця переважно порожня або всюди однакова.

Ще одна порада: для бізнесів із сильною сезонністю порівнюйте когорти не з попереднім місяцем, а з тим самим місяцем минулого року. Грудневі клієнти, що прийшли за подарунками, майже завжди повертаються рідше за вересневих, і це не означає, що щось зламалося.

#Як презентувати когорти команді

Трикутник когорт лякає тих, хто бачить його вперше. Почніть з одного рядка: «з клієнтів, що прийшли в травні, через три місяці повертаються 8%». Потім покажіть, що в серпні це вже 10%, і поясніть, що змінилося. Коли команда звикне читати один рядок, повна таблиця стає зрозумілою сама собою.

#Часті запитання

#Скільки даних потрібно для когортного аналізу?

Корисну картину дають 6+ місяців історії й когорти від кількох сотень клієнтів. На менших обсягах дивіться квартальні когорти.

#Що вважати «нормальним» утриманням?

Порівнюйте з власною історією і між каналами, а не з чужими бенчмарками — вони сильно залежать від категорії і частоти покупок.

#Як ідентифікувати клієнта без реєстрації?

За телефоном або email із замовлення (у нормалізованому вигляді). Для більшості українських магазинів телефон — найнадійніший ключ.

#Чи можна будувати когорти в Google Таблицях?

Так, через зведені таблиці на невеликих обсягах. На тисячах замовлень і з щотижневим оновленням краще перенести розрахунок у SQL.