dbt + BigQuery for Salesforce: Build a Complete Campaign Funnel Analytics Layer (Free Package)

Raw Salesforce data can be messy and difficult to work with. Data silos and time-consuming manual reporting often prevent teams from building a clear view of their Salesforce campaign performance. 

With the combined power of Windsor.ai, dbt, and BigQuery, you can free your team from hours of cleaning and joining data, so they can focus on insights across the entire customer journey.

As a leader in SaaS data integration, Windsor.ai is excited to introduce a free Salesforce dbt package purpose-built for campaign funnel analysis.

Explore Windsor.ai’s dbt-BigQuery Package for Salesforce: https://github.com/windsor-ai/dbt-bigquery-package-for-salesforce. 

This package helps you bring standardized Salesforce data directly into BigQuery, transforming raw data into an analytics‑ready campaign funnel reporting layer.

Common challenges with raw Salesforce data

If you have ever worked with Salesforce data, you know it’s quite disorganized and not ready for analytics by default. The structure is complex. The objects are scattered. And the relationships are not always clear. 

As a result, two recurring challenges emerge: data silos and manual reporting.

Data silo 

Salesforce stores information in many different objects. Campaigns, leads, contacts, and opportunities all live separately. In the app, they connect. But pulling them together outside takes effort.

Consider the following real case: your marketing team may run campaigns in Salesforce. At the same time, your sales team tracks revenue in opportunities. Without a proper integration layer, these datasets remain isolated from one another, making it hard to answer the simple question:

Which campaigns are driving the most revenue?

Time-consuming manual reporting 

Even if you export raw Salesforce data into BigQuery, it’s not analytics-ready. You still have to connect multiple objects and clean fields before analysis. Most teams end up running several reports, exporting them into spreadsheets, and manually stitching them together.

Here comes an example: A B2B company is trying to measure lead-to-opportunity conversions. Analysts pulled campaign_member data every week. They joined it with opportunities in Excel and built funnel views by hand. But by the time reports were ready, the data was already outdated.

The problem is that a manual process is slow. It drains your analysts. And it does not scale.

As a data engineer, you want to focus on building reliable pipelines and delivering fresh insights. But raw Salesforce data often gets in the way. Without a modern ELT approach, you end up spending more time wrangling data than actually analyzing it.

The solution: using automated ELT with a purpose-built dbt package

By combining Windsor.ai for data ingestion, dbt for transformation, and BigQuery for analytics, you can build a reliable and scalable campaign funnel analytics layer for your Salesforce data in a matter of minutes, not weeks. 

Let’s consider the role of every tool in this modernized approach.

Why Windsor.ai for data ingestion

As a data engineer, you know that high-volume data ingestion is the first hurdle. Salesforce APIs are complex. Rate limits, schema changes, and nested JSON responses make data extraction a challenging task. Windsor.ai handles all this with a pre-built connector for Salesforce that automatically streams your data to any destination, including BigQuery.

Instead of writing custom scripts or maintaining brittle pipelines, Windsor.ai automates ingestion into BigQuery, handling schema updates and keeping data fresh at your preferred schedule. This frees you from chasing API quirks, allowing you to focus on data modeling.

Real use case: Consider a global e-commerce brand. Their Salesforce instance contains millions of campaign_member records tied to leads and opportunities. Pulling this data manually would take days. With Windsor.ai, they can ingest large-scale datasets daily into BigQuery with minimal effort. Now their engineers can skip data wrangling and move straight into transformation.

Why dbt for data transformation

Once data is loaded into BigQuery, it remains raw. Objects like campaigns, leads, and opportunities need to be cleaned, joined, and standardized. Doing this manually in SQL scripts becomes unmanageable at scale. That’s where dbt comes in.

dbt offers version-controlled SQL transformations that are modular, testable, and reusable. With the purpose-built Salesforce dbt package from Windsor.ai, you get pre-built models that standardize source tables (stg_), create intermediate joins (int_), and deliver analytics-ready marts.

Our package also includes dbt tests for data quality. You can validate that clicks never outnumber impressions and that spend values are always non-negative. Instead of reinventing the wheel, you leverage the out-of-the-box package designed for accurate and error-free Salesforce funnel analytics.

Real use case: Imagine your team is building a Salesforce funnel model from scratch. You would spend weeks designing joins, writing macros, and testing conversions. With the Windsor’s dbt package, you get a solid foundation and customize only where needed. That brings great engineering efficiency.

Why BigQuery for analytics

Ultimately, you need a data warehouse that can scale seamlessly along with your increasing data volumes. Salesforce data grows quickly, especially if you track campaign performance across multiple regions or product lines. BigQuery is built to address this challenge.

It offers serverless, fully managed analytics with near-infinite scalability. Queries that take hours in a traditional database run in seconds in BigQuery. Since Windsor.ai streams Salesforce data directly into BigQuery, you avoid the need for complex ELT middle layers.

BigQuery also integrates seamlessly with BI tools like Looker Studio, Tableau, or Power BI. This means your analysts can further build dashboards on top of the campaign_funnel_summary table without waiting for manual exports and refreshes.

Real use case: An enterprise B2B company can use this setup to track campaign ROI in near real-time. Marketing can see which campaigns generated the highest opportunity conversions, while sales leaders can align pipeline forecasts with actual campaign performance. This visibility becomes possible and so simple thanks to the ingestion, transformation, and analytics layers being tightly connected.

With the modern ELT approach powered by Windsor.ai, dbt, and BigQuery, you get a streamlined, automated workflow and trustworthy funnel analytics that scale with your business.

Introducing the Salesforce dbt package from Windsor.ai

Our production-ready framework transforms raw Salesforce data into clean, analytics-ready tables in BigQuery. 

The package uses a modern ELT stack. It relies on Windsor.ai for data ingestion, dbt for transformation, and BigQuery as the scalable data warehouse.

Why do you need to use this package?

A pre-built Salesforce dbt package helps overcome the platform’s inherent complexities with the well-modeled campaign funnel. The funnel goes from campaign touchpoints to lead conversions to closed-won opportunities and links disparate Salesforce objects (Campaigns, Campaign Members, Leads, Contacts, and Opportunities).

The package boosts engineering efficiency. You don’t have to spend weeks building a transformation layer from scratch. It removes the need for writing complex joins, creating macros, and implementing data quality tests.

Instead, you can use these reusable macros that handle common tasks:

• clean_boolean() – Convert string booleans to proper boolean type
• clean_email() – Standardize and validate email addresses
• safe_date_parse() – Robust date parsing with error handling
• calculate_days_between() – Date difference calculations
• clean_currency() – Numeric/currency field validation
• clean_phone() – Phone number standardization

Overall, the package is a well-documented and battle-tested library for a Salesforce business domain, helping you accelerate time to insight and value.

What is included in this package? 

The package uses dbt’s recommended three-layer architecture: staging, intermediate, and marts.

Staging models (stg_salesforce__*)

The staging layer is the first step in the transformation process. It takes the raw Salesforce data from Windsor.ai and prepares it for analysis by cleaning and standardizing. 

This involves safely casting data types. For instance, it converts string values like ‘True’ or ‘False’ into booleans. 

It also handles null values and renames cryptic Salesforce fields to clear, consistent names. For instance, OwnerId becomes owner_id. 

The package utilizes the following core Salesforce tables: campaigns, leads, contacts, campaign_members, and opportunities. So you have to ingest these tables into BigQuery first.

Intermediate models (int_salesforce__*)

This layer holds the business logic. It joins the cleaned staging models, creating a cohesive data model. The key transformations to track the lead journey happen here. It allows you to calculate the time it takes for a lead to convert into a contact. 

It also builds the complete campaign funnel by joining campaign members to their leads and opportunities. 

Finally, it pre-calculates performance metrics, such as conversion rates and campaign costs.

Marts models (salesforce__*)

The marts layer contains the final, analytics-ready tables for direct use with BI tools like Looker Studio, Tableau, or Power BI. 

The package provides two key marts:

• salesforce__campaign_lead_funnel – Complete funnel analysis with conversion metrics
• salesforce__campaign_attribution_summary – Multi-touch attribution reporting

Setup guide: how to use this dbt package

To get started, first, you have to connect your Salesforce data to BigQuery using Windsor.ai (this open-source package is built to integrate with it). Then clone the package, configure variables for your specific use case, and run the installation to set up dependencies, build the models, and validate data quality. The dbt package transforms your data into ready‑to‑use tables. Running dbt commands ensures clean, tested, and analytics‑ready campaign metrics.

Integrating Salesforce data to BigQuery via Windsor.ai

Before using this dbt package, you have to integrate Salesforce data into BigQuery using the Windsor.ai connector to ensure the schema matches the expected format:

1. Sign up for Windsor.ai’s free trial.
2. Connect your Salesforce account(s).
3. Choose BigQuery as a data destination.
4. Create and run a destination task for every required table by selecting specific fields. 

To ensure this dbt package works smoothly, you must sync all required Salesforce fields from Windsor to BigQuery.

In Windsor, create separate destination tasks using these recommended presets:

• Campaigns
• Leads
• Contacts
• Campaign_members
• Opportunities

Use Windsor’s predefined “Report Presets” to auto-select all fields needed for dbt model compatibility. Manual changes to fields may break model assumptions.

✅ Required BigQuery tables

These tables must be created with the field structure defined in the sources.yml file:

• Campaigns

Use the Campaign preset from the Report Presets dropdown (it retrieves campaign-level info, such as names, types, status, and budgets).

Key fields: id, name, type, status, start_date, end_date, budgeted_cost, actual_cost, is_active.

• Leads

Use the Lead preset from the Report Presets dropdown (it retrieves lead records with contact information and source tracking).

Key fields: id, email, first_name, last_name, company, status, lead_source, created_date, converted_date, converted_contact_id, converted_opportunity_id.

• Contacts

Use the Contact preset from the Report Presets dropdown (it retrieves contact master data with account relationships).

Key fields: id, email, first_name, last_name, account_id, created_date, lead_source.

• Campaign_members

Use the Campaingmember preset from the Report Presets dropdown (to retrieve campaign membership associations linking campaigns to leads/contacts).

Key fields: id, campaign_id, lead_id, contact_id, status, first_responded_date, created_date.

• Opportunities

Use the Opportunity preset from the Report Presets dropdown (to retrieve sales opportunity pipeline data with campaign attribution).

Key fields: id, name, account_id, amount, stage_name, close_date, campaign_id, created_date, is_closed, is_won.

Windsor.ai will stream your Salesforce data to your BigQuery project in minutes. After verifying that the data is present, you’re ready to start transforming it using this dbt package.

For more details, visit our dedicated integration guide: How to Send Salesforce Data to BigQuery.

Package installation and configuration

Requirements:

• dbt Core >= 1.0.0 (tested up to 1.8.x)
• Python >= 3.7 (required for dbt Core)

Step 1: Install the package

Add to your packages.yml:

packages:
- git: "https://github.com/windsor-ai/dbt-bigquery-package-for-salesforce.git"
revision: main

Step 2: Install dependencies

dbt deps

Step 3: Configure source tables

Define your Salesforce source tables in models/sources.yml:

version: 2

sources:

- name: salesforce
description: "Raw Salesforce data tables"
tables:
    - name: campaigns
description: "Salesforce campaigns data"
    - name: leads
description: "Salesforce leads data"
    - name: contacts
description: "Salesforce contacts data"
    - name: campaign_members
description: "Campaign member associations"
    - name: opportunities
description: "Sales opportunities data"

Step 4: Run the models

# Run all models
dbt run

# Run specific layers
dbt run --select +stg_salesforce    \# Staging only
dbt run --select +int_salesforce    \# Staging + Intermediate
dbt run --select +salesforce        \# All models

# Run tests
dbt test

Ensuring data quality with dbt tests

The package includes pre-built dbt tests for:

• Uniqueness: Primary key constraints on all models
• Referential integrity: Foreign key relationships between tables
• Data validation: Email format, date ranges, picklist values
• Business logic: Conversion funnel consistency checks
• Completeness: Required field validation

You can run them with:

dbt test
dbt test --select stg_salesforce  \# Test staging models only

Package customization and extensibility

If your Salesforce org has custom objects or fields, you can easily extend and customize this package by adding new models or custom metrics. The dbt_project.yml file allows you to override variables and map custom fields. 

vars:

# Campaign field mappings
campaigns_id_field: 'campaign_id'
campaigns_name_field: 'campaign_name'
campaigns_type_field: 'campaign_type'
campaigns_status_field: 'campaign_status'
campaigns_actual_cost_field: 'campaign_actual_cost'
campaigns_budgeted_cost_field: 'campaign_budgeted_cost'

# Lead field mappings
leads_id_field: 'lead_id'
leads_email_field: 'lead_email'
leads_status_field: 'lead_status'
leads_source_field: 'lead_lead_source'
leads_converted_date_field: 'lead_converted_date'
leads_converted_contact_id_field: 'lead_converted_contact_id'

# Contact field mappings  
contacts_id_field: 'contact_id'
contacts_email_field: 'contact_email'
contacts_account_id_field: 'contact_accountid'
contacts_created_date_field: 'contact_createddate'

# Campaign Member field mappings
campaign_members_id_field: 'campaignmember_id'
campaign_members_campaign_id_field: 'campaignmember_campaign_id'
campaign_members_lead_id_field: 'campaignmember_lead_id'
campaign_members_contact_id_field: 'campaignmember_contact_id'

# Opportunity field mappings
opportunities_id_field: 'opportunity_id'
opportunities_name_field: 'opportunity_name'
opportunities_amount_field: 'opportunity_amount'
opportunities_stage_name_field: 'opportunity_stage_name'
opportunities_campaign_id_field: 'opportunity_campaign_id'

You can also configure picklist values. This ensures the package is compatible with your Salesforce schema. 

vars:

salesforce_campaign_types:
- 'Email'
- 'Webinar'
- 'Trade Show'
- 'Social Media'

salesforce_lead_statuses:
- 'Open - Not Contacted'
- 'Working - Contacted'
- 'Qualified'
- 'Unqualified'

Conclusion

A well-planned ELT approach is the key to unlocking true value from Salesforce data. The combination of Windsor.ai, dbt, and BigQuery creates a powerful analytics workflow, eliminating manual exports and spreadsheets. This automated ELT setup streamlines data ingestion, standardizes complex relationships, and delivers analytics-ready data at scale. 

🚀 Ready to streamline your Salesforce campaign funnel analytics in BigQuery?

Get started with Windsor.ai with a 30-day free trial to bring your Salesforce data into your BigQuery project, transform it with dbt, and unlock powerful insights.

FAQs 

What is a dbt package, and why do I need one for Salesforce?

A dbt package is a collection of pre-built models and macros. You need it for Salesforce to transform and standardize complex raw data, saving you significant development time and accelerating time to insight.

Does this package replace my existing ETL/ELT tool?

No, this package is a transformation layer. It works alongside your existing ELT tool like Windsor.ai to clean and structure data after it’s been loaded into BigQuery.

What Salesforce objects are included in this package?

This package includes core Salesforce objects, such as campaigns, leads, contacts, campaign members, and opportunities, to build a comprehensive view of the full funnel.

Can I connect my other data sources with this setup?

Yes, you can integrate other sources, such as Google Analytics or paid media data via Windsor.ai, and add them to your dbt project for advanced modeling alongside your Salesforce data.

What are the main benefits of using Windsor + dbt + BigQuery for Salesforce data?

Combining Windsor’s data ingestion with dbt’s data transformation and BigQuery’s scalable analytics gives you a fast, reliable, and fully automated workflow for trustworthy Salesforce campaign insights. 

Is it required to use Windsor.ai as a data integration layer to utilize this package?

Yes, the package is specifically designed to work with Windsor.ai’s Salesforce schema. While you could modify it, using Windsor.ai ensures a seamless and fully compatible setup.