Sorting with Formulas for Bounce Rate – Excel Tip

During my career I have developed a ton of Excel tricks that enabled me to mold data just the way I like it. It all began when I took an investment banking class and if you didn’t know enough hotkeys to get by without a mouse then you were shunned. During the years I was at Omniture/Adobe I was able to develop a reputation as being “Mr. Excel” which is a pretty high bar among a group of hundreds of consultants that use worksheets regularly. Users in general aren’t very good at Excel and many people don’t know all the creative things that are possible. With that in mind, this will be the beginning of many tips that help you use Excel better with web analytics so that you can spend less time gathering data and more time using data.

Automatic Sorting with Formulas

To start, let’s talk about sorting. Excel has built in ways to sort data using filter and sorting tools but they all require human interaction to make it happen. Through formulas, you can create sorting that is automatic, macro-free, and more user friendly. A great use case for this is bounce rate. In SiteCatalyst, if you were to look at the pages with the highest bounce rate you will most-likely be given some pages that have a 100% bounce rate. What a find! You now know of a bunch of pages that need to be fixed. Not so! If you look at the visits to those pages, chances are that just one person actually saw the page and bounced. Those pages probably are not worth your time fixing.

You can do quite a bit to calculate a weighted metric that takes into account volume and the rate. Another simple solution is to use a tool like ReportBuilder to automatically pull in the X most popular pages by visits and apply the formulas below to resort the data. When the report is delivered to the user, the formulas will automatically run and the user wont have to do a thing. This way you know which pages that have the worst bounce rate AND are still getting significant traffic.

Click here for an example workbook on sort with formulas and below are step-by-step instructions:

Simple Sort

After you have downloaded the workbook above follow these steps which walk through the example:

1. (Column A:C) Insert your data into the workbook sorting on your popularity metric (Visits in this case)
2. (Column G) Use the LARGE function to determine which bounce rate is the highest based on the Nth value. To calculate N I use the ROW function to get the current row number and minus the first row number. This is a good tip for creating an automatic counter so that N increases by 1 with each row.
3. (Column E) Use a combination of INDEX and MATCH to get the page name for the sorted bounce rate numbers. This works like VLOOKUP but allows you more flexibility if your lookup values aren’t on the left of your lookup table.
4. (Column F) Now that we have the page name we can just use VLOOKUP to get the rest of the metrics from the original report.

Advanced Sort

Keep in mind that the previous example works if all of your sort values are unique. In the example worksheet I have also included an advanced example where pages have duplicate bounce rate values. Not to worry! we can solve this with a few more steps:

1. Do the same thing you did for steps 1 & 2 of the simple sort
2. (Column M) Create an instance count for each value of your sort metric. Note how the beginning of the range is anchored but the end is relative. This formula lets us know how many duplicates of any given number there are as we move down the list. This count, along with the bounce rate value, creates a unique key that we can line everything up by.
3. (Column O) This is the tricky part! It is very much like what we did for step 3 of the simple sort but it uses an array function which allows us to use the bounce values AND the instance count for the lookup. To enter this function don’t just press Enter! You need to press Control + Shift + Enter. This lets Excel know that you want to use the formula as an array function.
4. (Column P) Use a VLOOKUP based on the page name to pull in the rest of your metrics.

Now you should have a beautifully resorted report. Hide the original report on some other worksheet where it is out of the way and just present the new report to the user.

Final Thoughts

This example was centered around bounce rate but it has many applications. For example, you may want to see which of your most-popular pages has the highest revenue participation per visit. Sorting is such a foundational aspect of using data that you will be able to apply this tip in many scenarios.

Let Me Know What You Think

I have been thinking about developing a class for Adobe ReportBuilder that would not only teach you the neat things you can do with that tool but would go beyond ReportBuilder to show you how to super-charge your workbook with Excel techniques that make the data much more useful. Let me know if you would be interested in such a class (kevin @ analyticsdemystified.com)