Duplicate Keywords: Some Search-and Destroy Techniques
Published: January 24, 2014
Author: Kaelee Heuschkel
As we all know, in the world of search, our job is never done. Accounts require constant TLC, a solid understanding of Excel, as well as desktop editing tools as accounts grow in scale. Beyond Excel and the desktop tools, individually you must think outside the box and come up with new ways they can work together as you seek solutions to your everyday tasks. I’d like to share with you two processes that help complete heavy lifting cleanup tasks.
Identify Broad Match Modified (BMM) duplicate keywords
Have you ever looked at your account and seen that you are bidding on a group of BMM keywords as listed below?
+red +blue +green
+blue +red +green
+green +blue +red
Well, the truth is that each of these keywords will capture the same traffic. There is no reason for all of them to be in the account. Although, history should be considered before removing two of the variations! The best way to deal with expanding your account and excluding keyword variations that capture the same traffic is to be proactive in your new keyword expansions. Beyond your normal deduplication process, try this Excel/AdWords trick and avoid creating a massive number of unnecessary BMM keywords.
This method involves creating a new campaign (that you will later revert and NOT post) and using the Find Duplicate Keywords Tool in AdWords Editor. Follow the steps below:
1. Copy and paste all active BMM keywords into and Excel file
2. Create a keyword upload sheet with these keywords in a fake campaign called “Dedupe” and assign them to an ad group called “Current”
3. Create a keyword upload sheet with the keywords you want to add to the account but do not know if they exist in a different order. Assign them to the Dedupe campaign in an ad group called “New “
4. Upload these sheets into AdWords Editor
5. Click Tools and then Find Duplicate Keywords in AdWords Editor
6. Only select the box next to your fake Dedupe campaign
7. Select the settings as shown below:
8. Click Find Duplicate Keywords
9. Click on the “New” ad group in the campaign tree
10. Revert the keywords that are shown in this view
At this point, the keywords that remain in the “New” ad group are those that do not exist in the rest of the account. In order to retain this list, copy and paste these into Excel, revert the Dedupe campaign, and proceed with your normal keyword upload process.
Identify and Pause Duplicate Ads With or Without Tracking Parameters
Never fear, there is a way to bulk-pause duplicate ads. The process involves finding a unique identifier for each ad, concatenating components of the ad, and then using the countif function in Excel to determine duplicates.
The first step in this process is to determine a unique variable among your ads. If you are using a third-party management system such as Marin, a unique identifier in the destination URL will work. If not, both Google and Bing have a unique number assigned to creatives. Download a creative report from the publisher and then follow the steps below:
1. Use the Excel function =concatenate to combine the campaign, ad group, headline, description line 1, description line 2, display URL and the base destination URL into a new column called “concatenated”
*** Be sure pre preserve the destination URL with tracking in a separate column for later use when pausing ads
2. Implement the =countif function to determine the number of instances the concatenated string appears in the concatenated column and label this column “duplicates”
3. Place a filter on the top of your data file, filter for 1 in the duplicates column, and delete these rows since there are no duplicates of this exact ad
4. Continue filtering in the duplicates row for each number that appears and place the filtered views on separate tabs
5. Use an advanced sort in Excel to sort by campaign and then by ad group on each tab
If there are three instances of an ad, write Active, Paused, Paused next to the first three creative and so on. If your ads do not have unique identifiers in the destination URL, using a unique ID is your golden ticket for pausing the ads. This process can be adapted in a variety of ways to solve common day-to-day tasks in accounts.
Learn to value tools provided in the desktop tools and combine them with Excel in order to save time in implementing new strategies. #EfficientWork
