Advanced Account Model Configuration

This page covers advanced scenarios for the Account model. Each section explains a specific configuration goal, how it works in FunnelStory, and provides query examples for multiple data source types.

Before reading this page, you should be familiar with:

• Writing Queries -- query syntax for your data source
• Field Reference -- available properties and their types

---

Calculate and Set ARR (Annual Recurring Revenue)

Map your revenue data to the amount property. FunnelStory uses this value for revenue dashboards, health scoring, and account prioritization.

Where the data comes from

Source	Common column/field names
Salesforce	AnnualRevenue, Customer_ARR__c, Opportunity Amount
HubSpot	Deal amount, Company annualrevenue
Data warehouse	arr, mrr * 12, contract_value, amount

Simple case: ARR on the account record

If ARR is stored directly on the account/company record:

SQL (warehouse):

SELECT
    id AS account_id,
    name,
    domain,
    created_at,
    arr AS amount
FROM accounts

Salesforce:

-- SOQL_START: accounts
SELECT Id, Name, Website, CreatedDate, AnnualRevenue FROM Account
-- SOQL_END

SELECT Id AS account_id, Name AS name, Website AS domain,
       CreatedDate AS created_at, AnnualRevenue AS amount
FROM accounts

Aggregating from deals/opportunities

When ARR must be calculated from individual deals, use SUM:

SQL (warehouse):

SELECT
    a.id AS account_id,
    a.name,
    a.domain,
    a.created_at,
    COALESCE(SUM(d.annual_amount), 0) AS amount
FROM accounts a
LEFT JOIN deals d ON a.id = d.account_id
    AND d.stage = 'closed_won'
    AND d.is_active = true
GROUP BY a.id, a.name, a.domain, a.created_at

Salesforce (multi-block):

-- SOQL_START: accounts
SELECT Id, Name, Website, CreatedDate FROM Account
-- SOQL_END

-- SOQL_START: opps
SELECT AccountId, Amount FROM Opportunity
WHERE StageName = 'Closed Won' AND IsClosed = true
-- SOQL_END

SELECT
    a.Id AS account_id, a.Name AS name, a.Website AS domain,
    a.CreatedDate AS created_at,
    COALESCE(SUM(o.Amount), 0) AS amount
FROM accounts a
LEFT JOIN opps o ON a.Id = o.AccountId
GROUP BY a.Id, a.Name, a.Website, a.CreatedDate

Mapping: Map the amount column to the amount property.

---

Set Start Date and Expiry Date

Start date (created_at)

The created_at property represents when the account became a customer. Common sources:

• CRM: CreatedDate (Salesforce), createdate (HubSpot), or a custom field like First_Closed_Won_Date__c
• Warehouse: created_at, signup_date, first_contract_date

If you want to use the first closed-won deal date instead of the account creation date:

SELECT
    a.id AS account_id,
    a.name,
    a.domain,
    MIN(d.close_date) AS created_at
FROM accounts a
JOIN deals d ON a.id = d.account_id AND d.stage = 'closed_won'
GROUP BY a.id, a.name, a.domain

Expiry date (expires_at)

The expires_at property drives renewal management. Map it from contract end dates:

SELECT
    a.id AS account_id,
    a.name,
    MAX(c.end_date) AS expires_at
FROM accounts a
LEFT JOIN contracts c ON a.id = c.account_id AND c.status = 'active'
GROUP BY a.id, a.name

Using MAX picks the latest contract end date when an account has multiple active contracts.

Timestamp formatting reminder: Ensure created_at and expires_at are in Unix seconds, native database timestamps, or RFC 3339 format. See Timestamp Formatting.

---

Set Domain

The domain property is used for account enrichment, matching, and deduplication. It should be a clean, lowercase domain without protocols or paths.

From a direct field

SELECT
    id AS account_id,
    name,
    LOWER(REPLACE(REPLACE(website, 'https://', ''), 'http://', '')) AS domain,
    created_at
FROM accounts

Extracted from email

If you don't have a domain field but have contact emails:

SELECT
    a.id AS account_id,
    a.name,
    LOWER(SPLIT_PART(MIN(c.email), '@', 2)) AS domain,  -- PostgreSQL
    a.created_at
FROM accounts a
LEFT JOIN contacts c ON a.id = c.account_id
GROUP BY a.id, a.name, a.created_at

---

Identify Actual Customers Using CRM Properties

Not everything in your CRM is a paying customer. Use your CRM's stage, status, or type fields to filter down to real customers.

Salesforce: Filter by Opportunity Stage

-- SOQL_START: accounts
SELECT Id, Name, Website, CreatedDate FROM Account
WHERE Id IN (SELECT AccountId FROM Opportunity WHERE StageName = 'Closed Won')
-- SOQL_END

SELECT Id AS account_id, Name AS name, Website AS domain, CreatedDate AS created_at
FROM accounts

Or filter by a custom account status field:

-- SOQL_START: accounts
SELECT Id, Name, Website, CreatedDate FROM Account
WHERE Customer_Status__c = 'Active Customer'
-- SOQL_END

SELECT Id AS account_id, Name AS name, Website AS domain, CreatedDate AS created_at
FROM accounts

HubSpot: Filter by Deal Stage

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

-- HS_START: companies
{
  "object_type": "companies",
  "properties": ["name", "domain", "createdate"]
}
-- HS_END

SELECT DISTINCT
    c.id AS account_id,
    c.name,
    c.domain,
    c.createdate AS created_at
FROM companies c
INNER JOIN deals d ON c.id = d.associations_company

Warehouse: Filter by status or type

SELECT id AS account_id, name, domain, created_at
FROM accounts
WHERE account_type = 'customer'
  AND status IN ('active', 'renewal_pending')

---

Define Parent-Child Account Relationships

Map the parent_account_id property to create hierarchical account structures. The parent account must also exist in the Account model (i.e., it must be returned by the same query with a matching account_id).

How it works

When FunnelStory processes the Account model:

1. It looks at each account's parent_account_id value
2. It finds the parent account by matching against account_id in the same model
3. It establishes the parent-child relationship

Parent accounts (sometimes called "container accounts") can aggregate metrics from their children, including aggregate_amount (sum of child ARR) and earliest_child_expiry.

Salesforce (using ParentId)

-- SOQL_START: accounts
SELECT Id, Name, Website, CreatedDate, ParentId FROM Account
WHERE IsDeleted = false
-- SOQL_END

SELECT
    Id AS account_id,
    Name AS name,
    Website AS domain,
    CreatedDate AS created_at,
    ParentId AS parent_account_id
FROM accounts

Warehouse

SELECT
    id AS account_id,
    name,
    domain,
    created_at,
    parent_id AS parent_account_id
FROM accounts

Important notes

• An account cannot be its own parent (parent_account_id must differ from account_id)
• Circular references (A is parent of B, B is parent of A) will cause issues
• The parent account must exist in the model -- if the parent isn't returned by your query, the relationship won't be created
• You can have multi-level hierarchies (grandparent -> parent -> child)

Container accounts

If you want a parent account to be treated purely as a grouping container (not a customer account itself), you can include a custom property is_container set to true:

SELECT
    id AS account_id,
    name,
    domain,
    created_at,
    parent_id AS parent_account_id,
    CASE WHEN account_type = 'holding_company' THEN 'true' ELSE 'false' END AS is_container
FROM accounts

---

Auto-Assign Accounts to CSMs

FunnelStory can automatically assign accounts to workspace users based on properties in the Account model. This works for CSMs, CSEs, AEs, SEs, and any other designations configured in your workspace.

How it works

1. You map CSM email/name from your data source to properties like csm_email and csm_name
2. On each Account model refresh, FunnelStory:

• Reads the csm_email value for each account
• Looks up that email in the workspace's user list
• If a match is found, assigns the account to that user under the "CSM" designation

3. Manual assignments are never overwritten -- only accounts without an existing assignment for that designation are auto-assigned

Supported designations

Email property	Name property	Designation
csm_email	csm_name	Customer Success Manager
cse_email	cse_name	Customer Success Engineer
ae_email	ae_name	Account Executive
se_email	se_name	Sales Engineer

You can use any combination. Most teams map at least csm_email.

Salesforce example

-- SOQL_START: accounts
SELECT Id, Name, Website, CreatedDate, Owner.Email, CSM_Email__c FROM Account
-- SOQL_END

SELECT
    Id AS account_id,
    Name AS name,
    Website AS domain,
    CreatedDate AS created_at,
    "Owner.Email" AS ae_email,
    CSM_Email__c AS csm_email
FROM accounts

Warehouse example

SELECT
    a.id AS account_id,
    a.name,
    a.domain,
    a.created_at,
    u_csm.email AS csm_email,
    u_csm.name AS csm_name,
    u_ae.email AS ae_email
FROM accounts a
LEFT JOIN users u_csm ON a.csm_user_id = u_csm.id
LEFT JOIN users u_ae ON a.owner_user_id = u_ae.id

Prerequisites

• The users being assigned must already exist in your FunnelStory workspace (invited and accepted)
• The email addresses in your data must match the email addresses users signed up with in FunnelStory

---

Combine Multiple Contracts or Deals for a Single Account

When an account has multiple active contracts, subscriptions, or deals, you typically want to produce one row per account with aggregated values.

Aggregate pattern

SELECT
    a.id AS account_id,
    a.name,
    a.domain,
    a.created_at,
    COUNT(d.id) AS deal_count,                    -- custom property
    SUM(d.amount) AS amount,                       -- total ARR
    MIN(d.start_date) AS created_at,               -- earliest contract start
    MAX(d.end_date) AS expires_at,                 -- latest contract end
    GROUP_CONCAT(DISTINCT d.product_name) AS products  -- custom property
FROM accounts a
LEFT JOIN deals d ON a.id = d.account_id AND d.status = 'active'
GROUP BY a.id, a.name, a.domain

Salesforce: Account + multiple Opportunities

-- SOQL_START: accounts
SELECT Id, Name, Website, CreatedDate FROM Account
-- SOQL_END

-- SOQL_START: opps
SELECT Id, AccountId, Amount, CloseDate, StageName FROM Opportunity
WHERE StageName = 'Closed Won'
-- SOQL_END

SELECT
    a.Id AS account_id,
    a.Name AS name,
    a.Website AS domain,
    a.CreatedDate AS created_at,
    COUNT(o.Id) AS deal_count,
    SUM(o.Amount) AS amount,
    MAX(o.CloseDate) AS expires_at
FROM accounts a
LEFT JOIN opps o ON a.Id = o.AccountId
GROUP BY a.Id, a.Name, a.Website, a.CreatedDate

HubSpot: Companies + multiple Deals

-- HS_START: companies
{
  "object_type": "companies",
  "properties": ["name", "domain", "createdate"]
}
-- HS_END

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

SELECT
    c.id AS account_id,
    c.name,
    c.domain,
    c.createdate AS created_at,
    COUNT(d.id) AS deal_count,
    SUM(CAST(d.amount AS REAL)) AS amount,
    MAX(d.closedate) AS expires_at
FROM companies c
LEFT JOIN deals d ON c.id = d.associations_company
GROUP BY c.id, c.name, c.domain, c.createdate

---

Identify and Set Churned Status

Map is_churned (boolean) and optionally churned_at (timestamp) to track which accounts have churned and when.

From a status field

If your CRM or warehouse has an explicit churn status:

SELECT
    id AS account_id,
    name,
    domain,
    created_at,
    CASE WHEN status = 'churned' THEN true ELSE false END AS is_churned,
    churned_date AS churned_at
FROM accounts

Salesforce (custom status field)

-- SOQL_START: accounts
SELECT Id, Name, Website, CreatedDate, CS_Status__c, Churn_Date__c FROM Account
-- SOQL_END

SELECT
    Id AS account_id,
    Name AS name,
    Website AS domain,
    CreatedDate AS created_at,
    CASE WHEN CS_Status__c = 'Churned' THEN 1 ELSE 0 END AS is_churned,
    Churn_Date__c AS churned_at
FROM accounts

Derived from contract expiry

If you don't have an explicit churn flag, derive it from expired contracts with no renewal:

SELECT
    a.id AS account_id,
    a.name,
    a.domain,
    a.created_at,
    CASE
        WHEN MAX(c.end_date) < CURRENT_DATE AND NOT EXISTS (
            SELECT 1 FROM contracts c2
            WHERE c2.account_id = a.id AND c2.status = 'active'
        )
        THEN true
        ELSE false
    END AS is_churned,
    MAX(c.end_date) AS churned_at
FROM accounts a
LEFT JOIN contracts c ON a.id = c.account_id
GROUP BY a.id, a.name, a.domain, a.created_at

---

Exclude Test and Internal Accounts

Filter out sandbox, test, demo, and internal accounts so they don't pollute your dashboards and metrics. Do this with WHERE clauses in your query.

By domain

SELECT id AS account_id, name, domain, created_at
FROM accounts
WHERE domain NOT IN ('example.com', 'test.com', 'yourcompany.com')
  AND domain NOT LIKE '%sandbox%'

By name pattern

SELECT id AS account_id, name, domain, created_at
FROM accounts
WHERE name NOT LIKE '%[TEST]%'
  AND name NOT LIKE '%[DEMO]%'
  AND name NOT LIKE 'Sandbox%'

By account type or flag

SELECT id AS account_id, name, domain, created_at
FROM accounts
WHERE is_test = false
  AND account_type != 'internal'

Salesforce

-- SOQL_START: accounts
SELECT Id, Name, Website, CreatedDate FROM Account
WHERE Type != 'Test'
  AND Name != NULL
  AND IsDeleted = false
-- SOQL_END

SELECT Id AS account_id, Name AS name, Website AS domain, CreatedDate AS created_at
FROM accounts
WHERE Name NOT LIKE '%[TEST]%'

Note that you can filter at both levels: in the SOQL (API-side filtering) and in the SQLite SQL (local filtering). Use API-side filtering for large datasets to reduce data transfer.

HubSpot

Use filter_groups to exclude at the API level, or filter in the SQLite SQL:

-- HS_START: companies
{
  "object_type": "companies",
  "properties": ["name", "domain", "createdate", "hs_object_id"],
  "filter_groups": [{
    "filters": [{
      "propertyName": "hs_is_target_account",
      "operator": "EQ",
      "value": "true"
    }]
  }]
}
-- HS_END

SELECT id AS account_id, name, domain, createdate AS created_at
FROM companies
WHERE name NOT LIKE '%test%'

---

Join with Data from Other Connections

FunnelStory supports Data Joins to combine data from two different connections. This is how you build a hybrid Account model -- for example, CRM account data enriched with warehouse product data.

How Data Joins work

1. Your primary data source (the model's main connection) provides the base account data
2. A secondary data source (configured as a Data Join) provides additional columns
3. You specify join columns -- which column from the primary maps to which column in the secondary
4. FunnelStory performs a left join: all primary rows are kept, matched secondary rows add their columns

Setting up a Data Join in the UI

1. In the Map Information step, scroll to the bottom
2. Click Add Data Join
3. Select the connection for the secondary data source
4. Write a query against the secondary connection
5. Configure join columns:

• Left column: A column from your primary query (e.g., account_id)
• Right column: A column from the secondary query (e.g., account_id or external_id)

6. The joined columns become available for mapping alongside your primary columns

Example: Salesforce accounts + warehouse usage data

Primary query (Salesforce connection):

-- SOQL_START: accounts
SELECT Id, Name, Website, CreatedDate, AnnualRevenue, OwnerId FROM Account
WHERE IsDeleted = false
-- SOQL_END

SELECT
    Id AS account_id,
    Name AS name,
    Website AS domain,
    CreatedDate AS created_at,
    AnnualRevenue AS amount
FROM accounts

Data Join query (Snowflake connection):

SELECT
    salesforce_account_id AS account_id,
    last_login_at,
    total_logins_30d,
    active_users_count
FROM product_analytics.account_usage_summary

Join columns:

• Left column: account_id
• Right column: account_id

After the join, you can map last_login_at, total_logins_30d, and active_users_count as custom account properties.

Example: Warehouse accounts + HubSpot deal data

Primary query (PostgreSQL connection):

SELECT
    id AS account_id,
    name,
    domain,
    created_at,
    hubspot_id
FROM accounts

Data Join query (HubSpot connection):

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

SELECT
    associations_company AS company_id,
    SUM(CAST(amount AS REAL)) AS deal_amount,
    MAX(closedate) AS latest_close_date
FROM deals
GROUP BY associations_company

Join columns:

• Left column: hubspot_id
• Right column: company_id

Tips for Data Joins

• Column name collisions: if both queries return a column with the same name, the primary query's column takes precedence
• The join is always a left join -- every row from the primary query is preserved, even if there's no match in the secondary query
• You can add multiple Data Joins to combine data from more than two connections
• Keep secondary queries focused -- only return the columns you need for the join to minimize data transfer
• Ensure the join column values match between the two sources (e.g., both use the same Salesforce Account ID format)

---

Next Steps

• See Real-World Examples for complete end-to-end configurations that combine many of these scenarios
• See Verification & Troubleshooting to confirm your configuration is working correctly