Introduction
Google Analytics is the most popular web analytics service that tracks and reports website traffic. In this blog post you will learn how to get data from Google Analytics using SSIS using SSIS Google Analytics Source Connector (Using drag and drop approach without Coding). This article assumes you have basic knowledge about SSIS and you have necessary tools installed – Check this if not)
For demo purpose we will load data from Google Analytics into SQL Server Database but you can use same techniques to load data into Oracle, MySQL, Excel or any other RDBMS.
Prerequisite
This article assumes you have installed following tools
- Install Client Tools for SSIS (atleast one version from below list) – Check this for detail
- BIDS for SSIS 2008 (i.e. SQL Server 2008 or 2008 R2) — OR —
- SSDT BI for Visual Studio 2012 (i.e. for SQL Server 2012) — OR —
- SSDT BI for Visual Studio 2013 (i.e. for SQL Server 2014) — OR —
- SSDT BI for Visual Studio 2015 (i.e. for SQL Server 2012/2014 or 2016)
- Install SSIS PowerPack (Click here to download) it will add new Connector in SSIS Toolbox to access Google Analytics Connector.
- You have setup correct permission for user to access google analytics data (See below section or read here)
Video Tutorial – Get data from Google Web Analytics Platform
What is Google Analytics Connector for SSIS?
The SSIS Google Analytics Source connector can be used to read data from your Google Analytics Service without learning complex APIs or coding/scripting in Python, Java, C#, etc. It has an easy to use drag and drop interface which allows you to extract large amounts of data, adjusting parameters such as dimensions, metrics, filters, sortby, date range etc. You can also use in-built reports or specify a direct query URL to get the desired data.
Read more about Google Analytics Source Connector
Features
- The intuitive user interface makes Google Analytics data integration super easy and fast without learning complex APIs or scripting languages
- Support for OAuth 2.0 Authentication and Google service accounts (*.p12 certificate file)
- Support for Proxy Server
- Easy to use interface with three different ways to query Google Analytics data: (1) Build query from UI, (2) Use built-in query templates, and (3) Use direct queries
- Support for dynamic dimensions, metrics, segments, filters and sortby fields (e.g. supply these values at runtime)
- Live preview support at design time
- Support for AdWords, AdSense, Ad Exchange and DoubleClick for Publisher (DFP)
Setup Google analytics data permission for users / service account
If you are not an admin of Google analytics and you need to access google analytics then make sure you ask admin to give you correct permission using below steps
To add/edit user permissions for Google Analytics Data perform following steps.
- Login to your Google Analytics Portal (Login using Admin account or use google user account who setup Google Analytics) Click here to go to google analytics
- Click on Admin tab > Click User management > Add/Edit user account permission
For more information you may visit official link : How to setup permission for google analytics
Add/Edit Permissions and User Account for Google Analytics Data Access (Service Account/ Normal Google Login)
How to setup permissions for Service Account (*.p12 certificate)
Service account is another method to access google data. This is useful when you don’t want to use google login to access data (Example: Some consultant working to setup automated jobs for your company and they don’t really have login for google or you don’t want to share your personal google login with them).
Service Account method (*.p12 certificate file) doesn’t require complex flow like OAuth so no more authorization prompts but make sure certificate generated for Service Account is kept secret.
To grant permission to service account so it can access google analytics data, First make sure Service Account is created and find account email (It may look like this account-1234@some-project-id-xyz.gserviceaccount.com ) . Click here to know more how to create google service account. Once service account created you may follow same steps described in above section to setup permission for Google Analytics Data.
Access Google Analytics data using OAuth 2.0 authentication protocol
Access Google Analytics Data using service account method (*.P12 Certificate file)
SSIS Google Analytics Connector – Select Web Profile
SSIS Google Analytics Connector – Using build your own query mode
SSIS Google Analytics Connector – Using direct query mode
SSIS Google Analytics Connector – Predefined Report Templates
SSIS Google Analytics Connector – Report Preview
SSIS Google Analytics Connector – Select Dimensions
SSIS Google Analytics Connector – Select Metrics
SSIS Google Analytics Connector – Select Filters
SSIS Google Analytics Connector – Select Segment
How to read google Adwords Data using Google Analytics Source
If you wish to access Google AdWords data using Google Analytics source then refer instructions listed on AdWords Tab on the UI. Once you enable integration you can view AdWords data easily without any other connector (see below)
Get Data from Google AdWords using SSIS ( use Google Analytics Source Connector)
SSIS Package – Get data from Google Web Analytics Platform and load into SQL Server
Conclusion
Integration with Google Analytics Data can be challenging unless you understand how to write C#, Java, Python code using Google SDKs. If you are not a coder or you are in time crunch then drag and drop integration in SSIS using High quality Connector using ZappySys Google Analytics Source Connector will surely make your life easy. It supports all major SQL Server versions upto SQL Server 2016 32-bit/64-bit. Try SSIS PowerPack yourself for FREE.
Keywords
Fetch data from Google Web Analytics using SSIS | Extract google analytics data using SSIS | Load data from Google Analytics without Coding | Google Web analytics data import using REST API
