Central Perk

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.

"Deadpool PC Gameplay Walkthrough Playthrough Part 7 HD - Deadpool's..." from gamer4ever and more

Check out the latest videos from your channel subscriptions for Jul 11, 2013. Play all »

Deadpool PC Gameplay Walkthrough Playthrough Part 7 HD - Deadpool's...

by gamer4ever

Just For Laughs Gags - Season 1 Episode 6

+ 3 videos by Just For Laughs Gags

Upload Schedule, Updates, And Help Wanted (BF3 Gameplay)

+ 1 video by eProfility

videos

Commentaries

PLAYLIST by eProfility

3.9 Patch Preview

by League of Legends

Rieju RR Stunts And Wheelies - PureGoProAction

+ 1 video by PureGoProAction

Пожарные плывут к месту пожара

+ 4 videos by nakedassTv

Hakken @ Party

by byRobie

videos

Magical Tales Sing Along Songs

PLAYLIST by AmbienceEnt

Seth's Blog : But it only works sometimes

But it only works sometimes

A glimpse is often more compelling than a certainty. For a minute or two, the drum solo on Monk's Dream is totally and completely alive. It even makes the neighbor's dog turn his head and stare at the speakers.

If all recorded music sounded this good all the time, it would lose its magic for me. I certainly wouldn't spend hours trying to get my stereo just right (one more time).

Word of mouth comes from intermittent delight. Things that work all the time are harder to talk about.

Random reinforcement drives people to focus their attention and effort, because it's worth sifting through many to find the one that's worth it.

Sure, there are places where six sigma reliability is essential (like pacemakers). But in most markets, your audience is likely to talk about your flashes of brilliance.

Sometimes, we're so focused on being consistent that we also lower the bar on amazing. After all, the thinking goes, if we can't be amazing all the time, better to reset the expectation to merely good. Which robs us of the ability to (sometimes) be amazing.

But amazing is what spreads.

In markets where some people expend unreasonable energy, we get uneven results, and those results are things we seek out, again and again.