GA4で使用した SQL3

GA4で使用したSQLの備忘録です。

・前々月に訪問したユーザーが前月にも訪問したユーザー数

サイトへ継続して訪問したユーザーがどれくらいいるか、その割合を確認できるクエリとなっています。
詳細として、前々月に訪問したユーザーのUIDに、前月に訪問したユーザーのUIDを結合することで、継続して訪問したユーザーを抽出しています。

WITH last_month_user as #先月のデータ
(
SELECT
    distinct user_pseudo_id
    , 1 as flag1
FROM
    'pmsg-portal-prd.analytics**************'
WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_DATE ("%Y%m%d", DATE_TRUNC (DATE_SUB (CURRENT_DATE ('Asia/Tokyo'), INTERVAL 1 MONTH),
MONTH））＃今日の日付の1か月前の日付を出し、丸めてN-1月1日を抽出
        AND FORMAT_DATE（"%Y%md"， DATE_SUB（DATE_TRUNC（CURRENT_DATE（'Asta/Tokyo"）， MONTH）， INTERVAL 1DAY））＃今日の日付をめてN月1日を出し、1日前の数値を出すことで、N-1月最終日を抽出できる
)


,two_months_ago_user as #先々月のデータ
(
SELECT
    distinct user_pseudo_id
    ,FORMAT_DATE （“%Y%m%d"， DATE_TRUNC （DATE_SUB（CURRENT_DATE（'Asia/Tokyo"），INTERVAL 1 MONTH）， MONTH））as event_date #先月の日付はこのクエリで入れ込む
, 1 as flag2
FROM 
     'pmsg-portal-prd.analytics**************'
WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_DATE ("%Y%m%d",
    DATE_TRUNC (DATE_SUB(CURRENT_DATE('Asia/Tokyo'),INTERVAL 2 MONTH),
    MONTH））#今日の日付の2か月前の日付を出し、丸めてN-2月1日を抽出
    AND FORMAT_DATE ("%Y%m%d", DATE_SUB (DATE_TRUNC (DATE_SUB (CURRENT_DATE ( 'Asia/Tokyo'), INTERVAL 1 MONTH), MONTH),
    INTERVAL 1DAY））#先月の日付を丸めてN-1月1日を出し、1日前の数値を出すことで、N-2月最終日を抽出

SELECT
    event_date
    , sum (flag2) as two_months_ago_user
    #先々月のユーザー数
    , (SELECT
        sum (flag1)
       FROM
        last_month_user
       INNER JOIN
        two_months_ago_user
       USING
        (user_pseudo_id)
       GROUP BY 
        flag1) as continue_next_month_users #先々月、先月も利用したユーサー数
    , (SELECT
        sum (flag1)
       FROM
        last_month_user
       INNER JOIN
        two_months_ago_user
       USING
        (user_pseudo_id)
       GROUP BY
        flag1) / sum(flag2) as continue_next_month_rete #先々月、先月も利用したユーザー数／先々月のユーザー数割合を算出している
FROM
    two_months_ago_user
GROUP BY
    flag2, event_date
    

まとめ

上記の比率を月毎に見ることで、ユーザーの継続状況を確認することができます。
是非参考にしてみてください。