Excel Feedback Form Reporting using Google Analytics API
- Eivind Savio
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.
- Feedback Form Report Overview (Dashboard)
- Qualitative Feedback Report
- Search Phrases and Feedback Score
Feedback Form Report Overview (Dashboard)
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
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
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
- Download the Excel Spreadsheet (ZIP-file) and unzip it
- Open the Excel file and activate macros.
- Select date period on the Dashboard.
- Go to the Settings sheet, and change "Sites" from YourWebsiteName to your site name.
- 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)
- Insert your Google Analytics username (email) and password in the Settings sheet.
- 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|
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.
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.
- Free Feedback Form for Google Analytics
- How to use BTBuckets to Administrate the Google Analytics Feedback Form
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.
- Comments are closed