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

Calculating a value from several event catgories

Follower ✭ ✭ ✭
# 1
Follower ✭ ✭ ✭

I have a rather complex problem which I am not sure is solveable in Data Studio.

 

I get reports through GA from a number of consumer devices. Each device reports a number of events regularly, and one of these event is a "baseline" event ("Event-X"), used to establish the number of reporting devices in some time interval. I can then compare it with the number of other events ("Event-A", "Event-B", etc, in order to see how large a %-age of all devices report each of these events ( event-A % = unique events ("Event-A") / unique events ("Event-X"). Event-X, Event-A, etc is distinguished by "Event Category" and "Event Action".

 

I have not found a way to do this in GA itself, which means I have to export the data to a spreadsheet for each event category, and then merge these exports into one sheet, whereupon I can produce graphs, tables etc for my reports.

 

So, i would need something like "(Unique Events(where EventCategory == "X" and EventAction == "xx" ) / Unique Events(where EventCategory == "A" and EventAction == "aaa"). But I do not find a way to to do this in Data Studio, even using a computed field.

1 Expert replyverified_user

Re: Calculating a value from several event catgories

Google Employee
# 2
Google Employee
Hi Kjell

I'm not sure if this will work for you, but have you tried using the CASE clause? For example, perhaps something like this:

SUM(CASE WHEN (Event Category = "X" AND Event Action = "XX" ) then 1 else 0 END )

Do that for each of the combinations to create the metrics you need, and finally create a calculation to get the %s.

(This isn't very scalable, I know, so if you have lots events this might not be practical).

Let me know if you solve this!

Rick

Re: Calculating a value from several event catgories

Follower ✭ ✭ ✭
# 3
Follower ✭ ✭ ✭
I cannot get useful results from this. In my example, I have a custom dimension "Heartbeat", and it looks like this if I just put it into a table:

Event Category Event Action Sessions AppUsage
Heartbeat Returning 30,095 1
Heartbeat New 5,366 1

Here, "AppUsage" is the calculated field, using the formula adapted from your suggestion, as I understand it:

"SUM(CASE WHEN (Event Category = "Heartbeat" ) THEN 1 else 0 END )"

I expected this to sum over all events, and for those events where Event Category is "Hearbeat" add 1 to the sum, otherwise add 0. Since I know that this event is always sent exactly once per session to serve as a baseline (we always start a new session when sending the data and the data transfer should take a few seconds or less) , the expression should be the same as the number of sessions. This is obviously not what happens.

I also attempted to separate the two event action types in this category, by using the formula "SUM(CASE WHEN (Event Category = "Heartbeat" AND Event Action = "Returning" ) THEN 1 else 0 END ), but I still get "1" as the result in both these rows; I would have expected, even given the above results, that the second row would be 0.

Either I am doing something pretty stupid or this does not work very well...

BTW, editing a formula which is rather simple is made more difficult by having only a single edit row with only a few characters. Editing anything larger than my second example will not work at all; it will have to be edited somewhere else and then cut and pasted into place. I find it urgent to add the capability to get multi-line edit field.

/kjell K

Re: Calculating a value from several event catgories

Follower ✭ ✭ ✭
# 4
Follower ✭ ✭ ✭
I see that I must amend one paragraph in my previous post, the one where I complained that I could not get "AND...." to work. After restarting Chrome, I now get 1 in the first row and 0 in the second row, as would be expected. However, there were no indication that the app was running out of memory other than a painfully slow interface.

The summing up of applicable events, however, still does not work. I also tried to insert SUM(Sessions) instead of 1, but that produced an error.

/kjell k

Re: Calculating a value from several event catgories

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭
Hi, i have a problem with analytics. It is reporting more users than sessions and no pageviews, can you help me?

Re: Calculating a value from several event catgories

Follower ✭ ✭ ✭
# 6
Follower ✭ ✭ ✭
I would suggest that you start a new thread, it does not appear to have anything to do with my question.

Re: Calculating a value from several event catgories

Follower ✭ ✭ ✭
# 7
Follower ✭ ✭ ✭
After some further experimentation, it appears that there is some kind of limitation in computed fields, where fields which are the result of aggregation cannot be used in computed fields. The only thing which I have managed to get to work in a limited manner is to count the number of different values in a dimension, which is not very useful. SInce almost all interesting things in GA is the result of aggregatiing individual reports, I have a hard time understanding the value of these computations.

Re: Calculating a value from several event catgories

Visitor ✭ ✭ ✭
# 8
Visitor ✭ ✭ ✭
Hi Kjell,

Where and how do I start a new thread

Thank you for answering my question

Re: Calculating a value from several event catgories

Follower ✭ ✭ ✭
# 9
Follower ✭ ✭ ✭
When you are on the list of topics, there should be a large red button with a "+" on it, to the lower right. If you press/tap/click on that you will start a new topics.

/kjell k

Calculating a value from several event catgories

Follower ✭ ☆ ☆
# 10
Follower ✭ ☆ ☆

I'm trying to do something similar, I want to create a custom metric based on a count of all events with a certain event action. I haven't found any way to do this yet. I would love to hear if anyone was able to figure this out.