Lära Customer Segmentation (RFM-style Logic)

Avsnitt 1. Kapitel 7

single

Svep för att visa menyn

Customer segmentation is a powerful analytical technique that helps businesses understand their customer base for more targeted marketing and personalized offers. One of the most widely used segmentation frameworks is RFM analysis, which stands for Recency, Frequency, and Monetary value. RFM segmentation groups customers based on how recently they made a purchase, how often they purchase, and how much they spend. Each of these metrics provides unique insights: recency highlights how recently a customer has engaged, frequency shows how often they return, and monetary value reveals their overall value to the business. By calculating these metrics for each customer, you can classify them into segments such as "Champions," "Loyal Customers," or "At Risk," and tailor marketing strategies accordingly. This approach is particularly valuable for e-commerce and subscription businesses that aim to maximize customer lifetime value and retention.

Definition

Recency: The number of days since a customer's most recent purchase;
Frequency: The total number of purchases a customer has made in a given period;
Monetary value: The total amount a customer has spent in that period.


              12345678910111213141516171819
            
-- Calculate RFM metrics for each customer in the e-commerce dataset
SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  -- Recency: days since latest order
  DATE_PART('day', CURRENT_DATE - MAX(o.order_date)) AS recency,
  -- Frequency: number of orders
  COUNT(o.order_id) AS frequency,
  -- Monetary: total spent
  SUM(o.total) AS monetary
FROM
  customers c
JOIN
  orders o ON c.customer_id = o.customer_id
GROUP BY
  c.customer_id, c.first_name, c.last_name
ORDER BY
  monetary DESC;

The e-commerce team wants to identify high-value customers for a loyalty program. Using RFM segmentation, you can classify customers and select those who not only purchase frequently, but also spend the most, ensuring that your loyalty offers reach the most valuable audience.


              12345678910111213141516171819202122232425262728293031
            
-- Assign RFM segments based on metric thresholds
SELECT
  customer_id,
  first_name,
  last_name,
  recency,
  frequency,
  monetary,
  CASE
    WHEN recency <= 30 AND frequency >= 3 AND monetary >= 1000 THEN 'Champion'
    WHEN recency <= 60 AND frequency >= 2 AND monetary >= 500 THEN 'Loyal'
    WHEN recency > 90 THEN 'At Risk'
    ELSE 'Regular'
  END AS rfm_segment
FROM (
  SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    DATE_PART('day', CURRENT_DATE - MAX(o.order_date)) AS recency,
    COUNT(o.order_id) AS frequency,
    SUM(o.total) AS monetary
  FROM
    customers c
  JOIN
    orders o ON c.customer_id = o.customer_id
  GROUP BY
    c.customer_id, c.first_name, c.last_name
) rfm
ORDER BY
  rfm_segment, monetary DESC;

Uppgift

Svep för att börja koda

List all customers who have both a high purchase frequency and high total spending.

Select customers from the customers table who have placed two or more orders.
Only include those whose total spending is at least 1000.
Display each customer's ID, first name, last name, their total order count as frequency, and their total spending as monetary.
Order the results from highest to lowest monetary value.

Lösning

Byt till skrivbordet för praktisk övningFortsätt där du är med ett av alternativen nedan

Var allt tydligt?

Tack för dina kommentarer!

Avsnitt 1. Kapitel 7

single

Fråga AI

Fråga vad du vill eller prova någon av de föreslagna frågorna för att starta vårt samtal