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!