Skip to content
LogoLogo

Data sources

PickedQL gives you four virtual tables. Each table is fast for one kind of work. It also gives you three reference tables, and those hold the options of your Picked project.

metrics — data that PickedQL aggregates each day

Use it for a dashboard and to study a trend. There is one row for each unique set of dimensions on each day. The dimension columns are there directly, and they need no join. Examples are device_category, country_code, and utm_source.

SELECT device_category, control_users(), variant_users()
FROM metrics
WHERE project_id = 'your-project-id'
GROUP BY device_category

events — one row for each event of each user

Use it for a deep study, for a funnel, and for a breakdown of your own. There is one row for each event of each user. PickedQL joins a dimension column, such as device_category or country_code, when you point at it.

SELECT user_canonical_id, device_category
FROM events
WHERE project_id = 'your-project-id'
  AND variant_type = 'Control'

features — one row for each feature flag

Use it to count the users, the sessions, and the events for each feature flag and bucket. It uses parameterized functions.

SELECT feature_users('homepage_test', 'control')
FROM features
WHERE project_id = 'your-project-id'

experience_journey — step transitions for a sankey chart

It returns one source, target, users row for each edge. You can put those rows directly into a sankey chart or a funnel chart. It holds the eight common table expressions that a card carried before. Those call lagInFrame, remove a duplicate between two steps that follow each other, and rank the top paths.

Every filter goes in the outer WHERE clause. PickedQL reads each one as a parameter and puts it into the query below. Only project_id is necessary. The rest are optional, and each has a sensible default.

Parameter, as a WHERE keyWhat it does
project_idThe tenant filter. It is necessary.
experience_idLimits the rows to one fact_events.experience_id.
starting_stepAnchors each edge at the first time that a user reaches this step_id. With no value, it anchors at the first step_enter event of that user.
targeting_stepTrims the window of each user at the first time that the user reaches this step_id. A user who never reaches it still gives a journey, up to num_nodes_after_start.
num_nodes_after_startThe size of the forward window from the start step.
num_nodes_before_targetThe size of the backward window from the target step. It needs targeting_step.
top_n_pathKeeps the paths with the most users, and drops the rest.
user_cohort_eventLimits the rows to a user with at least one fact_events row of this event_type, such as 'lead_capture'. Use it for the paths of the users who reached a goal.
client_local_dateA filter on the date. It takes =, <, <=, >, >=, and BETWEEN. PickedQL puts it into the fact_events read below.
event_timestampA filter on the timestamp from the server. It takes the same operators as client_local_date.

When you set both num_nodes_after_start and num_nodes_before_target, both tests apply. The result is the intersection of the two.

-- The "overall user journey" sankey.
-- It uses a forward window. The target step is optional.
SELECT source, target, users FROM experience_journey
WHERE project_id           = 'your-project-id'
  AND starting_step        = 'step-start'
  AND targeting_step       = 'step-target'
  AND num_nodes_after_start = 10
  AND client_local_date   >= '2026-04-01'    -- An optional range of dates.
  AND client_local_date   <= '2026-04-30'
-- The "paths to a target" sankey.
-- It uses a backward window and it ranks the top paths.
SELECT source, target, users FROM experience_journey
WHERE project_id             = 'your-project-id'
  AND experience_id          = 'exp-123'
  AND starting_step          = 'step-start'
  AND targeting_step         = 'step-target'
  AND num_nodes_before_target = 5
  AND top_n_path             = 10
-- The "drop-off" sankey. Leave the target step out.
SELECT source, target, users FROM experience_journey
WHERE project_id           = 'your-project-id'
  AND experience_id        = 'exp-123'
  AND starting_step        = 'step-start'
  AND num_nodes_after_start = 10
-- A cohort sankey. It holds the users who reached the goal only.
SELECT source, target, users FROM experience_journey
WHERE project_id           = 'your-project-id'
  AND starting_step        = 'step-start'
  AND targeting_step       = 'step-target'
  AND num_nodes_after_start = 10
  AND user_cohort_event    = 'lead_capture'   -- Users with one or more of these events.

The columns that it returns:

ColumnTypeDescription
sourceStringThe start of the edge, in the form NN. <step title>, such as 001. Welcome screen. PickedQL joins steps to find the title.
targetStringThe end of the edge, in the form NN. <step title>.
usersUInt64The number of different user_canonical_id values that went along this edge.

Reference tables — steps, experiences, projects

These reference tables hold the configuration of your Picked project. They have two uses:

  • PickedQL joins them onto events when you point at one of their analyst columns: step_title, experience_title, project_name, or project_timezone.
  • You can read them directly. Each one then needs its own tenant filter, so that you see only the rows of a project that you can reach.
SourceThe tenant filter that it needs
stepsWHERE project_id = '<project_id>'
experiencesWHERE project_id = '<project_id>'
projectsWHERE id = '<project_id>'
-- A direct query that reads the experiences of a project.
SELECT id, title FROM experiences WHERE project_id = 'your-project-id'
 
-- A direct query that reads the metadata of one project.
SELECT id, name, timezone FROM projects WHERE id = 'your-project-id'
-- Pointed at from events. PickedQL adds each LEFT JOIN for you.
-- The reference tables need no separate tenant filter, because
-- the event table already limits the join.
SELECT experience_title, step_title, count(*)
FROM events
WHERE project_id = 'your-project-id'
GROUP BY experience_title, step_title

See Schema, reference tables for the full column list and the join keys.