IP addresses, my postal address, my email - this type of data has been collected about me for years, likely being sold off to other companies so they can know exactly what type of niche, cotton turtle neck sweater to show me and when so I just HAVE to buy it…Finally however, I have the chance to be on the opposing end of it. All jokes aside however, I recently got the opportunity to look into the data as a service company, Semcasting; a company which links together every digital channel and real world signal to deterministically resolve persistent identities to both individuals and households. In order to best prep to interview at this company and simply learn more about a space i find intriguing (identity resolution, big data, and AI integration), I figured a blog post would be perfect to both document my learning journey and thought process as I also construct what is - hopefully - a useful project to better understand the space they operate in.
While this definitely won't make me an expert by any means, it can only help and will surely come in use for the future, so without further ado, let's begin.
Some buzzwords before we dive into the project:
The context of this is important and I always like to have my foundations in check before getting… ahead of myself… so let's quickly review some important concepts/terms necessary to understanding Semcasting and what the company offers!
Identity resolution: The problem space Semcasting operates in; with so many scattered signals in today's modern age - ip addresses, postal codes, device ids - being able to resolve and group them to one, persistent identity becomes extremely valuable for verifying existing customers and reaching new ones.
Deterministic vs probabilistic matching: rather than inferring likely matches between data and people (this smart tv streams outdoors channels and this laptop browse hiking gear, 75% the same person), Semcasting uses matching for only verifiable shared identifiers.
Identity graph: the network that links all the identifiers to the resolved entities. This is essentially the core of what everything else will be built upon.
Tagging / pixel / tracking tag: A tiny, almost invisible piece of code on a website companies will use to track when customers perform certain interactions on the site (for example, an invisible <img> tag on your website links to google, you have an ad on google for your website, when someone clicks the link and gets sent to your website that tag makes a request to google, and now google has a receipt that someone navigated to your site successfully from the ad)
More technical concepts (i'll try to make it quick 🤓)
ETL: Extract, transform, load - the core foundation of moving data. Get data from the source, clean or reshape it, and finally drop it to the final destination.
Columnar storage: storing data in columns rather than rows. If we only care about one attribute of the data, it is much faster to retrieve this way rather than fetching the whole row which will have excess information
Snowflake: A cloud data warehouse that utilizes massively parallel processing (MPP) where the storage and the engine are separate. That way, Snowflake can spin up the corresponding amount of/powered virtual warehouses (clusters of processors) depending on who needs it and the demand.
Hashing for PII (personally identifiable information): scrambling sensitive information so it becomes the same, fixed randomized sequence. That way, the information can still be matched to the same entity but cannot be unscrambled to the original data.
The Project
WOOH okay. Now that all the jargon is hopefully out the way - onto the project itself. As a quick synopsis, My hope is to build:
"a Python ETL pipeline that does a deterministic "matchback" - the core of ad attribution- concretely" → (using the exact deterministic data matching mentioned above to link things together with 100% certainty)
Via….
Step 1 : Scaffolding!
Basic setup of credentials in a .env file with Snowflake credentials
Step 2: Fake it till you make it
Generate two fake data sets, crm_records.csv (id, name, email, zip) & ad_exposures.csv (email, campaign, timestamp, converted)
Make emails overlap slightly for realistic match rate
Slightly "dirty" some emails (uppercase, extra spaces, etc) so normalizing data later on has an effect
Step 3: ETL
Read both CSVs into Python, normalize the data (clean it), then SHA-256 hash it for realistic security protocols (hashed version will become our key instead of emails themselves)
Step 4: Load baby load
Create DEMO_WH warehouse (the engine to run our queries) + MATCHBACK database (what holds our schemas and tables) and load both datasets into Snowflake using snowflake-connector-python.
Step 5: Satisfaction 🥹
Deterministic join on hashed emails and report match rate and the attributed conversion (match rate = % of ad viewers we could tie to a known person; attributed conversions = those matched viewers who then took the campaign's goal action)
The project is scoped to be intentionally small as the main function is to understand what Semcasting's attribution does - to grasp the foundational mechanics hands-on rather than just read about it. While this is a simplified version of the work Semcasting performs, I hope this will still give me a better understanding of the workspace they operate in! Onward!!!
github.com/SpencerTong/semcasting_demo_project