AutomateAnalytics.com

GA Data Grabber featured in the Google Analytics Blog

2012-01-27T09:30:00.000+02:00

The official Google Analytics blog has a new article out that shows how useful GA Data Grabber can be for automating multi-site Google Analytics reporting. The American TV network PBS is featured as a case study. Check out the article here.

Lots of thanks to Amy Sample from PBS for agreeing to the case study, and to the Google Analytics API team for releasing the article!

New Facebook page & app analytics tool now available

2012-01-13T12:18:00.000+02:00

We're happy to announce a new Facebook Module for GA Data Grabber, the #1 reporting tool for Google Analytics, AdWords & MS adCenter! The Facebook Module makes it easy to keep track of a large number of fan pages and applications, as it can fetch the numbers for dozens of pages/apps into an Excel sheet in a matter of seconds. But it's not just a data fetching tool, it also visualizes the data, automatically calculates comparisons to previous year figures, creates PowerPoint reports by the push of a button, and much more.

More information of GA Data Grabber & free trial download can be found here. Information specifically on the Facebook Module can be found here.

Check out the video and screenshots below:

Get the beta of the new GA Data Grabber

2011-12-14T17:15:00.000+02:00

Update: the new version has been released and can now be downloaded from http://www.gadatagrabbertool.com/pages/download-trial

We've been working on a big update to GA Data Grabber. We're now at the beta stage - you can download the beta version here.

I'll write a more detailed post on the new features once this version comes out of beta, but some main feature included:

A new Facebook module: gets the metrics for your Facebook fan pages and applications into Excel, making it easy to analyze. You'll get many metrics not available elsewhere.
Much easier filtering
Exporting reports to a separate Excel file
Option to use a template with the PowerPoint export feature
Improvements to the automatically created charts

There are many other changes too. But there may be some bugs left too - if you notice any, please let us know (info@automateanalytics.com). Of course, other feedback on the new features, and especially the Facebook module, is also welcome.

More information on GA Data Grabber can be found here.

We hope you enjoy the new version!

Translation Tool for Excel

2011-10-24T17:50:00.001+03:00

Two years ago, I published a small script I made for using Google Translate in Excel (http://www.automateanalytics.com/2009/09/excel-function-for-translating-text.html). I didn't give it much attention since, but apparently it's grown quite popular. Unfortunately, Google is now shutting down the free translation API that my script used, so it will stop functioning very soon. Many people contacted me regarding the issue, so I decided to make a new Excel tool for the new, paid Google Translate API.

This new tool is now available at http://translationtool.automateanalytics.com/.

The new tool is not completely free, but the usage cost is very low. It's also much faster (up to 40 times) than the old solution, can translate much longer phrases, and is more reliable. There's a free trial, so you can fully test the tool before buying. This tool is very simple to use, just select a cell range and the language you want to translate it to, press a button and the tool will conduct the translation in matter of seconds.

Warning: I've noticed someone is selling a tool called "Google Translate for Excel". This is not my tool; it looks like someone's just copied my earlier free script (which will stop functioning very soon), and put a price tag on it.

New version of the AdWords Addin for Excel

2011-10-19T13:18:00.000+03:00

We've just uploaded a new version of the AdWords Addin for Excel. This new version uses the new AdWords API version, which means you don't need to pay by usage anymore - the only cost is the annual license price (currently $199 USD per MCC). When using the functions, you no longer input an AdWords developer token as a parameter.

We've also simplified usage so that you no longer need to select a report type. Just select which fields you wish to fetch, and the tool will automatically determine the optimal report type in the background. The field names are also no longer case-sensitive. All this makes usage of the tool much easier.

Download a demo worksheet here. (Type your AdWords login credentials and the tool will automatically fetch some data for you. You can see how the functions are used and modify the parameters, and add new instances of the functions)

For more information and pricing, visit http://analyticsfunctions.automateanalytics.com/

(Using this tool requires learning some basics of array function usage in Excel. For an easier tool, you should check out GA Data Grabber)

Usage of GA Data Grabber AdWords module is now free!

2011-10-10T17:14:00.000+03:00

Google has published a new version of the AdWords API: with this new version, it's no longer necessary to purchase AdWords API units in order to run reports. We have just uploaded a new version of GA Data Grabber that takes advantage of the new API version, you can get it here. This means that from now on, the only expense of using the AdWords module is the small annual GA Data Grabber license fee (currently at $199) - no extra fee for running reports anymore! If you haven't bought a license yet, testing the AdWords module is now also much easier, as you can run as many reports as you wish during the 7 day trial period.

The old API version will be shut down at the end of February 2012. As the update includes some major changes to reporting, all existing users of GA Data Grabber AdWords module must upgrade to a newer GADG version before that date in order to be able to run new reports.

Note that using the GA Data Grabber Keyword Tool (included in the price of the AdWords module) still requires buying API units.

This update makes using GA Data Grabber as an AdWords reporting & analysis tool much more convenient, as there's no more uncertainty related to the usage cost of the tool. In addition to this, we also wanted to improve the user experience of the AdWords module. In the latest version of GADG, you no longer need to select an AdWords report type when creating a report - GADG now does the selection automatically in the background. Tbis makes creating report much easier, as you no longer need to look through different report types to find which of them include the field combination you wish to include in your report. We've also updated the list of available report fields to match the new API version.

Download the latest version of GA Data Grabber with these improvements here.

New Microsoft AdCenter reporting tool

2011-09-28T10:39:00.000+03:00

After the transition of Yahoo Search Marketing to Microsoft AdCenter (formerly known as MSN AdCenter) last year, AdCenter now serves ads to both Bing and Yahoo search engines, and currently has around 30 % of the US market. To help advertisers working with AdCenter, we are now introducing a new tool, GA Data Grabber AdCenter Module, which makes it easy to see how your ad campaigns are doing.

To test for yourself, download GA Data Grabber and log into the AdCenter module.

The simple interface of the tools makes it easy to run reports, but it's also very flexible, allowing to conduct analyses not possible via the AdCenter web interface. You can freely specify which metrics to include in a report and how to split them (for example, by time, by campaign, ad, keyword etc). Running reports for many campaigns is very fast, and you can easily duplicate reports for multiple accounts, if you are managing more than one. GA Data Grabber automatically creates charts out of the results, and you can export them to PowerPoint by the press of a button. GA Data Grabber works in MS Excel, making it easy to modify the reports and do additional calculations with the data.

GA Data Grabber is a huge time saver for anyone working with search engine marketing. The very low annual cost is easily outweighed by the time you save when using this tool to track campaign performance. And there's a free trial with full functionality, so you can test that this is true before buying.

Screenshots: report builder interface; report with campaign data on date level. Click to enlarge.

In addition to Microsoft AdCenter, GA Data Grabber can access Google Analytics and AdWords data. Click here for more information on GA Data Grabber. We're also marketing the AdCenter module separately as AdCenter Data Grabber.

If you're working with AdCenter, give GA Data Grabber a try straight away!

8 new metrics available in GA Data Grabber

2011-09-14T15:47:00.000+03:00

We're happy to announce the addition of 8 new metrics into GA Data Grabber! Google hasn't updated their API, but GA Data Grabber now uses existing dimension fields to calculate new interesting metrics, which are not available via the Analytics web interface nor through other tools that use the Analytics API. The new metrics are listed below. To start testing immediately, get the latest version from http://gadatagrabber.automateanalytics.com/.

Ecommerce, 2 new metrics:

Average number of days to transaction: how many days from the first visit does it on average take for a visitor to purchase? The bigger the average is, the more days the visitors take before making the purchase. The day of the first visit is counted as zero; if the average is zero, this means all transactions are done immediately on the day the visitor first arrives on the site.

The Google Analytics web interface does provide a simple distribution for this (in the new GA interface: Conversions -> E-commerce -> Time to purchase), but it doesn't show how this develops over time, or relative to other factors such as marketing campaigns. See for example the attached screenshot of a report showing the development of this metric by month and by traffic source - changes on the site and in campaign emails can have a significant effect on how fast visitors are converted to buyers.
Average number of visit to transaction: same as above, but counts how many visits it on average takes for a visitor to purchase.

SEO, 3 new metrics

# of referring domains with visits: shows how many different domains are sending traffic to your site. Great metric for assessing link-building efforts. Attached example screenshot shows how the number of domains linking to AutomateAnalytics.com has developed over the last two years.
# of keywords with visits: displays how many different keywords are sending traffic to your site. When plotted over time, this can be used to evaluate the success of search engine optimization. Note that to exclude PPC keywords from reports with this metric, you can use the advanced segment "Non-paid Search Traffic", or a filter string: medium==organic
# of landing pages with entrances: measures how many different pages draw visitors to the site. Query strings (starting with "?") and fragment identifiers (starting with "#") are stripped from the URLs before counting how many unique landing page addresses are found.

Content, 1 new metric:

# of pages with pageviews: how many different pages have been visited on the site? This metric can be used to evaluate how much different content is being viewed and how this develops over time. Each unique URL that has received pageviews is counted, but query strings (starting with "?") and fragment identifiers (starting with "#") are automatically stripped.

Location, 2 new metrics:

# of countries with visits: from how many countries has the site received visits? Can be used to evaluate the geographical spread of traffic.
# of cities with visits: same as above, but counts cities.

These are all included in the latest version of GA Data Grabber. You can download it here: http://gadatagrabber.automateanalytics.com/

These new metrics open up many new opportunities for using GA Data Grabber. We hope you find them useful.

GA Data Grabber now groups site content by directory

2011-08-24T16:20:00.001+03:00

One popular report of Google Analytics is Content Drilldown, which lets you see the metrics per content on different directory levels, instead of by individual URL. This report isn't available through the Analytics API directly, but we have now managed to add it into GA Data Grabber, by parsing it from the available data. There are four new dimensions available in GA Data Grabber: page directory levels 1 to 4 (you'll find these in the "Split by" dropdown menus under "Content"). These dimensions range from a more general to more detailed categorization of your content. For instance, for the URL "http://www.automateanalytics.com/2011/03/google-analytics-adwords-tool-for-mac.html", the values for these dimensions would be:

Page directory level 1: /2011/
Page directory level 2: /2011/03/
Page directory level 3: /2011/03/google-analytics-adwords-tool-for-mac.html
Page directory level 4: /2011/03/google-analytics-adwords-tool-for-mac.html

If the URL has less directory levels than specified in the dimension, then GA Data Grabber will return the full URL, but query strings (starting with "?") and fragment identifiers (starting with "#") are automatically stripped. You can of course still also access the complete URL by using the Page path dimension.

The new dimensions make it easy and fast to get a view of how the different site sections are performing. For example, take a look at this screenshot of a report that plots pageviews for directory level 1 by week:

To get your hands on the new features, go download the latest version of GA Data Grabber. There's a free 7 day trial, and with the annual price for the full version at just $139, you'd be crazy not buy it!

New free Analytics tool available: metrics by hour of day

2011-06-22T11:29:00.001+03:00

It's been some time since I've last released anything free of charge, but now's the time! By following this link, you can download an Excel tool which fetches Google Analytics metrics by hour of day, and splits that by day of week and by date. Once logged in, you can pick any of your Analytics profiles, and filter the results by an advanced segment. You can freely set a date range, and pick one of many metrics.

This works using my Analytics VBA functions, is completely free to use and does not require installing a plugin! The only thing it requires is that you enable macros in Excel's security settings (see instructions). (Currently works on Windows only - if someone's interested in contributing to the development cost, I can adapt it to Mac Excel too.)

The inspiration for this comes from an episode of Google's Web Analytics TV, where someone asked if it's possible to create a report like this, and was pointed to use a paid solution, which requires a plugin installation. In general, any dashboards like these can easily be created using my VBA functions for free with a few minutes of work (though it did take me almost an hour and half to build this one, since I wanted to add a nice interface too). It's even easier with our GA Data Grabber tool, which you can use to quickly create any of these reports, and which is not restricted to a predefined set of dashboards.

Automate Snoobi reporting in Excel

2011-05-20T13:40:00.002+03:00

Today we're happy to announce a new product, Snoobi for Excel. It's an analysis tool working in MS Excel that automatically fetches and visualizes your Snoobi data. Snoobi for Excel creates in a matter of seconds reports that would take hours to set up manually. With this tool, you can put your time into interpreting the numbers rather than waste it on manual copy-paste work. It even automatically puts together PowerPoint presentations of your reports. To read more and to download a free trial, visit SnoobiForExcel.com.

For those who've never heard of Snoobi, it's a popular web analytics tool in many parts of Europe. (Note that Snoobi is a whole separate web analytics tool and not connected to Google Analytics. For GA users, we recommend GA Data Grabber.) Snoobi was recently picked as one of the winners in the Red Herring 100 Global competition, which awards the most promising global technology companies. We love Google Analytics, but complement it with Snoobi, as it offers many nice benefits that set it apart. You can read more about Snoobi on their site.

GA Data Grabber now supports multiple logins and OAuth

2011-05-18T09:51:00.002+03:00

We have recently added several major new features to GA Data Grabber. Here's a brief overview of the most important ones. To test them for yourself, download the latest version here.

Support for multiple Analytics logins

GA Data Grabber now lets you log in with multiple Analytics users (Google Accounts) simultaneously. This is a handy feature if you don't have a single user account that has access to all the Analytics accounts you need to follow, for example, if you have a separate user account for each of your clients. When you log in with multiple users, the accounts and profiles these users have access to are all consolidated into a single profile list, and you can run reports that combine data between profiles from different users

Support for OAuth authentication

When you log into the Analytics module of GA Data Grabber, authenticating with Google Analytics is now done with OAuth. This means that authenticating happens in your Internet browser on a Google webpage, and you never need to enter your login credentials to GA Data Grabber. GA Data Grabber will never have access to your full login credentials, and its access rights are restricted to your Google Analytics data (GA Data Grabber cannot access other parts of your Google Account). You can revoke this access right at any time from your Google Account's control panel. To provide extra security, GA Data Grabber also uses SSL encrypted HTTPS connections for data transfer. These elements guarantee the your privacy and the safety of your data, and are especially important considering recent reports of vulnerabilities in Google's ClientLogin authentication, which is used by many other Analytics API tools. See GA Data Grabber's full privacy policy here.

Reports with dynamic date ranges

In addition to using fixed date ranges, you can now run reports for a dynamic date range, such as the previous full month, year to date, or last 7 days. Whenever you refresh a report that has a dynamic date range, the dates will be automatically updated to cover the chosen period. This makes it even easier to run your periodic reports with GA Data Grabber.

To test these new feature, download the latest version of GA Data Grabber here

GA Data Grabber AdWords module no longer requires a developer token

2011-04-05T14:41:00.002+03:00

With the latest version of GA Data Grabber (available here), you no longer need to have an AdWords developer token in order to use the AdWords module. This means that anyone with an AdWords account can now use this great reporting tool! When you have a GA Data Grabber subscription, you can buy AdWords API units through this page, and then use those units to run reports in GA Data Grabber.

(If you have your own AdWords developer token, you can still use that with GA Data Grabber if you wish. In this case, AdWords API usage will be directly billed by Google to your AdWords MCC account. You can enter your own developer token when logging into the AdWords module of GA Data Grabber.)

Google Analytics & AdWords tool for Mac OS X Excel

2011-03-15T09:52:00.000+02:00

Since I published the Google Analytics VBA functions for Windows Excel in 2009, lots of people have contacted me hoping to find a similar solution for Mac Excel. After several months of development and testing, we now have this solution ready: Google Analytics and AdWords data can now be fetched directly to an Excel spreadsheet on Mac Office 2011!

We have currently implemented this technology in GA Data Grabber, our sophisticated tool for fetching and visualizing Google Analytics and AdWords data in Excel. You can download the demo version here. GA Data Grabber works with the Mac Excel 2011 (Mac Excel 2004/2008 are not supported), and of course, Windows Excel 2003 and later. All the Windows functionality is available also on Mac, but fetching data is somewhat slower, due to restrictions in Mac Excel.

If you have any questions regarding our Google Analytics solution for Mac Excel, please contact us at info@automateanalytics.com.

Screenshots of GA Data Grabber working in Mac Excel

New Google Analytics data available

2011-01-27T15:37:00.004+02:00

On Tuesday, Google announced lots of new metrics and dimensions being available through the Analytics API. Most significantly:

There are 10 new AdWords dimensions available, such as the 'matched query' dimension, which shows the actual search query typed by the visitor that triggered the impression of the AdWords ad.
The 'visitors' metrics can now be split and filtered with lots of different dimensions, so it's possible to fetch, for example, visitors by traffic source. Previously the visitors metric could only be split by time.

As always when there's an update by Google, we have moved fast: these new fields are already available in the latest release of GA Data Grabber! Download the latest version here.

Of course, all of these new data fields are available also with our Excel VBA and Google Docs functions - these don't even need updating, since all new fields become immediately available when they are released by Google.

AutomateAnalytics In Twitter

2010-12-21T10:31:00.001+02:00

Our products such as GA Data Grabber are being constantly improved. To make it easier for you to see when there's something new, we will begin announcing these updates in Twitter. You can follow us at twitter.com/MTAnalytics

AutomateAnalytics wishes you a happy Christmas!

Validating EU VAT numbers in Google Docs

2010-12-01T15:47:00.005+02:00

I've recently struggled to learn the tax accounting procedures for intra-European Union trade. One part of this process is obtaining the VAT numbers of all the client companies in EU countries, which must then be verified before sending the forms to the tax office.

There's a tool for doing this validation on the EU commission site, but as it only takes one VAT number at a time, it takes quite a lot of effort to go through a long list of clients. To make this a bit easier, I created a custom function for doing the validation in a Google Docs spreadsheet; you can test it here (make a copy for your own use) To use the function in your own spreadsheets, in the menus go to Tools: Scripts: Script Editor and copy everything, then create a new script in your own spreadsheet and paste it there. It would be very easy to make a version of this for Excel too - if you are interested, please contact me by email.

The usage of the function is very simple, just input the VAT number and two letter country code as parameters. If the country code parameter is omitted, the function will assume the first two characters of the VAT number contain the country code. The function will then indicate whether the number is valid or not. For some countries, it will also show the name of the company.

The function screenscrapes the results from the EU commission tool - if that tool is changed then the function may break. Note that I don't make any guarantees for the accuracy of the results.

Demo version of GA Data Grabber for AdWords now available for download

2010-10-08T12:10:00.005+03:00

The free demo version of GA Data Grabber now supports AdWords queries in addition to Google Analytics ones. This means you can now see the kind of data and visualizations this tool will provides before buying. The limitation with the demo version is that it only fetches data for January-March 2010.

You can download the demo version of GA Data Grabber here.

If you have any questions regarding GA Data Grabber, please contact info@automateanalytics.com.

Excel functions for the Marchex API

2010-09-24T13:21:00.003+03:00

We have developed a new solution for fetching pay-per-call advertising data from the Marchex API to Excel with a set of VBA functions. This provides a great deal of flexibility for pay-per-call campaign analysis, as you are no longer tied to the built-in Marchex reports. It's also simple to combine the campaign metrics with data from other sources (for example, Google Analytics data, or sales data from your internal systems).

The functions can fetch data such as

Number of Calls
Unique Leads
Domain Referrals
Ad List
My Numbers

The data can be split by various dimensions; for example, you can fetch the number of calls by date and by campaign. There are many other dimensions available too, such as:

Call status (answered, hang-up, etc.)
Group
Tag
Caller name & number

The functions are used in the same manner as our Google Analytics and AdWords functions - they can be used directly on the worksheet like any of Excel's built-in functions. This makes it very easy to build dashboards that combine pay-per-call data with other metrics such as web analytics or PPC data - all that is required is some basic Excel skills (no VBA knowledge is necessary).

You can either buy a copy of the functions that you can use to build reports on your own, or hire us to create a complete dashboard for you. For more information and purchasing, please contact info@automateanalytics.com.

Fetch up to a million rows of GA data to Excel

2010-09-08T12:53:00.002+03:00

We've just published a new version of GA Data Grabber, which can fetch up to a million rows of data per profile. While the Analytics API has a limit of 10 000 rows per query, GA Data Grabber now has the ability to loop through up to a hundred queries and combine the data in Excel. This is especially useful when analyzing profiles with a lot of content or dynamically created URLs.

(Note that depending on the type of query, fetching a large number of rows may take a up to several hours.)

You can download the demo of the latest version of GA Data Grabber, purchase the full version, and read more about the app here. (Existing customers can get the updated version for free by contacting mikael.thuneberg@automateanalytics.com)

Check out the AutomateAnalytics Bookstore

2010-09-04T11:27:00.003+03:00

We've just released a bookstore site (using an Amazon template) with books related to the topics dealt with on this site: AutomateAnalytics bookstore. If you're looking to buy a web analytics/SEM/SEO/Excel book, please take a look. By purchasing from there you can support the free stuff we're sharing through this site. Thanks :)

Visualizing changes in popularity of site content

2010-09-02T11:48:00.005+03:00

Update: this tool is no longer being developed. For another tool that can do everything this tool can and much more, see GA Data Grabber: GA Data Grabber

I bumped into this post on the SEOmoz blog, and decided it might be a good time to make a new post about a tool we released back in January, but which most people probably don't know about - it's designed for conducting exactly the kind of analyses as described in the SEOmoz article.

If you want to see how to popularity of the different content you have on your site has changes over time, your only option is to look at charts like this, which show the pageviews for one URL (or folder containing several URLs) at a time:

These charts are good for checking the popularity of a certain content item, but not very handy if you want to get an overall picture of how things are changing. For this type of analysis, the SEOmoz article advises a manual process of downloading Excel files from GA, combining these and creating charts. Another option is to use my MT Google Analytics Visualizer tool, which works in Excel and does everything for you: it automatically downloads the data through the Analytics API, does the calculations and creates the charts - all this in a few seconds. You can then see the changes in popularity of your content with charts like this (click to enlarge):

You can select between four different detail levels for the content: plotting by the full URL, or grouping the URL's into folders of various levels. For example, for the URL "/2010/04/google-analytics-data-to-google-docs.html" the first folder level would be "/2010/", the second would be "/2010/04/", and the third would in this case be the same as the full URL, as there are no more sub-folder levels.

Plotted by folder level one, the chart above looks like this:

And the best thing is, you can plot all kinds of stuff, not just content - here's a chart with traffic source:

There are lots of other possibilities too: some are shown in the original article regarding the tool.

The tool is very affordable at just 59 US dollars, and there's a free demo version too - click this link to download the demo or purchase the full version.

AdWords API solution for Excel now available

2010-08-26T16:08:00.021+03:00

I'm excited to publish a great new solution for fetching AdWords figures to Excel through the AdWords API, something that many people have been asking for.

We've created two products based on this technology:

1. AdWords module for GA Data Grabber

With our popular report automation tool for Googla Analytics, GA Data Grabber, you now have the option to include an AdWords reporting module in addition to the regular Google Analytics module. All the advanced features of the tool, such as automated charting and PPT creation, can be used also with AdWords reporting. And the best thing is, as the data is already in Excel, it's very easy to modify the charts, do additional calculations and so on - making this a much more flexible analysis tool than anything else currently in the market. There are few screenshots on the left - click to enlarge.

To download a demo version GA Data Grabber and to purchase the full version, visit the GA Data Grabber site: http://www.gadatagrabbertool.com/

2. AutomateAnalytics AdWords functions

If you love our Google Analytics VBA functions, then this is for you: you can buy a blank workbook that has the VBA code for the AdWords functions embedded, so you can use them to build your own reports, just like with the Analytics functions. You will also get an Excel add-in that you can set up on your machine, when you have it installed then you can use the functions in all your workbooks. More information regarding this solution can be found here.

AutomateAnalytics featured in the GA blog

2010-06-10T22:18:00.003+03:00

Today, Google published an article about my business and solutions in the official Google Analytics blog - check it out here!

I'm really happy to be recognized this way :) Lots of thanks for this, and also for the earlier article published last August, to Nick Mihailovski of the GA API team - if he hadn't shown interest in my solution last summer, almost no one would probably have ever heard about it.

Lots of thanks also to Brian Clifton for dedicating several pages of the recently published 2nd edition of his great book, Advanced Web Metrics with Google Analytics, to my stuff!

Publish Excel data to the net via the Google Charts API

2010-06-07T14:10:00.004+03:00

I wrote a VBA function that creates a web chart of an Excel data range. With this, you can publish Excel data to the net instantaneously: just type the function, select the cells where the data is located, and you get the URL for the chart! The only required parameter is the data range, optionally you can also set things like the chart title.

Here's an example file with some World Cup statistics (sadly, my own country isn't on the list..)

And here's a chart I published from that Excel (click to enlarge):

Note that there's some limit on how much data you can publish with this - I don't know what it is exactly, but hundreds of rows seems to be too much.

I use Tiny URL to shorten the URLs generated by the function - I borrowed the piece of code for shortening the URLs from here.

If you find this solution useful, please make a donation to support my work: