Product Builder Resources š¤
Introduction
New to navigating the SafeGraph schema? Need to calculate deltas, find particular sets of POIs, or understand how to interpret relationships in our data? You're not alone, and this page is for you!
See below for common questions and workflows - along with example queries - to help kickstart your journey from "I have SafeGraph data" š¤·āāļø to "I am realizing valuable insights from SafeGraph data!" š”
- Query Example Notes:
- All code examples are in SQL. If a different language is preferred, paste the code sample into AI (OpenAI, Google Gemini, etc.) and prompt the AI to "Convert this code from SQL to [desired language]."
- The table names (following
FROMstatements) are generic and will need to reflect whatever table name you assign to our data in your environment. In the examples below, the generic table names store the following attributes:places: Includes ALL places (base, rich, address, premium rows) and all geometry attributesbrand_info: Includes all brand info attributes
- Queries involving more than 1 release will designate
places_oldandplaces_newtable names and/or[column name]_oldand[column_name]_newcolumn names.
Places
Detecting change across releases
- Prompt: "I need to know which places were removed, added, or saw a specific column value change compared to the previous release."
| Change Type | Query |
|---|---|
| View all places added | SELECT places_new.* FROM places_old RIGHT JOIN places_new ON places_old.placekey = places_new.placekey WHERE places_old.placekey IS NULL |
| View all places removed | SELECT places_new.* FROM places_old RIGHT JOIN places_new ON places_old.placekey = places_new.placekey WHERE places_old.placekey IS NULL |
| View all places with a specific column value change | Places with an
Places that closed (
|
Isolating Premium Rows
For customers that subscribe to our "premium" rows like industrial, apartments, and point POIs (see docs to learn more).
- Prompt: "I license premium row POIs and want to find them."
| Premium Row Type | Query |
|---|---|
| Point POIs |
EV charging stations only ex:
|
| Apartments | SELECT * FROM places WHERE naics_code = '531110' |
| Industrial POIs: Warehousing and Storage, Mini Warehouses and Self-Storage Units, Couriers and Express Delivery Services, Data Centers, Refrigerated Warehousing and Storage, Industrial Equipment Wholesalers, Other Merchant Wholesalers, Manufacturing Facilities, Motor Vehicle Manufacturing, Aircraft Manufacturing | SELECT * FROM places WHERE naics_code LIKE '4931%' OR naics_code = '531130' OR naics_code = '492110' OR naics_code = '518210' OR naics_code = '493120' OR naics_code LIKE '4238%' OR naics_code = '424490' OR naics_code = '423910' OR naics_code LIKE '3399%' OR naics_code LIKE '3361%' OR naics_code = '336411' |
How to find important/prominent POIs
We know that some places are more important than others and have specifically built our data to reflect customer requirements for a growing list of POI definitions across many countries. The following list of place definitions have had concerted efforts to ensure that we:
1.) Provide full coverage in the specified geography
2.) Enable customers to isolate only these POIs from a single query that guards against "noise" and/or other POIs with a "close-but-not-exact" definition match. NAICs are often not 1:1 matches for specific POI definitions, so some queries further narrow within a single naics_code or stretch across more than one naics_code using other features.
| POI Scope | Geography | Query | Geometry |
|---|---|---|---|
Sports Venues (except car and horse racing tracks). List of applicable designating the main sport(s) played at the venue plus any known professional, collegiate, or sanctioned club teams affiliated with the venue as available. | US, CA, Western Europe, AU | NBA Arenas ex:
| Captures the entire premise of the venue. Sub-venue POIs should reflect the venue in parent_placekey. |
Car and Horse Racing Venues. List of applicable designating the main sport (Racing or Horse Racing) raced at the track plus major car racing leagues affiliated with the venue as available. | US, CA, Western Europe, AU | NASCAR Stadiums ex:
| Captures the entire premise of the venue. Sub-venue POIs should reflect the venue in parent_placekey. |
Performance Art Venues. List of applicable designating a more granular description of the venue type (ex: "Convention Center", "Music Arena", etc.) as available. | US, CA, Western Europe, AU | Music Arenas ex:
| Captures the entire premise of the venue. Sub-venue POIs should reflect the venue in parent_placekey. |
| International and Large Regional Airports | GLOBAL | SELECT * FROM places WHERE naics_code = '488119' AND category_tags LIKE '%Airport%' | Captures the entire premise of the airport. Sub-airport POIs should reflect the airport in parent_placekey. |
Terminals and Concourses within International and Large Regional Airports. "International Terminal" and "Domestic Terminal" are possible category_tag values. | US | SELECT * FROM places WHERE naics_code = '488119' AND category_tags LIKE '%Terminal%' | Captures the entire terminal/concourse premise. Sub-terminal/concourse POIs should reflect the terminal/concourse in parent_placekey, and the terminal/concourse itself should reflect the larger airport in parent_placekey. |
| Prisons and Jails. Includes small, municipal jails and detention centers as well as maximum security federal prisons. | US | SELECT * FROM places WHERE naics_code = '922140' | Captures the entire prison/jail premise. In many cases, we do not have sub-prison/jail POIs, but where we do, they should reflect the prison/jail in parent_placekey. |
Shopping Malls. Note that for definitions on 11 possible values). | US and CA | SELECT * FROM places WHERE naics_code = '531120' | Captures the entire premise of the mall. Mall tenant POIs should reflect the mall in parent_placekey. |
College/University Campuses for 4-year universities, junior colleges, trade schools, etc. These are depicted by the following ) depicts the main campus and is required to exclude similarly named and categorized sub-campus POIs like "the business school." | US and CA | Junior Colleges ex:
| Captures the entire premise of the college campus. Sub-campus POIs should reflect the main campus in parent_placekey. |
Primary and Secondary Schools: Kindergarten through high school for public and private/charter/boarding schools. The following ): "Boarding School", "Early Childhood Education", "Elementary School", "High School", "Home School", "Kā12 School", "Middle School", "Montessori School", "Private School", "Public School", "Religious School", "Secondary School", "Special Education School", "Waldorf School", "Youth Programs & Services". | US and CA | SELECT * FROM places WHERE naics_code = '611110' | Geometry exists, but precise polygons are not available for all locations. We expect to close this gap by the July 2025 release. |
| Places Of Worship for all recognized religious groups. | US and CA | SELECT * FROM places WHERE naics_code = '813110' AND category_tags LIKE '%Place of Worship%' | Captures the entire premise of the place of worship. Sub-place of worship POIs should reflect the place of worship in parent_placekey. |
US Military Bases. The following category_tags depict which branch of military operates from each base: "US Army", "US National Guard", "US Air Force", "US Navy", "US Marine Corps", "US Defense Logistics Agency", or "US Space Force". | GLOBAL | US Air Force ex:
| Captures the entire premise of the military base. Sub-military base POIs should reflect the military base in parent_placekey. |
US Federal, State, and Local Courthouses. Federal courthouses have "Courthouses" category_tags while state and local courthouses have null category_tags. | US | Federal Courthouses ex:
| Captures the entire premise of the courthouse. |
| Hospital Campuses. Does not include sub-hospital campus POIs like "Trauma Center" or "Cardiology"). | SE, NO, DK, FI | SELECT * FROM places WHERE naics_code = '622110' | Precise geometry not currently supported for most locations. |
| Shopping Malls | SE, NO, DK, FI | SELECT * FROM places WHERE naics_code = '531120' | Precise geometry not currently supported for most locations. |
| College/University Campuses. Includes standard, 4-year universities and trade/technical colleges. | NO, DK, PL | 4-year Universities ex:
Trade/technical Colleges ex:
| Precise geometry not currently supported for most locations. |
College/University Campuses. Includes satellite campuses affiliated with a given university - designated by the word "campus" in location_name. | SE |
Satellite Campuses excluded ex:
| Precise geometry not currently supported for most locations. |
| College/University Campuses | FI | SELECT * FROM places WHERE naics_code = '611310' | Precise geometry not currently supported for most locations. |
Primary and Secondary Schools: Kindergarten through high school for public and private/charter/boarding schools. Kindergarten-only schools have "Elementary School" category_tags. | PL | SELECT * FROM places WHERE naics_code = '611110' | Precise geometry not currently supported for most locations. |
Nationally Protected Parks, Monuments, Preserves, etc. as defined by the . The following | US | National Parks ex:
| Captures the entire premise of the national park/preserve/etc. Sub-park POIs should reflect the park in parent_placekey. |
| Labor Union Offices for federally recognized unions registered with the Department of Labor via the "Labor-Management Reporting and Disclosure Act." | US | SELECT * FROM places WHERE naics_code = '813930' | Precise geometry not currently supported for most locations. |
Isolating POIs by unique features
For customers who leverage rich attributes like category_tags (docs) and/or amenity columns (docs) to isolate POI subsets meeting specific criteria. Commonly used by mapping and generative AI platforms to support user search, marketers to create location based audiences or plan OOH (out of home) campaigns, retailers to support real estate site selection, etc.
| Prompt | Query |
|---|---|
| "I want to find sports bars in New York that have a TV, wifi, parking, happy hour, burgers, and accept reservations." | SELECT * FROM places WHERE region = 'NY' AND category_tags LIKE '%Sports Bar%' AND category_tags LIKE '%Burgers%' AND amenities LIKE '%TV%' AND accessibility LIKE '%Parking%' AND service_options LIKE '%Accepts Reservations%' AND service_options LIKE '%Happy Hour%' |
| "I want to identify cafes in California that offer bagels, delivery, have outdoor seating, and are family friendly with a casual vibe." | SELECT * FROM places WHERE region = 'CA' AND category_tags LIKE '%Cafe%' AND category_tags LIKE '%Bagels%' AND amenities LIKE '%Outdoor Seating%' AND service_options LIKE '%Delivery%' AND setting LIKE '%Family Friendly%' AND setting LIKE '%Casual%' |
| "I want to find Department Stores in Texas that sell perfume." | SELECT * FROM places WHERE region = 'TX' AND category_tags LIKE '%Department Store%' AND category_tags LIKE '%Perfume%' |
How to link brand relationships
See brands and brand_info docs to learn about how we qualify "brands" and what details we capture.
| Prompt | Query |
|---|---|
| "I want to understand which brand locations in places are owned by a parent brand/company." |
|
| "I want to understand which brand locations in places are owned by a parent brand/company that is also owned by a parent brand/company." |
|
How to run a time series
Just plug your SafeGraph places table into the compute_brand_time_series_pd() function (see the two versions below), pick your brand_list and date range and go!
Month-to-Month Open Counts (wide format)
This variant of compute_brand_time_series_pd() will:
- Back-fill missing open/close dates
- Filter to only your target
brand_ids - Cross-join against a calendar of months
- Count unique, active stores per brand Ć month
- Pivot into a brands-as-rows, months-as-columns matrix
Monthly Open/Closure Counts (long format)
This second version of compute_brand_time_series_pd() returns a tidy table with one row per brand-month, including:
count(active stores)new_opened(stores that opened that month)new_closed(stores that closed that month)
Both functions handle all the date math, edge cases, and missing values for you. Just grab the code from the table below, paste it into your Python environment with pandas installed, and youāre ready to power charts, dashboards, or whatever downstream analysis youāve got in mind.
from typing import List
import pandas as pd
def compute_brand_time_series_pd(
df: pd.DataFrame,
brand_list: List[str],
start_date: str,
end_date: str
) -> pd.DataFrame:
# Precompute constants
start_ts = pd.to_datetime(start_date)
end_ts = pd.to_datetime(end_date)
brand_set = set(brand_list)
months = pd.date_range(start=start_ts, end=end_ts, freq="MS")
full_labels= months.strftime("%-m/%-d/%Y")
# Prepare DataFrame
df = df.copy()
if "brands" in df.columns:
df = df.rename(columns={"brands": "brand_name"})
# Logic for opened_on:
# use opened_on if present
# if opened_on is null, use tracking_closed_since if present
# if opened_on & tracking_closed_since is null, use '2019-07-01'
df["opened_on_filled"] = (
df["opened_on"]
.fillna(df["tracking_closed_since"])
.fillna("2019-07-01")
)
df["open_dt"] = pd.to_datetime(df["opened_on"], errors="coerce").fillna(start_ts)
df["close_dt"] = pd.to_datetime(df["closed_on"], errors="coerce").fillna(pd.Timestamp("2099-12-31"))
# Filter to only your brands
df["brand_id"] = df["safegraph_brand_ids"].astype(str).str.strip()
df = df[df["brand_id"].isin(brand_set)]
# Cross-join with calendar
brands = df[["brand_id","brand_name"]].drop_duplicates()
brands["__k"] = 1
cal = pd.DataFrame({"month": months})
cal["__k"] = 1
brand_cal = brands.merge(cal, on="__k").drop(columns="__k")
# Flag active stores
df["open_month"] = df["open_dt"].dt.to_period("M").dt.to_timestamp()
df["close_month"] = df["close_dt"].dt.to_period("M").dt.to_timestamp()
joined = brand_cal.merge(
df[["brand_id","brand_name","placekey","open_month","close_month"]],
on=["brand_id","brand_name"],
how="left"
)
op_cl = joined[
(joined["month"] >= joined["open_month"]) &
(joined["month"] < joined["close_month"])
]
# Aggregate & pivot table
long = (
op_cl
.groupby(["brand_name","month"], observed=True)
.placekey
.nunique()
.reset_index(name="count")
)
wide = (
long
.assign(month_label=long["month"].dt.strftime("%-m/%-d/%Y"))
.pivot_table(
index="brand_name",
columns="month_label",
values="count",
fill_value=0
)
.reindex(columns=full_labels, fill_value=0)
.reset_index()
)
return wide
# Set your parameters (change these to the dates and brands you wish to analyze)
start_date = "2019-07-01"
end_date = "2025-07-01"
brand_list = [
"SG_BRAND_3bbe7681bc7a60ed3d22764734cb699a",
"SG_BRAND_87748feb2bf7404a6bcfe5be735f2b75",
"SG_BRAND_cebb2b4363445dce8031918685aab2c4"
]
wide = compute_brand_time_series_pd(pd_places, brand_list, start_date, end_date)
display(wide)def compute_brand_time_series_pd(
df: pd.DataFrame,
brand_list: List[str],
start_date: str,
end_date: str
) -> pd.DataFrame:
"""
Returns a long DataFrame with columns:
brand_name, month, count, new_opened, new_closed
"""
# Precompute constants
start_ts = pd.to_datetime(start_date)
end_ts = pd.to_datetime(end_date)
brand_set = set(brand_list)
months = pd.date_range(start=start_ts, end=end_ts, freq="MS")
# Prepare df
df = df.copy()
if "brands" in df.columns:
df = df.rename(columns={"brands": "brand_name"})
df["opened_on_filled"] = (
df["opened_on"]
.fillna(df["tracking_closed_since"])
.fillna("2019-07-01")
)
df["open_dt"] = pd.to_datetime(df["opened_on"], errors="coerce").fillna(start_ts)
df["close_dt"] = pd.to_datetime(df["closed_on"], errors="coerce").fillna(pd.Timestamp("2099-12-31"))
df["brand_id"] = df["safegraph_brand_ids"].astype(str).str.strip()
df = df[df["brand_id"].isin(brand_set)]
# Compute monthālevel open/close
df["open_month"] = df["open_dt"].dt.to_period("M").dt.to_timestamp()
df["close_month"] = df["close_dt"].dt.to_period("M").dt.to_timestamp()
# Crossājoin brands Ć months
brands = df[["brand_id","brand_name"]].drop_duplicates()
brands["__k"] = 1
cal = pd.DataFrame({"month": months})
cal["__k"] = 1
base = brands.merge(cal, on="__k")[["brand_name","month"]]
# Active count
alive = (
base
.merge(
df[["brand_name","placekey","open_month","close_month"]],
on="brand_name",
how="left"
)
.query("month >= open_month and month < close_month")
)
active = (
alive
.groupby(["brand_name","month"], observed=True)
.placekey.nunique()
.reset_index(name="count")
)
# New opened
opened = (
df
.groupby(["brand_name","open_month"], observed=True)
.placekey.nunique()
.reset_index(name="new_opened")
.rename(columns={"open_month":"month"})
)
# New closed
closed = (
df
.groupby(["brand_name","close_month"], observed=True)
.placekey.nunique()
.reset_index(name="new_closed")
.rename(columns={"close_month":"month"})
)
# Stitch them back onto base
result = (
base
.merge(active, on=["brand_name","month"], how="left")
.merge(opened, on=["brand_name","month"], how="left")
.merge(closed, on=["brand_name","month"], how="left")
.fillna(0)
.assign(
count = lambda d: d["count"].astype(int),
new_opened = lambda d: d["new_opened"].astype(int),
new_closed = lambda d: d["new_closed"].astype(int)
)
.sort_values(["brand_name","month"])
.reset_index(drop=True)
)
result["month"] = result["month"].dt.strftime("%Y-%m-%d")
return result
# Set parameters (change these to the dates and brands you wish to analyze)
start_date = "2019-07-01"
end_date = "2025-07-01"
brand_list = [
"SG_BRAND_3bbe7681bc7a60ed3d22764734cb699a",
"SG_BRAND_87748feb2bf7404a6bcfe5be735f2b75",
"SG_BRAND_cebb2b4363445dce8031918685aab2c4"
]
pd_ts = compute_brand_time_series_pd(pd_places, brand_list, start_date, end_date)
display(pd_ts)Geometry
How to navigate spatial relationships (parent<>child)
In the real world, some places are "stand-alone" and distinctly inhabit the entire space they occupy. In other cases, many places may coexist within the same four walls, there may be several places located within a larger place, or there may be several places located "on the grounds" of a larger place. Our geometry concepts like polygon_class, spatial hierarchy, and enclosed aim to provide a blueprint depicting the physical relationships places share with one another.
| Prompt | Query |
|---|---|
"I want to see places and geometry columns for a POI's parent (and the parent's parent when applicable)." Note: This query is especially helpful when direct parent<>child relationships do not surface as expected. For example, a Panera Bread inside of a mall may show a parent that is not the actual mall. Whether this is intentional or a bug, the parent of Panera Bread should show the mall as its parent - making the Panera Bread the "grandchild" of the mall. |
|
Updated 11 months ago