https://www.growthbook.io/ logo
#give-feedback
Title
# give-feedback
e

early-tent-72446

09/17/2021, 8:03 AM
Hi! 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, 5
it 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"
10 Views