Datasets & Queries

A dataset is a saved query that defines what data to fetch. It's the first tier of the analytics pipeline — one dataset can power many charts.

Creating a Dataset

1. Navigate to Analytics > Datasets
2. Click + New Dataset
3. Name your dataset
4. Build your query (visual builder or raw SQL)
5. Save

Data Sources

A dataset can pull from any of these sources:

• Org CRM database — contacts, companies, deals, tasks, activities, and more
• Uploaded files — CSV or Excel
• Google Sheets
• External PostgreSQL databases — connected via integrations

Query Builder

Visual Mode

Build queries without writing SQL:

1. Select table - Choose data source
2. Add columns - Fields to include
3. Add filters - WHERE conditions
4. Group by - Aggregation groups
5. Order by - Sort results

SQL is the source of truth. Whatever you build in visual mode is compiled to SQL, and the SQL is what actually runs. Switching to SQL mode lets you fine-tune the exact query.

SQL Mode

Write raw SQL:

SELECT
  status,
  COUNT(*) as count,
  SUM(deal_value) as total_value
FROM deals
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY status
ORDER BY total_value DESC

Available Tables

CRM Tables

Table	Description
contacts	All contacts
companies	Company records
deals	Deal/opportunity data
tasks	Task records
activities	Activity log

Channel Tables

Table	Description
campaigns	Campaign data
campaign_stats	Performance metrics
templates	Message templates

Workflow Tables

Table	Description
workflows	Workflow definitions
workflow_executions	Execution history

Query Parameters

Make queries dynamic with parameters:

SELECT *
FROM contacts
WHERE created_at BETWEEN {{start_date}} AND {{end_date}}
  AND status = {{status}}

Parameters become filters in charts/dashboards.

Aggregations

Supported Functions

Function	Example
COUNT	Number of records
SUM	Total of values
AVG	Average value
MIN	Minimum value
MAX	Maximum value
COUNT_DISTINCT	Count of distinct values

Date Grouping

Time-series data can be grouped by hour, day, week, month, quarter, or year. In raw SQL, use DATE_TRUNC:

Expression	Description
DATE_TRUNC('month', date)	Group by month
DATE_TRUNC('week', date)	Group by week
EXTRACT(dow FROM date)	Day of week

Sorting & Limiting

Datasets support server-side Sort By and Top N, so charts only fetch the rows they need.

Testing Queries

1. Click Run to execute
2. View results in table
3. Check row count
4. Verify data accuracy

Query Performance

Best Practices

1. Filter early - Add WHERE clauses
2. Limit results - Use Top N for testing
3. Index columns - Filter on indexed fields
4. **Avoid SELECT *** - Only needed columns

Caching

Queries are cached:

• Default: 5 minutes
• Configure per dataset
• Force refresh available

Saving Datasets

Save datasets for reuse:

• In charts
• In dashboards
• As templates

Next Steps

• Building Charts
• Creating Dashboards