TARGIT Google Analytics SSIS 29


Please Share

Microsoft Sql ServerHave 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.”

 

Project

For these examples, I will be using SQL Server Business Intelligence Development Studio.

Create a new integration services project.

Create SSIS project

 

Data Flow Task

On the “Control Flow” tab dragon a Data flow task over and double click on the new Data flow task.
Add data flow to control flow

 

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.

TARGIT Data Reader for Google Analytics

 

  1. 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.
  2. Dimensions and metric:   There is an extensive list of dimensions and metrics that you can request from the Google Analytics API.
  3. Select Dates:  you have the option of specifying specific dates.
  4. SSIS Variables:  you can also set up SSIS variables and use them as the dates.
  5. Number of dates to loop over:  0 will prevent looping
  6. Number of rows to request back:   you can max request 10000 rows per request
  7. Columns: This page will show you the columns that will be returned, we automatically add profile id for you.
  8. 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.

Authenticate

oauthIn 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.

profilesSet 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

dimensionsmetricsThere 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.

 

Request Dates

In order to request data from the API you need to decide what dates you want your data.

Select Dates

You can make a request for specific dates.

SSIS variables

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.

Settings

There are two settings that you can use to improve performance of the system.

Loop Dates

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.

Max Rows

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.

Columns

columnsOnce 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.

Preview

previewPreview is also a good way to ensure that your data is returning correctly.   Preview does not return all of the data it just returns a little taste of it so don’t use it to double check numbers.


Conclusion

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.

Download

You can download the latest version of the Google Analytics SSIS Data-reader and Connection manger directly from TARGIT’s download page.   Download

Please Share


Linda Lawton

About Linda Lawton

My name is Linda Lawton I have more than 20 years experience working as a developer and a database expert. I have been working with several of the Google APIs, since 2012. I helping others in the On-Line community to develop with the Google APIs, by creating my own blog www.daimto.com. This and my presence on a number of On-Line developer forums lead me to be noticed by the Google Analytics API development team. I was nominated for and recently became one of the first Google Developer Experts for Google Analytics.


Leave a comment

Your email address will not be published. Required fields are marked *

29 thoughts on “TARGIT Google Analytics SSIS

  • Brian Brotherton

    Hello,

    I recently installed the plug in for SSIS 2008. I can see “Targit” listed in the avaliable connection managers, but I am not seeing it as an option as a source in the Data Flow toolbox. Do I need to do anything else to get it to show up?

    • Administrator
      Linda Lawton

      In the earlier versions of BIDS, the tasks must be added to the toolbox. Right click in the toolbox and, then “Choose Items…” and select the tasks in the “SSIS Data Flow Items” tab

  • Christi

    I’m trying out these tools in SSIS for SQL2014. However, trying to create the connection manager already has me stumped. As soon as it tries to authenticate, it comes back with a MethodNotFound error (‘System.Collections.Generic.IEnumerator’1 Newtonsoft.Json.Linq.JArray.GetEnumerator()’

    Any ideas?

    • Administrator
      Linda Lawton

      What do you see when you click the “new” button? At what stage do you get that error message? Do you have an older version of the JSon library in GAC? “C:\Windows\assembly\Newtonsoft.Json” should be version 4.5.0.0

      • Nguyen

        It’s me again. I just want to make sure that this happens to me after I clicked on “new” button then clicked on Accept. It’s after clicking on Accept that I got that “Error ShowAccounts: MethodNotFound error (‘System.Collections.Generic.IEnumerator’1 Newtonsoft.Json.Linq.JArray.GetEnumerator()” error like Christi.

    • Nguyen

      I have the same problem and I do have the JSon version 4.5.0.0 in the GAC. Please help as soon as you can because I’m already weeks behind on this implementation.

      Thanks,
      Nguyen

  • T Maroli

    Hi,

    I am looking to bring in data specific to a segment. Could you please let me know if this is possible with the data reader.?

    Thanks

  • Nguyen

    Hi,

    I tried to look for it but can’t find pricing information. How much would it be to use this? Also, per current company rules, we have to use .NET 4.0, does this support it or is there any way to support it?

    Thanks,
    Nguyen

  • Vitaliy Krasner

    Hi Linda, I setup global variables StartDate and EndDate in my SSIS project and choose to use “”SSIS varables”.
    However, there is nothing in “Start Date” and “End Date” comboboxes to select from and whatever variable’s names I typed it does not except. I did not find anything in property and in Advanced Editor to configure in order to force component to accept my variables. We are using SQL Server 2014 version. Please advise. Thank you

  • Jon Mal

    It seems like ga:visits and ga:NewVisits are missing from the list?
    Is there a reason for this or a way to update the list using an Expression in SSIS?

    Please let me know.

    Thanks

  • Dean

    Hi Linda,

    The TARGIT Data Reader for Google Analytics is exactly what I’ve been looking for to get our Google Analytics data into SQL Server for further analysis and reporting. I downloaded and installed in on my Windows 7 laptop with SQL Server 2005, set up the SSIS package in BIDS, created and new DB and table in our team’s SQL Server instance (hosted on Windows Server 2008 R2), tested the package, and it works great.

    However, when I installed TARGIT Data Reader for Google Analytics on the Windows Server 2008 R2 DB server, it shows as being installed properly, but it’s not working properly within BIDS on that server. Initially I found that the 4 DLL files did not get copied to the PipelineComponents folder within SQL Server, so I copied them there from the DLLs in that folder on my laptop. That allowed me to add the TARGIT Data Reader for Google Analytics Data Flow item to the list of Toolbox Items within BIDS on the server.

    Unfortunately, when trying to add the TARGIT Data Reader for Google Analytics Data Flow Source to the Data Flow Task stating that the “The component could not be added to the Data Flow task. Please verify that this component is properly installed.”. I restarted SSIS, and this error seemed to go away, but now I’m getting an error when trying to create the Connection Manager after double clicking on the TARGIT Data Reader for Google Analytics Data Flow Source. The error message states that the connection manager ‘TARGIT – Google Analytics’ is not properly installed. There is also an “Index out of range…” message on the dialog window.

    I’ve uninstalled, reinstalled, repaired, run the MSI from an Admin command prompt, run the MSI as Administrator (after modifying the registry), and while I’ve made some progress, I just can’t get this to work on the server. If I knew what the installer did, perhaps I could figure this out, but during install a Command Prompt window appears for less than a second and then vanishes, so I’m not sure what it’s doing.

    Any help you can provide getting this installed on my Windows Server 2008 R2 SQL Server 2005 instance is greatly appreciated.

    Thanks,
    Dean

    • Dean

      Update…

      I set up a new Windows Server 2008 R2 VM and installed SQL Server 2008 R2 SP3 on it. When I installed TARGIT SSIS For Google Analytics 2008, it’s not showing up in the Data Flow Items tab on the Choose Toolbox Items dialog window. Again, this is due to the 4 DLL files not getting copied to the PipelineComponents folder. Something with the MSI installer doesn’t seem to be working properly on the servers.

      Please help.

      Thanks,
      Dean

      • Dean

        I figured out the issue with the TARGIT SSIS For Google Analytics installer. It assumes that SQL Server is installed in the default directory. However, my SQL Server instances are not installed in the default directory. They are installed in a D: partition for the SQL 2005 server and an E: partition for the SQL 2008 R2 server. I created the DTS\Connections and DTS\PipelineComponents folders within the default SQL Server directory structure, and all of the DLL files were copied during installation to those folders, after which I copied them to the corresponding folders within my actual SQL Server installations on the D: and E: partitions. If you could update the installer to copy the DLLs to the actual SQL Server installation folders, that would be great. Thanks

  • Alexander Schneider

    Hi,

    I use SQL Server 2012 BI Edition and I have installed TARGITSSISForGoogleAnalytics2012 but
    I have no TARGIT Data Reader for Google Analytics in my SSIS-Toolbox and no TARGIT for Google
    Analytics – SSIS Connection Manager. Yet I have refreshed the SSIS-Toolbox and restarted Integration
    Services. Is there really a problem when SQL Server is not installed in the default path?

    Kind regards
    Alex

  • Julian Cox

    I downloaded the GA SSIS SQL Server 2016 Edition but when I open a project in Visual Studio 2015 community edition and add a data flow task I dont see TARGIT Data Reader for Google Analytics.
    Any ideas what might be wrong ?

    • Julian Cox

      The Visual Studio is running on an Azure VM. Could this be the problem?
      When I put GA SSIS SQL Server 2016 Edition on a laptop and use Visual Studio 2015 community edition I see the
      TARGIT Data Reader for Google Analytics.