How To Identify Query Parameters With Google Analytics

/

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 everyones’ comments (and criticism). This is my first of
what I hope to be many more posts to the LunaMetrics blog.

– Jason Green

About

  • http://epikone.com/blog Justin Cutroni

    What, no support for MACS!

    Great tool you you guys, thanks for sharing this with us. As soon as I move back to Windows I’ll start using it. 🙂

    Justin

  • http://www.poverty.org.uk Guy Palmer

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

    Yes for info: if you add the parameter “&limit=X” to the URL then GA will export X rows, up to many thousand.

  • http://www.lunametrics.com/blog Jason Green

    Thanks Justin. I’ll work on a more universal solution soon. 🙂

    Guy,

    Thanks for that tip. However, I’m having trouble getting it to work. When I add the parameter to the Top Content URL, the page goes back to showing 10 results. Are there any other details we need in order to make this work?

  • http://www.poverty.org.uk Guy Palmer

    Jason,

    When you add &limit=X to the URL, it only shows 10 results on the page but the export (at least the csv export – which is the one that I use) becomes X rows.

  • http://www.roirevolution.com Jeremy Aube

    I was hoping to get a look at the source code of your macro, but for some reason it’s not letting me download it.

    At any rate, it looks like you could eliminate step 3 of your procedure by including it in the macro. Other than that, it sounds like an excellent tool.

  • http://www.roirevolution.com Jeremy Aube

    I was able to get a hold of the source code for your macro. Everything looked pretty good, but there were a few things I might change:

    1. Change

    If InStr(remainParams, “&”) > 0 Then
    findAmp = InStr(remainParams, “&”) + 1 ‘ in the remaining string, find the index of the &
    remainParams = Mid(remainParams, findAmp, Len(myCell) – findAmp + 1) ‘ lose the & and everything before
    End If

    to

    If InStr(remainParams, “&”) > 0 Then
    findAmp = InStr(remainParams, “&”) + 1 ‘ in the remaining string, find the index of the &
    remainParams = Mid(remainParams, findAmp, Len(myCell) – findAmp + 1) ‘ lose the & and everything before
    Else
    remainParams = “” ‘if no ampersand, ditch the rest
    End If

    Otherwise, if the value of the final query parameter contains a ‘=’, then you’ll get garbage included in your list

    2. Change

    myNewString = Mid(myCell, findQuestion, 100)

    to

    myNewString = Mid(myCell, findQuestion, Len(myCell) – findQuestion + 1)

    as well as similar fixes in two other locations. This gives you the whole string rather than chopping it off at 100.

    3. Your current method looks like it was intended to find a max of 10 query parameters per unique pageview. This is fine, but the current execution will only allow 7 total and leave a bit of a mess if there’s more. All 10 columns should be taken care of.

    4. You can rewrite your code so that instead of deleting each duplicate query parameter as you come to it, you keep a running list of all the ones you need to delete and then delete the lot of them at the end. I rewrote the end this way and was able to cut runtime down to a couple of seconds at most.

    Just a few things I thought I’d point out to you. You had a lot of things in your code that I’ll want to start using in my own macros, so I thought I’d return the favor 🙂

    Thanks for referencing my blog article as well 🙂

  • http://www.lunametrics.com Jason Green

    Thanks for fixing up my code Jeremy. I knew there were lots of ways to improve this macro, and I appreciate you letting us know what we could change. If you see anything else, please let us know.

  • Enrique Delgado

    One way to avoid this problem is by excluding the URL parameters that you don’t care about (like session id’s etc) from being tracked separately in analytics. It’s a setting option in the profile. See http://analytics.blogspot.com/2008/06/make-your-content-reports-more-useful.html for instructions.

  • http://fix-personal-finance.blogspot.com Kate

    Hi,I just wanted to start a new thread for all of us going through IVF this year. BTW have you tried to log on to http://fix-personal-finance.blogspot.com recently??regards Kate

Contact Us.

LunaMetrics

24 S. 18th Street, Suite 100,
Pittsburgh, PA 15203

Follow Us

1.877.220.LUNA

1.412.381.5500

getinfo@lunametrics.com

Questions?
We'll get back to you
in ONE business day.