Creating Google Slides with Python

07.11.2020 — Tutorial, Automation — 2 min read

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.

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.

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:

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.