How To Create Display URLs In Bulk
August 14, 2015
Have you faced a tight deadline and needed to create hundreds or thousands of new display URLs? Maybe you’ve onboarded a new account and realized that you have 10,000 ads all with the same display URL and no mobile preferred ad copy.
Then this is the article for you.
In the next few minutes, you too will be able to impress your friends, family, and maybe even your boss with your new found ability.
Before delving into the five phases that are needed to create display URLs in bulk, let’s take a look at the before and after so you can get an idea of what you can expect. In true PPC fashion, the items that are on sale in our example today are of course wiaddgets, of all sizes and colors. We’ll also be adding ‘m.’ to the URLs as in this example we’re creating mobile preferred ad copy.
Here’s a link to the Google Docs sheet where you’ll find each phase on its own worksheet. I recommend opening that up and then following along with the article, especially if you’re dual screening. Even flicking through how each phase looks first will give you a better understanding as you start to read the details.
Note to Reader – As you embark on the steps below you will see the word ‘column’ a lot since we’re talking about Excel. In fact, it makes up about 3 percent of the total words in this article. If you have a synonym other than ‘upright pillar’ or ‘military convoy’ please let me know in the comments below.
Phase One – Making Space
- Start with an ad copy bulk sheet with the columns:
- Headline, Description Line 1, Description Line 2, Display URL, Destination URL
- Insert a duplicate Headline column, call this one Headline-2
- Then insert blank columns to the right of the Headline-2 column. Three or four should be enough. You should have a blank column for each additional word of your headline. For example, in our scenario there are three words in the headline so we need two blank columns.
- Next, right click and highlight the Headline-2 column
Phase Two – Text to Columns
- Now to break up the words in the Headline-2 into their own individual cells
- While the Headline-2 column is highlighted, Click the Data Ribbon, then Click “Text to Columns”
- Select Delimited, then Next
- Select “Space,” then Next (as you have a hyphen in Headline-2, this cell won’t be affected)
- Click Finish
Phase Three – Adding Separators
- Your sheet will look like the below (you can delete any extra columns you added that are blank all the way down)
- Next, add new columns containing a ‘-’ between columns B & C and C & D. These can be labeled simply with a dash ‘-’
- The amount of words that are in your longest headline will determine how many ‘-’ columns you need. i.e. two extra columns for three words etc.
Phase Four – Combining Columns
- Now it’s time to add the new URL root to a new column, here we’re using “m.widgets.com/” in Column I below
- In Column J, combine the new Destination URL (Column I) with each word and each separator
- Your formula will look like the following =I2&B2&C2&D2&E2&F2
- Fill Column I all the way down with new URL, (“m.widgets.com”)
- Drag formula in Column J down to fill all cells in column
- The custom URL is taking shape
Phase Five – Getting Rid of Excess
- Add a Filter (Ctrl +Shift+ L) to Row 1
- Filter Column J for “ends with ‘-’
- Use the following formula to remove the last character:
- Drag down the formula and that’s it!
- Now all you have to do is transfer these newly trimmed URLs and delete all the columns you no longer need
- Note: You’ll need to do some QA for any weird looking URLs or ones that are too long
There you have it. In a matter of minutes, you can have thousands upon thousands of relevant Display URLs.
One caveat is that these new URLs are completely dependent on how varied and relevant your headlines are. If all of your headlines are the same you can adopt the same principles above with the description lines, however, this is a lot more cumbersome and requires more editing and cleaning up.
You just may find that with this change you’ve improved your CTR, your Quality Score, and are providing ads which are more informative and eye-catching for searchers quickly glancing through the SERP.
About The Author – John Byne
John Byrne, who originally hails from Dublin, has been at iProspect Toronto since 2013. He works on Paid Search and Paid Social for “a well-known auto brand.” You may recognize him from his breakout role as “King’s Bodyguard #6” in Season 3 of The Tudors. Connect with him on Linkedin.
Browse By Category
Your Guide to Getting Organized for PPC Success
Organization is key to success in the PPC industry. Here is a guide to organizing your space, time, resources, and workload for efficient PPC management.
A Reminder About Work-Life Balance
Proper Work-Life Balance Is Something Easily Forgotten. Here Is A Reminder That It's Okay To Have A Life Outside Of Work & Constantly Checking Performance.
A bi-weekly newsletter packed full of resources and strategies that will help make you a better PPC expert.
Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero
The Advertising Principles That Guide Facebook's Decisions
Facebook highlights the advertising principles that guide their decision making in response to recent events.
Smart Ways to Get More Marketing Budget and Better Tools
In this new live webinar, Kristin Vick from Hanapin Marketing and Jeff Sauer from Jeffalytics discuss how marketers can ensure they have the budget they need to be effective with online advertising and get the right tools to make that argument.
The Hero Conf Difference
Of the 50+ search and social conferences you can choose from, we're here to show you why Hero Conf is a can't miss event for busy digital marketers like you.