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?

Hi,

formate it on a day and add you ovgOver formula to caudate the average. I will work on on day basis.

That’s from my side

Regards,
Naveed Ali

Hi @Naveed,

First of all thank you for the help!
But I am not sure I understand: as you can see the Date column is in the initial dataset (first table), my issue is that my final result won’t contain the date information since I want the overall lifetime value, I’d only aggregate it by the Company field. Or am I missing something in your answer?

Thank you!

Hi @Massi. You can combine Level Aware Calculations to achieve what you are looking for. The two calculations should be what you are looking for.

Avg Daily Purchases Amount per User =
avg(sumOver({PurchaseAmount_Clean},[User],PRE_AGG)/sumOver({Daily_Active},[User],PRE_AGG))