Clash Royale CLAN TAG

Firebase Highly Active Users With Purchase Count

Question:

I found this example, which allows me to get all the user_ids, but with a very filtered approach. I then did a self-join just to supplement these Highly Active Users with their purchases (without success so far). Perhaps there is an easier approach, but my idea is to not only add a purchase-column, but also other conversion-events later: i.e. session_start, user_login, download_video, etc. Any contributions are much appreciated!

#STANDARD_SQL
SELECT
DISTINCT T.user_id,
T.event_params.key,
SUM(T.event_params.value.int_value),
COUNT(Z.event_name)
FROM `bigquery-table.analytics_xxxx.events_*` AS T, T.event_params
LEFT JOIN `bigquery-table.analytics_xxxx.events_*` AS Z, Z.event_params
ON T.user_pseudo_id = Z.user_pseudo_id
WHERE
T.event_name = 'user_engagement'
AND Z.event_name = 'in_app_purchase'
# User engagement in the last M = 10 days */
AND T.event_timestamp > UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 10 DAY))
AND T.event_params.key = 'engagement_time_msec'
AND _TABLE_SUFFIX BETWEEN '20180716' AND '20240131'
AND platform = 'ANDROID'
GROUP BY 1,2
# Having engaged for more than N = 5 minutes
HAVING SUM(T.event_params.value.int_value) > 3 * 60 * 1000000


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.