Central Perk: Mobile Bid Adjustments Part 3 – Location, Location, Location

vineri, 12 iulie 2013

Mobile Bid Adjustments Part 3 – Location, Location, Location

Posted: 12 Jul 2013 01:19 AM PDT

Previously I've discussed ways to calculate your mobile bid adjustment, and how to use weighted averages to change keyword level bids to ad group level adjustments. Today it's time to check how adding location bid adjustments will change things.

Firstly, a disclaimer: a 10% change to a bid is not the same as a 10% change to a CPC. CPC depends on competitors’ bids rather than your own; changing the bid changes what auctions you enter. If you're restricted by budget, then the proportion of spend on each device might change. But this is a rough check, to make sure the layers of adjustments aren't creating unexpected exponentials.

The first step, as ever, is to download your data. Go to the Dimensions tab, then to the User Locations report. Make sure you have the right level of detail: if you're only going to add adjustments for countries then you can have just the Country/Territory; if you're using UK countries or US states then have the Region column.

Download the User Location report with the Device.

Add a second worksheet, rename it ‘Location’, and list your location bid adjustments. These are campaign level only.

Then add a third worksheet, rename it ‘Mobile’, and list your mobile bid adjustments. These could be campaign or ad group level: here I’ve got mine at ad group level.

We want these adjustments in the first worksheet, so go back to that one and add ‘Location Adjustment’ and ‘Mobile Adjustment’ columns. ‘Location Adjustment’ is:
=SUMIFS(Location!C:C,Location!A:A,D3,Location!B:B,C3)

‘Mobile Adjustment’ is:
=SUMIFS(Mobile!C:C,Mobile!A:A,D3,Mobile!B:B,E3)

If you had campaigns targeting multiple devices then the mobile bid used would not be the same as the bid you actually entered, as Google automatically adjusts it by an unknown amount. That means you can’t work with your actual mobile CPCs, because they don’t come from the right bid. So instead use the Desktop CPC, which will be multiplied by the mobile bid adjustment. Add a ‘Desktop CPC’ column.
=SUMIFS(I:I,A:A,"Computers",B:B,B3,C:C,C3,D:D,D3,E:E,E3)

Then we get the adjusted CPC by multiplying it by our modifiers.
=IF(A3="Mobile devices with full browsers",O3+1,1)*(N3+1)*P3

This uses the mobile adjustment if the row is for mobiles, and always uses the location adjustment.

Multiply the Adjusted CPC by the number of clicks to get the Adjusted Cost:
=Q3*F3
This is assuming that the bid changes won’t affect your number of clicks – which is wrong, as changing bids leads to changing position which leads to changing CTR. Again, this is just a sanity check that your bids are in the right area: we can’t calculate the precise difference they will make, as you don’t know the position or CTR you’ll end up with.

Now we’ve assembled everything, it’s time to put it into a Pivot Table. Select the data, go to Insert on the menu ribbon and click Pivot Table:

A dialogue box will appear – click ‘Okay’.

You’ll get a pivot table in a new worksheet. Select fields to appear in it – I suggest Campaign, Ad Group, Cost and Adjusted Cost to start with.

Then add calculated fields: click ‘Option’ under PivotTable Tools in the ribbon, then ‘Fields, Items, & Sets’, then ‘Calculated Field…’:

Create one called Adj CPC, for the adjusted CPC:

Also add ‘CPC’, which is:
=Cost/Clicks

CPA:
=Cost /'Conv. (1-per-click)'

and Adj CPA:
='Adjusted Cost' /'Conv. (1-per-click)'

You can then see how your bid adjustments should roughly affect the Cost, CPC and CPA.

You can change the Row Labels to see how bids are affected when viewed by Region or Device. You can also add an ‘Effective Adjustment’ calculated field (=CPC /'Adj CPC' -1) to see what percentage the difference is between current performance and expected performance. You can also change your adjustments in the Location and Mobile tabs, then hit ‘Refresh’ in the PivotTable Tools Options to see the effects.

You might want to have ad scheduling as well. Unfortunately you can't segment the User Location report by time of day, but you can segment by Device and Day of the Week. If you want ad scheduling but no location adjustments, you could download the Hour of Day report segmented by Time of Day and Device. You'll then need an extra worksheet with your time-based adjustments, and an extra column to add them to the main data, but you can calculate the 'Adjusted' columns and pivot in the same way.

If you’d like to see the example spreadsheet then download it here.

That’s it! Our three-part discussion of bid adjustments is over (for now). Do you have any thoughts on how to calculate them, or predict their effects when there are so many unknowns in play? Let us know in the comments.