Automate Google Analytics Integration Tasks from PowerShell

Ready to get started?

Download for a free trial:

Download Now

Learn more:

Google Analytics ADO.NET Provider

An easy-to-use database-like interface for .NET applications access to live Google Analytics data (Traffic, Users, Referrals, Geo, Behaviors, and more).



Are you looking for a quick and easy way to access Google Analytics data from PowerShell? We show how to use the Cmdlets for Google Analytics and the CData ADO.NET Provider for Google Analytics to connect to Google Analytics data and synchronize, automate, download, and more.

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.

Cmdlets or ADO.NET?

The cmdlets are not only a PowerShell interface to the Google Analytics API, 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.

After obtaining the needed connection properties, accessing Google Analytics data in PowerShell consists of three basic 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 }