How to create automated marketing reports in Google Slides in 5 simple steps

If you work in marketing, chances are you’ve copy/pasted data from Facebook Ads, Google Analytics, HubSpot, etc., into a Google Slides or PowerPoint presentation on more occasions than you’d like to admit.

And if you’ve been through the process, you’d probably agree that manually copying data is a huge time-suck. What’s even worse is that these manual reports are prone to errors.  

Luckily for you, our friends at Thesis agency figured out a convenient way to automate the process with a combination of: 

• Google Sheets, 
• Supermetrics for Google Sheets, and 
• Google Slides.

And in this post, Mark Dilson aka #MetricsMark, Senior Performance Analyst at Thesis, will walk you through the step-by-step process he uses to automate the agency’s client-facing Google Slides reports.

Ready? Let’s go!

Step 1: Pull the data you need into a spreadsheet

To keep this example simple, let’s say that you’re building a monthly Google Slides presentation to report on the performance of your organic social media posts on Facebook. (But don’t worry, the same logic applies whether you’re reporting on paid search, paid social, web analytics, SEO, etc.)

The very first thing you’ll want to do is to run a basic query with Supermetrics for Google Sheets to pull all the raw data you want to include in your report.

Psst! If you don’t have Supermetrics for Google Sheets just yet, you can install it for free to start your 14-day trial. 

Here’s what your query might look like:

In this example, we’re using Supermetrics to pull the following data:

• Data source: Facebook Insights
• Select accounts: [Your or a client’s account]
• Select dates: Last 2 months
• Select metrics: Total likes, Page post engagements, Content consumptions, Total impressions
• Split by dimensions: Split to columns:  Year & month
• Filter: [None]
• Options: Replace blank metric values with zeros

Once you’re happy with your query, click on “Get data to table” and wait for the query to finish. Your spreadsheet will look a little something like this.

But since you probably don’t want to share this unformatted data dump with your client or boss, let’s do some formatting before we start adding any data to the final presentation.

Step 2: Add a percent change formula and format your table

Next up, you’ll want to create a percent change formula to the right of your Supermetrics query. This formula calculates the month over month difference of the metrics you’ve pulled. A simple formula that you can use is “(Current Month / Previous Month) – 1”. In this particular sheet, this would mean: (C2/B2)-1.

You’ll want to do all the formatting to your table in Google Sheets since this is the exact table you’ll eventually copy to your Google Slides report.

For example, you may want to add some conditional formatting to your MoM column to highlight any positive numbers in green and, conversely, any negative developments in red.

Your final table might look something like this:

But before we copy the table, let’s look at how to highlight your best/worst performing posts (or ads or landing pages, etc.) in the report.

Step 3: Find the best performing posts/ads

In addition to month-over-month performance, you may also want to highlight the top 3-10 highest-performing posts, ads, or landing pages, etc., in your report.

In this Supermetrics query, we pull the top five Facebook posts from the previous month, based on the highest total post reactions. Here’s the Supermetrics query we’re using to do that:

• Data source: Facebook Insights
• Select accounts: [Your or a client’s account]
• Select dates: Last month
• Select metrics: Total post reactions, Post link clicks, Video views, Post impressions
• Split by dimensions:  Split to rows: Date, Link to post, Post Message
  • # of rows to fetch: 5
  • Sort rows: Total post reactions
  • Sort direction: Descending
  • Secondary sort: Automatic
• Filter: [None]
• Options: Replace blank metric values with zeros

Run your query by clicking on “Get data to table”, and wait for it to finish. You should see a table like this one:

Again, you’ll want to format the table in Google Sheets, so it’ll look good in your final report.

For this formatting, let’s do something a little different. Instead of formatting the Supermetrics data pull directly, use the “=” formula a few rows below your data pull to replicate it. Example: In the B9 cell have a formula that is “=A1”. 

To save space when pulling it into a Google Sheet, you can use the HYPERLINK() function to merge the “Link to post” and “Post message” into one column. The formula would look something like HYPERLINK(“Link to post”, “Post message”) or as seen in the spreadsheet as =HYPERLINK(B2,C2). 

Once you’ve added formatting to your replicated table, it should look something like this:

Step 4: Schedule data refreshes with Supermetrics

To make sure you never have to build this same report again manually, your next step is to schedule automatic data refreshes with the Supermetrics sidebar.

It’s always a good idea to wait for two or three days to pull the previous period’s data just to make sure that the most accurate conversion data is available.

In this case, since we’re building a monthly report, the scheduled refresh would look something like this:

Psst! Check out more ideas for using the scheduled refresh and emailing feature in Supermetrics for Google Sheets.

Step 5: Bring your tables into a Google Slides report

Once you’ve set up your scheduled refreshes, it’s time to simply copy the fancy tables you’ve just created to your final report in Google Slides.

In Google Slides, make sure you choose “Link to spreadsheet” as you paste each table. This selection will allow you to refresh all your tables at once when it comes to updating your report.

Once you’ve copied both your month-over-month pivot table and your top 5 table, your Google Slides report should look something like this.

Pro tip: if you’d also like to include screenshots in your report, you can quickly just use the links hyperlinked in your table to find each of the top-performing ads or posts.

Then just rinse and repeat this process for all the channels and data points you’d like to include in your report.

Voilà — Now all you have to do is copy and refresh your Google Slides report once a month

From here on out, updating data to your Google Slides reports will be smooth sailing.

After each scheduled refresh in the Google Sheets, you can follow these simple steps: 

1. Take a copy of the previous month’s Google Slides report (to make sure you’re not just writing over previous reports).
2. Make sure to find and replace the month’s name on all the slides (e.g., change December 2020 to January 2021).
3. Open the “linked objects” sidebar by right-clicking on any of the tables and click “Refresh all”.

Now, all you have to do is quickly spot check the data in your report to make sure that it matches what you can see in your marketing platforms. You may also want to include an executive summary, some analysis, and recommendations in the Google Slides report each month. 

And once you’re done with all of that, pat yourself on the back and send that report over to your client or internal stakeholders. ?

That’s it! From now on, you’ll be able to spend less time on manual report building and more time on analyzing your data and acting upon it.