Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

RFM Model - How to create a Cluster Record to visualize evolutiong throught time?

Hello Everyone!!

I created some sort of a Cluster usind the RFM Model (Recency, Frequency, Monetary) directly in a calculed collumn, so with that I can have the name of the cluster for each one of my clients. (Champions, Loyal Costumers, and others).

But the problem is that I don't have any record of that. Everyday the clients can change their cluster but their previous cluster does not became a record so I can see the evolution throught time.

With this model I can assign different actions to different clusters but if I can't keep on tracking the increase or decrease (just an example) of the Champions or Loyal Costumers, I will only be doing actions without knowing if it is working or not.

Does anyone have a suggestion? As I mentioned, I created all the parameters and using IF conditional I created the clusters in a calculed column.

Thank you very much in advance!

10 REPLIES 10
MFelix
Super User
Super User

Hi @Anonymous ,

 

How is the granularity of the information are you getting data at a daily basis, or every refresh overlays the data?

 

If you have daily information for the calculation you will need to make a measure to calculate the values within each Cluster based on the daily values, if you are getting information that is ovverwritten every single day, you need to have a way of getting the daily information.

 

Can you share some setup and mockup data?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Yes @MFelix 

every refresh overlays the data =(

Hi @Anonymous ,

 

Do you want to calculate this based on the update date of the information or on the order date?

 

I have prepared a setup based on a date dimension table and the difference to the selected value of the orders. 

 

So basically just replace your TODAY() from your days without orders to a MAXIMUM date, and all the formulas are with measures.

 

I will send the explanation and file between today and tomorrow.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@MFelix 

I want to calculate based on the update date, for example: How many of each clusters I had on December 1st, how many on December 2nd, throught every day. Also would be nice to see how many of each clusters through the months:

How many cluster when I was in 31 October, 30 November, and so on...

Did you get it?

Thank you very much!!

Hi @Anonymous ,

 

You don't have an updated date on your file. So you are not saving daily information, you refer that you overwritte the data so without that date this is not possible to do.

 

Regarding month by month I have everythi  prepared.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @Anonymous ,

 

I created the following measures:

 

Last Order Date = MAX(Orders[created_at])

Days Without Order Number = DATEDIFF([Last Order Date];MAX(DimDate[Date]);DAY)

Recency Note value = 
SWITCH (
    TRUE ();
    [Days Without Order Number] < 7
        && [Last Order Date] <> BLANK (); 5;
    [Days Without Order Number] >= 7
        && [Days Without Order Number] < 14; 4;
    [Days Without Order Number] >= 14
        && [Days Without Order Number] < 30; 3;
    [Days Without Order Number] >= 30
        && [Days Without Order Number] < 60; 2;
    [Days Without Order Number] >= 60
        && [Days Without Order Number] < 90; 1;
    0
)

 

Also created a dimension table with dates and another one with the clusters:

IDCluster

5 Champions
4 Loyal
3 Potencial Clientes
2 Need Attention
1 Almost Lost
0 Lost

 

 

Now add the following measure:

Cluster_Count =
VAR temptable =
    SUMMARIZE ( Clients; Clients[id]; "Recency"; [Recency Note value] )
RETURN
    IF (
        MAX ( DimDate[Date] ) <= MIN ( Orders[created_at] );
        BLANK ();
        CALCULATE (
            COUNTROWS ( FILTER ( temptable; [Recency] IN VALUES ( 'Cluster'[ID] ) ) )
        )
    )

 

See result below:

 

 

clusters.png

The two slicers on the bottom are for the order dates and for the date dimension  table that will allow to have filters. these two slicers are sincronized with each other.

 

Check file attach, and if you need any further assistance please tell me.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hey @MFelix 

Thank you very much for your help, I sent a mock up file just to understand the solution, but actually there is others parameters to identify a cluster. I just did not understand the Cluster Count part, I am sharing another power bi file with you, with all the parameters that actually counts. 

Just for you to follow, I have the recency note, the frequency note and other parameters like (first date order, amout of orders) that I used IF Conditional to create each one of the clusters inside the Clients Table.

Would you mind to have a look at this new file, it has the same idea, but it is a bit more complex.

Thank you very much in advance.

I saw that you come from Portugal, this other file is in Portuguese, because actually this file comes from Brazil haha

Please let me know if you can understand the tables, the measures and the calculated columns

https://drive.google.com/file/d/1oTaTjwouSF46nYiAxzeq3L5ueiirrgzI/view?usp=sharing 

Hi @Anonymous ,

 

Big file, I will have a look at it and will get back to you ASAP.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@MFelix thank you very much!!

I think it will be a bit harder to come up with a solution because there are actually too many variables to determinate all the clusters

Anonymous
Not applicable

Hey @MFelix !!

Yes of course, here it goes.

https://drive.google.com/file/d/14ZTb1Dzr3vhmg2MAmBW2WDaYr0RIYDJO/view?usp=sharing 

If you look inside the file, there is going to be 3 tables (Clients, Orders and Store)

I created a calculed column inside Clients to determinate the cluster for each one of the clients based on the other calculed column days without order.

I would like to track throught time how the cluster are increasing or decreasing

Thank you very much for your reply.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.