Шпаргалка по SQL запросам к Firebase.

01 Show distinct user_pseudo_id

SELECT
  distinct(user_pseudo_id)
FROM
  `firebase-public-project.analytics_153293282.events_20181003`

02 Table suffix

SELECT
  distinct(user_pseudo_id)
FROM
  `firebase-public-project.analytics_153293282.events_*` 
WHERE
  (_TABLE_SUFFIX between '20180719' and '20181122')

03 Demo and show a basic select * to look at data

SELECT
  *
 FROM
  `firebase-public-project.analytics_153293282.events_20181003`
LIMIT
  100

04 Filter by event. Take a look at event_params.key with diff values

SELECT
  *
 FROM
  `firebase-public-project.analytics_153293282.events_20181003`
WHERE
  event_name = "level_complete_quickplay"

05 Event name and key

SELECT
  *
FROM
  `firebase-public-project.analytics_153293282.events_20181003`,
  UNNEST(event_params) as param
WHERE
  event_name = "level_complete_quickplay"
  AND param.key = "firebase_screen_class"

06 Built-in functions

SELECT
  AVG(param.value.int_value) as avg_value,
  STDDEV(param.value.int_value) as stddev
 FROM
  `firebase-public-project.analytics_153293282.events_20181003`,
  UNNEST(event_params) as param
WHERE
  event_name = "level_complete_quickplay"
  AND param.key = "value"

07 Group By Count

  SELECT
  count(param.value.int_value) as value,
  device.category
 FROM
  `firebase-public-project.analytics_153293282.events_20181003`,
  UNNEST(event_params) as param
WHERE
  event_name = "level_complete_quickplay"
  AND param.key = "value"
 GROUP BY
  device.category

08 Group By AVG

SELECT
  avg(param.value.int_value) as avg_value,
  device.category
 FROM
  `firebase-public-project.analytics_153293282.events_20181003`,
  UNNEST(event_params) as param
WHERE
  event_name = "level_complete_quickplay"
  AND param.key = "value"
 GROUP BY
  device.category

09 Getting all event params

SELECT
  event_name, param
 FROM
  `firebase-public-project.analytics_153293282.events_20181003`,
  UNNEST(event_params) as param
WHERE
  event_name = "level_complete_quickplay"

10 To get one event param

SELECT
  param.value.int_value as value
 FROM
  `firebase-public-project.analytics_153293282.events_20181003`,
  UNNEST(event_params) as param
WHERE
  event_name = "level_complete_quickplay"
  AND param.key = "value"

11 Can be written in the same way

SELECT
  event_name,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = "value") AS value
 FROM
  `firebase-public-project.analytics_153293282.events_20181003`
WHERE
  event_name = "level_complete_quickplay"

12 Still 18 rows, but with all parameters

SELECT
  event_name,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = "value") as value,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "board") as board
 FROM
  `firebase-public-project.analytics_153293282.events_20181003`
WHERE
  event_name = "level_complete_quickplay"

13 Final query

SELECT
  AVG(value) as avg_value,
  MAX(board) as board_name
 FROM
  (SELECT
    event_name,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = "value") as value,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "monster") as board
   FROM
    `firebase-public-project.analytics_153293282.events_20181003`
  WHERE
    event_name = "level_complete_quickplay")
GROUP BY
  board
ORDER BY
  avg_value desc

14 Actual screen progressions

SELECT
  screen_0,
  screen_1,
  COUNT(*) AS count
FROM (
  SELECT
    user_pseudo_id,
    event_timestamp,
    param.value.string_value AS screen_0,
    LEAD (param.value.string_value, 1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp ) AS screen_1
  FROM
    `firebase-public-project.analytics_153293282.events_20181003`,
    UNNEST(event_params) AS param
  WHERE
    event_name = "screen_view"
    AND param.key = "firebase_screen_class"
  ORDER BY
    user_pseudo_id,
    event_timestamp )
WHERE
  screen_1 = "shop_menu" # IS NOT NULL
GROUP BY
  screen_0,
  screen_1
ORDER BY
  count DESC

15 Closed funnel with time constraints

SELECT
  COUNTIF(funnel_start_time IS NOT NULL) AS funnel_begin_count,
  COUNTIF(funnel_end_time - funnel_start_time < 5 * 1000 * 1000) AS funnel_end_count
FROM (
  SELECT
    funnel_start_time,
    LEAD(funnel_end_time, 1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS funnel_end_time
  FROM (
    SELECT
      event_name,
      IF (event_name = "level_start_quickplay",
        event_timestamp,
        NULL) AS funnel_start_time,
      IF (event_name = "level_end_quickplay",
        event_timestamp,
        NULL) AS funnel_end_time,
      user_pseudo_id,
      event_timestamp
    FROM
     `firebase-public-project.analytics_153293282.events_20181003`
    WHERE
      event_name = "level_start_quickplay"
      OR event_name = "level_end_quickplay"
#      AND _TABLE_SUFFIX BETWEEN '<yyyymmdd>'
#      AND '<yyyymmdd>'
    ORDER BY
      user_pseudo_id,
      event_timestamp) )

16 Trio of events are leading up to a desired

SELECT
  s0,
  s1,
  s2,
  COUNT(*) AS count
FROM (
  SELECT
    user_pseudo_id,
    event_timestamp,
    event_name AS s0,
    LEAD (event_name, 1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS s1,
    LEAD (event_name, 2) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS s2
  FROM
    `firebase-public-project.analytics_153293282.events_20181003`
  ORDER BY
    user_pseudo_id,
    event_timestamp )
WHERE
  s2 = "level_end_quickplay"
GROUP BY
  s0,
  s1,
  s2
ORDER BY
  count DESC

17 Common screen patterns

SELECT
  screen_0,
  screen_1,
  screen_2,
  COUNT(*) AS count
FROM (
  SELECT
    user_pseudo_id,
    event_timestamp,
    param.value.string_value AS screen_0,
    LEAD (param.value.string_value, 1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp ) AS screen_1,
    LEAD (param.value.string_value, 2) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp ) AS screen_2
  FROM
    `firebase-public-project.analytics_153293282.events_20181003`,
    UNNEST(event_params) AS param
  WHERE
    event_name = "screen_view"
    AND param.key = "firebase_screen_class"
  ORDER BY
    user_pseudo_id,
    event_timestamp )
WHERE
  screen_1 IS NOT NULL
  AND screen_2 IS NOT NULL
GROUP BY
  screen_0,
  screen_1,
  screen_2
ORDER BY
  count DESC
⤧  Previous post Переписываем SQL-запросы в Pandas ⤧  Next post GA запросы для анализа страниц