Unlocking the Mystery of Customer Behavior: Understanding Days Since Last Transaction

Photo by Estée Janssens on Unsplash

Business Problem — Identify how long ago customers purchased a product.

Why is this a business problem?

Photo by Evan Dennis on Unsplash

A paying customer is a happy customer meaning customer retention is as integral to revenue as new customer acquisition, which then leads us to understand customer behaviour.

One way to understand customer behaviour is to identify the last time a customer did a transaction. You can then create strategies around this, offer them discounts to bring them in, get their opinion on the product and see if there is anywhere, we can improve, etc.

The DAX measure that lets us calculate this is seen below. Personally, if I’m not going to be reusing a measure, I create it as a variable.

Let’s break the DAX into pieces.

Photo by Markus Spiske on Unsplash

Recall that we are trying to identify how long ago a particular customer did a transaction. To get this, we need the last date the customer made a trade and then compare that date with a base date. Usually, the base date should be today’s date, and as the day progresses, the base date changes. In Excel and PowerBI, we have the TODAY() function to get this.

But because this data is hypothetical, we won’t be using the TODAY() function because of the data used. The data ended in 2019, so comparing those transactions in 2019 with today seems far-fetched.

Hence the base date will be the last date any transaction was done in the data. Lines 3–7 calculate this for us.

We are telling PowerBi to remove all context on the Sales Data table and give me the last date on the sales data table, i.e. ignore the relationships all relationships on the Sales Data table.

Lines 9–10 get the last date each customer made a purchase.

As we can see, each customer’s last purchase was made on a different day.

All that is left is to get the difference between these two dates.

The result is seen below. Our base date is the 30th of December 2019 because that was the last time any customer made a transaction.

In a real scenario, the DAX will look like this.

The result will then look like this.

You can see the difference when using the TODAY() function. The number looks outrageous because the last sale was made in 2019 and today is 2022.

Continue playing with DAX measures to bring out those great analytics.

Photo by Junseong Lee on Unsplash