SEO News
AdWords

Streamline Your PPC Workflow With Excel

lynch-john
by , Comments

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.

Character Length Limits

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.

Headline Conditional Formatting

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.

Adjective Ad Group Label

To achieve the desired effect, simply use the following formula in a headline cell:

Headline Cell Formula

This will combine the two fields to create the desired headline, and will work quite nicely with the character limit field listed directly above.

Display URLs

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.

Concatenate Generate Display URLs

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.

Concatenate Function URLs

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:

Series of Destination URLs

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


SES LondonOptimising 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.

Recommend this story

comments powered by Disqus