Have you been looking for a way of downloading your Google Analytics data into your SQL Server Database? Are you using Microsoft SQL server 2005, 2008, 2008R2, 2012 or 2014? Do you have the ability to use SSIS? Then I am here to help you.
TARGIT has released a version of the Google Analytics SSIS Data-reader and Connection manger used with the TARGIT for Google Analytics BIA system for us to use. TARGIT for Google Analytics gives you insight into the valuable sweet spot at the intersection between your current ERP or CRM data and the data harnessed from your website. Integrate and analyze web data alongside your current company data at the click of the button.”
- Try TARGIT for Google Analytics Now!
- Download Google Analytics SSIS Data-reader and Connection manger Now!
For these examples, I will be using SQL Server Business Intelligence Development Studio.
Create a new integration services project.
Data Flow Task
TARGIT Data Reader for Google Analytics
You should now be on the “Data Flow” tab. Drag a TARGIT Data Reader for Google Analytics into the window and double click on it.
- Connection: In order for TARGIT Data Reader for Google Analytics to access your Google Analytics data you must authenticate it. I will be talking more about this in the Authenticate section.
- Dimensions and metric: There is an extensive list of dimensions and metrics that you can request from the Google Analytics API.
- Select Dates: you have the option of specifying specific dates.
- SSIS Variables: you can also set up SSIS variables and use them as the dates.
- Number of dates to loop over: 0 will prevent looping
- Number of rows to request back: you can max request 10000 rows per request
- Columns: This page will show you the columns that will be returned, we automatically add profile id for you.
- Preview: Will let you test your queries to ensure that they return correctly and there are no errors. Note: data returned by preview is estimate, not 100% the same as a run of the package will give you.
In order to access your Google Analytics data the TARGIT Data Reader for Google Analytics must have access to request your Google Analytics data. Google recommends something called Open Authentication, which is a secure method for accessing data. You do not need to supply TARGIT Data Reader for Google Analytics with your login and password; you need only “Grant” TARGIT Data Reader for Google Analytics access to your data. This means you login with a Google Account that has access to the Google Analytics account that you want to access data for. Google then tells TARGIT Data Reader for Google Analytics that you have given it access. All the access control is between you and Google. You can tell Google at any time that you no longer want TARGIT Data Reader for Google Analytics to have access to your data just go to the App Settings on your Google account and remove it. The access control is only on your machine this access, which we at TARGIT have no access too. Only you can request your data using the TARGIT Data Reader for Google Analytics.
After you click accept we will request a list of the Google Analytics accounts you can access.
Set a check by the profiles for which you would like to request data. You may select more then one profile the Data Reader will automatically add the profile id to your request this way you can keep track of which profile the data came from.
Dimensions and metrics
There is a large list of dimensions and metrics that you can request from the Google Analytics API. There is a limit of 7 dimensions and 10 metrics per query. This is a limitation by Google we can’t change it.
Google releases new Dimensions and metrics from time to time we release a new version of the TARGIT Data Reader for Google Analytics after we have tested the new dimensions and metrics in the system.
We do not automatically fetch the latest Dimensions and metrics from google because we have had issues with them in the past. We decided to ensure that the system is stable for you we would rather test them first and release a new version when we are sure they work properly.
In order to request data from the API you need to decide what dates you want your data.
You can make a request for specific dates.
You can also set up a SSIS variable to create your dates dynamically. For Example have the SSIS package auto detect today for example and today – 1 and you will be able to request data each data automatically.
There are two settings that you can use to improve performance of the system.
There is an issue with large data sets and Google Analytics API, if there is too much data Google Analytics API will sample your data. Data sampling returns data that is close but not 100% accurate. In order to try to prevent sampling we have added the ability to loop over days. If I request six day’s worth of data, and set loop days to one. The system will send a request to Google for every day so instead of sending one request we will be sending six requests. There is a drawback to this, we are limited to 10000 requests per day per profile / view if you go over the 10000 request you won’t be able to make any more requests until the next day. Use this ability wisely.
If the request you are going to make will return 100000 rows, you can max request back 10000 rows at a time from the API. This means that it will take 10 request to the API to return the data. Depending upon your system the data reader can slow down if this is set to high it can also use up your quota if set to low. You can tune this number to improve performance.
Once you have set up your request. Check the columns panel to see the names and the data types. As you can see we automatically add a column called profile id this will help you keep track of your data in the event you set up more than one profile in the connection manager.
If you have a Business intelligence system in your company or are considering it you should have a look at TARGIT for Google Analytics. If you have SSIS and just want to request your Google Analytics data out into your Microsoft SQL server 2005, 2008, 2008R2, 2012, 2014, or 2016 database then Google Analytics SSIS Data-reader and Connection manger might just be a simple easy solution for you. At this time Google Analytics SSIS Data-reader and Connection manger is free to use TARGIT is not charging for it, but it is unsupported. The data is secure and stays only on your system TARGIT has no direct access to your data.
You can download the latest version of the Google Analytics SSIS Data-reader and Connection manger directly from TARGIT’s download page. Download