Templated Success: How To Make Your Life Easier With Excel
June 29, 2012
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.
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:
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:
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”:
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”.
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.
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.
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:
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.
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:
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.
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:
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:
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.
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!
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.
How To Analyze Time-Of-Day Performance For Luxury Products
The importance of ad schedules varies across industries, services, and products. For e-commerce companies that sell luxury goods, ad schedules are more crucial.
One Week Left to Save 25% at Hero Conf London!
You still have time to join us for Hero Conf London, 24-26 October at etc.venues St Paul's at 25% off the conference price. But time is running out.
4 Common Questions Prospects Ask Me During The Sales Process
There’s always new, hot topics and questions we get asked by prospects to see what our company’s take is, and how we go about implementing from a services perspective. Read what our most common questions are.
A bi-weekly newsletter packed full of resources and strategies that will help make you a better PPC expert.
Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero
Mobile Price Extensions Rolling Out In AdWords
It looks like "Christmas in July" has come to Google. Recently announced price extensions are designed to help mobile marketers qualify clicks and inform prospective consumers.
[New Whitepaper!] 4 Ways To Survive PPC Campaigns for the Healthcare Market...
This whitepaper will highlight four examples of how thinking at the corners can help boost your PPC performance in the healthcare industry.
Create Visually Appealing Reports With Google Data Studio
When we talk about what tasks tend to eat up a significant chunk of time, reporting is surely mentioned. You may want to explore another option - Google Data Studio.
Liveblogging 2016 DoubleClick Announcements
Today is the day we learn more about the latest DoubleClick updates. Follow along live as we share DoubleClick's announcements as well as our opinions.
The 2016 Top 25 Most Influential PPC Experts
After a month of voting, rigorous research and data analysis, we are excited to announce the wildly anticipated list of the Top 25 Most Influential PPC Experts for 2016.
[New Webinar with AdEspresso!] 4 Effective Strategies for Facebook Lead Gen...
In this new webinar, AdEspresso and Hanapin experts discuss four effective strategies to use Facebook Lead Gen Ads for your business.
PPC Hero Launches New Site!
We're excited to announce the launch of a brand new PPC Hero! We've redesigned the site to be more search friendly with a better navigation system and more opportunity to find the right resources quicker.
How To Get More Out Of Excel Solver
Excel Solver is a fantastic tool, but most examples only cover simple marketing use cases. We’ll cover the next steps of enhancing your models and how to automate the process.
Where You Should Focus Your Facebook Brand Awareness Efforts
Learn the basic metrics to focus on when you want to build awareness on Facebook.
Google To Release New Shopping And Travel Features
Several upcoming changes to Google Shopping and travel-related ads can be expected in the near future. Read to find out more.