Snowflake + Fivetran + dbt: Turn Your Marketing Data Silos into Marketing Insights

The 2020 Marketing LUMAscape showcases more than 8,000 tools marketers can use to generate leads, drive brand awareness, and measure all their marketing efforts. But with all those tools, comes a lot of disparate and siloed data. How do you bring them all together in a consistent, reliable, and fast way to understand your ROI, determine attribution, and see which of your marketing efforts are working? 

Leading marketers have found that the solution to their problems does not lie in the next new shiny SaaS application that creates another data silo. Rather, the solution lies in working across the aisle with IT and data teams to solve the foundational issue of fragmented data. By combining business and technical knowledge, businesses are making step-change improvements in their ability to deliver personalized engagements and achieve detailed attribution and higher return on marketing spend.

Once aligned, how do marketers and data teams build a strong data foundation? Companies such as Snowflake, Fivetran, and others have been hard at work building tools and systems to meet these analytics needs for years now, and together their solutions form the modern data stack. 

Haven’t heard of the modern data stack? Not to worry. We’ll walk you through how it works and how it can work for you—getting you the data you need to form true marketing insights.

To determine ROI, you need to understand how much you spend on all your paid advertising efforts and how much revenue you bring in based on that spend. 

To start, you have all your disparate data sources; let’s think of these in different categories. Paid advertising would be one of the biggest categories for ROI. This would include information from platforms such as Facebook, LinkedIn, Google AdWords, and really anywhere you are spending money on advertising. 

Many marketers start off comparing metrics such as CPC (cost per click), CPL (cost per lead), and so forth within the respective platforms. This is relatively simple if you are running campaigns only on two platforms; but if you expand beyond that or if you are running multiple campaigns within a platform, the complexity quickly gets out of hand. 

You can download the metrics and compare them using Excel. This makes the data easier to wrangle and manipulate, but you’ll have to repeat this download and wrangling exercise daily if you’re aiming to make decisions from the most up-to-date data. Excel might solve an immediate need, but it creates further data silos and multiple sources of truth, leading to conflicting insights across the organization. And this is just the headache associated with one category of data: advertising data. 

The next category of data you’ll want access to conduct an ROI analysis is your point-of-sale data. If you are an ecommerce company, perhaps you use Stripe, or if you are a SaaS company, maybe you use Salesforce. Either way, you’ll want to marry the opportunity you brought in through paid advertising to what each person spent with you over their lifetime—their lifetime value (LTV)—to arrive at ROI. 

How do you stitch all this together? With more spreadsheets, more manual work, and more headaches. 

Now, if you want to get sophisticated, determining your true, fully-baked ROI is going to take into account a lot more than just advertising spend; it should include things such as the salary of your marketing team, the tools you are using to serve those ads and measure them, and so forth. That is truly what you are investing to acquire your customers. 

But with only manual systems for understanding marketing metrics such as ROI, marketers are hesitant to add even more data sources to complicate an already fuzzy picture. 

The modern data stack begins with all of the data you use to do your jobs. You need to get the data out of all those disparate platforms and move it to somewhere where you can easily access it, model it, and understand it. This is where Fivetran, Snowflake, and dbt come in, as shown in the following figure. 

Fivetran is an automated data integration platform. With a few clicks of a button, you can connect all your data sources to Fivetran. Think of it as a pipeline: You take all your sources and pump them through that pipeline to get to the same destination. Of course, it’s a lot more complicated than a pipeline. If you ask any of your data engineering friends, they would tell you they spend hours a week building and maintaining such a pipeline for each system within your business. 

You don’t have to worry about being an expert in all of the technical nuances of your data sources. Getting the data out of your platforms can be tricky to do easily since each platform has its own intricacies (for example, API call limits, the need to normalize data into tables, and so on). Fivetran builds and continuously maintains its own integrations to a vast array of sources so you don’t have to.

Any changes made within a platform or to your data—let’s say new email addresses come in through Marketo, or new ads are served in LinkedIn—are all available in near real time. You don’t have to worry about stale data or manually getting the latest data; Fivetran constantly checks for and updates any data sources, both for your accounts’ updated values as well as for the latest API versions. 

Best of all, it requires zero coding for you to use Fivetran connectors. With the same simplicity of logging into your marketing platform (for example, LinkedIn or Marketo), you can get your data out of those platforms and into your data warehouse. 

With this automated pipeline in place, all the data from your different marketing platforms is pumped reliably and cleanly into Snowflake’s Data Cloud. Before examining the benefits of Snowflake, let’s first explore data transformations and how you can ensure your data lands in Snowflake ready for analysis.

Getting all the data into your data warehouse is just one piece of the puzzle—though admittedly a difficult piece. Next, you need to have the data ready to query. The dbt (data building tool) command-line tool enables data analysts and engineers to transform their data. 

Simply put, dbt is a transformation and modeling tool that applies software engineering best practices, such as version control, to programmatically run transformations to model your data for ingestion and broader use. It has become the industry standard protocol for how to merge disparate data. However, to work with dbt, businesses need a technology that can activate this protocol within their enterprise tech stack.

In order to help you do that, the Fivetran team dedicates significant time and resources to creating free dbt packages that help you solve many of your business and marketing challenges. Analysts can either immediately use Fivetran prebuilt packages as a starting point and then use SQL to create models for things such as ROI or attribution (for example, “show me all the user IDs associated with a click in the last  seven days”), or they can build and deploy models of their own within the dbt framework. 

The following figure shows the Fivetran, dbt and, Snowflake pipeline.

So far, Fivetran has built new packages every quarter, and the marketing-specific packages include packages for the following:

• Salesforce
• Twitter Ads
• Pinterest
• LinkedIn
• Marketo
• HubSpot
• Microsoft Ads

Each of these help you answer key questions behind each data source. For example, the Fivetran ad-based connectors transform the core ad object tables into analytics-ready models and include an “ad adapter” model that can be easily joined with other ad platform packages to get a single view.  

Want to learn more about Fivetran’s dbt transformations? Check out this press release. Also check out The CMO’s Guide to Automating Your Marketing Analytics. 

The beauty of the modern data stack is that you can unlock different levels of sophistication to answer your ROI question as well as allow the answer to grow as your efforts do. With all your data in one place, you can build multiple views of your ROI going from a single campaign or channel to a fully baked version, with ease and without hours of manual data cleanup. 

To demonstrate just how easy it is to set all this up, check out the following Fivetran video, which walks you through the few basic steps needed to set up your own data stack and start bringing your data silos together to generate marketing insight. 

The Snowflake Data Cloud is a global network where thousands of organizations mobilize data with near-unlimited scale, concurrency, and performance. Wherever data or users live, Snowflake delivers a single and seamless experience across multiple public clouds, eliminating all previous silos. The following figure shows how all your data is quickly accessible by all your data users with Snowflake’s platform.

Snowflake provides a number of unique capabilities for marketers. 

In order to build a true 360-degree view of your customers, the first step is to break the data silos and consolidate your data into a single data platform that can support different kinds of data. Snowflake can efficiently and securely store all data, regardless of its source, format, or velocity. This means marketers can access from a single location—the Data Cloud—streaming IoT data alongside web clicks and product orders. In addition, having a centralized repository also makes it very easy to enforce data security policies to limit access to sensitive information such as PII data.

Marketing KPIs such as CPC and CPL are typically consumed by several different tools and teams inside an organization. In traditional data stores, you would have to set up multiple compute instances, each with its own copy of data, to avoid resource contention. This  wastes resources, and each of these instances needs its own security and governance controls, which adds extra overhead. With Snowflake’s multi-cluster shared data architecture, you need only one copy of the data. This means no managing data silos for one-off use cases and no delays for business reporting while high-performance data science models are driving personalization in real time. 

Marketing data, especially data generated by ad generation platforms, tends to be high velocity and demands a lot of compute and storage resources. With Snowflake’s Data Cloud, you can take advantage of the cloud and tap into virtually unlimited scale. For marketers, this can mean never having to wait until the next business day to access insights. Instead, they can adjust strategies or change campaign campaigns and channel spend dynamically to improve campaign performance and reduce costs. 

When it comes to building a customer 360 profile, enriching your first-party data with third-party data sources is crucial. For example, a customer's ZIP code alone does not add a lot of value. However when it’s combined with Census, foot traffic, weather, and COVID-19 data, a ZIP code can reveal valuable insights into customer behavior and projected purchasing intent. Marketers can spend enormous time and resources searching for, acquiring, and integrating data sources using traditional methods. That’s where Snowflake Data Marketplace comes into play, giving you seamless access to hundreds of data providers in real time. 

Want to learn even more about how you can use Snowflake and Fivetran to join together and automate your marketing analytics? Sign up for a free 90-minute guided hands-on lab to learn more. Register today.