Forum Discussion

_Ester_'s avatar
_Ester_
Helper I
2 years ago

Calculating Recency with Auto-Updating Sales Data

Hi everyone! I need some assistance with a Power BI project related to calculating "Recency" from our sales data. Recency is a metric that measures the number of days since a customer's last purchas...
Need Help
lbendlin's avatar
lbendlin
Super User
2 years ago

Thank you for the details, much appreciated.  So i guess you have to accept the slight inconsistencies that come with the monthly bucketing. For Segment moves I would propose to use a ribbon chart, or a Sankey chart (that one requires additional data prep).  To calculate the data you will have to materialize the RFM parameters for each customer and each month (either in a calculated table or through a measure), and then you can plot them accordingly.  You will need to disconnect your Calendar table.

 

I'll have a look at your sample PBIX.

lbendlin's avatar
lbendlin
Super User
2 years ago

Here's how a sample approach for recency for a single customer.

 

 

  • _Ester_'s avatar
    _Ester_
    Helper I
    2 years ago

    Hi lbendlin ,

    Thank you for your suggestions! I tried both the Ribbon chart and the Sankey chart while attempting to avoid creating a calculated table with every combination month-customer_id because I believe that in the long run, it could significantly slow down the system.
    Since the Sales file updates over time, the combinations of month and customer_id could become extremely large.

    Is there a way to achieve the same goal without slowing down the system and ensuring everything is automatic?

    • Is there a way to create the calendar with all month-customer_id combinations directly with a calculated measure and then use these data to create the visual?
    • Is there a way to ensure that the table created with the measure and the visual update automatically over time?

    Thank you again for your help!

    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago
      Is there a way to create the calendar with all month-customer_id combinations directly with a calculated measure and then use these data to create the visual?

      Measures return scalar values. They cannot hold tables.

       

      Is there a way to ensure that the table created with the measure and the visual update automatically over time?

      Not sure what you are asking.  Assuming your data source is in Import mode this will be taken care of during the semantic model refresh.

      • _Ester_'s avatar
        _Ester_
        Helper I
        2 years ago

        lbendlin sorry, I think I just now understood the solution you proposed.
        Let me take a look to make sure I got it right, and I'll get back to you.

        Thanks in the meantime!

  • _Ester_'s avatar
    _Ester_
    Helper I
    2 years ago

    Hi lbendlin,
    I tried to replicate your approach below:

    lbendlin wrote:

    Here's how a sample approach for recency for a single customer.

     

     


    I created the measures to calculate Recency as you showed me and I also added the measures for Frequency and Monetary:



    It works! However, I can't understand how from here I can visualize the customer migration, for example with a ribbon chart.

    I suppose I should somehow assign each customer_id a category based on their current condition, and then count how many customer_ids are in that category, but I can't find a way to do this with the calculated measures.
    I tried to create a calculated measure for the RFM segments, but it doesn't work.

    Could you guide me?
    I updated the PBIX file with these new measures.

    Again, thank you!

    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

      If you still want to avoid using a calculated table the next step is to prepare your independent dimensions  (dates and customers) and then calculate the measures for each of the periods.

       

      Without that the charts may not give you much

       

      • _Ester_'s avatar
        _Ester_
        Helper I
        2 years ago

        Hi lbendlin,
        I still can't understand your solution 😞

        In the meantime, I developed the idea you suggested, adapting it as much as possible to what I need.

        What's changed:

        🔲 I have added measures:
        Recency2, Frequency2, Monetary2, and RFM segments2 which calculate the status of each customer in EVERY period of the year, including periods where no transactions were made.



        🔲 I have added a RFM categories table with the 5 possible categories ("NEW", "FROZEN", etc.) and the "Unique customers per segment" measure which calculates the number of users in each RFM category for each period.

        ✅ What I achieved:
        I can know the size of each RFM segment over time, which can be an indication of how my business is performing.

        Look at the visual below:
        each column represents 100% of customers up to that date.


        ❌ What I didn't achieve:
        A segment may have grown not because of my marketing strategies, but simply because the total number of customers has grown.
        Additionally, I still don't know which segment the customers came from.

        For example:
        If the HABITUAL segment has grown, it is positive if the additional HABITUAL customers were previously TOP AT RISK customers, it is not positive if they were previously TOP customers.

        lbendlin do you think it is possible to start from what I have done and somehow understand the segment each customer belonged to in the previous period?

        I have updated the PBIX with the new measures and visualizations (page: "RFM_Analysis_2" and "Migration")

        Thanks again!