Skip to main content

Running Queries

The Reports page gives you direct access to pre-built SQL queries that run against your synced data. Unlike Insights, which focuses on time-series exploration, Reports lets you run structured queries and view the results as tables or charts.

Query list​

The left sidebar shows all available queries for your organization. Click any query to run it — results appear in the main panel.

Each query is pre-built to answer a specific question about your data (e.g., revenue by location, class utilization, membership churn). You can filter, sort, and visualize the results — or write your own SQL using the custom SQL editor.

Table view​

By default, query results display as a sortable table.

  • Sortable columns — click any column header to sort ascending or descending
  • Sticky headers — column headers stay visible as you scroll down
  • Auto-formatting — currency values show $ with commas, percentages show %, and dates are human-readable
  • Row count — the total number of rows is displayed above the table

Exporting to Excel​

Click Export above the results to download the current query result as an .xlsx file. Columns keep real Excel number formats — currency, percent, and plain numbers — inferred the same way as the on-screen table, so the spreadsheet stays sortable and chartable.

Parametric controls​

Many queries declare parameters that surface as controls above the result panel. They re-run the query as you change them — no manual refresh needed.

  • Date Range — preset windows (7d, 30d, 90d, 180d, 1y) or a Custom start/end picker. Defaults to the last year.
  • Granularity — pivots the time axis to Day, Week, or Month. Only granularities the query supports are offered.
  • Locations — multi-select location filter. Leave empty for all locations.

Which controls appear depends on the query. A query that only needs a date range won't show a granularity toggle, and a query without a location parameter won't show the locations picker.

Switching queries​

Click a different query in the sidebar to run it. Your current filters and chart configuration reset when switching queries, but any saved looks are preserved.

URL sharing​

The current query and view mode are reflected in the URL. Copy the URL to share a specific query with your team — they'll see the same results (subject to their own data permissions).

tip

If you find yourself running the same query with the same chart settings repeatedly, save it as a Look for one-click access.

Custom SQL​

You can write and execute your own SQL queries against your synced data.

Opening the editor​

Click the Custom SQL button in the sidebar (below the query list) to open the SQL editor panel. The editor provides a resizable text area where you write raw SQL.

Running a query​

Click Run (or press the run button) to execute your SQL. Results appear in the same table/chart panel as pre-built queries — you get all the same filtering, charting, and sorting capabilities.

If your query has a syntax error or fails, the error message is displayed inline below the editor.

Saving custom SQL as a look​

Custom SQL queries can be saved as Looks just like pre-built queries. The SQL text is stored with the look, so loading it restores both the query and any chart configuration.

info

Custom SQL runs against the same DuckDB tables that power pre-built queries. Table and column names match your synced data schema.

Building on the semantic layer​

Where your organization has a semantic layer configured, the editor also sees a set of named views and functions that encode your business definitions once — what counts as a membership, a class pack, an intro offer, or a third-party (aggregator) visit. The pre-built reports are written on top of the same objects, so a custom query that uses them agrees with the reports by construction.

Start from visits_classified (every visit plus classification columns such as service_role, pack_size_tier, membership_plan, membership_access, visit_source) rather than matching service names yourself:

SELECT DATE_TRUNC('month', visit_date) AS month,
       COUNT(DISTINCT client_id)       AS attending_members
FROM visits_classified
WHERE signed_in AND service_role = 'membership'
GROUP BY 1 ORDER BY 1;

SELECT * FROM service_catalog lists every product and how it is classified. The full list of available objects, with their definitions, is returned by GET /queries/semantic.