GA360 BigQuery Example - Top Conversion Paths

This article shows how to use the GA360 BigQuery export to analyze the top conversion paths for a given website. The example is using BigQuery legacy SQL in conjunction with Python code to compile the corresponding statistics.

To run the example, you can use Google Datalab or any other Python environment (with slight modifications).

 

 

Step 1: Import necessary libraries

import datalab.bigquery as bq
import matplotlib.pyplot as plt
import matplotlib.ticker as mtick
import numpy as np
import sys
import re
import time
import datetime
import operator

Step 2: Get touchpoint and conversion data from BigQuery

Query the GA360 BigQuery data to get the fullVisitorId along with source/medium and timestamp for each session and join this extract with the according transactions with timestamp for each visitor. Finally group this data by fullVisitorId to get a string containing all touchpoints and conversions in the order of thier appearance.

query = """
SELECT fullVisitorId, GROUP_CONCAT(touchpoint,' > ') AS touchpointPath
FROM
(
 SELECT *
 FROM
 (
 SELECT 
 fullVisitorId,
 'transaction' AS touchpoint,
 (visitStartTime+hits.time) AS timestamp
 FROM TABLE_DATE_RANGE([myproject:123456.ga_sessions_], TIMESTAMP('2018-06-01'), TIMESTAMP('2018-06-30'))
 WHERE hits.eCommerceAction.action_type="6"
 ) AS transactionData,
 (
 SELECT 
 fullVisitorId,
 CONCAT(trafficSource.source,'/',trafficSource.medium) AS touchpoint,
 (visitStartTime+hits.time) AS timestamp
 FROM TABLE_DATE_RANGE([myproject:1234567.ga_sessions_], TIMESTAMP('2018-06-01'), TIMESTAMP('2018-06-30'))
 WHERE hits.hitNumber=1
 ) AS touchpointData
 ORDER BY timestamp
) 
GROUP BY fullVisitorId
HAVING touchpointPath LIKE '%transaction%'
"""
 
inputData = bq.Query(query).results()

Step 3: Compile the touchpoint path data

In this final step we compile the touchpoint data for each conversion and split touchpoint paths with multiple conversions. Finally we output the sorted conversion paths.

touchpointConversionPaths = {}

for dataset in inputData:
  tmpPath = dataset['touchpointPath']
  while 'transaction' in tmpPath:
    pathToTransaction = tmpPath[:tmpPath.index('transaction')+11]
    tmpPath = tmpPath[tmpPath.index('transaction')+11:]
    if pathToTransaction in touchpointConversionPaths.keys():
      touchpointConversionPaths[pathToTransaction] = touchpointConversionPaths[pathToTransaction]+1
    else:
       touchpointConversionPaths[pathToTransaction] = 1

sortedKeys = list(sorted(touchpointConversionPaths, key=touchpointConversionPaths.__getitem__, reverse=True))

i = 0

for key in sortedKeys:
  print str(touchpointConversionPaths[key]).ljust(10), key
  if i > 10:
    break;
  i += 1

print

1059       google/cpc > transaction
898        google/organic > transaction
562        (direct)/(none) > transaction
549        google/cpc > google/cpc > transaction
273        google/organic > google/organic > transaction
233        google/cpc > google/cpc > google/cpc > transaction
172        google/cpc > google/organic > transaction
124        (direct)/(none) > (direct)/(none) > transaction
104        google/cpc > google/cpc > google/cpc > google/cpc > transaction
99         google/organic > google/organic > google/organic > transaction