HotCRM Logo

Analytics & ObjectQL Patterns

Guide to building analytics queries, reports, and dashboards using ObjectQL in HotCRM.

Analytics & ObjectQL Patterns

ObjectQL is the query language for all data access in HotCRM. This guide covers the analytics-specific patterns — aggregations, cross-object reporting, KPI definitions, and dashboard widgets — that power the Analytics Cloud.

Rule: All data access MUST use ObjectQL. Never write raw SQL. Format: broker.find('object', { filters, fields, sort, limit }).

ObjectQL Basics for Analytics

Simple Query

Retrieve records with filters, field selection, and sorting:

// Find all open opportunities over $50,000
const deals = await broker.find('opportunity', {
  filters: [
    ['stage', '!=', 'closed_lost'],
    ['amount', '>', 50000]
  ],
  fields: ['name', 'account_id', 'amount', 'stage', 'close_date'],
  sort: [{ field: 'amount', direction: 'desc' }],
  limit: 100
});

Counting Records

// Count leads by source
const webLeads = await broker.count('lead', {
  filters: [['lead_source', '=', 'Web']]
});

Pagination

// Paginated query for large datasets
const page1 = await broker.find('contact', {
  filters: [['account_id', '=', accountId]],
  fields: ['name', 'email', 'title'],
  limit: 50,
  offset: 0
});

Aggregation Patterns

ObjectQL supports four aggregation functions through Field.summary() rollup fields and direct query patterns.

Sum

Total a numeric field across matching records:

// Total pipeline value by stage
const pipeline = await broker.aggregate('opportunity', {
  filters: [['is_closed', '=', false]],
  groupBy: ['stage'],
  aggregate: [
    { field: 'amount', function: 'sum', alias: 'total_value' }
  ]
});

Count

Count records matching a condition:

// Count cases by priority
const casePriority = await broker.aggregate('case', {
  filters: [['status', '!=', 'closed']],
  groupBy: ['priority'],
  aggregate: [
    { field: 'id', function: 'count', alias: 'case_count' }
  ]
});

Min / Max

Find boundary values:

// Earliest and latest close dates in the pipeline
const dateRange = await broker.aggregate('opportunity', {
  filters: [['is_closed', '=', false]],
  aggregate: [
    { field: 'close_date', function: 'min', alias: 'earliest_close' },
    { field: 'close_date', function: 'max', alias: 'latest_close' }
  ]
});

Combined Aggregations

// Sales team performance summary
const teamStats = await broker.aggregate('opportunity', {
  filters: [
    ['close_date', '>=', '2024-01-01'],
    ['close_date', '<=', '2024-12-31']
  ],
  groupBy: ['owner_id'],
  aggregate: [
    { field: 'amount', function: 'sum', alias: 'total_revenue' },
    { field: 'id', function: 'count', alias: 'deal_count' },
    { field: 'amount', function: 'min', alias: 'smallest_deal' },
    { field: 'amount', function: 'max', alias: 'largest_deal' }
  ]
});

Cross-Object Reporting

Join data across multiple objects to answer complex business questions.

Accounts with Open Opportunities and Cases

// Accounts that have both open deals and open support cases
const accounts = await broker.find('account', {
  filters: [['type', '=', 'Customer']],
  fields: ['name', 'industry', 'annual_revenue'],
  include: [
    {
      object: 'opportunity',
      filters: [['is_closed', '=', false]],
      fields: ['name', 'amount', 'stage'],
      alias: 'open_deals'
    },
    {
      object: 'case',
      filters: [['status', '!=', 'closed']],
      fields: ['case_number', 'subject', 'priority'],
      alias: 'open_cases'
    }
  ]
});

Campaign ROI Report

// Campaign effectiveness with lead and opportunity data
const campaigns = await broker.find('campaign', {
  filters: [['status', '=', 'completed']],
  fields: ['name', 'type', 'actual_cost', 'start_date'],
  include: [
    {
      object: 'campaign_member',
      aggregate: [
        { field: 'id', function: 'count', alias: 'total_members' }
      ],
      alias: 'membership'
    },
    {
      object: 'opportunity',
      filters: [['is_won', '=', true]],
      aggregate: [
        { field: 'amount', function: 'sum', alias: 'revenue_generated' }
      ],
      alias: 'won_deals'
    }
  ]
});

KPI and Metric Definitions

Sales KPIs

// Win rate calculation
const wonDeals = await broker.count('opportunity', {
  filters: [['is_won', '=', true], ['close_date', '>=', startOfQuarter]]
});
const totalClosed = await broker.count('opportunity', {
  filters: [['is_closed', '=', true], ['close_date', '>=', startOfQuarter]]
});
const winRate = totalClosed > 0 ? (wonDeals / totalClosed) * 100 : 0;

// Average deal size
const dealStats = await broker.aggregate('opportunity', {
  filters: [['is_won', '=', true], ['close_date', '>=', startOfQuarter]],
  aggregate: [
    { field: 'amount', function: 'sum', alias: 'total_revenue' },
    { field: 'id', function: 'count', alias: 'deal_count' }
  ]
});
const avgDealSize = dealStats.total_revenue / dealStats.deal_count;

Service KPIs

// SLA compliance rate
const totalCases = await broker.count('case', {
  filters: [['created_date', '>=', startOfMonth]]
});
const slaMet = await broker.count('case', {
  filters: [
    ['created_date', '>=', startOfMonth],
    ['sla_met', '=', true]
  ]
});
const slaCompliance = totalCases > 0 ? (slaMet / totalCases) * 100 : 0;

Revenue KPIs

// Monthly Recurring Revenue
const mrrData = await broker.aggregate('contract', {
  filters: [
    ['status', '=', 'active'],
    ['contract_type', '=', 'subscription']
  ],
  aggregate: [
    { field: 'monthly_value', function: 'sum', alias: 'mrr' }
  ]
});

// Days Sales Outstanding
const invoiceAging = await broker.aggregate('invoice', {
  filters: [['status', '=', 'sent']],
  aggregate: [
    { field: 'days_outstanding', function: 'sum', alias: 'total_days' },
    { field: 'id', function: 'count', alias: 'invoice_count' }
  ]
});
const dso = invoiceAging.total_days / invoiceAging.invoice_count;

Report Scheduling

Reports can be scheduled for automatic generation and delivery using workflow rules:

// packages/analytics/src/weekly_pipeline_report.workflow.ts
import type { WorkflowRule } from '@objectstack/spec/automation';
import { WorkflowRuleSchema } from '@objectstack/spec/automation';

export const WeeklyPipelineReport = {
  name: 'weekly_pipeline_report',
  label: 'Weekly Pipeline Report',
  object: 'opportunity',
  trigger: {
    type: 'scheduled' as const,
    schedule: '0 8 * * MON'  // Every Monday at 8:00 AM
  },
  actions: [
    {
      type: 'send_report' as const,
      report: 'pipeline_summary',
      recipients: ['sales_managers'],
      format: 'pdf'
    }
  ]
} satisfies WorkflowRule;

WorkflowRuleSchema.parse(WeeklyPipelineReport);
export default WeeklyPipelineReport;

Dashboard Widget Patterns

Dashboard widgets are defined in *.dashboard.ts files using DashboardSchema from @objectstack/spec/ui.

KPI Card

Display a single metric with trend indicator:

{
  type: 'kpi_card',
  label: 'Pipeline Value',
  properties: {
    object: 'opportunity',
    aggregate: { field: 'amount', function: 'sum' },
    filters: [['is_closed', '=', false]],
    format: 'currency',
    trend: { period: 'month', direction: 'up_is_good' }
  }
}

Chart Widget

Visualize data with various chart types:

{
  type: 'chart',
  label: 'Deals by Stage',
  properties: {
    chartType: 'bar',
    object: 'opportunity',
    groupBy: 'stage',
    aggregate: { field: 'amount', function: 'sum' },
    filters: [['is_closed', '=', false]],
    sort: { field: 'stage_order', direction: 'asc' }
  }
}

Funnel Widget

Visualize conversion through stages:

{
  type: 'funnel',
  label: 'Sales Funnel',
  properties: {
    object: 'opportunity',
    stages: ['prospecting', 'qualification', 'proposal', 'negotiation', 'closed_won'],
    valueField: 'amount',
    countField: 'id'
  }
}

Table Widget

Display tabular data with sorting and pagination:

{
  type: 'table',
  label: 'Top 10 Accounts by Revenue',
  properties: {
    object: 'account',
    columns: ['name', 'industry', 'annual_revenue', 'number_of_employees'],
    sort: [{ field: 'annual_revenue', direction: 'desc' }],
    limit: 10
  }
}

Best Practices

  1. Filter early: Apply filters to reduce the dataset before aggregation — smaller datasets mean faster queries
  2. Use aggregations: Prefer broker.aggregate() over fetching all records and computing in application code
  3. Limit fields: Only request the fields you need with the fields parameter
  4. Cache results: For dashboards, cache aggregation results and refresh on a schedule rather than on every page load
  5. Index awareness: Filter on indexed fields (IDs, status, dates) for the best query performance
  6. Paginate large results: Use limit and offset to avoid loading thousands of records at once

Building Custom Connectors

Step-by-step guide to building integration connectors for HotCRM.

On this page

Analytics & ObjectQL PatternsObjectQL Basics for AnalyticsSimple QueryCounting RecordsPaginationAggregation PatternsSumCountMin / MaxCombined AggregationsCross-Object ReportingAccounts with Open Opportunities and CasesCampaign ROI ReportKPI and Metric DefinitionsSales KPIsService KPIsRevenue KPIsReport SchedulingDashboard Widget PatternsKPI CardChart WidgetFunnel WidgetTable WidgetBest Practices