Virtual functions

A virtual function is a short form of an aggregate expression. Each one is table-aware. The same function expands one way on metrics, where PickedQL aggregates the data first. It expands another way on events, where there is one row for each user.

A/B test metrics

Function	Description
`control_users([condition])`	The different users in the Control group. A condition of your own can narrow them.
`variant_users([condition])`	The different users in the Variant group. A condition of your own can narrow them.
`all_users()`	Every different user
`control_sessions([condition])`	The different sessions in the Control group. A condition of your own can narrow them.
`variant_sessions([condition])`	The different sessions in the Variant group. A condition of your own can narrow them.
`all_sessions()`	Every different session

Your own filters on `events`

On the events table, each control_* function and each variant_* function takes one optional filter argument. The filter counts only a user or a session that matches your expression. It applies together with the variant_type filter that PickedQL adds. Give the argument in the first place, or by the name filter=.

The filter uses the same column=value syntax as a conversion-rate condition. It takes the AND and OR operators, and parentheses can hold a set of terms.

-- The default: every Control user.
SELECT control_users() FROM events
 
-- Your own filter, in the first place.
SELECT control_users('event_type=app_initialize') FROM events
SELECT variant_users('event_type=app_initialize AND step_id=xxx') FROM events
 
-- The AND and OR operators, with parentheses.
SELECT control_users('event_type=app_initialize OR event_type=app_load') FROM events
SELECT control_users('event_type=form_submit AND (step_id=lead-capture OR step_id=xxx)') FROM events
 
-- Sessions
SELECT control_sessions('event_type=step_enter') FROM events
SELECT variant_sessions('event_type=step_enter AND step_id=checkout') FROM events
 
-- Named form
SELECT control_users(filter='event_type=app_initialize AND step_id=xxx') FROM events

Conversion metrics

Function	Description
`all_purchase_users()`	Users who completed a purchase
`control_purchase_users()`	Control users who purchased (`metrics` only)
`variant_purchase_users()`	Variant users who purchased (`metrics` only)
`all_purchase_sessions()`	Sessions with a purchase (`metrics` only)
`all_purchase_orders()`	Unique order count (`metrics` only)
`all_cart_add_users()`	Users who added to cart

Conversion rates

Function	Description
`control_conversion_rate([numerator [, denominator]])`	The conversion rate of the Control group. By default it divides the users with a purchase by all users.
`variant_conversion_rate([numerator [, denominator]])`	The conversion rate of the Variant group. It takes the same arguments.
`conversion_rate_uplift([numerator [, denominator]])`	The gain of the Variant over the Control: `(variant CR - control CR) / control CR`. It takes the same arguments.

Your own conditions on `events`

On the events table, each conversion-rate function takes up to two optional arguments. They set the numerator and the denominator. Each argument is either a condition expression string or a nested virtual function call. See below. Give the arguments in order, or by the names numerator= and denominator=.

Each condition expression uses column=value, with the AND and OR operators. Parentheses can hold a set of terms.

-- Default: purchase users / all users
SELECT control_conversion_rate() FROM events
 
-- Custom numerator, default denominator (all users)
SELECT control_conversion_rate('event_type=form_submit_success') FROM events
 
-- The AND and OR operators in a condition.
SELECT control_conversion_rate('event_type=checkout_complete OR event_type=order_create') FROM events
SELECT control_conversion_rate('step_id=lead-capture AND event_type=form_submit_success') FROM events
 
-- Parentheses hold a set of terms.
SELECT control_conversion_rate('event_type=form_submit AND (step_id=lead-capture OR step_id=xxx)') FROM events
 
-- Custom numerator AND denominator (positional)
SELECT control_conversion_rate('event_type=form_submit', 'step_id=lead-capture') FROM events
 
-- Named arguments (explicit, any order)
SELECT control_conversion_rate(numerator='event_type=form_submit', denominator='step_id=lead-capture') FROM events
 
-- Named: denominator only (numerator defaults to purchase)
SELECT control_conversion_rate(denominator='step_id=lead-capture') FROM events
 
-- The uplift, with the arguments given by name.
SELECT conversion_rate_uplift(numerator='event_type=form_submit', denominator='step_id=lead-capture') FROM events

A nested virtual function as the numerator or the denominator

You can pass a virtual function call as the numerator or the denominator, in place of a condition string. PickedQL puts the inner aggregate there directly. It adds no variant_type filter above it, because the nested function sets its own filter.

This form is the most flexible one when the numerator and the denominator come from different segments. An example is the Control users who fired one event, over all users of every variant:

-- The Control users who fired `app_initialize`, over all users.
SELECT control_conversion_rate(
  control_users('event_type=app_initialize'),
  all_users()
) FROM events
 
-- Mix string and nested forms
SELECT control_conversion_rate(
  'event_type=form_submit',
  all_users()
) FROM events
 
-- The uplift, with a nested function in each place.
SELECT conversion_rate_uplift(
  control_users('event_type=app_initialize'),
  all_users()
) FROM events

Visitor segmentation (`metrics` only)

Function	Description
`new_users()`	First-time visitors
`returning_users()`	Returning visitors
`new_sessions()`	Sessions from new visitors
`returning_sessions()`	Sessions from returning visitors

Funnel metrics (`events` only)

Function	Description
`step_enter_users()`	Users who entered a funnel step
`step_enter_sessions()`	Sessions that entered a funnel step

Cohort filters (`events` only)

Each of these functions has the shape of a predicate. It writes a user_canonical_id IN (SELECT … FROM events …) subquery. Put one in a WHERE clause to limit any analysis to a user with at least one event that matches a condition of your own.

Function	Description
`user_cohort_filter('<condition>')`	Limits the query to a user with one or more `events` rows that match `<condition>`
`user_cohort_filter(filter='<condition>')`	The same, with the argument given by name

<condition> uses the same short column=value [AND\|OR …] form as control_users and variant_users.

-- Funnel scoped to users who eventually submitted lead capture
SELECT all_users(), all_purchase_users()
FROM events
WHERE project_id = 'proj-abc'
  AND user_cohort_filter('event_type=lead_capture')
 
-- Multi-condition cohort with OR-grouping
SELECT all_sessions()
FROM events
WHERE project_id = 'proj-abc'
  AND user_cohort_filter('event_type=signup OR event_type=trial_start')

The outer query MUST hold one project_id filter. The function puts that tenant into the inner subquery. The read of the cohort therefore covers your project, and not the whole warehouse.

Feature functions, on `features` and on `events`

Parameterized functions that accept the feature name, bucket, and an optional feature key:

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

The bucket is what the SDK sent to the site of the visitor. A Boolean feature flag gives 'true' or 'false'. A Text feature flag gives the value of one variant, or 'false' for the control group. The key is the version of the feature flag, such as 'v1'.

-- Compare the two buckets of a Boolean feature flag.
SELECT
  feature_users('homepage_test', 'false') AS control,
  feature_users('homepage_test', 'true') AS variant
FROM features
 
-- Compare the variants of a Text feature flag.
SELECT
  feature_users('hero', 'false') AS control,
  feature_users('hero', 'hero-a') AS hero_a,
  feature_users('hero', 'hero-b') AS hero_b
FROM features
 
-- Count one version of the feature flag alone.
SELECT feature_users('homepage_test', 'false', 'v1') FROM features

Other (`metrics` only)

Function	Description
`event_count()`	Total event count (from pre-aggregation)

Filters — the filter string a function takes.
Analytics queries
About PickedQL

Virtual functions

A/B test metrics

Your own filters on events

Conversion metrics

Conversion rates

Your own conditions on events

A nested virtual function as the numerator or the denominator

Visitor segmentation (metrics only)

Funnel metrics (events only)

Cohort filters (events only)

Feature functions, on features and on events

Other (metrics only)

Related