Updated: November 28, 2018
Shape PPC Budget Template for Google Data Studio
When clients entrust you to spend their advertising dollars, typically they want to understand what they are receiving in return. Enter PPC reporting. Great PPC reporting helps the client by 1) Updating the client on how campaigns are progressing toward their goals and 2) Reporting on the return on their investment.
At Shape, we love Google Data Studio for reporting. Data Studio is a fantastic (free!) reporting tool that can be used to provide near-real time client dashboards.
To help keep your clients updated on their ppc spend and campaign performance, we created a free ppc budget report template. All active customers can use this tutorial to create client-facing reports using Shape, Google Sheets, and Data Studio. Not a Shape customer? Take our PPC budget software for a spin with our free tier!
Your final report will look like this:
How to Create a PPC Budget Report for Your Client
1. Export Data from Shape
Select a client from your Shape dashboard:
From the budget view, select the settings icon
Check all boxes in table settings to enable all visible columns. Then select download
Export table data as a CSV “without current filters”
All exported files will initially be named "download." Distinguish between reports by renaming each with a convention that denotes data type and export date:
Return to Shape, select the "Campaigns" tab, and repeat the process to export all campaigns for the client:
You should now have two CSVs ready to upload to Google Sheets:
2. Configure Google Sheets as Data Source
Upload the CSVs as Google Sheets to a client specific Google Drive folder
Make a copy, rename, and save the following Google Sheet to the same folder:
Open the "Data Source" workbook. Update the "data export date" on the first tab to match your Shape export file date
Copy all cells from your "All Budgets" Google Sheet (downloaded from Shape). Paste that data into cell A1 of the “CLIENT NAME - All Budgets - PASTE” tab of the "Data Source" Google Sheet:
Repeat with your Shape “All Campaigns” exported data and paste to the “CLIENT NAME - All Budgets - PASTE” tab:
Ensure data populates correctly on the “DON’T TOUCH” tabs by verifying spend, clicks, etc match your pasted budget and campaign reports:
You’re now ready to add this Google Sheet as a data source in Data Studio
3. Create Data Studio Report
From Data Studio's Data Sources, select the blue circle in the bottom right to add a new data source:
The "DON'T TOUCH" Worksheets must be added one at a time. First, connect your “CLIENT NAME - All Budgets - DON’T TOUCH” worksheet:
Add calculated metrics by selecting the circular blue + button in the "Fields" header. Update the added fields' data "Types" to numbers, percentages, or currency as applicable. Return your data sources by selecting Data Studio logo in the upper left screen (DO NOT SELECT CREATE REPORT):
CTR = (clicks) / (imp)
CPC - (spend) / (clicks)
Conv Rate = (conv) / (clicks)
CPA = (spend) / (conv)
Remaining Budget = (budget amount - spend)
Adjust to account for roll-over amounts (if enabled)
Remaining Daily Budget = (budget amount - spend) / (Remaining Days in Cycle)
Avg Daily Spend = (Spend) / (Past Days in Cycle)
For help with calculated metrics review step 4 in this post: Scaling Agency PPC with Google Data Studio: Report Template
Repeat step 3 above to add the second “DON’T TOUCH” worksheet for "All Campaigns." Then return to data sources once more:
Verify both worksheets have been added to your data sources:
Make a copy of the following Google Data Studio report:
To link your data to the corresponding report pages, first connect your "All Budgets" data source. Then add the "All Campaigns" data source. Finally, select “Create Report”:
You will see a "Configuration Incomplete" notice. Update tables on both pages of the report with your calculated metrics. First select the table.
For the "All Budgets" report, on the right-side menu, select the first invalid metric and replace it with the "Remaining Budget" field. Complete for each additional invalid metric in the following order:
Avg Daily Spend
Remaining Daily Budget
Switch to the "All Campaigns" report. Replace the invalid metrics in the following order:
Tables will update automatically when all invalid metrics are replaced. Inspect your report by toggling to "View" mode. If it looks good, adjust sharing setting to share the report link with your client:
You'll want to determine a report update schedule (e.g. daily, weekly, monthly, etc.) that suits your workflow and client's needs.
I welcome feedback, will give you credit, and am happy to help with questions you may have. If you discover issues, a better method, or simply want help configuring your report, please reach out on Twitter.
Looking for more? We’ve created a free Google Ads performance report to help you scale your PPC reporting, as well as a guide to using Google Analytics to understand engagement on data studio reports.
Not a Shape customer yet? Take our PPC budget software for a spin with our free tier.