Warehouse Enrichments: A COVID Hobby
Way back in 2020, as we battled cabin fever, we developed a method for performing API enrichments in the warehouse using Snowflake external functions. And we loved this process, because it allowed us to:
- Cut down on API usage: Storing large downloaded datasets can be expensive, but repeatedly pinging third part enrichment providers can eat away at API quotas repeating unnecessary work.
- Keep our data fresh: Now, we could control freshness across all of our apps by setting the sync frequency once in Census.
- Maintain consistency: By enriching directly in our warehouse and applying the changes to the datasets we sync, we could easily ensure that all of our data across our different apps, from Salesforce to Zendesk, share the same view of our customer.
Essentially, we built a SQL workflow to ping Clearbit for our enrichments, cache the results, and sync them out via Census. A highly technical team could implement this today. The only problems are:
- This was time consuming: This was cool, but our data team had to find the time to build and maintain it.
- This grows in complexity with every provider: Because we were only using Clearbit, this worked for us. But if using specialized services on specific datasets or enriching from multiple sources, this could quickly become more of a project.
Conserving API Quotas and Solution Archaeology
A few months back, we were chatting with a customer who was frustrated that they continually hit API limits enriching their Salesforce data via Clearbit. Census has a Clearbit integration, so we shared this as a solution. But then we got to thinking:
- Our users shouldn't be constrained to enrichment connections we selected
- Enriching in the warehouse doesn't have to be hard
So we decided to turn what we built into an enrichment pattern that anybody could use.
Warehouse Enrichments the Easy Way
Today, we're excited to announce that this protocol is now available in Census as HTTP columns. You can set up enrichments from any API endpoint directly in Census, without the lengthy setup or SQL maintenance. Any you can sync those enriched datasets directly to any connection.
You can check out how it works below, or check out our docs to get the nitty-gritty.
Large-scale enrichment providers are pricey, but here are a few ways you can get started with free or low-cost APIs to improve your data:
Identifying anonymous traffic
Generating insights from anonymous usage is one of the most common requests a data team will receive from marketing. Connect to a service like MaxMind which takes browser event information such as IP address and provides geographic information so you can determine where in the world traffic is coming from.
Address verification
Using invalid data to run campaigns is like lighting money on fire. Taking the time to verify that addresses, both email and physical, are valid before running campaigns can save a lot of money and increase ROAS as a result. Doing that once in your source of truth is the most efficient approach. Now any of your downstream deduping, segments, campaigns, and syncs to destination services can rely on having the most accurate and standardized address verification. Even the US Postal Service offers an API for this.
Integrating internal services
HTTP Enrichment isn't limited to working with public data provider companies. You can enrich from any JSON API endpoint, which means you can also use private APIs your company already offers. For companies with existing APIs to support their existing apps or microservice architecture, you can now easily integrate API data into your data warehouse ondemand without relying on a separate ETL process. Use this to connect to an internal API that returns live inventory availability or connect to your proprietary recommendation algorithm to connect product recommendations for retention campaigns.
Check it out in Census to get started!