Skip to content
LogoLogo

How to run an analytics query

This guide gives you a query for each common analytics question. Copy one, put your own project ID in the WHERE clause, and run it.

Every query must carry a tenant filter. See About PickedQL for why, and Data sources for every table.

Daily A/B test trend

SELECT
  project_local_date,
  control_users()                  AS control,
  variant_users()                  AS variant
FROM metrics
WHERE project_id = '{{ your project ID }}'
  AND project_local_date >= '2026-04-01'
GROUP BY project_local_date
ORDER BY project_local_date

Result:

project_local_datecontrolvariant
2026-04-114361101
2026-04-124178126
2026-04-135008176

Conversion rate uplift by date

SELECT
  project_local_date,
  control_conversion_rate()        AS control_conversion_rate,
  variant_conversion_rate()        AS variant_conversion_rate,
  conversion_rate_uplift()         AS conversion_rate_uplift_pct
FROM metrics
WHERE project_id = '{{ your project ID }}'
  AND project_local_date >= '2026-04-01'
GROUP BY project_local_date
ORDER BY project_local_date

Result:

project_local_datecontrol_conversion_ratevariant_conversion_rateconversion_rate_uplift_pct
2026-04-010.03220.03490.0349
2026-04-020.03980.04160.0416
2026-04-030.04620-0.0001

Custom conversion rate definition

Override the default conversion, which counts a purchase event, with your own numerator and denominator. Use this when a form submission, a lead capture, or another event of your own defines a conversion.

-- Lead capture conversion: form submissions / lead-capture step entries
SELECT
  project_local_date,
  control_conversion_rate(
    numerator='event_type=form_submit_success',
    denominator='step_id=lead-capture-step'
  ) AS control_lead_cr,
  variant_conversion_rate(
    numerator='event_type=form_submit_success',
    denominator='step_id=lead-capture-step'
  ) AS variant_lead_cr
FROM events
WHERE project_id = '{{ your project ID }}'
  AND client_local_date >= '2026-04-01'
GROUP BY project_local_date
ORDER BY project_local_date

You can also combine multiple events with OR and group with parentheses:

-- A `checkout_complete` event or an `order_create` event counts as a conversion.
SELECT
  control_conversion_rate(
    'event_type=checkout_complete OR event_type=order_create'
  ) AS control_cr,
  variant_conversion_rate(
    'event_type=checkout_complete OR event_type=order_create'
  ) AS variant_cr
FROM events
WHERE project_id = '{{ your project ID }}'

Your own segment with control_users and variant_users

On events, four functions take an optional filter string: control_users, variant_users, control_sessions, and variant_sessions. The filter counts only a user or a session that matches your expression. It applies together with the variant_type filter that PickedQL adds.

-- The control users who fired an `app_initialize` event, by step.
SELECT
  step_id,
  control_users('event_type=app_initialize')                      AS control_initialized,
  variant_users('event_type=app_initialize')                      AS variant_initialized
FROM events
WHERE project_id = '{{ your project ID }}'
  AND client_local_date >= '2026-04-01'
GROUP BY step_id
ORDER BY control_initialized DESC

The named form (filter=) is also supported:

SELECT control_users(filter='event_type=form_submit AND step_id=lead-capture') FROM events
WHERE project_id = '{{ your project ID }}'

Conversion rate from nested virtual functions

The numerator and the denominator can come from different segments. An example is the control users who fired an event of your own, divided by all users and not by the control users alone. For that, pass a virtual function call directly as an argument of the conversion rate:

-- The control users who fired `app_initialize`, over all users of every variant.
SELECT
  control_conversion_rate(
    control_users('event_type=app_initialize'),
    all_users()
  ) AS control_initialize_rate
FROM events
WHERE project_id = '{{ your project ID }}'
  AND client_local_date >= '2026-04-01'

A/B test by device

SELECT
  device_category,
  control_users()                  AS control,
  variant_users()                  AS variant
FROM metrics
WHERE project_id = '{{ your project ID }}'
GROUP BY device_category

Result:

device_categorycontrolvariant
Phone1934301723
Desktop23423309
Tablet1329465

Daily A/B test trend by device

Combine date and device dimensions to spot device-specific trends:

SELECT
  project_local_date,
  device_category,
  control_users()                  AS control,
  variant_users()                  AS variant
FROM metrics
WHERE project_id = '{{ your project ID }}'
  AND project_local_date >= '2026-04-01'
GROUP BY project_local_date, device_category
ORDER BY project_local_date, device_category

Result:

project_local_datedevice_categorycontrolvariant
2026-04-13Tablet1752
2026-04-14Desktop371
2026-04-14Phone2441

Purchase by UTM source

SELECT
  utm_source,
  all_users()                      AS users,
  all_purchase_users()             AS purchasers
FROM metrics
WHERE project_id = '{{ your project ID }}'
GROUP BY utm_source

Result:

utm_sourceuserspurchasers
meta12031101
fb365631
an100366299

Step funnel for variant users

Count unique variant users per step to understand the conversion funnel:

SELECT
  step_id,
  variant_users()                    AS variant
FROM metrics
WHERE project_id = '{{ your project ID }}'
  AND project_local_date >= '2026-04-01'
GROUP BY step_id
ORDER BY variant DESC

Result:

step_idvariant
who-are-you-protecting-step1093
about-yourself-step533
date-of-birth-step435
employment-status-step408
nicotine-usage-step407
coverage-motivation-step405
lead-capture-step398

Sankey — overall user journey

A forward window from the starting step. A user who never reaches targeting_step still gives edges, up to num_nodes_after_start.

SELECT source, target, users FROM experience_journey
WHERE project_id            = '{{ your project ID }}'
  AND starting_step         = '{{ starting step ID }}'
  AND targeting_step        = '{{ target step ID }}'
  AND num_nodes_after_start = 10

Result:

sourcetargetusers
001. step-start002. step-questionnaire-1182
002. step-questionnaire-1003. step-quote144
003. step-quote004. step-target121

Sankey — the top paths to a target step

A backward window anchored at the target. It also ranks the paths and keeps the ones with the most users.

SELECT source, target, users FROM experience_journey
WHERE project_id              = '{{ your project ID }}'
  AND experience_id           = '{{ experience ID }}'
  AND starting_step           = '{{ starting step ID }}'
  AND targeting_step          = '{{ target step ID }}'
  AND num_nodes_before_target = 5
  AND top_n_path              = 10

Sankey — drop-off view

Starts at starting_step and emits every consecutive transition until num_nodes_after_start steps later (no target).

SELECT source, target, users FROM experience_journey
WHERE project_id            = '{{ your project ID }}'
  AND experience_id         = '{{ experience ID }}'
  AND starting_step         = '{{ starting step ID }}'
  AND num_nodes_after_start = 10

List all control users for mobile (user-level)

Use events when you need data at the individual user level:

SELECT
  device_category,
  user_canonical_id
FROM events
WHERE project_id = '{{ your project ID }}'
  AND device_category = 'Mobile'
  AND variant_type = 'Control'

Result:

device_categoryuser_canonical_id
Mobile019cc336-5a6a-7c32-b638-cdcc983b3e70
Mobile019cc336-5a6a-7d8f-994c-69bc28e5f550
Mobile019cc80f-503c-7181-b2c6-986b1f76d496