Some skills are basic in PPC marketing, and you need to acquire them before even starting to think about this job. I think these days, every person should know even some programming, as there are many essential tasks that can be solved using these tools. Luckily, ChatGPT has made everything much easier, but it may still feel too difficult at the beginning.
Here's the script of the video, but I highly recommend watching it instead of reading.
Gosh, someone doesn’t have blind typing skills, and I highly recommend using an online typing tutor like Ratatype. It will save billions of hours and nerves if you have to work a lot with a computer.
Unfortunately, Google Sheets is also like some alien technology—some sort of magic for many people. I’m not going to explain everything about it in this video, but I will share some formulas and tools I use for 90% of my work. In the future, I will also create a video with advanced formulas you could potentially use to automate your job.
First, we need some data to work with. Let’s imagine we have three types of products: green, red, and yellow apples. We run Search, YouTube, and DemandGen campaigns. We target two states: California and New York.
Our naming convention for the campaigns is Type of Campaign, space, Product, space, State.
We have been running campaigns for one month and have data about impressions, clicks, cost, and conversions for every day. Our goal is to aggregate the data to understand the results of every campaign and product.
Note: The data here is randomized, and some steps are additional to demonstrate as many formulas as possible.
Let’s start with an estimation of Click-Through Rate (CTR) and Cost-per-Click (CPC)—two metrics that are important for measuring success if the data on conversions isn’t sufficient.
To do this, we need to add additional columns. Right-click the column header and select Insert 1 Column Left. After that, double-click the cell, type =, click the “Clicks” cell, then type a forward slash (/), click the “Impressions” cell. As you can see, Google Sheets suggests some auto-fill options, but we’ll skip them for now.
To copy our formulas, let’s use the Fill Handle tool. When you hover over the lower-right corner of a selected cell, you’ll see a small cross. Click and drag the Fill Handle to copy the formula or value from the selected cell to adjacent cells, either horizontally or vertically. Double-click it to auto-fill your column with the formula.
Let’s use another format since it’s a percentage. You can do this in two ways:
Find the Toolbar above the working space and click the percent icon. Use the hotkey Ctrl + Z (recommended) or click Undo on the toolbar to revert the change.
Alternatively, use Format from the Menu Bar. In the Number section, select Percentage format.
To calculate CPC, insert a column and repeat the steps we followed earlier. As you can see, we encounter a "divide by zero" error since the YouTube campaign may have costs without any clicks. To prevent this, use the IFERROR formula, which shows a specified value if an error occurs (it will be zero in our case). After applying this formula, let’s switch the column to a currency format.
Next, let’s look at the Filter tool. This tool allows you to sort, filter, and edit specific cells. It is located in the Menu Bar under Data > Create a filter.
To filter specific cells, you need to select the desired range. Alternatively, if you want to filter the entire table, it’s enough to click any cell in the range and create a filter.
Let’s sort the table by cost. As you can see, the “Search Yellow California” campaign spent the most on January 20th. However, this isn’t really useful information because we want to aggregate the data.
Before we do that, let’s create an additional column for the first day of the week. This will allow us to compare data week-to-week.
We’ll need the WEEKDAY() formula with two arguments. The first argument is the date, and the second specifies which day is considered the first of the week (1 is for Sunday, 2 is for Monday, etc.). I count Monday as the first day because our workweek starts on Monday, and we send weekly reports on Monday. The formula returns the weekday number.
To find the first day of the week, we calculate the date minus the weekday number plus one. This is because we are counting the distance between today and the first day of the week, not tomorrow and the first day.
Although this is a simple formula, at the beginning, I recommend keeping a reference list of all the formulas. I’ve prepared a document with formulas you can use in your work.
https://docs.google.com/spreadsheets/d/19SOlpM8LxtKwJh2nHrb4b4JTL5KyHDyzuOZ2eP7mb-M/edit?usp=sharing
Now we have a table with the following columns: day, week, campaign, impressions, clicks, CTR, CPC, cost, and conversions. Next, I want to see the total amount each campaign spent. There are two ways to calculate it.
Let’s start with the more complex method because this is the last time you’ll use it before transitioning to Pivot Tables. Usually, I use this method to merge data from different tables.
If we want to aggregate data by campaigns, we first need to import the unique values of campaigns using the UNIQUE formula. Create a new sheet and insert the required range.
Next, using the SUMIF formula, you need to set a condition to add values to the respective columns. Start by inserting the required columns. Just copy and paste them: Impressions, Clicks, CPC, etc.
For the SUMIF formula, the first argument is the range of campaign names, the second argument is the specific campaign name (cell), and the third argument is the column for impressions. You don’t have to specify the range for blank cells, as they won't be added. Use the Fill Handle to apply the formula to all the values.
I assume you don’t want to rewrite this formula repeatedly. The only range that changes is the metrics column. To fix the positions of the range and campaign column in your formula, click the formula and add a dollar sign ($) before the row and column references. This ensures that the references remain fixed when copied.
Alternatively, you can use the hotkey F4 to toggle between reference types:
The first press fixes both the row and column (e.g., $A$1).
The second press fixes only the row (e.g., A$1).
The third press fixes only the column (e.g., $A1).
The fourth press removes all fixes (e.g., A1).
Finally, copy and paste the adjusted formula, and use the Fill Handle to apply it across all relevant cells.
Let’s look at the CTR, and notice that it’s over 100%. This happened because it’s a calculated metric, so it needs to be recalculated for the aggregated data. Let’s fix that. We’ll also calculate the Cost per Action (CPA).
Now we have the aggregated data. Let’s determine which campaign spent the most. Create a filter, sort everything, and… nothing happens. This is because the table contains formulas, and they are tied to the campaign column. To address this, you need to paste everything as values. Simply copy and paste all the data as values. The hotkey for this is Ctrl + Shift + V.
Once that’s done, you can see that Search Red California spent the most among the campaigns. Meanwhile, Search Red New York and DemandGen Yellow New York brought in the highest conversions, and YouTube Green New York achieved the lowest CPA.
Let’s make the data more visual. Select the CPA Range, then navigate to Format → Conditional Formatting. Under Format Rules, choose a Color Scale and apply a gradient from green (minimum CPA) to red (maximum CPA).
Next, add another rule for the Conversions (Conv) column. Select the Conv Range and make the scale opposite: red for minimum values and green for maximum values.
Finally, calculate the total values. A common mistake at this stage is aggregating everything without careful consideration. For example, YouTube, DemandGen, and Search campaigns have different CTRs. To ensure accurate insights, calculate totals differently for each type of campaign.
To categorize campaigns by type, use the naming pattern: the first part of the campaign name indicates the type of campaign (e.g., Search, YouTube, DemandGen). Let’s create a new sheet and name it Map. Copy the unique values of campaigns into this sheet using the formula: =UNIQUE(Week!D:D)
Then, using the SPLIT function, let’s split the campaign names into separate values. The first argument is the column containing the campaign names, and the second argument is the delimiter you’re using. As you can see, it also splits New York, so let’s take an additional step by using the SUBSTITUTE formula to replace New York with New_York using an underscore. Name the columns Type, Product, and City.
And now it’s time for VLOOKUP! <<Insert VLOOKUP song>>
The VLOOKUP (Vertical Lookup) formula in Excel is used to search for a value in the first column of a table and return a value in the same row from a specified column.
In our case, the first argument will be Campaign, the second argument will be the range from the Map sheet, the index will correspond to the column we need (e.g., 2 for Type, 3 for Product, 4 for City), and [is_sorted] will be set to FALSE. Honestly, I still don’t know how to use the [is_sorted] argument; I’ve never tried doing it any other way. Don’t forget to fix the range so it doesn’t mess up your data.
The main issue with VLOOKUP is that you always have to change the column number manually, and there’s no way to auto-fill it. Therefore, learn an alternative formula as soon as possible: =INDEX(<<RANGE OF VALUES YOU NEED>>, MATCH(<<VALUE>>, <<RANGE YOU'RE LOOKING UP>>, 0))
The advantage of this formula is that if you fix the RANGE YOU'RE LOOKING UP, fix the column of VALUE, and fix the rows of the RANGE OF VALUES YOU NEED, you can copy and paste this formula without needing to manually adjust the numbers.
So now we have the Search, YouTube, and DemandGen campaign types. Let’s summarize their values using the SUMIF formula. Remember that CTR, CPC, and CPA are calculated values.
First, add rows with dedicated totals and manually assign their types. Now, using the SUMIF formula, we can aggregate the respective data. Pin the rows for the Sum Range, as well as the columns and rows for the Criteria Range, to prevent errors. Also, pin the Type Values column. If everything is done correctly, you should only need to copy and paste the data.
Let’s format everything properly. Don’t apply conditional formatting to the newly created totals, and remember to calculate the required metrics.
For the totals, simply SUM all the values above.
Done! Now we have enough data to take actionable steps. First of all, YouTube has the lowest CPA and spending, so it’s a great opportunity to increase its budget. Note that these metrics are imaginary, and in reality, it’s unlikely to achieve such results with such a small budget, but that’s outside the scope of this discussion.
We may also need some additional calculations. Your goal as a PPC specialist is to find a balance between aggressive spending and a steady flow of leads. This is part of a broader strategy and is a question that should also be discussed with the client.
From a technical perspective, you can already observe some results. To evaluate them, you need to calculate the Average and Median of Conversions and CPA. Ideally, you want more conversions and a lower CPA, but to achieve better results, you need to strike a balance between these metrics.
Let’s use the AVERAGE and MEDIAN formulas for this. In our example, the numbers are pretty close, so we can use them, but in reality, they may have a huge difference. So, in some cases, you ideally need to use the 2-sigma rule to exclude anomalies or values that could potentially affect your data significantly. However, sometimes it’s enough to use common logic. For example, in our case, if we have a CPA of $1,000, it’s obviously an anomaly.
So, our ideal CPA is $122.80 with 1 conversion. Anything with a CPA lower than this should be scaled. Of course, this excludes campaigns that have spending over 2-3 CPAs. This is an average number, but as you can see, Search Yellow New York doesn’t generate purchases, which is a red flag to either turn it off or significantly rework it.
Also, to estimate this number, I propose including an additional CPA Enhanced metric, which would treat CPA as equal to the cost if there are no conversions.
To take action, let’s add an Actions column and fill it out.
For Search Yellow New York, we should turn it off. If you're a beginner, don’t dig deeper into this campaign; it’s easier to focus on what works. If you're advanced, you know which parts of the Strategy, Targeting, and Messaging need to be changed to make this campaign work.
For campaigns with CPA lower than Average CPA + 20%, the bids should be increased. For campaigns with CPA higher than this, the bids should be decreased. You can also leave CPA within a +-20% range without making changes, but let's not focus too much on that.
OK, next we may want to analyze other dimensions such as Product, City, and by weeks. Of course, you can struggle with creating new tables using SUMIF, but luckily, Pivot Tables were invented. So let’s go back to the raw data.
Just open the raw data, select everything, go to Insert, and create a Pivot Table on a new sheet.
Done! Now you can easily manipulate dimensions without additional formulas. One small issue: We previously changed the format type to currency, which also affected text values, so make sure to revert it back to plain text. Go to Format, then Number, and select Plain Text. If you don't do this, you’ll see that Cost, for example, transforms to ‘ Cost ’, with spaces before and after the word.
Let’s create the previous table in a few clicks. Rows are the campaigns, and the Values are impressions, clicks, cost, and conversions. Then, add the calculated fields for CTR, CPC, CPA, and CR using the correct formulas. Also, let’s delete “SUM of”. Just delete it manually. Note: If you're doing this in Excel, don’t delete the space before the metric name. Otherwise, it will show an error because you’re creating an additional column with the same name.
One downside of this method is that you can’t manually place things in any position, like I did for the totals. Let’s add an additional table above the current one for the totals. Simply copy the upper-left corner of the table and insert it under the existing table. Use the Edit menu to change "Campaigns" to "Type." Now you have the table for the totals. One limitation is that you can’t sort it properly without an additional trick. I usually prefer to see Search first.
Let’s place the total numbers above the campaign numbers. Just cut them using Ctrl + X, and in a few steps, place them in the correct spot.
Copy any table again and replace the raw value with weeks. Now you can see the numbers in a week-to-week format. The last week is looking good; we have the highest number of conversions and the lowest CPA. Keep going this way!
Copy and paste the table again and change it to the Product dimension. As you can see, red apples perform better than other campaigns, yielding more conversions for a better price.
Copy and paste the table and change it to the City dimension. California and New York perform pretty much the same. But remember, these are imaginary numbers. In many cases, New York will spend more than any other state in America, and the revenue will be higher.
These basic tables are enough for you to track the performance of your campaigns. Note that everything I showed can be done using the Report Editor, but there are reasons for you to still use Google Sheets or at least Looker Studio, and ideally both, to track your performance.
Also, I have a little task for you: a home exercise! Here are 9 sheets. Using the information from the video, you should now be able to perform basic analytics on the data. Below are the descriptions of each task. It's better for you to try to solve them yourself, but I will attach the solution in the last sheet.
https://docs.google.com/spreadsheets/d/1k-YyLRMaoOvvR2UuNoodn_e7WtGcAFW0nXeGp4a3Cw8/edit?usp=sharing
When I started working at my previous agency, they sent me a similar initial task, and it was funny because I completed it easily. But if you're new to the world of PPC, you may have some trouble with it the first time. But understand that this is your threshold to proficiency. This is something you will be doing 50% of the time. If you don't want to learn how to do it, look for another job.