While the AdWords interface is a great way to digest overall campaign information, it isn’t necessarily the easiest way to quickly identify problematic campaign issues and make bulk changes. If you want to streamline workflow, these Microsoft Excel tricks will help you build, manage, and optimize your campaigns.
Excel is a terrific tool to build and load ads into a platform such as AdWords or adCenter. However, one of the downsides of building ads in Excel is never quite knowing if you’ve hit character limits for headlines, descriptions, and display URLs.
This can pretty quickly solved by creating character length limits to make sure your ads fit perfectly.
Headlines & Descriptions
First, insert a column directly adjacent to the headline of your ad.
Next, use the length function to keep a count of all characters. If your headline is in cell c2, simply enter the function =len(C2) in the adjacent row. This will provide a character count.
Next, use conditional formatting to highlight the cell red if the character count is greater than 25. Add a second condition to highlight the function in green if it's less than or equal to 25 characters. Use the same function for Description lines 1 & 2 (change character limits to 35 for each column) and you’ll now have a tool to quickly add a large number of ads to your campaigns.
Additionally, the concatenate function is great for quickly producing headlines. The concatenate function allows advertisers the ability to quickly generate fields by combining two or more cells of data. In the example below, I’ve used concatenate to quickly create a headline by combining a flattering adjective with the ad group label.
To achieve the desired effect, simply use the following formula in a headline cell:
This will combine the two fields to create the desired headline, and will work quite nicely with the character limit field listed directly above.
Additionally, you can use the concatenate function to quickly generate display URLs.
To do this, simply generate an additional right row labeled “URLs” and fill all cells with the base display URL (remember to capitalize the first letter of each word for better CTR).
Next, enter the concatenate function and select URL cell, followed by the corresponding ad group cell. In order to properly format the URL with dashes instead of blank spaces, simply use the substitute exactly as shown in the formula below.
The concatenate function is also extremely powerful for automatically generating URLs. While Google’s URL Builder only creates links one keyword at a time, advertisers can easily scale this effort across thousands of keywords using Excel.
To build, simply create standard inputs based on the source, medium, and URL by using the Google identification string, accompanied by the actual input on the right. These will be the building blocks of the destination URL.
Next, use the concatenate function to string together the properly formatted URL.
As shown above, the concatenate function is wonderful for stringing together a number of pieces of information. Remember to:
- Separate all cells with a comma in order to properly sequence the information.
- Remove spaces from the campaign and keyword fields. To do this, simply preface the field with the substitute function, and replace blank spaces with appropriate underscores, dashes, or plus signs.
- Use the $ sign in front of any cells that you don’t shift one row down when copying the formula to remaining cells. In the example listed above, the static fields should include source, medium, and URL. The variable fields include campaign and keyword, and this information should be pulled directly from the data set.
Once you’ve completed the formula, you should get a pretty series of URLs such as the ones listed below:
When finished, simply upload your finished spreadsheet to AdWords/adCenter editor.
Build PPC Ads With Excel
Learning a few formulas can go a long way in enabling PPC specialists to build large campaigns more quickly. Instead of going through the exercise of creating these sheets, I’ve made this tutorial available for download here: Build PPC Ads With Excel
Optimising Digital Marketing Campaigns with Search, Social and Analytics
At SES London (9-11 Feb) you'll get an overview of the latest tools, tips, and tactics in Paid, Owned, Earned, Integrated Media and Business Intelligence to streamline your marketing campaigns in 2015. Register by 31 October to take advantage of Early Bird Rates.