PPC Ads from Excel (Power Query)

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:

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 the Google Sheet template you will need later. It is a 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 useIMPORTXML 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 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.

Use this Google Sheet template and make a copy of this project. You might use the tab Project & Ad templates for notes and preparation of the required data.

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.

This IMPORTXML is also useful for managing campaigns of a client who can’t provide you with the product feed.

Example

Some columns are generated right from IMPORTXML :

Some elements could be just a piece of data taken from IMPORTXML:

And some columns need to be created manually:

My Power Query template generates by default the combination of name and 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 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&label

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.

Data Selection

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 keywords – just use CONCATENATE as I wrote earlier. You can have up to5 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 Source

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.

Keyword Generation

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.

Excluding keywords

If you want to change the excluding keywords – check the tab eKW. Its rules are defined in the query Excluding Keyword builder

Ad Generation

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

Recommended Posts

No comment yet, add your voice below!


Add a Comment

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *