Untitled
unknown
plain_text
a month ago
1.9 kB
1
Indexable
Never
#creating the install table WITH install_table AS ( SELECT user_pseudo_id, event_name, event_date, event_timestamp FROM `ios-mi-good-or-bad-moms-life.analytics_387432338.events_*` WHERE event_name = 'first_open' AND platform = 'IOS' ), #creating the revenue table revenue_table AS ( SELECT user_pseudo_id, event_name, event_timestamp, EXTRACT(date FROM Parse_datetime('%Y%m%d', event_date)) AS event_date, ( SELECT COALESCE(value.int_value, value.float_value, value.double_value, NULL) FROM UNNEST(event_params) WHERE KEY = 'value' AND event_name = 'paid_ad_impression' ) AS ad_funded_revenue, ( SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = 'currency' AND event_name = 'paid_ad_impression' ) AS ad_revenue_currency, ( CASE WHEN event_name = 'in_app_purchase' THEN event_value_in_usd ELSE 0 END) AS iap_revenue_usd, FROM `ios-mi-good-or-bad-moms-life.analytics_387432338.events_*` WHERE platform = 'IOS' AND event_name IN ( 'in_app_purchase', 'paid_ad_impression') ), total_revenue_table AS ( SELECT it.user_pseudo_id AS user_pseudo_id, rt.event_date, #combine ad revenue and IAP revenue, assuming both are in same currency sum(ifnull(rt.iap_revenue_usd,0) + ifnull(rt.ad_funded_revenue,0)) AS total_revenue, FROM install_table it INNER JOIN revenue_table rt ON it.user_pseudo_id = rt.user_pseudo_id WHERE rt.event_timestamp >= it.event_timestamp AND rt.event_timestamp <= it.event_timestamp + 86400000000 * 2 #added 86400 000 millisecond as 24 hours GROUP BY 1, 2 ) SELECT approx_quantiles(total_revenue, 64) AS buckets FROM total_revenue_table