Running Average for a calculated field (ARPU) in a pivot table

Hi all,

I am trying to calculate the running total of the average transaction amount per user based on first purchase month, split into 30-day periods since the first transaction, so that for example I know that for users who made their first purchase in January, their average revenue (ARPU from now on), was €100 in the first 30 days, then it is €110 when taking into consideration the first 60 days and so on.
The problem is that I cannot find a way to use sort-of a running total: all I can do is to calculate the average per each separate segment but of course for the 60 day segments it should take into account all transaction up to that point, so also the ones in the 30 days segment.
Is there any way to do that?

Here is what I have done so far.
Let’s say this is my data:

TransactionID CreatedOn UserID FirstPurchaseOn Type Status Amount_EUR
0001 15-01-2022 10:10:10 123 15-01-2022 10:10:10 Buy Accepted 50
0002 12-02-2022 11:11:11 123 15-01-2022 10:10:10 Buy Accepted 100
0003 23-02-2022 12:12:12 456 23-02-2022 12:12:12 Buy Accepted 100
0004 09-03-2022 13:13:13 123 15-01-2022 10:10:10 Sell Accepted 100
0005 18-03-2022 14:14:14 123 15-01-2022 10:10:10 Buy Cancelled 100
0006 14-04-2022 15:15:15 123 15-01-2022 10:10:10 Buy Accepted 50
0006 20-05-2022 16:16:16 123 15-01-2022 10:10:10 Buy Accepted 50

So in this case I’d check the user 123 and I’d be interested into the Buy type, Accepted status transactions. As you can see the FirstPurchaseOn is not per type or anything, it already show the first completed purchase date.

So, I calculated the days spent between CreatedOn and FirstPurchaseOn, then assigned them to 30 days segments, like this:

dateDiff({FirstPurchaseOn}, {CreatedOn}, 'DD') - (dateDiff({FirstPurchaseOn}, {CreatedOn}, 'DD') % 30) +30

I then tried to come up with a formula to calculate the ARPU, something like this:

sumIf({Amount_EUR}, {Type} = 'Buy') / distinct_countIf({UserID}, {Type} = 'Buy')

Using a pivot table, it will now group the amount per 30-day segment, which means I will get the average per user for transactions made in the first 30 days, then the amount for transactions made in the following 30 days, and so on. Instead, for the second segment, it should calculate the ARPU for all transactions up to 60 days, then 90 etc.
If I were not to use a calculated field, say if I wanted the sum of amount, then I’d have no problem since I could use the native Running Total function in the pivot table.

Is there any way I can do that?
I tried giving a look to the sumOver / avgOver and distinct_CountOver functions but I couldn’t write a formula which would help me.

Thank you.

@Massi thanks for posting! Curious about this as well.

My scenario is trying to determine the rolling average of course topics completed by week and then a second column to divide the value from the prior week by the rolling average.

Hope you receive some feedback soon!

1 Like