Automate Google Analytics Integration Tasks from PowerShell

The CData Cmdlets for Google Analytics are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time access to Google Analytics.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to Google Analytics, but also an SQL interface; this tutorial shows how to use both to retrieve Google Analytics data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for Google Analytics. To access Google Analytics data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for Google Analytics.

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, you will need to 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.

PowerShell

1. Install the module:

   Install-Module GoogleAnalyticsCmdlets

2. Connect:

   $googleanalytics = Connect-GAnalytics -Profile "$Profile"

3. Search for and retrieve data:

   $transactions = "0" $traffic = Select-GAnalytics -Connection $googleanalytics -Table "Traffic" -Where "Transactions = `'$Transactions`'" $traffic

   You can also use the Invoke-GAnalytics cmdlet to execute SQL commands:

   $traffic = Invoke-GAnalytics -Connection $googleanalytics -Query 'SELECT * FROM Traffic WHERE Transactions = @Transactions' -Params @{'@Transactions'='0'}

ADO.NET

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 }