How To Identify Query Parameters With Google Analytics

Tags:

December 13, 2007

Tired of seeing “other” in your Top Content report? How about knowing how many times a page was viewed without having to wade through query parameters? If you’ve had enough of irrelevant query parameters making garbage of your Top Content report, read on!

When analyzing a website, it’s often helpful to view the core pages of the site without being distracted by the query parameters. Identifying all of the parameters also allows you to figure out which ones can be excluded. Thus reducing erroneous pageviews.

Note: This post refers to Google Analytics, but the macro would work with any similarly-formatted spreadsheet.

Why do I need a macro to identify the parameters? Why can’t I just use a regular expression to find all pages in the Top Content report that contain a question mark, and pull the parameters by hand?

If you have 10 or 20 pages with 4 or 5 parameters, this might be fine. However, we’ve seen some sites with 80,000 unique pages that contain a question mark. This would be a bit tedious to do by hand.

Step 1: Get the Macro

You can download the macro here as a .bas file. (findParameters.bas)

The macro has been tested in Office 2003 and 2007.

I would love to rewrite the details on how to install the macro, but Jeremy Aube at ROI Revolution did such a great job, I’m compelled to point you there. (Instructions)

(Where he refers to Conversion.bas, replace that with findParameters.bas)

This step is much more involved than I’m giving it credit for here. If you have trouble installing the macro, leave me a comment and I’ll add more details to this post.

Step 2: Export your Data

2.1: Go to your Top Content report.

2.2: If you have this many, change the drop down box to show 500 rows.

(Yes. Unfortunately, GA only exports the data being viewed.)

2.3: Next, click Export, then CSV

2.4: Save the file, or Open it in Excel

Step 3: Prepare the Spreadsheet

– Basically, just get rid of everything but the URLs.

3.1: Delete all of the rows down to the headers above the list of URLs

– For a month of data, this should be around row 47.

– Select the rows above the headings, right-click, and select Delete.

3.2: Do the same for columns B through G (All columns after “A”)

When finished, your sheet should look something like this:

Step 4: Run the Macro

If you followed Jeremy’s instructions perfectly, you should have a button that you can push to run the macro. Click that. *after reading the warnings below*

However, if you took a shortcut and just installed the macro, you’ll need to find and run it manually:

– Press Alt+F8 to bring up your list of macros.

– Select “ExtractParameters”, and click “Run”

It will take about 1 minute to run. When finished, you’ll have a new sheet named “Parameters” which will contain a list of all unique parameters found.

Notes and Warnings:

  • The portion of the macro which eliminates duplicate parameters takes about 1 minute to run. Your screen may flicker while this is happening. Don’t try to use Excel during this time, lest you wish to visit the task manager.
  • The macro will work with up to 500 URLs (technically rows 1-505). If you have 80,000, I recommend you create a new profile that excludes all the parameters identified here. Wait, and repeat after new data, and possibly more parameters, have been collected. It took me 3 rounds to get all parameters from this example.
  • There must only be 1 sheet in your workbook.

I look forward to everyone's’ comments (and criticism). This is my first of what I hope to be many more posts to the LunaMetrics blog.