sbrissenden

#standardSQL
####################################################################
# PART 1: Cohort of New Users starting on SEPT 1
####################################################################
WITH 
new_user_cohort AS (  
  SELECT DISTINCT user_pseudo_id as new_user_id
  FROM 
    `projectId.analytics_YOUR_TABLE.events_*`
  WHERE

sbrissenden

#standardSQL
SELECT 
  event_id, issue_id, blame_frame.file, blame_frame.line
FROM 
  `projectId.firebase_crashlytics.package_name_ANDROID`
WHERE 
  FORMAT_TIMESTAMP("%Y%m%d", TIMESTAMP_TRUNC(event_timestamp, DAY)) = '20190509'

sbrissenden

SELECT COUNT(distinct p.user_id)
  FROM `friendly--pix.projectId.firebase_predictions.YOUR_PACKAGE_NAME_ANDROID` p CROSS JOIN UNNEST (predictions)
    INNER JOIN  `projectId.analytics_YOUR_TABLE.events_*`` a on p.user_id = a.user_id 
WHERE 
    prediction_name = "churn"   
    AND score > 0.7
    AND TIMESTAMP_TRUNC(p.prediction_time, DAY) = TIMESTAMP('2018-10-04') 
    AND a.geo.country = "France"
    
    

sbrissenden

SELECT COUNT(distinct p.user_id)
  FROM `friendly--pix.projectId.firebase_predictions.YOUR_PACKAGE_NAME_ANDROID` p CROSS JOIN UNNEST (predictions)

WHERE 
    prediction_name = "churn"   
    AND score > 0.7
    AND TIMESTAMP_TRUNC(p.prediction_time, DAY) = TIMESTAMP('2018-10-04') 

sbrissenden

SELECT
  AVG(trace_info.duration_us / 1000000) AS seconds_on_screen,
  AVG(trace_info.screen_info.slow_frame_ratio) AS slow_frame_ratio,
  AVG(trace_info.screen_info.frozen_frame_ratio) AS frozen_frame_ratio,
  event_name as screen,
  country
FROM `friendly--pix.projectId.firebase_performance.YOUR_PACKAGE_NAME_ANDROID`
WHERE _PARTITIONTIME > TIMESTAMP("2019-05-09")
AND event_type = "SCREEN_TRACE"
AND country = "FR"