You know those accounts. The ones with tons of Campaigns, hundreds of Ad Groups, and ads with Destination URLs specific to one of those levels. Oh, the humanity.
Dealing with that kind of scale can feel daunting and I’m an impatient person who wants to find the most efficient way to do things. I came up with the following steps during a massive account restructure after we took over a beast of an account that needed a lot of help. Some Ad Groups had multiple ads going, some didn’t. Some were tagged correctly, some weren’t. Many keywords needed to be reorganized…the list went on and on.
Something I found that was taking up time was trying to figure out which Ad Groups needed new ads inserted for testing, then creating the Ad Group-level tagged URLs that would have to accompany them. The thought of going one-by-one made me want to weep, so I broke it into a process that made the time commitment minimal.
There are probably a million ways to do it, but here I’ll share how I created a process for myself around it – maybe this will help you the next time you’re staring down the long barrel of ad copy uploads!
1. Figure out which Ad Groups only have one ad and therefore need some new ones for testing.
To help preserve quality score, we wanted to keep the ad copy versions that had been working. For some Ad Groups, that meant only one, while others had a few that were doing well. We first paused the underperformers, but that left us to figure out: which of these hundreds of Ad Groups now only had one and needed more?
Easy!
In Editor, I filtered for ads that were enabled. Then I exported those to Excel.
Next, I made a pivot table, where I dropped in the Ad Group as the row, and the Headline as the Value. The pivot table defaults to showing you the “count” of the Headlines, which means how many there are. So if an ad group showed only one Headline, I knew it only had one ad:
Now I had my list of the Ad Groups I needed to get ad copy challengers into!
2. Determine the tagged URL that each ad group would need.
Thanks to a handy URL builder my colleague Amy had previously created for this account, this was really easy. You have to create this more to the specifications of what your account needs, since all tagging is different, but the logic remains the same.
Determine what your URL tagging structure is for the account. Usually it’s made up of your base URL, and then different referral pieces for things like search engine, medium, ValueTrack Parameters, etc. Dissect what the pattern is of your URL, and then splice those pieces so there’s one column per piece of your URL string.
In our example, the tagging included the following:
- Campaign Name
- Ad Group Name
- Root Destination URL
- Traffic Source
- Stock Tagging String (this was a tag with ValueTrack parameters that was the same throughout the whole Account)
- Destination URL to Use (this will be blank and is where your formula will go)
All but that final “Destination URL to use” is information that is easy to cut and paste. I pasted the list of Ad Group Names in from the list I created in the first step, and then filled in the Campaign Name, Root URL, Traffic Source, and Stock Tagging String as a copy and paste into as many cells down as I needed.
3. Use Concatenate to create your Destination URLs.
Finally, in the “Destination URL to Use” column, I’d put in the Concatenate formula. It would merge together all of those fields to give me the final Destination URL I needed. Note for that step: it’s really important to make sure any spacing, characters, etc are accounted for, so do a test one to make sure it creates the URL the way you want before pasting it all the way down.
Now you have a handy list of the Destination URL assigned neatly to those hundreds of Ad Groups…are you dreading the part where you assign it to ad copy? You don’t have to.
4. Use VLOOKUP to pull your created URLs in.
This step is especially helpful if you have multiple ads per ad group that you created.
First: I created all my new ads in a single tab within Excel so that I could upload them to Editor, but left the Destination URLs blank.
What that meant in the end was multiple entries for each Ad Group, and a sea of copy that needed the correct Destination URL applied to it.
Second: I created a separate sheet in the workbook, and named it Destination URL key. So now I had two tabs in my workbook:
Remember the URL builder worbook? I pasted the Ad Group Name and the URLs that were created there into the Destintation URL Key tab.
Third: Then I went back into the Ad Copy tab, and scrolled to the Destination URL field, which was still blank. I entered a VLOOKUP formula, having it reference the Ad Group Name in the Destination URL Key tab.
I filled out the formula so VLOOKUP would look at my Ad Group name, find it on the Destination URL Key tab, and pull the associated URL from there as the one to use in my Ad Copy tab. Voila! I now had my Destination URLs filled in easily, using the ones I’d created in with my URL builder worksheet.
(And, if you’re not familiar with VLOOKUP – learn it! It’s awesomeness. There are a million tutorials online.)
In the end, I wound up with a tidy, upload-able sheet that I could post to Editor. It had all my new ads, all the correctly-tagged Destination URLs, and I got back a whole lot of my time!
