"Can you optimize the PPC account? You just graduated with an Econ degree, and landed your first job: marketing for a product recommendation tool for E-Commerce stores. You’ve gone through orientation so now the real work begins.
Welcome to Upsert Upsert is a product recommendation engine that let's you automatically insert product recommendations as natural links in your blog content. They just hired you in your first role out of college! They're B2B, targeting primarily ecommerce website owners who are on the Shopify or Woocommerce platforms, with which they have integrations. It's free to promote your own products using Upsert, but you can make money 'Upserting' the products of other relevant, non-competitive brands. Their main marketing channel outside of word of mouth and referral is PPC - ads on Google search. Learning how this channel works is the main thing that attracted you to the job. You started last week, which was mainly just orientation: you're eager to please and ready for your first task.
Upsert Office – Early Morning
You finished orientation last week, so now you're in early, eagerly awaiting your first task. You get an incoming Zoom call from the Head of Marketing...
Hey\nI hope you're settling in here?\nI was wondering...\nWould you like to take a look at our PPC account?\nIt's due a round of bid optimization - it hasn't been done in weeks.\nLet me know if you get stuck!
PPC (Pay Per Click) Referring to ads on search engines such as Google and Bing, or the business model they pioneered, where ads are bought on a self-service basis, using an auction model where advertisers set a 'bid': how much they are willing to pay per click on their ad.
Bid Optimization Bid optimization is one of the most fundamental skills you can learn as a marketer. To figure out what bid to set for your campaigns, you need to have a solid grasp on how to calculate the key performance metrics: CPM, CTR, CPC, CPA and more. The key metric you need to calculate is RPC: Revenue per Click. This is the amount of revenue you expect to make per click for that campaign. Each campaign, adgroup and keyword performs differently, so will have different expected values and therefore different bids.\nOnce you can figure out how much a click is worth for you, and pivot that calculation by channel, device or keyword, you can rapidly improve the performance of your campaigns. Knowing how to calculate the value of a PPC campaign is a transferrable skill: not every channel requires you to set bids, but if you can calculate the value of a click from any channel, and compare it across channels, you can make much better budgeting decisions to improve your ROI.
Even if it's called \"Pay Per Click\", the real goal is to pay the right amount per Action. So you should always set your 'bid', the amount you're willing to pay for a click, based on how many actions people will take after clicking, and what those actions are worth. \nTake the number of conversions you received on that campaign historically, and multiply that by the average value of those conversions. The formula is: (Average Conversion Value x Number of Conversions) / Number of Clicks = Expected Revenue per Click
Pay per click was just the beginning. The real evolution is pay per action.
Calculating the full range of KPIs Once we've got the data, it'll be time to calculate our metrics. First we need to calculate the main marketing metrics: CPC, CTR, CPA, and others.
Upsert Office – Late Morning
We're eager to please as this is the first task we've been trusted with. Once we get the data we'll calculate the KPIs, Key Performance Indicators, that our boss would expect: CPC, CTR, CPA and others. In the meantime, let's learn about Pivot Tables so we're ready.
KPIs (Key Performance Indicators) KPIs are the key metrics that marketers use to measure performance. They include metrics like CPC (Cost per Click), CTR (Click-through Rate) and CPA (Cost per Acquisition.
Marketing Analysis The main weapon in every data-driven marketer's arsenal is the mighty Pivot Table! It's the workhorse of marketing analytics, because it allows you to quickly summarize the data, run calculations and segment it any way you see fit. The best thing about it: it's drag and drop! Anybody can learn to use Pivot Tables, even if you aren't technical or don't know how to code. \nExcel was initially used in Marketing to manage media plans, but really came into its own when Search Engine Marketing rose to prominence. Because Google allows marketing to be bought self-service rather than by negotiation over the phone, Marketing became more analytical and being able to calculate marketing KPIs in Pivot Tables became an essential skill.
Pivot Tables Here's how to create a Pivot Table\nIf we click into the data, anywhere we want.\nPress CTRL + A, or Command + A if you're on a Mac\nAnd then you want to go into Insert > Pivot Table\nAnd you can choose whether you want it in a new sheet, or an existing sheet.\nSo we've got it in a new sheet, and what you need to do to create a Pivot Table is just drag in the Rows, Values or Columns\nSo if we want to see performance by keyword, we drag in the keyword column here\nand then for the values, we want spend\nYou might think you want spend in the columns, but that's actually something slightly different\nIf you put something in the columns, like device, then it actually just splits the spend by device up here as well\nSo that's how these work and this is all drag and drop, so don't be intimidated, it's pretty easy\nIf you instead wanted to put device over here, now we have a breakdown by device and by keyword, or you can put the keyword in the columns at the top here\n
PPC Data\nHey,\nHere's the data I promised from the PPC account. You should take this and calculate the main KPIs like CTR and Conversion rate, then work out what we should be paying per click for each keyword.\nLet me know if you get stuck!\nBest,
Create a Pivot Table with Adgroup as the Rows, and Spend as the Values. What Adgroup got the least clicks?
Calculated Fields Ok let's learn how to do Calculated Fields in Pivot Tables\nWe're looking at some click data, and some leads data, for a campaign that we ran, and it's broken down by device\nSo if we look at the underlying data\nIf we wanted to calculate conversion rate, we do have that calculated here\nand we might intuitively think \"ok let's just bring conversion rate into the pivot table, and then we've got the conversion rate... but this is actually wrong!\nIf we try and calculate the conversion rate, which is the leads divided by clicks, and then let's just turn this into a percentage, you can see that the true conversion rate is not 48%\nSo let's try and average it - this is summing all the conversion rates, lets try and average it\nAnd you can see it's still wrong! You shouldn't average an average, because it's going to give different weights to the final value\nSo how do you do it?\nWell the real way you do it, is with the calculated fields function\nSo you have to let the pivot table do the pivot table do the calculation for you\nAnd that's the only way to get the true, weighted average\nSo if we go to calculated field, and then we're just going to take the leads, and then divide by the clicks, then hit enter\nAnd now we have this calculated field, and if we select this and click percentage, we have the true conversion rate here\nWe can just call this conversion rate as well\nCool so that's how calculated fields work\nAnd if you wanted to then change how you're summarizing the data\nSo if we take off device and we add adgroup instead, we can see it's still calculating conversion rate correctly.\nAnd that's the power of calculated fields!\n\n
Why is it incorrect to take the average of a metric like conversion rate?
Pivot the data by Keyword instead of Adgroup, and calculate CTR (Clickthrough rate), which is Clicks/Impressions. What was the CTR of the keyword 'product recommendations'?
What are we willing to bid? We've learned how to calculate KPIs in Pivot Tables in Excel, but how do we know what we're willing to pay per click? We've got one more metric to calculate...
Upsert Office – Mid Afternoon
You've finished the KPI calculations warmup, not for the main task: calculating how much we should be paying per click. You ask a mentor for help...
Oh fun, bid optimization! I did this in my first job too :-)\nSure I'm happy to help\nThere are way more complicated ways of figuring this out, but you just need something simple\nTake the historical leads for a keyword, multiply them by how much a lead is worth on average\nThat's the effective revenue\nDivide that by the number of clicks, and that's what you're willing to pay\nAssuming you're running marketing to a breakeven ROAS
ROAS (Return on Ad Spend) Take the revenue or value generated from advertising, and divide it by how much you spent: that's your ROAS. It's similar to ROI, but is easier to calculate in scenarios where you don't know your profit margin. It's common in performance marketing to run your campaigns to break-even ROAS (100%) to maximize volume, on the assumption that organic / unpaid channels will contribute profit.
Assuming Leads are worth $45 each, use a calculate field to calculate ROAS. Do the campaigns break-even in aggregate?
Calculate the suggested bid for each keyword - what keyword are we willing to pay the most for per click?
It's often more useful to calculate the relative change needed in bid, the bid adjustment, rather than simply displaying the suggested bid. This is because it helps you identify mistakes (i.e. if an adjustment is too aggressive) and people usually find it easier to set percentage changes in the Google ads platform. To calculate bid adjustment, take the suggested bid, divide it by the Cost per Click and subtract 1, then format as a percentage.
Calculate the bid adjustments (%) for each keyword. What does the bid adjustment and other metrics tell you about the worst performing keyword?
RE: RE: PPC Data\nThis is amazing! I didn't expect you to get all this done on your first attempt. \nYou don't have posting privileges yet in the account, so I'll get someone to check and action this for you, but keep this up and you'll be running the account in no time!\nThanks again,
" 
