Real-World Examples

This page provides complete, end-to-end Account model configurations for common setups. Each example includes the full query, the property mapping table, and an explanation of what's being configured.

Use these as starting points and adapt them to your data schema.

---

Example 1: Salesforce as Sole Source

Scenario: Your organization uses Salesforce as the system of record. Accounts, opportunities, and owner assignments all live in Salesforce. You want to pull customer accounts with aggregated ARR from closed-won opportunities, set parent-child relationships, track churn, and auto-assign CSMs.

Connection

• Type: Salesforce
• Refresh: Daily (24h)

Query

-- SOQL_START: accounts (Id, Name, Website, CreatedDate, ParentId, AnnualRevenue, Customer_Status__c, Churn_Date__c, Type)
SELECT
    Id, Name, Website, CreatedDate, ParentId,
    AnnualRevenue, Customer_Status__c, Churn_Date__c, Type
FROM Account
WHERE IsDeleted = false AND Type != 'Prospect'
-- SOQL_END

-- SOQL_START: opps (Id, AccountId, Amount, CloseDate, StageName)
SELECT Id, AccountId, Amount, CloseDate, StageName
FROM Opportunity
WHERE StageName = 'Closed Won' AND IsDeleted = false
-- SOQL_END

-- SOQL_START: owners (Id, Email, Name)
SELECT Id, Email, Name FROM User
-- SOQL_END

-- SOQL_START: account_owners (AccountId, OwnerId, CSM_Email__c, CSM_Name__c)
SELECT Id AS AccountId, OwnerId, CSM_Email__c, CSM_Name__c
FROM Account
WHERE IsDeleted = false AND Type != 'Prospect'
-- SOQL_END

SELECT
    a.Id AS account_id,
    a.Name AS name,
    a.Website AS domain,
    a.CreatedDate AS created_at,
    a.ParentId AS parent_account_id,
    a.Id AS sfdc_account_id,

    -- ARR: use AnnualRevenue if set, otherwise sum opportunities
    COALESCE(
        NULLIF(a.AnnualRevenue, 0),
        opp_agg.total_amount
    ) AS amount,

    -- Expiry: latest opportunity close date
    opp_agg.latest_close AS expires_at,

    -- Churn
    CASE WHEN a.Customer_Status__c = 'Churned' THEN 1 ELSE 0 END AS is_churned,
    a.Churn_Date__c AS churned_at,

    -- CSM assignment
    ao.CSM_Email__c AS csm_email,
    ao.CSM_Name__c AS csm_name,
    ow.Email AS ae_email,
    ow.Name AS ae_name,

    -- Custom properties
    a.Type AS account_type

FROM accounts a

LEFT JOIN (
    SELECT
        AccountId,
        SUM(Amount) AS total_amount,
        MAX(CloseDate) AS latest_close
    FROM opps
    GROUP BY AccountId
) opp_agg ON a.Id = opp_agg.AccountId

LEFT JOIN account_owners ao ON a.Id = ao.AccountId
LEFT JOIN owners ow ON ao.OwnerId = ow.Id

WHERE a.Name NOT LIKE '%[TEST]%'
  AND a.Name NOT LIKE '%Sandbox%'

Property Mapping

Column	Property	Notes
account_id	account_id	Salesforce Account Id
name	name
domain	domain	From Account.Website
created_at	created_at	Salesforce CreatedDate (native timestamp)
parent_account_id	parent_account_id	Salesforce ParentId
sfdc_account_id	sfdc_account_id	Same as account_id; enables CRM sync
amount	amount	ARR from AnnualRevenue or summed opps
expires_at	expires_at	Latest opp close date
is_churned	is_churned	Derived from Customer_Status__c
churned_at	churned_at	Custom Churn_Date__c field
csm_email	csm_email	Auto-assigns CSM
csm_name	csm_name	Fallback for CSM assignment
ae_email	ae_email	Account Owner email
ae_name	ae_name	Account Owner name
account_type	account_type	Custom property for filtering

What this achieves

• Pulls all non-prospect, non-deleted Salesforce accounts
• Calculates ARR from AnnualRevenue field (preferred) or summed closed-won opportunities (fallback)
• Sets expiry from latest opportunity close date
• Builds parent-child hierarchy from ParentId
• Identifies churned accounts from a custom status field
• Auto-assigns CSMs and AEs from Salesforce user data
• Excludes test and sandbox accounts via name filters

---

Example 2: HubSpot as Sole Source

Scenario: Your organization uses HubSpot. Companies and deals are in HubSpot. You want to pull companies with at least one closed-won deal, aggregate deal values for ARR, and track basic account metadata.

Connection

• Type: HubSpot
• Refresh: Daily (24h)

Query

-- HS_START: companies
{
  "object_type": "companies",
  "properties": [
    "name", "domain", "createdate", "annualrevenue",
    "hubspot_owner_id", "industry", "numberofemployees",
    "lifecyclestage"
  ]
}
-- HS_END

-- HS_START: deals
{
  "object_type": "deals",
  "properties": [
    "dealname", "amount", "closedate", "dealstage",
    "hs_object_id", "associations.company"
  ],
  "filter_groups": [{
    "filters": [{
      "propertyName": "dealstage",
      "operator": "EQ",
      "value": "closedwon"
    }]
  }]
}
-- HS_END

-- HS_START: owners
{
  "object_type": "owners",
  "properties": ["email", "firstName", "lastName"]
}
-- HS_END

SELECT
    c.id AS account_id,
    c.name,
    c.domain,
    c.createdate AS created_at,
    c.id AS hubspot_company_id,

    -- ARR: company-level annualrevenue or sum of deals
    COALESCE(
        NULLIF(CAST(c.annualrevenue AS REAL), 0),
        deal_agg.total_amount
    ) AS amount,

    -- Expiry: latest deal close date
    deal_agg.latest_close AS expires_at,

    -- Owner as AE
    ow.email AS ae_email,
    (ow.firstName || ' ' || ow.lastName) AS ae_name,

    -- Custom properties
    c.industry,
    c.numberofemployees AS employee_count,
    c.lifecyclestage AS lifecycle_stage

FROM companies c

LEFT JOIN (
    SELECT
        associations_company AS company_id,
        SUM(CAST(amount AS REAL)) AS total_amount,
        MAX(closedate) AS latest_close,
        COUNT(*) AS deal_count
    FROM deals
    WHERE associations_company IS NOT NULL
    GROUP BY associations_company
) deal_agg ON c.id = deal_agg.company_id

LEFT JOIN owners ow ON c.hubspot_owner_id = ow.id

WHERE c.name IS NOT NULL
  AND c.name != ''
  AND c.name NOT LIKE '%test%'
  AND deal_agg.company_id IS NOT NULL  -- only companies with closed-won deals

Property Mapping

Column	Property	Notes
account_id	account_id	HubSpot Company ID
name	name
domain	domain
created_at	created_at	HubSpot createdate
hubspot_company_id	hubspot_company_id	Enables CRM sync
amount	amount	From annualrevenue or deal sum
expires_at	expires_at	Latest deal close date
ae_email	ae_email	HubSpot owner email
ae_name	ae_name	HubSpot owner name
industry	industry	Custom property
employee_count	employee_count	Custom property
lifecycle_stage	lifecycle_stage	Custom property

---

Example 3: Data Warehouse (Snowflake)

Scenario: Your data team maintains a curated analytics schema in Snowflake. Account data is in a dimension table that already joins CRM, billing, and product data. This is the cleanest approach when you have a well-maintained warehouse.

Connection

• Type: Snowflake
• Refresh: Daily (24h)

Query

SELECT
    a.account_id,
    a.account_name AS name,
    LOWER(a.domain) AS domain,
    EXTRACT(EPOCH FROM a.first_contract_date)::BIGINT AS created_at,
    a.salesforce_id AS sfdc_account_id,
    a.hubspot_id AS hubspot_company_id,
    a.parent_account_id,

    -- Revenue
    a.current_arr AS amount,

    -- Expiry
    EXTRACT(EPOCH FROM a.contract_end_date)::BIGINT AS expires_at,

    -- Churn
    CASE WHEN a.status = 'churned' THEN true ELSE false END AS is_churned,
    CASE
        WHEN a.status = 'churned'
        THEN EXTRACT(EPOCH FROM a.churn_date)::BIGINT
        ELSE NULL
    END AS churned_at,

    -- Team assignment
    csm.email AS csm_email,
    csm.full_name AS csm_name,
    ae.email AS ae_email,
    ae.full_name AS ae_name,

    -- Custom properties
    a.tier,
    a.industry,
    a.region,
    a.plan_name,
    a.employee_count,
    a.health_score

FROM analytics.funnelstory.dim_accounts a
LEFT JOIN analytics.funnelstory.dim_users csm ON a.csm_user_id = csm.user_id
LEFT JOIN analytics.funnelstory.dim_users ae ON a.ae_user_id = ae.user_id

WHERE a.is_deleted = false
  AND a.is_test_account = false
  AND a.account_type = 'customer'

Property Mapping

Column	Property	Notes
account_id	account_id	Internal account identifier
name	name
domain	domain	Lowercased
created_at	created_at	Converted to Unix seconds
sfdc_account_id	sfdc_account_id	For Salesforce sync
hubspot_company_id	hubspot_company_id	For HubSpot sync
parent_account_id	parent_account_id	For hierarchy
amount	amount	Current ARR
expires_at	expires_at	Converted to Unix seconds
is_churned	is_churned	Derived from status
churned_at	churned_at	Converted to Unix seconds
csm_email	csm_email	Auto-assigns CSM
csm_name	csm_name	Fallback for CSM assignment
ae_email	ae_email	Auto-assigns AE
ae_name	ae_name	Fallback for AE assignment
tier	tier	Custom: customer tier
industry	industry	Custom: industry vertical
region	region	Custom: geographic region
plan_name	plan_name	Custom: subscription plan
employee_count	employee_count	Custom
health_score	health_score	Custom

What this achieves

• Uses the warehouse as the single source of truth for clean, pre-modeled data
• Converts all timestamps to Unix seconds (Snowflake EXTRACT(EPOCH FROM ...))
• Links to both Salesforce and HubSpot for CRM sync
• Full parent-child hierarchy
• Complete churn tracking with derived boolean and timestamp
• Auto-assigns both CSMs and AEs
• Rich custom properties for filtering and segmentation
• Excludes deleted, test, and non-customer accounts at the query level

---

Example 4: Hybrid (Salesforce + Data Warehouse)

Scenario: Salesforce is your CRM for account metadata and ownership, but product usage and billing data lives in a PostgreSQL warehouse. You need data from both sources.

This uses a Data Join -- Salesforce as the primary source, PostgreSQL as a secondary source joined by Salesforce Account ID.

Primary Connection: Salesforce

Query:

-- SOQL_START: accounts (Id, Name, Website, CreatedDate, ParentId, AnnualRevenue, OwnerId, CSM_Email__c, Customer_Status__c)
SELECT
    Id, Name, Website, CreatedDate, ParentId,
    AnnualRevenue, OwnerId, CSM_Email__c, Customer_Status__c
FROM Account
WHERE IsDeleted = false
  AND Type = 'Customer'
-- SOQL_END

-- SOQL_START: owners (Id, Email, Name)
SELECT Id, Email, Name FROM User
-- SOQL_END

SELECT
    a.Id AS account_id,
    a.Name AS name,
    a.Website AS domain,
    a.CreatedDate AS created_at,
    a.ParentId AS parent_account_id,
    a.Id AS sfdc_account_id,
    a.AnnualRevenue AS amount,
    a.CSM_Email__c AS csm_email,
    ow.Email AS ae_email,
    CASE WHEN a.Customer_Status__c = 'Churned' THEN 1 ELSE 0 END AS is_churned
FROM accounts a
LEFT JOIN owners ow ON a.OwnerId = ow.Id
WHERE a.Name NOT LIKE '%[TEST]%'

Data Join: PostgreSQL

Connection: Your product PostgreSQL database

Query:

SELECT
    salesforce_account_id,
    EXTRACT(EPOCH FROM MAX(last_login_at))::BIGINT AS last_login_at,
    COUNT(DISTINCT active_user_id) AS active_users_30d,
    SUM(api_calls_30d) AS api_calls_30d,
    MAX(subscription_plan) AS plan_name,
    EXTRACT(EPOCH FROM MAX(subscription_end_date))::BIGINT AS contract_expires_at
FROM product_analytics.account_summary
WHERE last_login_at > NOW() - INTERVAL '90 days'
GROUP BY salesforce_account_id

Join Columns:

Left Column (Salesforce)	Right Column (PostgreSQL)
sfdc_account_id	salesforce_account_id

Combined Property Mapping

After the join, you have columns from both sources:

Column	Source	Property	Notes
account_id	Salesforce	account_id
name	Salesforce	name
domain	Salesforce	domain
created_at	Salesforce	created_at
parent_account_id	Salesforce	parent_account_id
sfdc_account_id	Salesforce	sfdc_account_id
amount	Salesforce	amount	AnnualRevenue
csm_email	Salesforce	csm_email
ae_email	Salesforce	ae_email
is_churned	Salesforce	is_churned
last_login_at	PostgreSQL	last_login_at	Custom; Unix seconds
active_users_30d	PostgreSQL	active_users_30d	Custom
api_calls_30d	PostgreSQL	api_calls_30d	Custom
plan_name	PostgreSQL	plan_name	Custom
contract_expires_at	PostgreSQL	expires_at	Unix seconds

What this achieves

• Salesforce provides CRM-managed data (account info, ownership, ARR, hierarchy, churn)
• PostgreSQL provides product usage metrics (last login, active users, API usage, subscription details)
• The Data Join links them by Salesforce Account ID
• Product metrics become custom account properties, visible on dashboards and usable in workflows
• Timestamps from PostgreSQL are converted to Unix seconds for reliable timestamp handling

---

Choosing the Right Example for Your Setup

Your situation	Start with
Salesforce is your primary CRM	Example 1
HubSpot is your primary CRM	Example 2
You have a curated data warehouse	Example 3
CRM + warehouse (need data from both)	Example 4
Multiple CRMs or complex multi-source	Combine patterns from Examples 1-4 using Data Joins

---

Next Steps

• Ready to configure? Go back to Configuration Walkthrough
• Need to verify your setup? See Verification & Troubleshooting