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

Count of GA Events as a calculated field in Data Studio?

Follower ✭ ✭ ☆
# 1
Follower ✭ ✭ ☆

 

Hi, 

 

I would like to create some graphs in data studio based on some simple operations on event counts. I am accomplishing what I need in Google Sheets using the Analytics add in (which is awesome btw), but it would be much simpler for us if we could have standard dashboards all generated in Data Studio. 

 

For example - we have event actions for UploadStart, UploadSuccess and UploadFailed. With these I can simple calculate Upload Success%, Upload Abandon%, Upload Fail % etc. We have more complex examples than this, but all use simple operators. (+,-,/,*)

To get the straight event counts, I can query Total Events in the report,  and then use a filter like Event Action="UploadStart", but I can't leverage calculated fields for calculations with this method. 

 

Based on some searching, I think I should be able to use a regex expression to create calculated fields - something like:

SUM (CASE WHEN REGEXP_MATCH(Event Action, 'UploadSuccess') THEN 1 ELSE 0 END)

 

The problem I am having is that this calculated field is returning '1', and not a sum of how many events occurred in the time range, which is a large number. I am guessing I am overlooking something simple. 

I have looked at using calculated metrics in GA, but because we have so many of these calculations, I would prefer to do it in Data Studio. 

 

Thanks for any help, 

 

brendan

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Brendan H
April

Count of GA Events as a calculated field in Data Studio?

Visitor ✭ ✭ ✭
# 9
Visitor ✭ ✭ ✭

Check out this walkthrough:

 

https://datarunsdeep.com.au/blog/creating-custom-conversion-rates-and-funnels-google-data-studio

 

It helped me to at least get the counts of events. Still not exactly what I wanted personally, but it might help you Smiley Happy

View solution in original post

Count of GA Events as a calculated field in Data Studio?

Visitor ✭ ✭ ✭
# 2
Visitor ✭ ✭ ✭

I can't find an answer to this question ANYWHERE online. How do you take one event value and subtract it from another? How in the hell is this so difficult to do!?

 

Count of GA Events as a calculated field in Data Studio?

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

 I used count instead of sum

Count of GA Events as a calculated field in Data Studio?

Explorer ✭ ✭ ☆
# 4
Explorer ✭ ✭ ☆

@Brendan H

 

Try to do it in 2 steps. 

First, create your calculated field FIELD1 with the formula :
CASE WHEN REGEXP_MATCH(Event Action, 'UploadSuccess') THEN 1 ELSE 0 END

Be sure that this field created is with type "number" or "numeric" and aggregation "none"

 

Second, make a new field FIELD2 with the formula : 

Sum(FIELD1)

 

Let me know if it works

Count of GA Events as a calculated field in Data Studio?

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

I've tried this and it doesn't work. Seems like a huge limitation to data studio.

Count of GA Events as a calculated field in Data Studio?

Explorer ✭ ✭ ☆
# 6
Explorer ✭ ✭ ☆

@Google A, can you share a screenshot ?

Count of GA Events as a calculated field in Data Studio?

Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭

Hi, I can confirm that this does not work. It simply returns 1 or 0 not a count/sum of the total

 

I have also tried the following...

 

SUM(CASE WHEN Event Action = 'My Action' THEN Total Events ELSE 0 END) 

 

but this results in Re-aggregating metrics are not supported.

Count of GA Events as a calculated field in Data Studio?

Visitor ✭ ✭ ✭
# 8
Visitor ✭ ✭ ✭

Did anyone ever get this to work?

Marked as Best Answer.
Solution
Accepted by topic author Brendan H
April

Count of GA Events as a calculated field in Data Studio?

Visitor ✭ ✭ ✭
# 9
Visitor ✭ ✭ ✭

Check out this walkthrough:

 

https://datarunsdeep.com.au/blog/creating-custom-conversion-rates-and-funnels-google-data-studio

 

It helped me to at least get the counts of events. Still not exactly what I wanted personally, but it might help you Smiley Happy

Count of GA Events as a calculated field in Data Studio?

[ Edited ]
Follower ✭ ☆ ☆
# 10
Follower ✭ ☆ ☆

Thanks! I used this trick with the Session Id to calculate total events with 2 actions (impression & click).

One trick I came up with, in order to be able to already use my existing data without waiting for the session id to be added to all future visitors:

I created a session ID field (text) and used the following formula:

 

CONCAT(Latitude, Longitude, Continent ID, Country ISO Code, Metro Id, City ID, Browser, Browser Version, Browser Size, Month of the year, Week of the Year, Date Hour and Minute, Day of the month)

 

Screen Shot 2017-10-03 at 12.30.44 AM.png

 

It automatically generated almost 100% unique ID's to all my visitors and I could already use it for my reporting.