Skip to main content

Workspace

The User Behavior Insights Workspace is an in-app SQL editor for exploring the queries and events you've tracked. It's the natural starting point when you want to answer questions like "which queries had no clicks last week?", "what's the click-through rate by application?", or "show me the most common follow-up queries after a zero-result search."

The Workspace is purely an exploration tool — it reads from your existing UBI data, it doesn't write or transform it. For ingestion, see Track Queries and Track Events.

What you can do​

  • Write SQL against the ClickHouse tables that hold UBI data.
  • Browse the schema — tables, columns, types — without leaving the editor.
  • Preview a table with one click, or view its CREATE TABLE DDL.
  • Keep multiple queries open in tabs, each with its own results.
  • Save tabs automatically per member, so they're still there when you come back.

The Workspace uses ClickHouse SQL syntax. If you're new to it, the ClickHouse SQL reference is the canonical guide; ClickHouse extends standard SQL with operators and functions that are particularly useful for analytics (e.g. arrayJoin, groupArray, quantile).

Layout​

The Workspace is split into three regions:

  • Schema explorer (left) — the list of UBI tables. Click a table name to see its columns and types. The "preview" action opens a SELECT * … LIMIT 100 query in a new tab; "show DDL" opens the CREATE TABLE statement in a new tab.
  • Editor (top right) — the SQL editor with autocomplete and formatting. Tabs sit above it; click + to add a tab, click the close button on a tab to remove it.
  • Results (bottom right) — the table of results returned by the most recent query in the current tab, with pagination and a click-to-expand cell drawer for long values like JSON.

Running a query​

Type SQL into the editor and click Run, or press Ctrl/Cmd + Enter. If you have text selected when you run, only the selection executes — useful when one tab holds several queries you want to run independently.

Errors return inline in the results area with the ClickHouse error message. Common cases:

  • "Unknown identifier" — usually a typo'd column name. Use the schema explorer to confirm.
  • "Cannot parse" — a syntax error; the message points at the offending token.
  • "Memory limit exceeded" — narrow the query (add a WHERE, project fewer columns, or use LIMIT).

Editor features​

  • Autocomplete — table and column names are suggested as you type. Hit Ctrl + Space to trigger suggestions manually.
  • Formatting — Ctrl/Cmd + Shift + F reformats the current query (or selection) using the built-in SQL formatter.
  • Save — Ctrl/Cmd + S flushes pending tab changes to the server immediately. Tabs also auto-save shortly after you stop typing, when you switch tabs, and on browser close.

Tabs​

Tabs are stored per member, so every Workspace user has their own set. Each tab keeps:

  • Its name (rename inline by clicking the title field).
  • Its SQL content.
  • Its order within the tab strip.

Results, pagination state, and errors are kept in memory only — running the query again on a fresh session is cheap.

A small indicator next to the tab name shows the save state: unsaved, saving, saved, or error. If a save fails (e.g. network drop), click the indicator to retry.

Inspecting a cell value​

Clicking a cell in the results table opens a drawer showing the full value, formatted appropriately for its type. This is particularly useful for JSON, Array(...), and Map(...) columns where the inline rendering is truncated for readability.

Common starting points​

A few queries that work as templates against the standard UBI schema:

-- Search volume per day
SELECT toDate(timestamp) AS day, count() AS searches
FROM queries
GROUP BY day
ORDER BY day;

-- Queries with no downstream events (zero engagement)
SELECT q.user_query, q.timestamp
FROM queries AS q
LEFT JOIN events AS e USING (query_id)
WHERE e.query_id IS NULL
ORDER BY q.timestamp DESC
LIMIT 100;

-- Click-through rate by application
SELECT
  q.application,
  count(DISTINCT q.query_id) AS searches,
  countIf(e.action_name = 'click_through') AS clicks,
  clicks / searches AS ctr
FROM queries AS q
LEFT JOIN events AS e USING (query_id)
GROUP BY q.application
ORDER BY searches DESC;

Replace queries and events with the actual table names shown in the schema explorer for your deployment.

Programmatic access​

The Workspace runs SQL through POST /api/v1/ubi/query, the same endpoint you can call directly from your own scripts:

curl -X POST "https://${RELEVAL_HOST}/api/v1/ubi/query" \
  -H 'Content-Type: application/json' \
  -H "Authorization: Bearer ${TOKEN}" \
  -d '{
    "query": "SELECT count() FROM queries WHERE timestamp >= now() - INTERVAL 7 DAY"
  }'

The response includes a meta array describing the columns, a data array of rows, and basic execution statistics. Use this for scheduled reporting, dashboards, or pulling UBI data into a notebook.