Sales Pipeline Analytics data model
Vendor-neutral Kimball pipeline mart focused on forecasting, velocity, and rep activity: an accumulating-snapshot opportunity fact with milestone date keys, a daily opportunity stage snapshot for pipeline waterfalls, and a transaction-grain sales-activity fact over conformed account/contact/sales-rep/product/stage/campaign dimensions. For Salesforce-specific objects — lead conversion, OpportunityLineItem, record types, field history — use the Salesforce Sales Cloud Analytics template.
Source-agnostic sales-pipeline analytics that works across any CRM (HubSpot, Pipedrive, Microsoft Dynamics, Salesforce): the opportunity lifecycle, daily pipeline stage snapshots, and sales-rep activity over a conformed SCD2 account dimension with contact, sales-rep, product, stage, and campaign dimensions for forecasting, velocity, win/loss, and rep-productivity analytics. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 10
- tables
- 108
- columns
- 13
- relationships
- 6
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Accumulating snapshot for opportunities
A deal moves through created → qualified → proposal → closed with well-defined milestones; an accumulating snapshot yields stage lag and cycle-time metrics from a single row per opportunity.
Daily stage snapshot alongside the accumulating fact
Pipeline waterfall, snapshot-over-snapshot movement, and velocity require the value and stage of every open deal captured daily, which the accumulating fact (which overwrites) cannot provide.
Transaction fact for sales activity
Rep productivity and engagement are measured at call/email/meeting grain, independent of whether an opportunity exists yet.
SCD2 on account, contact, and sales rep
Territory realignments, account tier changes, and contact role moves must be analysed as-of, so descriptive history is versioned.
Tables in the Sales Pipeline Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (3)
fct_opportunity
Grain: one row per sales opportunity (accumulating snapshot across the deal lifecycle)
Accumulating-snapshot fact — one row per opportunity; milestone date keys and stage update as the deal progresses.
| Column | Type | Keys |
|---|---|---|
| opportunity_key | bigint | PK |
| account_key | bigint | |
| contact_key | bigint | |
| sales_rep_key | bigint | |
| campaign_key | bigint | |
| current_stage_key | bigint | |
| created_date_key | bigint | |
| qualified_date_key | bigint | |
| proposal_date_key | bigint | |
| expected_close_date_key | bigint | |
| closed_date_key | bigint | |
| opportunity_id | varchar | |
| opportunity_name | varchar | |
| opportunity_type | varchar | |
| amount | decimal | |
| weighted_amount | decimal | |
| annual_recurring_revenue | decimal | |
| win_probability | decimal | |
| is_won | boolean | |
| is_closed | boolean | |
| sales_cycle_days | smallint | |
| forecast_category | varchar |
fct_opportunity_stage_snapshot
Grain: one row per open opportunity per day (periodic snapshot)
Daily pipeline snapshot — captures stage and value of every open deal for waterfall/velocity analysis.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| account_key | bigint | |
| sales_rep_key | bigint | |
| stage_key | bigint | |
| opportunity_id | varchar | |
| amount | decimal | |
| weighted_amount | decimal | |
| win_probability | decimal | |
| days_in_current_stage | smallint | |
| days_open | smallint | |
| is_stalled | boolean |
fct_sales_activity
Grain: one row per sales activity (call, email, meeting)
Transaction-grain sales-activity fact for rep productivity and engagement analytics.
| Column | Type | Keys |
|---|---|---|
| sales_activity_key | bigint | PK |
| date_key | bigint | |
| account_key | bigint | |
| contact_key | bigint | |
| sales_rep_key | bigint | |
| activity_id | varchar | |
| activity_type | varchar | |
| activity_direction | varchar | |
| duration_minutes | smallint | |
| activity_count | smallint | |
| is_connected | boolean | |
| is_meeting_booked | boolean |
Dimension tables (6)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| month_number | smallint | |
| quarter | smallint | |
| year | smallint | |
| fiscal_quarter | varchar | |
| fiscal_year | smallint | |
| is_quarter_end | boolean | |
| is_business_day | boolean |
dim_account
Grain: one row per account per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| account_key | bigint | PK |
| account_id | varchar | BK |
| account_name | varchar | |
| industry | varchar | |
| account_type | varchar | |
| account_tier | varchar | |
| annual_revenue_band | varchar | |
| employee_band | varchar | |
| billing_country | varchar | |
| billing_state | varchar | |
| owner_region | varchar | |
| is_target_account | boolean | |
| is_active_customer | boolean |
dim_contact
Grain: one row per contact per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| contact_key | bigint | PK |
| contact_id | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| job_title | varchar | |
| seniority | varchar | |
| buyer_role | varchar | |
| is_primary_contact | boolean |
dim_sales_rep
Grain: one row per sales rep per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| sales_rep_key | bigint | PK |
| sales_rep_id | varchar | BK |
| rep_name | varchar | PII |
| rep_email | varchar | PII |
| sales_team | varchar | |
| sales_role | varchar | |
| territory | varchar | |
| region | varchar | |
| segment | varchar | |
| quota_band | varchar |
dim_product
Grain: one row per product/SKU
| Column | Type | Keys |
|---|---|---|
| product_key | bigint | PK |
| product_id | varchar | BK |
| product_name | varchar | |
| product_family | varchar | |
| product_line | varchar | |
| pricing_model | varchar | |
| list_price | decimal |
dim_campaign
Grain: one row per marketing campaign
| Column | Type | Keys |
|---|---|---|
| campaign_key | bigint | PK |
| campaign_id | varchar | BK |
| campaign_name | varchar | |
| campaign_type | varchar | |
| channel | varchar | |
| start_date | date | |
| end_date | date |
Lookup tables (1)
dim_stage
Grain: one row per opportunity sales stage
| Column | Type | Keys |
|---|---|---|
| stage_key | bigint | PK |
| stage_code | varchar | BK |
| stage_name | varchar | |
| stage_order | smallint | |
| default_win_probability | decimal | |
| is_closed | boolean | |
| is_won | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_opportunity.account_key → dim_account (many-to-one)
- fct_opportunity.contact_key → dim_contact (many-to-one)
- fct_opportunity.sales_rep_key → dim_sales_rep (many-to-one)
- fct_opportunity.campaign_key → dim_campaign (many-to-one)
- fct_opportunity.current_stage_key → dim_stage (many-to-one)
- fct_opportunity_stage_snapshot.date_key → dim_date (many-to-one)
- fct_opportunity_stage_snapshot.account_key → dim_account (many-to-one)
- fct_opportunity_stage_snapshot.sales_rep_key → dim_sales_rep (many-to-one)
- fct_opportunity_stage_snapshot.stage_key → dim_stage (many-to-one)
- fct_sales_activity.date_key → dim_date (many-to-one)
- fct_sales_activity.account_key → dim_account (many-to-one)
- fct_sales_activity.contact_key → dim_contact (many-to-one)
- fct_sales_activity.sales_rep_key → dim_sales_rep (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.