How can we help?

New post
2

Weighted averages in drill-down table

Avatar
Storm Digital
None
Follow
Hi there, To my knowledge it is not possible to have weighted averages in a drill-down table, that is, I have two columns in my drill-down table and a third column which equals the ratio of the two previous columns. When I drill down the third column can only be summed or averaged by the drill-down structure instead of a weighted average. A solution I thought of was creating another table next to the drill-down table and reference to the two columns to get a weighted average. However, the reference of a drilled-down column equals its non-drill-downed version, so that still does not work. Now, a weighted average column has to be created for every drill-down level which costs a lot of computation power. Any ideas on this topic? Kind regards, Michiel
Date Votes

11 comments

  • 0
    Avatar
    Thuong Le Phong

    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?

  • 0
    Avatar
    The Reference

    This is something that I have hoping for ever since I started using Klipfolio.

    +1

  • 0
    Avatar
    Scott Lawrence

    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. 

    Cheers,

    Scott.

  • 0
    Avatar
    Thuong Le Phong

    Hi Scott,

    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.

    Thanks,

    Thuong

     

  • 0
    Avatar
    The Reference

    Hello Scott

    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!

    Best regards

    Roel Peters (The Reference)



  • 0
    Avatar
    Scott Lawrence

    Hi Roel, Thuong,

    Thank you for the additional context and your use cases. I've added them to the enhancement request.

    Cheers,

    Scott.

  • 0
    Avatar
    Eberhard von Huene

    I need to add weighted averages to my dashboard. Has anything been done to address this issue?

     

  • 0
    Avatar
    Darwin Leung

    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.

     

  • 0
    Avatar
    Seth Familian

    @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:

    groupby(select(@B:B,(between(@A:A;,(!'start-date'),(!'end-date')))),select(@C:C;,(between(@A:A,('start-date'),('end-date')))))

     

    And here's my calculation for "average session duration by channel":

    groupby(select(@B:B,(between(@A:A;,(!'start-date'),(!'end-date')))),select(@D:D;,(between(@A:A,('start-date'),('end-date')))),average)

    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!!!

  • 0
    Avatar
    Janice Janczyn

    Hi Seth,

     

    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.

    Thank you,
                  Janice

  • 0
    Avatar
    Meggan King

    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

     

Please sign in to leave a comment.