Getting Google Analytics pagination as part of a Python loop into Pandas dataframe

Viewed 68

I'm still very early in my Python journey and I'm quite sure the answer to this is obvious. I've read a good half dozen similar posts but can't quite wrap my head around what the solution might be for my use case.

What I'm trying to do: Automatically loop through Google Analytics reports day-by-day, loading each one to an external source once each day has been put into a Pandas DataFrame.

What I'm happy with: Most of the process! I'm pulling down simple queries, loading them up to the DataFrame, converting them to CSV and shooting them to where they need to go.

What I need help with: In trying to make my solution a little more fool-proof, I'm wanting to make sure that I step through any pagination that exists in the Google Analytics results, since queries are limited to 100k lines. I can't find a good example anywhere which shows how I might implement this kind of additional check and query, and how I might append that to my DataFrame.

I've cut out a lot of the bloat but essentially this is the code I'm working with. Note that I've limited page size purposely to 200 only.

import time
import pandas as pd
from pandas import json_normalize

import os
os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = "secrets.json"

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

jsonConfig = open("config.json", "r")
configRead = json.loads(jsonConfig.read())

SCOPES = ['https://www.googleapis.com/auth/analytics.readonly']
KEY_FILE_LOCATION = configRead['keyFileLocation']
VIEW_ID = configRead['gaViewId']

startDateOrig = 7
endDateOrig = 7

startDate = 7
endDate = 7

endDateScope = 3

def request1(page_token=None):
    req1 = {
        'reportRequests':
            [
                {
                    'viewId': VIEW_ID,
                    'dateRanges':
                        [{'startDate': f'{startDate}daysAgo',
                          'endDate': f'{endDate}daysAgo'}],
                    'metrics':
                        [{'expression': 'ga:sessions'}],
                    'dimensions':
                        [{'name': 'ga:date'},
                         {'name': 'ga:sourceMedium'},
                         {'name': 'ga:landingPagePath'}],
                    'pageSize': 200,
                    'pageToken': page_token,
                    'orderBys':
                        [{'fieldName': 'ga:sessions',
                          'sortOrder': 'DESCENDING'}]
                }
            ]
    }
    return req1


def initialize_analyticsreporting():
    credentials = ServiceAccountCredentials.from_json_keyfile_name(
        KEY_FILE_LOCATION, SCOPES)
    analytics = build('analyticsreporting', 'v4', credentials=credentials)
    return analytics


def get_report(analytics):
    global startDate
    global endDate
    return analytics.reports().batchGet(
        body=request1()
    ).execute()

    # Add in page_token loop here?

def parse_data(response):
    df = pd.DataFrame
    reports = response['reports'][0]
    columnHeader = reports['columnHeader']['dimensions']
    metricHeader = reports['columnHeader']['metricHeader']['metricHeaderEntries']
    columns = columnHeader

    for metric in metricHeader:
        columns.append(metric['name'])

    data = json_normalize(reports['data']['rows'])
    data_dimensions = df(data['dimensions'].tolist())
    data_metrics = df(data['metrics'].tolist())
    data_metrics = data_metrics.applymap(lambda x: x['values'])
    data_metrics = df(data_metrics[0].tolist())
    result = pd.concat([data_dimensions, data_metrics], axis=1, ignore_index=True)

    result.to_csv('result.csv', index=False, header=False)
    return result


def resetDates():
    global startDateOrig
    global endDateOrig
    global startDate
    global endDate

    startDate = startDateOrig
    endDate = endDateOrig

    return startDate
    return endDate


def main():
    global startDate
    global endDate
    global endDateScope

    resetDates()
    analytics = initialize_analyticsreporting()
    while startDate >= endDateScope:
        print(f'Running report for {startDate} days ago')
        response = get_report(analytics)
        parse_data(response)
        startDate -= 1
        endDate -= 1
        time.sleep(0.2)
    resetDates()
    print("Done now, cheers")

You might notice that I've got a comment in the get_report() function. Would that be the best place to add in a loop to pull any additional pages of data required? I'm thinking the parse_data() function would also need to be modified and possibly the response variable turned into an array/list?

Any help would be appreciated!

0 Answers
Related