How to Create a Free Google Ads Dashboard in Google Sheets

| 1490 Words | 7 minutes to read | supermetrics
This post contains affiliate links. I may earn a small fee if you choose to purchase something.

I’ve been doing some form of digital marketing for over 6 years. I’ve done everything from affiliate management, lifecycle email campaigns, to PPC ads in Google, Facebook, LinkedIn, and more.

I’ve spent over $6 million dollars on Google Ads alone in a single year. And there’s one tool I can’t live without.

Supermetrics.

It helps digital marketers pull data from many different ad platforms, send it to Google sheets, and then pipe in revenue from Mysql, RedShift, or any number of places to create an automatically updated dashboard of your paid media performance.

Pretty cool, right? Here are a few things I’ve used Supermetrics to do:

I’ll cover the first point today and teach you how to use Supermetrics to build a Google Ads dashboard in google sheets. You can copy my template and expand on it however you like.

Before we get started, you’ll need two things.

Tools needed:

They have a free plan that connects to just Google Analytics, but to harness the full power you’ll need a paid account. I may be able to help with a coupon code, so just send me an email.

Ok, let’s get started!

Table of Contents

  1. What is Supermetrics
  2. How to build a Google Ads Dashboard
  3. Instaling Supermetrics Addon
  4. Connect Your Google Ads Account
  5. Pull Your Data from Google Ads
  6. Setup Automatic Refresh
  7. Build Your Charts

What Is Supermetrics?

Supermetrics is a tool for the discerning, fast-moving digital marketer. It lets you pull data from a number of ad platforms or databases and dump it somewhere else.

The list of connections available through Supermetrics is staggering:

It’s essentially a SQL query for whatever data source you want.

The specific tool I use is Supermetrics for Google Sheets, which dumps the data you want into a Google sheet.

They have products for other tools as well:

I prefer Sheets over Excel for a few reasons:

Supermetrics has a free plan but you’re limited to Google Analytics data and you can’t schedule refreshes. Their paid plan right now starts at $100/month, paid annually, and is worth every freaking penny.

Building a Google Ads Dashboard

The Google Ads interface has evolved a lot over the years to better help users manage their campaigns. But it’s horrible for reporting on performance.

Every digital marketer I know downloads their data from Google Ads and throws it into a spreadsheet for analysis. The lucky ones have access to it in a data warehouse so they can slice and dice with expensive BI tools like Looker.

But development resources are expensive and time-consuming. What if you need to build that report tomorrow?

With Supermetrics you can download the data you need to be refreshed automatically every morning.

Let’s get started building that dashboard. Here are the charts I want to build, both weekly and for branded campaigns.

1. Install the Supermetrics Google Sheets Addon

2. Connect Your Google Ads Account

Supermetrics Addon for Google Sheets * Click Data sourceGoogle Ads * You’ll be prompted to connect Supermetrics to your Ads account. This is necessary to pull data into your Google sheet. * Once you’re connected, open Select Accounts and pick the account you want. You can choose the MCC level account or specific ones if you want, depending on how you gave it access.

3. Pull Your Data

We’re going to build 2 charts, each by week. I like to pull 6 months of data when looking weekly. You can choose more or less depending on your needs.

  1. Absolute Top Search Impression Share & Average CPCs for Branded Campaigns
  2. Total Conversions & CPA

Ok, you’re ready! Click Get Data To Table and watch everything populate!

4. Schedule Automatic Refresh

Before we build the charts, let’s schedule an automatic refresh so our data updates every week. Click Add-ons → Supermetrics → Schedule Refresh and Emailing.

Under Action, click Refresh Weekly. You can also choose Refresh & Email weekly, which will also email you the results. You can choose to have the result emailed in a PDF or Excel attachment, or inline CSV which is my preferred option.

I have the action start processing a few hours before I’ll ever need it, so here I selected 4 am.

Make sure to click Store Trigger at the bottom right, otherwise, it won’t be saved!

Supermetrics for Google Sheets Scheduled Refresh

5. Build Your Charts

Time to build the charts in our sweet Google Ads Dashboard! If you haven’t already, rename the sheet with your data in it something like Raw Data, just to keep this organized.

Create a new sheet, called Charts.

Go back to your Raw Data sheet, and build your charts. I prefer to build them in this sheet, and then copy them over to a new sheet. You can do whatever you prefer.

First, we’ll build our Absolute Search Impression Share & Avg CPCs for Branded Campaigns

Highlight the full columns for:

Once your columns are highlighted, click Insert → Chart. You’ll get something that looks like this (I’ve fudged my numbers a bit for privacy).

Create Google Ads Dashboard

A decent start, but not readable. Double click your chart, then in the Chart Editor hit Setup → Chart type → Combo Chart (bar and line graph).

Go to Customize, and make sure Impression Share is on the left axis, and a bar graph and CPC is on the right axis and a line graph. You change these under Series. Also, you can choose to show the data labels to make your chart more readable.

It may help to format the numbers in your data too, so you get nice percents.

You should have something like this.

Google Sheets Dashboard Template

Great! This helps me keep an eye on my brand campaigns to stay ahead of any competitors trying to bid on my brand. Usually, if I see my CPCs increase it means someone is conquesting my brand, and I need to look into raising my bids to out price them (they’re likely paying 3-4x my CPCs) or file a trademark request if they’re using my brand in their ads.

Now we’ll do the same for the next graph, Total Conversions & CPA for branded campaigns.

Highlight the below columns:

Create a chart in the same way, and you’ll get a familiar-looking result. Again, my numbers are fudged.

Supermetrics Google Sheets Chart

I made the same changes as the last one:

Supermetrics Google Sheets Chart

My fake company is crushing it! More conversions at a lower CPA.

Wrapping Up

That’s how you use Supermetrics to create a Google Ads dashboard in Google Sheets. Super simple to setup and the possibilities for expansion are limitless. If you want to bring in additional data sources, I’d recommend creating new tabs for each data source like Facebook Ads or Bing. Then you could Vlookup your relevant columns into one master raw data sheet, and build charts off those. That is how I build a weekly cost file across all my paid media channels.