Hello everyone watching. Thank you all for watching my third weekly wisdom video. I am Joel Bondorowsky, Founder and Executive Director of PPC Designs. I am also SEMrush’s PPC academy professor. For this week’s weekly wisdom video, I am going to show you how to set bidding with Google Ads.
Why is bidding so important? Because when you are doing PPC advertising, you are empowered by being able to choose the maximum amount that you would like to pay per click for any given keyword. But, in order to do pay per click bidding, you need to know what that amount is; this is something that is mysterious to people, especially when they are getting into Google Ads, but really the formula is quite simple.
The formula is basically one that lets you know what the value for each one of those clicks is. After all, to be profitable with a pay per click campaign, you need to pay less per click than the value that those clicks bring. So understanding the value per click, or VPC, is the essential component in understanding the amount you want to set as your max CPC bids with Google ads. So let's Begin.
Starting With Gathering Keywords in Your Account
The first thing you are going to do is log into your Google Ads account; once you are in your Google Ads account you are going to download a report — a keyword report that gives you the data you will need to determine what those max CPC bids are. There are two ways to extract this report; one is from the reporting section of Google Ads, and the second is by viewing a keyword list in the keyword view in Google Ads and clicking on the download icon.
Both methods will effectively download your keywords with the stats you need to download bids. For the purpose of this demonstration, I will walk you through this through the reporting section. The reporting section has a few advantages. The main one being that you can save the settings for your report so that you can easily download the report on a regular basis. You can even choose to have it run on a schedule so that you don’t have to log in and manually download it.
This option is important because bidding is something that you are going to want to do on a regular basis. You are going to want to regularly download these keyword reports.
Run these formulas that I am going to lay out for you.
Also, adjust bids on words that are profitable so that you are making more off of them and lower bids on keywords that are losing you money.
Once you log into Google ads, click on reporting.
Click on Custom.
Set the conditions for the report.
Example: Choose the time range for the report.
Filter just relevant campaigns.
Add dimensions and metrics for the reports.
Drag dimensions over to reporting section:
Ad group name
Search keyword status
After we add our dimensions, we will want to drag over our metrics.The metrics that we are going to want to choose are.
Next click download as CSV.As I have mentioned before, it is sometimes a good idea to schedule these reports so that they are run on a regular basis. You can also save them so that you can easily access the same report whenever you would like to run it.
Now that you have extracted the data from Google, go ahead and open your Excel spreadsheet.
Important Tips for Excel Spreadsheets
Next, save the excel file as an XLSX file. Select the keywords column, and find and replace equal signs to blanks. This is necessary if you have broad modifier keywords that start with a + symbol. When you open them up in Excel, it can think its a formula and try to equal them to something so we can get around this by finding and replacing them as I just outlined.
Now we will want to clean up the report a little bit by removing or hiding some of the columns that aren’t relevant for this task that we’re performing.
We will start by removing the top two rows that show the date range and name of the report. Next, we can go ahead and delete the currency column; we don’t need to be seeing that the report is in dollars over and over again. In the example in the video, we are setting bids on keywords that are run over multiple campaigns and ad groups. To drown out more noise, let’s go ahead and hide those columns.
Now that we are done we can go ahead and look at what we have.
At this point, it’s a bit overwhelming. If you scroll down a bit, you’ll see that you have a keyword list that has over 3600 words. When you are glancing at it, you really can’t deduce or understand a thing about it.
I’m going to give you a tip right now, a very important tip.
Whenever you’re looking at a report such as a long keyword report, a placement report, or any type of report, and you want to make sense of it; the best thing to do is to sort by top spenders down.
So I will go ahead and do that. Since we want to see the most significant spenders first, let’s go ahead and select the column for cost and sort it from top to bottom.
Next, we will start our calculation for bids. You may have heard me explain this in the past. There are really two values needed to determine the value of a keyword — that is the keywords conversion rate and the value of the conversion itself.
I will repeat that.The conversion rate represents the percentage of clicks that produce sales. The value of the sales that those clicks bring. I think it is pretty simple to see that if you multiply the two, you then have the value per click.
Let’s say that the value of a sale is $100 and half of the people that went to your site purchased, the value of one person is $50. It is as simple as that. Now let’s say that 1% of the people convert, which is a much more reasonable conversion rate when the value of a sale is $100. When that happens, you multiply your 1% against $100, and you have a value of $1 per click. That means you can pay $1 per click for a specific keyword and break even. Obviously breaking even is not our goal with any advertising effort, but in order to
get there; we need to first understand the value of the clicks that we are buying traffic
with and our PPC campaigns.
Calculating Conversion Rate
Let's calculate our conversion right — labeled as CVR. The formula to calculate the conversion rate is quite simple, take the numbers of conversions and divide them by the number of clicks. Which I will enter in cell I, and then drag it all down by double clicking on the bottom right corner of the cell. Now we want to present all the numbers as percentages; select the column and click the percentage symbol. After we want to move the decimal spot over 2 spaces.
Now we are ready to calculate the VPC or value per click. We will go ahead and do this in column J using that very simple formula that I explained before. Now, in this example that I am giving right here the value
of one conversion is $50. So in cell J to apply this formula for value per click, which is the value of the conversion, $50 multiplied by the conversion rate. And we will drag it down again as we did with the conversion rate formula by double-clicking on the bottom right-hand side of that cell. Since this value is in dollars, let's go ahead and select these cells and click on the dollar sign at the formatting of such and wow, there you have it.
Now that you have this data, don't think you are done!
The value per click for this entire list of 3,600 keywords is determined. We can go ahead and set them as our max CPC bids and ad words. Actually really not quite. I am sorry, I wish it was that simple, but no, it is really, really, really, really way more complicated than this. Why? Because this formula only works to give you the value per click of each one of these keywords if you have confidence in the numbers.
So let's go ahead and look at this report of it differently so that I can explain this to you a bit better. Let's now
sort from top to bottom on the "clicks" column. And I am going to give you some news that might look a little bad. The bad news that I am going to tell you is that we really don't have any confidence in conversion rates for any of the keywords after about the 30th or so in the list. The reason is that any of the keywords below 30 really don't have significant statistics, which allow us to accurately calculate their conversion rates in order to accurately determine their value per click.
Flukes & Convergence
If there is a statistical fluke on any of the keywords at the top of the list, which would give one of those keywords may be one or two more or fewer conversions, the conversion rate would more or less be the same. Like for example, if there is a keyword with 500 clicks and 50 conversions and you give it 52 conversions; the conversion rate is going to be very close to what you will as if it was 50. However, if you were to add or take away one or two conversions for one of the keywords after these 30, that extra rate too would make a big difference.
So if one or two extra or fewer convergences can make a big difference in the performance of a word, you don't have dependable data to determine a conversion rate in order to give you a bid that you can trust. Well, this sounds awful, doesn't it? I promise you that you could control your campaigns. You can make them profitable by understanding your value per click and setting effective bids.
It is really not that bad, and I will tell you why. So I will go ahead and select the spend of the first 30 keywords in the list and see something that actually makes things start looking a little bit better. The value of those first 30 words is $30,233.40. However, some of the spend of all 3,600 and so keywords is almost $54,000; this means that 31 keywords comprises 56% of the spend of over 3,600 words. That is right; 31 keyword,s that is about .8% of the keywords in this account is spending 56% of the total. Well, now it is becoming a little bit better. I mean, I am starting to realize that I can at least have control over 56% of the spend that's already getting
Sum Up the Conversions
And now to make the situation look even better, we can sum up the conversions of those first 30 keywords and compare them to the sum of that entire column as we did with the spend. And we will find that those first 30 words are responsible for 64% of the conversions in the entire account. This means that they are performing with a better cost per acquisition than the overall average for those 3,600 words. They are bringing in a sale at $47 while the rest of the account, while the whole account is doing it at $53.
As I said earlier, the value per conversion for this account is $50. So if we want these campaigns to be profitable, we can't be spending $53 per acquisition. So a very simple way to turn this account into a Google Ads money making machine without digging in too hard into statistical analysis and coming up with complex formulas in order to figure out how to give the best bid possible for that long list of more than 3,600 words that we don't have significant stats on, is to simply focus on that first 30 keywords which are giving us a positive return on investment.
And either cut out completely the rest or drastically lower their bid, so that way their spend is within check and they are also bringing us as a whole, a positive ROI. So a very simple way to turn this accounts into a Google Ads money making machine without digging into difficult statistical analysis on those 3,600 and so low stats keywords, is to simply focus on the 30 words that you can easily set accurate bids with that very simple formula I laid out. And either drastically cut bids as a whole on the words that you don't have stats on so that way immediately they stop becoming a drain in your account. Or if you want to be even more drastic, you can just cut those keywords out completely.
Just pause them, stop spending on them. Once you are able to get good performance with the main set of words that are very dependable, they are very manageable 30 words; you can start to introduce those other words back into the account. They will start to get statistics. You will be able to start bidding on them and in an effective manner so that they are also contributing to this beautiful Google Ads moneymaking machine
that is turning a profit for your business week in and week out.
So that is it for my Weekly Wisdom video for this week. Thank you for watching. Join me the next time, and we will revisit this report and show you how to deep dive into it further to have a better set bids,
optimize a bit better for profit, and also optimize the traffic you are driving with your campaign. This is all to enhance your PPC performance to get the most out of your Google Ads efforts and dollars.
Joel's Other Weekly Wisdom Videos