Writing Queries for the Account Model

The query you write determines what data FunnelStory pulls into the Account model. The format depends on your data source type. This page covers the query syntax for every supported connection type.

How Queries Work in FunnelStory

Regardless of your data source, the goal is the same: return a flat table of rows, where each row is one account. The columns should include at least account_id (or a column you'll map to it), plus any other account fields you want to track.

FunnelStory executes your query against the connected data source, validates the results, and lets you map columns to account properties. The query runs on every refresh cycle (hourly or daily, as configured).

---

SQL Databases and Data Warehouses

Applies to: PostgreSQL, MySQL, MS SQL Server, Snowflake, BigQuery, Databricks, Redshift, Amazon Athena

These connections use standard SQL. Write a SELECT statement that returns one row per account.

Basic example

SELECT
    id AS account_id,
    company_name AS name,
    website AS domain,
    created_at
FROM accounts
WHERE status != 'deleted'

With aggregated deal/contract data

If account metadata and revenue data live in different tables, join and aggregate them:

SELECT
    a.id AS account_id,
    a.name,
    a.domain,
    a.created_at,
    SUM(d.amount) AS amount,
    MAX(d.close_date) AS expires_at,
    a.csm_email
FROM accounts a
LEFT JOIN deals d ON a.id = d.account_id AND d.stage = 'closed_won'
GROUP BY a.id, a.name, a.domain, a.created_at, a.csm_email

Timestamp formatting

FunnelStory has specific preferences for how timestamp values should be formatted. Getting this right ensures properties like created_at, expires_at, and churned_at work correctly throughout the platform.

Preference order (most preferred first):

1. Native database timestamp/datetime types -- the preferred format. If your column is already TIMESTAMP, DATETIME, or TIMESTAMPTZ, the database driver preserves the type and FunnelStory reads it directly.
2. Unix timestamp in seconds (integer) -- a single integer representing seconds since January 1, 1970 UTC. Unambiguous and reliable.
3. RFC 3339 string format (e.g., 2024-01-15T12:00:00Z) -- acceptable when timestamps must be strings
4. Other standard ISO 8601 formats -- generally recognized but less reliable
5. Arbitrary string formats (e.g., "Jan 15, 2024", "15/01/2024") -- will NOT work. FunnelStory cannot parse non-standard date strings.

Converting to Unix seconds by SQL dialect:

Dialect	Conversion
PostgreSQL	EXTRACT(EPOCH FROM created_at)::BIGINT
MySQL	UNIX_TIMESTAMP(created_at)
BigQuery	UNIX_SECONDS(created_at)
Snowflake	DATE_PART(EPOCH_SECOND, created_at)
Databricks	UNIX_TIMESTAMP(created_at)
Redshift	EXTRACT(EPOCH FROM created_at)::BIGINT
MS SQL	DATEDIFF(SECOND, '1970-01-01', created_at)

When to convert: If your source column is already a native TIMESTAMP, DATETIME, or TIMESTAMPTZ type, you generally don't need to convert -- the database driver will handle it. Convert when:

• The column is stored as a string (e.g., VARCHAR with "2024-01-15")
• The column is a Unix timestamp in milliseconds (divide by 1000)
• The column uses a non-standard date format

Example -- converting a millisecond timestamp in PostgreSQL:

SELECT
    id AS account_id,
    name,
    domain,
    (created_at_ms / 1000) AS created_at
FROM accounts

Warehouse-specific tips

Snowflake: Use fully qualified names if needed (database.schema.table). Ensure your Snowflake user has SELECT grants on the relevant tables/views.

SELECT * FROM analytics.funnelstory.account_view

BigQuery: Use backtick-quoted dataset and table names.

SELECT * FROM `my-project.analytics.accounts`

Databricks: Reference tables from your configured catalog and schema.

SELECT * FROM catalog.schema.accounts

---

Salesforce (SOQL Block Queries)

Salesforce connections use a special block query format. You write SOQL (Salesforce Object Query Language) inside marked blocks, and FunnelStory fetches those records from Salesforce's API. You then write SQLite SQL outside the blocks to transform and combine the fetched data.

How it works

1. SOQL blocks fetch data from Salesforce objects and store results in named temporary tables
2. SQLite SQL (outside the blocks) queries those temporary tables to produce the final output

This two-layer approach lets you pull from multiple Salesforce objects and join them locally.

Syntax

-- SOQL_START: table_name (optional_column_hints)
SELECT Field1, Field2, Field3 FROM SalesforceObject WHERE conditions
-- SOQL_END

SELECT * FROM table_name

• -- SOQL_START: table_name begins a block. table_name is the alias you'll use in the SQLite SQL.
• The optional (col1, col2, col3) after the table name provides column hints (used when the SOQL returns zero rows, to still know the schema).
• Everything between START and END is valid SOQL sent directly to the Salesforce Query API.
• -- SOQL_END closes the block.
• SQL written outside any block is SQLite SQL that runs against the fetched tables.

Single-object example

Pull accounts directly from Salesforce's Account object:

-- SOQL_START: accounts (Id, Name, Website, CreatedDate, AnnualRevenue, ParentId, OwnerId)
SELECT Id, Name, Website, CreatedDate, AnnualRevenue, ParentId, 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,
    ParentId AS parent_account_id,
    OwnerId AS owner_id
FROM accounts

Multi-object example (Account + Opportunity)

Pull accounts and their closed-won opportunities, then aggregate:

-- SOQL_START: accounts (Id, Name, Website, CreatedDate, ParentId)
SELECT Id, Name, Website, CreatedDate, ParentId
FROM Account
WHERE IsDeleted = false
-- 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

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,
    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, a.ParentId

SOQL tips

• Use standard Salesforce field names (e.g., Id, Name, CreatedDate, IsDeleted)
• Custom fields end with __c (e.g., Customer_ARR__c, CS_Status__c)
• SOQL supports WHERE, ORDER BY, LIMIT, and relationship queries (e.g., Owner.Email)
• You can include multiple SOQL blocks in one query
• The SQLite SQL between/after blocks supports JOIN, GROUP BY, HAVING, subqueries, CTEs (WITH), and all standard SQLite functions

Important notes on SOQL blocks

• Every SOQL_START must have a matching SOQL_END
• Blocks cannot be nested
• The table name must be a single word (no spaces or special characters)
• Column hints in parentheses are optional but recommended -- they provide a fallback schema when the SOQL returns zero rows

---

HubSpot (HS Block Queries)

HubSpot connections use a block query format similar to Salesforce, but the block body is a JSON payload that maps to HubSpot's CRM Search API.

How it works

1. HS blocks call HubSpot's Search API to fetch objects (companies, deals, contacts) and store results in named temporary tables
2. SQLite SQL (outside the blocks) queries those temporary tables

Syntax

-- HS_START: table_name
{
  "object_type": "companies",
  "properties": ["property1", "property2"],
  "filter_groups": [...]
}
-- HS_END

SELECT * FROM table_name

JSON body fields

Field	Type	Required	Description
object_type	string	Yes	HubSpot object type: companies, deals, contacts, tickets, etc.
properties	array of strings	No	Which HubSpot properties to return. If omitted, returns default properties.
filter_groups	array of objects	No	Search filters following HubSpot's CRM Search API format.
limit	integer	No	Max records per page (default 100).
sleep_ms	integer	No	Milliseconds to wait between pagination requests (for rate limiting).
error_on_rate_limit	boolean	No	If true, fails on rate limit instead of stopping pagination.

Filter groups format

Filter groups follow HubSpot's standard CRM Search API syntax:

"filter_groups": [
  {
    "filters": [
      {
        "propertyName": "dealstage",
        "operator": "EQ",
        "value": "closedwon"
      }
    ]
  }
]

Common operators: EQ, NEQ, GT, GTE, LT, LTE, CONTAINS_TOKEN, NOT_CONTAINS_TOKEN, HAS_PROPERTY, NOT_HAS_PROPERTY.

Example: Pull companies

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

SELECT
    id AS account_id,
    name,
    domain,
    createdate AS created_at,
    annualrevenue AS amount
FROM companies

Note: HubSpot always includes the record id in results. It's available as a column in the SQLite query.

Example: Pull closed-won deals, then aggregate by company

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

SELECT
    company_id AS account_id,
    SUM(CAST(amount AS REAL)) AS amount,
    MAX(closedate) AS expires_at
FROM deals
WHERE company_id IS NOT NULL
GROUP BY company_id

HubSpot tips

• Property names use HubSpot's internal names (lowercase, underscored), not display names
• Use createdate for company creation, closedate for deal close date
• Filter groups are ANDed within a group, ORed across groups
• Rate limiting: If you're pulling large datasets, set sleep_ms (e.g., 200) to avoid hitting HubSpot's API rate limits

---

Attio (AT Block Queries)

Attio uses a block format similar to HubSpot with a JSON body.

Syntax

-- AT_START: table_name
{
  "object_type": "companies",
  "filter": { ... },
  "sorts": [{ "field": "created_at", "direction": "desc" }],
  "limit": 100
}
-- AT_END

SELECT * FROM table_name

JSON body fields

Field	Type	Required	Description
object_type	string	Yes	Attio object type (e.g., companies, people, deals)
filter	object	No	Filter conditions
sorts	array	No	Sort order
limit	integer	No	Maximum records to fetch

Example

-- AT_START: companies
{
  "object_type": "companies",
  "filter": {},
  "limit": 10000
}
-- AT_END

SELECT
    id AS account_id,
    name,
    primary_domain AS domain,
    created_at
FROM companies

---

MongoDB (MQL Block Queries)

MongoDB connections use MQL blocks with a JSON body specifying the database, collection, and optional filters.

Syntax

-- MQL_START: table_name
{
  "database": "your_db",
  "collection": "accounts",
  "filter": { "type": "customer" },
  "limit": 10000,
  "sort": { "created_at": -1 }
}
-- MQL_END

SELECT * FROM table_name

JSON body fields

Field	Type	Required	Description
database	string	Yes	MongoDB database name
collection	string	Yes	Collection name
filter	object	No	MongoDB query filter
limit	integer	No	Maximum documents
sort	object	No	Sort specification

---

Gainsight (GS Block Queries)

Gainsight uses blocks with an API endpoint and request body.

Syntax

-- GS_START: table_name
{
  "endpoint": "/v1/data/objects/query/Company",
  "body": {
    "select": ["Gsid", "Name", "Arr", "RenewalDate", "Stage"]
  }
}
-- GS_END

SELECT * FROM table_name

The endpoint field is required and specifies which Gainsight API endpoint to call.

---

Common Pattern: Blocks + SQLite SQL

All block-based query formats (SOQL, HS, AT, MQL, GS) share the same architecture:

1. Blocks fetch raw data from the external API and store it in named temporary tables
2. SQLite SQL outside the blocks transforms, joins, filters, and reshapes the data

This means you can:

• Use multiple blocks to pull from different objects/tables, then join them in SQLite
• Use CTEs (WITH ... AS) to build complex transformations
• Apply aggregations (SUM, COUNT, MIN, MAX, GROUP BY) in the SQLite layer
• Filter with WHERE clauses in the SQLite layer (in addition to or instead of API-level filters)
• Use standard SQLite functions for string manipulation, date handling, CASE expressions, etc.

The blocks handle API-specific syntax (SOQL, HubSpot Search JSON, etc.), while the SQLite SQL gives you full relational query power over the fetched results.

---

Next Steps

• See Field Reference for which properties to map your query columns to
• See Advanced Configuration for query patterns that handle ARR aggregation, churn detection, parent-child hierarchies, and more
• See Real-World Examples for complete worked examples