DAX offers powerful way to analyze “new” vs. “returning” customers. In this article learn easy and simple DAX measure patterns to count number of new customers and number of returning customers from your data.
What is a Returning Customer?
A returning customer is someone who comes back to our business and does another transaction. For example, in the above illustration, CUST-001 and CUST-004 are repeat or returning customers.
What is a NEW customer?
A new customer is someone who is doing their first transaction with us. In the above example data, all other customers (except CUST-001 & CUST-004) are technically NEW CUSTOMERS.
Note: A new customer today might be a returning customer in future.
DAX measures for calculating new vs. returning customer counts
All the measures in this example are based on a simple “Data” table with 4 columns – Customer ID, Date, Order Qty and Product Name.
Customer Count Measure
=Customer Count = DISTINCTCOUNT(data[Customer ID])This is a simple distinct count measure that tells us how many distinct customers transacted with us. When used with a the context of a date or product we will get the number of customers per each.
Returning Customer Count Measure
Returning Customer Count =
var custs = DISTINCT(data[Customer ID])
var curr_date = LASTDATE(data[Date])
return
sumX(custs, CALCULATE([Customer Count], data[Date]<curr_date))This measure tells us how many returning customers are there for the context of current “time-period”.
How this returning customer count works?
Imagine the below output and let’s focus on the second row.
- For the date context of 6-January
- We create custs variable which gives us all the 92 customer IDs.
- The curr_date variable tells us the latest date – i.e. 6-January.
- We then iterate for each of the customers in custs table and calculate the [Customer Count] prior to the curr_date. This would be 1 if the customer has previously transacted with us and 0 otherwise.
- The SUMX adds up all these values (ie all 1s) and tells us 33, which is the number of returning customers.
New Customers Measure
New Customers = [Customer Count] - [Returning Customer Count]If you already have both the total [customer count] and [returning customer count], you can easily subtract one from another to get the [new customers] count.
But if you don’t have the [returning customer count] or just want to directly calculate the [new customers], you can use below DAX measure.
New Customer Count - direct =
var custs = DISTINCT(data[Customer ID])
var curr_date = LASTDATE(data[Date])
return
SUMX(custs, IF(CALCULATE([Customer Count], data[Date] < curr_date)=0,1,0))The above measure uses the same approach as [Returning Customer Count] but flips the logic inside SUMX by using the IF function to negate the CALCULATE result.
Returning Customers in Last 4 Weeks or similar
While the above [Returning Customer Count] works flawlessly, it may not be realistic to consider a customer to be returning if they rarely transact. So a more realistic calculation would be to consider a customer to be returning if they did some business in the last 4 weeks (or x periods). Here is the DAX pattern for that.
Returning Customers in Last 4 Weeks =
var custs = DISTINCT(data[Customer ID])
var curr_date = LASTDATE(data[Date])
var start_date = DATEADD(curr_date,-28,DAY)
return
SUMX(custs, CALCULATE([Customer Count], data[Date]<curr_date && data[Date]>=start_date))In this case, we simply calculate the “start_date” for our calculation window as well. Here I have used 28 days as an example, but you can easily change this to any window size.
Then we apply the same SUMX logic but modify the filter context in the CALCULATE to check both boundaries of the dates.
Why not do this analysis in SQL or somewhere upstream?
When I mentioned about this approach to my wife Jo, she said, why not do this in SQL directly and tag each customer as “new” or “returning”?
Here is why I prefer to do this with DAX:
- Business Rule Flexibility: With DAX based approach, we can easily change the business rule surrounding who is a returning customer. For example, we can use the 4 week window like above easily.
- Interactivity: We can add a product slicer (see below) to analyze which customers returned to purchase the same product. This is incredibly helpful to understand customer loyalty and campaign effectiveness.
Of course, there are advantages with SQL approach too. Mainly,
- SQL tagging is faster: Unlike DAX calculations which run in real-time & client-side, SQL calculations are done once and at server side. When you have millions or billions of records, doing SUMX in real-time is going to be slow.
- Consistency: Applying customer tagging at server side in the data layer means the business rule & logic is consistently applied for every report.
Sample Power BI Workbook:
If you want to play with these measures and understand the calculation better, check out the sample PBIX file here.
In conclusion
New vs. Returning Customer analysis is a must-have for customer analytics. The DAX required for this is easy to implement and works beautifully. Try this analysis to understand the effectiveness of marketing campaigns (lead gen, customer capture) and loyalty programs (reward points, notifications). Using a time-window based calculations (ex: 4 weeks) is a great way to understand customer behavior and purchasing patterns.
24 Responses
I’d suggest simply using the subtotal function and filtering the data using the Win/Loss column. You get the same results and the formula is more comprehensible.
@John
That is one option.
There are times however when you want to see the whole data table or a filtered subset and still want to produce summary reports against an unfiltered field.
Is there a particular reason why you are using a comma and the unary (–) operator for the second array in the SUMPRODUCT formula? It seems to work the same if you were to string the arrays together using the asterisk (*). The advantage is that SUMPRODUCT treats the entire string of arrays as a single array.
@Mathew
Your correct, There is no difference.
I thought it may have been easier to explain this method.
Is there a way to do this on a large set of data? As in ~100,000 rows? When I try I get an error because the formula becomes too long. It says the max length of a formula is 8,192 characters. Excel 2010.
How do I incorporate a specific text within a cell for the second array. For instance, – -(C7:C13=”Apple”)
when I chose a specific text the formula does not work.
@RB
I am not sure what is the issue as if I use the sample data in the post the following work fine
Count:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)), –(C7:C13=”L”))
Sum:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)),(C7:C13=”L”)*(D7:D13))
You may want to check that there are no leading or trailing spaces in your list of Apples
I should have given a better explanation. Heres my situation. I have a column with cells filled with names like Column 1, Column 2, Pier 1, Pier 2, etc. If the cell just contained Pier and searched for that it works. But because it has other characters in the cell its not recognizing the pier. So how can I extract specific characters of a string of text in this formula?
Hopefully this was a better explanation
Hello-
This formula works pretty well for me except that it slow down excel and prevents some of my macros from working. I was wondering if there was a way to program this in VBA so that excel isn’t always trying to recalculate it. I would like to use a push of a button to get it to run then paste in a cell.
Thanks!
I am trying to sum filtered data in a column, but would want to ignore the negative values in the column. How to go about doing this?
@Akshay
Why not just add a filter to that column to only show the values greater than zero?
The negative values are required for reporting purposes, but their effect on the total is distorting the required output. Please advise.
@Akshay
I’d suggest making a post in the Chandoo.org Forums
http://forum.chandoo.org/
Attach a sample file to simplify the task
I have this working for counting and summing, however, I have a list and for the second array, I need a criteria. That is, I’m looking for b13:b200=”01.??.??” or =left((a1,2) or something like that. These types of criteria matches do not appear to work as I get a blank as a result.
Thanks!
@Bob
As your formula b13:b200=”01.??.??” looks like you are trying to check the first day of the month of the range
What about trying Day(B13:B200)=1
Hai Experts,
i understood this formula well and working fine in MS Excel 2013
but when the same am trying to place in google Spreadsheet it shows error as
“SUMPRODUCT has mismatched range sizes. Expected row count: 1. column count: 1. Actual row count: 2014, column count: 1.” and as a result #VALUE! Appears in cell.
Can anyone please help me how would i get it done in Google Spread sheet
or is there any other formula as a substitute for this.
Thank you very much.
thanks for providing this.. but why does excel keeps on prompting Circular referencing in cell D3?
@Vivek
I don’t know
I just downloaded the file and it is working fine and not showing that error
Goto the Formulas, Calculation Options Tab and check that Calculation is set to Automatic
What version of Excel and Windows are you using ?
I know that this forum is for MS Excel, but I am trying to help someone who is working in Google Sheets. The below formula works in Excel but Google Sheets returns:
“SUMPRODUCT has mismatched range sizes. Expected row count: 1. column count: 1. Actual row count: 39000, column count: 1.” and as a result #VALUE! Appears in cell.
This is the same problem asked by Srichirin above. Does anyone know if there is a formula for Google Sheets that will replicate what MS Excel does?
=SUMPRODUCT(SUBTOTAL(3,OFFSET($C$6:$C$39500,ROW($C$6:$C$39500)-MIN(ROW($C$6:$C$39500)),,1)),- -($C$6:$C$39500=H1),($D$6:$D$39500))
Trying to find a SUMPRODUCT formula that counts the word Closed by date for the last 7 days in a filtered list.
=COUNTIF(M:M,”>”&TODAY()-7) works ok for unfiltered count Column M contains Closure dates (blank if open) and Column L is Status Open or Closed
@ Terry
Please ask the question at the Chandoo.org Forums
https://chandoo.org/forum/
Please attach a sample file to ensure a quicker more accurate answer
I used this formula and worked like a charm! But, now I’ve been requested to use it but adding not one but two criteria in the same formula. For instance the sum I was doing added negative and positive numbers. I’ve been asked to use the exact same formula but adding that only positive numbers were considered… any idea on how to do this?
How exactly do you do sum filtered cells when two criteria are need not just one?
Thank you so much brother literally I have been struggling since morning to get the sum of the filtered category, however, after reading your blog attentively i got my solution, so thanks a lot once again.