Creating Google Slides with Python

How to create a reporting deck aggregating data from various sources, using Python and Google Slides.

How many travellers are using the application? How many of them are buying a ticket? How are they rating the app?

On a daily basis, we ask ourself these questions to see if the product is evolving in the right direction.
To keep our stakeholders informed on the trends of these metrics we share a monthly report with a comparison of the previous month.

redacted version of a typical page of the deck

Data is gathered from:

• Google Analytics for users metrics
• Our backend system for product sales breakdown
• App Store for ratings and reviews
• Play Store for ratings and reviews
• Our Customer Care for volumes of tickets managed

I use a Jupyter notebook for executing the various steps and construct the key:value pairs dictionary that will be used to fill the placeholders in the presentation template.

portion of the template used for reporting with placeholders

Shout-out to Victor Pérez for his presentation and handy package to hide the complexity of using the Google APIs.

We will use the google-api-support package to manage the authentication with google service account and the filling of data gathered inside a copy of a Google Slides template.

Google Analytics

After a brief, but painful, search for a solution to access Google Analytics data via API, I settled for a sort of a hack using Query Explorer.

First I’ve set properties and metrics I would like to use:

Start date and end date are not that important, we will update them programmatically with our request

the access token will be valid only for few hours

After running the query, you can take the access token and use it to make a request, like these:

response = requests.get(f”https://www.googleapis.com/analytics/v3/data/ga?ids=ga%{ga}&start-date=2020-{month}-01&end-date=2020-{month}-{monthLastDay}&metrics=ga%3Ausers%2Cga%3AnewUsers%2Cga%3Asessions&access_token={access_token}")

App Store and Play Store for ratings and reviews

To crawl Play Store app rating I use Google-Play-Scraper.
For iOS it is a simple Request to extract averageUserRating and userRatingCount.

Sales breakdown

This is very dependent on your backend stack, in my case I use Request to query our MongoDB for each product SKU:

def productReport(product_sku, startUnixTimeStamp, endUnixTimeStamp):
    querystring = {"product_sku":f"{product_sku}","_q":"{\"$and\":[{\"client\":{\"$regex\":\"(?i)app\",\"$options\":\"i\"}},{\"$and\":[{\"createdAt\":{\"$gt\":%s}},{\"createdAt\":{\"$lt\":%s}}]},{\"$or\":[{\"trash\":0},{\"trash\":{\"$exists\":false}}]}]}" % (startUnixTimeStamp, endUnixTimeStamp)} 
    payload = ""
    headers = {
    'accept': "*/*",
    'secret': f"{secret}"
    }
    reportResponse = requests.request("GET", url, data=payload, headers=headers, params=querystring)
    print(product_sku, reportResponse.text)
    return reportResponse.text

Putting it all together

This is where the magic happen. Take the id of the templated presentation from the url:

Place it has PRESENTATION_ID. You can define the naming structure of the new presentation document, and the id of a destination folder.

Feed it with a json (reportData) containing all the values that need to be replaced in the placeholders.

And voilà, our report is made and ready to be sent:

Automating this procedure is surely a time saving, previously the creation of this document would take 1–2 hours of copy-paste. But, above all, is an opportunity to use that time to better understand the data and deepen the interesting aspects of our business.