# Digital analytics conversion roll up question

1. ## 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.

Thank you excel community. You guys are my saviors.

2. ## Re: Digital analytics conversion roll up question

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

Formula:
`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

3. ## 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%

4. ## Re: Digital analytics conversion roll up question

Thank you everyone! This helped a lot

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

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