Excel reports built with ShufflePoint can get complex when you add lots of queries. Part of the difficulty is that Excel doesn't have good tools to look at all of the queries that you have added to your workbook at once. To troubleshoot customer workbooks we often turn to our "debug macro" which generates a new sheet named "QueryDebug" into the current workbook containing an "inventory" of sorts of all the queries in your workbook. I think any ShufflePoint customer using Excel will find this to be useful when trying to see which queries are generating error messages of missing different parameters, etc.

The macro itself can be found in a TXT file at this link.

The steps to use this macro in an Excel workbook that is giving you trouble are as follows:

1. Save your Excel file as 'macro enabled' (save as .xlsm).
2. Download the macro from the link above (right click, save link as...) and rename to querydebug.bas.
3. Open the Excel document you wish to debug.
4. Enable the developer ribbon.
5. Open the Visual Basic Studio.
6. In Visual Basic Studio choose file > import file - choose the querydebug.bas file that you saved in step 2 above, then close out the studio, no need to click save, etc...just close it.
7. Click on the Macros icon and run QueryDebug.

After completing step 7 you can sit back and watch as the Macro goes to work creating a new sheet and populating it with information about all the queries in your workbook (see screen grab below). From completed QueryDebug sheet you can see if there are missing parameters, or if a particular query is taking an extended period of time to run. You can also use it to look for situations where queries are "on top of each other" - or occupying the same cell-space within a sheet. None of the addresses in Column B (Address) should overlap. We have done some troubleshooting of that situation, and have some more advanced versions of this macro to share if needed.

Over the recent months we've been getting a lot of requests from our clients to gather data on what page an event occurred on. It turns out the solution was the just right mix of dimensions and filters.

First, a little background on Google Analytics event tracking:

Google Analytics gives you the ability to track different actions that visitors take on your website through some customization of the stock Google Analytics tracking code . Some of the most popular event tracking applications include clicks on outbound links, document downloads, video stats like plays, pauses and finishes, flash elements, AJAX elements, slideshows and load times. Granted, not all of these are relevant to what I'm about to show you but I wanted to at least introduce you to the power of event tracking in Google Analytics.

Enough background, lets get to the part where we actually learn something. Here's the question we'll be answering in this post; "What pages on my site did the most pdf/doc downloads happen on?". A simple question, yes? Not exactly.

This can be achieved in the New Google Analytics custom reports. These new custom reports allow for flat table reports and Explorer (drill down) reports. Historically, only Explorer reports were available. Using the flat table report we can have up to two dimensions on rows. First, you'll want to put this report into the right context, including only the Event Actions that you're interested in. In this case the event actions we're interested in are "PDF" and "DOC". Because event action labels can be set to whatever you want, the required filter may differ from profile to profile. If you use the same event action label for multiple events, you'll also need to include a filter for the event category that you're interested in. Next, you'll want to set the report type to "Flat Table" instead of "Explorer". Set the dimensions to "Page" and "Event Label" and the metrics to "Unique Events" and "Total Events". A screen grab of this custom report can be found below.

Having the page path next to the event label restricts the data output to only give you the number of downloads that happened for that special combination of page and event. When this report is run, it returns a table with the page paths and event labels on rows and the corresponding metrics for each unique combination on columns. We can now quickly analyze what pages have the most downloads and what downloads were most popular on that page, if there are multiple.

Each month, or whenever you would typically need to report on this data, you would need to log into Google Analytics, select the custom report, run the report, export the data to a spreadsheet and then format it to make it presentable.

Using ShufflePoint we can save a lot of time. All you need to do is setup the query, format the output and then each month click the "refresh" button. This saves you about 15 minutes for each time you would need to export this data from Google Analytics. If it's weekly, you just saved yourself 13 hours over the course of a year. Now you can use that extra 15 minutes to actually analyze the data.

Here's how we set up the query in Shufflepoint:

On Columns: Metrics - Unique Events descending and Total Events

On Rows: Dimensions - Page Path and Event Label

Filters: Dimensions - Event Action that matches "PDF" or "DOC" (or however your download actions are labeled) 

This combination returns exactly how many downloads occurred on each page and the sum of the total unique events matches the Events report. If we were to filter based on  event labels that ended in ".pdf" or ".doc" we might also get data for outbound links that end in ".pdf" or ".doc" (if you are also running outbound link tracking through events and do not have another filter for the Event Category).