Bigquery中新安装购买者队列的Firebase事件

考虑到android用户的安装日期,我想为那些在安装后的指定时间段内至少进行了一次购买的用户获取我们在第0天到第x天的所有200多个Firebase事件的用户计数。这个问题的前半部分是在 question . 我认为分享一个附加的“购买者”队列查询将有助于其他人的重用。

我的第一次尝试(失败了):

-- 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;