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)これで下記のような結果が出ます。
| hitdate | member_count | no_member_count |
| 2022-09-01 | 会員UU数 | 非会員UU数 |
GA4の非サンプリングデータが欲しい場合や、UUを軸にトラフィックを分析をする場合などに役に立つと思います。
Thanks a lot for the blog post.
I cannot thank you enough for the article. Fantastic.
I truly appreciate this blog article. Really Cool.
Really informative article post.Thanks Again. Fantastic.
Very informative post.Much thanks again. Really Great.
Thank you ever so for you blog.Really thank you! Much obliged.
A round of applause for your blog article.Much thanks again. Will read on…
Major thanks for the blog. Want more.
Thanks for sharing, this is a fantastic blog article.Thanks Again.
Great blog article.Really looking forward to read more. Great.
Thanks for the blog post.Much thanks again. Keep writing.
Is anyone able to recommend good Mild Steel B2B Business Data List? Thanks very much 😀
I really like your writing style, superb information, regards for posting : D.
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!
Really informative blog article.Really looking forward to read more. Fantastic.
wow, awesome blog.Really thank you!
Great, thanks for sharing this article post. Fantastic.
Enjoyed every bit of your article.Thanks Again. Awesome.
I think this is a real great blog.Thanks Again. Cool.
Im obliged for the article.Really looking forward to read more. Want more.
Thanks-a-mundo for the article post.Really looking forward to read more. Will read on…
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!
Great blog post.Really thank you! Really Great.
Thanks for the blog post.Really thank you! Really Cool.
I value the blog post.Really thank you! Awesome.
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
Hi, of course this article is really pleasant and I have learned lot of things from it on the topic of blogging. thanks.
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.
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.
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!!
I truly appreciate this blog article.Thanks Again. Cool.
Im thankful for the post.Really looking forward to read more. Cool.
Awesome article post.Really looking forward to read more. Will read on…
This is one awesome blog.Much thanks again.
Awesome blog.Thanks Again. Awesome.
Appreciate you sharing, great article. Want more.Loading…
It’s hard to search out educated individuals on this topic, however you sound like you realize what you’re speaking about! Thanks
Thank you ever so for you post. Awesome.
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.
Thanks for finally talking about > GA4の会員/非会員のユニークユーザー数をSQLで取得する実例 –
GA4 Quick.com < Liked it!
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
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
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
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.
Thanks for finally writing about > GA4の会員/非会員のユニークユーザー数をSQLで取得する実例 –
GA4 Quick.com < Liked it!
Quality articles or reviews is the key to invite the
visitors to go to see the site, that’s what this site is providing.