Excel is a crucial part of any Paid Marketer’s arsenal. In this post, we’re going to give a quick rundown of the essential skills any marketer should have when using Excel. Let’s dive in:
Use Case:
Calculating CTR, CPC, CPA, CVR for an ad test analysis
To make a calculation using a function in excel, start with an “=”. Then, select the cells you want to execute a calculation with, separated by:
Use Case:
Use this function when trying to match information in two different lists based on a piece of information they have in common. For example, if list A has a list of Ads and respective impressions and list B has the same Ads with conversion information, you could use vlookup to create list C with the Ad, impressions, and conversion information.
Use Case:
Integrating 3rd party data for ad analysis
Use this function for similar purposes as the vlookup function if the values from one list need to be summed before being pulled in. In the above example, if a given ad had 2 entries with 10 and 5 conversions, the value that would be matched is 15. If the vlookup function was used, excel would pick the first value in the list.
In this example, we are summing the cost for the redacted headlines in blue:
In this example, we are summing the cost for redacted headlines in blue headlines that are also in the “brand” category:
Use Case:
1. Combining category, headline, and description of an ad to create an ad test analysis report
Combine values from multiple cells into one. The formula will usually look something like this “=b4&c4&d4”
Use Case:
Substituting spaces in search terms for “ +” when creating new or negative broad match keywords
Use to replace a piece of text with another. In this example, we are replacing spaces in the headline with “ +”:
="+"&SUBSTITUTE(B2," "," +") will make a result like this: +cell +contents
Use Case:
Parse out combined category, headline, and description of an ad for the ad test analysis report
Text to column is a feature in excel that allows you to separate the values from one cell out into multiple others based on a “delimiting” character like a semi-colon. For example, I could separate the values that are the “Row Labels” column here based on the semi-colon:
Use Case:
Create a list of unique search terms to evaluate for new and negative keyword scrubs.
Remove duplicate values in a range of cells over a single or multiple columns.
Use Case:
1. Isolate ad ID from an ad’s tracking template
2. Isolate LP ID from an ad’s tracking template
In ad builds replace city abbreviations with the full city name (ex: San Francisco for SF)
Find a value and replace it with another. If you find a value and replace it with a blank it can be an effective way of deleting information.
This will delete anything before the text “adid-”. Use the * when you’d like to select everything:
Use Case:
Format numbers in ad test analysis reports for clients
Use Case:
Format ad test analysis reports so clients and account managers can easily see which ads are performing well and which are performing poorly.
Use Case:
To avoid an error when calculating CPA and there are 0 conversions
=IFERROR(H4/J4,”-”)
Instead of displaying #DIV/0! the cell will just have a hyphen in it.
Use Case:
Creating a table that sums impressions, clicks, cost, and conversions for each unique ad.
There is a lot more you can do with pivot tables but this is the most common use case.