Tutorial 2: Building a Multi-Source Sales Analytics Pipeline

Create a comprehensive sales analytics platform by integrating Salesforce CRM, Outreach sales engagement, and Google Calendar data into a unified data warehouse.

Level: Intermediate Time: 2-3 hours Cost: Free (using trial accounts)

---

What You'll Build

A production-grade sales analytics pipeline that combines:

• Salesforce: Opportunities, accounts, contacts, activities
• Outreach: Email sequences, touches, cadences
• Google Calendar: Meeting data for sales reps

End Result: Unified sales performance dashboard with:

• Pipeline health metrics
• Rep activity tracking across all tools
• Sales cycle velocity analysis
• Meeting-to-close conversion rates
• Attribution across touchpoints

---

Learning Objectives

• Design multi-source data architectures
• Handle schema drift across connectors
• Implement incremental loading for large datasets
• Build complex joins across different source systems
• Optimize for cost (MAR management)
• Create production-quality dbt models
• Implement data quality tests
• Build sales-specific analytics models

---

Prerequisites

Required - From Tutorial 1

• ✅ Completed Tutorial 1: Your First Fivetran Pipeline
• ✅ Snowflake account with Fivetran destination configured
• ✅ dbt installed and working
• ✅ Understanding of ELT basics

Required - New Accounts

• Salesforce: Free Developer Edition
• Outreach: Free trial or use Salesforce Activities only
• Google Calendar: Gmail account (free)

Required Skills

• Intermediate SQL (joins, window functions, CTEs)
• Basic understanding of sales processes
• Familiarity with dbt (from Tutorial 1)

---

Architecture Overview

---

Part 1: Set Up Salesforce Connector (30 minutes)

Step 1: Prepare Salesforce Developer Org

1. Sign up for Salesforce Developer Edition
2. Verify email and log in
3. Complete Salesforce setup wizard

Step 2: Generate Sample Data (Optional but Recommended)

Salesforce Developer orgs come with some sample data, but let's add more:

Option A: Use Data Import Wizard (Manual)

1. Setup → Data Import Wizard
2. Import Accounts, Contacts, Opportunities

Option B: Use Salesforce CLI (Faster)

Option C: Create Manually

1. Go to Accounts tab → New
2. Create 5-10 sample accounts
3. For each account, create 2-3 contacts
4. For each account, create 1-3 opportunities
5. Log some activities (tasks, events, calls)

Step 3: Create Salesforce Connected App for Fivetran

Fivetran uses OAuth to connect to Salesforce:

1. In Salesforce: Setup → App Manager
2. Click New Connected App
3. Fill in:
4. Save and note the Consumer Key (you won't need it, Fivetran handles this)

Step 4: Add Salesforce Connector in Fivetran

1. In Fivetran: Connectors → + Add connector
2. Search for "Salesforce"
3. Select Salesforce
4. Click Authorize
5. Log in to Salesforce when prompted
6. Grant Fivetran access
7. Configure connector:

Step 5: Select Salesforce Objects

Essential Objects (check these):

Objects to Exclude (uncheck to save MAR):

Step 6: Configure Column Exclusions

Exclude unused columns to reduce MAR:

8. Save & Test → Start Initial Sync

Step 7: Monitor Salesforce Sync

Initial sync takes 10-30 minutes depending on data volume.

---

Part 2: Set Up Outreach Connector (20 minutes)

Note: If you don't have Outreach access, skip this section and use Salesforce Activities (Task/Event) instead.

Step 1: Set Up Outreach Trial

1. Sign up at outreach.io/trial
2. Complete onboarding
3. Create a few sequences and prospect touches (or use existing data)

Step 2: Add Outreach Connector

1. In Fivetran: Connectors → + Add connector
2. Search for "Outreach"
3. Click Authorize
4. Log in to Outreach
5. Grant permissions
6. Configure:

Step 3: Select Outreach Objects

4. Save & Start Sync

---

Part 3: Set Up Google Calendar Connector (15 minutes)

Step 1: Add Google Calendar Connector

1. In Fivetran: Connectors → + Add connector
2. Search for "Google Calendar"
3. Click Authorize
4. Log in with Google account
5. Grant calendar read permissions

Step 2: Configure Calendar Sync

Step 3: Verify Sync

---

Part 4: Build Multi-Source dbt Models (60 minutes)

Step 1: Update dbt Sources Configuration

Edit models/sources.yml:

Step 2: Create Staging Models for Salesforce

Create models/staging/salesforce/stg_salesforce__accounts.sql:

Create models/staging/salesforce/stg_salesforce__opportunities.sql:

Create models/staging/salesforce/stg_salesforce__users.sql:

Create models/staging/salesforce/stg_salesforce__activities.sql:

Step 3: Create Intermediate Models

Create models/intermediate/int_opportunity_metrics.sql:

Create models/intermediate/int_sales_rep_activity.sql:

Step 4: Create Mart Models

Create models/marts/sales_pipeline.sql:

Create models/marts/sales_rep_performance.sql:

Create models/marts/pipeline_velocity.sql:

Step 5: Add Data Quality Tests

Create models/marts/schema.yml:

Step 6: Run dbt Pipeline

---

Part 5: Optimize for Production (20 minutes)

Step 1: Optimize MAR Usage

Optimization Actions:

1. Exclude unused Salesforce objects
2. Exclude unnecessary columns
3. Use incremental dbt models (we already did this!)
4. Reduce sync frequency for less critical data

Step 2: Set Up Monitoring Dashboard

Step 3: Schedule dbt Runs

Option A: Fivetran Transformations (Easiest)

1. In Fivetran: Transformations → Create Transformation
2. Connect your dbt Git repo
3. Set to run after Salesforce connector syncs
4. Fivetran will auto-run dbt after each sync

Option B: GitHub Actions (More control)

---

Part 6: Query Your Multi-Source Pipeline (10 minutes)

Example Queries

Sales Pipeline Overview:

Top Performing Reps:

Pipeline Health:

---

Congratulations! 🎉

You've built a production-grade multi-source sales analytics pipeline!

What You Accomplished

✅ Integrated 3 data sources (Salesforce, Outreach/Calendar) ✅ Built incremental loading for performance ✅ Created complex multi-table joins ✅ Implemented data quality tests ✅ Built sales-specific analytics models ✅ Optimized for cost (MAR management) ✅ Set up monitoring and freshness checks ✅ Created production-ready transformations

Your Architecture

---

Next Steps

1. Connect to BI Tool: Tableau, Looker, Mode for visualizations
2. Add More Sources: Marketing (Google Ads), Support (Zendesk)
3. Advanced Analytics: Cohort analysis, churn prediction, forecasting
4. Alerts: Set up Slack alerts for at-risk deals
5. Expand: Build more marts (marketing attribution, customer 360)

---

Summary

Time Invested: ~3 hours Value Created: Enterprise-grade sales analytics platform Skills Gained: Multi-source architecture, advanced dbt, production optimization

Production Ready: ✅ This pipeline is ready for real business use!

Questions? Contact me for consulting on production implementations.