LinkedIn
Twitter
I hope this message finds you well. I am currently facing a challenge with Google Analytics 4 data in BigQuery and thought the community might be able to provide some guidance or direct me to someone who can assist.
I am trying to calculate a total session count by hotel using the following SQL statement:
sql
Copy code
COUNT(DISTINCT CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id'))) AS Total_sessions
However, I'm encountering an issue where the session counts are being inflated due to how I am using the UNNEST function in the FROM clause of my query, specifically:
sql
Copy code
FROM `XXXXX-XXXXXXXXX-xxxxx.xxxxxxxxx_xxxxxxxxx.xxxxx_*`, UNNEST(items)
This issue arises when I attempt to extract item_name while also counting sessions, which results in inflated numbers due to the unnesting. Despite attempting various solutions, including CTEs and subqueries, I haven't been able to resolve this issue. The methods I've tried all stem from the GA4 BigQuery schema and not the GA4 UI, and unfortunately, advice from various online communities has not yet yielded a solution.
Given the complexity of this issue, I am open to engaging an expert on a consultancy basis. If you have the bandwidth to assist or can recommend someone who specializes in such queries, I would greatly appreciate it. Please let me know your thoughts or if you need any further information to understand the problem better.
btw,,,The table is already Unnested
