Visualizing Impressions/Population Ratio By Location with GeoPandas

Google ads provide an essential metric called Search Impression Share. It gives vital insights regarding scaling opportunities and how competitive your bids are.

Unfortunately, this metric isn’t available at the Geo level. In other words, if you target several locations in the same campaign, you won’t know what the impressions share for each location.

One alternative approach is finding the relative impressions observed in a specific location in relation to the population rate compared to the other locations is:

If this number is smaller than 1, you might want to increase bids. If it is greater than 1, you might want to decrease bids.

Step 1: Export Location data to Google sheet:

Go to Google Ads Scripts and implement the following code. It will export the necessary data to a pre-existed google sheet (in the case: https://docs.google.com/spreadsheets/d/13_fdj-11O_c7YKY5EqcQc06xbs/edit#gid=0)

ss_url = “https://docs.google.com/spreadsheets/d/13_fdj-11O_c7YKY5EqcQc";
function main() {
 
 var statement = “SELECT CountryCriteriaId, CampaignName, RegionCriteriaId, Impressions FROM GEO_PERFORMANCE_REPORT WHERE CampaignStatus = ‘ENABLED’ DURING LAST_30_DAYS “;
var report = AdsApp.report(statement);
 
 var ss = SpreadsheetApp.openByUrl(ss_url);
 report.exportToSheet(ss.getActiveSheet());
}

In this tutorial, we’ll focus on campaigns targeting US states.

The exported data in the Google sheet looks something like this:

Step 2: Importing data to Python

First, let’s import the necessary libraries:

from oauth2client.service_account import ServiceAccountCredentials
import gspread
from df2gspread import df2gspread as d2g
import numpy as np
import pandas as pd

Let’s import the data from the google sheet to a Pandas Dataframe. If you’re not sure how to do it, check out this tutorial.

# config
googlesheet_domain = [‘https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name(‘python-googlesheets-api.json’,googlesheet_domain)
client = gspread.authorize(credentials)
googlesheet_key = ‘13_fdj-11O_c7YKY5EqcQc06xbs’
active_sheet = "Sheet1"
gs_data = pd.DataFrame(client.open_by_key(googlesheet_key).worksheet(active_sheet).get_all_records())

Now that we have the impressions by location and campaign data let’s get population data from US Census API.

You’ll need to get an API key here: https://www.census.gov/developers/

The data comes in JSON format, so we’ll need to import additional libraries:

import requests
import json
apiKey = “YOUR API KEY”
baseAPI = “https://api.census.gov/data/2018/acs/acs5?get=B00001_001E&for=state:*&key=%s"
calledAPI = baseAPI % (apiKey)
response = requests.get(calledAPI)
formattedResponse = json.loads(response.text)[1:]
formattedResponse = [item[::-1] for item in formattedResponse]
StatesPopulations = pd.DataFrame(columns=[‘states’, ‘population’], data=formattedResponse)
StatesPopulations[“states”] = StatesPopulations[“states”].astype(“int”)

The states are coded with numerical codes. We’ll need to merge it with a dictionary.

I found the data here and saved it as ansi_states_code.csv

states = pd.read_csv(“ansi_states_code.csv”, names=[‘State’,’Code’,’Initials’], skiprows=1)
states[“Code”] = states[“Code”].astype(“int”)

Now let’s merge the data with the dictionary and create a ratio column:

population_data = pd.merge(StatesPopulations, states, left_on="states", right_on="Code")
population_data["ratio"] = population_data["population"].astype("int")/population_data["population"].astype("int").sum()
population_data.sort_values(by="ratio", ascending=False)

And merge the population data with the impressions data:

data = pd.merge(population_data, gs_data, left_on=”State”, right_on=”RegionCriteriaId”)

Last but not least, let’s get our Impressions/Population ratio:

imp_pop = (data.groupby(“State”)[‘Impressions’].sum()/data[‘Impressions’].sum())/(data.groupby(“State”)[‘ratio’].mean())

Step 3:

Now we are ready to visualize the data

We’ll start by downloading the map from Arcgis here: https://www.arcgis.com/home/item.html?id=b07a9393ecbd430795a6f6218443dccc

Then import the required libraries and the map.

import geopandas as gpd
statesfile = ‘states_21basic/states.shp’
statesmap = gpd.read_file(statesfile)[[‘STATE_NAME’, ‘STATE_ABBR’, ‘geometry’]]
statesmap_merged = statesmap.merge(imp_pop.to_frame(), left_on = ‘STATE_NAME’, right_on =’State’)
statesmap_merged = statesmap_merged.rename(columns={0:”Imp/Pop Ratio”})

We’ll use the Bokeh library to visualize the choropleth map. It works with JSON, so we’ll need to convert the data to JSON.

import json
statesmap_merged_json = json.loads(statesmap_merged.to_json())
json_data = json.dumps(statesmap_merged_json)

And now for setting up the visualization:

from bokeh.io import output_notebook, show, output_file
from bokeh.plotting import figure
from bokeh.models import GeoJSONDataSource, LinearColorMapper, ColorBar
from bokeh.palettes import brewer
geosource = GeoJSONDataSource(geojson = json_data)
palette = brewer[‘YlGnBu’][8]
palette = palette[::-1]
color_mapper = LinearColorMapper(palette = palette, low = 0, high = 4)
color_bar = ColorBar(color_mapper=color_mapper,label_standoff=8,width = 500, height = 20,border_line_color=None,location = (0,0), orientation = ‘horizontal’)
p = figure(title = ‘Impressions/Population Ratio’, plot_height = 600 , plot_width = 950, toolbar_location = None)
p.xgrid.grid_line_color = None
p.ygrid.grid_line_color = None
p.patches(‘xs’,’ys’, source = geosource,fill_color = {‘field’:’Imp/Pop Ratio’, ‘transform’ : color_mapper},
line_color = ‘black’, line_width = 0.25, fill_alpha = 1)
p.add_layout(color_bar, ‘below’)
output_notebook()
show(p)

Now it’s clearer which states are getting more impressions relative to the population size (like Alaska) and vice versa (like California).