Save Time & Money; Bid By Campaign Geos
Bidding by geography is one of the powerful tools at our fingertips, whether you are a local advertiser trying to find out which cities are best for you, or a national advertiser, trying to squeeze better performance out of each state. But there is only so much time in a day. On a search campaign alone, we can bid by keyword, geography, device, and time of day or day of week. It gets crazy sometimes keeping up with all of it.
So, we started writing a geography bidding script to help out. The account it’s running in is a local business, and we had to look the city up on a map just to find it. Before running it, we had some cities set at +10% because they were starting to perform very well, some we left at 0% to keep collating data, and one we had set at -10% since it was spending a lot with nothing to show for it. After running the script for almost 30 days, we’ve found that this tool removes a huge barrier, human bias. It has already pushed one city to +32%, and that city is now the biggest converter by far; and still a low CPA. The city that we had already bid down to -10%, but has already been pushed down to -25%; good thing too, it has still never converted! Now, the cost-per-lead on the account is down, and the conversions have risen at consistent pace.
There are some words of caution before applying this tool. For starters, if you apply it simply as-is, it will mess up your account. The numbers included are for one specific account. Before running this tool, use the Preview option. Read the numbers and the bid outcome. It you wouldn’t do it, the script shouldn’t do it, and the numbers need to change. There are extensive notes in the script itself as to how/where to do that. As a second note, we have not tested to see if this works on a Shopping Campaign. It isn’t designed to pull revenue either way, and it’s really only for lead gen accounts.
To get started, open the following link GeoEval Spreadsheet
On the Google Spreadsheet, go to File –> Make a Copy. Once you’ve accepted, you should have full control. You should see two Sheet Names at the bottom. The first is ‘GeoEval’. This is where you should be looking. The second is called ‘GeoCodes’. It is a specially formatted Sheet that contains every single AdWords geography code as of 5/29, including international. The GeoEval page uses this to show you which geography is listed and being changed. It will return an error on the sheet if you’re using a custom geography, but the code will still apply bidding changes.
In the script itself, you will need apply the URL of the Spreadsheet you created to where it says YOUR_SPREADSHEET_URL. You will need to choose one of your campaigns and apply the name in the two locations that it says YOUR_CAMPAIGN_NAME.
One last step you’ll need to do. Each geography in the targeted campaign will need to be set to a number, even if the number is “Increase by 0%”. The reason is because they are set to “–” by default, and so therefor aren’t a number.
Now preview and tweak the numbers until you are comfortable with the results. Set it to run every day, and watch the results closely.
Let us know if you have any questions or how the tool works for you!
// Script to Bid by Campaign Geography
// Kevin Adams
// RankHammer LLC
// 7/31/15
function main() {
var SPREADSHEET_URL = 'YOUR_SPREADSHEET_URL';
//Call the sheet
var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
//Make sure to name a tab "GeoEval"
var sheet = spreadsheet.getSheetByName('GeoEval');
//Clear the sheet and make the top row bold
sheet.clear();
var cell = sheet.getRange("A1:Z1");
cell.setFontWeight("bold");
//Declare column headers
var column1 = "Campaign"
var column2 = "Location ID"
var columnloc = "Location"
var column3 = "Clicks"
var column4 = "Avg CPC"
var column5 = "Cost"
var column6 = "Conversions"
var column7 = "Cost/Conv"
var column8 = "Geo Bid"
var column9 = "Bid Mod"
var column10 = "New Bid"
//Print the column headers
var columnRow = [column1,column2,columnloc,column3,column4,column5,column6,column7,column8,column9,column10];
sheet.appendRow(columnRow);
var report = AdWordsApp.report(
"SELECT CampaignName,AverageCpc,BidModifier,Id,Clicks,ConversionsManyPerClick,Cost,CostPerConversionManyPerClick" +
" FROM CAMPAIGN_LOCATION_TARGET_REPORT " +
" WHERE CampaignName = 'YOUR_CAMPAIGN_NAME' " +
"DURING LAST_30_DAYS");
var counter = 1;
var rows = report.rows();
while (rows.hasNext()) {
var row = rows.next();
var campaign = row["CampaignName"];
var geobid = row["BidModifier"];
var cpc = row["AverageCpc"];
var id = row["Id"];
var clicks = row["Clicks"];
var cost = row["Cost"];
var conversions = row["ConversionsManyPerClick"];
var costconv = row["CostPerConversionManyPerClick"];
var convrate = row["ConversionRateManyPerClick"];
counter = counter + 1;
var bidmod = 0;
var negstrong = -0.02;
var negweak = -0.01;
var bidnull = 0.00;
var posstrong = 0.02;
var posweak = 0.01;
var NewBid = 0;
// This begins our bid computation. If there are no conversions,
// we need to calculate on cost only
// Each threshold can and should be modified for your account.
// For example, where it say (cost > 120), change that for what
// think it needs to be
if (conversions < 1) {
// If zero conversions and our cost exceeds 120, bid down 2%
if (cost > 120) {
(bidmod = negstrong);
// If zero conversions and our cost exceeds 100, bid down 1%
} else if (cost > 100) {
(bidmod = negweak);
// If we've spent less than $15, increase bid 1%
// This serves to give the geo a chance, but slowly
} else if (cost < 15) {
(bidmod = posweak);
// If zero conversions and we don't meet the other criteria
// we tell the script to leave it alone.
} else {
(bidmod = bidnull);
}
// If 3 or more conversions, we need to treat the geography
// special. These are performers and not just a couple one-offs
// Larger accounts may need to push this much higher.
// Performers with less than $80 CPA, increase bid 2%
} else if (conversions > 2) {
if (costconv < 80) {
(bidmod = posstrong);
// Performers with less than $100 CPA, increase bid 1%
} else if (costconv < 100) {
(bidmod = posweak);
// Performers with greater than $130 CPA, decrease bid 1%
} else if (costconv > 130) {
(bidmod = negweak);
// Performers with greater than $160 CPA, decrease bid 2%
} else if (costconv > 160) {
(bidmod = negstrong);
// All other performers, we leave them alone
} else {
(bidmod = bidnull);
}
// Geos with 1-2 conversions, we treat normal
} else {
// Normal geos with CPA greater than $135, bid down 2%
if (costconv > 135) {
(bidmod = negstrong);
// Normal geos with CPA greater than $110, bid down 1%
} else if (costconv > 110) {
(bidmod = negweak);
// Normal geos with CPA less than $90, bid up 1%
} else if (costconv < 90) {
(bidmod = posweak);
// Normal geos with CPA greater than $70, bid up 2%
} else if (costconv < 70) {
(bidmod = posstrong);
// Leave all other normal geos alone
} else {
(bidmod = bidnull);
}
};
// Here, we double check to make sure the geo bid
// is not outside the acceptable ranges
// This spot should not be changed
if ((bidmod + geobid) < -0.90) {
(bidmod = 0)
};
if ((bidmod + geobid) > 3.00) {
(bidmod = 0)
};
var tableRows = [campaign,id,"=vlookup(b"+counter+", GeoCodes!A:B, 2, false)",clicks,"$"+cpc,cost,conversions,"$"+costconv,geobid,bidmod,"=(i"+counter+" + j"+counter+")"];
sheet.appendRow(tableRows);
var range = sheet.getRange("A:K");
// The row and column here are relative to the range
// getCell(1,1) returns the row that 'counter' has
// added to, and column 11.
var cell = range.getCell(counter, 11);
// Do a final setting of the bid
NewBid = 1 + (cell.getValue());
// Use the campaign name you want to apply the change to
var campaignIterator = AdWordsApp.campaigns()
.withCondition('Name = "YOUR_CAMPAIGN_NAME"')
.get();
while (campaignIterator.hasNext()) {
var campaign = campaignIterator.next();
var targetedLocationIterator = AdWordsApp.targeting()
.targetedLocations()
.withIds([[campaign.getId(), id]]).get();
if (targetedLocationIterator.hasNext) {
targetedLocationIterator.next().setBidModifier(NewBid);
}
}
}
};