"We needed a simple solution that captures information from Google Analytics in the format we wanted not any predefined way. We use Excel to create dashboards and KPIs for many of our clients and by using Supermetrics toolsets we can pre-configure automatic reports for our clients. Getting the data would normally be time consuming but using these tools allows us to save a massive amount of time. Highly recommended and very good value."
is the most flexible way of getting your business metrics into Excel and Google Spreadsheet: a custom function that you can type into directly spreadsheet cells. It's perfect for skilled Excel users who want complete control on how to display the data.
For advanced Excel users, Supermetrics Functions offers the most flexible way to integrate business data with Excel and Google Sheets reports: a custom function, getData, that you can type into spreadsheet cells, just like any of the built-in spreadsheet functions like SUM or COUNT.
We introduced this solution in 2009, and it has since been one of the most popular Google Analytics API tools. In an article in the Google Analytics blog, Google mentioned Mikael Thuneberg's VBA macros, the original version of this tool, as the #1 solution for fetching Analytics data to Excel. We have since expanded the functions' capabilities, and they can now also access AdWords, Bing Ads, YouTube and Facebook Insights.
Advantages of the custom function approach
- Speed: No need to install a plugin that slows down Excel
- Ease of use: The functions are used just the same as any of the built-in spreadsheet functions. Programming skills are not required.
- Easy sharing: The code fetching the data from Google is embedded in the workbook, so you can send the workbook to others, and they can refresh the GA data or modify the queries without them having to install anything
- Flexibility: The query parameters can be outputs from other functions or macros; execution of queries can be controlled with macros
- Compatibility: Works in all Windows Excel versions since 2003 (for Mac Excel, we provide Supermetrics Data Grabber), and in Google Spreadhseet.
- New API features immediately available: Whenever there are new metrics or dimensions added to the APIs, you can start fetching them immediately, without needing to wait for a plugin provider to add those to their tool.
- Customization: If you know VBA, you can modify the functions to fit your specific needs
- Download SupermetricsFunctions.xls or get a copy of the Google Spreadsheet template (you need to be logged into a Google Account for the link to work)
- Get an authentication token from supermetrics.com/functions-login and copy it to the sheet, and it will automatically load some of your data.
- There are some examples on how to use the functions in the spreadsheet - by modifying it you can create all kinds of reporting solutions. Just type "=getData(" and some values for the required parameters. You can see all the parametrics listed here.
Note that when using the Excel version, you need to have macros enabled in Excel's security settings for the functions to work (after changing the settings, you may have to restart Excel).