🔒Открыть пост
и посмотреть медиа
и посмотреть медиа
Как на SQL найти постоянных клиентов, которые уже ушли
Предположим, есть таблица с заказами, в которой нас интересуют лишь поля:
▶️client_id - id клиента
▶️order_date - дата заказа
И нужно найти тех клиентов, которые не совершали покупок более 90 дней, НО которые при этом раньше покупали регулярно (например, минимум 1 раз каждые 7 дней, суммарно не менее 5 покупок). С маркетинговой точки зрения такая выборка покажет, какие из наиболее лояльных и постоянных клиентов вдруг потеряли полностью интерес к нашей продукции
🟠Сделать такую выборку можно на SQL, используя СТЕ и оконные функции.
1️⃣Начать запрос можно с того, чтобы вывести с помощью оконных функций самую последнюю order_date и предыдущую от текущей order_date для каждого клиента, а также посчитать общее кол-во покупок у каждого клиента:
WITH client_orders AS (
SELECT
client_id,
order_date,
MAX(order_date) OVER (PARTITION BY client_id)
AS last_order_date,
LAG(order_date) OVER (PARTITION BY client_id
ORDER BY order_date)
AS prev_order_date,
COUNT(*) OVER (PARTITION BY client_id)
AS total_orders
FROM orders
),
2️⃣Затем можно сразу отдельно исключить тех клиентов, у которых есть хотя бы 1 перерыв между двумя заказами более 7 дней. Для этого используем разницу между текущей датой заказа и предыдущей, полученной с помощью LAG в шаге 1️⃣:
lag_orders as (
SELECT DISTINCT client_id
FROM client_orders
WHERE DATEDIFF
(order_date, prev_order_date) > 7
)
3️⃣И в конце выводим тех клиентов, у которых не менее 5 покупок и которые не совершали заказов более 90 дней, и при этом всём которые не пересекаются с client_id клиентов, выведенных в шаге 2️⃣:
SELECT DISTINCT client_id
FROM client_orders
WHERE total_orders >= 5
AND DATEDIFF(CURDATE(),
last_order_date) > 90
AND client_id NOT IN
(SELECT * FROM lag_orders)
Результат получится в виде уникальных client_id. Это как раз и будут искомые клиенты.