The Complete Guide To Using Excel For PPC
Excel is a crucial tool for any PPC marketer’s daily workflow. From building campaigns and writing ads to analyzing aspects of your account, your job will be made (a bit) easier by being Excel savvy. This whitepaper will outline the most important tasks that we use Excel for in our day-to-day PPC
Here’s what we’ll cover:
• Ad Copy Testing
• Bid Management
• Account Structure and Keyword Additions
• Analysis and Reporting Tools
Excel is a great tool for not only writing your ads and bringing them into AdWords through the Editor, but also for testing ads utilizing the label function in AdWords. Below are the best features to
employ for ad creation and testing in Excel.
The first thing to note for Ad Testing is the use of the LEN function. Using “=LEN(x)” will allow you to visualize how many characters are in your headline and each description line to assure you have not gone over the maximum amounts (25 characters in the headline, and 35 character in the description lines).
Pivot Tables For Testing
Pivot Tables are obviously a huge part of PPC, and Ad Testing is just another way to utilize them.
First, assure the ads in your test are labeled within AdWords. Then, download the ad data that is
involved in the test, and use the labels in the ‘Row Labels’ section of your Pivot Table as seen below:
Use a calculated field to get the Click Rate, Conversion Rate and potentially Conversion per Impression (or) Impressions Until Conversion numbers. To do this right, click the pivot table and select ‘calculated field’:
At the end, you can format things nicely, and see the results of your latest ad test either account-wide, campaign-wide, or at ad group level as seen below:
Finding Significance Levels in Ad Tests
The last trick to show for ad testing is being able to find significant levels for your ad test. In the example, we use ‘conversion/impression’ data as the metric to judge performance.
First step is to utilize this metric for both the Control and the Test copy as seen to the right: ￼
The formula seen in the formula bar can then be used to get the Standard Error for the Control and Test ads:
After this step we can get the Z-Score with the formula:
Finally, get the P-Value, which shows the significance level of the Ad in Row 6 vs. the Ad in Row 7. The inverse of this would be the significance level if the Ad in Row 7 is outperforming the Ad in Row 6.
This gives you the perfect set up for an ad test by utilizing Excel. You can also run the same test with CTRs and Conversion Rates in a similar manner.
There are so many ways to use Excel to change bids. Formulating things is the best way to go about using Excel for Bid Management. Some of the metrics typically used for Bid Management when using Excel include: ROAS, CPL as goal metrics and Average Position, Impression Share, Lost Impression Share as growth availability metrics. You can have extremely complicated rules or stick to simplicity. First, let’s find ways to get certain metrics, such as Lost Impressions.
Lost Impression Formula
Use the formula “=(Impressions/IS)*Lost IS” to find out how many impressions you’ve missed out on for a certain keyword. Then, use the total lost impressions side-by-side with your goal metric to see how much you’d be willing to raise bids in order to gain more impressions, clicks and potentially revenue.
ROAS Goal Max CPC
We usually recommend the formula of: “=(Revenue/Click)/ROAS Goal (or) Minimum ROAS”. This is a reset for bids activity; it will set your bid to the minimum ROAS you are willing to take based on how much revenue you typically receive per each click on a certain keyword.
CPL Goal Max CPC
From a CPL stand point, the formula to assure you are maximizing leads would be: “=CPL Goal/(Clicks/Leads)”
There are so many ways that IF Statements can be utilized. They can be as intense as using Top vs Other vs Lost Impression data side-by-side with your performance metric, or as simple as using Average Position data side-by-side with your performance metric.
The recommendation is always to utilize impression share, as this can be more telling than average position in a lot of cases. With that being said, note how to utilize IF Statements.
An example of an extreme IF Statement for bidding can be seen below:
With this concept, you can do what works best for your account.
For example: “=IF(AND(IS<50%, CPL<20), 120%)”. In this case, the 120% would mean to multiply the current bid by 120%. Test some things out utilizing the “IF(AND(“ function in Excel.
ACCOUNT STRUCTURING AND KEYWORD ADDITIONS
Creating large keyword lists and structuring your accounts is a breeze with Excel.
Quick Keyword Tool
Have themes in mind as to how you’d like to make your keyword list, but don’t want to go about it in
way where you need to type out each keyword? Apply the formula shown in the image below and create two lists – the formula will match those lists up and provide a keyword list that matches every scenario between the two lists.
Full Campaign Build Outs
Another great tactic using Excel for PPC is to do complete campaign builds. The first step to this is to come up with a naming convention for your campaigns and fill each section out as to how you would like to name your campaigns as seen below:
Then, you can create sub-categories and modifiers to build keyword lists off of as seen below:
Use the formula involved below in the campaign section across all the keyword and ad group builds:
Then, use this formula as the Ad Group:
Once all of this is in place, it is easy to implement the new campaigns, ad groups and keywords into your account from the AdWords Editor. This shows why it is so important to keep a consistent naming convention within an account.
ANALYSIS & REPORTING TOOLS
Now that you’ve learned how to build out campaigns and keyword lists and manage your account with bids and ad testing, let’s look at some ways to analyze your account and report on your findings.
We’ve already discussed Pivot Tables earlier in this guide, but they are so valuable and important that they are worth mentioning again. You can use pivot tables for general account analysis, ads, keywords, landing pages, etc. If you don’t use pivot tables currently for your PPC data, we highly suggest you start!
To make a Pivot Table, simply click the shown button and Excel will put your data into a pivot table. From there, you can start manipulating data in any way that you choose using the pivot table builder.
• Use calculated fields for metrics like cost/conversion, CTR, and conversion rates
• Use conditional formatting on a pivot table to utilize heatmapping
Conditional Formatting – Heat Mapping
Conditional formatting is great way to get quick insights from a set of data. Specifically, it’s a function that allows you to automatically change the format of a cell based on its values and the parameters that you define. It can modify text color and format, cell colors, as well as adding data bars and other graphics.
You can use it for dayparting and visualizing data. There are multiple pre-set rules that you can use, or you can create a custom rule.
Here we show a heat map of conversion metrics by time of day. By applying the conditional formatting, we can easy pick out any times that aren’t performing well and set a bid adjustment accordingly.
If you don’t want to highlight the entire cell in one color, choose the icon option. (Below)
Grouping does exactly what it sounds like: it groups rows or columns together.
The most frequent use of grouping that I’ve seen, is calculating your optimal average position. To do this, download a campaign or keyword report segmented by week (or day). Then, create a pivot table with Average Position as your Row Label. Then select the rows, right click, and click “group”.
This gives you an easy-to-digest view of performance by ad position.
Text-to-column is a neat little tool to help you separate data that’s contained in one column. Two of the most practical uses I have found for this function are manipulating destination URLs and splitting campaign names. For example, if I structure my account like this: State – Theme – Match Type, then I can use Text-to-column to easily pivot my data by one of the three categories.
Navigate to the Data tab and select Text-to-Columns under Tools.
Copy and paste the campaign name into a new column before using the text-to-column feature. This way you will still have the original campaign name intact.
Macros are a recorded series of actions that can automate simple tasks. For example, at Hanapin we frequently use account snapshot macros that give the user a quick overview of number of keywords, match types, and quality scores across the account.
You can use macros to analyze your whole account, including match type analysis, ad copy analysis,
and impression share analysis.
To record a macro, navigate to the Developer tab in
Excel, and click Record:
Excel makes projections easy. Typically, the formula used is MTD + ((Last 7/7)*Days Left In Month), but of course you can adjust this to whatever method of projecting you prefer. This can be used for cost, conversions, clicks, whichever metric you want. The beauty in using Excel is that once you set up the basic formula, you can copy and paste in your data and it will automatically calculate it for you!
Use the =today( ) function to automatically insert
today’s date. Set Days Left to subtract today from
month end and add 1 to include today.
Charts & Graphs
Charts and graphs are great to show to clients, and can be very helpful in recognizing trends to figure out what’s happening in your account. Select the data you want to represent in a graph or chart, and select the chart layout you want.
Make sure your graphs are easy to read and avoid unnecessary clutter.
Now that we’ve gone through how to create graphs, adding a trendline is a great way to quickly see an overall performance trend. As you can see here, there are a few different options as to what kind of trendline you would like to use:
This gives clients (or bosses) an easy way to quickly see how the account is trending overall.
We’ve all been in a situation where you have a huge Excel file with tons of info. The problem is, you can’t scroll down and still see your headers. All you have to do is Freeze your header row and voila, it’ll stick! No more frantic scrolling back to the top to remind yourself what those numbers mean.
Using freeze panes not only helps when viewing large amounts of data, but also assists in making more client-friendly reports.
This tool allows you to copy the formatting from one (or many) cells and copy it to another.
• Double-click the icon and you can paste the formatting on multiple selections.
• You can use this tool to copy image formatting too!
WORK TOWARD BECOMING A MASTER
Once you become an Excel expert, analyzing your account, building campaigns, and implementing ad testing cycles will become that much easier and faster. With experience, you’ll immediately know which Excel functions and capabilities will solve which problem (or answer a question) in your PPC accounts.