How to make a calculated field using both GA4 event and session data in Looker Studio?
Want to calculate the rate of a GA4 event by sessions, but don't manage to make it work? Makes sense, you can't do it natively in Looker Studio, so here's the solution
The question
Hello,
I have two data with personalized filter and I want to divide nb_candidates / nb_sessions.
I did not find a way to do that efficiently.
I tried calculated field but can’t make it work.
Do you have any idea ??
Thank you for your help.
Top Looker Studio connectors we love and use on a daily basis (all with free trials): PMA - Windsor - Supermetrics - Catchr - Funnel - Dataslayer. Reviews here and there.
Not sure which one to pick? Have a question? Need a pro to get a project done? Contact us on LinkedIn or by e-mail, and we’ll clear up any doubt you might have.
Looking for Looker Studio courses? We don’t have any… but you can check Udemy!
The answer
The problem you’re facing: you cannot apply 2 different types of filters (on sessions & on events) to the same calculated field, GA4 doesn’t like it that much…
You will need to blend data as I see you have filters on both your KPIs, so, top of your page, Resources, Manage blended data, and add blend, then:
Left table: put sessions as a metric, adding your Filter “sessions candidates” at the bottom, date auto
Right table: put event count as a metric, adding your Filter “filtre Event name” at the bottom, date auto again
If there are no more dimensions (apart from the date that is automatic), you can cross join (at the top, between the two tables). Don’t put any other dimension, only one metric on the right, one metric on the left. If you want to filter by a given field, you’ll need to put this field on both dimensions (left and right) and do a left, inner or full outer join depending on your data and needs
Eventually, using the blended data as a data source in a KPI, you can now write the the following formula:
SUM(event count)/SUM(sessions)
And that’s it for today’s Q&A! If you’ve got questions you want to be answered, contact us and it’ll be our pleasure to find a solution to your problem!
PROBLEM SOLVED
Top Looker Studio connectors we love and use on a daily basis (all with free trials): PMA - Windsor - Supermetrics - Catchr - Funnel - Dataslayer. Reviews here and there.
Not sure which one to pick? Have a question? Need a pro to get a project done? Contact us on LinkedIn or by e-mail, and we’ll clear up any doubt you might have.
Looking for Looker Studio courses? We don’t have any… but you can check Udemy!
Communicate and browse privately. Check Proton Mail and Proton VPN
Website hosted by Tropical Server