cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

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
Super User III
Super User III

Hi @danlindoso11 ,

 

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





Yes @MFelix 

every refresh overlays the data =(

Hi @danlindoso11 ,

 

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





@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 @danlindoso11 ,

 

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 @danlindoso11 ,

 

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





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 @danlindoso11 ,

 

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





@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

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
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors