Skip to content

Instantly share code, notes, and snippets.

@cgi-ace

cgi-ace/AppAnalytics-04.sql Secret

Created June 16, 2021 06:51
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save cgi-ace/0b6962fc49464763bf0b41faf3f1609e to your computer and use it in GitHub Desktop.
Save cgi-ace/0b6962fc49464763bf0b41faf3f1609e to your computer and use it in GitHub Desktop.
Download ZIP
Raw
AppAnalytics-04.sql
-- Avg Session Interval Across Daysuseful for Apps like Food Delivery Apps.
-- Step 1 is to find the minimum of event time for engament event for ever user; as a sample lets try for Australia. Although there can be many engagement event for a user
-- in a day, we are intrested in only earliest engagement event
SELECT
user_pseudo_id,
event_date,
min(TIMESTAMP_MICROS(event_timestamp)) as session_start,
geo.country as country,
device.operating_system as OS
FROM `firebase-public-project.analytics_153293282.events_*`
where event_name = 'user_engagement'
and _TABLE_SUFFIX between '20180601'and '20181031'
and geo.country = 'Australia'
group by user_pseudo_id,event_date,country,OS
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment