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

Question

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!

mfelix · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
I created the following measures:
&nbsp;
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] &lt; 7
        &amp;&amp; [Last Order Date] &lt;&gt; BLANK (); 5;
    [Days Without Order Number] &gt;= 7
        &amp;&amp; [Days Without Order Number] &lt; 14; 4;
    [Days Without Order Number] &gt;= 14
        &amp;&amp; [Days Without Order Number] &lt; 30; 3;
    [Days Without Order Number] &gt;= 30
        &amp;&amp; [Days Without Order Number] &lt; 60; 2;
    [Days Without Order Number] &gt;= 60
        &amp;&amp; [Days Without Order Number] &lt; 90; 1;
    0
)
&nbsp;
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

&nbsp;
&nbsp;
Now add the following measure:
Cluster_Count =
VAR temptable =
    SUMMARIZE ( Clients; Clients[id]; "Recency"; [Recency Note value] )
RETURN
    IF (
        MAX ( DimDate[Date] ) &lt;= MIN ( Orders[created_at] );
        BLANK ();
        CALCULATE (
            COUNTROWS ( FILTER ( temptable; [Recency] IN VALUES ( 'Cluster'[ID] ) ) )
        )
    )
&nbsp;
See result below:
&nbsp;
&nbsp;

The two slicers on the bottom are for the order dates and for the date dimension&nbsp; table that will allow to have filters. these two slicers are sincronized with each other.
&nbsp;
Check file attach, and if you need any further assistance please tell me.

mfelix · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
Big file, I will have a look at it and will get back to you ASAP.

mfelix · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
How is the granularity of the information are you getting data at a daily basis, or every refresh overlays the data?
&nbsp;
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.
&nbsp;
Can you share some setup and mockup data?

anonymous · Answer

Hey MFelix&nbsp;!!Yes of course, here it goes.https://drive.google.com/file/d/14ZTb1Dzr3vhmg2MAmBW2WDaYr0RIYDJO/view?usp=sharing&nbsp;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 decreasingThank you very much for your reply.

anonymous · Answer

Yes&nbsp;MFelix&nbsp;every refresh overlays the data =(

Forum Discussion

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

10 Replies

Related content

Drill throught the date

data not pulling throught due to relationships

Custom Visual

Visual

Create column throught measure