Calculating lifetime value

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 =

I then check the average daily purchases per user:

(Purchases Daily Avg Sum / Distinct Count of Users) =
(€400 + €2,400 + €300) / 3 =

And at this point I simply multiply these two values:

Avg user active days * Avg daily purchases amount =
2 * €1,033 =

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!


can you please add date column if you have in your dataset ?
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 :slight_smile:

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 =

Lifetime Value =