“As you read these lines somewhere in the world, your name is being dragged from cell C25 to D14 on a roster,” – Colm O’Regan in his article for bbc.com.
When setting up new AdWords campaigns, marketing specialists in different fields – from event to tech marketing – spend a considerable amount of time on routine tasks. Like creating ads, groups and campaigns by hand in AdWords.
Many companies have found a way to optimize this process by using AdWords Editor – a free application for managing large AdWords accounts – that has bulk editing features and also works offline.
But you can go even a step further. In this post I will explain how we use Excel to quickly create multiple campaigns, copy them into the AdWords Editor and import them all at once directly to AdWords account.
It will allow your marketing team to concentrate on tasks that are more crucial and better suit their expertise than mindless data entry.
Download our ready-to-use template with pre-built macros to create new AdWords campaign quickly.
But first, you should make sure your PPC specialists are familiar with Excel features used there.
After all, according to Tim Worstall, writer and Senior Fellow of the Adam Smith Institute in London, “the only spreadsheet that anyone uses in any quantity in business or finance is indeed Excel”.
How To Build a Keyword List Quickly?
Excel is here for you.
Let’s say you have CDN services as a seed keyword.
Now you would like to add several long-tail keywords to your CDN campaign such as full-cycle CDN services, affordable CDN services, etc.
To do this quickly and efficiently, you will need to combine your seed keyword from one cell with the ‘tail’ from another.
For that you will have to paste the following formula containing & operator:
Add $ symbol to make sure the seed keyword stays the same as you’re copying the formula across several cells. To do that, place cursor after = (“equals to” symbol) and press F4. The “ “ part specifies a regular space between the strings of text.
To use operator & in different cases, use following syntax: =”text1”&”text2”. Do not forget to use
Do not forget to use & between every part of the final text and enclose every word (even spaces) in quotes.
In this example, my seed keyword was CDN services and I combined it with affordable and full-cycle.
How To Put a Keyword into Any Match Type?
If you work with AdWords, you probably use several match types for your keywords. They help control which user searches can trigger your ad.
For example, you could use the keyword in broad match to show your ad to a wide audience or you could use exact match to hone in on specific groups of customers.
Match type is signified by special punctuation. Broad match modifier keywords get pluses in front of them (+shopify +plugins), phrase keywords are enclosed in square parenthesis (“shopify plugins”), quotes mean phrase match type [shopify plugins].
You can use Excel tools to add punctuation to keywords and, thus, specify their match type.
For exact and phrase keywords, use previously mentioned & operator:
Broad match modifier is a little bit trickier. Here you will need to introduce the SUBSTITUTE function to the formula. It’s syntax is: SUBSTITUTE(cell with the text you want to change, old text, new text).
In this case, the old text is “ “ (space) and the new text is “ +”. Note there’s a space before + in the new text. Also, you need to add “+” in front of the word with & symbol:
How To Fill Out Keyword Match Type Column?
If you use AdWords Editor to create your campaigns, you need to fill out the keyword match type column. It can take a long time, but luckily Excel can help with that.
So you have successfully added necessary punctuation to your keywords and now you need to define their match types:
You can do this by using the IF function. In its easiest form, it says:
IF (Something is True, then do something, otherwise do something else)
To fill out match type column, you will need to combine the IF function with other Excel tools:
The first part of the formula tells Excel to put Broad in the match type column if there is + in B2 cell (+IoT +solutions).
If there isn’t, Excel moves to the next part of the formula.
The second part of the formula says that if there are no + symbols, then Excel should look for the [ symbol and put Exact as a match type if it finds one.
The last part of the formula tells that if the keyword contains neither + nor [, the program should put Phrase in the column C.
How To Capitalize the First Letter of Every Word in an Ad?
Capitalization of every word (with exceptions of articles and pronouns) is one of the ad writing best practices. It has proven to be effective in making ads stand out.
But it is also time-consuming if you have many ads to capitalize at once.
The PROPER function can help you with that:
You can also use this function to capitalize ad group titles for example.
How To Keep Track of the Ad Character Length?
Google imposes length limits on all text ads. For a new format of Extended text ads headlines can’t exceed 30 characters, and description – 80.
It is best to keep track of ad length from the very beginning to avoid surprises later, when you are importing campaigns to the AdWords Editor.
You can do this with the LEN function:
How To Quickly Create Campaigns in AdWords Editor?
Each of Excel tools can help you avoid doing the same boring tasks many times. Now imagine what several of them can do if used together.
Follow this step-by-step guide on how to use Kraftblick template to fill out the table for AdWords editor.
A few words about the spreadsheet structure. There are three tabs:
The final table with all the data necessary to create a new campaign. You will only need to fill out the cells marked yellow. The rest will be filled out automatically thanks to Excel magic.
- Keyword List
Here you can enter all match types of your keywords and maximum CPC for each of them. These values will be then used by Excel to correctly fill out the “Max. CPC” column on the first tab.
To quickly create new campaigns, follow me:
- Open our AdWords Editor Template and go to the Keyword List.
You may get the Excel security warning if macros are disabled in your settings. In that case, just click the Enable Content button.
2. Paste your keyword list in the column A. For this specific example let’s use the following keywords:
3. Press alt+F8 and choose the TwoRows macro. Click Run.
Two empty rows should appear after each keyword.
4. Run the Keyword_x3.
You should end up with this.
5. Select three cells from the column B that appeared after you ran the Keyword_x3.
Drag these cells all the way down to the last keyword plus two more cells to copy functions they contain.
6. Go to the Template tab and select the first three rows from the column A through M.
Drag the highlighted area all the way down for as many rows as the number of keywords in column B on the Keyword List tab is.
In my case, I have 9 keywords in column B on the Keyword List tab. So I drag these three rows on the Template tab down to the row 10.
7. Go to the Keyword List Copy all the cells from column B.
8. Return to the Template Paste values of the copied cells in column B.
Some of the columns will be filled at once.
9. Now all you need is fill out the campaign name, destination URL and ad text columns. Sorry, but Excel can’t do the entire job for you. But wouldn’t it be nice?
10. By that moment you have all information in the template to import into AdWords Editor. Before that, copy the whole table (except for the first row)…
…and replace every cell with its value by using the Paste Special option.
11. Delete the Keyword (initial) and all length columns.
12. The final step is to import Excel data to AdWords Editor and then to your AdWords account.
Copy you table from the Template tab to a new tab.
13. On a new tab (a copy of the Template tab) delete Keyword, Type and Max. CPC columns.
14. Copy what’s left of the table.
15. Open AdWords Editor. Select Account –> Import. If you have pending proposed changes, the menu item will be grayed out. Once you accept or reject proposals, you will be able to proceed.
Choose Paste Text.
16. Press the Past from clipboard.
17. Review the column headers. If a header is incorrect, select the correct header from the drop-down menu.
18. Click Process. You can still revert the import later if needed.
Review the list of imported changes. If there are issues with the template, AdWords Editor will display a list of errors and warnings.
Click Finish and review changes.
19. After that, imported changes should be displayed as proposed changes in your account.
- To accept all changes, click Keep proposed changes.
- To reject all changes, click Reject proposed changes.
20. Go back the Excel template and undo the last delete on a newly created tab. Then remove Headline 1 and Headline 2, Description and Final URL
21. Repeat steps 14 through 19.
22. When you are finished, check all changes one more time and press Post.
All changes will be imported into your AdWords account.
Out team pays a great deal of attention to the automation of processes, which make everyday work much easier.
The solutions in this article were first created for internal use in Kraftblick. After some time we realized that many peer marketers will need them too.
Excel can help speed up workflow and boost productivity in many ways. And the best thing is – it’s already on your computer and completely free.
Use it to create campaigns for AdWords quicker than usual. Also, Excel allows you to keep backups of all changes in the account and other data you might want to keep close at hand.
Download our templates for generation of keywords and creation of spreadsheets in AdWords Editor. They can easily save the precious time you have to spend on all these monotonous tasks.