Build A Google Looker Studio Performance Dashboard with Supermetrics and Google?Sheets

Have you ever struggled to get a clear, unified view of your cross-channel marketing performance in a simple-to-use performance dashboard? Creating a comprehensive dashboard can seem impossible with data scattered across various platforms. Yet, having a centralized dashboard is crucial for understanding your performance and making data-driven decisions that can significantly impact your business outcomes.

In this series of posts, we’ll dive into different Google Looker Studio Performance Dashboard builds, starting with the foundational data architecture and automation flows. By the end of this guide, you’ll have a 28-day cross-channel trends dashboard, complete with controls to measure, analyze, and optimize data trends across Google Ads, Microsoft Ads (Bing), and Meta.

In this tutorial, we will build two specific performance dashboard views, but don’t feel limited by these examples. Once your foundational data table is set up, you’ll have endless possibilities for visualization and additional channels.

Here’s a preview of a cross-channel year-over-year scorecard view with custom filter controls:

Here is a preview of the last 30 days' time series trend with a year-over-year overlay:

Let’s build a DIY Data Warehouse using Google Sheets and Looker Studio!

Required Tools?—?Supermetrics, Google Sheets, Looker?Studio

We will use a low-lift approach to unify data across multiple platforms without needing any data science or engineering support. The result will be a solid tool to measure cross-channel performance and impress clients.

To achieve this, we’ll use the following tools:

Google Sheets: This is where your final data table that connects to Looker Studio will live.

Supermetrics: This tool will help us pull data from different sources into Google Sheets.

Looker Studio: A free Google product that we’ll use to build our reporting dashboard.

Google Looker Studio Performance Dashboard Resources:

You can reference and make a copy of this Looker Studio Template. Additionally, here is the Google Sheet containing the demo platform data. You can copy this sheet to see the formulas and connect it to your Looker Studio report as a data source.

Ad Platform Naming Conventions

The ad account structure and campaign naming convention are key to making this work properly. Let’s break down the identifiers we must include in our campaign naming conventions to parse out values and organize our data.

These are the core pieces of information we need:

Stage?—?Awareness, Consideration, Decision, Brand, Non-Brand

Platform?—?Meta, Google Ads, Microsoft Ads, DV360

Type?—?ASC, Performance Max, Search, YouTube, Standard

Channel?—?Paid Search, Paid Social, Display, Video

Country?—?United States, Canada

Region?—?North America

Campaign?—?Evergreen, New Launch

It is important to keep the order of each variable consistent. We will explain why when we get to our Google Sheets formulas. Here are some example campaign names:

Decision_Meta_ASC_Paid-Social_United-States_North-Amercia_Evergreen_Analytics-Experts

The more information you need to include, the longer the campaign name will be, so you might want to create a key for each value to shorten it.

In addition, this format is easily legible in UTM Parameters, and even though there is no PII data included, it is better to code each variable so that it is not easily legible when translated into UTMs

Here is what a coded campaign name looks like:

DESC_META_ASC_PSI_US_NA_EG

We can also include additional pieces of information after the core variable. For example:

Audience

Here is what a coded campaign name looks like

DESC_META_ASC_PSI_US_NA_EG_Analytics-Experts

Writing Business Logic In Google?Sheets

We will use a mix of Google Sheets formulas, including ARRAYFORMULAs, and nested IF statements using REGEXMATCH to parse our final data set. This approach allows us to categorize data with dimension fields for dashboard segmentation and filtering.

If you’re not an advanced Google Sheets user, don’t worry! I will provide each formula here with a breakdown of how it works and nuances to consider in the formula logic.

Platform Data Tables?—?The Date Campaign?Table

In this example, we will unify Meta Ads, Google Ads, and Microsoft Ads into one data table to report on channel, platform, stage, and campaign data over time. Let’s get started building your data campaign table.

Data Table?Metrics

We will only pull raw data into the Google Sheet, so we do not need to include calculated metrics such as conversion rate or cost per purchase. We will create custom fields for these metrics within Looker Studio.

The raw metrics we will include are:

• Cost
• Impressions
• Clicks
• Conversions?—?Purchases
• Conversion Value?—?Revenue

Setting Up Data Table Dimensions You Can Filter In Your Performance Dashboard

One key consideration is how each platform breaks down dimensions. Meta, Google, and Bing are consistent; however, with DV360, you need to add conversion types as a dimension breakdown so that we can create a purchase conversion column that matches the other platforms. This results in DV360 having an additional column in our data table, but we need to keep our columns consistent when we combine all data sets into the final data table.

To ensure the same column is set across each platform, we must add a filler column to Meta, Google, and Bing. We can do this by adding the account name.

Defining Required Header Columns In Your Data?Table

Since each platform uses its own header columns, we need to create standardized naming for all header columns. We can create these manually and make them work best for us. We are going to use the following header columns:

• Date
• Campaign Name
• Spend
• Impr.
• Clicks
• Conversions
• Revenue

Here is an example of how Supermetrics pulls in header columns for Meta:

Here is how they are defined in Google Ads:

And here is how they are defined for Microsoft Ads (Bing):

Because we will set our header columns manually, they will stay consistent. First, we pull in the platform data with the header columns included to verify that we are pulling the correct data.

Once the columns have been verified, go back and modify your query. Under options, select “No header row.”

Setting Up The Google Looker Studio Performance Dashboard Data Table?—?Creating Tabs for Each Platform In Google?Sheets

For this example, we are going to pull data from three sources:

• Meta
• Google Ads
• Microsoft Ads (Bing)

For consistency, we will create one tab for each platform and use the same manually defined header columns for each tab, as we defined in the previous step.

Let’s go ahead and create one tab for each platform, naming them as follows:

• Meta?—?Raw Data
• Google Ads?—?Raw Data
• Microsoft Ads (Bing)?—?Raw Data

Add one more tab called ‘Totals’ with the same header names. This tab is where we will combine the data across all data sources.

Note: You can only have 1 million cells per spreadsheet, so removing any unused columns is important to avoid maxing out your spreadsheet size.

Creating Supermetrics Queries In Google?Sheets

Supermetrics is pretty intuitive to use. I won’t walk through every step, but I’ll include screenshots of each query below that you can reference:

Meta Ads Supermetrics Query

Important: Ensure that Disable unified attribution settings is disabled; otherwise, you might not see any conversion and revenue data.

Google Ads Supermetrics Query

Microsoft Ads (Bing) Supermetrics Query

Adding Conversion Columns To Google?Sheets

We are going to pull in the primary conversion for each ad platform. My next post will discuss pulling in multiple conversions to understand the conversion makeup across products and build funnel reporting.

Google and Microsoft Ads

For Google and Bing, we will pull in the conversions column. The conversions column will only count conversions assigned at the campaign level. If campaigns use Account-Level Conversions, they will include all conversions defined as Primary Conversions in Google Ads.

Meta Ads

Since we are working with a traditional purchase funnel, we will use the Website Purchase conversion event as our primary conversion.

Date Range Lookback?Window

Since seasonality influences performance year over year, it accurately represents performance trends more than period-over-period data. If you have enough historical data, I recommend using a one-year lookback as the start date for your Supermetrics queries. This way, we can pull in YoY trendlines and percent changes into the scorecards.

Setting The Update Frequency In Supermetrics

Once you add all the queries to your sheet, let’s create a daily update schedule. You need to upgrade to the Supermetrics for Google Sheets Core plan to allow daily refreshes. I like to set the Start for Daily Refreshes to early in the morning so that my data is updated when I get online.

Aggregating Platform Tables In Google?Sheets

Now that we have all of our data organized and cleaned up, with a daily refresh scheduled, we can build our final data table that will connect to Looker Data Studio.

First, we need to create a new tab and call it “Totals” (Google Sheet for Reference) The name of the tab is optional, so feel free to label it whatever you want. Next, we need to copy our column headers over. For this step, it is important that all column headers are consistent across all tabs.

Now, we are going to use the UNIQUE function. This will aggregate and deduplicate all rows across all tabs. The end result is one cross-channel data table that we can now connect to Google Data Studio.

=UNIQUE({'Meta Ads - Raw Data'!A3:G; 'Google Ads - Raw Data'!A3:G; 'Microsoft Ads (Bing) - Raw Data'!A3:G})        

Adding Business Logic In Google?Sheets

Now, let’s add business logic to create a data architecture that we can use to segment and filter our data in the live Google Looker Studio Performance Dashboard.

We will add the following dimensions as column headers to the Totals tab.

• Stage
• Channel
• Platform
• Type
• Country
• Region
• Campaign
• Audience

You can add as many dimensions as needed, provided your naming conventions remain consistent across platforms. Setting up dimensions involves the same steps for each. Below, I’ll demonstrate specific examples of Stages, Platforms, and Channels that you can replicate.

Note: I am defining custom dimensions in this guide's Google Sheets data source. Alternatively, you can create dimensions directly in Looker Studio using Custom Groups.

Stage Google Looker Studio Performance Dashboard Dimension

For the stage, we need to parse out each stage using the campaign name. To do this, we will use the IFS and REGEX functions to look for a condition that matches. Remember that the IFS function returns a value corresponding to the first true condition. We will nest the IFS function in an ARRAYFORMULA so that it auto-populates down all rows.

=arrayformula(ifs(regexmatch(J2:J,"CONS_")=TRUE,"Consideration",regexmatch(J2:J,"DESC_")=TRUE,"Decision",regexmatch(J2:J,"AWAR_")=TRUE,"Awareness"))        

Platform Google Looker Studio Performance Dashboard Dimension

For the platform, we will use the same functions as we did for the stage.

=arrayformula(ifs(regexmatch(J2:J,"META")=TRUE,"Meta",regexmatch(J2:J,"GOOGLE")=TRUE,"Google",regexmatch(J2:J,"BING")=TRUE,"Bing"))        

Channel Google Looker Studio Performance Dashboard Dimension

We can use a static string of text for the channel and platform since they are unique to each tab. However, we want them to automatically populate to the last row as Supermetrics daily refresh adds new rows. To do this, we will nest a simple IF statement that returns the same value for TRUE and FALSE into an array formula.

=arrayformula(ifs(regexmatch(J2:J,"PSI")=TRUE,"Paid Social",regexmatch(J2:J,"PSC")=TRUE,"Paid Search"))        

Building Your Performance Dashboard In Google Looker?Studio

Let's head over to Looker Data Studio and create a new report.

Adding Tables As Data Sources In Looker?Studio

Add data to the report by selecting Google Sheets as the data source.

Choose the Google Sheet you created, select the Totals tab, then click Add in the bottom right.

Creating Custom Data Source Fields In Looker?Studio

Now that we have our raw data table connected, we will want to build out all of our required metrics at the data source level. If you create them on the Chart level, they will only be available for said chart.

After adding your Google Sheets data source, select the default table in your report and click on the pencil icon under Data Source.

Create the following calculated fields:

• CTR (Click Through Rate)
• CPC (Cost Per Click)
• CVR (Conversion Rate)
• CPA (Cost Per Action)
• ROAS (Return on Ad Spend)

To create a calculate field, click on ADD A FIELD

Then select Add calculated field

I will walk you through the creation of the first field, and then go ahead and create the remaining fields.

1. First, give the Calculated Field a name.
2. Next, enter the formula to calculate the filed
3. Then click save

You can copy and paste each formula using the same column headers we specified earlier in the post. If not, you will need to replace the field names in the calculations to match your column headers. For example, if instead of Spend, you are using Cost, you would need to replace all Spend values below with Cost.

CTR = (sum(Clicks))/(sum(Impressions))

CPC = (sum(Spend))/(sum(Clicks))

CVR = (sum(Conversions))/(sum(Clicks))

CPA = (sum(spend))/(sum(Conversions))

ROAS = (sum(Revenue))/(sum(Spend))

Next, we are going to review all fields to ensure they are set to the right type. Date should be Date Spend, Revenue should be set to currency, and so forth.

Google Looker Studio Page?Settings

Go to Page and then go to Current page settings

• Data source?—?Assing the same Google Sheet data source that we created above
• Date?—?Assing the Date field
• Default date range?—?Select Custom and set the date range to Last 28 days (exclude today)

Now that we have our page settings defined, let’s go ahead and add scorecards.

Adding Scorecards To Your Google Looker Studio Performance Dashboard

First, we are going to want to add scorecards. Click on Add a chart and then add a scorecard

Select the scorecard, and in the right-hand menu under Setup, set the following:

• Date Range Dimension = Date
• Metric = Spend
• Default date range = Auto
• Comparison date range = Previous year

Next, select the Style tab and make the following adjustments.

Select Compact numbers under Primary Metric

Depending on the Scorecard metric the Comparison Filed colors will need to be updated. For example when your CPA is down it should be green, not red. Since this example highlights Spend we will keep the colors as is.

Let’s also add a border to our Scorecard.

Once the scorecard looks how you want it to, you can duplicate it for all metrics. Since this is a performance dashboard, our Scorecards focus on conversion metrics:

• Spend
• Impr.
• CPM
• Clicks
• CTR
• CPC
• Conversions
• CVR
• CPA
• Revenue
• ROAS
• AOV

The result looks like this. You can also reference the Google Looker Studio Performance Dashboard here.

Adding Charts To Your Google Looker Studio Performance Dashboard

Now, let’s add our charts for each one of the metrics in the Scorecards we created above. For this, we are going to add the Time series chart.

Click on Add a chart and then add a Time Series

Select the scorecard, and in the right-hand menu under Setup, set the following:

• Date Range Dimension = Date
• Dimension = Date
• Metric = Spend
• Default date range = Auto
• Comparison date range = Previous year

Next, select the Style tab and make any visual adjustments to the chart and series to match your dashboard theme.

Adding a moving average trendline is also helpful in understanding performance trends outside anomaly days. In this example, I changed the Moving average Trendline to orange to make it easier to read.

Now, you can duplicate your time series chart and create one for each of the following:

• Spend
• Revenue
• Conversions
• CVR
• CPA
• AOV
• ROAS

The result looks like this:

Adding Report-Level Filter Controls To Your Dashboard

The above view is a great way to get a bird' s-eye view of how performance is trending across channels. However, the nuances of what is a positive or negative driver are found on the channel and platform level. Let's add some filter controls so that you can dive into specifics when looking at these charts.

To do this, click on Add a control in the top navigation and select Drop-down list.

Under Setup in the right-hand menu, set the Date range dimension, select the Control field, set the Metric to Conversion, and set the Control field to Platform.

We are going to add the following controls:

• Stage
• Platform
• Type
• Channel
• Country
• Region
• Campaign

Having a Date range control is also helpful so that you can modify the timelines to examine specific campaigns or seasonal moments. To add a date filter, click on Add a control in the top navigation and select Date range control.

The end results are shown here. The final scorecard and time series Google Looker Studio Performance Dashboards can be accessed here.

Looker Studio Scorecard Dashboard

Looker Studio Time Series Dashboard

Conclusion

Building a cross-channel data warehouse with Google Sheets and Looker Studio might seem daunting at first, but with the right tools and a step-by-step approach, it becomes manageable and rewarding. Following the process outlined in this post gives you a solid foundation for measuring, analyzing, and optimizing your marketing performance across multiple channels.

Remember, the key to a successful Google Looker Studio Performance Dashboard is consistency in your data and naming conventions, regular updates, and clear, actionable insights. With your data now unified and automated, you can focus on what really matters?—?making informed decisions that drive business outcomes.

If you have any questions or need further assistance with building your dashboard, don’t hesitate to contact me. Whether you’re looking for help with setup, customization, or advanced analytics, I’m here to help.?