Templated Success: How To Make Your Life Easier With Excel

By , Associate Director of Paid Search at Hanapin Marketing

76 SHARES

Anyone who has ever been put under pressure to build out a large account in a short space of time will know the trademark feeling of despair that can come with such a task. Luckily for us, there are a number of tools at our disposal that can speed up this process. When trying to build out thousands of ad groups, ads and keywords, templates can really improve your overall campaign creation speed. Today we’re going to take a quick look at how to build good PPC templates in Excel and how they can help you! Before we get started I recommend you check out our top 5 Excel tips for PPC managers.

For those of you new to templating, there are a number of reasons why you might want to use them:

  • Save valuable time
  • Provide consistency across ad groups and campaigns
  • Can be used with many different kinds of accounts
  • Generate many different keywords in a short amount of time
  • Experience improved quality score by having ads that use keywords in the copy for all ad groups

Before you get started you will want to think about what can and can’t be done with templates. Generally I avoid templates when campaigns have very specific keywords that can’t be used in more than one ad group. Also, you should be aware that your ads will also most likely need some manual attention to avoid them going over character limits. Be aware that Excel PPC templates often require a fairly significant up front time investment, so might not be suited to one-off campaigns.

Now we’re clear about the whys, let’s move on to the how-tos! For the sake of the following example I’m going to invent the fictional eCommerce client, HeroShop.  HeroShop is a huge electronic appliance retailer that has asked us to build out ten new campaigns – starting with TVs, but moving on to fridges, freezers, microwaves and the like.

Keywords:

The first step you’ll want to take is to do some thorough keyword research. In this case I’m going to look at the Google Keyword Tool to discover a number of buying phrases that customers regularly use when shopping for products. After some time I come up with a list of these terms: Buy, Sale, Discount, Offer, Cheap, Cheapest, Best, and Top. Once I’ve done this I go back to HeroShop’s website and list all the different variations of TVs on offer. In this case HeroShop is selling three sizes of TV – 46″, 55″ and 70″.

Now you have your list, fire up Excel and put in headings for Campaign, Ad Group, Max CPC, Keyword, and Keyword Type – It should look something like this:

 

Template Headings

 

Decide on a naming structure for your campaigns. In HeroShop’s case I want my appliance type followed by “- SN” to signify this campaign is on the search network. This is also a good time to decide on your ad group Max CPC – I typically go with my desired Cost Per Acquisition multiplied by my average account Conversion Rate. For simplicity in this example I’m going with $0.50.

My first, generic appliance ad group looks like this:

 

Generic Ad Group From Excel Template

 

Now we’re going to start expanding this list to include all the relevant keywords we dug up in our research earlier. I’m going to start with “buy”:

 

Excel Template of Buy Ad Group

Note: It’s important to leave a blank space in the fields beside your Max CPC so that AdWords Editor understands this is an ad group level bid.

 

Once I have this I can quickly replicate this ad group for all of my keywords using copy and paste, and then by using find and replace to change out the word “buy” for the word “cheap”.

 

Find And Replace From Template

Eventually you should have a long list of all the ad groups you wish to set up based around a number of different keywords. Once we have this template created we want to move onto our second template which focusses on the variations in the TV products. Here I’ve created a new file into which I can copy and paste all my different brands and size information.

 

Templates Based On Appliance Type

 

I’d like to point out at this point that you can start making any combination of these different keyword lists that you feel you need in your account – For clarity’s sake I am keeping my lists simplified in the example.

At this point we have two long lists of keywords – one focussed on our buying terms, and one focussed on our product types. With a quick find and replace of “Appliance” with “Television” and “appliance” with “television” my first list is suddenly filled out and ready to go.

 

Template For Buying Keywords

 

Repeat the process for your ‘type’ template, while also replacing “type1” with (in my case) “55 inch” and so on, to get something that looks like this:

 

Template Type Televisions

 

Now, not only do you have a pretty full list of keywords and ad groups for one campaign, but you also have two templates you can easily use to replicate the process across any number of different appliances. The first campaign might have taken you a while, but the next nine appliances should be really quick and easy to set up.

Ads:

The other side of the coin is that we also need to create an ad template. You’ll want to decide how many ad variations you are comfortable running – in my case it will be two, as that should produce a winner in the shortest time possible. You’ll want to create a template with a list of your ad groups (two for each as we want two ads), and which includes the headings “headline”, “description line 1”, “description line 2”, “display URL”, and “destination URL”. See below for an example of a template ready for us to start writing ads for:

 

Empty Ad Template In Excel

Notice the “LN” column headings? Those are so we can make sure our ads don’t go over the allowed limits: 25 characters for the headline and 35 for each of the description lines and the display URL. To do this we use the function =LEN(“Cell Number”) and Excel will display the number of characters for us. We can also add in conditional formatting so any over our limits highlight as red. To do that first highlight your LN row, then select conditional formatting on your home menu and set your character limit.

 

How To Use Conditional Formatting

 

Now write your ads! I’m using two ad variations and copying them across all my ad groups for description lines 1 and 2, and being a bit more manual with my headlines. See below for how your template might look:

 

A Built-Out Ad Template

Phew! Start find and replacing like mad to get your ad template to reflect your keyword templates until you get something like my example below:

 

Television Ads From Excel Template

See – that wasn’t so hard was it? Luckily for us, none of these ads went over the length limits, but if they had done you would need to remember to go back and edit them before uploading.

We’ve spent a while creating some good templates for televisions, but the next nine campaigns should be a breeze. Sure, you’ll have to go back and make a few tweaks, but the majority of the work has already been done. Remember, specific products or keywords can still be added in later once we’ve finished with our templating, so don’t worry if you some of your products or services are tricky to shoehorn into these templates.

Uploading

Save your templates as .csv so you can upload them into the AdWords Editor using ‘Import CSV’ in the File menu. Use Editor to check your campaign settings and to look for any other silly mistakes. Once you’re happy with your new campaigns, post your new campaigns and watch them fly!

Summary

Hopefully this guide will have been helpful to those of you new to using Excel templates, and has helped demonstrate how you might be able to get 10 new campaigns set up in no time at all! If you have any particular questions about anything shown or have your own template building tips, please share in the comment section below.

Get more weekly links with our Fast Five newsletter! Five Fast Links in Your Email Every Friday.

Also send me a daily RSS digest

Social Advertising Toolkit

Twitter Facebook LinkedIn Google+ Email Print More
  • Catalin Macovei

    Great article Sam,

    I’ve notice one thing that i think will help everyone. When importing first import without the keywords, keyword type because you might get the error “ambiguous row type”. So first select the adgroups, and the ads -> import and them select only the adroup name, keyword, keywords type and that will do it. Hope it will help anyone.

    • Sam Owen

      Thanks Catalin,

      I’ve never encountered the ambiguous row type problem but I appreciate the heads up!

      • Catalin Macovei

        It happened to me mostly on adgroups when Brand name were used in headline and keyword and had keyword insertion {KeyWord:}.