Discover Your Competitor’s PPC Keywords & Ad Copy
Enter your #1 competitor's URL

6 Advanced Excel Tips for PPC

Share on Facebook30Share on Google+35Tweet about this on Twitter130Share on LinkedIn106Buffer this pagePrint this pageEmail this to someone
6 Advanced Excel Tips for Better PPC

Efficiency and time management are two factors that can make or break the success of any PPC manager. Being proficient with Microsoft Excel and knowing the quickest way to analyze data, find insights, and create new PPC campaigns can lead to better campaign performance and less stress during your workday.

Below are six PPC Excel tips I like to use to create reports, analyze data, and quickly create campaigns.

#1: Use SUBTOTAL, Not SUM When Filtering Table Data

Reviewing a massive table of data in Excel can be overwhelming, but applying filters to each header can make analysis a bit easier to take in. I often like to use filters in Search Query Reports to see if the inclusion of certain keyword modifiers like “free” or “cheap” is beneficial to a campaign.

The problem with using SUM when totaling clicks, spend, conversions, or whatever metric you’re analyzing is that SUM always calculates the total table, not just the filtered results that we want to have a look at. This is when SUBTOTAL comes into play. By placing the SUBTOTAL function at the top of your page, you can quickly see what the filtered results total and your sheet automatically becomes a dynamic tool.

You can learn more about how to calculate subtotals in Excel here.

Calculate Excel Sub-total for PPC

#2: Excel Pivot Tables for PPC

PPC is extremely data driven and there is no better way to analyze data than a pivot table. If you’re not comfortable with creating pivot tables, don’t be intimidated. They’re actually pretty easy to create.

The key is having a well-organized table of data that is complete with headers. I use pivot tables daily to manage campaign budgets and to analyze CPA data for multiple accounts. First, I download a campaign level report from Google AdWords as a CSV file and then copy / paste it to an Excel workbook so it looks like the image below.

Excel Pivot Tables for PPC

I then go to the “Data” tab to insert a pivot table on a new worksheet.

Adding Excel Pivot Table

After adding the pivot table, I’m able to drag and drop the different metrics via Excel’s pivot table builder to organize the data and see how the campaigns are performing and if they are on track to spend their allocated monthly budget.

Pivot Tables for Excel

Above my pivot table I have a few manual input values like campaign start date so I know how to set the daily budget in AdWords for the corresponding campaign.

There is an endless number of ways to apply pivot tables to PPC. I’ve used them to analyze data for keyword match types, user locations, day of week, and much more.

#3: Budget Pacing with the NETWORKDAYS Function

This Excel tip accompanies the example above when it comes to pacing your budget throughout the month. In most cases, when a client supplies a monthly click budget we divide the budget by 30 days and we arrive at what our daily spend should be. But not all clients want to run their ads everyday; some may want to run ads on weekdays (Monday – Friday) only.

Now you could look at the calendar for each month and subtract the number of Saturdays and Sundays to see how many weekdays you have to run ads on, but if we apply the NETWORKDAYS function in Excel we quickly determine what that number is. The month of July has 23 weekdays in it, and you can place this at the top of a budget tracking spreadsheet to make sure you schedule your ad spend appropriately.

Network Days Excel

So we’ve saved time by avoiding manually counting the days while simultaneously eliminating the chance of human error.

#4: Count Ad Characters Using the LEN Function

Composing new ads in Excel? Then you need to know if your headlines and descriptions are within the character limits. By using the =LEN function we can easily see if the ad line is over the limit and needs to be edited. Take it a step further and add conditional formatting to cells and you can quickly see if a line needs to be edited.

Bonus tip: Do you use Dynamic Keyword Insertion? If so, then Excel is going to count the {Keyword:…} characters as part of your actual copy, but if you apply this formula: =LEN(A2)-10*COUNTIF(A2,”*{KeyWord:*}*”) then Excel will not include the DKI against your total characters.

Count Ad Characters Excel

#5: Clean Up your Ad Copy with TRIM & PROPER

When writing ads in Excel you might accidentally include a space after some of your headlines and description lines. The LEN function will include the space and could make it appear that your headline is too long. To avoid this add =TRIM to an empty column next to your headline to remove any unnecessary spaces.

Accompanying =TRIM with =PROPER allows us to easily capitalize the first letter of each word which most marketers like to do with their headlines. Now you don’t have to worry about capitalizing your headlines as you type, just format with TRIM & PROPER afterwards.

Trim and Proper Excel

#6: Create Modified Broad Match Keywords

There is not a simple way to convert a keyword to modified broad match within the AdWords Editor or the AdWords online interface, but Excel can make this task fairly easy. To do this, copy / paste the keywords that you want to convert to modified broad match into Excel and in the next column over apply the SUBSTITUTE formula.

Keyword Substitute Excel

By adding the “+” in the front of the formula, we append the first + sign, and then the SUBSTITUTE function searches for any spaces and then replaces them with a space+. It is critical to place a space in front of the + symbol in the last part of the formula, or all your words will be smashed together.

Takeaways

Excel is an extremely powerful tool, but it can take awhile to even understand what it’s capable of. Putting these six advanced excel tips for PPC can save hours of time and many headaches. Hopefully the above tips improve your workflow and give you new ideas for how to report and analyze data. Let me know if you have any other favorites when it comes to Excel and PPC management!

Author

Brad MerkelBrad Merkel is and online marketing consultant with Clear Peach Marketing and specializes in PPC management. Brad is a certified Google Partner and graduated from Clemson University with a degree in Marketing. Connect with Brad on Facebook, Google+, or LinkedIn.

Get our PPC marketing tips!
Join 18,785+ subscribers

NO SPAM, WE PROMISE!

Share on Facebook30Share on Google+35Tweet about this on Twitter130Share on LinkedIn106Buffer this pagePrint this pageEmail this to someone

Related Posts

  • Seth

    Great Article, thanks! The subtotal function – I love learning these quick & easy excel tips that make a big difference. The whole article is great for PPC mgrs.

  • Thanks, Seth. We’re really glad you liked it! Any Excel tips of your own that you’d share?

  • Great article for us SEM nerds. When navigating around excel and especially grabbing your proper data set for a pivot table, using CRTL and SHIFT and the arrow keys are a must. CTRL+SHIFT+RIGHT ARROW+DOWN ARROW would quickly grab your data set.

    CTRL+SHIFT+L is my favorite for adding a Filter.

    I also love using Format Painter as well. Much forgot about capability in excel, word, outlook.

    Conditional Formatting to Highlight cell Rules that contain duplicate vales are often very useful as well.

    I found myself grabbing data in the same way so often that I started creating macros to help automate the process. I challenge everyone to build macros to help any repetitive tasks in excel.

  • For creating Broad modifiers, I was using the options – Append text and Replace text, in Adwords editor.
    Now I have learned to create modified broad match keywords in excel. Thanks for your article..

  • Naresh Toshniwal

    Thanks Brad for wonderful article on Excel, which is powerful tool for SEM. I hope to read full & comprehensive article on the above in future.

  • Umesh

    Hello,
    I suggest you explore ppc timesaver and ppctimesaver.com.
    Its an amazing Excel Add on designed especially for ppc managers.

  • Michael Yost

    Just wanted to chime in hear with an easier way to create modified broad keywords in Adwords editor in bulk. If you clickj on the replace text link you can do a find and replace space with space+ this will replace all the spaces between words in a keyword with the space+ and then you can append text with a + before the keyword. Be sure to use the advanced filters to show only broad matched keywords to be sure you dont switch your exact matches over, unless you are working in a broad match only ad group.

  • CT Chong

    For Modified Broad Match, you may find this keyword wrapper, an Excel Add-in file, useful. It is macro/ VBA, automating the process of adding “+” to words in the keywords.

    For trimming keywords, this Wordz Trim, another Excel Add-in file, can do the job of trimming unwanted space in one click.

    Both are freely available for download. 🙂

  • Pingback: 10 Tools for Effective AdWords Management - Vires Marketing()

  • Pingback: 10 tools for effective AdWords management | aheadvertize()

  • Thank you for bringing more information to this topic for me. I’m truly grateful
    and really impressed. Top 10 PPC Management Companies For Best Services

  • Great list of blogs for motivating people. Appreciate you share.
    http://webdogs.in/

  • Abigail Abi

    Abigail:Good Tips…its useful to work in excel…

  • Awesome insights Brad!