Отток клиентов в SQL: кто перестал покупать без подписки?

Четверо не купили ничего в сентябре. Но условный отток — только двое. Разберём дату среза, границу 60 дней и кого ещё рано оценивать.

SQL Studio / Разборы SQL / Кто ушёл из магазина, если «отменить подписку» нельзя

Руководитель видит четырёх покупателей без заказов в сентябре и объявляет: «Ушли четверо». По заранее выбранному правилу 60 полных дней без доставленной покупки к срезу 1 октября 2026 года 00:00 UTC условно ушли только двое. Для магазина без подписки нет наблюдаемого события «отписался»; SQL может обнаружить длительную паузу, но не доказать окончательный уход. Дату среза и правило нужно показывать рядом с числом.

Вход — синтетические доставленные заказы, отмены и один заказ после среза. Берём заказы с датой строго < '2026-10-01'. Кандидат в покупатели появляется только после первой доставки. Важный вопрос: клиент, впервые купивший 15 сентября, ещё не имел 60 дней наблюдения.

Соблазнительная ошибка: «не покупал в сентябре»

Этот запрос честно находит клиентов, чья последняя доставка пришлась до 1 сентября. Однако ответ «ушёл» он подменяет ответом «не заказывал за один календарный месяц».

WITH purchases(customer_id, order_day, status) AS (
  VALUES (1,'2026-05-10','delivered'), (1,'2026-07-01','delivered'),
         (2,'2026-06-01','delivered'), (2,'2026-08-02','delivered'),
         (3,'2026-05-20','delivered'), (3,'2026-08-03','delivered'),
         (3,'2026-09-25','cancelled'),
         (4,'2026-09-15','delivered'),
         (5,'2026-05-01','delivered'), (5,'2026-09-25','delivered'),
         (6,'2026-08-15','delivered'),
         (7,'2026-09-10','cancelled'),
         (1,'2026-10-02','delivered')
),
buyers AS (
  SELECT customer_id, MIN(order_day) AS first_day, MAX(order_day) AS last_day
  FROM purchases
  WHERE status = 'delivered' AND order_day < '2026-10-01'
  GROUP BY customer_id
)
SELECT customer_id
FROM buyers
WHERE last_day < '2026-09-01'
ORDER BY customer_id;
Последняя доставка до сентября
Клиент
1
2
3
6

Здесь четыре ID: 1, 2, 3 и 6. У клиента №3 прошло 59 дней — он ещё не достиг нашего порога; №6 впервые купил 15 августа, так что окно потенциального 60-дневного молчания для него пока не закрылось. Это не клиенты с одинаковым статусом.

Считаем паузу от последней доставки

Сначала каждому покупателю определяем первую и последнюю доставки. Для порога сравниваем julianday() у двух дат без часового пояса, а не DATE_TRUNC из PostgreSQL. Правило примера: если с первой покупки прошло меньше 60 дней, метку «отток» ещё не присваиваем; иначе 60 дней от последней доставки достаточно для условного churn.

WITH purchases(customer_id, order_day, status) AS (
  VALUES (1,'2026-05-10','delivered'), (1,'2026-07-01','delivered'),
         (2,'2026-06-01','delivered'), (2,'2026-08-02','delivered'),
         (3,'2026-05-20','delivered'), (3,'2026-08-03','delivered'),
         (3,'2026-09-25','cancelled'),
         (4,'2026-09-15','delivered'),
         (5,'2026-05-01','delivered'), (5,'2026-09-25','delivered'),
         (6,'2026-08-15','delivered'),
         (7,'2026-09-10','cancelled'),
         (1,'2026-10-02','delivered')
),
buyers AS (
  SELECT customer_id, MIN(order_day) AS first_day, MAX(order_day) AS last_day
  FROM purchases
  WHERE status = 'delivered' AND order_day < '2026-10-01'
  GROUP BY customer_id
)
SELECT customer_id, first_day, last_day,
  CAST(julianday('2026-10-01')-julianday(last_day) AS INTEGER) AS silent_days,
  CASE
    WHEN julianday('2026-10-01')-julianday(first_day) < 60 THEN 'мало наблюдений'
    WHEN julianday('2026-10-01')-julianday(last_day) >= 60 THEN 'условный отток'
    ELSE 'ещё активен'
  END AS status_60d
FROM buyers ORDER BY customer_id;
Шесть покупателей к срезу
КлиентПерваяПоследняяБез доставки, днейМетка
12026-05-102026-07-0192условный отток
22026-06-012026-08-0260условный отток
32026-05-202026-08-0359ещё активен
42026-09-152026-09-1516мало наблюдений
52026-05-012026-09-256ещё активен
62026-08-152026-08-1547мало наблюдений

№1 молчит 92 дня, №2 — ровно 60; оба удовлетворяют правилу. №3 молчит 59, №5 — 6, оба ещё активны по этому правилу. №4 и №6 молоды: их текущая пауза не позволяет наблюдать полные 60 дней с момента первой доставки. Клиент №7 с единственной отменой не покупатель; доставка №1 от 2 октября находится за датой среза и не должна «лечить» прежнюю историю.

Знаменатель и правое цензурирование

Если просто разделить два «ушедших» на шесть покупателей, получится 33,3%, но в знаменателе окажутся два человека, которые физически не могли наблюдаться 60 дней. В этом учебном отчёте берём только четверых с 60 и более днями истории покупок. Это доля покупателей старшей наблюдаемой группы с текущей паузой ≥60 дней, а не коэффициент оттока за сентябрь или доказательство потери покупателя.

WITH purchases(customer_id, order_day, status) AS (
  VALUES (1,'2026-05-10','delivered'), (1,'2026-07-01','delivered'),
         (2,'2026-06-01','delivered'), (2,'2026-08-02','delivered'),
         (3,'2026-05-20','delivered'), (3,'2026-08-03','delivered'),
         (3,'2026-09-25','cancelled'),
         (4,'2026-09-15','delivered'),
         (5,'2026-05-01','delivered'), (5,'2026-09-25','delivered'),
         (6,'2026-08-15','delivered'),
         (7,'2026-09-10','cancelled'),
         (1,'2026-10-02','delivered')
),
buyers AS (
  SELECT customer_id, MIN(order_day) AS first_day, MAX(order_day) AS last_day
  FROM purchases
  WHERE status = 'delivered' AND order_day < '2026-10-01'
  GROUP BY customer_id
),
classified AS (
  SELECT customer_id,
    CASE
      WHEN julianday('2026-10-01')-julianday(first_day) < 60 THEN 'censored_new'
      WHEN julianday('2026-10-01')-julianday(last_day) >= 60 THEN 'churned'
      ELSE 'not_churned_yet'
    END AS state
  FROM buyers
)
SELECT COUNT(*) AS buyers,
       SUM(state = 'censored_new') AS censored_new,
       SUM(state = 'churned') AS churned,
       SUM(state = 'not_churned_yet') AS not_churned_yet,
       ROUND(100.0 * SUM(state = 'churned') /
             NULLIF(SUM(state <> 'censored_new'), 0), 1) AS churn_share_eligible_pct
FROM classified;
Сводка по правилу 60 дней
ВсегоНовички: недостаточно окнаУсловно ушлиПока активныДоля среди 4, %
622250.0

Числа: 2 из 4 = 50,0% для выбранной наблюдаемой группы. Назовите этот показатель в отчёте «доля с паузой 60+ дней», не подписывайте его как месячный churn rate или вероятность никогда не вернуться. Клиенты без нового заказа к срезу правосторонне цензурированы относительно будущего возврата: мы не знаем, купят ли они позже. Новички №4 и №6 ещё и не имеют полного 60-дневного окна для заданной метки.

Один день решает границу

У клиента №2 последняя доставка была 2 августа; у №3 — 3 августа. На 1 октября, исключая сам день среза, это ровно 60 и 59 дней. Здесь >= 60 принципиально, а «больше 60» вернуло бы другой результат.

WITH purchases(customer_id, order_day, status) AS (
  VALUES (1,'2026-05-10','delivered'), (1,'2026-07-01','delivered'),
         (2,'2026-06-01','delivered'), (2,'2026-08-02','delivered'),
         (3,'2026-05-20','delivered'), (3,'2026-08-03','delivered'),
         (3,'2026-09-25','cancelled'),
         (4,'2026-09-15','delivered'),
         (5,'2026-05-01','delivered'), (5,'2026-09-25','delivered'),
         (6,'2026-08-15','delivered'),
         (7,'2026-09-10','cancelled'),
         (1,'2026-10-02','delivered')
),
buyers AS (
  SELECT customer_id, MIN(order_day) AS first_day, MAX(order_day) AS last_day
  FROM purchases
  WHERE status = 'delivered' AND order_day < '2026-10-01'
  GROUP BY customer_id
)
SELECT customer_id, last_day,
       CAST(julianday('2026-10-01')-julianday(last_day) AS INTEGER) AS silent_days,
       CASE WHEN last_day <= date('2026-10-01','-60 days') THEN 1 ELSE 0 END AS inactivity_60d
FROM buyers
WHERE customer_id IN (2,3)
ORDER BY customer_id;
Граница классификации
КлиентПоследняяДнейПауза ≥60
22026-08-02601
32026-08-03590

Мини-эксперимент

Что произойдёт с меткой №3, если передвинуть дату среза на 2 октября, сохранив всё остальное? А что изменится, если ошибочно засчитать его отмену от 25 сентября как покупку? Сверьте ответы в SQL-песочнице. Для соседнего вопроса о давности и частоте заказов полезен RFM-разбор; для доли покупателей с повтором — повторные покупки; сравнение когорт — в retention M1/M2.

Для начала проверьте аудиторию в задаче P070 «Зарегистрированные без доставок». Это не расчёт оттока за 60 дней: он дополнительно требует истории заказов, даты среза и порога неактивности.


Для задач на MySQL 8 вместо SQLite — отдельный курс на Stepik. Синтаксис дат и оконных функций у движков может отличаться; запросы этой статьи проверены именно в SQLite.