This is something crucial that Klipfolio is lacking. Are there any plans to implement such a feature?
Seeing that this post is from 2014 probably not?
This is something that I have hoping for ever since I started using Klipfolio.
Hi Thuong, and The Reference Admin,
Thank you for adding your voice to this enhancement request. Can you share some additional context about your situations? We would like to better understand your use cases.
- What are you trying to do?
- How does this weave into your use of Klipfolio?
- What kind of impact would this kind of feature have for you?
Any additional context you can share would be very much appreciated!
In the meantime, I will add your names to an enhancement request.
I think the original author has outlined the problem very precisely.
It is essentially something that I have encountered in particular with drill-down tables.
Let's say I have a table that shows the performance of a PPC campaign on a daily basis. The data can also be presented as weekly or monthly. One of the metrics in the table is the average position.
When I want to present this metric in the weekly or monthly view, the Sum or Average option don't do the job. It needs to be a weighted average, calculated in excel as:
=Sumproduct(sessions per day, average position per day) / sum(sessions per day)
By now, I have figured out that this can be calculated in Klipfolio, as implied by the original author. However, the solution to this problem is not very intuitive. First of all the formula is rather complex. Second, additional columns need to be created and set up to hide and or show in certain drill-down views.
I envision a solution where users can find a weighted average option available for tables, where a user can simply specify which column contains the weights.
It's basically what Thuong says. For ratios such as CTR and CPC you can't just use AVERAGE in the 'Other Columns' menu. If you have two lines, one with CPC of € 100 but only 1 click and one with CPC of € 1 but 50 clicks, Klipfolio will show it as an average of €50.5. The weighted average however is €2.94. So now I hide ratio columns when users are in the top level of the table.
This would really enhance some of my dashboards so I'm really enthousiastic about your response!
Roel Peters (The Reference)
Hi Roel, Thuong,
Thank you for the additional context and your use cases. I've added them to the enhancement request.
I need to add weighted averages to my dashboard. Has anything been done to address this issue?
I have exact same issue with Roel (The Reference), there are some calculations that cannot be done by SUM or AVERAGE in the result row. For example , I have a col for revenue, a col for sessions, I would love to show: RPS = revenue / sessions, I can simply add a new col by referencing to the two columns, however in the result row of the new column I cannot simply use SUM or AVERAGE since we cannot just sum or take average if we want to find the "real" overall RPS across the period, so I can use a CUSTOM formula in the result row: SUM(!revenue)/ SUM(!sessions), this works fine on a regular table. Doesn't work for a table with drill down, the result row shows the same values on all levels of the drill down, SUM(!revenue) will sum all rows regardless of the drill down level.
@klipfolio this continues to be a HUGE problem! I also cannot figure out how to hack a fix by doing a sumproduct() calculation as described by Thuong.
My data is daily Google Analytics data grouped by channel with dimensions for sessions and avg session duration.
Col A: Date
Col B: channelGrouping
Col C: sessions
Col D: avg session duration
This data is not rolled-up by channel-grouping since I'm showing daily sparklines for each channel.
Here's how I'm calculating "total sessions by channel" across dynamic start and end dates:
And here's my calculation for "average session duration by channel":
but as noted by other Klipfolio customers above, this calculation gives an unweighted average of average session durations by channel, which is a highly misleading number.
So here's my question: How can Klipfolio calculate the weighted average value of session duration, which is effectively...
Sumproduct(sessions per channel per day, average session duration per channel per day) / sum(sessions per channel)
please advise with a formula which uses my existing formulas above. Thank you!!!
To get a weighted average session duration, you'll first need to pull the ga:sessionDuration metric from Google Analytics so you can calculate the weighted average yourself, using the same calculation as Google Analytics: ga:avgSessionDuration = ga:sessionDuration / ga:sessions. (Aside: the Google Analytics Dimensions & Metrics Explorer is a very useful site to bookmark.)
I've just posted an article on Drill Down Tables in our Klipfolio Help Center Community under the Klip Builders section. Please take a look at the Advanced Drill Down section, which describes how to aggregate ratios using formulas to ensure the data aggregates correctly at each level. In your scenario, I believe the formula to calculate average session would be something like (assuming Col E: sessionDuration and ignoring for now the SELECT by date):
E:E / LOOKUP( B:B, GROUP( B:B ), GROUPBY( B:B, C:C) )
Please let me know if any further clarification is needed.
Here is a link to the drill down example: https://support.klipfolio.com/hc/en-us/community/posts/115004673274-Drill-Down-Tables-from-Basic-to-Advanced