CODEWITHSUNDEEP
google-bigqueryfirebase-analyticslooker-studio
Ellynas
Ellynas

Reputation: 105

Create chart in Data Studio using two custom Firebase params as dimensions

I am developing an iOS app and was asked to implement some user analytics to it. I'm using Firebase, as Google Analytics mobile will no longer be supported and it is the recommended alternative.

In my example, I have an event that is sent to Firebase named "changed_tool" with two custom (String) params : "source" and "tool"

I want to be able to know/see how many users have selected the "ruler" tool from the side menu, and how many have selected the same tool from a radial menu I also have.

From what I've seen, this is not possible using the Firebase console alone, and we decided to export that data to BigQuery and use Data Studio to visualize it.

My BigQuery table look like that : 

// omitted event metrics such as date or timestamp
"event_name": "changed_tool",
"event_params": [ 
    {
        "key": "source",
        "value": {
            "string_value": "radial",
            "int_value": null,
            "float_value": null,
            "double_value": null
        }
    },{
        "key": "tool",
        "value": {
            "string_value": "ruler",
            "int_value": null,
            "float_value": null,
            "double_value": null
        }
    }
]
// omitted event metrics

The only useful available dimensions Data Studio has available automatically when importing from BigQuery are "Event Name", "Event Param Name" and "Event Param Value (String)".

How can I create in Google Data Studio a stacked column chart with, as abscissas, my tool type "dimension" (in this case ruler or protractor) and as ordinates the "Event Count" metric, with the columns being "split" by my source (radial or side) "dimension" ?

So far, I haven't been able to split correctly my data to meet my needs, event when fiddling with filters as they seem the applied to the data is queried, not how the data is displayed.

Upvotes: 3

Views: 2000

Answers (1)

Ellynas
Ellynas

Reputation: 105

I figured it out.

The solution is to replicate the behavior of that query : 

SELECT
  tools.value.string_value AS tool,
  sources.value.string_value AS source,
  COUNT(event_name) as total
FROM
  `analytics_123456789.events_*`,
  UNNEST(event_params) AS tools,
  UNNEST(event_params) AS sources
WHERE
  event_name = 'changed_tool'
  AND tools.key = 'tool'
  AND sources.key = 'source'
GROUP BY
  tool,
  source
ORDER BY
  total DESC

In Data studio, create a new Stacked column chart, and as data source create a blended data with the exact same data source (in my case, events_YYYYMMDD) blended in.

As join key, use the Event time dimension. Select Event Param Value (String) as dimension and Event Count as Metrics for both data sources.

Then, Filter each data source to display only Event Param Name = tool in one case and Event Param Name = source in the other. Before exiting the blended data window, you may want to rename each Event Param Value (String) with a more meaningful name (like Tools and Sources)

Save the blended data, and use your new Tools and Sources dimensions as primary and breakdown dimensions, and Event Count as Metric, Sort and Secondary sort.

There's no need to specify again a Filter, your blended data took care of that.

Upvotes: 7

Related Questions