When you want to analyze first-time visitors to your web site, where they land first is often a key question. Maybe you had an email campaign with embedded links or you want to measure traffic from blogs that targeted special landing pages. Your challenge is to identify and report on the traffic to these special pages so you can measure the effectiveness of your efforts. With Nextanalytics for Excel and our Google Analytics connector, this is an easy task that can be fully automated.

The first step is to get the data from Google’s Data API. In this case, we’ll need the visitorType dimension as well as the landingPagePath and date. For metrics, select uniquePageviews so we don’t include multiple views from the same visitor. In the Google Analytics connector interface, include these selections as well as your desired date range, then fetch the data from Google.

Pivot by date to see trends

When Nextanalytics for Excel’s main form opens, it will take you straight to the Pivot tab. The date is presented as Columns by default (trend is the most commonly requested analysis), but you may want to change the Format on worksheet to present months instead of days, and you may want to increase the number of periods being displayed. The limit defaults to 7 so you don’t overload the spreadsheet with too many columns when you first load large date ranges.

For the Rows, we want the landingPagePath and the visitorType, and for Numerics we use the uniquePageviews. Now we can see all our data trended over time.

Filter to just first-time visitors

Since we only want to see new visitor traffic, we need to filter the data, and we can do this either by pre-filtering or post-filtering. Pre-filtering filters during the data load process, preventing the filtered data from being loaded in the first place. The filter commands are part of the Advanced script commands that appear when you select Show Advanced. Clicking on ‘Pre-Filter Text Column’ makes the actual script command visible in the text box below, where placeholders for ‘<Column>’ and ‘<String>’ and can be replaced with ‘visitorType’ and ‘New Visitor’ accordingly. This will filter out anything that does not match the string we entered. Clicking on the Add button adds the modified command to the Chosen script lines box and executes it.

Find the most popular landing pages

Before we attempt any cleanup, we should sort the list to see which landing pages are the most popular. Rather than sort by one particular month, we could create a total column and sort that, descending. This is easily accomplished by selecting the appropriate commands from the list. The new commands can be double-clicked or dragged from the list into our Chosen script lines box.

Convert technical page names for legibility

Since web site url’s are usually not built for legibility, we need to convert them into something meaningful for our report. My selecting the Fix tab, we can easily perform this activity. Turn on the Automated Search/Replace and we can enter a series of rules that will convert the landingPagePath contents as desired. For example, we can test to see if the landingPagePath is an exact match for ‘/’, and change it into ‘Homepage’. Likewise, we can change ‘/Default.aspx’ to ‘Homepage’ as well, since they are simply alternative urls for the same page. The two rows are combined into one – they way they should be.

In a similar fashion, you can work through your web site URL’s, converting your landing pages to meaningful names for reporting, and the rules are saved along the way for easy reuse. For the advanced user, Nextanalytics for Excel includes the ability to use regular expressions for this conversion. Pages that are of no interest for the analysis can be converted to a catch-all category.

Presentation and Automation

As a final step, the script is saved and can be combined with others and summarized to a common worksheet or ‘dashboard’ with the usual Excel embellishments of formatting and/or charting. The information can be updated by clicking the Nextanalytics Refresh menu item. Having personalized and automated the analysis within Excel, you can now check in regularly to see how your campaigns are performing and whether one of your pages experiences an unexpected traffic change, and your report is pre-formatted for presentation or distribution.