New! Local SEM Competitive Intelligence
Discover Your Competitor’s PPC Keywords & Ad Copy
Enter your #1 competitor's URL

6 Advanced Excel Tips for PPC

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!