Report queries

A report query describes a report in a few lines of text. The language is called RepzoQL. It’s another way to build the same reports as the report builder: every query fills the builder, and every builder report can be shown as a query. If you have used ShopifyQL or SQL, it will feel familiar.

FROM deals
SHOW count, won_value
GROUP BY owner
WHERE stage = "Proposal" OR stage = "Negotiation"
DURING this_quarter
COMPARE TO previous_quarter

Getting started

Each step answers a question by adding one clause to the query before it.

How many deals do we have? Start with the object and what to count. See FROM and SHOW.

FROM deals
SHOW count

How much did we win this quarter? Add a metric and a period. See SINCE, UNTIL, DURING.

FROM deals
SHOW count, won_value
DURING this_quarter
ON won_at

Who won it? Split the numbers by a field. See GROUP BY.

FROM deals
SHOW count, won_value
GROUP BY owner
DURING this_quarter
ON won_at

Only deals from the website? Filter the records. See WHERE.

FROM deals
SHOW count, won_value
GROUP BY owner
WHERE contacts.source = "Website"
DURING this_quarter
ON won_at

Better or worse than last quarter? Compare periods and sort. See COMPARE TO and ORDER BY.

FROM deals
SHOW count, won_value
GROUP BY owner
WHERE contacts.source = "Website"
DURING this_quarter
ON won_at
COMPARE TO previous_quarter
ORDER BY won_value DESC

Running queries

In the report page

Open Analytics → Reports and start or open a Freeform report. The query sits above the chart; while the report is empty it’s just the Ask Fox box, and the arrow (or ⌘ E) opens the editor. It updates as you change the builder on the right, and the builder follows the query when you run it with ⌘ Enter. Controls at the top hides the right-hand panel to give the chart the full width.

The editor helps as you type:

• Suggestions open on a new line, after a space, and after you pick one, with a short description under each. The selected one shows faded after the cursor; Tab or Enter takes it.
• Mistakes are underlined, with a fix such as “Did you mean count?”.
• The book icon lists the metrics and fields of each object; click a name to add it.
• The ? icon opens this documentation.

Cohorts is the report page’s second mode, for retention reports without writing a query. See COHORT.

Ask Fox

Describe a report in the box under the query, such as “average won deal by owner, only owners with at least 3 deals”. Fox writes the query, explains what it built in one sentence, and runs it. Ask again to change it, for example “split it by month” or “compare with last year”. Fox in the side panel answers number questions the same way, with your own permissions and data visibility.

Saving, sharing, and links

A report written as a query is saved with its query. When it’s opened, the query runs again, so me means the person viewing it and -30d means the last 30 days from today. The same is true for dashboard widgets added from the report: on a shared dashboard each person sees their own numbers.

The address of a report keeps its query (?q=), so you can send the link to a colleague.

Using queries from the API

Integrations can run a query with POST /api/v1/reports/query and the reports:read scope. See Developer API for keys and scopes.

{ "query": "FROM deals SHOW count, won_value GROUP BY owner DURING this_quarter WITH TOTALS" }

The response has the compiled report, its rows, the comparison rows when the query compares periods, and the totals. A mistake returns a validation error with its line, column, and suggestions.

Queries also build saved reports and dashboard widgets (reports:write):

Call	Body
POST /api/v1/reports	{ "name": "Won by owner", "query": "FROM deals SHOW won_value GROUP BY owner" }
POST /api/v1/dashboards	{ "name": "Sales", "template": "sales" } (or blank, default)
POST /api/v1/dashboards/{id}/widgets	{ "title": "Revenue trend", "query": "FROM deals SHOW won_value TIMESERIES month DURING last_12_months", "ownPeriod": true }

A widget follows the dashboard’s date range unless ownPeriod is set, in which case it uses the query’s own DURING and COMPARE TO. GET /api/v1/dashboards/{id} lists each widget with its query, so you can run it with POST /api/v1/reports/query. The Repzo CLI has the same commands.

Related records

Reports can mix objects that are linked to each other. Write the related object’s name and a dot:

-- Deals by their account's industry, for contacts from the website
FROM deals
SHOW count, value
GROUP BY accounts.industry
WHERE contacts.source = "Website"

-- Contacts with their deals and orders side by side
FROM contacts
SHOW count, deals.count, deals.won_value, orders.total
GROUP BY source

A related object follows every link between the two records. To follow one specific reference, use its field name instead, for example GROUP BY decision_maker.title on deals.

Deals, contacts, accounts, activities, price offers, invoices, tickets, projects, appointments, requests, orders, carts, subscriptions, listings, and campaigns can be combined. Products, product variants, line items, articles, forms, and suppliers are reported on their own. Each object keeps its own permissions and data visibility. See Objects.

Parts of a query

Clause	What it does
FROM and SHOW	The object, and the numbers to show
WHERE	Only count matching records
GROUP BY	Split the numbers by fields
TIMESERIES	Split by time, with no gaps
SINCE, UNTIL, DURING	The period, and which date it uses
COMPARE TO	A second period to compare with
WITH	Totals, percent change, running totals, currency, time zone
HAVING	Keep only groups whose numbers match
ORDER BY and LIMIT	Sort and page the rows
COHORT	Follow people from their first record
VISUALIZE	Pick the chart

The syntax overview covers names, values, comments, and errors; Functions covers sum(), math, and date functions.

Related pages

• Reports
• Dashboard
• Properties
• Developer API