Customer Migration Analysis

[_Ester_]

Hello!

First off, thank you in advance to anyone who can assist; I've been wrestling with this issue for days and can't seem to find a solution.

I'm working in Power BI Desktop with a dataset named "Sales" that records customer transactions. Each row in this dataset represents a unique transaction and has the following columns:
- date: The date of the transaction (note that multiple transactions can occur on the same date).
- customer_id: Identifies a unique customer. A single customer_id may appear multiple times if that customer has made multiple transactions.
- total_sales: The amount spent in each transaction.

Here's what I'm trying to achieve:

1) Assign each unique customer a category based on the number of purchases they've made up to that date.
2) Categorize each customer based on the number of months since their last transaction.
3) Categorize each customer based on the total amount they have spent up to that date.

Additionally, I want to create a visual that shows how many customers have moved from one category to another, as a percentage of the total number of customers up to that moment.
It could be something like that:

For example, if I'm looking at the month of March in the visual, I want to see the percentage of customers that moved from one segment to another compared to the total number of customers I had up until March.

IMPORTANT: The "Sales" dataset updates automatically, so I need the visual to update dynamically as well.

Any guidance or suggestions on how to set up these categories dynamically and create the required visual would be greatly appreciated!

Here a PBIX example: https://drive.google.com/drive/folders/1_9VDmudNyqEUdSpVIdMI9OgVA0E-2QsH?usp=sharing

Thank you for your help!

[Anonymous]

Hi, @_Ester_ 

Based on your description, failing to understand the logic of achieving the goal
1. what is the categorization criteria for the number of purchases
2. what are the criteria for categorizing the number of months since the last transaction
3. still the same problem, the categorization criteria are not clear

Best Regards,
Yang
Community Support Team

[_Ester_]

Hello @Anonymous , Thank you for responding!

Context: 
I am trying to segment customers based on an RFM analysis (Recency, Frequency, Monetary - see below) and then understand how they move from one category to another on a month-by-month basis (customer migration analysis) to determine if my marketing strategies are effective.

Here are the RFM categories:
1. Assign each unique customer a category based on the number of purchases they've made up to that date.

Here the Frequency categories:
Number of purchases up to the current date <= 1: "NEW"
Number of purchases up to the current date between 2 and 3: "OCCASIONAL"
Number of purchases up to the current date between 4 and 7: "FREQUENT"
Number of purchases up to the current date > 7: "LOYAL"

2. Categorize each customer based on the number of months since their last transaction.

Here the Recency categories:
Months passed since the previous transaction (up to the current date) <= 1 month ago, then: "HOT"
Months passed since the previous transaction (up to the current date) from 1 to 6 months ago, then: "RECENT"
Months passed since the previous transaction (up to the current date) from 6 to 12 months ago, then: "INACTIVE"
Months passed since the previous transaction (up to the current date) > 12 months ago, then: "HIBERNATING"

3. Categorize each customer based on the total amount they have spent up to that date.

Here the Monetary categories:
Total sales up to the current date <=50 €, then: "COPPER",
Total sales up to the current date between 50 and 100 €, then: "BRONZE",
Total sales up to the current date between 100 and 200 €, then: "SILVER",
Total sales up to the current date >200 €, then: "GOLD"

Issues

I have no issues segmenting customers based on their current status; the real challenge arises when I want to conduct a customer migration analysis to find out HOW MANY customers move from one segment to another over time.

I believe I should have a calculated table with all combinations of date (all dates of the period) and customer_id, and include in the third, fourth, and fifth columns the calculation of the RFM categories (as if they were running categories).
However, I can't do this because it would affect the performance of the report.

I've tried countless solutions, but I still can't find one that works.
Thank you for your help!