General Steps for RFM Segmentation Utilizing Tertiles

Data Preparation: - Data Collection: Accumulate transaction records that include customer IDs, transaction dates, and amounts. - Data Cleaning: Ensure data integrity by eliminating duplicates, fixing inaccuracies, and addressing missing entries.

Calculating RFM Metrics: - Recency (R): Measure the days elapsed since a customer's last purchase. - Frequency (F): Count the total number of purchases by the customer. - Monetary (M): Calculate the total expenditure of the customer.

Assigning RFM Scores: - Score Calculation: Determine scores for each RFM metric by segmenting customers into tertiles, where higher scores indicate superior performance.

• Recency: More recent purchases receive higher scores.
• Frequency: More frequent purchases yield higher scores.
• Monetary: Greater spending results in higher scores.

Combining RFM Scores: - Merge the individual RFM scores to form a composite RFM score, typically by concatenating the three metrics (e.g., R = 3, F = 2, M = 1 results in a combined score of 321).

Segmenting Customers: - Categorize customers based on their overall RFM scores. For instance, the highest scores (333) may denote "Champions," while lower scores could indicate less engaged customers.

RFM Implementation in Power BI

Creating an RFM Table: - RFM Table: Construct a table where each customer has a single row. This could include fields such as Customer ID, Email, or another unique identifier.

RFM Table =

CALCULATETABLE(

VALUES(order_v[Email]),

FILTER(order_v,

order_v[Email] <> BLANK()

)

)

Calculating RFM Metrics: - Recency (R): Retrieve the date of the last order for each specific customer to compute the days since that date.

Recency =

VAR last_order_date =

MAXX(

FILTER(order_v,

order_v[Email] = 'RFM Table'[Email]

),

order_v[Order Date]

)

VAR output = DATEDIFF(last_order_date, TODAY(), DAY)

RETURN

output

• Frequency (F): Perform a distinct count of the order ID or the unique identifier in your dataset.

  Frequency =

  CALCULATE(

  DISTINCTCOUNT(order_v[Order ID]),

  FILTER(order_v,

  order_v[Email] = 'RFM Table'[Email]

  )

  )

• Monetary (M): Sum up the net revenue per customer. You can opt for net revenue or any other type of revenue (gross, etc.).

  Monetary =

  SUMX(

  FILTER(order_v,

  order_v[Email] = 'RFM Table'[Email]

  ),

  order_v[Net Revenue]

  )

Assigning RFM Scores: - Assign scores for each RFM metric by dividing customers into tertiles, where higher scores indicate better performance.

• Recency: The lower the value, the better, so assign a score of 3 to customers in the lowest third.

  R Score =

  VAR one = PERCENTILE.INC('RFM Table'[Recency], 1/3)

  VAR two = PERCENTILE.INC('RFM Table'[Recency], 2/3)

  VAR output =

  IF('RFM Table'[Recency] < one,

  3,

  IF('RFM Table'[Recency] >= one && 'RFM Table'[Recency] < two,

  2,

  1

  )

  )

  RETURN

  output

• Frequency: The higher the value, the better, so assign a score of 3 to customers in the highest third.

  F Score =

  VAR one = PERCENTILE.INC('RFM Table'[Frequency], 1/3)

  VAR two = PERCENTILE.INC('RFM Table'[Frequency], 2/3)

  VAR output =

  IF('RFM Table'[Frequency] < one,

  1,

  IF('RFM Table'[Frequency] >= one && 'RFM Table'[Frequency] < two,

  2,

  3

  )

  )

  RETURN

  output

• Monetary: Similarly, higher amounts result in higher scores.

  M Score =

  VAR one = PERCENTILE.INC('RFM Table'[Monetary], 1/3)

  VAR two = PERCENTILE.INC('RFM Table'[Monetary], 2/3)

  VAR output =

  IF('RFM Table'[Monetary] < one,

  1,

  IF('RFM Table'[Monetary] >= one && 'RFM Table'[Monetary] < two,

  2,

  3

  )

  )

  RETURN

  output

Combining RFM Scores: - Derive the overall RFM Score by concatenating the three individual scores in the format “R - F - M”.

RFM Score =

'RFM Table'[R Score] & " - " & 'RFM Table'[F Score] & " - " & 'RFM Table'[M Score]

RFM Table: This is how your RFM Table should appear.

Note that additional columns like First Category and First Product have been incorporated for filtering purposes in the report.

Segmenting Customers: - Create an RFM Lookup Table: This will not only store each RFM label but also define sorting preferences for display in visuals and include actionable steps for each RFM label.

RFM Lookup =

CALCULATETABLE(

VALUES('RFM Table'[RFM Score]),

'RFM Table'[RFM Score] <> BLANK()

)

• Define RFM Labels: I opted for a granular approach, resulting in 27 labels (this is why tertiles were used instead of quintiles).

  RFM Label =

  SWITCH('RFM Lookup'[RFM Score],

  "1 - 1 - 1", "Frozen - Few Orders - Cheap",

  ...

  "3 - 3 - 3", "Recent - Multi Orders - Expensive"

  )

• Set up an Order column: This column aids in logically sorting the RFM Label column for reporting.

  Order =

  SWITCH('RFM Lookup'[RFM Score],

  "1 - 1 - 1", 1,

  ...

  "3 - 3 - 3", 27

  )

RFM Table: This is how your RFM Lookup Table should look.

Having established both the RFM Table and the RFM Lookup, connect them using the RFM Score columns to enable visualizations in Power BI, which will facilitate customer behavior analysis and valuable insights.

Benefits and Applications of RFM Segmentation

Personalized Marketing: - Customize marketing efforts for various customer segments. For instance, extend special offers to “Recent — Multi Orders — Expensive” customers as a loyalty incentive or initiate re-engagement campaigns for “At Risk” customers.

Customer Retention: - Identify and focus on at-risk customers with targeted retention strategies to minimize churn.

Resource Allocation: - Optimize marketing resource distribution by concentrating on high-value segments.

Enhanced Customer Insights: - Acquire deeper understanding of customer behaviors and preferences, leading to more informed decision-making.

If you found this guide helpful, please consider supporting me by:

1. Following me on Medium.
2. Liking this article.
3. Sharing it with anyone who might benefit.
4. Subscribing to my articles for updates on future posts.

Let’s Stay Connected

Got questions or comments you’d like to discuss?

Here’s my LinkedIn profile.

Look forward to more engaging Marketing Analytics articles coming your way!

Until next time, Tom