Sometimes I need to do a really simple task about 1,000 times. Although I’d love to say I have the bandwidth to pour over my accounts line-by-line, I simply don’t. Fortunately, I’ve crowdsourced some useful functions that help me maintain sanity and get things done quickly. I’ll share these processes with you and expand on where they’ve served me best.
If/Then for Logical Decision Making
This has come in handy for ad copy management. I have a massive account that has a lot of duplicate ads running and I’m working through to pause off the duplicates.
Here’s how it works
In this example, all we care about is that there are content duplicates. We’re not interested in the metrics (yet) of how one duplicate is doing compared to its doppelganger.
Step 1: Download ads that are eligible to run
Step 2: Add a column that you can concatenate your ads into
Step 3: Sort Concatenate column by ascending
- This is insanely important for your formula to work!
Step 4: Add a Status column
Step 5: Apply the formula =IF(Concat Cell = Above Concat Cell,”Paused”,”Keep”
What’s this mean?
- If the ad you chose matches the ad above it, pause that ad. If the ads don’t match, keep it.
- If you’ve sorted your concats correctly, this will follow a logical succession and cover all of your ads. The first test is a control. You are guaranteed that your first ad’s concatenated cell does not match the column header “Concat”. You’ll know the test is working if it shoots back the “Keep” label.
Step 6: Check your work
- Run through and make sure the new status labels make sense. Don’t blindly trust the machine!!
Step 7: Make an upload sheet
- Google Ads Editor won’t recognize “Keep” as a status, so you’ll want to filter for your paused ads and put them in a separate upload sheet. You can also remove your concatenate column, as Editor will not recognize it.
Step 8: Label the duplicates you’ve just paused so you have a record of what happened.
You can apply this same logic for ad performance in general. Again, as long as you’ve sorted your columns first, you can apply the If/Then statement to quickly weed out and pause underperforming ads (If CTR < 3%, Pause, Keep). I find this test formula tremendously helpful for repetitive tasks and I hope it serves you well too!
Substitute
Formatting keywords can take forever and a day if you don’t use formulas. I particularly hate formatting broad match keywords because Excel is typically a total spaz about text vs. general formatting. Cue =SUBSTITUTE! Following this formula, I can quickly transform my keywords into the appropriate match type.
Step 1: Highlight your Keyword column and change your formatting from General to Text (for good measure)
Step 2: In an adjacent column enter =SUBSTITUTE(“+”&Keyword Cell,” “,” +”)
What’s happening?
- Concatenate a plus sign to the beginning of my keyword, then substitute spaces for space plus
Step 3: Copy and Paste Special (as values) into your keyword column and move on with your life.
Concatenate to build Exact & Phrase Match keywords
Hey, you may have noticed that I love to concatenate things. Concats are essential for formatting Exact and Phrase Match keywords in your upload docs and you can set them up in seconds.
Step 1: In an adjacent column, insert a bracket header and a bracket closer individually
Step 2: Write out your concatenate formula using the “&” symbol (or try =CONCATENATE or =CONCAT)
Step 3: Drag your formula down and Paste Special (values) the results over
Final Thoughts
There are a lot of different ways to skin a cat. These are just a few tools that help me pick up the pace in production work so I can get beautiful campaigns up and running. What types of Excel tricks do you use? Hit me up @ad_jennarator and @PPCHero to keep the conversation going!