Opening & Joining Places
What's worse than receiving a long anticipated package, only to find out it has a 47 step "do-it-yourself" manual? Nothing...absolutely nothing. That's why we strive to package rich datasets that are ready to use upon delivery. That said, there are a few necessary steps in order to get your data working for you.
SafeGraph packages their rich datasets to be ready upon delivery, so you can use it ‘out-of-the-box’. These instructions show you how to open and join places data for Shop and Sample Data customers. With this data, you’ll be able to gain insights into geographic distribution and POI visits across geography and time.
OPENING PLACES
*Please note: The following instructions for opening places are only pertinent to Shop and Sample Data customers. Enterprise Customers: Please reference the Bulk Data Delivery documentation.
- Step 1 (download & save)
Once you've completed your purchase on SafeGraph Shop, you should see the following prompt at the top of your screen:
Click "here" to navigate to your downloads. Alternatively, you can click on the "DOWNLOADS" button located at the top right of your screen.
From here, select the link reflecting the "Name" and "Created At" date of your recent purchase (Note that this will show as "PROCESSING" until your data is ready to download.) :
The datasets will automatically start downloading to the "Downloads" folder on your local machine once selected.
Data Sample Customers: Download the .zip file from your email to a known location on your local machine. Proceed to step #2.
- Step 2 (un-zip)
Navigate to the downloaded .zip file on your local machine. Right click the file and select "Extract all," "Unzip," or "Open With" depending on your operating system and/or installed plugins. This will create a new folder with the same name as the .zip file. This folder contains each individual file within the original .zip.
Depending on file size and/or the tool used to "un-zip" the data, you may need to repeat the previous step for all remaining files ending in .zip or .gz.
Once all files are extracted, you are ready for action! Proceed as usual in your preferred environment.
JOINING PLACES
Like Jack Johnson, we know "it's always better when we're together," and we advise enjoying our delicious datasets seamlessly blended together. Below is a breakdown of how our datasets are automatically joined for you:
- Places, Geometry, and Patterns
If you downloaded Places+Geometry+Patterns, then core_poi, geometry, and patterns have been joined usingplacekey
as the primary key. The file should read ascore_poi-geometry-patterns.csv
-
Places and Geometry
If you downloaded Places+Geometry, then core_poi and geometry have been joined usingplacekey
as the primary key. The file should read ascore_poi-geometry.csv
-
Places and Patterns
If you downloaded Places+Patterns, then core_poi and patterns have been joined usingplacekey
as the primary key. The file should read ascore_poi-patterns.csv
-
Geometry and Patterns
If you downloaded Geometry+Patterns, then geometry and patterns have been joined usingplacekey
as the primary key. The file should read asgeometry-patterns.csv
But wait...there's more!
Brand Info
The core_poi file is delivered with a supplemental brand file named brand_info.csv.
To join this file to core_poi, use "safegraph_brand_id" as the primary key. Stuck? See an example written in SQL below:
SELECT *
FROM core_poi
LEFT JOIN brand_info
ON core_poi.safegraph_brand_ids = brand_info.safegraph_brand_id;
Panel Overview Data
Patterns is delivered with three supplemental files: home_panel_summary.csv
, visit_panel_summary.csv
, and normalization_stats.csv
. These files provide insight into the size and geographic distribution of our panel and should be used to normalize visits to POIs across geography and time. See our Data Science Resources for normalization best practices.
Please note that the columns containing multiple census block groups per record in patterns - visitor_home_cbgs
and visitor_daytime_cbgs
- are JSON data types and will need to be parsed before joining to the "census_block_group" column in the home_panel_summary.csv
file. For an easy way to parse JSON tuples, see our free CSV Exploder tool.
The "region" column in normalization_stats.csv
and the "state" column in visit_panel_summary.csv
can be directly joined on the "region" column in SafeGraph Patterns.
For detailed column orderings per product combination, please reference the Column Ordering section in the Places Manual.
Updated about 2 years ago