SOLVED
Dynamically calculate the average unique visitor for last 7 days of any selected date range.
Hello all,
I was wondering if there is any way(Calculated metrics/ segments) of dynamically calculating the average unique visitors of last 7 days of given time period?
For example, if I select the date range as October 6th - November 4th 2024, I need to get the average of Unique visitors for last 7 days of selected period i.e. October 29th to November 4th
Average Unique Visitors formula :- (Unique Visitors for Oct 29th + 30th + 31st + Nov 1st + 2nd + 3rd + 4th) Divided by 7
Last 7 days date range only considers 7 days from current date.
Also, enabling "Make date range components relative to panel calendar" will consider the previous 7 days of selected period. i.e. in case of date range October 6th - November 4th 2024, last 7 days would be September 30th - October 6th 2024.
Thanks in advance.
1 Accepted Solution
Correct answer by
This would probably be a great use for the cumulative average function in a calculated metric. If you go into the calculated metric builder, at the bottom there is a section called "functions", in here there are a ton of different functions that you can use in your calculated metric. The cumulative average is the one that you want here.
The cumulative average calculates an average value for whatever metric you select for the last X number of rows. In the screenshot below, this is how to build it. Under number you would put 7, because you want a rolling 7 day average. This means that for each row, it is going to calculate the average of the last 7 rows. Then you put in the metric that you want to average, in this case you mentioned unique visitors.
Once you put that in your table, put it against the dimension day. This way it uses the days to calculate the average. The table below is demo data, but it should still give you a good idea of how this works. For my date range I just did November 2024. So the first row, Nov 1, the average is the same as the unique visitors because there is only 1 row. Nov 2 it takes the average of Nov 1 and Nov 2. And so on. By the time we get to Nov 7, it now has 7 rows of data to average. So Nov 7, and every row after that will be the average of the last 7 days (the current row + the 6 previous ones).
The cool thing about cumulative averages is that it works based on the time dimension (in this case, days). It depends on the value in the dimension, not the order of it. What that means is that you can have the days in order like in the screenshot above, or you can sort by another column, having the days not in order. It will still calculate properly. If you look at the screenshot below, you can see Nov 1 is now on row 18, but it is still only calculating the average based on the single day. If you look at the values in the screenshot above and below, you will find all of the days still match up in terms of values.
If you want the most recent 7 days, sort by your time dimension with the newest value at the top (or bottom) of the table, and then you can create a summary number off of it's position. Otherwise you can see all the rolling averages in a table.
Correct answer by
This would probably be a great use for the cumulative average function in a calculated metric. If you go into the calculated metric builder, at the bottom there is a section called "functions", in here there are a ton of different functions that you can use in your calculated metric. The cumulative average is the one that you want here.
The cumulative average calculates an average value for whatever metric you select for the last X number of rows. In the screenshot below, this is how to build it. Under number you would put 7, because you want a rolling 7 day average. This means that for each row, it is going to calculate the average of the last 7 rows. Then you put in the metric that you want to average, in this case you mentioned unique visitors.
Once you put that in your table, put it against the dimension day. This way it uses the days to calculate the average. The table below is demo data, but it should still give you a good idea of how this works. For my date range I just did November 2024. So the first row, Nov 1, the average is the same as the unique visitors because there is only 1 row. Nov 2 it takes the average of Nov 1 and Nov 2. And so on. By the time we get to Nov 7, it now has 7 rows of data to average. So Nov 7, and every row after that will be the average of the last 7 days (the current row + the 6 previous ones).
The cool thing about cumulative averages is that it works based on the time dimension (in this case, days). It depends on the value in the dimension, not the order of it. What that means is that you can have the days in order like in the screenshot above, or you can sort by another column, having the days not in order. It will still calculate properly. If you look at the screenshot below, you can see Nov 1 is now on row 18, but it is still only calculating the average based on the single day. If you look at the values in the screenshot above and below, you will find all of the days still match up in terms of values.
If you want the most recent 7 days, sort by your time dimension with the newest value at the top (or bottom) of the table, and then you can create a summary number off of it's position. Otherwise you can see all the rolling averages in a table.
Cumulative Average is good, but if you want to compare each row to your average for the last 7 days, that becomes trickier to do, since each row is showing the average of it's own last 7 days.
If you don't want to do Cumulative Average, and really need a "hard last 7 day average", you can create a segment for the Last 7 Full Days (using the pre-built date range, or create your own), like so:
Then you can use that segment in a calculated metric, using the Mean Function and the metric of your choice:
(You can choose to include zeros or not... I assume you won't have zero users so it shouldn't make a big difference)
This should apply the average for the last 7 days for all rows in your table:
(1437 + 1369 + 1602 + 1313 + 1363 + 1518 + 1382) / 7 = 1426
I added the mean for the entire Last 30 Days for illustration purposes.
Thanks @MandyGeorge ,
I tried this and its working for me as expected when I change the date range.
However, I have an additional question on this.
Let's say I want to calculate Weekly Active Users(Last 7 days which we created) divided by Total Unique Visitors for the entire time period I choose.
From the screenshot below I want to calculate (257/18315)*100
I used a calculated metric (Screenshot below) using Column SUM of Unique Visitors in the denominator which calculates the sum of all individual entries. If I don't use Column SUM, it takes the unique visitor count to each day for the calculation.
However, the drawback of using Column SUM is that the total unique visitor count of all column goes up more than 18315.
Is there any way to access the value 18315 in denominator dynamically instead using Column SUM?
Appreciate your help on this.
That's one of the difficult things of the column sum is that it sums up the values in every row, where as freeform table totals normally have a deduplicated value. So the column sum is going to slightly overcount your unique visitors (and depending on how long of a time period you're using, it might be a small amount or it could get larger). Although, it does divide each row against the same value, so any under/over counting is going to be consistent for all the rows, so you will still have directionally accurate results.
One thing you could try, if you're capturing a unique visitor ID, there is a function called "approximate count distinct." I don't have access to an environment that has a custom unique visitor identifier available, so I can't test this. But, if you do, try nesting an approximate count distinct inside of a column sum and see if that gives you the right number.
I will say, I'm not entirely sure the above will work and I don't have a way to test it, but if I did, I would build it like this and put your unique customer identifier in there and see if that helps with the deduplication at all.
If you are trying to divide the "last" 7 days average by the total UVs, then my solution (not using cumulative average) would work the best.... you just need to do a simple division between the two columns... each row will show the 7 Day Average / Daily UV (which isn't what you need).
However, if you make this as a duplicate table, then create a Summary Number visualization on the top cell (That takes the 7 Day Average / Total), then you can hide this table:
So you need the "Last 7 Days" from the perspective of your date range?
You should still be able to do this, using "Make date range components relative to the panel calendar"... however, this will complicate things a bit... since the "relative" is based on the first selected date.
So if I were to choose a panel range of Sept 2024, my "last 7 Full Days actually becomes the average for Aug 25-Aug 31... which I know, is not what you want...
However, if you know that you will want your table to always show from "preset ranges" (and you can define those), like "Last 30 Full Days" (and you want Sept data), if you select your panel range to Oct 1 (just a single day), your last 7 Days will become Sept 24 - Sept 30, and you can pull your "Last 30 Days" Date Range into your panel to force the table back to 30 Days worth of data.
In fact, if you make segments for different date ranges, you can provide them as a drop down on your report panel, so you don't have to be locked to 30 Days, you can provide whatever ranges you need, and so long as the panel date range is one day ahead, your 7 Days average and Report will be relative.
Related Conversations
