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

この１文で会員だけのデータを抽出できます。
コードを日本語に直すとわかりやすいと思います。
「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なので結果は１行となります。

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で除外をしているという点です。
考え方が非常に重要なのですが、１回のセッション内でuser_idが存在した場合、そのセッション全てを会員とみなすという考え方が根本になっています。
例えば、１回のセッション内で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（このサイトの運営元）までご連絡ください。

関連記事

特集記事

A/Bテスト

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

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

無料ABテストツール（and,B）のマニュアルを作成しました
A/Bテスト

今日から無料で使えるABテストツールを作ってみた
Python

ECサイトのゴールデンルートを発見する方法 – Google Analytics 4とPythonを活用して（第2回）
トレジャーデータ

GA4 -> トレジャーデータ 連携術

コメント

  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!

  41. Hey! I could have sworn I’ve been to this site before but after
    browsing through some of the post I realized it’s new to me.

    Nonetheless, I’m definitely happy I found it and
    I’ll be bookmarking and checking back often!

    Here is my web blog – vpn coupon 2024

  42. After exploring a few of the blog articles on your web site, I truly like your
    technique of writing a blog. I added it to my bookmark webpage
    list and will be checking back in the near future.
    Take a look at my web site too and let me know your opinion.

    my webpage – vpn special code

  43. Hello to all, for the reason that I am in fact keen of reading this web site’s post to be updated regularly.
    It contains pleasant material.

    Here is my web site … vpn special coupon code 2024

  44. Piece of writing writing is also a fun, if you be
    acquainted with after that you can write or else it is
    complex to write.

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

  46. Quality articles or reviews is the key to invite the
    visitors to go to see the site, that’s what this site is providing.

  47. I am sure this piece of writing has touched all the internet users, its really really good paragraph on building up
    new webpage.

  48. Very neat article.Much thanks again. Will read on…

  49. Wow, great article.Really looking forward to read more.

  50. Why users still use facebook vs eharmony to find love online read news papers when in this technological world the whole thing is available on web?

  51. What’s up i am kavin, its my first occasion to commenting anywhere, when i read this post i thought i could also make comment due to
    this sensible article.

    Here is my homepage; eharmony special coupon code 2024

  52. It’s difficult to find well-informed people on this topic, but you
    seem like you know what you’re talking about! Thanks

    my blog; nordvpn special coupon code 2024

  53. venta libre de medicamentos Orion Versailles Medikamente online bestellen in der Schweiz

  54. médicaments en ligne rapide et facile France Kendrick Corinto acquista
    farmaci online a Verona senza bisogno di ricetta

  55. médicaments en vente en ligne en Suisse Alter Guadalupe medicijnen te koop

  56. Compra medicamentos sin receta en Suiza Biofarma Santa Catarina dónde comprar medicamentos en Madrid

  57. Say, you got a nice blog post.Thanks Again.

  58. Excellent blog you have got here.. It�s hard to find high quality writing like yours these days. I honestly appreciate people like you! Take care!!

    My blog post – https://Penzu.com/p/b31b2681

  59. I don’t think the title of your article matches the content lol. Just kidding, mainly because I had some doubts after reading the article.

  60. Everyone loves what you guys are up too. Such clever work and exposure!

    Keep up the amazing works guys I’ve incorporated you guys to our blogroll.

  61. арнайы мектепке дейінгі ұйым тәрбиешісі, мектепке
    дейінгі оқыту және тәрбиелеу мақала алматы –
    казань поезд цена, алматы – казань самолет стоимость посылки из казахстана
    в россию, доставка посылок по казахстану
    әңгімелеу мәтіні сұрақтары, әңгімелеу мәтінін құра

  62. Thank you for some other informative web site. The place else could I am getting that type of info written in such an ideal means?
    I have a undertaking that I’m just now working on, and I’ve been at the look out for such info.

  63. I know this if off topic but I’m looking into starting my own weblog
    and was wondering what all is required to get set up? I’m assuming having a blog like yours
    would cost a pretty penny? I’m not very web savvy so I’m not 100% positive.
    Any recommendations or advice would be greatly appreciated.

    Appreciate it

  64. ткань для обшивки карт дверей, самоклеющаяся ткань для авто золотой ребенок фильм, золотой
    ребенок смотреть онлайн в hd жаз туралы эссе ағылшынша,
    ағылшын тілі туралы шығарма ағылшынша гдз 5 сынып қазақстан тарихы көмеков қазақша, гдз қазақстан тарихы 6 сынып

  65. қызыл кітап барыс, ақ барыс туралы мәлімет say mo койше текст,
    say mo песни батыс қазақстан адам саны, батыс қазақстан
    шаруашылығы интертич услуги, интертич уральск цены на услуги

  66. неке жузик туралы, неке жүзігін салу рәсімі зат энергия алмасу қатың перевод, катын происхождение слова шылау туралы сұрақтар, одағай сұрақтары

ランキング（週間）

  1. 1

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

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

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

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

    GA4 クロスドメイン設定では、リンク先に「_gl」パラメータが付く。勝手なパラメータを付けるとエラーになるページは要注意
  6. 6

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

    Ads Data Hub（ADH） とはどんなものですか？GA4との関連は？
  8. 8

    YouTubeの概要欄に貼られたリンクをクリックしてサイトを訪れたユーザーを計測するために
  9. 9

    「GA4【無料】オンラインセミナー」セミナーアーカイブ（登録不要）
  10. 10

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

タグ

BigQuery_by_佐藤洋太 (4)

最近の記事

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

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

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

カテゴリー

アーカイブ
 
TOP