第一步:了解数据模型
对于此分析,我们假设拥有如下数据库:
customers
:客户信息表。orders
:订单表。payments
:付款交易表。
-- 识别不活跃客户(过去 90 天内未下订单)
WITH inactive_customers AS (
SELECT
customer_id
FROM
customers
WHERE
customer_id NOT IN (
SELECT DISTINCT customer_id
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '90' DAY
)
)
-- 计算流失率
SELECT
COUNT(*) AS churned_customers,
COUNT(*) * 100.0 / (SELECT COUNT(*) FROM customers) AS churn_rate
FROM
inactive_customers;
第三步:分析客户流失模式
--根据订单状态确定流失的常见原因
SELECT
order_status,
COUNT(*) AS count
FROM
orders
WHERE
customer_id IN (SELECT customer_id FROM inactive_customers)
GROUP BY
order_status;
3.2 按支付方式分析流失率
--根据支付方式分析流失率
SELECT
payment_method,
COUNT(*) AS count
FROM
payments
WHERE
customer_id IN (SELECT customer_id FROM inactive_customers)
GROUP BY
payment_method;
--按购买频率细分客户
SELECT
CASE
WHEN order_count = 1 THEN 'One-Time Purchasers'
WHEN order_count = 2 THEN 'Occasional Buyers'
WHEN order_count <= 5 THEN 'Regular Customers'
ELSE 'Loyal Customers'
END AS customer_segment,
COUNT(*) AS customer_count
FROM (
SELECT
customer_id,
COUNT(DISTINCT order_id) AS order_count
FROM
orders
GROUP BY
customer_id
) AS customer_orders
GROUP BY
customer_segment;
三、结论:
使用 SQL 进行流失分析为客户保留策略的企业提供了可行的见解。通过 SQL 查询,可以计算客户流失率、确定客户流失的常见原因,并根据客户的行为对客户进行细分。这样,就可以制定有针对性的策略来留住客户并培养长期关系。