Dbt-Nexus - Data Beyond Dashboards

Where do you go to get a complete view of a customer?

Like most companies, our customer data is spread across many different tools - Gmail, Google Calendar, Stripe, our app, and more.

Previously, when a customer emailed us a support request, we’d have to manually search for them in several tools just find out how much they were paying us.

That’s why we built dbt-nexus.

What is dbt-nexus?

Dbt-nexus is a way of structuring all company data in your data warehouse so it’s operationally useful, not just good for dashboards. By that I mean it can actually help speed up customer support, close sales and reduce churn or increase upsells.

Specifically it’s a dbt (data build tool) package that let’s a data engineer quickly merge and organize any data source into a combined list of people, companies, and events that looks like this.

This view merges all our data sources (Gmail, Stripe, custom app data, etc) into a simple timeline of events that contains everything we know about the person Lewis and his company Hollywood Farms. This is what you’d want your sales, customer support and lifecycle management teams (or AI tools) to know when interacting with Lewis.

With the dbt-nexus package, a data engineer can build this from any data source in days or hours instead of months.

Let’s breakdown how and why dbt-nexus does this in the data warehouse.

Why The Data Warehouse?

Lots of tools promise a “360 degree view”, a “single source of truth”, or an “all-in-one CRM” that “scale[s] endlessly with autonomous AI agents, unified data, and CRM apps together on one integrated platform” (straight from the Salesforce homepage).

Why don’t these tools ever deliver on their promises? Because to do so you’d have to use their platform for everything, forever, and nobody does that.

For instance, to “know” everything about Lexis, I need:

• emails from Gmail (email is probably the most underrated data source for small to medium size businesses)
• meetings from Google Calendar
• app subscriptions from Shopify
• attribution & website data from Google Analytics 4
• custom data from our app’s backend

It’s not good enough to start tracking this data now with CDPs like Segment or hacky Zapier integrations. I need to pull the full history going back years for my Lewis timeline to be useful today.

The data warehouse is the only tool flexible enough to store the full history of any data source. It is, in fact, exactly what data warehouses are designed to do.

Creating your timeline in the data warehouse also has the added benefit of giving you full control over your data so you’re not locked into a single platform.

And finally, all your tools - email marketing, reporting, Salesforce, AI platforms - can pull from the warehouse, letting you share your dbt-nexus results with everyone and everything that needs good clean data about what happened.

How Dbt-Nexus Structures Data

Dbt-nexus outputs these final tables in the data warehouse.

These three tables, and their relationships to each other, have all the concepts you need to create the person and group (company) centric timeline shown above. They are also source agnostic - you can add any data source to these tables with dbt-nexus.

Let’s review what each of these tables/concepts contains using Lewis as an example.

Events

In dbt-nexus, events are the what happened. They are the timeline in the image below. You can roughly think about it as a log of facts.

In Lewis’s case we can see emails he’s exchanged with team members from Gmail, invoices he’s paid from Stripe, meetings from Google Calendar, and more all on the same timeline.

With dbt-nexus, you transform all data from all sources into the same core event structure, or schema.

For instance, Gmail, Stripe, and Google Calendar data are all unioned into one events table with the following schema.

Why structure everything as an event?

A single events table is the most natural way to answer questions about what happened across any data source.

For instance, suppose I want to know if Lewis is consistently late on paying his invoices.

Stripe has an invoices table that contains the current status (paid or unpaid) of invoices. But we don’t just need to know the current status of his invoices, but also when he paid his invoices.

So, instead of merely replicating the Stripe invoices table into our warehouse, we breakdown a single invoice into multiple events, like invoice created and invoice paid .

Now we can easily answer our question. In the past three months, Lewis has been late paying his invoice twice.

Furthermore, reformatting all sources as events into a single table makes it easy to answer questions that require data from multiple sources. For instance, suppose I wanted to know if we had to send Lewis an email reminder about his overdue invoice last month.

With Stripe and Gmail data in the same event log I can answer this question with a simple glance. I don’t have to understand the intricacies of the Stripe and Gmail tables or, more likely, search for Lewis in both Stripe and Gmail.

Fundamentally, analytics data like dbt-nexus is about when things happened. That’s what a single events table provides. When combined with the person and group concepts below, anyone on your team (or an AI) can quickly and easily get the data they need to support your customers.

Persons

The persons table merges every trait - name, email addresses, title, etc - we know about Lewis from any data sources into a single row in the persons table.

We use the term person here deliberately. A person could be a lead, a customer, an investor, a potential partner, an employee, whatever is relevant to your business. Person reflects the core fact that these are all people that have traits and do things (events).

Identity Resolution

The “merge” mentioned above is actually a graph-based identity resolution algorithm. We’ll dive into its mechanics in a later post, but the important thing to note is that it can take any piece of identifying information - email address, phone number, app user id - and use it to stitch data across sources into a single person.

We have to merge using this algorithm because there’s no single identifier we can reference across all sources. For instance:

1. I have Lewis’s phone number and user id in my app’s data.
2. I have his email address and phone number from a Google calendar meeting.
3. I have his email address in my Gmail exchanges with him.

Identity resolution stitches the user id, phone number and email address into a single person. Now, anywhere I see one of those traits in my data, I know it’s Lewis.

In dbt-nexus, you can define any identifying information you want. Whatever makes sense for your business and data. Email addresses and phone numbers are common, user ids are specific. But it doesn’t matter because dbt-nexus is source agnostic.

Groups

Conceptually, groups are simply collections of people. They can be companies, households, neighborhoods, whatever make sense for you.

Like persons, groups have traits (website domains, names, addresses, etc) and take actions (pay invoices, install apps, file customer support tickets). For instance, Hollywood Farms is a Shopify store that installed our Google Analytics 4 app on their site.

In dbt-nexus, the groups table is derived the exact same way as persons, using identifying information and graph based identity resolution.

For instance, Hollywood Farms has several pieces of identifying information in our setup:

• domain - hollywoodfarms.com
• Shopify ID - 12345
• My shopify domain - hollywood-farms.myshopify.com

Any time dbt-nexus sees any of these traits in our data it stitches those records to the Hollywood Farms group.

For many businesses, groups may be more relevant than persons. For instance, in our case it’s actually Shopify stores that install our app and pay us, not individual users. For us, understanding how groups interact with our products is more important than people.

Memberships

Lewis is a member of the Hollywood Farms group. In his case, he’s an employee, specifically the director of e-commerce. But Lewis could be a consultant and thus a member of several companies. That’s why groups and people have a “many to many” relationship. Groups can have many people and people can belong to many groups.

Understanding how people and groups relate to each other is critical to understanding what happened. It makes dbt-nexus a headless CRM, something we’ll talk about in another post.

Sources of Truth

Any business realistically uses multiple tools to acquire and service customers. There’s never a single source of truth. Instead, to get a realistic picture of what happened you need to pull the full history from all of these sources of truth and merge them into some useful, human (or AI) readable format like the events timeline.

dbt-nexus accomplishes this by being source agnostic - it doesn’t care about the source of data. Instead, it cares about the structure of data - ie the events, persons and groups structure described above. This makes adding additional sources much faster and more flexible than off-the-shelf tools or a traditional data warehouse structure.

Furthermore, since dbt-nexus lives in the data warehouse and is source agnostic, you can use any tool you like to copy data from your sources of truth into your warehouse. That process is called Extract Transform and Load (ETL). We’ll examine the different options in a later post, but the key takeaway here is that dbt-nexus, unlike most tools, doesn’t care where data comes from or how it gets into your warehouse.

To add a new source, all you have to do is transform its data from the raw ETL structure into the correct nexus structure. Dbt-nexus handles the rest, stitching all your sources into a single events timeline.

We’ll dive into how to do this in another post, but suffice to say, it takes a data engineer hours instead of weeks or months to add new sources. For instance, we added Gmail, Google Calendar, and Stripe to our instance with about an hour’s worth of work each.

And, if we start using a new tool, like Notion for task tracking, we can add that to our timeline almost immediately. That gives us the operationally flexibility to try out new tools and pick the right one for the job, without having to consider lengthy integrations.

dbt-nexus is designed to fit with the way companies actually operate. It doesn’t assume some idealized end-state and force company-wide operational changes before its useful. Instead, it lets you add data from any source to your instance quickly and iteratively with any specific customizations you require.

Instead of requiring a single source of truth, it give you the flexibility and power of combining any sources of truth into one intuitive structure.

How We Use Dbt-Nexus

We built dbt-nexus as a way to quickly add new data sources to our data warehouse and output the consistent person and group centric timeline discussed above without having to change the existing tools we already liked using.

Here are some examples of how we use it.

Timeline App

We built a lightweight app that reads directly from the dbt-nexus tables in the warehouse and shows events by person, group and data source. The screenshots of the timelines are taken from this app. We use that in both sales and support to understand what happened to a customer and provide better service.

Daily Updates

We get an email every day that tells us the most important events that happened at the company.

Up to Date Email List

We push the persons table into our email marketing tool, customer.io, so we always have an up to date email list for all our customers.

Abandon Setup Notification

We use the events to automatically notify us of incomplete setups so we can reach out to those customers personally and get them onboarded.

Metrics and Dashboards

We built all our metrics and dashboards on top of the nexus tables. Once we had a single table with everything that happened in a consistent structure, building metrics like revenue and sign-ups was easy, especially with a semantic layer like cube cloud.

AI Integrations

We’re now working on integrating AI with the nexus data so we can provide even faster, more personalized, and more automated marketing, sales, and support.

Data for Your Company Operating System

As you can see, dbt-nexus lets us actually use our data in our operations instead of merely building dashboards (though it does that too). In fact, it has become the central piece of our company’s operating system.

We hope you’ll find it useful for your company as well.

Reach out to us here if you want help building out your dbt-nexus instance.