Schema

Column reference for each PickedQL data source.

`metrics`

Pre-aggregated daily data. One row per unique combination of dimensions per day.

Column	Type	Description
`project_id`	`String`	Project identifier (required in `WHERE`)
`project_local_date`	`Date`	Date in project timezone
`experience_id`	`String`	Experience identifier
`step_id`	`String`	Step identifier
`event_type`	`String`	Event type (for example `page_view`, `click`)
`interaction_type`	`String`	Interaction type (for example `Button Click`)
`variant_type`	`String`	`Control`, `Variant`, or `Unknown`
`device_category`	`String`	Device category (for example `Desktop`, `Mobile`)
`country_code`	`String`	ISO country code
`utm_source`	`String`	UTM source
`utm_medium`	`String`	UTM medium
`product_name`	`String`	Product name
`product_category`	`String`	Product category
`landing_page_url`	`String`	Landing page URL

`events`

User-level event data. One row per event per user. Dimension columns are joined automatically when referenced.

Native columns

Column	Type	Description
`project_id`	`String`	Project identifier (required in `WHERE`)
`event_id`	`String`	Unique event identifier
`user_canonical_id`	`String`	Canonical user identifier
`session_id`	`String`	Session identifier
`experience_id`	`String`	Experience identifier
`step_id`	`String`	Step identifier
`event_type`	`String`	Event type (for example `page_view`, `click`)
`interaction_type`	`String`	Interaction type (for example `Button Click`)
`client_local_date`	`Date`	Date in client timezone
`is_purchase`	`UInt8`	`1` if checkout complete event
`is_cart_add`	`UInt8`	`1` if cart add event
`is_first_session`	`UInt8`	`1` if first session for this user
`landing_page_url`	`String`	Landing page URL
`features`	Map(String, String)	Feature flag assignments
`feature_keys`	Map(String, String)	Feature key assignments
`event_meta`	`String`	Event metadata

Dimension columns (auto-joined)

These columns are joined automatically when referenced — no JOIN syntax needed.

Column	Source	Description
`variant_type`	`dim_variant`	`Control`, `Variant`, or `Unknown`
`ab_feature_name`	`dim_variant`	A/B feature name
`ab_feature_value`	`dim_variant`	A/B feature value
`device_category`	`dim_device`	Device category
`browser_name`	`dim_device`	Browser name
`browser_version`	`dim_device`	Browser version
`os_name`	`dim_device`	The name of the operating system
`os_version`	`dim_device`	The version of the operating system
`platform`	`dim_device`	Platform
`country_code`	`dim_geo`	ISO country code
`country_name`	`dim_geo`	Country name
`region`	`dim_geo`	Region
`city`	`dim_geo`	City
`utm_source`	`dim_utm`	UTM source
`utm_medium`	`dim_utm`	UTM medium
`utm_campaign`	`dim_utm`	UTM campaign
`utm_term`	`dim_utm`	UTM term
`utm_content`	`dim_utm`	UTM content
`product_name`	`dim_product`	Product name
`product_category`	`dim_product`	Product category
`product_page`	`dim_product`	Product page
`step_title`	`steps`	Step title (joined on `project_id` + `experience_id` + `step_id`)
`experience_title`	`experiences`	Experience title (joined on `project_id` + `experience_id`)
`project_name`	`projects`	Project name
`project_timezone`	`projects`	Project timezone

`steps`, `experiences`, `projects`

These reference tables hold the configuration of your Picked project. PickedQL joins them onto events when you point at one of their analyst-facing columns. Each one needs its own tenant filter when you read it directly.

Source	Real columns	Analyst columns, when you point at them from `events`	Tenant filter, when you read the table directly
`steps`	`id`, `title`, `experience_id`, `project_id`, `organization_id`	`step_title`	`project_id = '…'`
`experiences`	`id`, `title`, `project_id`, `organization_id`	`experience_title`	`project_id = '…'`
`projects`	`id`, `name`, `timezone`, `organization_id`	`project_name`, `project_timezone`	`id = '<project_id>'`

When queried directly, use the real column names:

-- List all experiences for a project
SELECT id, title FROM experiences WHERE project_id = 'your-project-id'

When referenced from events, use the prefixed analyst names — joins are injected automatically:

SELECT experience_title, step_title, count(*)
FROM events
WHERE project_id = 'your-project-id'
GROUP BY experience_title, step_title

`features`

The features table gives you parameterized virtual functions. Use them to read which visitors each feature flag holds:

Function	Description
`feature_users('name', 'bucket' [, 'key'])`	Unique users in a feature bucket
`feature_sessions('name', 'bucket' [, 'key'])`	Unique sessions in a feature bucket
`feature_events('name', 'bucket' [, 'key'])`	Unique events in a feature bucket

`experience_journey`

This table gives one edge for each step transition, and PickedQL aggregates them first. Use the edges in a sankey chart or a funnel chart. Give each parameter in the outer WHERE clause. PickedQL reads them and puts them into the query below.

Columns

Column	Type	Description
`source`	`String`	Edge start, formatted as `NN. <step title>` (for example `001. Welcome screen`)
`target`	`String`	Edge end, formatted as `NN. <step title>`.
`users`	`UInt64`	Distinct `user_canonical_id` count that traversed this edge.

Parameters, in the `WHERE` clause

Key	Type	Required	Purpose
`project_id`	`String`	✓	The tenant filter.
`experience_id`	`String`		Limits the rows to one `fact_events.experience_id`.
`starting_step`	`String`		Anchors each edge at the first time that a user reaches this `step_id`. The default is the first step of each user.
`targeting_step`	`String`		Trims the window of each user at the first time that the user reaches this `step_id`. A user who never reaches it still gives the journey up to that point.
`num_nodes_after_start`	Integer		The forward window from the start. It keeps a step where `seq_pos < start_seq + N`.
`num_nodes_before_target`	Integer		The backward window from the target. It keeps a step where `seq_pos >= target_seq - N`. It needs `targeting_step`.
`top_n_path`	Integer		Keeps the paths that the most users take, and drops the rest. The default is no limit.
`user_cohort_event`	`String`		Limits the rows to a user with at least one `fact_events` row of this `event_type`. PickedQL writes it as `user_canonical_id IN (SELECT DISTINCT user_canonical_id FROM fact_events WHERE event_type = '…')`.
`client_local_date`	`Date`		A filter on a range of dates. PickedQL puts it into the read below. It takes `=`, `<`, `<=`, `>`, `>=`, and `BETWEEN`.
`event_timestamp`	`DateTime64`		A filter on the timestamp from the server. It takes the same operators as `client_local_date`.

When you set both window parameters, both tests apply, and the result is their intersection. A date filter joins the other parameters with AND.

See data sources, experience_journey for examples.

Schema

metrics

events

Native columns

Dimension columns (auto-joined)

steps, experiences, projects

features

experience_journey