With one of our clients we had a poor month with product listing ads in October.
I was just digging into the results, found something interesting so thought that I would write about it here.
Product listing ads are triggered through users searches matching to data in a product feed submitted to the Google merchant centre.
Therefore if you have similar products in your feed then Google may opt to show different items for exactly the same search queries.
We tend to optimise our clients product listing ads through grouping related products into ad groups so that we can understand more accurately which products show for which searches.
After running the following analysis I am now wondering whether having one product per ad group may be a better bet so that you can see exactly which products show for which searches and also how they perform for those searches.
The screenshot below shows the results, split by month for a specific search term firstly by one ad group and then by another. The same search term has actually had products from 3 separate ad groups show for it, but for the purpose of clarity I am only showing the results for 2 ad groups here.
The first thing to notice is the conversion rate variance between the 2 ad groups. The first ad group has an overall conversion rate of 0.18% whereas the second has an overall conversion rate of 1.55%.
Overall the impressions and traffic are similar but a key thing is that the better performing ad group had 0 impressions for this search term in October. Bingo – we just found a pot of gold.
By adding this search term as a negative exact match keyword to the worse performing ad group we can improve the conversions of this search term by around 800%!
Running this search query analysis en masse
To run this analysis and develop a list of negative keywords to add quickly you need to take this analysis into Excel.
The idea is that you need to isolate the best performing ad group for each search query and to then add the search query as a negative exact match keyword to all other ad groups.
This screenshot taken from Excel shows you what it will look like. You can then just apply a filter on the far right columns “add as negative” cells and then add these as exact match negative keywords through Adwords editor.
I will explain how to get this data below.
In Adwords you need to make sure that you get a reliable set of data to work with so set the date range to at least a few months:
Once you get the data into Excel you need to do 2 things:
- Rank the seach queries by traffic (or impressions).
- Determine which ad group performs best for each search query.
To rank the queries by traffic (clicks) you need to create a pivot table that will add up all of the search queries into one row along with the clicks from all of the ad groups it has triggered for in one row.
In Excel; go to the “data” tab, click the little arrow next to the pivot table and select “Create Manual Pivot Table” as shown below:
Then once your pivot table appear in another tab you need to set it up like this to get a summary of each of the search queries total data:
Once the pivot table is built then add another column to the right hand side and use the filter function to filter by clicks, in the final row put in the value “1” to row 1 and then just drag it down to the bottom to put the rank in for each search query. this is shown here:
You then go back to the main sheet and run a vlookup to pull in the values of the traffic rank for each of the search queries. There is probably a formulaic way to do this whole thing but I am not bright enough to figure it out hence the workaround with a few of the functions.
You can see this data pulled in on the first row in from the right:
To get the spreadsheet organised as above you just need to apply a custom sort – this is the function to the left of the filter shown in the screenshot below – click on the little arrow to select the custom filter option.
Now, you have the data sorted by search query and ranked in order of conversion rate. The final thing to do is to isolate all of the best converting search queries so that you can add all of the others as negative keywords.
A key thing to remember is to only go through with adding negatives where there is sufficient data to determine that a search query definitely performs better in one ad group than another. For all of the ones shown above it is pretty conclusive but once you get down below the top 20 search queries the data becomes a bit more sketchy.
To highlight the best performers I used the following formula in the final column:
=IF(L2=L1,”add as negative”,FALSE)
I then dragged the formula down and you should see that the word “FALSE” appears next to all of the ad groups that convert best for each of the search queries. I then copied and pasted special to get the values as text on this column. Switched the word “FALSE” for “Best” using a find and replace and then I was done.
All you need to do then is add all of the search queries that have “add as negative” next to them as negative exact match keywords through Adwords editor.
You can just copy and paste from excel – make sure that you add the match type column with all values as “exact” though.
Management going forward
Following this analysis I am going to, wherever realistic and possible, set up product listing ads with an ad group for each product so that I can work out exactly which products work best for each search query. It will take longer to build up a reliable data set but it will be a more effective structure in the long term I believe. Also makes it easier to run product level promotions.
The only niggling concern I have is that some people may enter the site through a product listing ad on one product and then purchase a completely different product in the end. This screws the data up so it may be worth analysing this in Google analytics just so that you have an idea of how often this actually happens.
