Friday, July 23, 2010

How to access your Google Analytics account from Excel

Google allows you to download reports from your Analytics account as CSV sheets which you can open in any spreadsheet program like Microsoft Excel. This is useful when you wish to keep an offline record or carry trend analysis upon the data. If you do that quite often, there is a way by which you can skip the entire step of logging into your Analytics account from a browser, generating a report and downloading it to your computer. Instead, you can import that data directly into Excel from within Excel.

To do that you will need to install a free Excel plug-in called Excellent Analytics. You will also need Microsoft Office 2007 or higher and .Net Framework 4.

Once you have installed Excellent Analytics, open Microsoft Excel and locate the Excellent Analytics tab on the ribbon. Click on “Account” and enter the Google ID and password you use to login to your Analytics account.

excellent-analytics

After you have successfully logged in, a Query window will pop open where you will be able to select the data you wish to pull into Excel. I will show you how to use this tool through an example. We will use Excellent Analytics to retrieve a list of pages which received the most views during the last 7 days.

The first thing you need to do is select your website (profile) from the drop-down list on top, in case you have multiple sites tracked by Google Analytics. Also choose the start and end dates from the calendar, or click on Time Span and choose a time period.

excellent-analytics-2

Click on the Dimensions tab, expand the item “Content” and select the box next to “landing page path”

excellent-analytics-3

Move over to the Metrics tab, expand the item “Visitors” and select the box next to “pageviews”

excellent-analytics-4

Choose Maximum results to retrieve. Remember that if you choose a huge number like the default value of 10,000 it can take a long time to download. And who has the time and patience to go through ten thousand results anyway? Choose a more practical figure like 100 or 1000 or 500. I chose 100.

excellent-analytics-5

Click on the Sort by drop-down menu and choose pageviews. Then click the button next to Sort order so that it shows Descending as shown in the screengrab above.

Now all you to do is click on Execute, wait a few moments and your report is ready.

excellent-analytics-report1

Isn’t that great? If Excel fails to retrieve any data then you must have chosen the wrong combination of Dimensions and Metrics. In that case, use the Analytics API help page as a reference.

If you want to retrieve a different set of data, say, a list of keywords that visitors used to arrive at your site from search engines, then go to the Dimensions tab, expand the item “Campaign” and select the box next to “keyword”. All options you find on your Google Analytics account from the browser are available from this tool.

excellent-analytics-6

You can also filter your results using the options under the Filter tab. For example, you can ask Excel to retrieve only those results where a certain word occurs in the keyword. “Firefox” in this example.

excellent-analytics-7

You can also pull data for all visits or only new visits, visits for returning visitors, search engine traffic, direct traffic and so.

excellent-analytics-8

Excellent Analytics is a very powerful tool, and the only free Google Analytics plug-in for Excel available at the time of this writing.

3 comments:

  1. This is a good,common sense article.Very helpful to one who is just finding the resouces about this part.It will certainly help educate me.

    ReplyDelete
  2. Very good post.

    For custom report automation on Google Analytics data, I use an application that it's saving us a lot of time and money. It's is really worth checking it out: http://www.reportingsuite.com

    ReplyDelete
  3. http://excellentanalytics.com/ - this site has been hacked, at the moment.

    ReplyDelete

Popular Posts