The Adwords interface is great to check account performance metrics and keyword data at a glance. It can however be hard to get a grasp of how the account is doing when managing very large campaigns that run across wide territories for multiple products, especially when your account structure is very granular. Labeling your Adwords Campaigns/Adgroups/Keywords is a good way to quickly segment your data but not very efficient when it comes to displaying it in a meaningful way.
In the past months we have had to monitor very closely the spend per service and spend per state of one of our client’s Adwords account. I wanted to share with you our custom solution, that is far from what a developer could come up with using the Adwords API and a good graph library but that is easy to implement with very limited coding knowledge and functions entirely in the Google ecosystem.
1. Building the Adwords Script
Google provides a few examples and tutorials of Adwords Scripts here. For the sake of this tutorial we will use the following code that grabs the cost data for each campaign of your account since the beginning of the current month:
In your Adwords Account click on > Bulk Operations > Scripts > Create Script. Once authorized, paste the code above into the field. Don’t run it yet as you still have to create and set up the Google Spreadsheet before using the script.
2. Setting up the Script Settings and outputting the Data to Google Drive
Create a new spreadsheet in Google Drive and paste the full shared URL (obtained by clicking on share at the top right end corner) on the 5th line of the Adwords script code. Make sure the Tab name of your Google Spreadsheet matches the one in the code on line 6.
Run the Script, after a few seconds the data should automatically populate in the Google Spreadsheet.
Depending on the time frame of your report, you should schedule the script to run hourly, daily or weekly. In this case I chose daily because most of my graphs include daily data.
3. Formatting the Google Spreadsheet
In order to create a chart with your dataset it is important to format it properly. I used a Pivot Table to aggregate the costs across locations and products and built my graph using the data from the Pivot Table. The complexity of this step depends on how your account is structured and how you broke down your products and locations. To allow room for data expansion I recommend having one data set only per tab. Once your data is neatly organized you can play around the Chart Tool and pick your metrics.
4. Outputting the data to a Google Site
The last part of the tutorial consists in creating your actual Dashboard using Google Sites. Once you have created your Google Site you just have to import the Graphs from your Google Spreadsheet. The dashboard will update itself automatically each time the Adwords Script runs (can be turned off by selecting “snapshot” instead of “live” in the graph settings).
If your Google account is hosted on your company’s domain, you can even share the dashboard with all your coworkers at once. Don’t forget to give them access to the Google Spreadsheet otherwise Google won’t let them see the actual graphs on the dashboard.