A How To guide for downloading your Google Analytics data into your SQL Server database.
Have you been looking for a way to download your Google Analytics data into your SQL Server database? Are you using Microsoft SQL Server 2005, 2008, 2008R2, 2012 or 2014? Do you have skills in SQL Server Integration Services? Then I'm here to help you!
TARGIT has recently released a new version of the Google Analytics SSIS Data Reader and Connection Manger
used with TARGIT BI Accelerator for Google Analytics
. TARGIT BIA 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. You can integrate and analyze web data alongside your current company data at the click of the button.
Don't have TARGIT for Google Analytics? You can give it a test drive in the free trial.
Or you can download the TARGIT Data Reader for Google Analytics for free.
For this example, I will be using SQL Server Business Intelligence Development Studio.
First, create a new integration services project.
Data Flow Task
On the control flow tab, drag over a Data Flow Task and double click on the new 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. More about this in the Authenticate section below
- Dimensions and metrics: 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 for dynamic date handling
- Number of dates to loop over: 0 will prevent looping
- Number of rows to request for each page: You can request a maximum of 10,000 rows per request page
- Columns: This page will show you the columns that will be returned. TARGIT automatically adds the 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 the preview is an estimate and therefore not 100% the same as what the package will return when executed.)
TARGIT Data Reader must be granted access to your Google Analytics data. Google recommends using Open Authentication, which is a secure method for accessing data. You do not need to supply TARGIT with your login and password. You need only grant the application access to your data. This means you login with a Google account that has access to the Google Analytics account containing the data you wish to access.
Google then tells TARGIT that you have access. All access control is between you and Google. You can tell Google at any time that you no longer want TARGIT Data Reader to have access to your data by going to the App Settings on your Google account and removing access. The access token granted is only found on your machine. This access isn’t sent to TARGIT, so data can only be requested using TARGIT Data Reader for Google Analytics.
After you click accept, TARGIT will request a list of the Google Analytics accounts you can access.
Check the profiles you would like to extract data from. You can select one or more profiles. TARGIT automatically adds the profile ID to your request so you can easily keep track of which profile each row of data comes 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 for a query. This is a limitation by Google and therefore can't be bypassed.
Google releases new dimensions and metrics from time to time, so we periodically release a new version of the TARGIT Data Reader for Google Analytics after testing each new addition to the system.
TARGIT does not automatically fetch the latest dimensions and metrics from Google. At TARGIT, we prefer to test all changes first, then release a new version when we have made sure they work properly, ensuring a stable environment for our users.
In order to request data from the API, you need to first set the dates for which you want data.
You can make a request for specific dates.
You can set up SSIS variables to create your dates dynamically. For example, set the SSIS package to auto-detect "today" and "today -1", and the system will automatically fetch data for that period.
There are two settings that you can use to improve performance of the system.
There is a known limitation with large data sets using the Google Analytics API. If there is too much data, the data returned is potentially sampled. As I mentioned above, data sampling returns data that is close but not 100% accurate. In order to prevent sampling, we have added the ability to loop over days.
Here's how it works: If 6 days of data is requested and looping days have set to 1, the Data Reader will automatically send one request for each day instead of one query for the entire date span. Please note that you are limited to 10,000 unique requests per day per profile, so if you go over 10,000 requests, you won't be able to make any more for 24 hours. Therefore, use this feature carefully.
If the request you are making returns a total of 100,000 rows, you can request it in chunks of up to 10,000 rows from the API. In other words, it will take 10 requests for the API to return all the data. The higher the number, the more system resources (memory) will be needed, but the number of queries required will be lower. You can tune this number to find the best mix of performance and system resources.
Once you have set up your request, check the columns panel to see the names and the data types. As you can see, TARGIT automatically adds a Profile ID column. This will help you keep track of your data in the event you set up more than one profile in the connection manager.
Preview is also a good way to ensure that your data is returning correctly. Preview doesn’t return all of the data. It just returns a preview chunk, so don’t use it to double check numbers.
If you have a Business Intelligence system in your company or are considering it, you should have a look at TARGIT BIA 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, or 2014 database, then the TARGIT Data Reader might be the simpler solution for you.
TARGIT Data Reader for Google Analytics is currently free to use, but to be entitled to full support, you need to be a TARGIT customer.
Please note that all data is secure and stays only on your system. TARGIT has no direct access to any of your data.
You can download the latest version of the TARGIT Data Reader (including Connection Manager) directly from the TARGIT Store.