-
Calculating Conversion Rate between Specific Event Names in Looker Studio with GA4 Data
Hey there! I’ve been messing around with GA4 in Looker Studio and set up two events – ‘view’ and ‘application’. Each one has a ‘name’ parameter attached.
I’ve made a pivot table that showcases this data, using event count as a measure. Here’s a peak at it: [TABLE](https://i.stack.imgur.com/gJTgW.png). So far so good, right? For this table, I’m just filtering to only include my event names – ‘view’ and ‘application’.
Now here’s the pickle I’m in – I want to add another column. This one would indicate the ratio of ‘application’ events to ‘view’ events for each row. I’ve been scratching my head on how to do this in Looker Studio and need some help.
I’ve tried a few things, like creating a calculated metric on the table or in the data source:
SUM(CASE WHEN Event Name = 'application' THEN 1 ELSE 0 END) / SUM(CASE WHEN Event Name = 'view' THEN 1 ELSE 0 END)Weird thing is that every row is giving me a big fat zero.
Just for kicks, I attempted to add a new field for each type of event like this:
SUM(CASE WHEN Event Name = 'job_application' THEN Event Count ELSE 0 END)In theory, then I could just divide the two fields, but that only stirred up this error message:
> Sorry, calculated fields can’t mix metrics (aggregated values) and
> dimensions (non-aggregated values). Please check the aggregation types
> of the fields used in this formula.I’m kind of at a loss for where to go from here. I mean, sure, I could create two separate tables with individual filters and blend them together, but that feels like a roundabout way of doing things. I’d really rather calculate these within the same data source instead of making new ones if I can avoid it. Any advice or ideas? Thanks a bunch!
-
2 Replies
-
Your issue is occurring due to the problem when aggregating over both metrics and dimensions together. A Looker-based solution would be to create two different measures: one for the sum of ‘application’ events and one for the sum of ‘view’ events. You can do this by editing the code of the measures to be something similar to:
SUM IF([Event Name] = 'application',[Event Count],0)
and
SUM IF([Event Name] = 'view',[Event Count],0)
respectively.Then, you can calculate the ratio within LookML, using these two new measures and avoiding the issue of mixing aggregations. For example:
[Application Sum] / [View Sum]
Remember to handle cases where View Sum may be zero to avoid division by zero errors.This solution allows you to avoid creating calculated fields which mix metrics and dimensions, which Looker does not allow. It will calculate the ratios you need without the need for merging or blending multiple tables.
-
In Looker Studio, you should be able to create separate measures for each of your events, then use a table calculation to find the ratio between them. Your issue with the calculated field might be because you’re using a dimension in an aggregate function, which Looker doesn’t allow. So, create two separate measures – one for ‘application’ events and one for ‘view’ events, and then use a table calculation to divide the ‘application’ count by the ‘view’ count.
Log in to reply.