Integrate Google Analytic Data to Local Database using SSIS

Introduction

As data analysts and business intelligence developers, it's part of your job to provide insights from all sources to your business. But when it comes to providing insights regarding your web sites, it can be really time consuming to login to your Google analytic account to retrieve data everyday. But thanks to a plugin called SSIS GoogleAnalyticsSource, you can create jobs to retrieve data to your local database from Google analytics.

This plugin is freely available on Codeplex.

Using the Code

Step 1

Go in to the below mentioned URL and download the SSIS GoogleAnalytics Source 2014.msi from the Codeplex web site.

After downloading SSIS GoogleAnalytics Source 2014.msi, download dimension.xml and metric.xml.

Step 2

Install the .msi file.

Step 3

Open SQL Server Data Tools for Visual Studio 2013 and create a new Integration Services project.

Step 4

First, add a data flow task to Control Flow as below image:

Step 5

Double click on the data flow task and go inside it.

Now drag and drop GoogleAnalyticsSource component from SSIS toolbox - common section.

Double click on the component and sign-in to your Google analytic account.

Step 6

Click on the New button in front of the Dimension textbox, and give it the location of the Dimension.xml file you downloaded from the Codeplex project.

Step 7

Do the same way for Metrics text box as well.

Step 8

Select the dimensions and metrics you prefer and press ok.

Step 9

Add an OLE DB Destination component to insert data to the database and select the relevant table you need this information to be inserted.

Step 10

Now run the package:

Step 11

Run a select query on the database and see whether data has been retrieved properly.

Points of Interest

Using SQL Server agent job, we can schedule these packages to run on a periodic basis to capture data from the Google analytic which can be then later used to create relevant analytic work and reports.