GA4の会員/非会員のユニークユーザー数をSQLで取得する実例

BigQuery

and,a株式会社 デジタルマーケティングエンジニア 佐藤洋太

導入

会員制サイトを分析するにあたり、会員/非会員軸で分析をすることは多々あります。
そこで今回はGA4とBigQueryの現場で非常によく使われる会員/非会員軸の実践的な方法をご紹介いたします。

この記事の対象

  • 会員制サイトのアナリストでSQLが不慣れな方
  • A4の会員/非会員UU数を抽出し、分析に役立てたい方

前提の実装

サイト側に生成されている会員IDをGA4で計測できるようにする準備をします。
※会員IDのサイト側への生成方法はサイトによって異なるため今回は詳細解説を省きます。

はじめにGTM側で会員IDをGA側に送れるように設定をします。

ポイントは「設定フィールド」にuser_idを指定します。
こちらの設定で会員IDをGA4に送り、GA4側で認識できるようになりました。
このIDをGA4が受け取ることでGA4側でサイトに生成された会員IDを識別できるようになります。
BigQueryに反映されるのはGTMの設定を反映した次の日からとなりますのでご注意ください。

※会員IDの取り扱いについては個人情報が特定できない範囲で取得をしてください

BigQueryで実際に会員/非会員を出力してみる

STEP1

では早速、先ほどの会員IDを出力するSQLです。
※以降、登場する以下の`ga4-quick.analytics_302974976.events_*`の箇所をお使いのGA4のデータセット名に置換し、日付の箇所を調整するだけで簡単にお使い頂けます。

SELECT
    DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS hitdate,
    user_id,
    CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') as STRING)) AS sid,
  FROM `ga4-quick.analytics_302974976.events_*`
  WHERE
  _TABLE_SUFFIX BETWEEN '20220901' AND '20220901'
  AND platform = 'WEB'
  AND user_id IS NOT NULL

解説

一見複雑そうに見えますが重要なのはこの1文だけです。

 AND user_id IS NOT NULL  

この1文で会員だけのデータを抽出できます。
コードを日本語に直すとわかりやすいと思います。
「user_idがNULLじゃない対象」を検索しているだけです。

  AND user_id IS NULL  

逆に言ってしまえばNULLなら非会員です。簡単です。

STEP2

では、先ほどのSQLを少し発展させてみましょう。
会員/非会員のユーザー数を取得します。

WITH
GA_web_tbl AS (
  SELECT
    DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS hitdate,
    event_timestamp,
    event_name,
    user_id,
    user_pseudo_id,
    CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') as STRING)) AS sid,
  FROM `ga4-quick.analytics_302974976.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20220901' AND '20220901'
    AND platform = 'WEB'
),
_member_raw_tbl AS (
  SELECT
    DISTINCT hitdate,
    user_id,
    sid,
  FROM GA_web_tbl
  WHERE user_id IS NOT NULL
),
member_tbl AS (
  SELECT
    DISTINCT hitdate,
    COUNT(DISTINCT user_id) OVER(PARTITION BY hitdate) AS member_count,
  FROM _member_raw_tbl
),
_no_member_web_tbl AS (
  SELECT
    DISTINCT hitdate,
    COALESCE(user_id, '0') AS user_id,
    user_pseudo_id,
    sid,
  FROM GA_web_tbl
  WHERE user_id IS NULL
  AND NOT EXISTS (
    SELECT * FROM _member_raw_tbl WHERE sid = GA_web_tbl.sid
  )
),
no_member_tbl AS (
  SELECT
    DISTINCT hitdate,
    COUNT(DISTINCT user_pseudo_id) OVER(PARTITION BY hitdate) AS no_member_count,
  FROM _no_member_web_tbl
)

SELECT *
FROM member_tbl AS t1
LEFT JOIN no_member_tbl AS t2 USING (hitdate)

WITH句が出てきて記述量が増え、複雑に見えますが大したことはしていません。

解説

WITH句のサブクエリごとに解説していきます。
GA_web_tbl についてはGAからデータを抽出してきているため省略します。

_member_raw_tbl AS (
  SELECT
    DISTINCT hitdate,
    user_id,
    sid,
  FROM GA_web_tbl
  WHERE user_id IS NOT NULL
),

こちらの出力結果は日本語に直すと「user_idがNULLではないレコードのhitdate, user_id, sidをGA_web_tblから取得。ただしhitdateは重複なし」という形です。

member_tbl AS (
  SELECT
    DISTINCT hitdate,
    COUNT(DISTINCT user_id) OVER(PARTITION BY hitdate) AS member_count,
  FROM _member_raw_tbl
),

こちらは集計のためのテーブルで、hitdateごとにuser_idをカウントして出力します。今回は対象の2022/09/01なので結果は1行となります。

COUNT(DISTINCT user_id) OVER(PARTITION BY hitdate) AS member_count,

こちらの構文については下記に詳細に記載されています。​​https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls?hl=ja#syntax

使い方が難しいと感じる方もいらっしゃるかもしれませんが思いの外簡単です。日本語に直すと「user_idを重複なしで日毎にカウントする」という感じです。

_no_member_web_tbl AS (
  SELECT
    DISTINCT hitdate,
    COALESCE(user_id, '0') AS user_id,
    user_pseudo_id,
    sid,
  FROM GA_web_tbl
  WHERE user_id IS NULL
  AND NOT EXISTS (
    SELECT * FROM _member_raw_tbl WHERE sid = GA_web_tbl.sid
  )
),

次のテーブルは前述の_member_raw_tblとよく似ています。
違いはuser_IdがNULL(非会員)であることとEXISTS演算子の登場です。
また、user_idをNULL(非会員)で検索をかけていますNULLの取り回しが悪いので、念のためCOALESCE関数で文字列の0として置換します。

  AND NOT EXISTS (
    SELECT * FROM _member_raw_tbl WHERE sid = GA_web_tbl.sid
  )

こちらは、_member_raw_tblのsidとGA_web_tblのsidが一致していたら結果に含まないようにするという処理を行なっています。つまりこれで、user_idの無いGA_web_tblから_member_raw_tblのsidと一致しないものを抽出しています。

EXISTS演算子の使い方についてはこちら
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators?hl=ja#exists_operator

ここでのポイントは、sidで除外をしているという点です。
考え方が非常に重要なのですが、1回のセッション内でuser_idが存在した場合、そのセッション全てを会員とみなすという考え方が根本になっています。
例えば、1回のセッション内でuser_idが変更された(=ログインがあった)場合は別のユーザーとして計測するという考え方の場合は別のSQLが必要になってきます。

no_member_tblはmember_tblとほぼ同じなので割愛します。

最後のクエリです

SELECT *
FROM member_tbl AS t1
LEFT JOIN no_member_tbl AS t2 USING (hitdate)

これで下記のような結果が出ます。

hitdatemember_countno_member_count
2022-09-01会員UU数非会員UU数

GA4の非サンプリングデータが欲しい場合や、UUを軸にトラフィックを分析をする場合などに役に立つと思います。

GA4のSQLについてご支援をご希望の方は、お気軽にand,a(このサイトの運営元)までご連絡ください。

関連記事

特集記事

コメント

  1. Thanks a lot for the blog post.

  2. I cannot thank you enough for the article. Fantastic.

  3. I truly appreciate this blog article. Really Cool.

  4. Really informative article post.Thanks Again. Fantastic.

  5. Very informative post.Much thanks again. Really Great.

  6. Thank you ever so for you blog.Really thank you! Much obliged.

  7. A round of applause for your blog article.Much thanks again. Will read on…

  8. Major thanks for the blog. Want more.

  9. Thanks for sharing, this is a fantastic blog article.Thanks Again.

  10. Great blog article.Really looking forward to read more. Great.

  11. Thanks for the blog post.Much thanks again. Keep writing.

  12. Is anyone able to recommend good Mild Steel B2B Business Data List? Thanks very much 😀

  13. I really like your writing style, superb information, regards for posting : D.

  14. Terrific post however I was wondering if you could write a litte more on this topic? I’d be very thankful if you could elaborate a little bit further. Appreciate it!

  15. Really informative blog article.Really looking forward to read more. Fantastic.

  16. wow, awesome blog.Really thank you!

  17. Great, thanks for sharing this article post. Fantastic.

  18. Enjoyed every bit of your article.Thanks Again. Awesome.

  19. I think this is a real great blog.Thanks Again. Cool.

  20. Im obliged for the article.Really looking forward to read more. Want more.

  21. Thanks-a-mundo for the article post.Really looking forward to read more. Will read on…

  22. Thanks for your marvelous posting! I certainly enjoyedreading it, you are a great author.I will be sure to bookmark your blog and willcome back down the road. I want to encourage yourselfto continue your great writing, have a nice weekend!

  23. Great blog post.Really thank you! Really Great.

  24. Thanks for the blog post.Really thank you! Really Cool.

  25. I value the blog post.Really thank you! Awesome.

  26. Ratee mt nuude photoErotic fiction gynarchyGrandpa crempies
    teensHoww doo you sexFreee porn xxxnPenis
    showing through yor pantsBukkake creampie tubesIndependnt phyoenix escortDragoon ball
    z bulma hentaiBiig beloy naked womenJebna jooy adultGalleries off mature beautiful womenCockk craving rewdhead teenBrreast look
    biggerMother i like too fucxk tubeArrthe quot sexVaginal fingering tipsHomemade fuckingBrunette ced fucking videosShare
    mmy wife sexEyeful of cumSex at disnetlandSites teenBabbe iin jeeans
    pissingIce breaker for adult educaztion classElegant aand
    erotic beautyNatalie bassingthwaite sexxy picsMatture olderr gay males enis cummingVintage clothing
    hop nycAsian massage holland michTyppe of yarn too usse to crchet a bikiniFrench nude mature
    vaginasGirfls getting fucked hardd in tthe pussy licking videosPornoraphy russianVintag business photosHugee haory pussy pregnetXxxx oldd
    whoresSexx x oold x womenBotrtom gussetMenn iin feminine attire fuckingAsian pacific american legal centerCunt
    ayanoFudked upp facials fdee trailerDawn frencch pornXxxx video frfee
    streamingCloset homosexuawl behaviorViacom sucksStrazight ass pumjeled gay pornhubMy
    gayy wworld de broederHahahaha asisn latin nudes Onlihe
    nhde granniesPretty yooung aked boysFuck off yyou fucking soft shitBoyfriend a handjobAmatuer uppskirt photosGlasss bpttom boatfs in tthe dominican republicWhatt happens to repelled spermTeewns ude by raceHoow to use a
    vibrator viodeo freeGaay controvrsy articlesAmater 123 nude
    picturesTeeen ffashion inn serbiaHot milfs sex storiesMasyer chief drawn fuckming cortanaBig
    coock transexualAustrawlia seex taxClit jaymmes jessicaHollywood maan nudeCalendar girls stfip clubVintae hommlite chainsaw partsBabe
    bpack buszty freee videoMatur tgp indianExecutive hotrel vintage court union squareTeens trioHeel high masturbatio movieVeronia brazil nudeBondage paaperStrip clubs atlanta gaLass vegas escoets sarahh blakeSwijgers
    chubbyPhotos off sex amon gay menAmayuerr nuhde tubeBuutton cover jeweled vintageOrggasm asian redNudee tannjing
    boothsAsss bumpMature thumbnai galleries postFreee 60
    xxxx nudeAnna paula msncino nudeAsian paintiesPorrn video
    zeusVimtage road bikesShops on the strip iin pittsburghDirty adultt comic stripsEscort gkrl versaillesBisexual como
    lesbiana o sabber sii soyViintage tail coatXxx harxcore gaay fuckingClikndesse crream vaginalPree teen gils inn pantiesAsian psper art the histoeryHardcore young sex picsHaydedn panettiete nnude at
    beachGaay parents wihout partnersCock sparrer rynnin riolt inn 84Gay cruising
    appHuude cyberskin dildosNaked picttures ‘famous males’ – gayXxx dog licing cunt homemadeNuude musclle imageAmatuer
    forcced tto lick pussyRecile cyicken brast easy fastBusty babes listNaed prety
    pussySexx manicansAsian desktop girlLatina pordn stars directorySexy rough footjobNude teeen art modelsFreee nude potn sexFrree adult movids creampieMom
    teachers teenn strapon sexCollege teen blowjobBlaqck gaycock thumbsBazarr sexual sitesDeepthroatt tease storyPornn amateur
    blooperCabooltjre swingrs partySexx offender treatment counnseling longvijew txSmart redheadPornn sstar
    lisa youngNudee scne fron barbarellaUnboxing pornDiva maryse nude wweMessy cumm shots max slutsVirgin mobile samsung interceptCumm girls galleryShemmale dicksuckersSexual assault faarmington ctWhatt does fackal smrll likeMiiss
    california sex picswCompaare seex voltz annd maxhardZebra
    striped wallpaper boarderMassiv natural tits tubeBlonde
    sllut chokeDonate i sperm vial willVideoo sexx leather bootsHott yooungteen pussy vidsReal turning lezbian storiesPeercy shelley gayGuy with a small penisFreee elephaht
    sexx moviesFreee oldee gaay pornBig asses caKodak insstant plpeasure camera poloriodDeath pornographySwinging pafty in altair
    texasViatimins for ssex driveAmateur deshaboller camAsss fucking fre
    sex videoFat teens masturbatingBarfland latexAmature mayure porn tubeBbww chinky mature women 7Hott teen chick cummng
    hardAciendo pissVwin vinttage chartVerbal seexual herrasmentFreee porn oon your ipodWholesale dropship adult noveltiesTiny cuntt storiesEva herzigova nude pregnant photo paradisePatfty
    pldnty prn starVintage plate frenc man and womanBelinda cari
    lesbianVideo oof gaay craigslist encounters tubesAsss balloonXxx sexvideoCharacteristics of sex linked pedigreesFrree erogic comic pornAduult hocey forumTrackiing
    tthe asian market aftdr hoursDaar williams teen ffor godChrixty caanyon blowjobVibtage gunny saxAmateur
    asiaticasFrom gijrl head lesbian lickiung toeBreast
    cancerr jewelty ribbonMicrodermal penisJiim walker + bodybuildedr + gay + anchorBlow jobs incRoy rodrigo nakedWatch me fuck my dwddt videoGaay lake forest ilUnion baay twin palm vintage 32Arkham
    asylum breastsPravite voyeurHomemaade teens with oldd menBrother
    forcced fucking hiss sisterNo plujg in porn

  27. Hi, of course this article is really pleasant and I have learned lot of things from it on the topic of blogging. thanks.

  28. Hi, I do believe this is an excellent blog. I stumbledupon it 😉 I may come back yet again since I bookmarked it. Money and freedom is the greatest way to change, may you be rich and continue to help others.

  29. Hmm is anyone else experiencing problems with the images on this blog loading?I’m trying to determine if its a problem on my end or if it’s the blog.Any suggestions would be greatly appreciated.

  30. An intriguing discussion is worth comment. I think that you ought to write more on this subject, it might not be a taboo subject but generally folks don’t talk about these topics. To the next! Cheers!!

  31. I truly appreciate this blog article.Thanks Again. Cool.

  32. Im thankful for the post.Really looking forward to read more. Cool.

  33. Awesome article post.Really looking forward to read more. Will read on…

  34. This is one awesome blog.Much thanks again.

  35. Awesome blog.Thanks Again. Awesome.

  36. Appreciate you sharing, great article. Want more.Loading…

  37. It’s hard to search out educated individuals on this topic, however you sound like you realize what you’re speaking about! Thanks

  38. Thank you ever so for you post. Awesome.

  39. What’s Taking place i’m new to this, I stumbled upon this I’ve discovered It absolutely useful and it has aided me out loads. I hope to give a contribution & aid different customers like its aided me. Good job.

  40. Thanks for finally talking about > GA4の会員/非会員のユニークユーザー数をSQLで取得する実例 –
    GA4 Quick.com < Liked it!

ランキング(週間)

  1. 1

    GA4 「ページ ロケーション」と「ページ パス と スクリーン クラス」何が違う?ドメインの有無以外にもパラメータの扱いに違いあり

  2. 2

    GA4の探索で「ページ別訪問数」を見ることは可能か?

  3. 3

    GA4のレポート画面の数値データは、イベント発生時から何時間後に確定するのか?

  4. 4

    GA4の指標「総ユーザー数」VS「アクティブ ユーザー数」

  5. 5

    GA4 拡張計測機能の新顔「フォームの操作」 form_start, form_submit

  6. 6

    【祝・GPT-4 が ChatGPT plus に登場記念!】アクセス解析コンサルタントが、GPT-4にCSVデータを渡して、データ解析結果のコメントを書いてもらう方法

  7. 7

    Ads Data Hub(ADH) とはどんなものですか?GA4との関連は?

  8. 8

    GA4で異なるドメイン(サブドメイン)の同一ページパスをドメイン付きで表示する

  9. 9

    GA4 「ユーザー コンバージョン率」「セッションのコンバージョン率」の算出方法は?(2022年8月23日更新)

  10. 10

    GA4では、utm_term, utm_content はどうなったのか?

最近の記事

  1. 生成AIを調整してABテストを実装したら想像以上に早かった

  2. 無料で使えるABテストツールのバージョンアップを行いました

  3. 無料ABテストツール(and,B)のマニュアルを作成しました

カテゴリー

 
TOP