Google Analytics serverless ETL function

How to build your own Google Analytics ETL?

Sometimes Google Analytics webpage is just not enough (or maybe you want to add some data from other sources?). Here we will try to build a working lambda function to extract data from a Google Analytics account. The function will create a csv file with transaction_id and other marketing columns of one day and one account view.

The demo is done on Mac OS Catalina.

Requirements:
1) Google Account
2) Google Analytics account
3) AWS account
4) Installed Docker

Here are the main steps:
1) Create Google API service account and download credentials file
2) Enable Google Analytics and Google Analytics reporting for this API
3) Grant Google Analytics access to your service account email
4) Create python function to write api query result to S3

STEP 1

Go to your cloud credential page: https://console.cloud.google.com/apis/credentials and click "Create Credentials" and select "Service account"

Create desired account name:

Skip part 2:

Click "CREATE KEY" and generate JSON key file. It should download to your computer.

Step 2

Go to https://console.cloud.google.com/apis/library and enable both analytics services

Step 3

Go to your Google Analytics. Go to Admin -> Account User Management and add your service account email (e.g. mytesting@local-storm-255611.iam.gserviceaccount.com) and give "Read & Analyse" permissions. 

Step 4

Create AWS account (or log in if you already have one) and create a bucket:

https://console.aws.amazon.com/s3/bucket/create?region=eu-north-1

Go to https://eu-north-1.console.aws.amazon.com/lambda/home?region=eu-north-1#/functions and press "Create Function"

 Then, name your function and choose python 3.8 runtime

Create credential file in your lambda function and copy credential json file, downloaded in the previous step.

Copy the function to lambda_function.py:

import json
import boto3
import csv
from apiclient.discovery import build
from oauth2client.service_account import ServiceAccountCredentials

SCOPES = ['https://www.googleapis.com/auth/analytics.readonly']
KEY_FILE_LOCATION = 'cred.json'

def initialize_analyticsreporting():
  """Initializes an Analytics Reporting API V4 service object.
  Returns:
    An authorized Analytics Reporting API V4 service object.
  """
  credentials = ServiceAccountCredentials.from_json_keyfile_name(
      KEY_FILE_LOCATION, SCOPES)
  # Build the service object.
  analytics = build('analyticsreporting', 'v4', credentials=credentials, cache_discovery=False)
  return analytics

def get_report(analytics, VIEW_ID, dt):
  """Queries the Analytics Reporting API V4.
  Args:
    analytics: An authorized Analytics Reporting API V4 service object.
  Returns:
    The Analytics Reporting API V4 response.
  """
  return analytics.reports().batchGet(
      body={
        'reportRequests': [
        {
          'viewId': VIEW_ID,
          'dateRanges': [{'startDate': dt, 'endDate': dt}],
          'hideTotals': True,
          'metrics': [{'expression': 'ga:itemQuantity'}],
          'dimensions': [{'name': 'ga:transactionId'},
                         {'name': 'ga:dateHour'},
                         {'name': 'ga:adContent'},
                         {'name': 'ga:sourceMedium'},
                         {'name': 'ga:campaign'},
                         {'name': 'ga:keyword'}
                         ]
        }]
      }
  ).execute()
def lambda_handler(event, context):
    VIEW_ID = str(event['view_id'])
    dt=str(event['dt'])
    dt
    analytics = initialize_analyticsreporting()
    response = get_report(analytics, VIEW_ID, dt)
    if response['reports'][0]['data'].get('rows'):
      a=[]
      for row in json.loads(json.dumps(response))['reports'][0]['data']['rows']:
          a.append(row['dimensions'])
    
      with open('/tmp/temp.csv', 'w', newline='') as file:
          writer = csv.writer(file)
          writer.writerows(a)
        
      with open("/tmp/temp.csv", "rb") as f:
          s3 = boto3.client('s3')
          fn='google/GA/' + dt[0:4] + '/' + dt[5:7] + '/' + dt[8:10] + '/' + str(VIEW_ID) + '.csv'
          s3.upload_fileobj(f, str(event['bucket']), fn)

If you would try to run the function, it won't work. Google api library is not a standard one, so we need to import it by adding a layer.

First, we will create a layer using Docker desktop. You will need to open terminal and just paste this code

cd Documents/ mkdir google_layer cd google_layer/ mkdir lambda_function lambda_layers mkdir -p lambda_layers/python/lib/python3.8/site-packages Cd lambda_layers docker run -v "$PWD":/var/task "lambci/lambda:build-python3.8" /bin/sh -c "pip install google-api-python-client -t python/lib/python3.8/site-packages/; pip install oauth2client -t python/lib/python3.8/site-packages/; exit" zip -r ga.zip python > /dev/null

After this you will find in your documents folder, google_layers folder with ga.zip

Upload this file to your lambda layers.

In Lambda menu, go to Layers and press "Create layer".

Upload a .zip file that we have created in the previous step.

Name it and choose Python 3.8 compatible runtime

Go back to your created function and click on Layers part of Designer and click "Add a layer"

Chose your created layer here and click add. Do not forget to save the function after. 

Let's test our function.

Click on test and configure your test event (paste this and change where needed):

{

  "view_id": 1111111,

  "dt": "2020-04-20",

  "bucket": "mytestbucket"

}

You can find your own view_id on this page: https://ga-dev-tools.appspot.com/account-explorer/

Finally you would need to give access to your lambda role to access your S3 bucket.

Go to https://console.aws.amazon.com/iam/home?region=eu-north-1#/home

From the left menu, select roles and click on your lambda role:

Click on "Attach policies":

Start typing "S3" and check the box on "AmazonS3FullAccess"

Now you can try to run your lambda function. If everything is ok, you will see a file created in your S3 folder.

Further notes:

• You can extract any kind of data from here. You can build a valid query using metrics and dimensions explorer https://ga-dev-tools.appspot.com/dimensions-metrics-explorer/
• You can write additional function to process the created file and insert rows into the database.
• You can write an additional lambda function, to extract all the historical data.
• Step functions could be used if you have a complex ETL process.
• Additional logic for pagination can be easily added
• Stitch Data is offering free 6 million rows per month for free, so if you are not planning a lot of row,  you might want to check them out