There are lots of good reasons to pull Google Analytics data into spreadsheets or databases for reporting and analysis. You can get rich details not available in the web interface, you can blend it with internal data sources, you can extract large volumes of unsampled data in bulk, and so much more.
For these reasons, Google Analytics APIs have been embedded into a number of applications, including spreadsheet add-ons for Excel and Sheets, visualization tools like Tableau, reporting tools like Data Studio, and ETL software like Analytics Canvas.
The problem with using spreadsheet Add-ons and reporting tools to pull Google Analytics data
While there are a number of ways to get data from the Google Analytics APIs, not all tools are equal.
Spreadsheets are great for scratchpad work, but are difficult to maintain, slow down long before reaching their row limits, and are difficult for the average person to automate.
Reporting tools connect charts and tables to the raw data through APIs, but often have simple connectors allowing for only basic queries on small datasets.
Both types of software struggle with:
- loading and re-loading large data volumes
- combining data from multiple accounts
- processing raw data and applying a consistent set of business rules
- connecting GA data to other forms of marketing and internal data
- issues like data sampling and report query limiting
So what's an analyst to do?
Once kept hidden away in IT departments of very large enterprises, ETL software is crossing over and is now more broadly available to business users. Yet most analysts we encounter have never heard the term "ETL" and have no idea how to use ETL software to pull Google Analytics data.
What is ETL?
ETL stands for Extract, Transform, and Load.
You are probably already doing these functions as part of your reporting - downloading data from the GA Web interface (extract), manipulating the data in Excel (transform), then exporting a CSV file and up-loading it into Google Data Studio for reporting.
And you are probably performing these tasks manually, in ways that are not documented, repeatable, or automated. That's ok - that's what most analysts are doing.
If you're fortunate enough to have support from a developer or data engineer and you were able to provide a fixed set of requirements, you may have some of your processes automated with ETL scripts or macros. You likely still require IT to execute the code or to make periodic updates as your marketing campaigns and reporting requirements evolve, but at least some of your work is automated with code that performs the ETL steps.
What is ETL Software?
ETL Software is designed to perform these same data extraction, processing and loading tasks, but within a friendly user interface that anyone can use.
Within the software, users develop workflows or "data pipelines" that describe each stage in the reporting process. They do so by laying down 'blocks' and flowing data between them with 'connectors'.
Blocks perform specific tasks, like extracting data from the Google Analytics API. They perform data transformation tasks, like filtering out internal traffic or calculating conversion rates. And they perform data loading steps, like writing to landing tables within a Google Analytics data warehouse, creating and uploading a Tableau Hyper file, or updating data in an Excel dashboard.
The graphic above shows a Google Analytics data pipeline created with Analytics Canvas. It starts with a query to the Google Analytics Reporting API, transforms the data using calculate, summarize, filter and sort blocks, then exports the data into the right range of an attached Excel dashboard.
Each block in the workflow provides its own user-friendly interface that allows the analyst to customize it.
The user simply continues adding blocks to their workflow to analyse data or to process it towards a complete data set that is ready for analysis and visualization. Once configured, the rules defined within each block are locked-in and will run the same way each time the workflow runs.
This means that no human input is required to do things like clear a range in a spreadsheet or fill down formulas to the last row in the new range. It also means each step is clearly defined - you know exactly how the data is being shaped as it is prepared for analysis.
Complete workflows, like the one shown above, are ready for automation - a task that is also easily peformed by the analyst within the software.
This means that the data and dashboards are ready for consumption - there's no waiting for the report to refresh, or worse, for someone to manually perform all of the routine steps before the report is ready for distribution.
Why use ETL software instead of spreadsheets or other BI tools to pull Google Analytics data?
Almost everything that can be done with ETL software can be done manually either by running scripts, executing SQL or using a spreadsheet. As well, BI tools like Tableau and Looker can connect to lots of data sources and perform data transformations.
While some tools address some of the challenges with exporting data from Google Analytics, most of them have very basic connectors that are good for very basic reporting requirements.
There are good reasons why Google Analytics consultants, Google Marketing Platform Partners and so many others rely on Analytics Canvas to load their Google Analytics data warehouses and deliver their Google Analytics data to Excel, Tableau, Data Studio and other reporting platforms.
Top 3 reasons to use Analytics Canvas to pull Google Analytics data
Best In Class Google Analytics Data Extraction
As Google Analytics Trusted Testers since 2009 and Google Marketing Platform Technology Partners, Analytics Canvas is intensely focused on the Google Analytics and Analytics 360 Reporting APIs and is trusted by Google Analytics consultants, Google Marketing Platform Partners, Analytics360 customers, governments, agencies, and brands all around the world.
There are few, if any, solutions that go to the lengths that Analytics Canvas does to ensure that users have a robust and reliable solution that deals with so many of the challenges that Google Analytics reporting presents.
Analysts can access data from the Google Analytics Reporting APIs (V3 and V4), the Unsampled Reports API, the Multi-Channel Funnels API, and BigQuery using one of two APIs proprietary to Analytics Canvas.
Whether you're dealing with data from multiple accounts, creating complex queries with multiple segments and filters, have issues with data sampling or report query limiting, or if you simply need a reliable tool to keep your Google Analytics data up to date, Analytics Canvas is the trusted source to ensure complete and accurate loads each time.
Analytics Canvas is ETL Software!
Analytics Canvas offers all of the benefits of ETL software with a specialization in Google Analytics.
Canvas can load and maintain a Google Analytics data warehouse with dozens of tables and millions of rows and includes important meta data so that datasets can be used and re-used effectively.
It can blend in other sources of marketing and internal data from other databases, files and APIs, and it can publish updated datasets to Data Studio and Tableau, write to databases like SQL Server, RedShift and BigQuery, and deliver files through Amazon S3, Google Cloud Storage, Dropbox, and Google Drive.
And of course, the whole thing can be automated to run either on-premise or in the cloud.
Price
We don't often mention this one, but it is true. Other ETL or ELT tools, both on premise and Cloud based, are just way | more | expensive than Analytics Canvas and don't specialize in Google Analytics APIs or offer ETL capabilities like data blending or scheduling. Our pricing is fully transparent and starts at just $49/mo.
Wrapping Up
If you need to pull Google Analytics data into your spreadsheets or data warehouse and you want to ensure that the data is being pulled in accordance with practices established by data engineers who specialize in Google Analytics, try Analytics Canvas.
You can enjoy all the benefits of ETL and have access to a best-in-class Google Analytics data connector from as little as $49/mo.
Sign-up to start your free trial, contact us with your questions or book a time to connect with one of us for a demo or discussion about your Google Analytics reporting needs.