Excel Feedback Form Reporting using Google Analytics API

Recently I released my Free Feedback Form for Google Analytics. Although it is pretty easy to drill down into the Feedback Form data inside Google Analytics, exporting the data and putting together a useful report can take some time.

Because of this I have made a Excel Spreadsheet using the Google Analytics API, which means that you can log into Google Analytics inside Excel, and your Feedback Form Report will be automatically generated.

Excel Feedback Form Reports

The Excel sheet contains 3 reports.

  1. Feedback Form Report Overview (Dashboard)
  2. Qualitative Feedback Report
  3. Search Phrases and Feedback Score

Feedback Form Report Overview (Dashboard)

Feedback Form - Excel Dashboard using Google Analytics API

The Feedback Form Dashboard gives you an overview of which pages visitors have given feedback on, how many votes for each page, total average score and the average score for each page.

On the Dashboard sheet you also select Date Period and which Question you want reports for.

Qualitative Feedback Report

Feedback Form - Excel Qualitative Report using Google Analytics API

The Qualitative Feedback Report sheet shows visitor comments (and hopefully Why visitors has rated your page good or bad), which page the comment was given on and page rating.

Search Phrases and Feedback Score

Feedback Form - Excel Search Phrases Report using Google Analytics API

Not all visitors will tell you why they rated a page/your site good or bad. Although my Feedback Form supports settings for Mandatory Commenting, I would normally not force visitors to comment.

Analyzing Search Phrases in combination with Ratings can give you a clue about why visitors rated your pages like they did. For example, ranking and getting traffic for the wrong search phrases can result in bad ratings.

How to use the Excel Feedback Form Report

  1. Download the Excel Spreadsheet (ZIP-file) and unzip it
  2. Open the Excel file and activate macros.
  3. Select date period on the Dashboard.
  4. Go to the Settings sheet, and change "Sites" from YourWebsiteName to your site name.
  5. On the same line as your site name, insert your Google Analytics ID number. This is not your UA-XXXX-X ID, it's the ID in the Google Analytics URL (ex. https://www.google.com/analytics/reporting/dashboard?id=123456)
  6. Insert your Google Analytics username (email) and password in the Settings sheet.
  7. Done. Your report is now generated.

To the right of each report you will see which Google Analytics API metrics and dimensions that are being used. Example:

Sort results by Votes: TRUE
Metrics: eventValue&totalEvents
Dimensions: pagePath

As standard reports are sorted from highest to lowest value. If you change TRUE to FALSE the report will be sorted by URL name instead. Pages with a low score will be highlighted with red text (Excel 2007).

The Feedback Form Report works best in Excel 2007 because of the Conditional Formatting, but the other functions are working in Excel 2003. I haven't tested other Excel versions.

Fetching data from Google Analytics with Excel

Excel is fetching data from the Google Analytics API using VBA coding. These codes are written by Mikael Thuneberg, and he has explained on his blog how to use the codes. You will also find other Excel examples there.

Speedometer/Gauge Chart

Although it can be debated how useful Speedometer Charts are for presenting data, in this context I think this type of chart is doing the best job.

The Speedometer Chart I have used is made by Chandoo.org.

Related articles:

Some final words about this site and how to comment

If you look around on my site you have probably noticed that everything else is written in Norwegian. Since this blog normally is in Norwegian, and I haven't had time to implement a multilingual blog system, you find some explanations of how to comment below:

Click "Legg til kommentar" below. Than fill out your name (Navn) (Mandatory), e-mail (Epost) (Mandatory) and your website (Ditt nettsted) if you have a website. HTML formatting doesn't work in my comment field, use BBCode formatting instead. You can drag the comment field around if you want to. If you want to preview what you have written, click on "Forhåndsvis". When you are done and want to save your comment, click on "Lagre".

If you haven't had any comments approved on this blog before, I will have to approve your comment manually.

Du kan følge kommentarer til denne posten med RSS. Du må ha javascript aktivert for å kunne kommentere. Tilbaketråkk er deaktivert.

Tags : , , , , ,

  • Comments are closed
  • Martijn
  • 5/19/2011 12:45:13 PM
Hi,

It looks very promising. However, if I add my username and password to the usersettings, excell crashes.
Thanks for your feedback Martijn.

I have a never version that I will upload over the weekend (busy days ahead). Since I don't experience your problem I'm not sure if it will solve your issue. The code is mostly the same, but the new version is easier to use (it has a date picker).

I'll post an update when the new version is uploaded.

Eivind
I have uploaded the new version.

Download the new version:
http://www.savio.no/files/documents/google-analytics-feedback-form-report-v2.zip

XClick to move the comment field