-
-
Save brunomertins/710d0bcebd79f91c5e898373d1ab15b8 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
-- user_id (dimension | User ID) | |
user_id, | |
-- user_pseudo_id (dimension | the pseudonymous id (e.g., app instance ID) for the user) | |
user_pseudo_id, | |
-- user_first_touch_timestamp (dimension | the time (in microseconds) at which the user first opened the app/website) | |
TIMESTAMP_MICROS(user_first_touch_timestamp) AS user_first_touch_timestamp, | |
-- user_string_value (dimension | the string value of the user property) | |
( | |
SELECT | |
value.string_value | |
FROM | |
UNNEST(user_properties) | |
WHERE | |
-- Insert a parameter key | |
key = '<insert key>') AS user_string_value, | |
-- user_int_value (metric | the integer value of the user property) | |
( | |
SELECT | |
value.int_value | |
FROM | |
UNNEST(user_properties) | |
WHERE | |
-- Insert a parameter key | |
key = '<insert key>') AS user_int_value, | |
-- user_float_value (metric | the float value of the user property) | |
( | |
SELECT | |
value.float_value | |
FROM | |
UNNEST(user_properties) | |
WHERE | |
-- Insert a parameter key | |
key = '<insert key>') AS user_float_value, | |
-- user_double_value (metric | the double value of the user property) | |
( | |
SELECT | |
value.double_value | |
FROM | |
UNNEST(user_properties) | |
WHERE | |
-- Insert a parameter key | |
key = '<insert key>') AS user_double_value, | |
-- user_set_timestamp_micros (dimension | the time (in microseconds) at which the user property was last set) | |
TIMESTAMP_MICROS(( | |
SELECT | |
value.set_timestamp_micros | |
FROM | |
UNNEST(user_properties) | |
WHERE | |
-- Insert a parameter key | |
key = '<insert key>')) AS user_set_timestamp_micros, | |
-- user_ltv_revenue (metric | the Lifetime Value (revenue) of the user) | |
user_ltv.revenue AS user_ltv_revenue, | |
-- user_ltv.currency (dimension | the Lifetime Value (currency) of the user) | |
user_ltv.currency | |
FROM | |
-- Change this to your Google Analytics 4 export location in BigQuery | |
`ga4.analytics_1234567890.events_*` | |
WHERE | |
-- Define static and/or dynamic start and end date | |
_table_suffix BETWEEN '20200220' | |
AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) | |
GROUP BY | |
1, | |
2, | |
3, | |
4, | |
5, | |
6, | |
7, | |
8, | |
9, | |
10 | |
LIMIT | |
10 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment