#give-feedback
Title
e
early-tent-72446
09/17/2021, 8:03 AMHi! Have you ever thought about adding a default dimension of new vs. returning visitors (relative to the experiment)? New as in first seen after the experiment has started, and returning as those who have some page views before the experiment was launched.
Right now, I'm trying to define it using subqueries on the experiment query, but maybe this use case is general enough to always include by default?
f
future-teacher-7046
09/17/2021, 9:52 AM
I like the idea of having some built-in dimensions. I'd be curious to see what subquery you come up with to see if there's a way to easily generalize it.
e
early-tent-72446
09/17/2021, 10:19 AM Copy code
SELECT TRIM(user_id::varchar) AS user_id,
TRIM(user_id::varchar) AS anonymous_id,
TRIM(test_id) AS experiment_id,
TRIM(variant_label) AS variation_id,
CASE WHEN da.created_at < experiment_start_time THEN 'returning' ELSE 'new' END AS user_cohort,
MIN(timestamp) AS timestamp
FROM rpt.segment_ab_test
JOIN dwh.dim_account da USING (account_id)
JOIN (
SELECT test_id, MIN(timestamp) AS experiment_start_time
FROM rpt.segment_ab_test
GROUP BY 1) exp_start USING (test_id)
GROUP BY 1, 2, 3, 4, 5it checks for account creation time vs. first time an experiment flag was fired.
but as you're already collecting pageviews in another query, maybe that one could be used instead of account creation table the tool doesn't know about. IMHO it would be enough that a user was seen before the start of the experiment to call them "returning"
3 Views