Skip to content

Instantly share code, notes, and snippets.

@Osiyuks

Osiyuks/Firebase AB testing: purchase, retention, target action

Last active February 25, 2020 08:08
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Osiyuks/73814fab30784b527691604a1520b92d to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save Osiyuks/73814fab30784b527691604a1520b92d to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Firebase AB testing: purchase, retention, target action
with t as (
SELECT
user_pseudo_id, event_date,
CASE userProperty.value.string_value
WHEN "0" THEN "Control group"
WHEN "1" THEN "Variant A"
END AS test
FROM `<project_id>.<dataset>.events_*`, -- change your project_id and dataset
UNNEST(user_properties) AS userProperty
WHERE _TABLE_SUFFIX > "20200212" and _TABLE_SUFFIX < "20200228"
and userProperty.key = "firebase_exp_28" -- change your number test
group by 1,2,3
), p as (
SELECT
user_pseudo_id, event_date, 'new' as p
FROM `<project_id>.<dataset>.events_*` -- change your project_id and dataset
WHERE _TABLE_SUFFIX > "20200212" and _TABLE_SUFFIX < "20200228"
and event_name = 'first_open'
group by 1,2
), c as (
select user_pseudo_id, 1 as c
from
(SELECT
user_pseudo_id,
row_number() over(partition by user_pseudo_id order by event_timestamp) as row
FROM `<project_id>.<dataset>.events_*` -- change your project_id and dataset
WHERE _TABLE_SUFFIX > "20200212" and _TABLE_SUFFIX < "20200228"
and event_name = 'in_app_purchase')
where row = 1
), a as (
select user_pseudo_id, 1 as a
FROM `<project_id>.<dataset>.events_*` -- change your project_id and dataset
WHERE _TABLE_SUFFIX > "20200212" and _TABLE_SUFFIX < "20200228"
and event_name = 'target_action' -- change your target event
group by 1
), r as (
select user_pseudo_id, 1 as r
from
(SELECT
user_pseudo_id,
row_number() over(partition by user_pseudo_id order by event_timestamp) as row
FROM `<project_id>.<dataset>.events_*`
WHERE _TABLE_SUFFIX > "20200212" and _TABLE_SUFFIX < "20200228"
and event_name = 'session_start')
where row = 2
), f as (
select t.*, p.p, c.c, a.a, r.r from t
left join p on t.user_pseudo_id=p.user_pseudo_id and t.event_date=p.event_date
left join c on t.user_pseudo_id=c.user_pseudo_id
left join a on t.user_pseudo_id=a.user_pseudo_id
left join r on t.user_pseudo_id=r.user_pseudo_id
)
select
test, count(*) as users, sum(c) as cr_to_purchase, sum(a) as cr_to_target_action, sum(r) as cr_to_retention
from f
where p='new'
group by 1
order by 1, 2 desc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment