You've finished calculating the main KPIs, but as for calculating how much we should be paying per click, you have no idea. You ask a mentor for help…

Oh fun, bid optimization! I did this in my first job too :-)
Sure I'm happy to help
There are way more complicated ways of figuring this out, but you just need something simple
Take the historical leads for a keyword, multiply them by how much a lead is worth on average
That's the effective revenue
Divide that by the number of clicks, and that's what you're willing to pay
Assuming you're running marketing to a breakeven ROAS

It’s important to make sure you get the most out of your Google Ads campaigns. One way to do this is to optimize your bids. Bid optimization is the process of setting the maximum amount you are willing to pay for each click on an ad. This can be tricky, as you want to pay as little as possible while still getting the most out of your ads. To do this, you’ll need to look at the data and calculate the return on ads spend (ROAS). 
ROAS is calculated in a pivot table. To edit the pivot table, click edit and you can see the keyword and different columns. You’ll also have calculated fields such as CPM, CTR, etc. which will give you a good idea of why the ROAS is good or bad. For example, if you have a keyword that has a good cost per click but a poor conversion rate, the ROAS will be low. 
Once you have diagnosed the data, you can work out the suggested bid. This can be done by multiplying the lead worth (e.g. $45) by the leads to find the revenue generated. Then divide the revenue by clicks to get the revenue per click. Then calculate the cost per click and divide by clicks minus one. Then divide the two together. This will give you the amount higher the revenue per click is than the cost per click. 
Alternatively, you can add a calculated field in the pivot table to compare the revenue per click to the cost per click. This will help you decide on a suggested bid. For example, if the CPC is 55 cents, that could be the same as the suggested bid. If the CPC is 70 cents, you could drastically cut the bid.
To make sure you don’t make drastic changes, you can also add a dampener such as a 20% cap. This means you will never increase the bid by more than 20%. 
Bid optimization is a great way to get the most out of your Google Ads campaigns. By looking at the data and calculating the ROAS, you can determine the suggested bid. However, it’s important to keep in mind that this is not always foolproof and you may want to let Google set the bids. You can also add a dampener such as a 20% cap to make sure you don’t make drastic changes.

Bid Optimization

0:00 Okay, you want to figure out how much to pay per click for your Google ads campaigns. This is the raw data and we have the keywords ad groups and then all of the spend clicks click through etc.
0:15 But the main thing we're interested in is the ROAS, the return on ads spend. And this is calculated in a pivot table.
0:22 So if you want to edit the pivot table, click edit, and then you can see here, we have the keyword and then the different columns.
0:28 Now we have these calculated fields. So you can see the CPM, the CTR, et cetera. I'll give you a good idea of why the ROAS is good or bad.
0:37 For example, for product recommendations, keyword, the cost per click is pretty good, but the conversion rate is pretty poor. And that's why the ROAS is low, right?
0:50 So you can diagnose those and kind of dig into that data as you need. But the the main thing we care about is that a lead is worth $45 to us.
1:01 So the return on ads. And then to work out the suggested bid. What I like to do, and this isn't a foolproof way, you know, there's a more advanced way to do it.
1:24 And typically, honestly, you would, you know, let Google set the bids, etc. But but if you wanted to set it manually, this is the way I, I tend to do it.
1:34 So first of all you get the $45 and multiply it by the leads. So that gives that gives you how much kind of revenue that keyword generated.
1:46 And then you divide the revenue by clicks, so give you revenue per click. And then and then you get the, you calculate the cost per clicks and divided by clicks minus one.
1:56 And you divide those two together. So what you get is essentially how much higher is the revenue per click than the cost per click.
2:05 And you could calculate that as a separate field as well. So if you wanted to add a calculated field in here, that was you know in this case $45 speeds divided by clicks.
2:24 Then that would give you the revenue per click and you could compare them. So. The the CPC here is 55 cents.
2:36 I'd say that's the same as the suggested bid. So if this is basically wanting to get it to. Break even with the CPC, that's what you do.
2:45 Cool. So in this case, you would, you know, drastically cut the bids of you know, this keyword, for example, the CPC is 70.
2:56 We're suggesting it should be 20. Whereas with others, you'd push them up. And you might want to add some sort of dampener on here.
3:04 So if this is this is like the raw amount you could also kind of cap it at like, say 20%.
3:13 Maybe you know, the way you would do that is just say max oh, sorry, min Okay, so. This there you go.
3:28 So that means that you would never increase the bid by more than 20%. In this case, it's just camping at 20%.
3:35 So that's one way you could do it, but that, that way you'd know. And you know, you're not kind of making too drastic over change.
3:41 Anyway this is you know, kind of a simple way of doing it. There's, you know, more calculated ways you could find the derivatives, whatever.
3:48 But this should be good enough for your purposes. Alright, hopefully that was useful.

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?

Calculate the bid adjustments (%) for each keyword. What does the bid adjustment and other metrics tell you about the worst performing keyword?

It’s your first job in marketing and the first task is optimizing the PPC campaigns. You’ve got the report, but how do you calculate what to pay per click for each keyword?

Vexpower Learning Certificate of Completion

Congratulations Dr Ravi Prasad 💪