Imagine you’re a retail brand trying to determine which of your stores performs best on a revenue-per-visit basis (RPV), compared to others. How would you solve this problem?
Analyses like these are interesting, because they require multiple disparate datasets to be brought together in order to tell a story. In this example, you’d be analyzing transaction data to calculate revenue per store over a certain time period, along with foot-traffic data. Maybe you’d even compare it against other variables like store age, square footage, and more. You would want to use data from different sources— store transaction data from Affinity Solutions, and foot traffic data from SafeGraph.
The tough thing about this approach is that you need all these datasets to talk to each other. Problems like these are why data scientists spend up to 80% of their time doing data preparation, rather than driving insights. This situation is especially problematic when you’re working with location data, since many addresses don’t have standardization. A Target at 2626 E Stone Dr in one dataset might be listed as 2626 E Stone Dr Ste 90 in another, even though they’re the same store. A dataset indexed on the first address wouldn’t match the second.
Placekey was designed to solve address and POI matching problems like these. It works like this: an address or place name is inputted, and Placekey generates a unique, authoritative identifier that can be used for address and POI matching, deduplication, normalization, entity resolution, and more.
This week, Placekey officially announced the launch of its no-code solutions for Google Sheets and Excel. What this means is that thorny address and POI matching problems can now be solved without writing a single line of code.
We’ll look at how Affinity Solutions, a leading provider for consumer purchase and intent behavior is using Placekeys appended to their data sets to make it easier to combine with other data sets and drive insights. For more context, you can check Nitin Duggal, Affinity’s Chief Product Officer discussing Placekey here:
This blog post will walk you through a practical example of a Placekey use case: where one is merging purchase intelligence information from Affinity, and places data from SafeGraph— all without code.
The Data: Affinity and SafeGraph
SinceAffinity is the leading provider of purchase data intelligence, their dataset covers granular transactions, down to the individual store level. Here’s a sample of data about US-based Target stores:
On the foot traffic data side, SafeGraph aggregates anonymized location data from numerous applications, and generates information like number of visits, bucketed dwell times, and more. Here’s a sample of SafeGraph data for one US-based Target location:
The Problem: Merging Location-based Datasets is Really Hard
While we want to merge these datasets together in order to leverage Affinity’s spend data and SafeGraph’s mobility information for each store, addresses weren’t invented in a time of machine-tooled standardization. Arbitrary format convention differences in point-of-interest or POI naming s make the task of joining location-based datasets together super hard. For example, in these two data sets above, the respective address formats differ considerably.
To unlock the insights that come from bringing multiple datasets together, we need a way to cleanly merge address and POI data.
The Solution: Meet Placekey Placekey is a universal standard identifier for a physical location, and it was created to solve problems like these. Placekey does the tough job of address and POI resolution, standardization, validation, and geolocation behind the scenes, producing instead a simple identifier that uniquely identifies a place. Here, the two different street names resolve to the same Placekey, which can then be used to merge the data together.
Now, using Placekey as a join key to merge these two datasets produces the below dataset:
Now, from these columns, it is possible to directly compute dollars spent per visit - an analysis which relies on data from both Affinity and SafeGraph, and gives a more holistic view of revenue-per-visitor (RPV) at a granular level. We can also leverage the store age column to produce the below plot of store age vs. conversion.
Demo: Merging Affinity and SafeGraph Data Using the Placekey No-Code Integrations for Excel and Google Sheets
To conduct a similar analysis, you can append Placekeys to your dataset using Excel or Google Sheets. Here’s how you do it:
After loading your data into Excel (or Google Sheets), open the Placekey extension, and click “Generate Placekeys”.
Enter your API key (note: this is not always required). If you don’t yet have one, sign up for free here in a few seconds.
Open the first dataset you’re working with. Map the headers in the data (i.e. brand, street, city, state, and zip) to the relevant API field values, and when you’re done, click “Generate Placekeys”
After a few seconds, the Placekeys are appended to the data in a new column.
Repeat the same process on the data you would like to join. Here, the SafeGraph data already has a Placekey column, making it super easy to join in without any pre-processing.
Use a standard Google Sheets or Excel VLOOKUP function to merge the rows of the two datasets. Here, we highlight two relevant columns — spend data from Affinity and visits data from SafeGraph, plus a simulated value for the age of the store.
From these columns, you can directly compute a measure of dollars spent per visit. As mentioned above, the store age column can also be leveraged to produce the below plot:
This example demonstrates how any best-in-class transaction dataset can be joined to third-party data sources by leveraging Placekey to match addresses and POIs. Placekey opens the door for analyses such as this and many more to be performed by expanding the interoperability of different data solutions.
Placekey is the universal standard identifier for a physical place. Learn more about us at Placekey.io.
Get ready to unlock new insights on physical places