Блог

Ореол,

Мне нужна помощь, я хочу получить количество событий данных, как в dasboard Google Analytics в событии session_start. У меня есть запрос try в нашем пуле bigquery, объединяющем данные GA4, но у меня разные значения между bigquery и панелью мониторинга GA.

это мой вопрос :

 -- subquery to define static and/or dynamic start and end date for the whole query
with date_range as (
select
    '20210509' as start_date,
    '20210531' as end_date
    #format_date('%Y%m%d',date_sub(current_date(), interval 1 day)) as end_date
    ),

-- subquery to prepare and calculate engagement data
engagement as (
select
    count(distinct case when session_engaged = '1' then concat(user_pseudo_id,session_id) end) as engaged_sessions,
    sum(engagement_time_msec)/1000 as engagement_time_seconds,
    count(distinct case when session_engaged = '0' then concat(user_pseudo_id,session_id) end) as bounces
from (
    select
        user_pseudo_id,
        (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
    from
        -- change this to your google analytics 4 export location in bigquery
        `ruparupamobileapp-df258.analytics_250754805.events_*`,
        date_range
    where
      _table_suffix between date_range.start_date and date_range.end_date
    group by
        user_pseudo_id,
        session_id))

-- main query
select
    -- sessions (metric | the total number of sessions)
    count(case when event_name = 'session_start' then concat(user_pseudo_id, cast(event_timestamp as string)) end) as sessions,
    -- engaged sessions (metric | the number of sessions that lasted longer than 10 seconds, or had a conversion event, or had 2 or more screen or page views)
    max(engaged_sessions) as engaged_sessions,
    -- engagement rate (metric | the percentage of engaged sessions compared to all sessions)
    safe_divide(max(engaged_sessions),count(distinct case when event_name = 'session_start' then concat(user_pseudo_id, cast(event_timestamp as string)) end)) as engagement_rate,
    -- engagement time (metric | the average length of time in seconds that the app was in the foreground, or the web site had focus in the browser)
    safe_divide(max(engagement_time_seconds),max(engaged_sessions)) as engagement_time,
    -- bounces (metric | the total number of non-engaged sessions)
    max(bounces) as bounces,
    -- bounce rate (metric | bounces divided by total sessions)
    safe_divide(max(bounces),count(distinct case when event_name = 'session_start' then concat(user_pseudo_id, cast(event_timestamp as string)) end)) as bounce_rate,
    -- event count per session (metric | number of times an individual event (i.e. 'page_view') was triggered divided by all sessions)
    safe_divide(count(distinct case when event_name = 'page_view' then concat(user_pseudo_id, cast(event_timestamp as string)) end),count(distinct case when event_name = 'session_start' then concat(user_pseudo_id, cast(event_timestamp as string)) end)) as event_count_per_session
from
    -- change this to your google analytics 4 export location in bigquery
    `xxx-dfxxx.analytics_xxxxx.events_*`,
    date_range,
    engagement
where
    _table_suffix between date_range.start_date and date_range.end_date
   
 

у меня есть результат этого в bigquery:

Результат по Bigquery

но я хочу получить счетчик событий session_start для этого представления в GA с той же датой периода :

Начало сеанса подсчета событий в GA

Как я получил общее количество событий session_start на основе периода с датой, как на панели мониторинга GA.

Спасибо.