Post modified on
It’s sometimes better to use Excel for PPC ads creation when you do it effectively. Check my approach and you can download a free Power Query template to create your PPC campaigns in a few clicks!
This article is a third part of the PPC Ads Series:
- Introduction to PPC Ads
- Automated Ad Creation with PPC Bee
- PPC Ads from Excel
- Testování a vyhodnocování
Excel has an add-on called Power Query (PQ). It is a technology that enables you to connect, combine, and refine data sources. Power Query can speed up generating new campaigns. I created a template that could be modified and use for almost any account. Today, I am sharing my PQ template with you.
When to use Power Query in PPC?
- The ad groups and ads are not necessary to update frequently
- Using PPC Bee is too expensive for the client
- Specific campaigns that are not suitable for PPC Bee
- When you like working with Excel 🙂
I am focusing on product ads. However, you can use the same methodology for categorical campaigns or basically anything.
Google Sheets Template
It all starts in Google Sheet. Here is a link to Google Sheet template you will need later. It is great tool because you usually ad templates from copywrites and Google Sheets are the best tool for both sides. Moreover, it enables more functionality like the QUERY function, which is described later.
It is easier to prepare all important elements in Google Sheets than adjusting the Power Query code. You can either copy the elements from feed or you can use
IMPORTXML to “scrape” the data from the website.
Check several product pages and check which elements might be important for the end customer. It could be used in the ads or in the keyword builder. And your copywriter will love it 🙂 It’s quite easy. Just check the website with Chrome Inspect and check the elements you need + write down the element class etc.
In this example, I took h1 with function
=IMPORTXML(https://www.martinus.cz/?uItem=604503;"//h1[@class='product-detail__title mb-small']") and the same logic applies to other elements as well.
Once you create the ads, you might want to add specific keywords – Search terms that people use when searching for the product but it is different from the product title or other keywords that would be generated. Each specific keyword split with a semicolon (;) like in the screenshot below.
IMPORTXML is also useful for managing campaigns of a client who can’t provide you with the product feed.
Some columns are generated right from
Some elements could be just a piece of data taken from
And some columns need to be created manually:
My Power Query template generates by default the combination of
Attribute 1 + 2. But what if you need way more keywords? For that, there is a column called
KW generator. Here you can define your own combinations of keywords (for example with
CONANETE like in the screenshot below). You can repeat the keywords. Power Query deletes the duplicates. (Check in the screenshot that some keywords are there several times – some specific element is missing – like name of the second author.)
Adding labels add the label and PLUS it adds the label as a URL parameter in this format example.com/urlpart
In case you don’t want to append anything to the url. Change it in the Power Query settings: Query: Ad builder and Step Added Final URL 2. Here just adjust the code from this
[Final URL]&"&"&[Labels] to this
[Final URL]. But we will get to it later.
Now you should have your tab full of columns. I assume that you don’t want all of them. Select just the columns that are in the tab ‘Data‘. Use function
QUERY and select it in a way the Data list is defined. You can also filter only specific rows.
The left columns are pretty clear:
Final URL, Campaign, Ad group, Labels, Headline 1, Headline 2, Headline 3, Description 1, Description 2, Path 1, Path 2
The following columns are for keyword generation:
Name, Attribute 1, Attribute 2, KW generator, Specific Keywords
My Excel template will generate it in this style:
- Name + space+ Attribute 1
- Name + space + Attribute 2
If you won’t use both of them, just keep it empty. If you need more keyowrds – just use CONCATENATE asi I wrote earlier. You can have up to
5 keywords separated by a semicolon.
Max. CPCs are predefined in the ‘Set-up‘ tab also with Match Type options. Each Match Type generates different campaign with a different suffix. You can change it there if you want to.
BONUS Excluding Keywords and Diacritics
In the Google Sheet template are two extra tabs:
Replace Words is just for keywords – The final list is without diacritics.
Excluding words is only for excluding characters in keywords and ads that would Google disapprove (this applies all headlines, descriptions and paths). You can add more words/characters.
Now you need to set the Sheet as Public on the web and all the 4 tabs download as csv.
Ad generation in Excel
The main source for the Excel is the Google Sheet you have prepared. Use the URL from downloading the csv and use it as a source in Power Query. Check this blog post if you need help with that.
Finally here is the promised Power Query template. Download it and click on
Enable content after you open it. It is possible that in the older Excel version is pop-up Microsoft Office has identified a potential security concern – just click on
OKand it will work just fine.
Click on Data>Queries and connections. Click on the Queries in Input folder and the world of Power Query should be in front of you. Yet with many exclamation marks. You need to add the URLs from Google Sheet download in the first step called
Input data – The .csv download link from Google Sheet (Tab Data) add instead of ‘INSERT GOOGLE SHEET LINK – DATA HERE‘.
Input set-up – Add the .csv download link from the tab Set-up – instead of ‘INSERT GOOGLE SHEET LINK – SET-UP HERE‘.
Excluding words – Add the .csv download link from the tab Excluding words – instead of ‘INSERT GOOGLE SHEET LINK – EXCLUDING WORDS HERE‘.
Replace words – The last source is from the Replace words tab.
And you are done. Just click on Close & Load and Excel will provide you with the complete structure of keywords, ad groups, and excluding keywords. All you need to import in Google Ads Editor.
Data Click on button Refresh All once you need to update the ads. Excel will download the data from your Google Sheet and creates again a combination of keywords and ads.
Ad Group Generation
Max. CPCs are taken from the Google Sheet table (Exact, Phrase or M. Broad). Alternatively, you can adjust the bids later in Ads Editor.
Excel generates keywords with and without diacritics. It should be without any characters that Google bans. If it changes, you can easily add the new character in the Google Sheet tab ‘Excluding words‘.
Don’t forget that the column with specific KW from GSheet needs to be separated by a semicolon and you can have just 5 of them. If you need more, just click on the query
Keyword builder (in folder Other Queries) and change the 12. step (
Split KW generator by Delimiter) and 13. (
Split Specific Keywords by Delimiter) by settings icon and change it there.
If you want to change the excluding keywords – check the tab eKW. Its rules are defined in the query
Excluding Keyword builder
Ads are more-or-less ready from the Google Sheet. So there is nothing to change there. Don’t forget that there is a limit in characters for each title and description.
Your ads won’t contain any characters that are in Google Sheet’s tab Excluding words.
Pro tip – You can generate similarly the campaigns from BigQuery – check the Dan Zrůst’s blog
The last post is about ad testing and analyzing the performance