The Single Source of Truth: Your Ultimate How-To Guide for GTM Data Dominance

We are moving in a premium direction with almost zero free articles and 3 to 5 paid articles each month. The price is increasing to $15/month or $150 for the annual.

You can get a whole year for $95 right now. Lock-in that rate.

Why: Our article quality keeps improving but time is getting limited. I’m appearing on podcasts with 8-figure audiences, speaking at conferences and investing in tech start-ups. I just took over as COO of one of them. All of this is also competing with the 3 publications I write.

Wealth Systems

is where we focus on wealth building.

Life in the Singularity

is where we focus on technology.

Mastering Revenue Operations

is where we focus on building revenue engines.

---

You are about to learn my favorite work game.

I call it “RevOps Bingo” and I play it every time I am hired as a consultant to clean-up RevOps (or set one up) or whenever I invest into a company.

Here’s an example of how to play:

• Marketing insists a lead from ICP Weekly is “Marketing Qualified” but Sales says that person is already an active, paying user in a different business unit.

• Your Customer Success team gets a churn notification for a major wealth management firm, but they have no idea the firm’s product usage dropped off a cliff 45 days ago.

• The Controller calculates Customer Acquisition Cost at $12,000, while Marketing’s “first-touch” model claims it’s $7,500.

• The CEO asks a simple question: “Which of our product features are most used by firms that don’t churn?” You spend the next three days in spreadsheet hell trying to stitch Salesforce exports to a CSV from your product’s Postgres database.

If you nodded, grimaced, or hit “BINGO” you don’t have a data problem.

You have a truth problem.

Your CRM, your marketing automation platform, your sales engagement platform, your customer success platform (…starting to see the problem?), your product database, and your billing system are all telling different, conflicting stories.

As a Revenue Operations leader, you’re not just an operator. Sometimes you are a referee. But there IS a tool for that. Or rather, a solution in the form of a system.

The solution is a Single Source of Truth. Not just a buzzword, but a functional, centralized data warehouse that sits above your siloed tools. It’s the master layer where all data (prospect, customer, product, and finance) is ingested, cleaned, unified, and modeled to reflect the actual state of your business.

For a modern B2B SaaS company (like our example, “AcmeTools” which sells compliance software to professional services firms), this isn’t a “nice to have.” It’s the engine.

It’s how you stop leaks, spot opportunities, and scale efficiently.

This guide is your blueprint. We will walk through, step-by-step, how to set up this exact SSOT using a favorite of mine, Google BigQuery.

We’ll cover the strategy (Part 1), the ingestion (Part 2), the transformation (Part 3), and the activation (Part 4).

All in this SINGLE article.

See what I meant by premium direction?

This is a big project, but the payoff is bigger. Let’s build.

Part 1: The Foundation

“Strategy and Scaffolding”

You cannot build a skyscraper without a blueprint. Before you write a single line of SQL or connect a single API, you must define your strategy. This is the 80% of the work that makes the other 20% (the technical part) possible.

Define Your “Truth”

Your SSOT is being built for one reason: to answer critical business questions you currently cannot. Get your GTM leaders (Heads of Sales, Marketing, CS, and Finance) in a room and whiteboard these questions.

For a typical SaaS company, these might be:

• Marketing: “What is the true funnel velocity from an MQL at an accounting firm to a Closed-Won deal, and how does product usage from a free trial impact that velocity?”

• Sales: “Show me a list of all ‘free’ product users at wealth management firms who have invited more than 3 colleagues. I want to see this in Salesforce.”

• Customer Success: “I need an ‘at-risk’ score for every customer that combines low product usage, high support tickets, and a recent billing failure.”

• Finance: “What is our exact Net Revenue Retention segmented by the customer’s firm size (sole practitioner, under-100 employees, mid-market, enterprise)?”

These questions expose your data gaps. They give you a clear target and, most importantly, create the executive buy-in you’ll need.

Identify Your Sources and Core Entities

Now, list every system that holds a piece of the story.

• CRM: Salesforce (Accounts, Contacts, Leads, Opportunities, Tasks)

• MAP: HubSpot (Contacts, Companies, Email Engagements, Form Fills, Web Activity)

• Product: PostgreSQL Database (Users, Workspaces, Login Events, Feature Click Events)

• Billing: Stripe (Subscriptions, Invoices, Payments, Churn Events)

• Support: Zendesk (Tickets, Agents, CSAT Scores)

The goal of the SSOT is to unify these disparate sources around three core “entities” we call them:

1. The Company: This is the master entity. It unifies the Salesforce Account, the HubSpot Company, and the product Workspace. This is the firm (e.g., “Smith & Co. Accounting”).

2. The Person: This unifies the Salesforce Contact/Lead, the HubSpot Contact, and the product User. This is the individual (e.g., jane.doe@smithco.com).

3. The Event: This is a chronological stream of every touchpoint. It unifies HubSpot email_clicks, Salesforce tasks, product feature_usage, and Zendesk tickets.

Map Your Golden Identifiers

This is the most critical step in your strategic plan. How will you stitch these entities together?

You need “golden keys” that exist (or can exist) across your systems.

• For Persons: The key is almost always the email address. Suggest you use LOWER(email_address) to create a universally unique ID for every person.

• For Companies: This is harder. The best key is the website domain (e.g., smithco.com). It’s more reliable than company_name, which suffers from typos (”Smith & Co”, “Smith and Co Inc.”, “Smith Co.”). You’ll need a RevOps-led process to ensure your CRM and MAP records have this domain data. I’m also seeing a lot of companies use LinkedIn profile as identifiers.

Set Up Your Google Cloud Project

With the strategy set, let’s build the house.

1. Create a GCP Project: Go to the Google Cloud Console. Create a new project. Call it something logical, like exampleco-revops-ssot-prod.

2. Enable APIs: In the “APIs & Services” library, you must enable the following:

   • BigQuery API: The data warehouse itself.

   • BigQuery Data Transfer Service API: This allows for native data loading from sources like Google Ads, YouTube, and even some third-party SaaS apps.

   • Cloud Functions API: For running serverless data-moving scripts.

   • Cloud Scheduler API: For scheduling your data jobs.

3. Set Up IAM: Don’t give everyone “Owner” access. You should always follow the principle of least privilege.

   • revops-admins (Your team): Project Owner / BigQuery Admin. You build and manage the warehouse.

   • data-consumers (BI, Analysts): BigQuery User / BigQuery Data Viewer. They can run queries and connect BI tools.

   • service-accounts (For your tools): Create dedicated service accounts for your ELT tool (like Fivetran) and your dbt jobs. Give them “BigQuery Data Editor” and “BigQuery Job User” roles.

Your scaffolding is built. Pretty easy huh?

Now, it’s time to get the data flowing.

Part 2: The Build

“Ingestion and Loading”

It’s a common mistake is to try and transform data before you load it (known as ETL).

The modern approach, especially with a powerful warehouse like BigQuery, is ELT.

We will Extract the raw data from our sources, Load it untouched into BigQuery, and then Transform it using SQL. This is faster, more flexible, and preserves your raw data so you can always rebuild if you make a mistake.

I love to build raw tables and then layer aggregate tables (weekly amounts, changes over time, etc..) so you can see how your revenue engine is evolving (and your entire org).

Design Your BigQuery Dataset Structure

Inside your BigQuery project, you don’t just dump files. That creates cost without any return. Literal debt and tech debt.

Instead, you create datasets which are like folders or schemas.

A layered “Medallion Architecture” is a best practice you can use to start.

1. bronze_layer (or raw_data):

   • Create one dataset per source. E.g., raw_salesforce, raw_hubspot, raw_product_db, raw_stripe.

   • Rule: The data here is an exact, raw, untouched copy of the source system. No cleaning, no filtering.

2. silver_layer (or staging):

   • This is your workbench. You’ll create datasets like staging.

   • Rule: Data here is cleaned, de-duplicated, standardized (e.g., all email columns are renamed email_address), and cast to the correct types (e.g., created_at is a TIMESTAMP, not a STRING).

3. gold_layer (or production):

   • This is the SSOT itself. Create a dataset called production or analytics.

   • Rule: This dataset contains your final, beautiful, unified tables. These are the only tables your business users and BI tools should ever touch. Examples: dim_company, dim_person, fct_mrr, fct_activities.

Choose Your Ingestion Method

How do you get data from Salesforce, HubSpot, etc., into your bronze_layer datasets?

There are so many ways these days!

Option 1: The Native Connectors (Easy Mode)