CODEWITHSUNDEEP
firebasegoogle-bigqueryfirebase-analytics
Jonathan Svärdh
Jonathan Svärdh

Reputation: 21

Bigquery & Firebase Analytics - how to select one event based on different parameters

Im trying to create a query that allows me too see amount of times where firebse_event_origin = "auto" and firebase_screen_class = TicketViewController.

I tried putting AND statements as seen in the image below, but I dont get any results back. (ps. there are instances where it happens).

https://i.sstatic.net/XyX2R.png

Basically what I want is to know: How many screen_view events occured when
firebase_event_origin = "auto"
firebase_screen_class = "TicketViewController"

Upvotes: 1

Views: 818

Answers (1)

Willian Fuks
Willian Fuks

Reputation: 11797

This might be what you are looking for, or might give you some insights:

SELECT
  count(1) screen_views
FROM `ruter-pilot.no_ruter_RuterPilot_WPI_IOS.app_events_20170816`
WHERE EXISTS(SELECT 1 FROM UNNEST(event_dim) ed WHERE ed.name = 'user_engagement' AND (EXISTS(SELECT 1 FROM UNNEST(ed.params) params WHERE params.key = 'firebase_event_origin' AND value.string_value = 'auto')) AND(EXISTS(SELECT 1 FROM UNNEST(ed.params) params WHERE params.key = 'firebase_screen_class' AND value.string_value = 'TicketViewController')))

You can play with simulated data:

WITH data AS(
  SELECT ARRAY< STRUCT<name STRING, params ARRAY< STRUCT<key STRING, value STRUCT<string_value STRING> > > > > [STRUCT('user_engagement' AS name, [STRUCT('key' AS key, STRUCT('value' AS string_value) AS value ), STRUCT('firebase_event_origin' AS key, STRUCT('auto' AS string_value) AS value )] AS params)] event_dim UNION ALL
  SELECT ARRAY< STRUCT<name STRING, params ARRAY< STRUCT<key STRING, value STRUCT<string_value STRING> > > > > [STRUCT('user_engagement' AS name, [STRUCT('firebase_event_origin' AS key, STRUCT('auto' AS string_value) AS value ), STRUCT('firebase_screen_class' AS key, STRUCT('TicketViewController' AS string_value) AS value )] AS params)] event_dim UNION ALL
  SELECT ARRAY< STRUCT<name STRING, params ARRAY< STRUCT<key STRING, value STRUCT<string_value STRING> > > > > [STRUCT('user_engagement' AS name, [STRUCT('firebase_event_origin' AS key, STRUCT('auto' AS string_value) AS value ), STRUCT('firebase_screen_class' AS key, STRUCT('TicketViewController' AS string_value) AS value ), STRUCT('firebase_event_origin' AS key, STRUCT('auto' AS string_value) AS value ), STRUCT('firebase_screen_class' AS key, STRUCT('TicketViewController' AS string_value) AS value )] AS params)] event_dim UNION ALL
  SELECT ARRAY< STRUCT<name STRING, params ARRAY< STRUCT<key STRING, value STRUCT<string_value STRING> > > > > [STRUCT('user_not_engagement' AS name, [STRUCT('key' AS key, STRUCT('value' AS string_value) AS value ), STRUCT('firebase_event_origin' AS key, STRUCT('auto' AS string_value) AS value )] AS params)] event_dim
  )

SELECT
  count(1) screen_views
FROM data
WHERE EXISTS(SELECT 1 FROM UNNEST(event_dim) ed WHERE ed.name = 'user_engagement' AND (EXISTS(SELECT 1 FROM UNNEST(ed.params) params WHERE params.key = 'firebase_event_origin' AND value.string_value = 'auto')) AND(EXISTS(SELECT 1 FROM UNNEST(ed.params) params WHERE params.key = 'firebase_screen_class' AND value.string_value = 'TicketViewController')))

Basically the query looks for users whose given event name had both the specified params fired and counts all rows where this happened.

Upvotes: 2

Related Questions