How do I query my data with SQL?

Short answer

Open All-platform analytics › Behavior › Behavior analysis and switch to the SQL tab. Write one SELECT (or WITH … SELECT) against the views v_events, v_sessions, v_persons or v_autocapture. They are already limited to the current project and the selected date range. Run with Command-Enter or Ctrl-Enter.

Use SQL for cross-tabs and one-off lists the built-in models do not give you. For a recurring ratio, save a custom metric instead.

The four views

subject is the person, visitor or account, depending on the identity chosen in the builder.

Two example queries

Sessions and engaged sessions by channel and device:

SELECT channel, device_type,
       COUNT(*) AS sessions,
       SUM(CASE WHEN engaged = 1 THEN 1 ELSE 0 END) AS engaged_sessions
FROM v_sessions
GROUP BY channel, device_type
ORDER BY sessions DESC

Entry pages of sessions that included a sign-up:

WITH signups AS (
  SELECT DISTINCT session_id FROM v_events WHERE name = 'signup'
)
SELECT s.entry_path, COUNT(*) AS sessions
FROM v_sessions s
JOIN signups g ON s.id = g.session_id
GROUP BY s.entry_path
ORDER BY sessions DESC
LIMIT 20

To apply the builder's global filters, put {filters} in a WHERE clause: WHERE {filters}. It becomes a constant true when there are no filters, and only works on columns the view has. Return rewritten SQL shows the statement that actually ran.

Limits

• One SELECT or WITH statement. Comments, semicolons, backticks, writes and system tables are rejected, as are real table names.
• At most 50,000 rows returned (the footer says when the result was cut), 30-second timeout, 20,000 characters of SQL.
• No breakdown control and no compare period; segment comparison is rejected.
• Members with hidden properties cannot run SQL.

Common errors

Need a file for someone else to download later? Use an export job; see export raw data.