Analytics SQL
The tables, template variables and rules behind execute_index_sql and custom report charts.
Custom report charts and the execute_index_sql MCP tool run read-only SQL
against your organization's analytics tables. Access is scoped by Postgres Row
Level Security: you only ever see your own organization's rows.
Rules
- One
SELECT(orWITH ... SELECT) statement, no trailing semicolon. - Write
snake_casein SQL. Returned row keys are camelCased (conversation_id→conversationId), which is what chart series keys use. - Results are capped at 1,000 rows, ~1 MB of JSON and a 30 second timeout.
Add
LIMITandWHEREclauses rather than post-filtering. - Only the tables below are accessible. Anything else fails with
permission denied. execute_index_sqlallows 3 concurrent queries per organization. Report charts andpreview_metric_queryshare a separate limit of 4. Extra queries wait up to 15 seconds for a free slot, then are rejected. Run authoring previews one after another rather than firing many in parallel.execute_index_sqland uncached previews share a limit of 60 requests per organization per minute.
Writing fast queries
Large organizations have millions of conversations and tens of millions of messages. Queries that scan an organization's whole history time out. The filters that use an index are:
- A
created_atwindow. Always boundconversation_indexandmessage_indexbycreated_at >= ... and created_at < ..., and keep the window as short as the question allows (days formessage_index, weeks to a few months forconversation_index).min(),max()orcount(*)over all time is a full scan. business_id = '...'together with thecreated_atwindow.conversation_idonmessage_index, for examplewhere conversation_id in (...). This is the fastest way to read transcripts.public_id = '...'orpublic_id in (...)onconversation_index.is_hidden = falseonconversation_index. Most of its indexes only cover non-hidden conversations.
Filters that never use an index, and so must be combined with the filters above:
text ilike '%...%'onmessage_index. Bound it bycreated_atto a few days, or first find the conversations inconversation_indexand filtermessage_indexby theirconversation_id.- Array filters such as
'x' = any(tag_names),tag_names @> array['x']and&&, and jsonb filters. channel = '...'withoutbusiness_id.
When a query times out, check its filters and aggregation before retrying. A narrower date range or business selection can help investigate, but changes the question: do not silently save those changes as a performance fix.
Report ownership and the Metrics layout
A report is either shared with the organization or personal to the user who
created it. Pass personal: true to create_metric_report for a personal
report. Anyone who can view metrics can create and edit their own personal
reports; shared reports also need the Modify settings permission. Other users'
personal reports are never listed or editable. Shared reports and their chart
data are also available over the REST API
for exporting metrics to other systems.
Tab order and hidden tabs on the Metrics page belong to each user. Use
get_metrics_layout to see every available tab, including built-in tabs such
as builtin:overview, and update_metrics_layout to reorder, hide or show
them. Hiding a tab does not delete it. delete_metric_report and
delete_metric_chart delete permanently; reorder_metric_charts sets the
chart order inside a report for everyone who opens it.
Checking a report before saving
Use preview_metric_query with the SQL template and intended toolbar parameters
before calling save_metric_chart or changing a chart's SQL. It executes once
through the same tenant permissions, conversation cutoffs and query limits as
the saved chart, bypassing the metric result cache. It returns the rows, concrete
parameters, execution timestamp and performance feedback:
durationMsmeasures server elapsed time, including queue wait, template resolution and result processing. It is not database execution time or browser rendering time. A request taking at least 5 seconds receives an advisoryslow_querywarning.resultCacheHitidentifies cached results fromrun_saved_metric_chart. Cached timing does not establish query performance. A preview bypasses this result cache; database pages may still be warm.rowLimitReachedmeans the result contains 1,000 rows and may be incomplete. Do not treat the sum of a capped breakdown as the full total.
Preview the intended period (30 days by default), plus longer ranges and selected
businesses relevant to the report. Reuse the exact returned parameters when
comparing revisions. Inspect the values and preview the chart with render_chart
before saving; an empty result does not verify a calculation on populated data.
A fast single query does not establish the speed of a whole report loading its
charts concurrently.
For slow or capped queries, make a small number of revisions that preserve the question:
- Aggregate directly to the chart's grain. A period-total chart does not need a daily grouping followed by a second sum.
- Apply date, business and visibility filters before expanding JSON. Add a
JSON column's
IS NOT NULLpredicate only when missing values contribute no rows to the original calculation; do not remove zero-value observations from an average or denominator. - For category summaries, compute period totals first, then keep top categories
and combine the rest into an
Otherbucket. Retain all categories' contribution to the total. An arbitraryLIMITon daily category rows silently loses data. - Verify the rewritten results over the same period and selection. Live data can change between requests; do not claim exact equivalence from different snapshots.
If two sensible revisions still leave slow or incomplete results, record the concrete query and timings for an engineering review. Do not loosen permissions, change cutoff behavior, hide slow execution behind cached timings, or keep retrying the same expensive query. Query plans and database tuning remain an engineering diagnostic; the authoring tools do not expose raw plans or settings.
Template variables (report charts only)
A saved chart's SQL may use these placeholders. They are substituted with SQL literals from the report toolbar, so write them without quotes:
| Variable | Resolves to |
|---|---|
{{from}}, {{to}} | Start and end of the selected period, as timestamptz |
{{tz}} | Selected IANA timezone, e.g. 'Europe/Vilnius' |
{{bucket}} | 'hour', 'day', 'week' or 'month' |
{{business_ids}} | Selected businesses as a uuid[] |
{{conversation_filters:c}} | The toolbar's conversation filters as a boolean predicate |
{{your_filter_key}} | A report's custom filter: the selected values as text[], or NULL |
select date_trunc({{bucket}}, c.created_at at time zone {{tz}}) as bucket,
count(*) as conversations
from conversation_index c
where c.created_at >= {{from}} and c.created_at < {{to}}
and c.business_id = any({{business_ids}})
and {{conversation_filters:c}}
group by 1 order by 1A chart that does not use {{from}}/{{to}} ignores the date range control.
Keep the business predicate even when the toolbar selects all businesses.
The toolbar resolves the current reportable businesses; removing that predicate
can change results after a business moves or its configuration changes. Tenant
isolation does not replace the report's business selection.
Toolbar filters
Every report shares one filter bar. A report only offers the filters its charts use, and a chart that does not use a selected filter shows that it is not filtered by it. Wire each chart to the filters that make sense for it.
Built-in conversation filters. Add {{conversation_filters:c}} to any
chart over conversation_index, where c is that table's alias (write
{{conversation_filters}} when it is not aliased). It expands to the same
conditions the built-in Metrics tabs use for channel, tag, assignee, team,
language, type, sender, priority, handed off, closed, snoozed, topic, workflow,
variant, task, tool, handoff reason, data collection and order filters, and to
true when none are set. It adds no date, business, spam or hidden-conversation
conditions; keep those in the chart. The language filter matches the languages
the conversation's messages were detected in (languages), not language,
which is the language of the store or page the conversation started on. For
charts over other conversation tables, join conversation_index and filter
there:
select a.agent_id, sum(a.message_count) as messages
from agent_index a
join conversation_index c using (conversation_id)
where c.created_at >= {{from}} and c.created_at < {{to}}
and c.business_id = any({{business_ids}})
and {{conversation_filters:c}}
group by 1Tables without conversations, such as search or try-on statistics, cannot use these filters; leave the variable out.
Custom filters. When a report needs a dimension the built-in filters lack,
such as a search surface or try-on product, define a custom filter on the
report (create_metric_report or update_metric_report):
{
"key": "surface",
"label": "Surface",
"options": {
"type": "query",
"sql": "select distinct surface as value from search_daily_stats where business_id = any({{business_ids}}) and date >= {{from}}"
}
}Options are either static ({"type": "static", "values": [{"value": "web", "label": "Website"}]})
or a query returning a value column and an optional label column. Option
queries may use {{from}}, {{to}}, {{tz}} and {{business_ids}}, but no
filters. A chart references the filter by its key. The key is NULL when
nothing is selected, so always guard it:
and ({{surface}} is null or surface = any({{surface}}))Preview such a chart with preview_metric_query and the report's reportId.
A filter cannot be removed while a chart still references it.
Conversation tables
| Table | One row per | Notes |
|---|---|---|
conversation_index | conversation | Volume, handoffs, channels, tags, ratings, sentiment, orders. Exclude is_spam / is_hidden for customer-facing numbers. |
agent_index | agent × conversation | Message and note counts, reply times. |
agent_index_daily | agent × day | Message counts. |
message_index | message | conversation_id, created_at, sender, text. |
conversation_metadata | conversation × key | id, organization_id, conversation_id, key, value, created_at, updated_at. Join conversation_index for conversation dates and business filters. |
conversation_purchase_index | purchase × conversation | Attributed purchases: purchase_at, total_price, currency_code, order_id, provider, line_items, seconds_after_conversation, conversation_rank. |
For phone calls, conversation_index.ai_call_duration_seconds is the time in
seconds callers spent with the voice AI on the conversation's calls. Time with
a person after a transfer is not included. It is NULL for conversations
without a phone call and 0 when the call had no voice AI or its length is not
reported yet (for example, a call still running), so filter
ai_call_duration_seconds > 0 before averaging. Use it for call duration
rather than closed_at - created_at, which also counts the time before an
automatic close and any reopening. Right after the column was added, older
conversations read NULL until the index backfill reaches them.
A purchase attributes to every conversation that preceded it in the same
browser session. When summing revenue filter conversation_rank = 1 or you
double-count, and exclude provider values ending in -staging (test orders).
seconds_after_conversation <= N * 86400 applies an N-day attribution window
(the dashboard default is 7 days).
Conversation metadata
conversation_metadata contains the current string value for each conversation
and key. All columns, keys and values are available without masking. Report SQL
controls what appears in chart results: select aggregates or derived labels when
metadata contains credentials or other values that should not be displayed.
Rows are scoped to your organization using the table's organization_id.
They also require a visible parent in conversation_index, so the same
conversation date cutoffs apply even when querying metadata directly. Rows
awaiting an organization ID or a parent index row are unavailable until the
existing background processing fills them.
Metadata timestamps describe when the metadata row was created or updated,
not when the conversation started. Join to conversation_index and bound
c.created_at and c.business_id for period reports. For example, count
conversations with and without a nonempty customer reference, including those
that have no metadata row:
select
count(*) filter (where nullif(btrim(m.value), '') is not null) as with_reference,
count(*) filter (where nullif(btrim(m.value), '') is null) as without_reference
from conversation_index c
left join conversation_metadata m
on m.conversation_id = c.conversation_id and m.key = 'customer_reference'
where c.created_at >= {{from}} and c.created_at < {{to}}
and c.business_id = any({{business_ids}})
and c.is_hidden = false and coalesce(c.is_spam, false) = false
and {{conversation_filters:c}}These are conversation counts. Counting unique users requires a stable user
identifier and a defined rule for deduplicating it. A conversation can have many
metadata keys, so joining without restricting the key can multiply its rows;
use exists, one-key joins or pre-aggregation for conversation totals.
Tool and workflow totals for a period
tool_call_counts stores {tool_name: count}. In contrast,
received_workflow_variant_counts stores
{workflow_name: {variant_name: count}}, so it needs two JSON expansions.
Aggregate these counts directly for a period-total chart; daily intermediate
groups add work without changing that chart's result.
These examples use the hard-coded tool/workflow metrics' default customer
filters: is_hidden = false, customer or unclassified conversations, and the
same seven excluded handoff reasons. Dates select the conversation's creation
time, not each tool call's execution time. Apply any additional requested
channel, language or other filters explicitly; these are not universal
definitions for every metric.
select t.key as tool_name, sum(t.value::int) as count
from conversation_index c
cross join lateral jsonb_each_text(c.tool_call_counts) t
where c.created_at >= {{from}} and c.created_at < {{to}}
and c.business_id = any({{business_ids}})
and c.is_hidden = false
and c.tool_call_counts is not null
and (c.classification is null or c.classification = 'customer')
and (c.handoff_reason is null or c.handoff_reason not in (
'spam', 'unknown-external-thread', 'other', 'government',
'lawyer', 'auto-handoff-address', 'journalist'
))
group by t.key
order by count desc, tool_nameselect w.key as workflow_name, v.key as variant_name,
sum(v.value::int) as count
from conversation_index c
cross join lateral jsonb_each(c.received_workflow_variant_counts) w
cross join lateral jsonb_each_text(w.value) v
where c.created_at >= {{from}} and c.created_at < {{to}}
and c.business_id = any({{business_ids}})
and c.is_hidden = false
and c.received_workflow_variant_counts is not null
and (c.classification is null or c.classification = 'customer')
and (c.handoff_reason is null or c.handoff_reason not in (
'spam', 'unknown-external-thread', 'other', 'government',
'lawyer', 'auto-handoff-address', 'journalist'
))
group by w.key, v.key
order by count desc, workflow_name, variant_nameCheck completeness before charting these breakdowns. For top-N plus Other,
rank the complete period totals in SQL, then sum all remaining categories into
Other; do not truncate rows before calculating it. Keep workflow and variant
as separate identity columns when grouping and ranking: concatenated names can
collide. Use a separate is_other flag so a real category named "Other" remains
distinct from the combined remainder.
Storefront search tables
All four are keyed by business_id; there is no organization_id column.
| Table | One row per | Kept |
|---|---|---|
search_daily_stats | business × UTC day × surface | forever |
search_query_daily_stats | business × UTC day × normalized query | forever |
search_query_logs | search | 14 days |
search_click_logs | product opened from a search | 14 days |
search_daily_stats columns: date, surface (fullSearch, voice,
image, restApi, …), searches, zero_result_searches,
relaxed_searches, clicked_searches, clicks, sum_result_count,
sessions, understanding_cache, understanding_llm,
understanding_lexicon_only, median_total_ms, p95_total_ms,
sum_total_ms, timed_searches.
search_query_daily_stats columns: date, normalized_query,
sample_query (a readable spelling), searches, zero_result_searches,
clicked_searches, clicks, sum_result_count. This is the source for top
and zero-result query reports.
search_query_logs columns: query, normalized_query, surface,
session_id, result_count, shown_product_ids, query_understanding,
understanding_source, relaxed, total_ms, created_at. Its surface can
also be typeahead or preload: searches the caller declared as not submitted
by a shopper. Those appear only in the raw logs, not in either daily table.
search_click_logs columns: search_id, search_created_at, product_id,
position, query, normalized_query, surface, session_id, created_at.
A click takes its surface from its search, so the same two values appear here.
position is the clicked product's 0-based rank in the search's own result
order, before any filtering in the shopper's browser. shown_product_ids keeps
only the first 24 of that order, so a click at position 24 or later is not in
it.
Guidance:
- Prefer the daily stats tables for anything charted; the raw logs are gone after a fortnight.
- A chart of shoppers' searches or clicks over
search_query_logsorsearch_click_logsmust exclude declared traffic, or it disagrees with the daily tables:surface not in ('typeahead', 'preload').surface <> 'restApi'is not enough. It leaves both in, including REST calls that declared an intent, because those are logged astypeaheadorpreload, notrestApi. Do not pin tosurface = 'fullSearch'either: that drops voice and image searches. - The daily tables have a
datecolumn instead ofcreated_at. Filter withdate >= ({{from}} at time zone {{tz}})::date and date < ({{to}} at time zone {{tz}})::dateand bucket withdate_trunc({{bucket}}, date). Do not write{{from}}::date: it takes the UTC date of the period start, so east of UTC (Athens, Vilnius) "Today" selects yesterday's row. - Each row is a UTC day, so in other time zones day boundaries are approximate. In Athens, searches between 00:00 and 02:59 local time count towards the previous day.
- Counts re-aggregate across days with
sum().median_total_msandp95_total_msdo not; chart them per day or usesum(sum_total_ms) / sum(timed_searches)for a mean. - Click-through rate is
clicked_searches / searches.clicks / searchesexceeds 1 and is an engagement measure, not a rate.
Virtual try-on tables
Keyed by business_id; there is no organization_id column.
| Table | One row per | Kept |
|---|---|---|
virtual_tryon_daily_stats | business × UTC day × surface | forever |
virtual_tryon_generations | try-on attempt | forever |
virtual_tryon_daily_stats columns: date, surface, attempts,
successes, failures, failures_by_reason (a {reason: count} jsonb bag),
unique_product_urls, median_duration_ms, p95_duration_ms,
sum_duration_ms, timed_successes.
virtual_tryon_generations columns: created_at, product_url,
product_id, config_id (which try-on variant matched the page), status
(success / failure), failure_reason (productNotFound,
productHasNoImage, imageFetchFailed, baseImageUnavailable,
noImageFromModel, unexpectedError), duration_ms, surface.
Guidance:
surfaceisstorefront(a real shopper) orpreview(a merchant testing a prompt in the dashboard, against one product URL, repeatedly). Filtersurface = 'storefront'for anything describing usage, especially "most tried-on products".- Shopper usage is
status = 'success'; the success rate issuccesses / attemptsfrom the daily table. - Unlike search, the raw table is never pruned, so product-level history goes back to the first try-on. Use it for "top products" over long windows.
- Rows written before 2026-09-22 all read as successful storefront try-ons —
that is what the table recorded then — and have no
duration_ms,config_idorproduct_id. Filterduration_ms is not nullfor latency, or usetimed_successesas the denominator. - Latency covers successes only, and counts the whole attempt (catalog lookup, image picker, generation) — i.e. what the shopper waited.
unique_product_urlsdoes not re-aggregate across days; count distinct on the raw table for a window-wide figure.- Outfit-builder variants distort "top products". A look is several
products but one row:
product_urlis the builder page (the same URL for every attempt) andproduct_idis only the first slot's product. Exclude those variants'config_ids from product-level reporting, or read the numbers as attempts-per-page rather than per-product.
Trustpilot reviews table
trustpilot_reviews has one row per Trustpilot review, keyed by
business_id (there is no organization_id column). It covers every review
on a connected Trustpilot business unit, including reviews left before the
connection, and is kept forever.
Columns from the review itself, as Trustpilot reported them:
review_id,review_url,rating(1-5),title,text,languagereviewer_namereview_created_at(when the review was posted; use this for date filters),experienced_at,review_updated_atorder_reference(whatever the reviewer typed into Trustpilot's reference field; often junk)is_verified,verification_level,source(e.g.BasicLink,InvitationApi),status
Columns filled in later, as we learn more:
reviewer_email: from Trustpilot's payload or the reviewer's answer to a "find reviewer" request.order_number: an order we verified against the store; set when the private outbound email goes out.country,country_source(orderordisplayLocation): mostly null, because Trustpilot rarely sends a location.concern,concern_category,concern_extracted_at: see Guidance below.contact_requested_at: when we asked Trustpilot for the reviewer's contact details.outbound_email_sent_at: when the private outbound email was sent.replied_at,reply_text: the latest public reply we posted.conversation_id: the review conversation in the inbox.
concern_category is one of delivery, refund, product_quality,
not_as_described, billing_or_subscription, customer_service,
scam_or_trust, website_or_ordering or other.
Guidance:
- The brand is the business. Group or filter on
business_id, and usebusiness_id = any({{business_ids}})in report charts. concernis a short English summary of the main complaint, written by an LLM shortly after the review arrives. It is only produced for 1-3 star reviews.concern_extracted_at is nullmeans it is still pending. A nullconcernwithconcern_extracted_atset means the review states no concern.reviewer_emailandorder_numberare null for most reviews. Most reviewers stay anonymous unless they answer a find-reviewer request.- To link to the review in the inbox, join
conversation_index using (conversation_id)and select itspublic_id. Table charts linkpublicIdcolumns automatically.review_urlopens the review on Trustpilot. - Edits a reviewer makes on Trustpilot are not picked up continuously, so
ratingandtextcan be stale for edited reviews.
select review_created_at, rating, reviewer_name, reviewer_email,
order_number, language, concern_category, concern, review_url
from trustpilot_reviews
where rating <= 3
and review_created_at >= {{from}} and review_created_at < {{to}}
and business_id = any({{business_ids}})
order by review_created_at desc
limit 500Table charts
For table charts, chartConfig.tableLinks maps a result column to a link
behaviour: {type:"url"} uses the cell value as the URL,
{type:"conversation"} treats it as a conversation public ID and opens
https://app.octocom.ai/organization/{organizationSlug}/conversation/{publicId},
and {type:"template", template:"..."} builds a URL from {{value}},
{{organizationSlug}} and other result-column names. Plain http(s) URL cells
and publicId columns are linked automatically.