Forum Discussion
Helper IVNew/Returning/Lost Customers
Hi,
I need to create a report that shows which clients were New that month, which were Returning that month and which were Lost.
It needs to be based on the selected Month in a slicer, not from 'today'.
NEW means they have spent in the selected month, but NOT in the previous 13 months from the selection. (Any spend before 13 months doesn't matter as we would call them 'new' again.)
RETURNING means they have spent in the selected month, AND at some point in the previous 13 months.
LOST means they have not spent in the selected month, nor in the previous 13 months. But they HAVE spent before the previous 13 months.
I'd like New/Returning/Lost to be choosable in a slicer.
My main data source has a 'month' column, which links by relationship to a 'fulldate' column in my calendar table. I'm not sure whether to use the month column or the fulldate column so guidance on that would be appreciated too please.
I have seen this page but I don't feel it fits my circumstances as I'm not looking to count the clients. I want the client to show up in a list with their name and their spend per month.
https://www.daxpatterns.com/new-and-returning-customers/
Thanks in advance for any help you can provide :-)
18 Replies
- Ashish_Mathur
Super User
Hi,
Share the link from where i can download your PBI file.
abloorThanks for your time Ashish_Mathur
I have created this data and PBIX - hopefully it's ok. I feel like I need a measure or calc column to add into my matrix rows, above the client name, so the clients are grouped into New/Returning/Lost. However if this isn't possible I'd be happy with a simple table that can be filtered with a slicer for New/Returning/Lost. Open to other ideas too of course.
https://1drv.ms/u/s!AvZLa7VG1kXrgUdAGdAYeHEMOSbS?e=4Shf6p
Expected Result if Sep 2019 is chosen in slicer:
NEW
AAA
BBB
FFF
RETURNING
CCC
DDD
LOST
EEE
- Ashish_Mathur
Hi Ashish_Mathur ,
Thanks for sharing all the useful information. I have a similar problem and want to find new, lost and repeated customers. My dataset is huge so attaching a sample of it. Dataset has multiple rows of same and different customers making multiple purchases from 2017 to 2019. My goal is to have a table with Col 1--> Year , Col-->2 Months , Col 3--> Count of New customers and so on for Lost and repeated customers + their Sales. Also a slicer for Year and Month filtering. (already have a calendar table in my report). Churn period is 2 months.
Tried various formulas but nothing worked. All those work on unique customer list which is not in my case. Requesting your help here. Couldn't find attach option, hence pasting sample here.
THANKS A LOT !!Date Cust sku Sales Units 1/1/2017 1234 706 4.99 1 1/1/2017 1234 704 35.98 2 1/1/2017 1234 1009 6.49 1 23/5/2017 4444 107 192.35 5 12/7/2018 4578 2102 3.58 1 20/8/2018 1234 109 0 0 10/12/2018 7654 106 -31.99 -1 12/5/2018 458 221 3.27 1 12/11/2017 2442 102 3.43 1 12/5/2018 8715 20 66.14 1 12/5/2018 8715 113 78.98 2 12/5/2018 8715 1839 188.27 1 23/11/2019 1234 922 3.99 1 10/4/2019 1224 99 -7.99 -1 12/5/2018 6446 1002 90.77 1 12/5/2018 1714 158 64.22 1 12/5/2018 6736 702 90.24 6 12/5/2018 2646 70 19.99 1 10/3/2019 2135 108 5.99 1 14/5/2019 6861 813 96 48 20/7/2019 2801 20 35.2 2 22/9/2019 1872 1015 21.99 1 10/10/2019 3615 1055 12.32 4 10/11/2019 1029 25 0 0 22/12/2019 1872 9862 40 0 10/4/2019 1234 105 26.79 1 10/4/2019 6736 1089 29.99 1 10/4/2019 606 2188 49.48 2 10/4/2019 6736 19 99.99 1 - Ashish_Mathur
Hi,
On the sample data that you have shared, show the exact result you are expecting.
fazza1991Helper II
Hi,
Lets take the example from your PBIX file.
If we look at just the lost clients '2019'
All three clients last booking date:
Client Last Date AAA 01/09/2019 BBB 01/09/2019 EEE 01/12/2016 As you can see from the screenshot - each client is showing as lost in each month therefore double counting them.
I would have expected the following:
AAA and BBB to show as LOST 01/01/2018 and only for January 2018 rather than every month up until Sep-2019.
EEE to show LOST 01/12/2016 and not appear in every month
So in theory i did not lose these clients every month
In reality you can only lose a client once (they may return after a period of time but that would be considered recovered or new).
Thanks
- fazza1991
sorry to bring up topic from many years ago.
Solution for me wokred in some elements but...
How do we apply this so its unqiue per account id per month? currently it displays lost customers and keeps adding them to the list each month. E.g. lets say ABCD would appear in my lost customer for Jan Feb Mar Apr etc but only need it to appear in the first month no revenue was generated for them (if this makes sense)
- Ashish_Mathur
Hi,
Share some data, explain the question and show the expected result.
- fazza1991
Hi,
Lets take the example from your PBIX file.
If we look at just the lost clients '2019'
All three clients last booking date:
Client Last Date AAA 01/09/2019 BBB 01/09/2019 EEE 01/12/2016 As you can see from the screenshot - each client is showing as lost in each month therefore double counting them.
I would have expected the following:
AAA and BBB to show as LOST 01/01/2018 and only for January 2018 rather than every month up until Sep-2019.
EEE to show LOST 01/12/2016 and not appear in every month
So in theory i did not lose these clients every month
In reality you can only lose a client once (they may return after a period of time but that would be considered recovered/new).
Thanks
- Ashish_Mathur
