In this week’s Excel dashboard, we look at website landing pages – which ones are your most effective and how different visitor groups treat them. Four major segments are shown side-by-side: direct traffic, visits from search engines, visits from referring web sites, and clicks from paid advertising. Leveraging Excel’s conditional formatting makes it easy to do a quick visual comparison between the 4 groupings. It makes it pretty easy to tell which landing pages are pulling in the referrals, and which paid campaigns are being outdone by organic search.

We made a couple of assumptions for this report – first, that the purpose of your landing pages is to draw traffic in to other pages on your web site. Second, that longer visits are better. If this fits with your site goals, then this report will provide valuable insight into what draws traffic into your site and which pages keep them there.

Selecting the dimensions and metrics

To build the report, we want the Google Analytics metrics for visits, time on site, bounces and medium (the source categories). Since we’re interested in landing pages, we actually have to pull the ‘entrances’ metric instead of the ‘visits’ metric. Landing pages and visits is one of the invalid dimension/metric combinations identified by Google. Next Analytics makes it easy to see these invalid combinations by greying out the selections as you build your query. We also want to see the metrics for each of four segments, which we could get by issuing four separate queries, but we can also do it all at once if we include the ‘medium’ dimension which identifies the source category, or segment, of the traffic.

Calculating the ‘true’ time on site

For this report, we thought we’d use a definition of “true time on site” we found on the ROI Revolution blog. Simplified, they make the observation that bounced visits are recorded as having zero time on site – we don’t actually know how long they spent — so it is misleading to include the bounces in your visit total when you calculate the average time on site. More bounces means adding more zeros per visit, and that drops the average. This is an important distinction for this report because we may be comparing time on site for a segment with a high bounce rate versus one with a low bounce rate. By using ‘true time on site’, we provide a more accurate depiction of the visitor behaviour that we know. With Next Analytics, we can do the subtraction and the ratio calculation easily enough in our analytic script, and that means we have to maintain a series of cell formulas in Excel. We calculate the bounce rate as well so we have all the metrics required for display.

Lining up the landing pages for each segment

From this big list of metrics, we split the data into the four segments based on the value of the ‘medium’ dimension. Through the magic of Next Analytics (well, actually, there’s a script command that does it), we line up the rows of landing pages and add the columns of metrics for each of the 4 segments. If a particular landing page doesn’t have any data for a specific segment, then the cells are left blank. Some people think this is a BIG DEAL to do in Excel, with worksheets full of lookup formulas, but with Next Analytics it is just a couple of script commands.

Determining the ‘best’ landing page

One last challenge is to determine what order to display the landing pages in. We don’t really want to see all of them – just the best ones. The typical thing would be to show the ones with the most visits, but since we started off by assuming a good landing page draws visitors in to your site, we chose to sort it by the total of the non-bounce visits. If two pages have similar total visits, we will show the one with fewer bounces first i.e. the one that draws more visitors into the site. If you are following along in the script, we grab the non-bounce visits columns from the each of the four segments and add them together, then sort the whole list.

Our final report worksheet is a group of simple cell references to the detailed data page, and it includes Excel conditional formatting to help visualize the good from the not-so-good. For the visits and time on site columns, we use two-colors since more is better but less is not necessarily bad. For the bounce rate columns, though, high bounce rates ARE bad, so we use a red-yellow-green scheme. The result makes it easy to visually compare the performance of each landing page across the four visitor segments.

Conclusion

This is a prime example of the value that Next Analytics delivers in all of our dashboards and reports. We make it easy to get your web data, apply a few simple math functions, segment and rearrange the results and leave it in a Microsoft Excel worksheet for presentation. Next Analytics actually makes that as easy as it sounds, so you can spend time looking at the performance of your top landing pages, not struggling with formulas in a spreadsheet.

The finished dashboard is available from our main web site (http://www.nextanalytics.com) and requires a copy of the Next Analytics add-in to be installed. Trial versions are available.