Hi all,
Sorry for spamming the forum, but lately I have quite a few creative tasks and my searches lead to nowhere (that’s why I am assuming that they are creative).
(I even had issues coming up with the title.)
Problem of the day: monthly customers lost and retained.
What I am trying to achieve is this: I have a parameter with a date, by default previous day. I want to check how many customers had made a purchase last month up to the same date last month. With this I can then find out how many have also made one this month (so they have been retained), and how many have been lost because up to the parameter date, they have not made a purchase.
I would be planning to display this information as a KPIs showing retained, lost and also the net balance (current month’s customers - lost ones), and probably as a column chart to show the month by month trend.
To simplify things, I have decided to use a table showing all users’ daily activity, also when they do not have any purchases. It is like this (I will show only few days per month but I have all in my original source):
| Date | User | Daily_Active |
|---|---|---|
| 01/04/2023 | ABC | 0 |
| 02/04/2023 | ABC | 0 |
| 03/04/2023 | ABC | 1 |
| 04/04/2023 | ABC | 0 |
| 05/04/2023 | ABC | 1 |
| 06/04/2023 | ABC | 0 |
| 07/04/2023 | ABC | 0 |
| 01/05/2023 | ABC | 0 |
| 02/05/2023 | ABC | 0 |
| 03/05/2023 | ABC | 0 |
| 04/05/2023 | ABC | 0 |
| 05/05/2023 | ABC | 0 |
| 06/05/2023 | ABC | 0 |
| 07/05/2023 | ABC | 0 |
| 08/05/2023 | ABC | 0 |
| 09/05/2023 | ABC | 1 |
| 10/05/2023 | ABC | 0 |
So this player had at least one purchase on the days in which Daily_Active is 1.
After coming up with several ways of tackling this, I thought that the best way would be if I could add a column and go from there.
Using a period-to-date function, I thought about max, check if at a particular point in time the monthly max for Daily_Active is 1, and if it is return 1. This worked for the selected month, but not for the previous month.
In fact, if I use this formula for Monthly_Active:
periodToDateMaxOverTime(max(Daily_Active), {Date}, MONTH)
I would now get a table like this:
| Date | User | Daily_Active | Montly_Active |
|---|---|---|---|
| 01/04/2023 | ABC | 0 | 0 |
| 02/04/2023 | ABC | 0 | 0 |
| 03/04/2023 | ABC | 1 | 1 |
| 04/04/2023 | ABC | 0 | 1 |
| 05/04/2023 | ABC | 1 | 1 |
| 06/04/2023 | ABC | 0 | 1 |
| 07/04/2023 | ABC | 0 | 1 |
| 01/05/2023 | ABC | 0 | 0 |
| 02/05/2023 | ABC | 0 | 0 |
| 03/05/2023 | ABC | 0 | 0 |
| 04/05/2023 | ABC | 0 | 0 |
| 05/05/2023 | ABC | 0 | 0 |
| 06/05/2023 | ABC | 0 | 0 |
| 07/05/2023 | ABC | 0 | 0 |
| 08/05/2023 | ABC | 0 | 0 |
| 09/05/2023 | ABC | 1 | 1 |
| 10/05/2023 | ABC | 0 | 1 |
But once I try to show the previous month’s activity, I get funky results.
The formula I use is simply:
periodToDateMaxOverTime(max(Daily_Active), addDateTime(-1, 'MM', {Date}), MONTH)
As I understand this is because the formula checks MonthToDate, so in this case this is not what I am really after.
I then looked at periodToDateMax, since I can use an ending date, but that is not working either. I used something like this:
periodToDateMax(Daily_Active, {Date}, MONTH, addDateTime(-1, 'MM', {Date}))
(I also tried changing dateTime using the same as endDate.)
Is there any way for me to return what I am after? Or am I even on the right way and should I instead look at some distinct count over time? (Since I will be looking at distinct count of users)
Thank you to anyone who can help 