Total Weather Bidding Made Easy

A story of how we made it possible to bid based on weather down to city-based targeting with little effort using Python, openweatherapi.com and Google Sheets.

Full source code can be found at: https://github.com/Risgn94/Weather_Mapping

A little background

Ryans-raincoats.com is selling raincoats to the people of rainy Denmark — Needless to say; Business is good.

Ryan is mainly using Google AdWords for accumulating customers online, and all of a sudden he gets the idea: “Maybe I should start bidding depending on the weather?”. After some googling, Ryan finds the following script developed by Google. At first, this seems to meet the needs of Ryan, and he implements it at his account.

After some time, Ryan gets annoyed that every-time he wants to add a new location bidding or campaign, he has to add another row in the Google Sheet used for managing the script, and he just imagines what a process if he had had a larger account. For instance, if you would like to weather-bid 5 different places for 10 campaigns you would have 5*10 ~ 50 rows in the Google Sheet. Depending on your business, this could result in an enormous amount of rows.

This not-so-fictional problem we experienced at AdNudging. How we solved it? Created a better and easier manageable script — Simple.

How we tackled the problem

As the basic idea was to be able to target a lot of cities for a lot of campaigns, at first we ran into one problem: Referring Google AdWords locations to the Openweatherapi.com’s location ID’s. This meant that we needed some sort of database, but since using a SQL database might seem a bit excessive, we landed on using Google Sheets for this. This meant, that we needed to create a long list with the cities we would like to bid on if it rained combined with Google AdWords locations. The final solution looked like this:

Name is for Openweatherapi, ID for Google AdWords and weather used for whether or not to bidadjust

Now we have a look-up table able to tell us what the Google ID is for a given Openweatherapi city and vice-versa. We used the public list with all Google AdWords locations and the same for Openweatherapi to create this list of all cities available in Denmark.

To attach the current weather, we needed a script to read the city names and request the current weather from openweatherapi.com. To do this, we created a simple Python script. This consisted of two files. Our main.py file:

#!/usr/bin/env python3
import spreadsheet
import os
import sys
from lazyme.string import color_print //This can be excluded and editing "color_print" to regular "print"
import datetime

def main():
try:
if(sys.argv[1] == "default"):
print("Default working directory has been set")
else:
os.chdir(sys.argv[1])
color_print("Working directory set to: %s" %sys.argv[1], color='green')
except (IndexError, FileNotFoundError) as err:
print(err)
color_print("Working directory has not been set.", color='red')
print('Starting updating weahter conditions...')
spreadsheet.getWeatherLoc()
dt = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
print("Ran script at: "+str(dt))
print('finished updating weather condiditons at')

if(__name__ == "__main__"):
main()

and our “read from spreadsheet”-file (spreadsheet.py):

import gspread
import requests
import json
import time
from oauth2client.service_account import ServiceAccountCredentials

def getWeatherLoc():
_scope = ['https://spreadsheets.google.com/feeds']
_creds = ServiceAccountCredentials.from_json_keyfile_name('YOUR_CLIENT_SECRET', _scope)
_client = gspread.authorize(_creds)
_name = 'SPREADSHEET_NAME'
_sheet = _client.open(_name).worksheet('SHEET_NAME')

max_Rows = _sheet.row_count
range_String = "A2:C" + str(max_Rows)
range = _sheet.range(range_String)
for idx, cell in enumerate(range):
if (cell.col == 1):
if((idx/3)%50 == 0 and idx != 0):
print("Sleeping")
time.sleep(61)
print("Sleep over")
city_Name = cell.value
url = "http://api.openweathermap.org/data/2.5/weather?q="+city_Name+"&appid=APIKEY"
r = json.loads(requests.get(url).text)
try:
range[idx + 2].value = r['weather'][0]['main']
except KeyError:
print(r)
_sheet.update_cells(range)

You need to acquire a service_account client_secret from the Google developer console, create a Google Sheet, and openweatherapi apikey to successfully running it.

Now, you should be able to run the script and see the list of cities being updated with the current weather. If you do not need an AdWords integration, this is it, but if you want to do the final step, keep on reading.

AdWords implementation

The last thing you need to do, is implement the AdWords script below:

var RAIN_BID = 1;//The amount you multiply bid with
var NO_RAIN_BID = 1; //Default bidadjustment
var SPREADSHEET_URL = "SPREADSHEET_URL"
var SHEET_NAME = "SHEET_NAME"
var BID_ADJ = 1;//The amount you multiply bid with
var loc_Array = [];
var camp_Ids = [];
var outer_Array = [];

function main() {
var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var sheet = spreadsheet.getSheetByName(SHEET_NAME)
var max_Rows = sheet.getLastRow();
var max_Cols = sheet.getLastColumn();
var range = sheet.getRange(1, 1, max_Rows, 3)

for(i = 1 ; i < max_Rows ; i++){ //max_Rows+1
cell_Name = range.getCell(i+1, 1).getValue();
cell_Id = range.getCell(i+1, 2).getValue();
cell_Weather = range.getCell(i+1, 3).getValue();
loc_Array.push({name: cell_Name, id: cell_Id, weather: cell_Weather});
}

var campaignSelector = AdWordsApp
.campaigns()
.withCondition("Status = ENABLED");
var campaignIterator = campaignSelector.get();
while (campaignIterator.hasNext()) {
var campaign = campaignIterator.next();
camp_Id = campaign.getId();
camp_Ids.push(camp_Id);
}

for(campaigns in camp_Ids){
inner_Array = [];
for(locs in loc_Array){
inner_Array.push([camp_Ids[campaigns], loc_Array[locs].id])
}
outer_Array.push(inner_Array);
}

for(locations in outer_Array){
campaign_Counter = 0;
var targetedLocationSelector = AdWordsApp.targeting()
.targetedLocations()
.withIds(outer_Array[locations]);
var targetedLocationIterator = targetedLocationSelector.get();
while (targetedLocationIterator.hasNext()) {
var targetedLocation = targetedLocationIterator.next();
var targetId = targetedLocation.getId()
if(checkRain(targetId)){
targetedLocation.setBidModifier(BID_ADJ)
} else {
targetedLocation.setBidModifier(1)
}
}
campaign_Counter++;
}
}

function checkRain(id){
for(locations in loc_Array){
if(loc_Array[locations].id == id){
if(loc_Array[locations].weather == "Rain"){
return true;
} else {
return false;
}
}
}
}

This script will read from the spreadsheet with weather data, check whether it is raining or not, and then adjust the bid. For this to work, you need to implement the geolocations in your account beforehand.

And that’s it! You can now create a cronjob for the python code and run the AdWords script hourly. Enjoy!

To evaluate whether your bidding works or not, I can recommend this post for integrating weather data into Google Analytics: https://www.simoahava.com/analytics/send-weather-data-to-google-analytics-in-gtm-v2/

Feel free to contact me, if any help is needed.

If you like this post, please show your support, and let me know in the comments if something is missing.

Best Regards

Asger Thyregod