In this article, you will learn how to run your Cohort Analysis in Power BI.
The cohort analysis is a powerful customer analysis: it segments customers based on when they first purchased a product. Specifically, it answers the questions:
- Are newer customers coming back more often than older customers?
- Are newer customers spending more than older customers?
These questions are key to understand if the company is going in the right direction, with newer customers coming back more often and spending more money.
If you are looking for a simpler analysis of new existing customers, check out my former article here.
Let's now take the questions one by one.
Are newer customers coming back more often than older customers?
Below is the classical chart of the cohort analysis. On left is the month of customer acquisition. The numbers represent the percentage of customers coming back after x months.
For example, 12% of the customers that first purchased in January 2018 came back to buy a month later, in February 2018.
From this chart, it looks like customers acquired from May 2019 are coming back more often. This is great news!
Nevertheless, the second semester of 2019 also has few 'empty spots' with zero customers coming back. Specifically, the new customers of April 2019 and June 2019 did not come back for 3 consecutive months (month 2 through month 4).
Looking at the totals at the bottom of the report, we see that on average 14% of customers buy again after 1 month, then only 8% in month 2. The remaining months are low, until a spike around month 10.
The spike in month 10 can be linked to a sales cycle: possibly the customers are replacing products after 10 months.
Let's move to the second question.
Are newer customers spending more than older customers?
From the table below, the average spend for new customers on month 0 (the first month) did not change across 2 years: it stays at about 65 dollars. There are however increases in average spend 2-3 months after acquisition, and 10 months after acquisition.
This chart highlights two factors:
- we are not able to increase the average price spent by new customers in their first month.
- we should look into the products purchased after 2-3 months and 10 months: identify if we can convince consumers to purchase these additional products earlier on.
With the goal of the cohort analysis clear, it's time to get our hands dirty and start with Power BI!
Load the sales data for the cohort analysis in Power BI
To follow along with the tutorial, download the sample sales data from here.
You can load it by going to Get Data, then clicking on Text/CSV. Below is what the sales table looks like.
Once the table is loaded, select it.
With the table selected, add a new column.
We add three columns this way. The first one is Month Year, and it will be used in the Cohort Analysis visualization.
The DAX formula is:
Month Year = FORMAT([date],"mmm yyyy")
The second and third columns are necessary for the cohort analysis calculations. Here they are:
Year Month Number = VALUE(FORMAT([date],"yyyymm"))
Year Month order = RANKX('Sales table',[Year Month Number],,ASC,Dense)
Once the three columns are added, the Sales table will look like this.
The last step for this table is to sort the Month Year column based on the Order column. Click on Month Year, then Sort by Column and finally on Year Month Order.
Create a table for the future months of the Cohort Analysis.
This is a simple table and will have rows for 'Month 0', 'Month 1', etc.
Click on New Table.
When the textbox appears, enter the following DAX formula:
Future months = GENERATESERIES(0, 12, 1)
The table will have 12 rows and look like this.
Similarly as before, click on New Column and add the following column:
Months = "Month "&[Value]
We have now the final table for the future months.
Done with creating tables! We can move our focus to adding the measures that we want to track.
Create a Power BI measure for the customer count (Cohort Analysis).
This first measure will count the customers coming, based on their acquisition month.
For example, out of the 32 customers who purchased in May 2018 for their first time, 9 purchased again one month later.
Here is the DAX formula for it:
New customers count =
var acquisitionMonth = SELECTEDVALUE('Sales table'[Year Month order])
var selectedFutureMonth = SELECTEDVALUE('Future months'[Value])
var currentCustomers = VALUES('Sales table'[customer])
var pastCustomers = CALCULATETABLE(VALUES('Sales table'[customer]),
ALL('Sales table'[Month Year],'Sales table'[Year Month Number])
, 'Sales table'[Year Month order]<acquisitionMonth)
var newCustomers = EXCEPT(currentCustomers,pastCustomers)
var customersFuturePeriods = CALCULATETABLE(VALUES('Sales table'[customer]),
ALL('Sales table'[Month Year],
'Sales table'[Year Month Number]),
'Sales table'[Year Month order] = acquisitionMonth+selectedFutureMonth )
var newCustomersBuyingInFuturePeriods = INTERSECT(newCustomers, customersFuturePeriods)
RETURN COUNTROWS(newCustomersBuyingInFuturePeriods)
There are three main parts to the formula.
The first part defines the variables acquisitionMonth and selectedFutureMonth. Using the example of the 9 customers purchasing again after one month, acquisitionMonth would be May 2018 and futureMonth would be 1.
The second section has the variables currentCustomers, pastCustomers, and newCustomers. This section finds the customers buying for the first time in May 2018.
Finally, the variable newCustomersBuyingInFuturePeriods identifies the customers acquired in May 2018 that also bought in June 2018.
This formula is quite complicated so my recommendation is to play around with it, load easy data to Power BI and see the result.
Create a formula for the percentage of new customers coming back
We want to build the table below.
If you don't need the total at the bottom, then you can use a simple formula:
New customers % no total =
[New customers count]
/
CALCULATE([New customers count],ALL('Future months'),'Future months'[Value]=0)
Using the same example as before, to calculate the percentage of customers acquired in May 2018 and coming back after 1 month, we divide 9 (customers coming back after 1 month) by 32 (customers acquired in May). The result is 28%.
This simpler formula, however, does not work for the total. To work on the total, we should use SUMX, which sums the values on different months. Below is the formula updated to work on the total.
New customers % =
var selectedFutureMonth = SELECTEDVALUE('Future months'[Value])
var maxRank = MAXX(ALL('Sales table'[Year Month order]),[Year Month order])
var months = VALUES('Sales table'[Year Month order])
var monthsFiltered = FILTER(months, [Year Month order]<=maxRank-selectedFutureMonth)
RETURN SUMX(monthsFiltered,
[New customers count])
/
SUMX(monthsFiltered,
CALCULATE([New customers count],ALL('Future months'),'Future months'[Value]=0))
Let's move to the last formula: the new customers average spend.
Create a Power BI formula for the average spend of customers (Cohort Analysis)
This last formula shows us how much returning customer spend. It allows us to create the table below.
The formula is similar to the first formula for the customer count, except for the last part.
New customers average spend =
var acquisitionMonth = SELECTEDVALUE('Sales table'[Year Month order])
var selectedFutureMonth = SELECTEDVALUE('Future months'[Value])
var currentCustomers = VALUES('Sales table'[customer])
var pastCustomers = CALCULATETABLE(VALUES('Sales table'[customer]),
ALL('Sales table'[Month Year],'Sales table'[Year Month Number])
, 'Sales table'[Year Month order]<acquisitionMonth)
var newCustomers = EXCEPT(currentCustomers,pastCustomers)
var customersFuturePeriods = CALCULATETABLE(VALUES('Sales table'[customer]),
ALL('Sales table'[Month Year],'Sales table'[Year Month Number]),
'Sales table'[Year Month order] = acquisitionMonth+selectedFutureMonth )
var newCustomersBuyingInFuturePeriods = INTERSECT(newCustomers, customersFuturePeriods)
RETURN
CALCULATE(SUM('Sales table'[sales]),
ALL('Sales table'[Month Year],'Sales table'[Year Month Number]),
'Sales table'[Year Month order] = acquisitionMonth+selectedFutureMonth,
'Sales table'[customer] IN newCustomersBuyingInFuturePeriods)
/
COUNTROWS(newCustomersBuyingInFuturePeriods)
The last part (starting from RETURN) is different: there is a CALCULATE divided by a COUNTROWS.
The COUNTROWS is the same as before: it counts the number of customers returning after x months. The CALCULATE, on the other hand, finds how much these customers spend.
With all the measures created, add a Matrix report and you have your Cohort Analysis.
That's it! You can create a powerful Cohort Analysis in Power BI. If you have questions, do not hesitate to add comments below.
Enjoyed the article? Subscribe to the newsletter and download the Power BI of the article for free.
