Central Perk: Using Excel to Help Move to Enhanced Campaigns

joi, 9 mai 2013

Using Excel to Help Move to Enhanced Campaigns

Posted: 08 May 2013 02:29 AM PDT

A few months ago Google announced Enhanced Campaigns. One of the major changes this brings is that segmenting Search Network campaigns by device is impossible. Tablet is now lumped together with desktop. Bids for mobile can be altered (as a campaign level bid adjustment), and you can turn off mobile advertising all together, but you can't turn off desktop targeting to have a mobile-only campaign.

Your options are campaigns that target desktop and tablets, or campaigns that target desktop, tablet and mobile (with adjusted bids). This means that if you had different versions of campaigns for different devices, when you upgrade you can only keep one active. But if you’ve had separate campaigns they are likely to have grown apart, even if they started as simple duplicates.

So, let's say you’ve decided to keep the desktop campaign but you have tablet and mobile campaigns you want to merge into it. You need to check for differences, then judge whether anything that was only in the mobile or tablet campaigns should be copied into the desktop campaign. But how?

From the title you may have guessed: I suggest using Excel.

Firstly, download your account into AdWords Editor. Then go to the File menu, 'Export spreadsheet (CSV)', and 'Export selected campaigns and ad groups' – select the campaigns you're interested in and click ‘OK’.

Open the CSV you’ve just made in Excel. There will be many columns – what columns you get will depend on what AdWords features your account uses.

Before we do anything, we're going to need to add some more columns. Copy the Campaign column (column A) and paste it into the first empty column (in my case it's column AS but it may be different for you). Rename the first cell 'Campaign Root'.

The idea is that all campaigns that are going to be merged should have the same Campaign Root. If you have a naming convention that gives you names such as "Things – Desktop", "Things – Tablet", "Things – Mobile", "Widgets – Desktop", "Widgets – Mobile", etc, then use find and replace to get rid of " – Desktop", " – Tablet" and " – Mobile" to leave Campaign Root column saying "Things" or "Widgets". If you haven’t got a consistent naming scheme you may have to manually change the campaign names.

Then add the column name "Is Primary". This will say if the campaign is the primary campaign – the one you'll keep when you’ve upgraded. Fill the column with 'Yes' for rows from the primary campaign, and leave them blank for the other campaigns (i.e. the mobile and tablet duplicates).

Then add to the top row the following column titles:

"Is Ad"
"Ad Check"
"Is Keyword"
"KW Check"
"Is Sitelink"
"Sitelink Check"
"Is Campaign Setting"
"Setting Check"
"Ad Group Bid"
"Keyword Bid"
"Primary Bid"
"Bid Adjustment"

Then select the entire worksheet (press Ctrl and A), go to the Insert menu, and click 'Table'.

There will be a dialogue box – click OK.

Your data should now be a table – the column headers have drop down buttons next to them, and everything has probably changed colour (in my case to blue).

Why are we turning this into a Table? Mostly so the columns are named sensibly. The file you create will have different columns depending on what features are in your account – having display campaigns, location extensions, or dynamic search ads will add extra columns. It's easier to keep track of what's happening if you know you're looking at the column of display URLs rather than knowing you're looking at column X (which contains display URLs some of the time). It also makes writing this blog post easier!

Note that [@Column Name] or [@[Column Name]] means the cell of the named column in the current row, while [Column Name] refers to the whole column.

Scroll sideways back to the column 'Is Ad'. In the first cell underneath type in
=AND(NOT(ISBLANK([@Headline])),NOT([@[AdGroup Status]]="Paused"),NOT([@Status]="Paused"))
It should be automatically copied into the entire column.

This formula is true if Headline isn't blank, the ad group isn't paused, and the ad isn't paused – which is to say, it's true if and only if the row is an active ad.

Then go to the first cell under 'Ad Check' write this code:
=IF([@Is Ad],COUNTIFS([Campaign Root],[@Campaign Root],[Ad Group],[@Ad Group],[Headline],[@Headline],[Description Line 1],[@Description Line 1],[Description Line 2],[@Description Line 2],[Display URL],[@Display URL],[Destination URL],[@Destination URL],[Is Ad],[@Is Ad]),"")
The IF means that if the row isn't an ad, then this column is blank.

Then the COUNTIFS checks how many rows have the same campaign root, ad group, ad text, and destination URL as the current row. If the answer is one then the ad isn't in all the campaigns. If the answer is three then the ad is in all three campaigns. If the answer is two then the ad is in two campaigns but not one.

Under "Is Keyword" type
=AND(NOT(ISBLANK([@Keyword])),NOT([@AdGroup Status]="paused"),NOT([@Status]="paused"))
This is true if the row is a keyword that's not paused or in a paused ad group. Note that this covers negative keywords as well as positive ones.

Under “KW Check” type
=IF([@Is Keyword],COUNTIFS([Campaign Root],[@Campaign Root],[Ad Group],""&[@Ad Group],[Keyword],[@Keyword],[Criterion Type],[@Criterion Type],[Is Keyword],[@Is Keyword]),"")

If the row isn’t a keyword, it's blank: if it is, then it's the count of how many duplicate campaigns the keyword is in.

Why do we need “”&[@Ad Group] rather than just [@Ad Group]? It's a bit complicated. The ad group could be blank, if the keyword is a campaign level negative. But there is an oddity of the COUNTIFS function – an empty criteria will be treated as 0 (a number), while the empty cells in the criteria range are treated as "" (a string). Using ""&[@Ad Group] as the criteria means that if there is no ad group the function will see [@Ad Group] as "" and count the number of rows where [Ad Group] is blank (rather than treating the [@Ad Group] as 0 and looking for 0s in the [Ad Group] column).

Under "Is Sitelink" type
=NOT(ISBLANK([@Link Text]))
This just checks that there's link text (unenhanced sitelinks can't be paused so there's no need to check the status).

Under "SL Check" type
=IF([@Is Sitelink],COUNTIFS([Campaign Root],[@Campaign Root],[Link Text],[@Link Text],[Destination URL],[@Destination URL]),"")
This counts the number of duplicate campaigns the sitelink is in.

Under "Is Campaign Setting" type
=OR(NOT(ISBLANK([@Campaign Daily Budget])),NOT(ISBLANK([@ID])))
Some rows contain the campaign's budget, language, ad schedule, and networks. Others contain the locations targeted.

And under "Setting Check" type
=IF([@Is Campaign Setting],COUNTIFS([Campaign Root],""&[@Campaign Root],[Languages],""&[@Languages],[Ad Schedule],""&[@Ad Schedule],[Networks],""&[@Networks],[ID],""&[@ID],[Location],""&[@Location]),"")
This counts the number of duplicate campaigns with the same language, location, ad schedule, and networks settings.

Note: COUNTIFS only works if strings are under 255 characters, and if you have a complicated ad schedule it may be over this limit. In that case the formula may return 0 and you'll have to check manually whether the settings in duplicate campaigns are the same or not.

That's all the formulae! Now you can use these new columns to filter – if a Check column shows a number less than the number of duplicate campaigns, then the row is an item that is not the same in all of the duplicate campaigns.

For example, if you have three versions of each campaign (desktop, mobile and tablet) then any Check column saying two or less shows something that is different between the campaigns: if you went to the KW Check column and filter for 1s and 2s, you'll get a list of keywords that aren’t the same in all versions of the campaign. You can order by keyword to make it clearer which campaigns a keyword is in and which it isn’t in.

You'll have to decide for yourself what to do with these. Why is the keyword not running on all devices? If it's because it performed badly on something you'll have to weigh up that against losing the keyword on the device it does work on. Similarly, compare sitelinks and ads – you can keep separate mobile ads and sitelinks, but you can't have tablet-only versions – and campaign settings.

If you want a look at my example spreadsheet, download it here.

Have you been using any Excel tricks or other tools to help you upgrade to enhanced campaigns? Let us know in the comments!