In this week’s Web Analytics TV with Avinash and Nick, Lisa C asked if it was possible to see a trend report for the top landing pages from SEO. Although Avinash agreed “I want this!”, he said this is one of those reports that is only possible through the API today. Since Next Analytics has that API connectivity, I thought I’d show just how easy it is to produce the desired report right in Microsoft Excel.

When we make a Google Analytics query in Next Analytics for Excel, we get to choose which analytics account, profile and segment we want. To see SEO effectiveness, we should look at the Non-paid Search Traffic segment, removing all the paid, direct and referral traffic. Then pick a time period – I chose a year of data – and if you have a busy site, increase the Maximum Number of Rows appropriately – I chose a million rows. Note that this is well in excess of the API’s 10,000 row limit per query. Next Analytics automatically makes multiple queries to get all your results downloaded. If you have a lot of pages and pick a really big number, you might want to turn off the automatic preview.

The next step is to pick the dimensions and metrics of interest; in this case that would be date and landing page. Notice that you can’t pick the metric ‘Visits’ – it is greyed out. This is because the API has restrictions on valid combinations of dimensions and metrics, which Next Analytics respects. We’ll use ‘Entrances’ instead.

Clicking on the Analyze button performs the query and pops us into analysis mode. Here we see that Next Analytics automatically pivoted the data by date, giving us a worksheet of trends. By changing the Date Format to show year and month, the trend data is automatically regrouped into monthly columns for a nice summary table.

Moving to the Analyze tab, we can quickly create a Summary Column with the total or ‘Sum’ for the year, and sort that column in descending order, giving us our top landing pages in order.

At this point, your landing page trend numbers are sitting in an ordinary Excel workbook where you can view, manipulate, chart, or further analyze the results. You could also save the analysis so it can be easily refreshed next month.

Job done. Lisa and Avinash would be pleased.

This example just barely touches on the capabilities of Next Analytics. Read more in other articles, or just download it and see for yourself. The free trial is time limited but otherwise fully functional.