How to request data from Google Analytics API & print to Google Sheets


Posted by BigEyeGuy on 2018-07-15 00:56:43

When it comes to detailed reporting, it's often difficult, and sometimes impossible to grab the data you want in exactly the way you, or your client, want it. Even if you make it around the sample data issue, you can't always combine the metrics and dimensions you want. For example, it's not possible to download goal completions for each specific goal for all default channel groupings, and yet this is data many clients would probably like to see.

Now it is possible to use tools like Supermetrics to achieve the above, but if you don't mind getting your hands dirty with some code (and you love the price of FREE), the following will show you the easiest way I've found to access the Google Reporting API using python, and to then print the data you want to a Google Sheet.

Firstly you'll need to use Google's developer console to create a new project, and enable access for the following APIs. - Analytics API - Google Analytics Reporting API - Google Drive API - Google Sheets API

Then under 'credentials', you'll need to create a OAuth 2.0 client ID, with a Type of 'other'. Once done, download the json file and save it with the name 'client_secret.json' in the same folder as the python script we'll create shortly. If you need more info on this, Google's basics documentation should have the answers. You'll also need to add the email address from this json file to the view whose data you want to access, and give it read & analyse only permission.

Then go to https://console.developers.google.com/iam-admin/serviceaccounts and you should see the email you just gave read/analyse permission for. In the actions column, choose 'Create key' and download the json file. Also save this in the same folder.

Now for the script. Name it whatever you like. You need the imported libraries downloaded, so do that now using the 'pip install' command for each. Shout out to the phenomenal google2pandas library for making an online marketer's life that much easier. The 'scope', 'creds' and 'client' variables will remain unchanged.

To make naming the file easier, I define the view ID, name of the client, and month as new variables. The date is used within each query afterwards.

  
  from google2pandas import *
import xlsxwriter
import pandas as pd
import gspread
import gspread_dataframe as gd
from oauth2client.service_account import ServiceAccountCredentials

# https://github.com/burnash/gspread  -- for info
# https://www.twilio.com/blog/2017/02/an-easy-way-to-read-and-write-to-a-google-spreadsheet-in-python.html -- for info

## use creds to create a client to interact with the Google Drive API
scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']

creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
client = gspread.authorize(creds)

VIEW_ID = '12345678' 
CLIENT_NAME = 'Client1'
DATE = [{ "endDate": "2018-06-30", "startDate": "2018-06-01" }]
MONTH = 'June_2018'
  

Recalling our example query from earlier, we'd define the query as below, with default channel as our dimension, and each of the goals as our metrics.

For clarity, I've included a second example query. This one returns the number of organic events by device and by channel grouping, which again seems impossible to do using the Google Analytics GUI. This time, we use total events as our metric, and device category, event category and channel groupings as our dimensions. If you wanted to return only organic results, you could use a dimension filter, to ensure only organic results are returned, as I've done here in the orange comment.

  
  # Goals by Channel - limited to 10
query1 = {
    "reportRequests": [{
        "dateRanges": DATE,

        "dimensions":[
          {"name":"ga:channelGrouping"}],          

        "metrics": [
            {"expression": "ga:goal2Completions"}, #goal name 1 
            {"expression": "ga:goal3Completions"}, #goal name 2
            {'expression': 'ga:goal5Completions'}, #goal name 3
            {'expression': 'ga:goal6Completions'}, #goal name 4
            {'expression': 'ga:goal9Completions'}, #goal name 5
            {'expression': 'ga:goal10Completions'}, #goal name 6
            {'expression': 'ga:goal12Completions'}, #goal name 7
            {'expression': 'ga:goal13Completions'}, #goal name 8
            {'expression': 'ga:goal14Completions'} #goal name 9
            ],

        "viewId": VIEW_ID,

    }]
}

# Organic Events by Device / channel grouping
query2 = {
        "reportRequests": [{
      "dateRanges": DATE,
      "metrics": [
          {"expression": "ga:totalEvents"}
      ],

      "viewId": VIEW_ID,

      "dimensions":[
        {"name":"ga:deviceCategory"},
        {"name":"ga:eventCategory"},
        {"name":"ga:channelGrouping"}
      ],

      '''
      "dimensionFilterClauses": [{
            "filters": [{
                "dimension_name": "ga:channelGrouping",
                "operator": "EXACT",
                "expressions": ["Organic Search"]
            }]
        }], '''
  }]
}
  

Within the 'conn' variable, use your service account json file name to replace my example, then define the dataframes with your queries, as below.

We then use the gspread library to create a new Google sheet, with the name and month defined earlier used to name the file, before sharing with whomever. Now the sheet exists, we open it, and add 2 new tabs/worksheets. For each, we define the name of the tab, and their size by rows and columns.

Finally we use gspread_dataframe to add our queries/dataframes to these tabs. If you wish, you can rename the top row cells with the actual goal names, using the update_cell function from gspread.


conn = GoogleAnalyticsQueryV4(secrets='GA ClickConsult Reporting-8ae64e4dc967.json')
df1 = conn.execute_query(query1)
df2 = conn.execute_query(query2)

sh = client.create('Monthly_Report_%s_%s' % (CLIENT_NAME, MONTH))
sh.share('test@test.co.uk', perm_type='user', role='writer')

gss = client.open('Monthly_Report_%s_%s' % (CLIENT_NAME, MONTH))
ws1 = gss.add_worksheet(title='Goals by Channel', rows="1000", cols="50")
ws2 = gss.add_worksheet(title='Events by Channel', rows="1000", cols="50")

gd.set_with_dataframe(ws1, df1)
gd.set_with_dataframe(ws2, df2)

ws1.update_cell(1, 2, "Offer Funnel")
# etc
# note it's possible to format the sheets with G sheets API v4 - https://developers.google.com/sheets/api/samples/formatting

And that's it. Run the script, and if all is well, check the email you defined as the user and you'll have a link to your shiny new report.


Posted by BigEyeGuy on 2018-07-15 00:56:43