[B! SQL][*WebAnalytics] SuiJackDoã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯

SuiJackDo id:SuiJackDo

SQLã¨*WebAnalyticsã«é–¢ã™ã‚‹SuiJackDoã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯ (3)

${{author_name}}$

{{{comment_expanded}}}

{{label}}

{{#is_bookmark}}ãƒªã‚¹ãƒˆ{{/is_bookmark}}{{^is_bookmark}}ãƒªãƒ³ã‚¯{{/is_bookmark}}

${{author_name}}$
{{author_name}}{{created}}
{{ #comment }}{{ comment }}{{ /comment }}
- {{ label }}

${{author_name}}$

{{{comment_expanded}}}

{{label}}

{{#is_bookmark}}ãƒªã‚¹ãƒˆ{{/is_bookmark}}{{^is_bookmark}}ãƒªãƒ³ã‚¯{{/is_bookmark}}

GA4ãƒ‡ãƒ¼ã‚¿ã®åˆ†æžç”¨SQLã¾ã¨ã‚ï½œDentsu Digital Tech Blog
é›»é€šãƒ‡ã‚¸ã‚¿ãƒ«ã®ä¸é‡Žã§ã™ã€‚ ä»Šå›žã¯ã€Google Analytics 4ï¼ˆGA4ï¼‰ã‚’BigQueryã§åˆ†æžã™ã‚‹éš›ã«ã‚ˆãä½¿ã†SQLã‚’ã¾ã¨ã‚ã¾ã—ãŸã€‚ è‡ªèº«ã®æ¥å‹™ã§ã‚‚ã‚ˆãä½¿ç”¨ã™ã‚‹ãŸã‚å‚™å¿˜éŒ²çš„ãªå´é¢ã‚‚ã‚ã‚Šã¾ã™ãŒã€å‚è€ƒã¾ã§ã«æ´»ç”¨ã„ãŸã ã‘ã‚Œã°ã¨æ€ã„ã¾ã™ã€‚ å‰æBigQueryã«é€£æºã—ãŸGA4ãƒ‡ãƒ¼ã‚¿ã«ã¯ã„ãã¤ã‹å‰ææ¡ä»¶ãŒã‚ã‚Šã¾ã™ã€‚ ãã®ä¸ã§æœ€ã‚‚å¤§ããªç‚¹ã¯ã€GA4ã®ãƒ¬ãƒãƒ¼ãƒˆç”»é¢ã¨æ•°å€¤ãŒä¸€è‡´ã—ãªã„å ´åˆãŒã‚ã‚‹ã€ã¨ã„ã†ã“ã¨ã§ã™ã€‚ è©³ç´°ã«ã¤ã„ã¦ã¯ã€ã“ã¡ã‚‰ã®ãƒšãƒ¼ã‚¸ã«è¨˜è¼‰ãŒã‚ã‚Šã¾ã™ã€‚ ãƒ¦ãƒ¼ã‚¶ãƒ¼æ•°ã‚„ã‚»ãƒƒã‚·ãƒ§ãƒ³æ•°ã¨ã„ã£ãŸãƒ¦ãƒ‹ãƒ¼ã‚¯æ•°ã‚’ç®—å‡ºã™ã‚‹å ´åˆã€ãƒ‡ãƒ¼ã‚¿é‡ãŒå¤šã„ã¨è¨ˆç®—ã«æ™‚é–“ãŒã‹ã‹ã‚Šã¾ã™ã€‚ãã“ã§GA4ãƒ¬ãƒãƒ¼ãƒˆã§ã¯HyperLogLogã¨ã„ã†ã‚¢ãƒ«ã‚´ãƒªã‚ºãƒ ã‚’ä½¿ç”¨ã—ã¦è¿‘ä¼¼å€¤ã‚’ç®—å‡ºã—ã¦ã„ã¾ã™ã€‚ ç²¾ç·»ãªå€¤ã‚’è¨ˆç®—ã™ã‚‹å ´åˆã¯ã€ç¾çŠ¶BigQueryä»¥å¤–ã«æ‰‹æ®µãŒã‚ã‚Šã¾ã›ã‚“ã€‚ã“ã†ã„ã£ãŸåˆ¶ç´„ã‹ã‚‰ã‚‚BigQueryä¸Šã§SQLã‚’ä½¿ã£ãŸåˆ†æžã‚’ã™ã‚‹å ´é¢ã¯å¢—
SuiJackDo 2023/02/06
*google

*WebAnalytics

GA4

SQL
ãƒªãƒ³ã‚¯
GA4ã®ä¼šå“¡/éžä¼šå“¡ã®ãƒ¦ãƒ‹ãƒ¼ã‚¯ãƒ¦ãƒ¼ã‚¶ãƒ¼æ•°ã‚’SQLã§å–å¾—ã™ã‚‹å®Ÿä¾‹ - GA4 Quick.com
and,aæ ªå¼ä¼šç¤¾ã€€ãƒ‡ã‚¸ã‚¿ãƒ«ãƒžãƒ¼ã‚±ãƒ†ã‚£ãƒ³ã‚°ã‚¨ãƒ³ã‚¸ãƒ‹ã‚¢ã€€ä½è—¤æ´‹å¤ª å°Žå…¥ ä¼šå“¡åˆ¶ã‚µã‚¤ãƒˆã‚’åˆ†æžã™ã‚‹ã«ã‚ãŸã‚Šã€ä¼šå“¡/éžä¼šå“¡è»¸ã§åˆ†æžã‚’ã™ã‚‹ã“ã¨ã¯å¤šã€…ã‚ã‚Šã¾ã™ã€‚ ãã“ã§ä»Šå›žã¯GA4ã¨BigQueryã®ç¾å ´ã§éžå¸¸ã«ã‚ˆãä½¿ã‚ã‚Œã‚‹ä¼šå“¡/éžä¼šå“¡è»¸ã®å®Ÿè·µçš„ãªæ–¹æ³•ã‚’ã”ç´¹ä»‹ã„ãŸã—ã¾ã™ã€‚ ã“ã®è¨˜äº‹ã®å¯¾è±¡ ä¼šå“¡åˆ¶ã‚µã‚¤ãƒˆã®ã‚¢ãƒŠãƒªã‚¹ãƒˆã§SQLãŒä¸æ…£ã‚Œãªæ–¹A4ã®ä¼šå“¡/éžä¼šå“¡UUæ•°ã‚’æŠ½å‡ºã—ã€åˆ†æžã«å½¹ç«‹ã¦ãŸã„æ–¹ å‰æã®å®Ÿè£… ã‚µã‚¤ãƒˆå´ã«ç”Ÿæˆã•ã‚Œã¦ã„ã‚‹ä¼šå“¡IDã‚’GA4ã§è¨ˆæ¸¬ã§ãã‚‹ã‚ˆã†ã«ã™ã‚‹æº–å‚™ã‚’ã—ã¾ã™ã€‚ â€»ä¼šå“¡IDã®ã‚µã‚¤ãƒˆå´ã¸ã®ç”Ÿæˆæ–¹æ³•ã¯ã‚µã‚¤ãƒˆã«ã‚ˆã£ã¦ç•°ãªã‚‹ãŸã‚ä»Šå›žã¯è©³ç´°è§£èª¬ã‚’çœãã¾ã™ã€‚ ã¯ã˜ã‚ã«GTMå´ã§ä¼šå“¡IDã‚’GAå´ã«é€ã‚Œã‚‹ã‚ˆã†ã«è¨å®šã‚’ã—ã¾ã™ã€‚ ãƒã‚¤ãƒ³ãƒˆã¯ã€Œè¨å®šãƒ•ã‚£ãƒ¼ãƒ«ãƒ‰ã€ã«user_idã‚’æŒ‡å®šã—ã¾ã™ã€‚ ã“ã¡ã‚‰ã®è¨å®šã§ä¼šå“¡IDã‚’GA4ã«é€ã‚Šã€GA4å´ã§èªè˜ã§ãã‚‹ã‚ˆã†ã«ãªã‚Šã¾ã—ãŸã€‚ ã“ã®IDã‚’GA4ãŒå—ã‘å–ã‚‹ã“ã¨ã§GA4å´ã§ã‚µã‚¤
SuiJackDo 2022/09/24
*google

*tips

*WebAnalytics

BigQuery

SQL
ãƒªãƒ³ã‚¯
Google Analytics 4 + BigQueryã§ã‚ˆãä½¿ã†åŸºæœ¬çš„ãªSQLä¾‹
ãƒžãƒ¼ã‚±ãƒ†ã‚£ãƒ³ã‚°ãƒ†ã‚¯ãƒŽãƒã‚¸ãƒ¼ã®æƒ…å ±ã‚„ãƒŽã‚¦ãƒã‚¦ãƒ»TIPSã€ã‚¨ã‚¯ã‚¹ãƒãƒ¥ã‚¢ç¤¾ã®æƒ…å ±ã‚’ç™ºä¿¡ã€‚ã€ãƒ–ãƒã‚°ãƒã‚¿å‹Ÿé›†ã€‘ã”èˆˆå‘³ã®ã‚ã‚‹åˆ†é‡Žã‚’æ•™ãˆã¦ãã ã•ã„ï¼ã”è¦æœ›ã®å†…å®¹ã‚’è¨˜äº‹ã«èµ·ã“ã—ã¾ã™ï¼ãƒ¡ãƒ‹ãƒ¥ãƒ¼ã®ã€Œãƒ–ãƒã‚°ã¸ã®ã€ãƒªã‚¯ã‚¨ã‚¹ãƒˆã‚ˆã‚ŠãŠé€ã‚Šãã ã•ã„ã€‚ menu ã“ã‚“ã«ã¡ã¯ã€ã‚¨ã‚¯ã‚¹ãƒãƒ¥ã‚¢ã®æ¬Šæ³³æ±(æ¨©æ³³æ±/ã‚³ãƒ³ãƒ»ãƒ¨ãƒ³ãƒ‰ãƒ³)ã§ã™ã€‚ Google Analytics 4 (ä»¥ä¸‹GA4)ãŒãƒªãƒªãƒ¼ã‚¹ã•ã‚Œã¾ã—ãŸãã€‚ ã¨ã„ã†ã‚ã‘ã§GA4ã®BigQueryã‚¨ã‚¯ã‚¹ãƒãƒ¼ãƒˆæ©Ÿèƒ½ã‚’ä½¿ã£ã¦æ—©é€Ÿãƒ‡ãƒ¼ã‚¿ã‚’æŠ½å‡ºã—ã¦ã¿ã¾ã—ã‚‡ã†ï¼ ä»¥å‰ã€GA360+BigQueryã§ç§ãŒã‚ˆãä½¿ã†åŸºæœ¬çš„ãªSQLä¾‹ã‚’ç´¹ä»‹ã—ã¾ã—ãŸãŒã€ä»Šå›žã¯ãã‚Œã®GA4ãƒãƒ¼ã‚¸ãƒ§ãƒ³ã§ã™ã€‚ 1. ãƒšãƒ¼ã‚¸ãƒ“ãƒ¥ãƒ¼æ•° GA4ã®æ—¥åˆ¥ãƒšãƒ¼ã‚¸ãƒ“ãƒ¥ãƒ¼ã‚’å‡ºã—ã¦ã¿ã¾ã™ã€‚ ã‚¿ã‚¤ãƒ ã‚¾ãƒ¼ãƒ³ã‚’ä»»æ„ã§å¤‰æ›´ã—ãŸã„ã®ã§ã€event_dateã‚«ãƒ©ãƒ ã§ã¯ãªãevent_timestampã‚’ã€ŒAsia/Tokyoã€ã«ã—ã¦ä½¿ã£ã¦ã¾ã™ã€‚ ã
SuiJackDo 2022/09/22
*google

BigQuery

SQL

*WebAnalytics
ãƒªãƒ³ã‚¯
1

ãŠçŸ¥ã‚‰ã›

ã‚‚ã£ã¨èªã‚€

å…¬å¼Twitter

@hatebu
æœ€æ–°ã®äººæ°—ã‚¨ãƒ³ãƒˆãƒªãƒ¼ã®é…ä¿¡

ã‚ãƒ¼ãƒœãƒ¼ãƒ‰ã‚·ãƒ§ãƒ¼ãƒˆã‚«ãƒƒãƒˆä¸€è¦§

jæ¬¡ã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯

kå‰ã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯

lã‚ã¨ã§èªã‚€

eã‚³ãƒ¡ãƒ³ãƒˆä¸€è¦§ã‚’é–‹ã

oãƒšãƒ¼ã‚¸ã‚’é–‹ã

è¨å®šã‚’å¤‰æ›´ã—ã¾ã—ãŸx

ã¯ã¦ãªãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯

ã‚¿ã‚°

é–¢é€£ã‚¿ã‚°ã§çµžã‚Šè¾¼ã‚€ (4)

SQLã¨*WebAnalyticsã«é–¢ã™ã‚‹SuiJackDoã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯ (3)

ãŠçŸ¥ã‚‰ã›

ä»Šé€±ã®ã¯ã¦ãªãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯æ•°ãƒ©ãƒ³ã‚ãƒ³ã‚°ï¼ˆ2024å¹´12æœˆç¬¬4é€±ï¼‰

ã€Œã‚ã¨ã§èªã‚€ã€ã‚¿ã‚°ã§æŒ¯ã‚Šè¿”ã‚‹2024å¹´ ã€œä»Šå¹´ã®ã€Œã‚ã¨ã§èªã‚€ã€ã€ä»Šå¹´ã®ã†ã¡ã«ã€œ

ä»Šé€±ã®ã¯ã¦ãªãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯æ•°ãƒ©ãƒ³ã‚ãƒ³ã‚°ï¼ˆ2024å¹´12æœˆç¬¬3é€±ï¼‰

å…¬å¼Twitter

ã‚ãƒ¼ãƒœãƒ¼ãƒ‰ã‚·ãƒ§ãƒ¼ãƒˆã‚«ãƒƒãƒˆä¸€è¦§

ã¯ã¦ãªãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯

å…¬å¼Twitter

ã¯ã¦ãªã®ã‚µãƒ¼ãƒ“ã‚¹

ã‚¿ã‚°

é–¢é€£ã‚¿ã‚°ã§çµžã‚Šè¾¼ã‚€ (4)

SQLã¨*WebAnalyticsã«é–¢ã™ã‚‹SuiJackDoã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯ (3)

GA4ãƒ‡ãƒ¼ã‚¿ã®åˆ†æžç”¨SQLã¾ã¨ã‚ï½œDentsu Digital Tech Blog

GA4ã®ä¼šå“¡/éžä¼šå“¡ã®ãƒ¦ãƒ‹ãƒ¼ã‚¯ãƒ¦ãƒ¼ã‚¶ãƒ¼æ•°ã‚’SQLã§å–å¾—ã™ã‚‹å®Ÿä¾‹ - GA4 Quick.com

Google Analytics 4 + BigQueryã§ã‚ˆãä½¿ã†åŸºæœ¬çš„ãªSQLä¾‹

ãŠçŸ¥ã‚‰ã›

ä»Šé€±ã®ã¯ã¦ãªãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯æ•°ãƒ©ãƒ³ã‚­ãƒ³ã‚°ï¼ˆ2024å¹´12æœˆç¬¬4é€±ï¼‰

ã€Œã‚ã¨ã§èª­ã‚€ã€ã‚¿ã‚°ã§æŒ¯ã‚Šè¿”ã‚‹2024å¹´ ã€œä»Šå¹´ã®ã€Œã‚ã¨ã§èª­ã‚€ã€ã€ä»Šå¹´ã®ã†ã¡ã«ã€œ

ä»Šé€±ã®ã¯ã¦ãªãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯æ•°ãƒ©ãƒ³ã‚­ãƒ³ã‚°ï¼ˆ2024å¹´12æœˆç¬¬3é€±ï¼‰

å…¬å¼Twitter

ã‚­ãƒ¼ãƒœãƒ¼ãƒ‰ã‚·ãƒ§ãƒ¼ãƒˆã‚«ãƒƒãƒˆä¸€è¦§

ã¯ã¦ãªãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯

å…¬å¼Twitter

ã¯ã¦ãªã®ã‚µãƒ¼ãƒ“ã‚¹

é–¢é€£ã‚¿ã‚°ã§çµžã‚Šè¾¼ã‚€ (4)

SQLã¨*WebAnalyticsã«é–¢ã™ã‚‹SuiJackDoã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯ (3)

GA4ãƒ‡ãƒ¼ã‚¿ã®åˆ†æžç”¨SQLã¾ã¨ã‚ï½œDentsu Digital Tech Blog

GA4ã®ä¼šå“¡/éžä¼šå“¡ã®ãƒ¦ãƒ‹ãƒ¼ã‚¯ãƒ¦ãƒ¼ã‚¶ãƒ¼æ•°ã‚’SQLã§å–å¾—ã™ã‚‹å®Ÿä¾‹ - GA4 Quick.com

Google Analytics 4 + BigQueryã§ã‚ˆãä½¿ã†åŸºæœ¬çš„ãªSQLä¾‹

ãŠçŸ¥ã‚‰ã›

ä»Šé€±ã®ã¯ã¦ãªãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯æ•°ãƒ©ãƒ³ã‚ãƒ³ã‚°ï¼ˆ2024å¹´12æœˆç¬¬4é€±ï¼‰

ã€Œã‚ã¨ã§èªã‚€ã€ã‚¿ã‚°ã§æŒ¯ã‚Šè¿”ã‚‹2024å¹´ ã€œä»Šå¹´ã®ã€Œã‚ã¨ã§èªã‚€ã€ã€ä»Šå¹´ã®ã†ã¡ã«ã€œ

ä»Šé€±ã®ã¯ã¦ãªãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯æ•°ãƒ©ãƒ³ã‚ãƒ³ã‚°ï¼ˆ2024å¹´12æœˆç¬¬3é€±ï¼‰

å…¬å¼Twitter

ã‚ãƒ¼ãƒœãƒ¼ãƒ‰ã‚·ãƒ§ãƒ¼ãƒˆã‚«ãƒƒãƒˆä¸€è¦§

ã¯ã¦ãªãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯

å…¬å¼Twitter

ã¯ã¦ãªã®ã‚µãƒ¼ãƒ“ã‚¹