How to Build a Marketing Data Warehouse in 30 Days
Making the leap from spreadsheets to a data warehouse can seem like an enormous jump, but thanks to “Modern Data Stacks” like Fivetran, Snowflake, Tableau, PowerBI, and Sigma, we can help you get from here-to-there in under 30 days.
Back in 2001, I was taking a SQL class at UCLA, and I overheard a classmate talking about a Data *Warehouse* he was working on. From the sounds of it, this was not just any database, but a mysterious and gigantic system akin to the hull of an aircraft carrier, guarded by men in black suits, clipboards, and PhD’s. Working with “cubes” and “star schemas” seemed to me like the major leagues of working in data.
I never pursued a PhD, but 20 years later, data warehouses have become a sort of standard. In fact, tools like Snowflake and BigQuery make managing Billion- and Trillion-scale databases a cinch. But to this day, I still imagine that many Marketers still feel as overwhelmed about “Data Warehousing” as I did as a young developer just starting out.
Thankfully, we’ve reached the era of the “Modern Data Stack”, and not a minute too soon. Marketers, for one, are drowning in an ocean of data platforms - over 10,000 data platforms by some accounts - and this proliferation has turned Data Integration into an existential problem for Marketers.
In 2021 the average company has their mission-critical data stored in between 10-30 different platforms! This turns the answers to simple questions into system integration nightmares. It’s not uncommon to see companies ask questions like “Can we see our Sales & Marketing data in one dashboard”, but the answer takes 3+ months of intense database development. So its true that nowadays companies need to build a latticework just to get to the insight.
But it doesn’t have to be this painful!
At Latticework Insights, we specialize in helping companies reconnect all of their 1st and 3rd party data in a central data warehouse, and helping them create dashboards that allow them to focus their attention back on running their business.
In fact, if a company is ready to make the switch, we can stand them up on a basic “Marketing Data Warehouse” in less than 30 days!
After going through this process with literally dozens of companies over the last few years, we’ve got it down to a science. Below, we walk you through each step that we take when building a warehouse from scratch, how long we spend on each step, and where all the gotchas live at each step.
Getting Started
It turns out, getting all your marketing data into a central warehouse is the easy part. Modern data tools like Fivetran and Snowflake, make getting this whole thing done in 30 days very straightforward. Especially if you’re not focused on the enormous “long tail” of marketing tech platforms, but instead focused on just the high-value, well-mapped-out sources that almost every company uses to market and sell. In fact, the “Ingestion” and the “Delivery” portion each only take about 20% of the total time.
We’ve done enough of these projects that we see the 20-60-20 pattern emerge in almost every project…
In fact, we’re even able to break it out into further detail, where we know exactly where all the time and resources of the project are going to come into play. Call this the 10-10-30-30-10-10 pattern below…
Then we just run our standard playbook.
1. Inventory
First things first, we need to understand what we’re collecting. There’s almost 10,000 marketing tech platforms nowadays and we need to start getting specific fast. We’ve only got 30 days after all.
It’s hard to be an export in every Marketing Tech platform, but the good news is most companies tend to use the same 5-10. They’re buying on Facebook and Google. They’re selling on Shopify and Recharge. They’re engaging users on Salesforce and Mailchimp. And they’re measuring on Google Analytics or Mixpanel.
2. Access
Then we need some logins. We need get into the source platforms, get authenticated, and start grabbing the right data. Platforms like Fivetran make extracting that data completely painless. Sometimes they've already pre-built reports for you so don't have to scan through 100+ columns to decide what you want. They deliver clean, standard schemas into warehouses like Snowflake, so we know exactly what to expect. Imaging build the Facebook Ads schema below by hand? Well nowadays, you the work is already done for you.
This is a quick process. You should be able to get Fivetran loading all your sources into a Snowflake instance in 1-2 days. Then comes the fun part.
3. Data Validation
Is all this loaded data correct? You may be surprised, but many API’s expose slightly different versions of their source data than what comes out of their source UI’s. And sometimes the API has hiccups, and forgets to deliver a couple days worth of stuff.
You’d be surprised how often folks fail to plan for this section of the process. But it’s well mapped out territory for our team. Just bear in mind that this phase often requires 50% more time and attention than the first two phases combined. Lots of clients can get spooked by this process and abandon the project at this phase, but it’s all very standard.
4. Joining & Visualizing
Now comes the actual fun part. We need decide how to “Reconnect the Dots”. This isn’t some ethereal concept. For example, when someone orders a Big Mac at McDonalds, over 25 systems store some important sliver of that transaction. Sales, marketing, inventory, operations, payroll, accounting. Some of those systems store things at different levels of granularity (ex. Some systems speak users & email addresses. Others speak days or campaigns. Others speak DMA’s. And some of the systems even contradict other systems!.
We quite literally need to reassemble the McDonalds Big Mac if you will. It goes beyond just figuring out join keys (although that's part of it). This is both art & science and its the core of our expertise.
And once we’re done joining, we’re really start sprinting. There’s lots of questions you can ask of your marketing data, but we've heard most of these questions at some point in the past. All the major ad systems tend to speak the same stuff (impressions, clicks, conversions, ROAS, CPA, LTV, Churn Rates) and covering 80% of those questions is usually just plotting them into a template that we’ve already got built.
Remember, we’re at the 80% mark and we’ve only got 6 days left in the project. Let’s get this in the client's hands for review.
5. Handoff
Handoff, or - as it's sometimes adoringly referred to - “Quality Assurance” is an important phase to plan for. We ask for sample reports up front so we know what we’re comparing to. If we’ve done our work correctly, there’s little risk of major slip. This is generally just pixel pushing and chasing a small amount of edge cases.
6. Training
At this point we’re getting the client prepared to fly the airplane themselves. There’s very little for them to configure since this is all so well mapped out. If they want to bring in some new data sources, or create some very specific custom metrics - or if they want to blow their stakeholders out of the water with some custom art direction - we’re jotting all this down and prepping for months 2 and 3.
But let's not lose the forest for the trees here.
30 days ago there was no infrastructure and zero data scientists. Today there’s a fully automated, fully functional “Marketing Data Warehouse” with a dashboard to run the company with.
We can do this for you and your clients too!
As you can tell from the blueprint above, this process is pretty standard for our team. The 30 day sprint may not include everything under the sun, but it’ll give your company all the major building blocks to run your company off a modern data stack, instead of messing around with spreadsheets and wrangling 3rd party platforms together.
We’d love to hear how we can help you get started. A modern “Marketing Data Warehouse” is a key asset for being competitive in 2021, and we want to help you get there.
Drop us a line at info@LatticeworkInsights.com and let’s get you started.
