Hi again, I have a complex query which I want to use it as a metric throw growthBook. the query calculates the episode listen consumption rate which works like this: • when the user opens an episode an episode_selected event is fired (linked with the experiment). • on player progress an episode_listen event is fired which holds the current second and the episode duration. • for now, for each user we gets the max point he reached on player progress then divide it by the episode duration. • then gets the avg for all users who selected the episode. any ideas how can i do this throw growthBook metric?

WITH

__rawExperiment AS ( SELECT user_id AS user_id, TIMESTAMP_MICROS(event_timestamp) AS timestamp, experiment_id_param.value.string_value AS experiment_id, variation_id_param.value.int_value AS variation_id, duration_param.value.int_value AS duration, played_seconds_param.value.double_value AS played_seconds, geo.country AS country, traffic_source.source AS source, traffic_source.medium AS medium, device.category AS device, device.web_info.browser AS browser, device.operating_system AS os FROM

**

.

**

.

events_*

, UNNEST(event_params) AS duration_param, UNNEST(event_params) AS played_seconds_param, UNNEST(event_params) AS experiment_id_param, UNNEST(event_params) AS variation_id_param WHERE ( (_TABLE_SUFFIX BETWEEN '20241018' AND '20241018') OR ( _TABLE_SUFFIX BETWEEN 'intraday_20241018' AND 'intraday_20241018' ) ) AND event_name = 'episode-listened' AND experiment_id_param.key = 'experiment_id' AND variation_id_param.key = 'variation_id' AND played_seconds_param.key = 'played_seconds' AND duration_param.key = 'duration' AND user_id IS NOT NULL ), max_played_seconds_per_user AS ( SELECT user_id, experiment_id, variation_id, MAX(played_seconds) AS max_played_seconds, duration FROM __rawExperiment GROUP BY user_id, experiment_id, variation_id, duration ), listen_consumption_rate_per_user AS ( SELECT user_id, experiment_id, variation_id, SAFE_DIVIDE(max_played_seconds, duration) AS listen_consumption_rate FROM max_played_seconds_per_user ), total_episode_selected_users AS ( SELECT experiment_id_param.value.string_value AS experiment_id, variation_id_param.value.int_value AS variation_id, COUNT(DISTINCT user_id) AS total_users FROM

**

.

**

.

events_*

, UNNEST(event_params) AS experiment_id_param, UNNEST(event_params) AS variation_id_param WHERE ( (_TABLE_SUFFIX BETWEEN '20241018' AND '20241020') OR ( _TABLE_SUFFIX BETWEEN 'intraday_20241018' AND 'intraday_20241020' ) ) AND event_name = 'episode-selected' AND experiment_id_param.key = 'experiment_id' AND variation_id_param.key = 'variation_id' GROUP BY experiment_id, variation_id ) SELECT lcrpu.experiment_id, lcrpu.variation_id, SAFE_DIVIDE(SUM(lcrpu.listen_consumption_rate),teu.total_users) * 100 AS average_listen_consumption_rate, teu.total_users FROM listen_consumption_rate_per_user lcrpu JOIN total_episode_selected_users teu ON lcrpu.experiment_id = teu.experiment_id AND lcrpu.variation_id = teu.variation_id GROUP BY lcrpu.experiment_id, lcrpu.variation_id, teu.total_users;

Hi Abdelhady, It may help to create "selected the episode" as an activation metric. This would filter your users to only those that selected the episode. I'm not sure what your treatment is, but "episode listen" may also be an appropriate activation metric, as long as treatment does not affect the probability a user listens to the episode. Then you can create a metric for (max point / duration) and compare across variations. I hope this helps. Luke