Skip to content
Jacopo Martolini

Creating Google Slides with Python

Tutorial, Automation2 min read

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

1 sZteu2n1G0MJARPMaYy qg

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.

1 aoKLig4vBeVyvV9z03qV0Q

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.

1 OGuGkuHLKG TAOb4sf5W8Q

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:

1 0sCshGus9xhe9odraKUZKQ 1  Y4j6oKFft1JQWqW PTAgw

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

1response = requests.get(f”{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.

1 KI5ZfR9 U6ACdElx fbYGw

Sales breakdown

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

1def productReport(product\_sku, startUnixTimeStamp, endUnixTimeStamp):
2 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)}
3 payload = ""
4 headers = {
5 'accept': "\*/\*",
6 'secret': f"{secret}"
7 }
8 reportResponse = requests.request("GET", url, data=payload, headers=headers, params=querystring)
9 print(product\_sku, reportResponse.text)
10 return reportResponse.text

Putting it all together

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

1 cYCSAsxEkQbgn6EhrYpwzg

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.

1 aTxPjkXZKjVFYQGvqnNFig

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

1 OCDl2Krn1m1wzPl PXaLSw

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.

© 2021 by Jacopo Martolini. All rights reserved.
Theme by LekoArts