Firebase Events for Newly Installed Purchaser Cohort in Bigquery

Question

Given the install date of android users, I would like to get the users' count for all our 200+ Firebase events on day0 to dayX for users which have already made at least one purchase in a defined period after installation. The first half of this question was previously solved in this question. I thought it would be helpful to share an added "purchaser"-cohort query for others to re-use.

My first attempt (which failed):

-- STANDARD SQL
-- NEW BIGQUERY EXPORT SCHEMA
SELECT
  a.event_name AS event_name,
  a._TABLE_SUFFIX as day,
  COUNT(1) as users
FROM `xxxx.analytics_xxxx.events_*` as c
    RIGHT JOIN (SELECT user_pseudo_id, event_date, event_timestamp, event_name
                FROM `xxxx.analytics_xxxx.events_*`
                WHERE user_first_touch_timestamp BETWEEN 1530453600000000 AND 1530468000000000
                AND _TABLE_SUFFIX BETWEEN '20180630' AND '20180707'
                AND platform = "ANDROID"
                AND (event_name = 'in_app_purchase' OR event_name = 'ecommerce_purchase')
                ) as a
    ON a.user_pseudo_id = c.user_pseudo_id 
WHERE _TABLE_SUFFIX BETWEEN '20180630' AND '20180707'
GROUP BY event_name, day;

Answer 1

Answer:

-- STANDARD SQL
-- NEW BIGQUERY EXPORT SCHEMA
SELECT    
  event_name AS event_name,
  _TABLE_SUFFIX as day,
  COUNT(1) as users
FROM `xxxx.analytics_xxxx.events_*`
    WHERE _TABLE_SUFFIX BETWEEN '20180630' AND '20180707'
    AND user_pseudo_id IN (SELECT user_pseudo_id
                           FROM `xxxx.analytics_xxxx.events_*`
                           WHERE _TABLE_SUFFIX BETWEEN '20180630' AND '20180707'
                           AND user_first_touch_timestamp BETWEEN 1530453600000000 AND 1530468000000000
                           AND (event_name = 'in_app_purchase' OR event_name = 'ecommerce_purchase')
                           AND platform = "ANDROID")
GROUP BY event_name, day;

PS: Suggestions to optimize this script are always welcome :)