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!