Data Studio
2.4K members online now
2.4K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Google Data Studio custom metric multiplying 2 parameters

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

I am using Firebase for collecting events from my application.

For example lets say I have an event print_attempt and it has 2 parameters page_count and copies. Something like this..

event {   name: print_attempt
   param {     name: copies
     int_value: 10
   }   param {     name: page_count
     int_value: 5
   }
 }

Now in Google Data Studio, I want to have a metric total page printed. How do I multiply 2 param values?

SUM(CASE WHEN Event Param Name = "page_count" THEN Event Param Value Int ELSE 0 END)

returns me the sum of page_count but in this scenario copies value is ignored.

I tried something like below, but this gives me error.

SUM(CASE 
      WHEN Event Param Name = "page_count" THEN (              Event Param Value Int * CASE WHEN EVENT PARAM NAME ="copies" THEN 
              Event Param Value Int ELSE 1 END) 
      ELSE 0 END)

Any pointers?

Marked as Best Answer.
Solution
Accepted by topic author Naveen M
May 2017

Google Data Studio custom metric multiplying 2 parameters

Follower ✭ ☆ ☆
# 2
Follower ✭ ☆ ☆

Hi Naveen, 

What you are trying to do is not directly possible in Data Studio. The connector works with a flattened schema where the int values that you are trying to multiply will be in different records. Here is an example:

 

"20170101", "print_attempt", "copies", 10
"20170101",  "print_attempt", "page_count", 5
"20170102", "print_attempt", "copies", 20
"20170102", "print_attempt", "page_count", 4

These cannot be aggregated the way you want to.

 

I see 2 options:

1) You can create a BigQuery view where you can join the table with itself using keys like app_instance_id, bundle_sequence_id, event timestamp. You will have to decide what defines a unique row in your dataset.

The full schema is documented here: https://support.google.com/firebase/answer/7029846?hl=en

In the joined view, you need to have copies and page_count as separate columns in the same row. Then in Data Studio you can use a formula like this: SUM(copies_col_in_view * page_count_col_in_view).

 

2) In Firebase Analytics, add a new event param for pages_used (or however you wish to name this) with value of page_count*copies. Then you can directly define SUM over this fields in Data Studio.

 

Google Data Studio custom metric multiplying 2 parameters

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Thanks Nikhil

I ended up adding another parameter in the event as you suggested.