CODEWITHSUNDEEP
google-bigquerygoogle-analytics
TiagoD
TiagoD

Reputation: 19

Google Analytics Object in Google BigQuery

I'm trying to extract data from Google Analytics, but due to incompatibilities between dimensions and metrics, it was decided to use Google Big Query instead, to obtain the data related to GA4.

I'm struggling to find some metrics/dimensions in Google BigQuery, even searching on the documentation: https://support.google.com/analytics/answer/3437719?hl=en

Google Analytics Dimensions/Metrics:

Google Analytics Dimensions/Metrics

These are the dimensions and metrics that I've used from google analytics and the ones I can't find in Google Big Query are:

  1. Users
  2. Sessions (I used totals. visits, but I get only NULLs and 1's, while on GA it fills with more numbers)
  3. TransactionsPerSession
  4. CountryIsoCode (In GA it is only the country indicative, for instance, Spain --> ES, but in Big Query, it's the country's complete name. This can be solved, but would be good to have the country code directly from the source)
  5. avgSessionDuration

Upvotes: 0

Views: 341

Answers (1)

Max_Stone
Max_Stone

Reputation: 624

A great place to get this information is https://www.ga4bigquery.com/ I have copied one of my reports that will provide you with points 1,2,3 & 5. I don't use country but it can be found in the link above

-- subquery to prepare the data
with prep_traffic as (
select
    user_pseudo_id,
    event_date as date,
     count(distinct (ecommerce.transaction_id)) as Transactions,
    (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id,
    max((select value.string_value from unnest(event_params) where key = 'session_engaged')) as session_engaged,
    max((select value.int_value from unnest(event_params) where key = 'engagement_time_msec')) as engagement_time_msec,
    -- change event_name to the event(s) you want to count
    countif(event_name = 'page_view') as event_count,
    -- change event_name to the conversion event(s) you want to count
    countif(event_name = 'add_payment_info') as conversions,
    sum(ecommerce.purchase_revenue) as total_revenue   
from
    -- change this to your google analytics 4 bigquery export location
    `bigquery****.events_*`
where
    -- change the date range by using static and/or dynamic dates
    _table_suffix between '20230129' and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
group by 
    user_pseudo_id,
    session_id,
    event_date)

-- main query
select
   
 count(distinct user_pseudo_id) as users,
    count(distinct concat(user_pseudo_id,session_id)) as sessions,
    count(distinct case when session_engaged = '1' then concat(user_pseudo_id,session_id) end) as engaged_sessions,
    ROUND(safe_divide(count(distinct case when session_engaged = '1' then concat(user_pseudo_id,session_id) end),count(distinct user_pseudo_id)),2) as engaged_sessions_per_user,
    ROUND(safe_divide(count(distinct case when session_engaged = '1' then concat(user_pseudo_id,session_id) end),count(distinct concat(user_pseudo_id,session_id))),2) as engagement_rate,
    (sum(Transactions)) As transactions,
    (sum(Transactions))/ count(distinct concat(user_pseudo_id,session_id)) as TransactionsPerSession,
     safe_divide(sum(engagement_time_msec),count(distinct case when session_engaged = '1' then concat(user_pseudo_id,session_id) end)) /count(distinct case when session_engaged = '1' then concat(user_pseudo_id,session_id) end)as avgSessionDuration,
    sum(event_count) as event_count,
    sum(conversions) as conversions,
    ifnull(sum(total_revenue),0) as total_revenue,
   date

from
    prep_traffic
group by
    date

order by
    date desc, users desc

Upvotes: 1

Related Questions