How to query this custom event data in Bigquery?

Question

I´ve been getting around firebase and BigQuery and since I´m not into databases I´m not sure if I´m doing right requesting some data structures.

I have a Unity3D project in which I log some custom events that help me visualize how things are working in the game. For example this:

Parameter[] Params = 
        {
            new Parameter("Purchased_item","coins_14000"),
            new Parameter("Completed_levels",DataSaveManager.completedlevels.ToString() ),
            new Parameter("Scene",SceneManager.GetActiveScene().name ),
            new Parameter("Stage",DataSaveManager.Stage.ToString()),
            new Parameter("Previous_purchases",DataSaveManager.purchasesDone.ToString())
        };
        FirebaseAnalytics.LogEvent("Purchase",Params);

The problem is I don´t know how to query that data in BigQuery. I´ve been trying in many forms, but I´m not getting what I want. I´d Like to see all my events with a column for every parameter I´m tracking.

If somebody could recommend me any training course for this purpose, it would be much appreciated.

Answer 1

You will have to explicitly encode names of the parameters in the SQL query to unpivot them into separate columns. Something along these lines (assuming that parameter names in one event are all unique).

#standardSQL
SELECT
  (SELECT p.value FROM UNNEST(event_dim.params) p WHERE p.key = 'Purchased_item')
     AS Purchased_item,
  (SELECT p.value FROM UNNEST(event_dim.params) p WHERE p.key = 'Completed_levels')
     AS Completed_levels,
  ... 

Answer 2

All previous answers did not work for me due to syntax errors in BigQuery SQL editor. Maybe because the schema has changed in the meantime?

Following Java code is used for my example:

Bundle noAudio = new Bundle();
    noAudio.putString("Service", serviceString);
    noAudio.putString("Date", new Date().toString());
FirebaseAnalytics.getInstance(context).logEvent("NoAudio", noAudio);

Following BigQuery SQL creates a table of all events with some nice additional information about the user and its device.

SELECT 
  (SELECT event_params.value.string_value FROM x.event_params WHERE event_params.key = 'Date') AS Date,
  (SELECT event_params.value.string_value FROM x.event_params WHERE event_params.key = 'Service') AS Service,      
  (SELECT geo.city ) AS City,
  (SELECT user_pseudo_id ) AS User_Pseudo_ID,
  (SELECT device.mobile_os_hardware_model ) AS Model,
  (SELECT device.operating_system_version) AS OS_version
FROM `INSERT_PROJECT_NAME.analytics_INSERT_DATASET_NAME.events_*` AS x
WHERE event_name = 'NoAudio'

Results looks like this: