Matching a store list to location identifiers
This example shows how to match a list of store addresses to our location identifiers, so the list can be joined to any of our US Places metrics. We use four San Antonio addresses: three H-E-B stores and one control address that is not in the directory.
For this we use our US Places metrics, queried from Python. The helper below is all the setup the example needs:
import os
import requests
API = "https://api.pine59.com/v3/datasets"
HEADERS = {"Authorization": f"Bearer {os.environ['PINE59_API_KEY']}"}
def run_query(dataset, body):
r = requests.post(f"{API}/{dataset}:runQuery", json=body, headers=HEADERS)
r.raise_for_status()
return [rec["fields"] for rec in r.json().get("records", [])]
def search_values(dataset, field, term):
r = requests.post(f"{API}/{dataset}:searchFieldValues",
json={"field": {"name": field}, "term": term}, headers=HEADERS)
r.raise_for_status()
return r.json().get("values", [])
For US places,
location_idis the location's Placekey. A list that already carries Placekeys needs no matching: filter onlocation_iddirectly.
1. The list
A store list as it usually arrives: an address, a postal code, and your own store number.
STORES = [
{"store": "SA-01", "address": "12125 Alamo Ranch Pkwy", "zip": "78253"},
{"store": "SA-02", "address": "9238 N Loop 1604 W", "zip": "78249"},
{"store": "SA-03", "address": "8300 Floyd Curl Dr", "zip": "78229"},
{"store": "SA-99", "address": "1234 Nowhere St", "zip": "78201"}, # control: not a real place
]
2. Exact address match
One query for the whole list: street_address with the in operator, the city as a second filter, and the fields that tell records apart:
hits = run_query("visitation_usa_places.places", {
"fields": [
{"name": "location_id"},
{"name": "street_address"},
{"name": "postal_code"},
{"name": "location_name"},
{"name": "brands"},
],
"filters": [
{"fieldName": "street_address", "operator": "in",
"values": [s["address"] for s in STORES]},
{"fieldName": "city", "operator": "==", "value": "San Antonio"},
],
"pageSize": 100,
})
for h in sorted(hits, key=lambda h: h["street_address"]):
print(h["street_address"], "|", h["location_name"], "|", h["brands"], "|", h["location_id"])
Result:
| Address | Records at the address |
|---|---|
| 12125 Alamo Ranch Pkwy | H-E-B, H-E-B Pharmacy, H-E-B Fuel |
| 9238 N Loop 1604 W | H-E-B, H-E-B Pharmacy, Auntie Anne's, Davi Nails, Mas Caf (unbranded) |
| 8300 Floyd Curl Dr | none |
| 1234 Nowhere St | none |
How to read it: an address is not a place. The first two addresses return the store and everything that shares its address: the pharmacy and fuel station that are separate places in the directory, and at the second address the tenants of the same building. The third address is a real store that did not match, and the fourth is the control. Two of four are resolved to the address, none yet to a single record.
3. Disambiguate by brand
A store list means the store, so keep the record whose brands is the chain itself, not its pharmacy or fuel banner:
matches = {}
for s in STORES:
at_address = [h for h in hits if h["street_address"] == s["address"]]
store_rec = [h for h in at_address if h["brands"] == "H-E-B"]
if len(store_rec) == 1:
matches[s["store"]] = (store_rec[0]["location_id"], "exact")
print(matches)
Two matches, one record each: SA-01 to zzw-222@8sz-ty2-kj9 and SA-02 to 227-226@8sz-tzb-zvf. When a list is not one brand, match on location_name instead, or keep every record at the address and let the reader of the mapping decide.
4. Search for the rest
The unmatched addresses go through a value search on the address field, which finds the directory's spelling of an address that exists:
for s in STORES:
if s["store"] in matches:
continue
candidates = search_values("visitation_usa_places.places", "street_address", s["address"])
print(s["store"], s["address"], "->", candidates[:3])
Result: SA-03 8300 Floyd Curl Dr -> ['8300 Floyd Curl Dr Ste 105'] and SA-99 1234 Nowhere St -> [].
How to read it: the third store exists; the list dropped its suite number. The search returns the directory's spelling, so the exact match from section 2 runs again with 8300 Floyd Curl Dr Ste 105 and resolves it. The control returns nothing at every pass, and stays unmatched: a location that is not in the directory has no data to join to, and forcing it to the nearest record would put another place's visitors on your store number.
resolved = run_query("visitation_usa_places.places", {
"fields": [{"name": "location_id"}, {"name": "brands"}],
"filters": [
{"fieldName": "street_address", "operator": "==", "value": "8300 Floyd Curl Dr Ste 105"},
{"fieldName": "brands", "operator": "==", "value": "H-E-B"},
],
})
matches["SA-03"] = (resolved[0]["location_id"], "search")
5. The mapping, and the join it enables
The result is a mapping table: your identifier, our identifier, and the pass that produced it.
| Store | location_id | Matched by |
|---|---|---|
| SA-01 | zzw-222@8sz-ty2-kj9 | exact |
| SA-02 | 227-226@8sz-tzb-zvf | exact |
| SA-03 | 222-222@8sz-tfd-789 | search |
| SA-99 | unmatched |
Every metric dataset carries the same location_id, so the mapping joins the list to any of them. One query proves it, visits for the three matched stores in 2025:
ids = [loc for loc, _ in matches.values()]
visits = run_query("visitation_usa_places.foot_traffic_month", {
"fields": [{"name": "location_id"}, {"name": "visits_sum", "aggregation": "SUM"}],
"filters": [
{"fieldName": "location_id", "operator": "in", "values": ids},
{"fieldName": "observation_start_date", "operator": ">=", "value": "2025-01-01"},
{"fieldName": "observation_start_date", "operator": "<", "value": "2026-01-01"},
],
"groupBy": [{"fieldName": "location_id"}],
})
by_id = {v["location_id"]: float(v["visits_sum_SUM"]) for v in visits}
for store, (loc, _) in matches.items():
print(store, loc, f"{by_id[loc]/1e6:.2f}M visits in 2025")
| Store | Visits, 2025 |
|---|---|
| SA-01 | 1.60M |
| SA-02 | 1.54M |
| SA-03 | 1.15M |
How to read it: the join works on the identifier alone; from here every other example applies to the list as it stands. Keep the mapping with the pass that produced each row, and re-run the exact pass against the directory each quarter: places open, close, and get re-identified.