-
Keep your marketing ad spend on track with this Looker Studio dashboard
## What We’re Going to Do
I’m going to show you a simple setup for analyzing your Google Ads budget & pacing using Big Query and Looker Studio. [Here’s](https://lookerstudio.google.com/reporting/f29cd08c-6bb4-407d-8838-6b155f14e6bf/page/p_49kpsmh72c) an example dashboard that you’ll be able to build at the end of this post.
This dashboard will help you;
* Track over & underspending across accounts, channels, campaigns, and projects.
* Identify optimizations related to budget and pacing.
* Adjust budgets to hit campaign objectives.
* Build far more useful charts for your own budget analysis than you could in a spreadsheet.I’ve been using a very similar solution to this in Google Sheets but spreadsheets have their problems.
* The sheet requires regular maintenance, like updating the number of rows for longer months and manually changing dates.
* It’s not very professional for sharing with the company executive team or a client.
* The formulas are complicated and hard to read.
* It’s difficult to blend with performance data for actionable optimizations.So we built a solution in Google’s Looker Studio. This example uses Google Ads data but it can be modified to work with TikTok, Meta, Reddit, any ad platform you have budgets you want to track.
I’m really excited about this for a few reasons. This is the first iteration but I plan to;
* Merge the dashboard with performance data to identify the days, week and months we should weight our budget to optimize for things like months with extra weekends, holidays or sales.
* Quickly identify underspend so that we don’t lose cheap conversions early in the month and spend valuable budget chasing conversions at the end.
* Blend this data with GA4 performance to measure sales lift from specific platforms.
* Blend with ad platform data to identify how much potential more reach there is and at what cost.## Please Can You Help?
* If you’re a regular Big Query, SQL, or Looker Studio user – Do you have any recommendations for how I can improve my process, setup, or code?
* If you found this interesting and you’re interested in learning more – How can I structure this better? Would chunking it up into smaller sections make it easier to read?
* If you have some other feedback please share, I would like to hear it.## Why Would You Use This Tutorial?
If you want to learn how to use Big Query, experiment with some data analysis scripts, or use any of the tools in the Google Cloud Platform you’ll have to start by first setting up a project on the Google Cloud Platform.
## Why Should You Use The Google Cloud Platform (GCP)
I use GCP for many reasons, but mainly for hosting applications and datasets when I’m doing analysis of marketing data. For example, I use GCP for;
* Pulling data from Google Ads, TikTok Ads, and Facebook Ads.
* Connecting platforms with each other.
* Analysis of social media posts.
* Hosting data for marketing tools.## There Are 4 Steps To This Post
Setting up GCP is relatively simple, but this guide is going to go further. I’m going to walk you through creating a project and pulling data into any Looker Studio dashboard.
I’ve broken this down into 4 sections;
* Creating a project on the Google Cloud Platform.
* Setting up the development environment and resources on your local machine.
* Enabling the APIs and setting up the access credentials in your GCP project.
* Deploy your new application to your GCP project.More information about building a web app in the Google Cloud Environment can be found [here from Google’s documentation](https://cloud.google.com/appengine/docs/standard/python3/building-app/creating-gcp-project).
## Glossary
* Google Cloud Platform – I’ll refer to this as “GCP” or Google Cloud Platform – This is the platform and may contain one or more Google Cloud Projects.
* Google Cloud Project – I’ll refer to this as the “Project” – The Google Cloud Project holds your application and other Google Cloud resources like API keys and data storage.## Setting Up Your Project
* On the project selector page of the GCP Console, [SELECT PROJECT](https://console.cloud.google.com/projectselector2/home/dashboard) or [CREATE PROJECT.](https://console.cloud.google.com/projectselector2/home/dashboard) This project will contain the application and its resources.
* Note: If you don’t plan to keep the application that you build in this tutorial, create a new Google Cloud project instead of selecting an existing project. After you finish this walk-through, you can easily delete the whole project, removing all resources associated with the project. You’ll be limited to a quota of projects (around 20) so you don’t want to create loads of them needlessly.[https://imgur.com/Mjl05cd](https://imgur.com/Mjl05cd)
* Make sure that billing is enabled for your Cloud project. You’ll either have a billing account setup which you can select from the dropdown or you’ll need to create one.
* Note: Learn how to [check if billing is enabled on a project here in Google support](https://cloud.google.com/billing/docs/how-to/verify-billing-enabled). I’ll show you how to set this up with minimal cost but you will be billed if you exceed the quotas Google outlines. Keep in mind that pulling huge amounts of data via the API and storing it in the cloud will cost you money.[https://imgur.com/ltkYWvZ](https://imgur.com/ltkYWvZ)
* Click the blue “CREATE” button and you’ll be forwarded to the projects console page.
## Create Your Big Query Data Source
For this example, we are going to create two tables in Big Query;
* **google\_campaign\_budgets** \- This will include your target budgets for each channel and each campaign.
* **google\_campaign\_spend** \- This will include the total amount that each campaign in your Google Ads has spent by day.For simplicity, my example is going to source the data from Google Sheets. I’ve created some fake data in Google Sheets and I’m linking this data to Big Query. For a live production tool, you’ll want to automate this with a third-party tool or script. There are many ways you could get your data into Big Query.
You could use a tool like;
* [Supermetrics](https://supermetrics.com/?utm_source=google&utm_medium=cpc&utm_campaign=canada-brand&utm_adgroup=Supermetrics&utm_category=search-brand&utm_term=supermetrics&location=&gclid=CjwKCAiArY2fBhB9EiwAWqHK6kO94nxEXLRkHjbz5FkpSMEQiNxR_5z3ub1qq75oUQH0f3qXIfth0RoC-kYQAvD_BwE) to import it
* [Fivetran](https://www.fivetran.com/) has connectors
* GCP and python to host your own custom script
* Google Scripts
* [Google’s own connector](https://cloud.google.com/bigquery/docs/adwords-transfer)
* [Google Ads Add-on for Spreadsheets](https://support.google.com/google-ads/answer/9000139)*Hint: For a tutorial like this, it’s easy to get going with Google Sheets. It means you can easily manipulate the data under the hood and get a feel for what’s happening. A data engineer would probably cry if you told them you were using Google Sheets to host your Big Query data but it helps me teach you how to do this.*
## Copy The Google Sheets of Fake Data
Start by creating a copy of this [spreadsheet](https://docs.google.com/spreadsheets/d/1Y0icAl2MeBIx4yHsEj0Ufe_ae1yTH7exO-s1VYkoxkQ/edit#gid=766355602). You’ll see there are two example sheets;
* [GOOGLE CAMPAIGN BUDGETS](https://docs.google.com/spreadsheets/d/1Y0icAl2MeBIx4yHsEj0Ufe_ae1yTH7exO-s1VYkoxkQ/edit#gid=766355602)
* [GOOGLE CAMPAIGN SPEND](https://docs.google.com/spreadsheets/d/1Y0icAl2MeBIx4yHsEj0Ufe_ae1yTH7exO-s1VYkoxkQ/edit#gid=1910104186)If you click into the cells in the fake data you’ll see there are some formulas. These formulas are to keep the data up to the most relevant date so we always have the last 5 to 6 months of data to work with. You can ignore the formulas, they are just there to make the example work.
[https://imgur.com/iAZvjER](https://imgur.com/iAZvjER)
## Setting Up The Data In Big Query
First, I’m going to walk you through connecting the example data in Google Sheets to Big Query. If you want to experiment you can change the data in Google Sheets or even add your own data to Bigquery
## Creating a Dataset
* Open [Google Big Query](https://console.cloud.google.com/welcome?project=example-budget-analysis). Make sure you’re in the correct project – You can see here the project selected is “datapull.”
[https://imgur.com/kZBHDh8](https://imgur.com/kZBHDh8)
* Click the three dots next to the project id and select “Create dataset.”
[https://imgur.com/fxnUsBl](https://imgur.com/fxnUsBl)
* Give the [dataset](https://cloud.google.com/bigquery/docs/datasets-intro) a name – I called mine “budgets” and select CREATE DATASET. You should be returned to the Big Query console.
[https://imgur.com/OYzNWnb](https://imgur.com/OYzNWnb)
## Add Your Tables Of Fake Data
**GOOGLE CAMPAIGN BUDGETS**
We’re now going to add [tables](https://cloud.google.com/bigquery/docs/tables-intro) of data to our dataset;
* Click +ADD DATA
* Select “Google Drive”
* Add the URI of the Google Sheet
* Change the “File format” to “Google Sheets”
* Add the sheet name “GOOGLE CAMPAIGN BUDGETS” for Sheet Range
* Double-check that you’re adding this to the correct project.
* Select the dataset you created – remember mine was “budgets”Name the table. For simplicity, I named mine the same as the Google Sheet “google\_campaign\_budgets” but in all lowercase. *IMPORTANT These table names are referenced in our SQL queries so double-check your spelling and underscores. They are also case-sensitive. Check then double-check.*
[https://imgur.com/JrKtRi8](https://imgur.com/JrKtRi8)
* At the bottom you’ll need to add the following schema for each column so that Looker Studio understands the data type and how to handle it correctly;
* channel – STRING
* program – STRING
* currency – STRING
* date – DATE
* budget – FLOAT[https://imgur.com/aronnOs](https://imgur.com/aronnOs)
* Before moving forward select “Advanced options” and set “skip header rows” to 1. This is so that our headers are not included as part of the data.
**GOOGLE CAMPAIGN SPEND**
Next, you will have to add the second data source, “google\_campaign\_spend”, to your “budgets” dataset.
* Inside the “budgets” dataset add the sheet for “google\_campaign\_spend” with the following schema;
* accountid – INTEGER
* account – STRING
* currency – STRING
* date – DATE
* campaign – STRING
* spend – FLOAT
* Your dataset structure should look like this[https://imgur.com/vPtGRxt](https://imgur.com/vPtGRxt)
## Adding the BigQuery views
The SQL queries detailed below form the basis for our BigQuery views. These views form an abstraction layer on top of our tables of fake data, ultimately simplifying the final query that will be used by Looker Studio to generate the underlying dataset for the dashboard.
* Open up the “Editor” in Big Query.
[https://imgur.com/90jp2fA](https://imgur.com/90jp2fA)
* Daily Budgets: This sql distributes data from the monthly budget sheet across all days in the month. This allows us to join the daily spend more effectively.
* Copy and paste [this code](https://github.com/mikejaking/bigquery-budget-tracker/blob/main/daily_budgets.sql) into the editor.
* “Click SAVE” > “Save view” and call the new view `daily_budgets`[https://imgur.com/SIyzW1s](https://imgur.com/SIyzW1s)
* Daily Spend By Program: This view parses the program from the underlying campaign and then the daily spend per program.
* Open a new Editor window
* Copy and paste [this code](https://github.com/mikejaking/bigquery-budget-tracker/blob/main/daily_spend_by_program.sql) into the Editor
* “Click SAVE” > “Save view” and call the new view `daily_spend_by_program`
* Budgets Vs Actual: This view joins the above views for budget and spend and generates a dataset that contains additional information such as a daily running total for spend as well as a dynamic budget for each day that is based on the amount of underspend/overspend in relation to the current budget.
* Open a new Editor window
* Copy and paste [this code](https://github.com/mikejaking/bigquery-budget-tracker/blob/main/budgets_vs_actual.sql) into the Editor
* “Click SAVE” > “Save view” and call the new view `budgets_vs_actual`
* “`budgets_vs_actual`” is what we will use as our data source in Looker Studio and this is what your structure will look like in Big Query.[https://imgur.com/jpppAUr](https://imgur.com/jpppAUr)
## Setting Up Your Looker Studio Report
* [Open Looker Studio](https://lookerstudio.google.com/).
* Create a new report.## Connect Your Data Sources
* Search for Big Query
[https://imgur.com/ouuoGPS](https://imgur.com/ouuoGPS)
* You should be able to see your \[GCP project\] > \[Dataset name\] > \[budgets\_vs\_actual\] table.
[https://imgur.com/POdMxtO](https://imgur.com/POdMxtO)
* Add the data source to your account
[https://imgur.com/T4Et6a1](https://imgur.com/T4Et6a1)
## Create Your Data Fields
We are going to create a few fields to get you started.
* First, recommend renaming your dashboard so you don’t lose it.
[https://imgur.com/uT2c58D](https://imgur.com/uT2c58D)
* Click on Resources > Manage added data sources
[https://imgur.com/exr0DBp](https://imgur.com/exr0DBp)
* Click Edit on your budget\_vs\_actuals data source.
[https://imgur.com/vmjhyWM](https://imgur.com/vmjhyWM)
* Create a new field called ”% Daily Budget Spent” and add the following formula:
`CASE`
`WHEN SUM(budget_dynamic) <= 0 THEN -0.00001`
`ELSE SUM(actual) / SUM(budget_dynamic)`
`END`
[https://imgur.com/J05lQev](https://imgur.com/J05lQev)
* Create a new field called ”% Monthly Budget Spent” and add the following formula:
`SUM(actual_rt_sod + actual) / SUM(target_budget_monthly)`
[https://imgur.com/WMUsQNg](https://imgur.com/WMUsQNg)
## Create Your Charts
You made it this far! Fantastic. There are a few options now.
* You can build your own budget tracking reports.
* You can create a copy of the example I made [here](https://lookerstudio.google.com/reporting/f29cd08c-6bb4-407d-8838-6b155f14e6bf/page/p_49kpsmh72c). Using this report you can add the “budget\_vs\_actuals” data source you’ve created. It should just plug in.Alternative If you want to build your own report I recommend you explore the metrics;
* actual – The total spend on this date.
* actual\_rt\_eod – The total spend up to this date at the end of the day.
* actual\_rt\_sod – The total spend up to this date at the beginning of the day.
* budget\_crude – This is the original monthly budget, split out by day.
* budget\_dynamic – Is the monthly budget minus the total spend to date divided by the number of days left in the month to reach the target budget.
* program – The campaign or program name.
* target\_budget\_monthly – The target budget to spend.
* underspend\_crude – The target budget for the day minus the daily spend.
* underspend\_dynamic – How much under (or over) budget the program is for the day. Returns 0 for days greater than the current day.
* % Daily Budget Spent – The percentage of the daily budget spent.
* % Monthly Budget Spent – The percentage of the monthly budget spent.In the example [dashboard](https://lookerstudio.google.com/reporting/f29cd08c-6bb4-407d-8838-6b155f14e6bf/page/p_49kpsmh72c) charts, the metrics are renamed to something more relevant for the user. I recommend you do the same.
[https://imgur.com/CrdcRT7](https://imgur.com/CrdcRT7)
We also added conditional formatting to make it easier to spot the fluctuations in performance. We also added a date selector for the month and program filters, and currency.
## What’s Next?
There’s some more advanced analysis that we can do on our budgets, but this post has already gotten pretty long. I’ll follow up with what to do with this data in a second post.
-
0 Replies
Sorry, there were no replies found.
Log in to reply.