Chicago runs a website that collects and aggregates the statistics of the city: City of Chicago Data Portal. Here you can find all sorts of data sets, but the one I was interested in was the Crime Stats.
After a little exploration, I found that the data is available in JSON (an easy to consume format for software). So I put together a script to read that data and then automatically update the keyword AdParams with the total count of the different types of crime.
This might come in handy for any companies that sell insurance or security systems, but I'm sure there's other companies that might be able to use this info.
Other major cities in the US keep similar sets of data as well:
What other public records data could your business use to target your customers? Let me know in the comments and I'll take a look.
Thanks,
Russ
//-----------------------------------
// Put Chicago Crime Stats in Your Creatives
// Created By: Russ Savage
// FreeAdWordsScripts.com
//-----------------------------------
function main() {
// You can get this link by going here: http://goo.gl/tfNgM
// Apply some filters and then click export > api
// This end point is good for all of 2013
var DATA_ENDPOINT = "http://data.cityofchicago.org/resource/pga9-zdiw.json";
var CAMPAIGN_PREFIX = 'Crime_Data_Chicago_'; //All your campaigns start with this
var AD_PARAM = 1; // 1 or 2
try {
var json = Utilities.jsonParse(UrlFetchApp.fetch(DATA_ENDPOINT).getContentText());
var summary = summarizeCrimeStats(json);
//logCrimeSummary(summary);
for(var i in summary) {
var total = totalPrimaryDescription(i,summary);
var kw_iter = AdWordsApp.keywords()
.withCondition("CampaignName CONTAINS_IGNORE_CASE '"+CAMPAIGN_PREFIX+i+"'")
.get();
while(kw_iter.hasNext()) {
var kw = kw_iter.next();
kw.setAdParam(AD_PARAM, total);
}
}
}catch(e) {
}
}
// A helper function to aggregate the data by primary description
function totalPrimaryDescription(key,summary) {
var tot = 0;
for(var i in summary[key]) {
tot += summary[key][i];
}
return tot;
}
//This function takes in a json formatted object and stores the count of instances
//in a 2 dimentional hash of [Primary Description][Secondary Description]
function summarizeCrimeStats(json) {
var crime_summary = {};
for(var i in json) {
var crime = json[i];
if(crime_summary[crime._primary_decsription]) {
if(crime_summary[crime._primary_decsription][crime._secondary_description]) {
crime_summary[crime._primary_decsription][crime._secondary_description]++;
}else{
crime_summary[crime._primary_decsription][crime._secondary_description] = 1;
}
}else{
crime_summary[crime._primary_decsription] = {};
crime_summary[crime._primary_decsription][crime._secondary_description] = 1;
}
}
return crime_summary;
}
//Just a helper function to print out the summary info so that
//I can find the data I'm interested in.
function logCrimeSummary(crime_summary) {
for(var i in crime_summary) {
for(var x in crime_summary[i]) {
Logger.log([i,x,crime_summary[i][x]].join(', '));
}
}
}
