+ Reply to Thread
Results 1 to 4 of 4

Digital analytics conversion roll up question

  1. #1
    Registered User
    Join Date
    05-08-2018
    Location
    Vancouver, Canada
    MS-Off Ver
    Ofice 2013
    Posts
    11

    Digital analytics conversion roll up question

    Hello excel community,

    I need some help rolling up some Ecommerce numbers for my company. I'm not sure how best to do this math to figure out the conversion rate for one of the companies regions.

    Essentially, I have to add up/roll up the KPI's of many companies into one file. My question: what is the most accurate way of getting the full picture of conversion rate? I have attached an example of the file that I'm working on.

    As an example, I highlighted cell AB16 in the BUD2020 tab in the attached file. This is the eventual cell that I need to populate with the information from the tab 'company 2 APAC'.

    I could simply take an average formula for all of the conversion rates in column L, I believe however this gives an inaccurate picture since the conversion rates in the middle for crows 14 to 25 are very high but the gross order sales for them are quite low. I wouldn't want this to affect the top line conversion as it would come in too high.

    Help me please!!! If you need more information or for me to clarify let me know.

    Thank you excel community. You guys are my saviors.
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,518

    Re: Digital analytics conversion roll up question

    One way would be to weight the data. i.e.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    this returns an average of 1.45 compared with an average of 1.35 if rows 14:25 are deleted altogether.
    i.e. it still uses all your data but proportionately reduces the overall average for the extremes.

    If you wanted to return the 1.35 which excluded all the rows then it would be necessary to determine some rule that would exclude those particular rows.
    Another option would be to exclude the extremes at both end by picking thsoe values within the 10th-90th percentiles
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    4,546

    Re: Digital analytics conversion roll up question

    You could simply do...
    =SUM(J:J)/SUM(I:I)

    Which should give more accurate representation of overall conversion rate.
    Approx. 0.516%
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  4. #4
    Registered User
    Join Date
    05-08-2018
    Location
    Vancouver, Canada
    MS-Off Ver
    Ofice 2013
    Posts
    11

    Re: Digital analytics conversion roll up question

    Thank you everyone! This helped a lot

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1