Hi everyone,
I am trying to calculate the lifetime value for our customers but I cannot figure out how to do that in QuickSight. I can do it easily in Excel using a couple of steps but I cannot understand how to use avgOver here (I am guessing this is what I need to use).
The idea is to use a very simple formula:
Average Active Days * Average Purchases Amount
“Active Day” is defined as a day in which the user has made a purchase and it’s a simple calculation done in SQL: if purchases > 0, 1, 0.
I will then add filters, maybe modify the underlying calculation not to use simply “purchases amount” etc., but this is the core of it.
Here is my initial data:
| Company | Date | User | Purchases Amount | Daily_Active |
|---|---|---|---|---|
| A | 01/08/2022 | 123 | €100 | 1 |
| A | 01/08/2022 | 456 | €2,400 | 1 |
| A | 01/08/2022 | 789 | €0 | 0 |
| A | 02/08/2022 | 123 | €500 | 1 |
| A | 02/08/2022 | 456 | €0 | 0 |
| A | 02/08/2022 | 789 | €400 | 1 |
| A | 03/08/2022 | 123 | €600 | 1 |
| A | 03/08/2022 | 456 | €0 | 0 |
| A | 03/08/2022 | 789 | €200 | 1 |
| A | 04/08/2022 | 123 | €0 | 0 |
| A | 04/08/2022 | 456 | €0 | 0 |
| A | 04/08/2022 | 789 | €0 | 0 |
In other words, daily aggregated data with: date, user, total purchases for the day, and a flag to show if the user had purchases as explained above.
Aggregating the data per user, I would get:
| Company | User | Purchases Amount Sum | Active Days Sum | Purchases Daily Avg |
|---|---|---|---|---|
| A | 123 | €1,200 | 3 | €400 |
| A | 456 | €2,400 | 1 | €2,400 |
| A | 789 | €600 | 2 | €300 |
As it is clear I am simply summing up the “purchases amount” and “active day” fields per player, and also calculating the daily average (purchases amount sum / active days sum).
To then get my lifetime value I first calculate the average active days:
(Active Days Sum / Distinct Count of Active Users) =
(3 + 1 + 2) / 3 =
2
I then check the average daily purchases per user:
(Purchases Daily Avg Sum / Distinct Count of Users) =
(€400 + €2,400 + €300) / 3 =
€1,033
And at this point I simply multiply these two values:
Avg user active days * Avg daily purchases amount =
2 * €1,033 =
€2,066
This is basically what I am looking to extract into QuickSight.
I would like a simple table with the final results, something like:
| Company | Avg Active Days per User | Avg Daily Purchases Amount per User | Lifetime Value |
|---|---|---|---|
| A | 2 | €1,033 | €2,066 |
But for the life of me I cannot come up with the calculation. I tried to use avgOver but I’ve had no luck with my attempts. I am guessing that’s what I need since I need to calculate average daily purchases per user and then use it in another average calculation.
Anyone has ideas?
Thank you in advance!
