Block fraudulent clicks on your Google Ads
ADVERTISEMENT

4 Ways to Use Excel Macros in PPC to Save Time

  • 442
    SHARES
  • 7.3K
    READS
4 Ways to Use Excel Macros in PPC to Save Time

Are you ever looking for ways to complete ongoing PPC tasks quickly, accurately, and without giving up all control?

Me too.

I appreciate all of the automated rules, bidding options, and dynamic ad features that have been created recently but there are often tasks that I don’t want to leave to the automated powers-that-be.

Enter: Excel Macros.

Here are some ways to use this Excel feature that can save you time and help ensure accuracy.

What are Excel Macros?

Excel macros are automated sequences that can be created and personalized to complete a task. Users can record the sequence of keyboard and mouse actions and save it for future use.

Basic Excel Macro 101

Here are the basic things you should know about macros before getting started:

Always save macro workbooks as “Excel Macro-Enabled Workbook (.xlsm)”.

Excel Macro-Enabled Workbooks | SEJ

When opening a saved macro file, you will need to make sure to enable macros otherwise your recordings will not work.

If you do not enable here, you will have to reopen the spreadsheet and reenable.

4 Ways to Use Excel Macros in PPC to Save Time

Macros are found on the View tab of Excel (on Macs).

You’ll see two options: View Macros and Record Macros.

View or Save Excel Macros | SEJ

To record a macro: Head to the View tab and click Record Macro.

Name the macro so that once it’s completed, where you want to store it and a description if you prefer.

You can also create a shortcut keyboard sequence.

How to Record Macros in Excel | SEJ

You will need to name each macro to differentiate the function of each. You can also add a description if you need additional differentiating information.

Once you click “OK” to record, at which point all of your actions will be recorded.

Once you have recorded one or more macros, you’ll be able to go back and view those macros as long as they exist in an open workbook or your current workbook by clicking View Macros.

Viewing Existing Macros in Excel | SEJ

After you are finished with what you want to record, hit Stop Recording in the View top navigation.

4 Ways to Use Excel Macros in PPC to Save Time

Now that you know the basics on how to get started, how to save your spreadsheets correctly and how to find previously saved macros, let’s hop into four ways to apply macros in your everyday workflow.

1. Search Query Analysis & Negative Identification Filters

Download your search query report with desired metrics and filters applied. Make sure to save your spreadsheet properly before recording.

I prefer to filter for search queries that are not added or excluded when I am running this macro.

Sort your data in Excel by your preferred metric – clicks, cost, conversions, etc.

There are a few different macros you can create for search query mining and negative keyword identification:

High Spend, No Conversions

To identify terms that have spent and not converted that may show low intent: Select the Cost column and apply conditional formatting for your desired cost threshold. In this case, I selected terms that have spent over $12.

High Spend No Conversions Filter | SEJ

Conditional Formatting in Excel | SEJ

Select the conversions column and apply conditional formatting for less than 1 conversion or Equal to 0 conversions.

Conditional Formatting for Macros | SEJ

Back to Custom Sort, organize your data by cell color starting with the Cost column and followed by the conversion column with your colored cells at the top.

Conditional Formatting Report | SEJ

Stop recording. This view will help you identify poor quality terms that likely need to be excluded or may indicate you need a change in keyword strategy.

The next macro may take a little longer and will likely need to be adjusted over time as traffic changes and fluctuates.

Identifying Terms with Desired Negatives

Before beginning, identify a list of words that you would like to exclude from your campaigns.

Remember, this process is much longer during macro setup. A little time now will pay dividends the next time you need to run search query analysis.

Take for example a client whose brand name matches with an unrelated product in India. I would make a list of all the terms that you want to make sure to exclude.

Clear conditional formatting that you previously applied for ease. Name your new macro and begin recording.

Select the Search Term column, begin applying conditional formatting using Highlight Cells > Text that Contains.

Begin entering the terms that you want to exclude so that they’re highlighted. Once you have entered all of the words you want to filter out, sort your data by cell color again.

Identifying Negatives with Conditional Formatting | SEJ

Make sure to save frequently to keep your macro up to date as you’re working through your negative keyword list.

Stop recording. Review the terms and identify which terms should be excluded.

A similar macro could also be created for display placement reports.

You can then proceed with the final part of this macro or stop here since you have identified high spenders and any terms with your desired negatives.

Pro Tip: Pivot Tables

If you’re working with a large account or a large timeframe, it may be worth your while to create a pivot table to better group repeat queries.

Before recording you macro, create a pivot table, then highlight between the headers and total rows and paste into a new sheet so that you’re able to better manipulate the data without the constrains of a pivot table.

Negative Keyword Match Type

To add negative keyword match type, copy and paste your highlighted terms into a new sheet without data.

Create a new macro in a new sheet and start recording. (Since the amount of highlighted terms will vary each time you run the report, you’ll have to do this manually before starting the third macro.)

Duplicate your keywords for as many match types as you will have. Create a new column for match type and drag down to apply phrase/exact/broad.

Stop recording and save your sheet.

Add these negatives to your channels.

2. Campaign Builds for Google Ads Editor

Please note this macro will be easier if you are dividing keywords into their own ad groups while keeping match types together in the same ad group.

Begin this process with a list of desired keywords you are building the campaign for in Column A.

Begin recording the macro.

Duplicate keyword list for as many different match types as you will have and consider adding some additional blanks (for future use) if your list isn’t very long.

Insert row above keywords for data headers. Add columns for Campaign & Ad Group.

Campaign Builds for Google Ads Editor Setup | SEJ

Use the =PROPER function to pull over the keyword into the Ad Group column and name the ad groups. If you don’t care about capitalization, pull over your keyword into the Ad Group column using “=(cell)” which will copy over the text.

Campaign Builds for GAE Drag and Drop | SEJ

Create a column for Criterion Type and add each of your match types for each list. Drag down to apply and make sure to apply to any empty cells as well.

If you have blank cells, you can add one more step of going to the Data tab in the top navigation, selecting your data and clicking “Remove Duplicates.” End recording.

Name your campaign and drag down to apply to all keywords. Delete any blank rows.

Spreadsheet for Easy Upload to Google Ads Editor | SEJ

You can now upload this data at the keyword level in Google Ads to create all of the new ad groups, the campaign and the keywords. For broad match modified keywords, I tend to wait to add the plus signs until I am in Editor for ease.

3. Tracking Template Edits

Have you ever had a client completely rework their very intricate tracking? Have a client who is still using manual tagging in 2019?

I’ve been there, too. That’s why I’ve found ways to make quick changes.

Grab a spreadsheet of your tracking templates at whatever level you’re needing – ad, ad group, or campaign.

Google Ad Tracking Templates | SEJ

Begin recording the macro.

For long or detailed targeting, use the Text to Columns feature, which allows you to break down large strings of text into separate columns.

Convert Text to Columns in Excel | SEJ

Find and replace any words that you want to change.

Changing UTM Parameters in Excel SEJ

Add additional columns for new information and utilize the CONCATENATE function to add the new text.

In the example below I wanted to add a variant number and date to the UTM Content tag so I added two new columns: one for a dash that serves as a divider in the tracking as well as one for the variant/date.

4 Ways to Use Excel Macros in PPC to Save Time

Using the CONCATENATE Function for UTM Parameter Tracking | SEJ

Once you have replaced and added any desired information, stop recording. You can now upload this data to the channels and you’ll be able to make these same changes later with the click of a mouse.

4. Adjusting Google Ads Data for Upload into Bing Ads Editor

The Bing Ads Editor Google Import tool is very handy. However, over the years, I have found that new ad types don’t always play nice between these two programs.

I often record macros to account for differences in ads.

Pull in a spreadsheet of your ads from Google. Start recording the macro.

Change the Headline titles to Title Part 1 / Title Part 2 / Title Part 3. Change Description Line to Ad Text. Adjust your tracking templates.

For new ad types, I also like to apply conditional formatting to identify differences in character lengths just to be safe.

Stop recording and save.

Get Started with Excel Macros

There are many other ways you can use Excel macros, but this is a good start if you want to save time while staying invested in your accounts.

Invest a little time now to save a lot of time later!

More Resources:


Image Credits

Screenshots taken by author, January 2019 

Subscribe to SEJ

Get our daily newsletter from SEJ's Founder Loren Baker about the latest news in the industry!

Ebook
ADVERTISEMENT

Andrea Taylor

Outreach Manager at Clix Marketing

Andrea is the Outreach Manager at Clix Marketing. Having spent over 6 years in PPC and 2 years in television ... [Read full bio]

ADVERTISEMENT
Advertisement
Read the Next Article
Read the Next