Joining is a method of combining data from various tables based on common fields. The result is a new virtual table that extends horizontally with the added columns. Joining data allows analysts to access multiple data sets with a single query, thereby gaining insights that would otherwise be inaccessible.
Given the large amount of applications and other sources businesses use, there are endless ways to join data for holistic insights, from building marketing attribution models to matching booked revenue with payments received. Below, two Fivetran customers weigh in on the ways they’re joining data.
Scribe Media Joins Data for Job Costing and Cash Forecasting
Scribe Media is a publishing company that helps aspiring authors write and publish books. Services vary depending on an individuals’ needs and include publishing, writing guidance and ghostwriting. Cody West, Analytics Engineering Manager, develops Scribe’s data stack, which includes centralizing, storing, transforming and ultimately visualizing the data.
Scribe divides the process of writing and publishing a book into a series of milestones, each of which has its own associated costs. By joining data from multiple sources, Scribe can understand all elements of job cost. “We want to know how much we are spending on each project and how those expenses are broken out,” explains West. “We use freelancers for cover design, editing, proofreading, and other services so we need to understand the costs associated with using a freelancer versus hiring someone in-house. Understanding this allows us to manage our margins really well.”
To get to these numbers, Scribe needs to join data from Salesforce, Xero, and Teamwork in Google BigQuery. West offers the following example: “Let’s say that we were paid $40,000 for a given project. That value is recorded in Salesforce as signed revenue. We use Xero to see how much of the $40,000 has been paid and how much is still outstanding. Xero data also tells us how much we're paying freelancers and we aggregate the payment data with billing data stored in Teamwork. Once we join the data together, we are able to subtract costs from revenue to visualize our margins for each project.”
West also joins data from multiple sources to answer questions around marketing attribution and cash forecasting:
It’s important for us to understand revenue earned per channel. If we earned a million dollars, we would need to determine what percent of that million dollars can be attributed back to our Facebook Ads, organic search, affiliate programs and so on.
With a clearer picture of marketing attribution, Scribe can double down on high performing channels and positively impact growth. This information can help the leadership team accurately understand cash flow and optimize spending decisions. “We bill people on eight to 12 month payment schedules, so we need to manage that incoming cash every month and then forecast cash flow eight to 12 months out,” West shares. “We also need to forecast our leads and how many will turn into sales. We're paying a lot of attention to this because the forecasts are essentially our whole business. If you know that you have a lot more cash coming in the next three to four months then maybe you do more hiring to quickly scale.”
When it comes to reporting for the executive team, West has built a dashboard that leverages almost every data source the company uses:
The executive dashboard is broken out in terms of KPIs by department and probably has the most datasets: Salesforce data for revenue, Xero data for costs, Google analytics for website traffic, Teamwork data for project efficiency and more.
West recognizes that the modern data stack is critical for joining these datasets together with ease to deliver reliable insights to stakeholders:
The modern data stack allows analysts like me to load data directly into a warehouse without relying on developers to manage API changes and transformations. You can have a one person analytics team and still be super productive.
Grocery TV Joins Data for Executive Reporting and Revenue Sharing
Dallas Griffin, Director of Data and Analytics at Grocery TV, defines and implements the data strategy for the entire organization, including building internal and external reporting and dashboards and developing and implementing AI and automation solutions.
Grocery TV is the largest and fastest-growing digital advertising network in grocery stores. Consequently, ad sales are the business’ primary source of revenue. Getting a comprehensive picture of revenue requires joining third party advertising data from its Postgres production database with direct sales data from HubSpot. Griffin explain that both are critical to revenue reporting:
With Fivetran, we're able to create a consolidated view of our total revenue in Looker, which bubbles into a dashboard used by our leadership team. This process is really important for the business to understand performance and to ensure that we hit our goals.
As part of its business model, Grocery TV shares advertising revenue with the grocery chains that host the displays. So the business must track when and where advertisements were shown to in-store shoppers. Griffin explains the process:
We match every ad payment to a contract and to various campaigns that have run in our stores. By centralizing and joining this data we can determine that a payment from Stripe corresponds to a deal in HubSpot, which is associated with certain campaigns. If these campaigns ran at retailers A, B and C, retailer A may get 60% branded distribution while B and C both get 20%, for instance.
Griffin acknowledges the difficulty in quantifying the ROI of data tools to a company like Grocery TV, but was able to break up the value into near and long term categories.
Fivetran allowed us to accomplish our goals without hiring a data engineer for at least six months, which is nearly $60,000 in savings. But beyond that is the opportunity savings that you gain by eliminating the need to build in-house. The automation makes projects that would have been time and resource-intensive in the past much easier.
Running a successful data analytics strategy almost always involves joining data from multiple sources for a cohesive view of your business. Whether you are looking to measure lifetime customer value, understand churn, aggregate revenue, or widen your margins, Fivetran is the first step in integrating the data from the sources your business relies on.
If you’re ready to see the power of automated data integration for yourself, sign up for a Fivetran free trial today.
.png)
.svg)
.svg)
.svg)