Sometimes in Customer Analysis we need to calculate the projected customer number.
And the projected customer churn.
In the demo dataset we have the given number of new customers and the retention factor for every year that customers stay with the company (by buying items, renewing subscriptions, etc.).
The retention factor is the percent of customers that are most likely to stay trough a period of time, in this example, the retention after the first year is quite low, however, after the second year the customers that remained after the first year are more likely to stay the 3rd year, hence the retention factor for the 2nd year is bigger than the retention factor for 1st year.
Every group of customers new or any group of “retained” customers is also called a cohort (a group of customers with a common characteristic, i.e. the acquisition year).
What we are trying to calculate here is the Total number of customers.
The Total number is a sum of New customers (or Acquired customers) and the customers that are staying with us – Continuing customers.
The number of New customers is given, so we do not need to spend time here.
The number of Continuing customers is the main calculation we are trying to achieve.
The complexity of the calculation lies in various cohorts (customer groups) for each year.
Say in the third year, we will have, namely, the New customers and the groups retained from previous years: last year customers and customers that have signed/bought already 2 years ago continuing to this year.
So, how do we sketch up the formula?
Maybe not the most trivial thing, but it goes something like:
Continuing Customers =
SUMX (
FILTER ( ALL ( ‘Year’ ); ‘Year'[Year] <= MAX ( ‘Year'[Year] ) );
PRODUCTX (
FILTER (
Retention;
Retention[Year After]
<= MAX ( ‘Year'[Year] ) – EARLIER ( ‘Year'[Year] )
);
[Retention Rate]
)
* CALCULATE (
SUM ( FactCustomers[New Customers] );
FILTER ( ALL ( ‘Year’ ); ‘Year'[Year] = EARLIER ( ‘Year'[Year] ) )
)
)
This formula gives us (for every year) the total number of retained customers from previous year.
A quick explanation goes like this:
- for all the groups of customers that we were able to retain (prev. year, 2 years ago, etc.) – SUMX
- calculate the retention factor of the group (how many years after acquiring), multiply the factors together to get a “final” factor – PRODUCTX
- compute this factor by the initial number of customers that were acquired – CALCULATE
Other articles:
Basket Analysis in Power BI
Totaling Financial Accounts in Power BI
Power BI has come a long way from where it started (only as a Excel add-on). It has a very versatile and powerful in-memory calculation engine called the DAX engine (or xVelocity more technically). I have been using Power BI to gain insights from its inception, year 2015.
If you are interested to gain your insights from your (customer) data, you can contact me!
The BI Journey 