Automate Google Analytics Integration Tasks from PowerShell

The CData ADO.NET Provider for Google Analytics is a standard ADO.NET Provider that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time access to Google Analytics.

ADO.NET Provider

The ADO.NET Provider provides a SQL interface for Google Analytics; this tutorial shows how to use the Provider to retrieve Google Analytics data.

Once you have acquired the necessary connection properties, accessing Google Analytics data in PowerShell can be enabled in three steps.

Google uses the OAuth authentication standard. To access Google APIs on behalf on individual users, you can use the embedded credentials or you can register your own OAuth app.

OAuth also enables you to use a service account to connect on behalf of users in a Google Apps domain. To authenticate with a service account, register an application to obtain the OAuth JWT values.

In addition to the OAuth values, set Profile to the profile you want to connect to. This can be set to either the Id or website URL for the Profile. If not specified, the first Profile returned will be used.

1. Load the provider's assembly:

   
   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Google Analytics\lib\System.Data.CData.GoogleAnalytics.dll")
       

2. Connect to Google Analytics:

    
   $conn= New-Object System.Data.CData.GoogleAnalytics.GoogleAnalyticsConnection("Profile=MyProfile;InitiateOAuth=GETANDREFRESH;")
   $conn.Open()
   

3. Instantiate the GoogleAnalyticsDataAdapter, execute an SQL query, and output the results:

   
   $sql="SELECT Browser, Sessions from Traffic"
   
   $da= New-Object System.Data.CData.GoogleAnalytics.GoogleAnalyticsDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.browser $_.sessions
   }