Hi Looker Studio Community,
I'm encountering a persistent and perplexing issue when trying to create a simple Click-Through Rate (CTR) calculated field in Looker Studio using a Google Analytics 4 (GA4) data source. I'm hoping for some insights or solutions.
Goal:
Calculate the CTR for a specific GA4 event named lp_cta_click using the formula:
lp_cta_click event count / Sessions
GA4 Setup:
The event lp_cta_click is correctly configured in GA4 and is being collected.
The necessary standard fields "Event name" (イベント名 - Text), "Event count" (イベント数 - Number), and "Sessions" (セッション - Number) are available and visible in the Looker Studio data source panel with the correct data types.
Looker Studio Attempts & Errors:
I have tried numerous variations of calculated fields, all resulting in errors, primarily:
"Sorry, calculated fields can't mix metrics (aggregated values) and dimensions (non-aggregated values)." (申し訳ありませんが、計算フィールドには指標 (集計値) とディメンション (非集計値) を混在させることはできません。)
"Invalid formula - Invalid input expression. Field name in join query must be fully qualified with table alias" (when using blended data as a workaround).
"Syntax error: Expected ')' but got keyword NAME" or "Unknown ID for dimension or metric 'Event name'" (when trying different ways of referencing the "Event name" field).
Calculated Field Formulas Attempted (using a single GA4 data source):
Attempt 1 (Standard IF, no backticks - field names highlight correctly in editor):
SUM(IF(イベント名 = "lp_cta_click", イベント数, 0)) / SUM(セッション)
Use code with caution.
Lookerstudio
Result: "Cannot mix metrics and dimensions" error.
Attempt 2 (Standard CASE, no backticks - field names highlight correctly in editor):
SUM(CASE WHEN イベント名 = "lp_cta_click" THEN イベント数 ELSE 0 END) / SUM(セッション)
Use code with caution.
Lookerstudio
Result: "Cannot mix metrics and dimensions" error.
Attempt 3 (With backticks - though field names don't contain spaces or special characters that usually require them):
SUM(IF(`イベント名` = "lp_cta_click", `イベント数`, 0)) / SUM(`セッション`)
Use code with caution.
Lookerstudio
Result: "Unknown ID for dimension or metric イベント名" or similar syntax errors, even though イベント名 alone (without backticks) is recognized by the editor.
Troubleshooting Steps Taken:
Confirmed that イベント名, イベント数, and セッション are present in the data source and their data types are correct.
When typing イベント名 into the calculated field editor, it highlights (e.g., in green or blue) and shows "ABC Text" underneath, indicating it's recognized as a dimension. Similarly for イベント数 and セッション as metrics.
Tried creating the calculated field in a brand new, blank Looker Studio report with a freshly added GA4 data source. The issue persists.
Temporarily experienced a UI issue where the data source and metric/dimension selection panel in a new scorecard was completely blank, but this has since resolved itself (UI is back to normal). However, the calculated field error remains.
Checked the GTM container JSON for the lp_cta_click event; the setup appears standard (event name is lp_cta_click, triggered by a custom event, GA4 event tag type). No obvious GTM-side misconfiguration that would explain this Looker Studio behavior was found.
Attempted a workaround using blended data (filtering イベント数 for lp_cta_click in one table, セッション in another, then dividing them in a calculated field on the blend), but this led to the "Field name in join query must be fully qualified with table alias" error, even when trying to qualify with table aliases (e.g., T1.指標名).
Core Problem:
Despite the "Event name" (イベント名) field being recognized as a dimension in the editor, the IF(イベント名 = "lp_cta_click", ...) or CASE WHEN イベント名 = "lp_cta_click" THEN ... part of the formula seems to be treated as a non-aggregated value, causing the "Cannot mix metrics and dimensions" error, even when the entire conditional part is wrapped in SUM().
This behavior seems fundamentally incorrect, as this is a standard way to calculate conditional sums for CTR-like metrics.
Request:
Has anyone encountered such a stubborn "Cannot mix metrics and dimensions" error with GA4 data sources under similar circumstances? Are there any known bugs, GA4 connector quirks, or alternative syntaxes that might resolve this specific issue when the basic SUM(IF(...)) or SUM(CASE WHEN ...)) fails despite field names being recognized?
Any help or suggestions would be greatly appreciated, as I'm currently unable to calculate this basic CTR.
Thank you for your time and expertise.
LinkedIn
Twitter
