Skip to content
LogoLogo

Dimension columns

Every data source has dimension columns. The metrics table holds them directly. On events and on features, PickedQL adds the LEFT JOIN that it needs when you point at one. You write no join.

The dimensions

SourceColumns
dim_variantvariant_type, ab_feature_name, ab_feature_value
dim_devicedevice_category, browser_name, browser_version, os_name, os_version, platform
dim_geocountry_code, country_name, region, city
dim_utmutm_source, utm_medium, utm_campaign, utm_term, utm_content
dim_productproduct_name, product_category, product_page
stepsstep_title
experiencesexperience_title
projectsproject_name, project_timezone

The steps, experiences, and projects sources are reference tables, and they come from the operational database. PickedQL joins them onto events and fact_events when you point at one of their analyst-facing columns. Each one also needs its own tenant filter when you query it directly:

SourceThe tenant filter that it needs
steps, experiencesWHERE project_id = '<project_id>'
projectsWHERE id = '<project_id>', because the id of the row is the project ID

PickedQL also puts a tenant equality in the ON clause of each join that it adds. Examples are AND st.project_id = '<project_id>' and AND pr.id = '<project_id>'. ClickHouse then reads part of the dimension table and not all of it. PickedQL puts the test in ON and not in WHERE, because a test in WHERE would change a LEFT JOIN into an inner join.

Each analyst-facing column name carries a prefix, such as step_title and project_name. The prefix stops a collision between two sources. Both steps.title and experiences.title are there, and you point at them as step_title and experience_title.

-- Group the events by experience and by step name.
-- PickedQL joins both `experiences` and `steps` for you.
SELECT experience_title, step_title, count(*)
FROM events
WHERE project_id = 'your-project-id'
GROUP BY experience_title, step_title

An automatic join also works inside a subquery and inside a common table expression. Point at a dimension column where you need it, and PickedQL adds the LEFT JOIN at that level:

-- Dimension columns inside a common table expression.
WITH per_step AS (
  SELECT step_title, count(*) AS n
  FROM events
  WHERE project_id = 'your-project-id'
  GROUP BY step_title
)
SELECT * FROM per_step ORDER BY n DESC
 
-- Dimension columns inside a nested query.
SELECT step_title, n FROM (
  SELECT step_title, count(*) AS n FROM events
  WHERE project_id = 'your-project-id'
  GROUP BY step_title
) sub

Native event columns

These columns are on events directly, and they need no join:

event_type, interaction_type, experience_id, step_id, event_id, user_canonical_id, session_id, client_local_date, is_purchase, is_cart_add, event_meta.

Two of them take a name from the SDK. A filter on a name that does not exist gives an empty result set and not an error:

ColumnThe values that it holds
event_typeThe name of the event, such as step_enter or cart_add. Analytics events names every one, together with the name of each custom event of your own.
interaction_typeThe label of the element behind the event. It falls back to event_type when the event has no element, so it is not the list of interaction types. Analytics events describes both.