# New, Lost and Returning Customers

Hi,

I would like a report that counts New, Lost and Returning Customers by year and month for a new product launched on February 2023.

Suppose that the sales for the customers are as follows:

The definitions for customers’ status are:

- New Customer: Customer’s revenue is > 0 and there is no sale in previous months
- Lost Customer: A customer has not purchase for two consecutive months
- Returning Customer: The customer buys again after he was lost

The final result should look like the following:

Is there any pattern to solve this out?

Thank you.

0

## Comments

Niels ThomsenHi Nikos

Yes it can be done with some calculations.

One thing you need to decide is about the timefilter. How far do you want to go back?

Also - it could actually be 2 different visualizations - one showing the 3 possible different types of status in a crosstab, and the other just showing the numbers.

First one would have months on the horizontal axis and customers on the vertical - and the other visualization the other way around.

I did the first one - a fragment of the end result shown here:

Quick guide to do this:

Create 3 calculations (all as new measure) that will set a "flag" (value=1) where the customer meets the requirements:

New Customercalculation syntax:if sum(0, 0, m1) > 0 and sum(d1:0, 0, m1) = sum(0, 0, m1) then 1 else 0Explained: If the current cell (current column and row) holds a value - and that value is equal to the sum of all the columns from the first up to the current then we have a new customerLost Customercalculation syntax:if sum(-1:0, 0, m1) = 0 and sum(-2:0, 0, m1) > 0 and sum(d1:0, 0, m1) > 0 then 1 else 0Explained: If the previous column and current column of the current row holds no values and it's not already marked as lost and the value of all the columns > 0 (meaning there has a value at some point), then we have a lost customerReturning customercalculation syntax:if sum(-2:-1, 0, m1, 0) = 0 and sum(0, 0, m2) = 0 and sum(0, 0, m1) > 0 then 1 else 0Explained: If the 2 previous columns didn't have any numbers (which means the customer is lost) and the flag for new customer is 0 (we don't want to confuse a returning customer with a new one) and the current cell in the current column and row has a value, then we have a returning customer.Now just hide the original measure using visibility.

Hide all values that are 0 in the the 3 calculations using visibility agents.

Use number formatting to change the numbers to text with this "hack"

Finally make a color agent for each of your flags with the condition value=1

Hope it makes sense

Niels ThomsenAnd here's the other one - showing a count New, Lost and Returning customers per month.

First end result:

Now the same 3 calculations need to be made (only the column and row references are switch around because the to axis' have been switched.

New Customer:if sum(0, 0, m1) > 0 and sum(0, d1:0, m1) = sum(0, 0, m1) then 1 else 0Lost Customer:Returning Customer:if sum(0, -2:-1, m1, 0) = 0 and sum(0, 0, m2) = 0 and sum(0, d1:0, m1) > 0 and sum(0, 0, m1) > 0 then 1 else 0We also need 3 calculated columns to count all the flags for each month:

No of New Customers:sum(all, 0, m2)Explanation:sums of all columns if the current row for measure 2 (which is our first calculated measure - New Customer)No of Lost Customers:sum(all, 0, m3)Explanation: sums up measure 3No of Returning Customers:sum(all, 0, m4)Explanation: sums up measure 4Now hide everything using visibility (Except your 3 calculated columns)

Hope it makes sense

Nikos DimakisHi Niels,

Thank you very much for the provided pattern and "hack".

It works great except from the fact that totals for Lost and Returning Customers do not return any results.

Do you have any idea why does this happen and how we could solve this?

Niels ThomsenAhh I see it. It's a bit of long explanation, but in short TARGIT will try to do the calculation on all levels (subtotal and grandtotal level and leaf level). This will create a problem when you refer to -1 or -2 since that will mean the previous subtotal/grandtotal (which isn't there)

Anyway - the fix is to:

Now add a new calculated column called Lost Customers with the syntax:

if allcount(c2,all(c),m1)=0 then sum(c2,0,m1) else sum(c2,all(c),m1)Explanation:C2 is the name of the second calculated columns (Intermediate Lost Customers)

The allcount checks for "children" which is a way of detecting if you are on subtotal level. If that is the case the number of children (c) should be greater than 0. Then we sum up all the rows to create a new correct subtotal

Also add a new calculated column with the name Returning Customers with the syntax:

if allcount(c3, all(c), m1) = 0 then sum(c3, 0, m1) else sum(c3, all(c), m1)Explanation: See aboveNow hide the intermediates, and you should have subtotals that are correct.:

Nikos DimakisNiels after making a slight modification to the formula, since I hide the days in dates hierarchy your fix worked great.

Thank you very much for your time and explanations!

Niels ThomsenGreat to hear that it worked!

Please sign in to leave a comment.