Most of the templates and sample reports that I’ve posted simply demonstrate that Nextanalytics can produce all the same web reports you have seen elsewhere. But Nextanalytics can go so much further; we’re not just another quick and easy reporting tool – we can run with the big dogs.

In this example, we not only create segments for Recency, Loyalty, Duration and Depth of Visit (like in the Excel-based Website Visitor Dashboard), but we compare all the various combinations thereof on a percentage of visits basis and use Excel’s conditional formatting to highlight the hotspots.

This is a 2-part blog – in this article, I will describe the technical aspects of the report. See part 2 for the interpretive description.

For this report, I took an alternative approach to creating all the various segments, leveraging Nextanalytics’ built-in search-and-replace functionality, in combination with some regular expressions, to convert each of the source data fields into the respective segment group (e.g. ‘5’ is converted into ‘2-6 days ago’). Doing it this way allows extremely large source files to be processed since the segmenting and aggregation is performed as it loads. Once the summarized information is loaded, each of the tables is a simple (for Nextanalytics) pivot function and the highlighting is ordinary Excel conditional formatting.

Segmenting with Search and Replace

We needed 5 fields from Google Analytics for this report; daysSinceLastVisit (Recency), visitCount (Loyalty), visitLength (Duration), pageDepth (Depth) and visits (our metric). Although all of them are numeric, we treat the first four as text so we can subject them to Nextanalytics’ search and replace functionality. During the load operation, contents of each text field are subjected to all the search and replace rules identified in a ‘dictionary’ file that has been constructed previously. The new values are then added to an aggregated result set, where duplicate rows are combined and the metrics summed.

The search and replace rules simply replace the number values with the respective segment text. For simple groups that contain a single number (‘0’ becomes ‘Same day’), a simple text match is performed. For ranges, I took advantage of the available regular expression substitution (e.g. number ‘3’. ‘4’ and ‘5’ are replaced with ‘3-5 page views’). Regular expression substitution can be very complicated, and there are plenty of web references (like Regular Expressions for Google Analytics).

For this specific case, all of the replacements are for numeric ranges, so I took advantage of an online utility to build the search criteria for me. The rules are contained in the engagement.csv file included in the download.

To produce each of the cross-segmentation tables, it is a simple matter of selecting the two columns of interest and pivoting one of them to the columns. If you want to see the actual script commands, unhide the EngagementAnalysis_actions worksheet. The ‘pivot’ command is ‘SwapTextColumnWithColumn’, and includes the caption of the column to be pivoted. The SortCustom commands ensure the columns are in the desired order, and the Compare command changes all the numbers to represent a percentage of the total. The results are saved to a series of worksheets (which are hidden) and the dashboard contains simple cell references to those dynamic worksheets.

Installation and Configuration

To use the report, simply download the zip file and extract the spreadsheet files (Excel 2003 and 2007 versions included) as well as the dictionary file (engagement.csv). The dictionary file MUST be placed in the user’s Documents\Nextanalytics\pages\ directory.

Enter your Google Analytics account information into the boxes on the AccountInfo worksheet. You’ll need the profile ID number for your web site — you can find that simply by logging in to the Google Analytics data connection; all your profiles and their ID numbers are shown in the drop-down. After you have entered that information, click on the Nextanalytics Refresh menu item, and watch your website metrics appear.