BigQueryでユーザー・流入元を分析するクエリ集

select
    extract(date from timestamp_micros(user_first_touch_timestamp)at time zone "Asia/Tokyo") as day_first_touch, 　-- user_first_touch_timestampという初回訪問時間が記録されている列から日付を抽出する
    count(distinct user_pseudo_id) as users 　-- 該当日付のユニークなユーザー数をカウント
from
    `ha-ga4.analytics_227084301.events_*`   　-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220201' and '20220205' 　-- 日付の指定
group by
    day_first_touch 　-- 日付でグルーピングする
having 
    day_first_touch is not null　 　-- タイムスタンプがnullではないという条件を満たす事で空白を除外
order by
    day_first_touch　-- 日付で並び替え

select
    traffic_source.source,-- 参照元を抽出
    traffic_source.medium,-- メディアを抽出
    traffic_source.name as campaign,　-- キャンペーン名を抽出しcampignと名付ける
    count(distinct user_pseudo_id) as users　-- ユニークなユーザー数
 
from
    `ha-ga4.analytics_227084301.events_*`   　-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220201' and '20220205' 　-- 日付の指定
group by
    source,　　-- 参照元でグルーピング
    medium,　　-- メディアでグルーピング
    campaign　　-- キャンペーンでグルーピング
order by
    users desc　　-- ユーザー降順で並び替え

select
    concat(traffic_source.source, " / ", traffic_source.medium) as source_medium,　-- concat関数で２つのsourceとmediumを「/」でつなぐ
    count(event_name) as sessions　-- 後ほど指定するイベント名の列の見出しを「sessions」にする
from
    `ha-ga4.analytics_227084301.events_*`   　-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220201' and '20220205' 　-- 日付の指定
    and event_name = 'session_start' 　-- イベント名がsession_startに合致するものだけを抽出
group by
   source_medium 　-- source_mediumでグルーピング
order by
   sessions desc　　-- ユーザー降順で並び替え

select
    geo.country,　　-- 国を指定
    nullif(geo.region,'') as region,　　-- 地域（都道府県）を指定。空白の場合はnullに置き換える
    nullif(geo.city,'') as city,　　　-- 都市を指定。空白の場合はnullに置き換える
    count(distinct user_pseudo_id) as users
 
from
    `ha-ga4.analytics_227084301.events_*`   　-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220201' and '20220205' 　-- 日付の指定
    and geo.country = 'Japan' 　-- 国が日本のみを指定
group by
    country,　　-- 国でグルーピング
    region,　　-- 都道府県でグルーピング
    city　　--　市でグルーピング
order by
    users desc　　-- ユーザー降順で並び替え

select
    device.category,
    device.operating_system,
    device.operating_system_version,
    device.language,
    device.web_info.browser,
    device.web_info.browser_version,
    device.web_info.hostname,
    count(distinct user_pseudo_id) as users
 
from
    `ha-ga4.analytics_227084301.events_*`   　-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
    _table_suffix between '20220201' and '20220205' 　-- 日付の指定
group by 
    category,
    operating_system,
    operating_system_version,
    language,
    browser,
    browser_version,
    hostname
order by
    users desc　　-- ユーザー降順で並び替え

select
    count(distinct (select value.int_value from unnest(event_params) where key = 'ga_session_id')) as session_id_count -- ga_session_idパラメータのユニーク数(distinct)をカウントする(count)。unnestでパラメータ値を展開する必要があります
from
    `ha-ga4.analytics_227084301.events_*`   　-- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
 
where
    _table_suffix between '20220201' and '20220205'　-- 日付の指定