Yogi Land

Many of our clients use Excel to manage their ad campaigns, visualize marketing performance, and perform complex data analysis. Most analysts use the Google Analytics Export feature to manually export their report data to CSV files. Then they import the CSV file into Excel. No longer! Now, with the Google Analytics API you can bypass this manual step and export Google Analytics data directly from within Excel! Once you've set it up, there's no need to visit the Analytics reports to view data in Excel.

Thanks to a variety of developers, here are four solutions that can transform you from a reporting monkey to an analysis ninja (as Avinash would say).

• VBA Macros - The simplest solution of them all. Mikael Thuneberg's page explains how to make API requests directly from Excel using VBA Scripts and includes a pre-built Excel worksheet to get you started.
  
  
• The Tatvic Excel Plugin - Another easy-to-use plugin for Windows users that supports both Excel 2003 and Excel 2007. To get started you download the plugin then register to use the tool. Its simple UI helps you build complex queries and get data from Analytics right into your Excel worksheet.
  
  
  Tatvic's Plugin Query Builder
  
  
• Excellent Analytics - Is an open-source initiative by Mark Red and Dropit. This Excel 2007 plugin works on Vista/XP and comes with a query builder to help you create Google Analytics queries and pull data right into Excel. Webanalytics.info put together a great step by step tutorial to get started using this plugin.
  
  
  Excellent Analytic's Query Builder Interface
  
  
• ShufflePoint - Works somewhat differently than the solutions above. ShufflePoint has developed a query language that works with the Google Analytics API to achieve common tasks, such as defining the last 30 days as a date range. One then uses this language to construct an Analytics Data Export API query either by navigating to a URL within Excel, or by using their web-based query builder, then importing this data into Excel. This process allows the ShufflePoint solution to work across most versions of Excel, as well as Powerpoint, and iGoogle gadgets.
  
  
  Shufflepoint's Web Based Query Builder
  

We continue to be impressed by the new solutions developers are bringing to market by leveraging the Google Analytics Platform. If you have developed a useful new tool or integration on top of Google Analytics, drop us an email at analytics-api@google.com. If it's innovative and useful we'll highlight it to our readers on this blog.


Posted by Nick Mihailovski, Google Analytics API Team

In your Analytics reports, you'll see some of the same entries come up again and again in your data tables. In the last Back to Basics post, we learned about 'not set' entries -- this week we'll learn what it means when you see 'direct,' 'referral' and 'organic' under the Sources column in your reports.

• (direct)[(none)] - Visitors who visited the site by typing the URL directly into their browser. 'Direct' can also refer to the visitors who clicked on the links from their bookmarks/favorites, untagged links within emails, or links from documents that don't include tracking variables (such as PDFs or Word documents).
  
  
• [referral] - Visitors referred by links on other websites. (Links that have been tagged with campaign variables won't show up as [referral] unless they happen to have been tagged with utm_medium=referral. )
  
  
• [organic] - Visitors referred by an unpaid search engine listing, e.g. a Google.com search.

Once you learn where the traffic to your site is coming from, you can start analyzing the information to make intelligent decisions for your website. For example, the Referring Sites report shows you which websites have been most effective at driving people to your site -- and which ones haven't been effective. Furthermore, if you have defined as goals the key pages you want visitors to see, you can see the percentage of visits from each referral during which the visitor saw these pages. (Just click Goals tab to see your conversion rates for each goal.)

To learn more about how to spot quality traffic from your Goals tab, please refer to this earlier Back to Basics post.

Posted by Christina Park, Google Analytics Team

The Search Engine Strategies San Jose conference is next week! This conference has become probably the most well-attended conference for anything related to doing business on the web. You'll see everything - booths representing cutting edge, fledgling technologies as well as the traditional online companies - and everyone, from experts in almost every facet of the web, to analysts, media and bloggers, to ad execs.

There's a ton of great sessions to attend and networking to be done, and a bunch of Googlers will be there including many of us on the Google Analytics team. We'll also have a booth where we will be demoing and answering your questions, along with Google Website Optimizer, AdWords, AdSense, Webmaster Tools, YouTube, and Doubleclick. You can register now and save 20% with the code SJ20SES.

Here are some sessions that might be of interest for you, oh ye of the analytical leanings:

Monday, August 10th, 2009
All day Web Analytics Association Base Camp - training on ways to optimize your website and what tools to use.

Tuesday, August 11th, 2009
10:00-11:30 - Always Be Testing - Bryan Eisenberg
11:45-12:45 - Creating a Web Analytics Culture - Feras Alhlou
1:45-2:45 - How to Turn Your Web Analytics into a Money Making Machine - Avinash Kaushik, Bryan Eisenberg, Jim Sterne