Todd Kerpelman Todd Kerpelman Felipe Hoffa Thanks again for your scripts, they are really epic! It might also be good to add them here for a wider audience to enjoy them: https://support.google.com/firebase/answer/9037342?hl=en

I have four follow-up questions from my side (if you will permit me):

1. Is it correct to add in the iOS as platform only at the top of my code, or should I also add it at the very bottom of my code for each week? I only want to calculate retention for iOS users (not android).

WITH analytics_data AS (
  SELECT user_pseudo_id, event_timestamp, event_name,
    UNIX_MICROS(TIMESTAMP("2018-08-01 00:00:00", "-7:00")) AS start_day,
    3600*1000*1000*24*7 AS one_week_micros
  FROM `firebase-public-project.analytics_153293282.events_*`
  WHERE _table_suffix BETWEEN '20180731' AND '20180829'
  AND platform = "IOS"
  AND user_pseudo_id IN (
    -- Anybody who's ever encountered a level_retry_quickplay event!
    SELECT DISTINCT(user_pseudo_id) FROM
      (SELECT user_pseudo_id, event_name
       FROM `firebase-public-project.analytics_153293282.events_*`
       WHERE _table_suffix BETWEEN '20180731' AND '20180829'
       AND platform = "IOS"
      )
    WHERE event_name = "level_retry_quickplay"
  )
)

2. When I add in event_name = “level_retry_quickplay” at the top, does this mean that I will only be looking at users which triggered this event at least once across all sessions? What changes should I make to look at users who triggered this event at least two times across all their sessions?

3. How should I calculate retention for subscribers?

Currently Firebase has four default events for subscriptions, which I want to use in two complex cases:

#### Not yet usable in BQ, but maybe in the future:
* app_store_subscription_cancel
* app_store_subscription_convert
* app_store_subscription_renew
#### In BQ
* in_app_purchase

a) Users who converted to a paid subscription, but cancelled:

• I updated the script below, but I don’t think it is correct. Your thoughts?

WITH analytics_data AS (
  SELECT user_pseudo_id, event_timestamp, event_name,
    UNIX_MICROS(TIMESTAMP("2018-08-01 00:00:00", "-7:00")) AS start_day,
    3600*1000*1000*24*7 AS one_week_micros
  FROM `firebase-public-project.analytics_153293282.events_*`
  WHERE _table_suffix BETWEEN '20180731' AND '20180829'
  AND platform = "IOS"
  AND user_pseudo_id IN (
    -- Anybody who's ever encountered a level_retry_quickplay event!
    SELECT DISTINCT(user_pseudo_id) FROM
      (SELECT user_pseudo_id, event_name
       FROM `firebase-public-project.analytics_153293282.events_*`
       WHERE _table_suffix BETWEEN '20180731' AND '20180829'
       AND platform = "IOS"
      )
    WHERE event_name = "app_store_subscription_convert" AND                event_name = "app_store_subscription_cancel"
  )
)

b) Users who converted to a paid subscription, cancelled, and re-subscribed:

Would the logic below work, or what would be the best way to handle this?

#### sudo code
"app_store_subscription_convert" where event_count >= 2
AND "app_store_subscription_cancel" where event_count > 1

4. Necati Aydın the fact that your retention goes down when you comment out the specific event means that your app’s overall user-base is not as sticky as users who did that event across the selected time range. Think of it like this:

Users who did a key feature in your app has a way higher probability to get retained than those who did not do the action. (As far as I can see the original code addresses your question).

Todd Kerpelman correct me if I am wrong, but one would only move the event explicitly to “week_0_users” if you wanted to include all users who install and explicitly did the event in week 0 (view the snippet below)? or should my code below be adjusted? What approach do you recommend?

week_0_users AS (SELECT DISTINCT user_pseudo_id                           FROM `firebase-public-project.analytics_153293282.events_*`                           WHERE event_name IN ('first_open', 'level_retry_quickplay') AND event_timestamp >= UNIX_MICROS(TIMESTAMP("2018-08-01 00:00:00", "-7:00"))                              AND  event_timestamp < UNIX_MICROS(TIMESTAMP("2018-08-08 00:00:00", "-7:00"))                              AND _TABLE_SUFFIX BETWEEN '20180731' AND '20180808'                         )

Thanks again for sharing this code!