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.