Adding Geo-Intent modified keywords such as “Restaurants Near Road A” or “Cafe by Building X” to your search campaigns allows your SEM Ads to be served against highly relevant searches. This also increases your chances on Google Maps (desktop and ads) once you enable local search ads.

We could generate hundreds of quality keywords with a bit of Python and tap on OpenStreetMap‘s data.

OSM is a free editable community maintained map of the world that enriched with tags of what can be found at a location. OSM is a gold mine of information on any location: below is a sample of the most popular classes of tags. For example in the “tourism class” we could find locations tagged with useful tourist information identifying attractions, hotels, zoos…etc. And another “Amenity class” lists all sort of useful amenity tags such as locating restaurants, car parks, benches & even toilets.

 

With such a rich dataset we could potentially extract interesting things useful for a large variety of campaigns: Attractions for tour agencies, Competitor retail shops within a certain radius, Competitor restaurants.

The limit is your imagination with OSM data.

Here we would focus on generating keyword pairs such as “Restaurants Near Road A/Buidling name” for within a certain radius in 2 steps –

  1. Data Extraction: Using Python with OSM’s API We extract all relevant road name and building in a bounding box
  2. Keyword Generation: Using GSheet formulas to create all possible permutations

Data Extraction

To extract data from OSM you would need to use Overpass-QL. OOQL elements consist of Node, Ways and Relations. More on OSM data model hereFor our purpose we would extracting “Ways” (line feature connecting two or more Nodes, which are point features) within a bounding box of longitude and latitude.

Overpass Turbo is an excellent way to experiment with your Query. For example, the code snippet below would pick out all the building within the viewport:

[out:json];
// gather results
(
 way["building"]({{bbox}});
);
// print results
out body;
>;
out skel qt;

and the circle are all the buildings detected:

We need to replace {{bbox}} with coordinates that are within a box of our initial latitude and longitude. Where it would be {{south-most latitude, east-most longitude, north-most latitude, west-most longitude}}

To do this, we use the following function:

def gen_bound_box(lat,long,radius):
  start = geopy.Point(lat, long)
  d = geopy.distance.VincentyDistance(radius/1000)
  s=d.destination(point=start, bearing=180)[0]
  e=d.destination(point=start, bearing=90)[1]
  n=d.destination(point=start, bearing=0)[0]
  w=d.destination(point=start, bearing=270)[1]
  return [s,w,n,e]

Passing in the above with coord_list along with feature with a value of  “way[building]”  we could start generating the correct overpass_query. This gets pushed to the endpoint which would respond us with a JSON file

def osm_get_roads(coord_list,feature):
    s, w, n, e = coord_list
    overpass_url = "http://overpass-api.de/api/interpreter"
    overpass_query = F"""
    [out:json];
    {feature}
      ({s},{w},{n},{e}); 
    out;
    """
    response = requests.get(overpass_url,params={'data': overpass_query})
    road_data = response.json()
    return road_data

This is a truncated JSON response from running the above:

PRETTY NEAT – there is a large list of interesting details that could be either used as keywords (name) or help us decide if this is a point we might want (postcode, wikidata).

To extract only the fields we need we pass along a list [‘name’,’building,’postcode’,’wikidata’] to the following function to get this into a pandas dataframe:

def extract_dataframe(json_data,fields_to_extract):
    df = pd.DataFrame()
    for field in fields_to_extract:
        df[field] = 
[field] if field in row['tags'].keys() else None for row in json_data['elements']] return df

 

This could easily be turned into a CSV by the df.to_csv(“mycsv”) function, or pushed to a Google Sheet with gspread.

With this list of building names, we could now pair with other keywords.

Keyword Generation

Ideally, we would want to pair the above with a based keyword and modifier to arrive at the following output:

To do this:

We observe that the number of elements in col F is actually – number A* number B* number C and each column of values is repeated N times ,  ( number A* number B* number C ) / (number A) times. This give us:

=COUNTA(A2:A)*COUNTA(C2:C)*COUNTA(B2:B)

For Column A


To Repeat each list N times we first return all of the elements in the columns by:

=filter(A2:A,LEN(A2:A))


We transpose this into:

=TRANSPOSE(filter(A2:A,LEN(A2:A)))


Then  we join this back into a single cell

=JOIN(",",TRANSPOSE(filter(A2:A,LEN(A2:A))))


The above is now ready to be repeated N times: (number of elem A* number of elem B* number of elem C ) / ( of elem number A) times with an additonal comma added in.

 

=REPT(
    JOIN(",", TRANSPOSE(filter(A2:A,LEN(A2:A)))) & "," ,
COUNTA(A2:A)*COUNTA(C2:C)*COUNTA(B2:B)/COUNTA(A2:A)
)


Now we massage the data back into the form we need – each value placed into a single cell in a column. First we split the above into multiple columns:

=split(REPT(JOIN(",",TRANSPOSE(filter(A2:A,LEN(A2:A))))&",",$D$1/COUNTA(A2:A)),",")


Then we transpose it back into a multiple row format:

=TRANSPOSE( split(REPT(JOIN(",",TRANSPOSE(filter(A2:A,LEN(A2:A))))&",",$D$1/COUNTA(A2:A)),","))


We repeat the process for each columns generating the following:

 

Then finally we put them all together with an array formula:

= ArrayFormula(J13:J40&K13:K40&L13:L40)

Conclusion

And there you have it – a list of powerful geo-intent keywords to use. As OSM has a very rich dataset, there is huge potential to do this for different types of campaigns. Be it tour agencies targeting searches for attractions on a country scale, or a retailer looking to conquest competitor retails shops within a set amount of kilometers.

To make this process more smooth, I’ve hosted the python bit in Cloud functions and allowed Gsheets to ping it via pubsub and have the function update the sheet – turns the above into a one-button click process!