Central Perk: Mobile Bid Adjustments Part 2 – Going from Keyword Bids to Ad Group Adjustments

joi, 11 iulie 2013

Mobile Bid Adjustments Part 2 – Going from Keyword Bids to Ad Group Adjustments

Posted: 11 Jul 2013 01:15 AM PDT

In yesterday’s post I talked about the different ways to find your mobile bid adjustments, and said that if you have keyword level adjustments you’ll need to change them into ad group level adjustments. Today I’ll show how to do this with weighted averages.

Calculating Weighted Averages

Firstly you’ll need to get your keywords into Excel, so go to AdWords and to the Keywords tab.

Be careful to set the date range: you don’t want to have a range that will include anything that’s out of date, but you need enough data to make decisions.

The next steps depend on whether you have separate mobile campaigns or not.

If do you have separate mobile campaigns, you just need to download a Keyword report. Then replace 'Campaign' with 'Campaign Root' and add a 'Device' column. The Campaign Root should be the same for all campaigns that are the same except for device. Device should be the device the campaign is targeted at: 'mobile' or 'computers'. If you've named your campaigns sensibly then you should be able to fill these by copying the Campaign column, then using find and replace.

Then add a filter so you can just look at the mobile campaigns' keywords.

Add columns "Desktop bid" and "Mobile bid". To find the Desktop Bid for the keyword, use:

=SUMIFS(G:G,B:B,B3,C:C,C3,E:E,E3,D:D,"Computers")

This will find the Max CPC of the row with the same keyword, campaign root and ad group, where the device is ‘computers’.

To find the Mobile Bid, just copy column G, as you’re already only looking at mobile keywords.

If you don’t have campaigns split by device, but you have worked out what your mobile bids should be at keyword level, you’ll want to download the Keyword report segmented by Device. This means you already have a Device column, and can use the Campaign column without needing to add a Campaign Root column. Filter by Device so that you’re only looking at Mobile traffic. Then instead of messing with sumifs you can just copy the Max. CPC column to make the “Desktop Bid” column, and copy your mobile bids into the “Mobile Bid” column.

Whichever method you're using, your spreadsheet should end up looking something like this:

Add a column for ‘Mobile/Desktop’, for the keyword level percentage. Fill it with:

=IFERROR(O3/N3,0)

(The IFERROR is in case there is no desktop bid, which would otherwise lead to dividing by zero. If you need to find keywords that are in your mobile campaigns but not your desktop ones, you can filter for where Desktop Bid is 0.)

Then add a column for ‘Weight’. This is the keyword level percentage multiplied by the cost of the keyword:

=P3*L3

Then comes the bit you’re actually after. Put in a column for ‘Bid Adjustment’ and fill it with:

=SUMIFS(Q:Q,C:C,C3,D:D,D3,E:E,E3)/SUMIFS(L:L,C:C,C3,D:D,D3,E:E,E3,Q:Q,">0")-1

This sums up the weights (where the campaign, ad group and device are the same) and divides by the cost of mobile traffic to the ad group (ignoring where the weight is 0, as that would mean the keyword isn’t in the desktop campaign). The -1 is because mobile bid adjustments are the difference between the percentage multiplier and 100%.

If you’d like to see this in Excel, download the example spreadsheet here.

Review

You should then review your adjustments, to make sure they look sensible.

You may have the problem that a mobile ad group hasn’t had any clicks: its cost is 0, all its weights are 0 and the suggested bid adjustment is #DIV/0!. As you haven’t had any costs to show how you should weight the average, you could use an unweighted average:

=AVERAGEIFS(P:P,C:C,C3,D:D,D9,E:E,E3,P:P,">0")-1

Also, your final adjustments need to be between -90% and +300%. Rather than just use Excel to force those adjustments, I’d review the ad groups that are outside the limits. If the best modifier is over 300%, is the traffic valuable enough that it’s worth increasing desktop bids? If the bid modifier is under 90%, is it worth having mobile traffic at all?

Another thing you can do is add a column with the difference between the bid adjustment and the keyword’s preferred adjustment:

=IF(P3>0,P3-1-R3,"-")

Then filter to see the biggest differences, or filter by this and by cost to see where there would be the biggest difference in spend. Judge whether there are any keywords that would be better off in a separate ad group with its own adjustment.