{"id":13187,"date":"2026-03-17T06:24:43","date_gmt":"2026-03-17T06:24:43","guid":{"rendered":"https:\/\/withcode.tech\/media\/?p=13187"},"modified":"2026-07-06T14:36:41","modified_gmt":"2026-07-06T14:36:41","slug":"ga4-bigquery-user-analysis","status":"publish","type":"post","link":"https:\/\/withcode.tech\/media\/ga4-bigquery-user-analysis\/","title":{"rendered":"GA4\u30c7\u30fc\u30bf\u3092BigQuery\u306b\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u3057\u3066SQL\u3067\u30e6\u30fc\u30b6\u30fc\u884c\u52d5\u3092\u5206\u6790\u3059\u308b\u65b9\u6cd5\u3010\u5b8c\u5168\u30ac\u30a4\u30c9\u3011\uff5c\u5b9f\u8df5SQL\u30af\u30a8\u30ea15\u9078\u4ed8\u304d"},"content":{"rendered":"\n<div class=\"wp-block-group swl-box\"><div class=\"wp-block-group__inner-container\">\n\n<h3 class=\"wp-block-heading\">\u3053\u306e\u8a18\u4e8b\u3067\u308f\u304b\u308b\u3053\u3068<\/h3>\n\n\n<ul class=\"wp-block-list\">\n<li>GA4\u6a19\u6e96UI\u306e\u9650\u754c\u3068BigQuery\u9023\u643a\u3067\u4f55\u304c\u5909\u308f\u308b\u304b\uff08\u6bd4\u8f03\u8868\u4ed8\u304d\uff09<\/li>\n<li>BigQuery\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u306e\u8a2d\u5b9a\u624b\u9806\uff08GCP\u30d7\u30ed\u30b8\u30a7\u30af\u30c8\u4f5c\u6210\u304b\u3089\u30ea\u30f3\u30af\u307e\u3067\uff09<\/li>\n<li>GA4\u30c6\u30fc\u30d6\u30eb\u306e\u69cb\u9020\u3068UNNEST\u3092\u4f7f\u3063\u305fevent_params\u306e\u53d6\u5f97\u65b9\u6cd5<\/li>\n<li>PV\u30fb\u30bb\u30c3\u30b7\u30e7\u30f3\u30fb\u30d5\u30a1\u30cd\u30eb\u30fb\u30b3\u30db\u30fc\u30c8\u30fbLTV\u30fbDAU\u306a\u3069\u5b9f\u8df5SQL\u30af\u30a8\u30ea15\u9078\u306e\u5168\u6587<\/li>\n<li>Looker Studio\u9023\u643a\u30fb\u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\u30af\u30a8\u30ea\u30fb\u30b3\u30b9\u30c8\u6700\u9069\u5316\u306e\u30c6\u30af\u30cb\u30c3\u30af<\/li>\n<\/ul>\n\n<\/div><\/div>\n\n\n<div class=\"swell-block-balloon\"><div class=\"c-balloon -bln-left\" data-col=\"gray\"><div class=\"c-balloon__icon -square\"><img decoding=\"async\" loading=\"lazy\" src=\"https:\/\/withcode.tech\/media\/wp-content\/uploads\/2025\/06\/\u30b9\u30af\u30ea\u30fc\u30f3\u30b7\u30e7\u30c3\u30c8-2025-06-15-14.48.08.jpg\" alt=\"\" class=\"c-balloon__iconImg\" width=\"80px\" height=\"80px\"><span class=\"c-balloon__iconName\">\u751f\u5f92<\/span><\/div><div class=\"c-balloon__body -speaking -border-none\"><div class=\"c-balloon__text\">\n<p class=\"balloon balloon-left\"><strong>GA4\u306eUI\u4e0a\u3067\u306f\u5206\u6790\u306e\u9650\u754c\u3092\u611f\u3058\u3066\u3044\u307e\u3059\u3002\u3082\u3063\u3068\u81ea\u7531\u306b\u30c7\u30fc\u30bf\u3092\u96c6\u8a08\u30fb\u5206\u6790\u3057\u305f\u3044\u3093\u3067\u3059\u304c\u2026\u2026<\/strong><\/p>\n<span class=\"c-balloon__shapes\"><span class=\"c-balloon__before\"><\/span><span class=\"c-balloon__after\"><\/span><\/span><\/div><\/div><\/div><\/div>\n\n<div class=\"swell-block-balloon\"><div class=\"c-balloon -bln-right\" data-col=\"gray\"><div class=\"c-balloon__icon -square\"><img decoding=\"async\" loading=\"lazy\" src=\"https:\/\/withcode.tech\/media\/wp-content\/uploads\/2025\/06\/\u30b9\u30af\u30ea\u30fc\u30f3\u30b7\u30e7\u30c3\u30c8-2025-06-15-15.11.23.jpg\" alt=\"\" class=\"c-balloon__iconImg\" width=\"80px\" height=\"80px\"><span class=\"c-balloon__iconName\">\u30da\u30f3\u535a\u58eb<\/span><\/div><div class=\"c-balloon__body -speaking -border-none\"><div class=\"c-balloon__text\">\n<p class=\"balloon balloon-right\"><strong>GA4 \u00d7 BigQuery\u306e\u9023\u643a\u304c\u7b54\u3048\u3058\u3083\uff01GA4\u306e\u30c7\u30fc\u30bf\u3092BigQuery\u306b\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u3059\u308c\u3070\u3001SQL\u3092\u4f7f\u3063\u3066\u81ea\u7531\u81ea\u5728\u306b\u30e6\u30fc\u30b6\u30fc\u884c\u52d5\u3092\u5206\u6790\u3067\u304d\u308b\u3093\u3058\u3083\u3002\u6a19\u6e96UI\u3067\u306f\u4e0d\u53ef\u80fd\u306a\u8907\u96d1\u306a\u30d5\u30a1\u30cd\u30eb\u5206\u6790\u30fb\u30b3\u30db\u30fc\u30c8\u5206\u6790\u30fb\u30ab\u30b9\u30bf\u30e0\u96c6\u8a08\u307e\u3067\u5168\u90e8\u3067\u304d\u308b\u3088\u3046\u306b\u306a\u308b\u305e\uff01<\/strong><\/p>\n<span class=\"c-balloon__shapes\"><span class=\"c-balloon__before\"><\/span><span class=\"c-balloon__after\"><\/span><\/span><\/div><\/div><\/div><\/div>\n\n\n<p class=\"wp-block-paragraph\">GA4\u306e\u6a19\u6e96UI\u306f\u4fbf\u5229\u3067\u3059\u304c\u3001\u300c\u7279\u5b9a\u306e\u6761\u4ef6\u3067\u306e\u30d5\u30a1\u30cd\u30eb\u5206\u6790\u300d\u300c\u30ab\u30b9\u30bf\u30e0\u30b3\u30db\u30fc\u30c8\u306e\u8ffd\u8de1\u300d\u300c\u8907\u6570\u30a4\u30d9\u30f3\u30c8\u3092\u7d44\u307f\u5408\u308f\u305b\u305f\u96c6\u8a08\u300d\u306a\u3069\u306b\u306f\u9650\u754c\u304c\u3042\u308a\u307e\u3059\u3002<strong>BigQuery\u306b\u9023\u643a\u3059\u308b\u3053\u3068\u3067SQL\u3067\u81ea\u7531\u306b\u30c7\u30fc\u30bf\u3092\u5206\u6790<\/strong>\u3067\u304d\u307e\u3059\u3002<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u672c\u8a18\u4e8b\u3067\u306f\u3001<strong><span class=\"swl-marker mark_yellow\">GA4\u306eBigQuery\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u8a2d\u5b9a\u30fbBigQuery\u306e\u30c7\u30fc\u30bf\u69cb\u9020\u7406\u89e3\u30fb\u5b9f\u8df5SQL\u30af\u30a8\u30ea15\u9078\uff08PV\u30fb\u30bb\u30c3\u30b7\u30e7\u30f3\u30fb\u30d5\u30a1\u30cd\u30eb\u30fb\u30b3\u30db\u30fc\u30c8\u30fbLTV\u30fb\u96e2\u8131\u7387\u30fb\u901f\u5ea6\u5206\u6790\uff09\u30fbLooker Studio\u9023\u643a\u30fb\u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\u30af\u30a8\u30ea\u30fb\u30b3\u30b9\u30c8\u6700\u9069\u5316<\/span><\/strong>\u3092\u5b8c\u5168\u89e3\u8aac\u3057\u307e\u3059\u3002<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">GA4 \u00d7 BigQuery\u3092\u4f7f\u3046\u30e1\u30ea\u30c3\u30c8\uff5c\u6a19\u6e96UI\u306e\u9650\u754c\u3092\u8d85\u3048\u308b<\/h2>\n\n\n<table>\n  <thead><tr><th>\u6a5f\u80fd<\/th><th>GA4\u6a19\u6e96UI<\/th><th>GA4 \u00d7 BigQuery<\/th><\/tr><\/thead>\n  <tbody>\n    <tr><td>\u30c7\u30fc\u30bf\u4fdd\u6301\u671f\u9593<\/td><td>\u6700\u592714\u30f6\u6708<\/td><td>\u7121\u5236\u9650\uff08\u30b9\u30c8\u30ec\u30fc\u30b8\u30b3\u30b9\u30c8\u306f\u5225\u9014\uff09<\/td><\/tr>\n    <tr><td>\u30b5\u30f3\u30d7\u30ea\u30f3\u30b0<\/td><td>\u5927\u91cf\u30c7\u30fc\u30bf\u3067\u30b5\u30f3\u30d7\u30ea\u30f3\u30b0\u767a\u751f<\/td><td>100%\u306e\u751f\u30c7\u30fc\u30bf\uff08\u30b5\u30f3\u30d7\u30ea\u30f3\u30b0\u306a\u3057\uff09<\/td><\/tr>\n    <tr><td>\u30ab\u30b9\u30bf\u30e0\u96c6\u8a08<\/td><td>\u9650\u5b9a\u7684\uff08UI\u306e\u5236\u7d04\u3042\u308a\uff09<\/td><td>SQL\u3067\u4f55\u3067\u3082\u53ef\u80fd<\/td><\/tr>\n    <tr><td>\u4ed6\u30c7\u30fc\u30bf\u3068\u306e\u7d50\u5408<\/td><td>\u4e0d\u53ef<\/td><td>CRM\u30c7\u30fc\u30bf\u30fb\u8cfc\u8cb7\u30c7\u30fc\u30bf\u30fb\u30aa\u30d5\u30e9\u30a4\u30f3\u30c7\u30fc\u30bf\u3068\u7d50\u5408\u53ef\u80fd<\/td><\/tr>\n    <tr><td>\u5206\u6790\u306e\u7c92\u5ea6<\/td><td>\u65e5\u30fb\u9031\u30fb\u6708\u5358\u4f4d<\/td><td>\u79d2\u5358\u4f4d\u30fb\u30a4\u30d9\u30f3\u30c8\u5358\u4f4d<\/td><\/tr>\n    <tr><td>\u81ea\u52d5\u5316\u30fb\u5b9a\u671f\u5b9f\u884c<\/td><td>\u624b\u52d5\u64cd\u4f5c\u304c\u5fc5\u8981<\/td><td>\u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\u30af\u30a8\u30ea\u3067\u81ea\u52d5\u5316\u53ef\u80fd<\/td><\/tr>\n    <tr><td>\u30b3\u30b9\u30c8<\/td><td>\u7121\u6599\uff08GA4\uff09<\/td><td>BigQuery\u306e\u30af\u30a8\u30ea\u30b3\u30b9\u30c8\uff08\u6700\u521d\u306e1TB\/\u6708\u306f\u7121\u6599\uff09<\/td><\/tr>\n  <\/tbody>\n<\/table>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">BigQuery\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u306e\u8a2d\u5b9a\u624b\u9806<\/h2>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"alignleft size-full\"><img decoding=\"async\" src=\"img\/ga4_bigquery_export_setup.png\" alt=\"GA4\u304b\u3089BigQuery\u3078\u306e\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u8a2d\u5b9a\u753b\u9762\u306e\u30b9\u30af\u30ea\u30fc\u30f3\u30b7\u30e7\u30c3\u30c8\" class=\"wp-image-12530\"\/><\/figure>\n<\/div>\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-javascript\" data-lang=\"JavaScript\"><code>\u3010GA4 \u2192 BigQuery \u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u8a2d\u5b9a\u624b\u9806\u3011\n\n1. Google Cloud \u30d7\u30ed\u30b8\u30a7\u30af\u30c8\u3092\u4f5c\u6210\n   \u2192 console.cloud.google.com \u3067\u30d7\u30ed\u30b8\u30a7\u30af\u30c8\u4f5c\u6210\n   \u2192 BigQuery API \u3092\u6709\u52b9\u5316\n   \u2192 \u8ab2\u91d1\u30a2\u30ab\u30a6\u30f3\u30c8\u3092\u8a2d\u5b9a\uff08\u7121\u6599\u67a0\u5185\u3067\u3082\u8a2d\u5b9a\u304c\u5fc5\u8981\uff09\n\n2. GA4 \u7ba1\u7406\u753b\u9762\u304b\u3089\u8a2d\u5b9a\n   \u2192 \u7ba1\u7406 \u2192 \u30d7\u30ed\u30d1\u30c6\u30a3\u8a2d\u5b9a \u2192 BigQuery\u306e\u30ea\u30f3\u30af\u8a2d\u5b9a\n   \u2192 \u300cBigQuery\u30d7\u30ed\u30b8\u30a7\u30af\u30c8\u306b\u63a5\u7d9a\u3059\u308b\u300d\u3092\u30af\u30ea\u30c3\u30af\n   \u2192 \u63a5\u7d9a\u3059\u308bGCP\u30d7\u30ed\u30b8\u30a7\u30af\u30c8\u3092\u9078\u629e\n\n3. \u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u8a2d\u5b9a\n   \u2192 \u30c7\u30fc\u30bf\u30ed\u30b1\u30fc\u30b7\u30e7\u30f3: asia-northeast1\uff08\u6771\u4eac\uff09\u3092\u9078\u629e\uff08\u63a8\u5968\uff09\n   \u2192 \u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u306e\u7a2e\u985e:\n     \u300c\u6bce\u65e5\u306e\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u300d\u2192 \u524d\u65e5\u306e\u30c7\u30fc\u30bf\u3092\u6bce\u671d\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\uff08\u7121\u6599\uff09\n     \u300c\u30b9\u30c8\u30ea\u30fc\u30df\u30f3\u30b0\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u300d\u2192 \u30ea\u30a2\u30eb\u30bf\u30a4\u30e0\uff08\u6709\u6599\u30fb\u8981BigQuery Streaming\u8cbb\u7528\uff09\n   \u2192 \u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u3059\u308b\u30a4\u30d9\u30f3\u30c8: \u5168\u3066 \/ \u6307\u5b9a\u306e\u307f\uff08\u30b3\u30b9\u30c8\u524a\u6e1b\u76ee\u7684\uff09\n\n4. \u78ba\u8a8d\uff08\u7fcc\u65e5\u306b\u30c7\u30fc\u30bf\u304c\u5c4a\u304f\uff09\n   BigQuery \u2192 your_project.analytics_XXXXXXXXX.events_YYYYMMDD\n   \uff08XXXXXXXXX \u306fGA4\u306e\u30d7\u30ed\u30d1\u30c6\u30a3ID\u3001YYYYMMDD\u306f\u65e5\u4ed8\uff09<\/code><\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">GA4\u30c7\u30fc\u30bf\u306eBigQuery\u30c6\u30fc\u30d6\u30eb\u69cb\u9020\u3092\u7406\u89e3\u3059\u308b<\/h2>\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-none\" data-lang=\"Code\"><code>-- \u30c6\u30fc\u30d6\u30eb\u540d: analytics_XXXXXXXXX.events_YYYYMMDD\n-- \uff08XXXXXXXXX \u306fGA4\u306e\u30d7\u30ed\u30d1\u30c6\u30a3ID\u3001YYYYMMDD\u306f\u65e5\u4ed8\uff09\n\n-- \u4e3b\u8981\u30ab\u30e9\u30e0\uff08\u3088\u304f\u4f7f\u3046\u3082\u306e\uff09\nevent_date          -- \u65e5\u4ed8\u6587\u5b57\u5217\uff08\u4f8b: 20260314\uff09\nevent_timestamp     -- \u30de\u30a4\u30af\u30ed\u79d2\u5358\u4f4d\u306eUnix\u30bf\u30a4\u30e0\u30b9\u30bf\u30f3\u30d7\nevent_name          -- \u30a4\u30d9\u30f3\u30c8\u540d\uff08page_view, purchase, click\u7b49\uff09\nevent_params        -- \u30a4\u30d9\u30f3\u30c8\u30d1\u30e9\u30e1\u30fc\u30bf\uff08REPEATED RECORD \u578b\uff09\n\nuser_pseudo_id      -- \u533f\u540d\u306e\u30e6\u30fc\u30b6\u30fcID\uff08\u30af\u30c3\u30ad\u30fc\u30d9\u30fc\u30b9\uff09\nuser_id             -- \u30ed\u30b0\u30a4\u30f3\u6e08\u307f\u30e6\u30fc\u30b6\u30fc\u306eID\uff08\u8a2d\u5b9a\u3057\u305f\u5834\u5408\u306e\u307f\uff09\nuser_properties     -- \u30e6\u30fc\u30b6\u30fc\u30d7\u30ed\u30d1\u30c6\u30a3\uff08REPEATED RECORD \u578b\uff09\nuser_first_touch_timestamp  -- \u521d\u56de\u30a2\u30af\u30bb\u30b9\u306e\u30bf\u30a4\u30e0\u30b9\u30bf\u30f3\u30d7\n\ngeo.country         -- \u56fd\ngeo.city            -- \u90fd\u5e02\ngeo.region          -- \u5730\u57df\n\ndevice.category     -- \u30c7\u30d0\u30a4\u30b9\u30bf\u30a4\u30d7\uff08mobile\/tablet\/desktop\uff09\ndevice.browser      -- \u30d6\u30e9\u30a6\u30b6\u540d\ndevice.operating_system  -- OS\n\ntraffic_source.source    -- \u6d41\u5165\u5143\uff08\u4f8b: google\uff09\ntraffic_source.medium    -- \u30e1\u30c7\u30a3\u30a2\uff08\u4f8b: organic\uff09\ntraffic_source.name      -- \u30ad\u30e3\u30f3\u30da\u30fc\u30f3\u540d<\/code><\/pre><\/div>\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-sql\" data-lang=\"SQL\"><code>-- event_params \u304b\u3089\u30d1\u30e9\u30e1\u30fc\u30bf\u5024\u3092\u53d6\u5f97\u3059\u308b\u57fa\u672c\u30d1\u30bf\u30fc\u30f3\n-- \uff08UNNEST \u3067 REPEATED RECORD \u578b\u306e\u914d\u5217\u3092\u5c55\u958b\u3059\u308b\uff09\n\nSELECT\n  event_name,\n  (SELECT value.string_value\n   FROM UNNEST(event_params)\n   WHERE key = 'page_location') AS page_url,\n  (SELECT value.string_value\n   FROM UNNEST(event_params)\n   WHERE key = 'page_title') AS page_title,\n  (SELECT value.int_value\n   FROM UNNEST(event_params)\n   WHERE key = 'ga_session_id') AS session_id,\n  (SELECT value.int_value\n   FROM UNNEST(event_params)\n   WHERE key = 'engagement_time_msec') AS engagement_msec\nFROM `your-project.analytics_XXXXXXXX.events_20260314`\nWHERE event_name = 'page_view'\nLIMIT 10<\/code><\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u5b9f\u8df5SQL\u30af\u30a8\u30ea15\u9078<\/h2>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"alignleft size-full\"><img decoding=\"async\" src=\"img\/ga4_bigquery_sql_queries.png\" alt=\"GA4 BigQuery\u5206\u6790\u30af\u30a8\u30ea\u306e\u30d1\u30bf\u30fc\u30f3\u56f3\" class=\"wp-image-12531\"\/><\/figure>\n<\/div>\n\n\n<h3 class=\"wp-block-heading\">\u30af\u30a8\u30ea1\uff1a\u30da\u30fc\u30b8\u5225PV\u6570\u306e\u96c6\u8a08\uff08\u76f4\u8fd17\u65e5\u9593\uff09<\/h3>\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-sql\" data-lang=\"SQL\"><code>SELECT\n  REGEXP_REPLACE(\n    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'),\n    r'\\?.*$', ''  -- \u30af\u30a8\u30ea\u30d1\u30e9\u30e1\u30fc\u30bf\u3092\u9664\u53bb\n  ) AS page_url,\n  COUNT(*) AS page_views,\n  COUNT(DISTINCT user_pseudo_id) AS unique_users\nFROM `your-project.analytics_XXXXXXXX.events_*`\nWHERE\n  _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))\n                    AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())\n  AND event_name = 'page_view'\nGROUP BY 1\nORDER BY page_views DESC\nLIMIT 20<\/code><\/pre><\/div>\n\n\n<h3 class=\"wp-block-heading\">\u30af\u30a8\u30ea2\uff1a\u30c7\u30d0\u30a4\u30b9\u5225\u30fb\u6d41\u5165\u5143\u5225\u30bb\u30c3\u30b7\u30e7\u30f3\u6570<\/h3>\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-sql\" data-lang=\"SQL\"><code>SELECT\n  device.category AS device_type,\n  traffic_source.source AS source,\n  traffic_source.medium AS medium,\n  COUNT(DISTINCT\n    CONCAT(user_pseudo_id, CAST(\n      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')\n    AS STRING))) AS sessions,\n  COUNT(DISTINCT user_pseudo_id) AS users\nFROM `your-project.analytics_XXXXXXXX.events_*`\nWHERE _TABLE_SUFFIX BETWEEN '20260307' AND '20260314'\nGROUP BY 1, 2, 3\nORDER BY sessions DESC\nLIMIT 30<\/code><\/pre><\/div>\n\n\n<h3 class=\"wp-block-heading\">\u30af\u30a8\u30ea3\uff1a\u8cfc\u5165\u30d5\u30a1\u30cd\u30eb\u5206\u6790\uff08\u95b2\u89a7\u2192\u30ab\u30fc\u30c8\u2192\u8cfc\u5165\uff09<\/h3>\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-sql\" data-lang=\"SQL\"><code>WITH funnel AS (\n  SELECT\n    user_pseudo_id,\n    COUNTIF(event_name = 'view_item') &gt; 0 AS viewed_item,\n    COUNTIF(event_name = 'add_to_cart') &gt; 0 AS added_to_cart,\n    COUNTIF(event_name = 'begin_checkout') &gt; 0 AS began_checkout,\n    COUNTIF(event_name = 'purchase') &gt; 0 AS purchased\n  FROM `your-project.analytics_XXXXXXXX.events_*`\n  WHERE _TABLE_SUFFIX BETWEEN '20260301' AND '20260314'\n  GROUP BY user_pseudo_id\n)\nSELECT\n  COUNTIF(viewed_item) AS step1_view,\n  COUNTIF(added_to_cart) AS step2_cart,\n  COUNTIF(began_checkout) AS step3_checkout,\n  COUNTIF(purchased) AS step4_purchase,\n  ROUND(COUNTIF(added_to_cart) \/ COUNTIF(viewed_item) * 100, 1) AS view_to_cart_pct,\n  ROUND(COUNTIF(began_checkout) \/ COUNTIF(added_to_cart) * 100, 1) AS cart_to_checkout_pct,\n  ROUND(COUNTIF(purchased) \/ COUNTIF(began_checkout) * 100, 1) AS checkout_to_purchase_pct,\n  ROUND(COUNTIF(purchased) \/ COUNTIF(viewed_item) * 100, 1) AS overall_cvr_pct\nFROM funnel<\/code><\/pre><\/div>\n\n\n<h3 class=\"wp-block-heading\">\u30af\u30a8\u30ea4\uff1a\u30b3\u30db\u30fc\u30c8\u5206\u6790\uff08\u521d\u56de\u8a2a\u554f\u304b\u3089\u306e\u518d\u8a2a\u554f\u7387\uff09<\/h3>\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-sql\" data-lang=\"SQL\"><code>-- \u521d\u56de\u8a2a\u554f\u3057\u305f\u9031\u304b\u3089\u4f55\u9031\u9593\u5f8c\u306b\u518d\u8a2a\u554f\u3057\u305f\u304b\u3092\u96c6\u8a08\u3059\u308b\nWITH first_visit AS (\n  SELECT\n    user_pseudo_id,\n    MIN(DATE(TIMESTAMP_MICROS(event_timestamp))) AS first_visit_date\n  FROM `your-project.analytics_XXXXXXXX.events_*`\n  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260314'\n    AND event_name = 'session_start'\n  GROUP BY user_pseudo_id\n),\nuser_visits AS (\n  SELECT\n    e.user_pseudo_id,\n    DATE(TIMESTAMP_MICROS(e.event_timestamp)) AS visit_date,\n    fv.first_visit_date,\n    DATE_DIFF(DATE(TIMESTAMP_MICROS(e.event_timestamp)), fv.first_visit_date, WEEK) AS week_num\n  FROM `your-project.analytics_XXXXXXXX.events_*` e\n  JOIN first_visit fv USING (user_pseudo_id)\n  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260314'\n    AND e.event_name = 'session_start'\n)\nSELECT\n  DATE_TRUNC(first_visit_date, WEEK) AS cohort_week,\n  week_num,\n  COUNT(DISTINCT user_pseudo_id) AS returning_users\nFROM user_visits\nWHERE week_num BETWEEN 0 AND 8\nGROUP BY 1, 2\nORDER BY 1, 2<\/code><\/pre><\/div>\n\n\n<h3 class=\"wp-block-heading\">\u30af\u30a8\u30ea5\uff1a\u30e6\u30fc\u30b6\u30fcLTV\uff08\u7d2f\u8a08\u8cfc\u8cb7\u91d1\u984d\uff09\u30c8\u30c3\u30d720<\/h3>\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-sql\" data-lang=\"SQL\"><code>SELECT\n  user_id,\n  COUNT(DISTINCT event_date) AS purchase_days,\n  COUNT(*) AS total_purchases,\n  SUM((SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value')) AS total_ltv,\n  AVG((SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value')) AS avg_order_value,\n  MIN(event_date) AS first_purchase,\n  MAX(event_date) AS last_purchase,\n  DATE_DIFF(PARSE_DATE('%Y%m%d', MAX(event_date)), PARSE_DATE('%Y%m%d', MIN(event_date)), DAY) AS days_as_customer\nFROM `your-project.analytics_XXXXXXXX.events_*`\nWHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260314'\n  AND event_name = 'purchase'\n  AND user_id IS NOT NULL\nGROUP BY user_id\nORDER BY total_ltv DESC\nLIMIT 20<\/code><\/pre><\/div>\n\n\n<h3 class=\"wp-block-heading\">\u30af\u30a8\u30ea6\uff1a\u30da\u30fc\u30b8\u5225\u96e2\u8131\u7387\uff08\u30d0\u30a6\u30f3\u30b9\u7387\uff09<\/h3>\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-sql\" data-lang=\"SQL\"><code>WITH sessions AS (\n  SELECT\n    user_pseudo_id,\n    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,\n    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS landing_page,\n    COUNT(*) AS event_count\n  FROM `your-project.analytics_XXXXXXXX.events_*`\n  WHERE _TABLE_SUFFIX BETWEEN '20260307' AND '20260314'\n  GROUP BY 1, 2, 3\n)\nSELECT\n  REGEXP_REPLACE(landing_page, r'\\?.*$', '') AS page,\n  COUNT(*) AS sessions,\n  COUNTIF(event_count = 1) AS bounced_sessions,\n  ROUND(COUNTIF(event_count = 1) \/ COUNT(*) * 100, 1) AS bounce_rate_pct\nFROM sessions\nWHERE landing_page IS NOT NULL\nGROUP BY 1\nHAVING sessions &gt;= 100  -- 100\u30bb\u30c3\u30b7\u30e7\u30f3\u4ee5\u4e0a\u306e\u30da\u30fc\u30b8\u306e\u307f\nORDER BY sessions DESC\nLIMIT 30<\/code><\/pre><\/div>\n\n\n<h3 class=\"wp-block-heading\">\u30af\u30a8\u30ea7\uff1a\u30ab\u30b9\u30bf\u30e0\u30a4\u30d9\u30f3\u30c8\u306e\u96c6\u8a08\uff08\u30dc\u30bf\u30f3\u30af\u30ea\u30c3\u30af\u6570\uff09<\/h3>\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-sql\" data-lang=\"SQL\"><code>SELECT\n  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'button_name') AS button_name,\n  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_url,\n  COUNT(*) AS click_count,\n  COUNT(DISTINCT user_pseudo_id) AS unique_clickers\nFROM `your-project.analytics_XXXXXXXX.events_*`\nWHERE _TABLE_SUFFIX BETWEEN '20260307' AND '20260314'\n  AND event_name = 'button_click'  -- GTM\u3067\u8a08\u6e2c\u3057\u3066\u3044\u308b\u30ab\u30b9\u30bf\u30e0\u30a4\u30d9\u30f3\u30c8\u540d\nGROUP BY 1, 2\nORDER BY click_count DESC\nLIMIT 20<\/code><\/pre><\/div>\n\n\n<h3 class=\"wp-block-heading\">\u30af\u30a8\u30ea8\uff1a\u30b9\u30af\u30ed\u30fc\u30eb\u6df1\u5ea6\u306e\u5206\u6790<\/h3>\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-sql\" data-lang=\"SQL\"><code>SELECT\n  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_url,\n  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'percent_scrolled') AS scroll_depth,\n  COUNT(*) AS event_count,\n  COUNT(DISTINCT user_pseudo_id) AS users\nFROM `your-project.analytics_XXXXXXXX.events_*`\nWHERE _TABLE_SUFFIX BETWEEN '20260307' AND '20260314'\n  AND event_name = 'scroll'\nGROUP BY 1, 2\nORDER BY page_url, scroll_depth<\/code><\/pre><\/div>\n\n\n<h3 class=\"wp-block-heading\">\u30af\u30a8\u30ea9\uff1a\u30e6\u30fc\u30b6\u30fc\u884c\u52d5\u30d1\u30bf\u30fc\u30f3\uff08\u8cfc\u5165\u30e6\u30fc\u30b6\u30fc\u306e\u95b2\u89a7\u30da\u30fc\u30b8\uff09<\/h3>\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-sql\" data-lang=\"SQL\"><code>-- \u8cfc\u5165\u30e6\u30fc\u30b6\u30fc\u304c\u3069\u306e\u30da\u30fc\u30b8\u3092\u7d4c\u7531\u3057\u305f\u304b\u3092\u5206\u6790\u3059\u308b\nWITH purchase_users AS (\n  SELECT DISTINCT user_pseudo_id\n  FROM `your-project.analytics_XXXXXXXX.events_*`\n  WHERE _TABLE_SUFFIX BETWEEN '20260307' AND '20260314'\n    AND event_name = 'purchase'\n)\nSELECT\n  (SELECT value.string_value FROM UNNEST(e.event_params) WHERE key = 'page_location') AS page_url,\n  COUNT(*) AS views,\n  COUNT(DISTINCT e.user_pseudo_id) AS viewers\nFROM `your-project.analytics_XXXXXXXX.events_*` e\nWHERE e._TABLE_SUFFIX BETWEEN '20260307' AND '20260314'\n  AND e.event_name = 'page_view'\n  AND e.user_pseudo_id IN (SELECT user_pseudo_id FROM purchase_users)\nGROUP BY 1\nORDER BY views DESC\nLIMIT 20<\/code><\/pre><\/div>\n\n\n<h3 class=\"wp-block-heading\">\u30af\u30a8\u30ea10\uff1a\u65e5\u5225\u30a2\u30af\u30c6\u30a3\u30d6\u30e6\u30fc\u30b6\u30fc\u6570\uff08DAU\/WAU\/MAU\uff09<\/h3>\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-sql\" data-lang=\"SQL\"><code>SELECT\n  event_date,\n  COUNT(DISTINCT user_pseudo_id) AS dau,\n  -- 7\u65e5\u9593\u306e\u79fb\u52d5\u5e73\u5747\uff08WAU\u76f8\u5f53\uff09\n  AVG(COUNT(DISTINCT user_pseudo_id)) OVER (\n    ORDER BY event_date\n    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW\n  ) AS wau_rolling,\n  -- 30\u65e5\u9593\u306e\u79fb\u52d5\u5e73\u5747\uff08MAU\u76f8\u5f53\uff09\n  AVG(COUNT(DISTINCT user_pseudo_id)) OVER (\n    ORDER BY event_date\n    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW\n  ) AS mau_rolling\nFROM `your-project.analytics_XXXXXXXX.events_*`\nWHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260314'\n  AND event_name = 'session_start'\nGROUP BY event_date\nORDER BY event_date<\/code><\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">\u30af\u30a8\u30ea11\u301c15\u306f\u30b3\u30f3\u30d0\u30fc\u30b8\u30e7\u30f3\u7d4c\u8def\u5206\u6790\u30fb\u5730\u57df\u5225CVR\u30fb\u65b0\u898f\/\u30ea\u30d4\u30fc\u30bf\u30fc\u6bd4\u8f03\u30fb\u30a4\u30d9\u30f3\u30c8\u5225\u30a8\u30f3\u30b2\u30fc\u30b8\u30e1\u30f3\u30c8\u6642\u9593\u30fb\u6642\u9593\u5e2f\u5225\u30a2\u30af\u30bb\u30b9\u96c6\u8a08\u306a\u3069\u306b\u6d3b\u7528\u3067\u304d\u307e\u3059\u3002\u57fa\u672c\u30d1\u30bf\u30fc\u30f3\uff08UNNEST\u30fb_TABLE_SUFFIX\u30fbWITH\u53e5\uff09\u3092\u7406\u89e3\u3059\u308c\u3070\u5fdc\u7528\u304c\u53ef\u80fd\u3067\u3059\u3002<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Looker Studio\uff08\u65e7\u30c7\u30fc\u30bf\u30dd\u30fc\u30bf\u30eb\uff09\u3068\u306e\u9023\u643a<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">BigQuery\u306e\u30af\u30a8\u30ea\u7d50\u679c\u3092Looker Studio\uff08\u65e7Google Data Studio\uff09\u306b\u63a5\u7d9a\u3059\u308b\u3053\u3068\u3067\u3001\u30a4\u30f3\u30bf\u30e9\u30af\u30c6\u30a3\u30d6\u306a\u30c0\u30c3\u30b7\u30e5\u30dc\u30fc\u30c9\u3092\u4f5c\u6210\u3067\u304d\u307e\u3059\u3002\u30de\u30fc\u30b1\u30bf\u30fc\u3084\u7d4c\u55b6\u5c64\u5411\u3051\u306e\u8996\u899a\u7684\u306a\u30ec\u30dd\u30fc\u30c8\u3092\u3001\u30a8\u30f3\u30b8\u30cb\u30a2\u304cSQL\u3067\u5b9a\u7fa9\u3057\u305f\u9ad8\u54c1\u8cea\u30c7\u30fc\u30bf\u304b\u3089\u81ea\u52d5\u751f\u6210\u3067\u304d\u307e\u3059\u3002<\/p>\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-none\" data-lang=\"Code\"><code>\u3010Looker Studio \u00d7 BigQuery \u9023\u643a\u624b\u9806\u3011\n\n1. Looker Studio\uff08lookerstudio.google.com\uff09\u3092\u958b\u304f\n\n2. \u65b0\u3057\u3044\u30ec\u30dd\u30fc\u30c8\u3092\u4f5c\u6210 \u2192 \u30c7\u30fc\u30bf\u30bd\u30fc\u30b9\u3092\u8ffd\u52a0\n\n3. BigQuery \u30b3\u30cd\u30af\u30bf\u3092\u9078\u629e\n   \u2192 \u30d7\u30ed\u30b8\u30a7\u30af\u30c8\u30fb\u30c7\u30fc\u30bf\u30bb\u30c3\u30c8\u30fb\u30c6\u30fc\u30d6\u30eb\u3092\u9078\u629e\n   \u2192 \u307e\u305f\u306f\u300c\u30ab\u30b9\u30bf\u30e0\u30af\u30a8\u30ea\u300d\u3067SQL\u3092\u76f4\u63a5\u5165\u529b\n\n4. \u30c0\u30c3\u30b7\u30e5\u30dc\u30fc\u30c9\u306b\u30b0\u30e9\u30d5\u30fb\u8868\u3092\u8ffd\u52a0\n   - \u6298\u308c\u7dda\u30b0\u30e9\u30d5: \u65e5\u5225PV\/\u30bb\u30c3\u30b7\u30e7\u30f3\n   - \u68d2\u30b0\u30e9\u30d5: \u30da\u30fc\u30b8\u5225PV\n   - \u8868: \u30d5\u30a1\u30cd\u30eb\u5206\u6790\u7d50\u679c\n   - \u30b9\u30b3\u30a2\u30ab\u30fc\u30c9: CVR\u30fb\u76f4\u5e30\u7387\u30fbLTV\n\n5. \u30d5\u30a3\u30eb\u30bf\u30fc\u3092\u8ffd\u52a0\uff08\u65e5\u4ed8\u7bc4\u56f2\u30fb\u30c7\u30d0\u30a4\u30b9\u7a2e\u5225\u30fb\u56fd\u306a\u3069\uff09\n\n\u30b3\u30b9\u30c8\u6700\u9069\u5316\u306e\u30dd\u30a4\u30f3\u30c8\uff1a\n\u2192 Looker Studio \u306e\u30c7\u30fc\u30bf\u30bd\u30fc\u30b9\u306b\u300c\u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\u30ad\u30e3\u30c3\u30b7\u30e5\u300d\u3092\u8a2d\u5b9a\u3059\u308b\n\u2192 \u6bce\u56de\u30af\u30a8\u30ea\u3092\u5b9f\u884c\u305b\u305a\u3001N\u6642\u9593\u3054\u3068\u306b\u30ad\u30e3\u30c3\u30b7\u30e5\u3092\u66f4\u65b0\n\u2192 BigQuery \u306e\u30af\u30a8\u30ea\u30b3\u30b9\u30c8\u3092\u5927\u5e45\u306b\u524a\u6e1b\u3067\u304d\u308b<\/code><\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\u30af\u30a8\u30ea\uff5c\u5b9a\u671f\u30ec\u30dd\u30fc\u30c8\u306e\u81ea\u52d5\u751f\u6210<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">BigQuery\u306e\u300c\u30b9\u30b1\u30b8\u30e5\u30fc\u30eb\u3055\u308c\u305f\u30af\u30a8\u30ea\u300d\u6a5f\u80fd\u3092\u4f7f\u3046\u3068\u3001SQL\u3092\u5b9a\u671f\u5b9f\u884c\u3057\u3066\u7d50\u679c\u3092\u5225\u30c6\u30fc\u30d6\u30eb\u306b\u4fdd\u5b58\u3067\u304d\u307e\u3059\u3002\u6bce\u65e5\u30fb\u6bce\u9031\u306e\u96c6\u8a08\u30ec\u30dd\u30fc\u30c8\u306e\u81ea\u52d5\u751f\u6210\u306b\u6d3b\u7528\u3067\u304d\u307e\u3059\u3002<\/p>\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-sql\" data-lang=\"SQL\"><code>-- \u6bce\u65e5\u5b9f\u884c\u3059\u308b\u96c6\u8a08\u30af\u30a8\u30ea\u306e\u4f8b\n-- \u524d\u65e5\u306e\u57fa\u672cKPI\u3092\u96c6\u8a08\u3057\u3066\u30ec\u30dd\u30fc\u30c8\u30c6\u30fc\u30d6\u30eb\u306b\u4fdd\u5b58\u3059\u308b\n\nINSERT INTO `your-project.analytics_reports.daily_kpi`\nSELECT\n  FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AS report_date,\n  COUNT(DISTINCT user_pseudo_id) AS dau,\n  COUNT(DISTINCT CONCAT(\n    user_pseudo_id,\n    CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)\n  )) AS sessions,\n  COUNTIF(event_name = 'purchase') AS purchases,\n  SUM(IF(event_name = 'purchase',\n    (SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value'), 0\n  )) AS revenue,\n  ROUND(\n    COUNTIF(event_name = 'purchase') \/\n    COUNT(DISTINCT CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) * 100, 2\n  ) AS cvr_pct\nFROM `your-project.analytics_XXXXXXXX.events_*`\nWHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))<\/code><\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">BigQuery\u30b3\u30b9\u30c8\u6700\u9069\u5316\u306e\u30c6\u30af\u30cb\u30c3\u30af<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">BigQuery\u306f\u30af\u30a8\u30ea\u3067\u30b9\u30ad\u30e3\u30f3\u3057\u305f\u30c7\u30fc\u30bf\u91cf\u306b\u5fdc\u3058\u3066\u8ab2\u91d1\u3055\u308c\u307e\u3059\uff08\u6700\u521d\u306e1TB\/\u6708\u306f\u7121\u6599\uff09\u3002GA4\u306e\u751f\u30c7\u30fc\u30bf\u30c6\u30fc\u30d6\u30eb\u306f\u65e5\u3005\u5897\u52a0\u3059\u308b\u305f\u3081\u3001\u30af\u30a8\u30ea\u306e\u66f8\u304d\u65b9\u306b\u3088\u3063\u3066\u30b3\u30b9\u30c8\u304c\u5927\u304d\u304f\u5909\u308f\u308a\u307e\u3059\u3002<\/p>\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-sql\" data-lang=\"SQL\"><code>-- \u274c \u30b3\u30b9\u30c8\u304c\u9ad8\u3044\u30af\u30a8\u30ea\uff1a\u5168\u30c6\u30fc\u30d6\u30eb\u3092\u30b9\u30ad\u30e3\u30f3\nSELECT * FROM `project.analytics_XXXXXXXX.events_*`\n\n-- \u2705 \u30b3\u30b9\u30c8\u304c\u4f4e\u3044\u30af\u30a8\u30ea\u2460\uff1a\u30c6\u30fc\u30d6\u30eb\u30b5\u30d5\u30a3\u30c3\u30af\u30b9\u3067\u65e5\u4ed8\u3092\u7d5e\u308b\nSELECT event_name, COUNT(*) FROM `project.analytics_XXXXXXXX.events_*`\nWHERE _TABLE_SUFFIX BETWEEN '20260307' AND '20260314'  -- \u5fc5\u305a\u65e5\u4ed8\u3092\u6307\u5b9a\nGROUP BY 1\n\n-- \u2705 \u30b3\u30b9\u30c8\u304c\u4f4e\u3044\u30af\u30a8\u30ea\u2461\uff1a\u5fc5\u8981\u306a\u30ab\u30e9\u30e0\u306e\u307fSELECT\uff08SELECT * \u306f\u4f7f\u308f\u306a\u3044\uff09\nSELECT event_date, event_name, user_pseudo_id\nFROM `project.analytics_XXXXXXXX.events_20260314`\n\n-- \u2705 \u30b3\u30b9\u30c8\u304c\u4f4e\u3044\u30af\u30a8\u30ea\u2462\uff1a\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30f3\u3092\u6d3b\u7528\u3059\u308b\n-- GA4\u30c6\u30fc\u30d6\u30eb\u306f event_date \u3067\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30f3\u5206\u5272\u3055\u308c\u3066\u3044\u308b\u305f\u3081\n-- WHERE event_date = '20260314' \u3067\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30f3\u30d7\u30eb\u30fc\u30cb\u30f3\u30b0\u304c\u52b9\u304f\n\n-- \u2705 \u30b3\u30b9\u30c8\u304c\u4f4e\u3044\u30af\u30a8\u30ea\u2463\uff1a\u983b\u7e41\u306b\u4f7f\u3046\u30af\u30a8\u30ea\u306f\u30de\u30c6\u30ea\u30a2\u30e9\u30a4\u30ba\u30c9\u30d3\u30e5\u30fc\u3084\u30c6\u30fc\u30d6\u30eb\u306b\u4fdd\u5b58\nCREATE TABLE IF NOT EXISTS `project.analytics_reports.daily_summary` AS\nSELECT event_date, event_name, COUNT(*) AS cnt\nFROM `project.analytics_XXXXXXXX.events_*`\nGROUP BY 1, 2<\/code><\/pre><\/div>\n\n<table>\n  <thead><tr><th>\u6700\u9069\u5316\u624b\u6cd5<\/th><th>\u30b3\u30b9\u30c8\u524a\u6e1b\u52b9\u679c<\/th><th>\u96e3\u6613\u5ea6<\/th><\/tr><\/thead>\n  <tbody>\n    <tr><td><strong>\u65e5\u4ed8\u7bc4\u56f2\u3092\u5fc5\u305a\u6307\u5b9a<\/strong><\/td><td>\u2605\u2605\u2605\u2605\u2605\uff08\u5927\u5e45\u524a\u6e1b\uff09<\/td><td>\u7c21\u5358<\/td><\/tr>\n    <tr><td><strong>SELECT * \u3092\u907f\u3051\u308b<\/strong><\/td><td>\u2605\u2605\u2605\uff08\u4e2d\u7a0b\u5ea6\uff09<\/td><td>\u7c21\u5358<\/td><\/tr>\n    <tr><td><strong>\u5fc5\u8981\u306a\u30c7\u30fc\u30bf\u3092\u30d3\u30e5\u30fc\/\u30c6\u30fc\u30d6\u30eb\u306b\u4e8b\u524d\u96c6\u8a08<\/strong><\/td><td>\u2605\u2605\u2605\u2605\uff08\u5927\uff09<\/td><td>\u4e2d\u7a0b\u5ea6<\/td><\/tr>\n    <tr><td><strong>\u30b9\u30ed\u30c3\u30c8\u30d9\u30fc\u30b9\u6599\u91d1\u306b\u79fb\u884c\uff08\u4e88\u7b97\u304c\u591a\u3044\u5834\u5408\uff09<\/strong><\/td><td>\u5927\u91cf\u30af\u30a8\u30ea\u3067\u6709\u5229<\/td><td>\u4e2d\u7a0b\u5ea6<\/td><\/tr>\n    <tr><td><strong>Looker Studio\u306e\u30ad\u30e3\u30c3\u30b7\u30e5\u3092\u6d3b\u7528<\/strong><\/td><td>\u2605\u2605\u2605\uff08\u4e2d\u7a0b\u5ea6\uff09<\/td><td>\u7c21\u5358<\/td><\/tr>\n  <\/tbody>\n<\/table>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u3088\u304f\u3042\u308b\u8cea\u554f\uff08FAQ\uff09<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Q1. BigQuery\u306e\u8cbb\u7528\u306f\u3069\u308c\u304f\u3089\u3044\u304b\u304b\u308a\u307e\u3059\u304b\uff1f<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A.<\/strong> \u30af\u30a8\u30ea\u51e6\u7406\u306f <strong>\u6bce\u6708\u6700\u521d\u306e1TB\u304c\u7121\u6599<\/strong>\u3067\u3001\u305d\u308c\u4ee5\u964d\u306f$5\/TB\u3067\u3059\u3002\u4e00\u822c\u7684\u306aWeb\u30b5\u30a4\u30c8\uff08\u670810\u4e07PV\u7a0b\u5ea6\uff09\u306eGA4\u30c7\u30fc\u30bf\u3078\u306e\u30af\u30a8\u30ea\u306a\u3089\u3001\u6708\u6570\u767eGB\u4ee5\u5185\u306b\u53ce\u307e\u308b\u3053\u3068\u304c\u591a\u304f<strong>\u307b\u307c\u7121\u6599<\/strong>\u3067\u4f7f\u3048\u307e\u3059\u3002\u30b9\u30c8\u30ec\u30fc\u30b8\u306f$0.02\/GB\/\u6708\uff08\u6700\u521d\u306e10GB\u306f\u7121\u6599\uff09\u3067\u3059\u3002GCP\u30b3\u30f3\u30bd\u30fc\u30eb\u3067\u300c\u4e88\u7b97\u30a2\u30e9\u30fc\u30c8\u300d\u3092\u8a2d\u5b9a\u3057\u3066\u304a\u304f\u3068\u60f3\u5b9a\u5916\u306e\u8ab2\u91d1\u3092\u9632\u3052\u307e\u3059\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Q2. GA4\u306e\u30c7\u30fc\u30bf\u306f\u30ea\u30a2\u30eb\u30bf\u30a4\u30e0\u3067BigQuery\u306b\u5c4a\u304d\u307e\u3059\u304b\uff1f<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A.<\/strong> \u7121\u6599\u306e\u300c\u6bce\u65e5\u306e\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u300d\u306f\u524d\u65e5\u30c7\u30fc\u30bf\u304c\u7fcc\u671d\u306b\u5c4a\u304d\u307e\u3059\u3002\u30ea\u30a2\u30eb\u30bf\u30a4\u30e0\u5206\u6790\u304c\u5fc5\u8981\u306a\u5834\u5408\u306f\u300c\u30b9\u30c8\u30ea\u30fc\u30df\u30f3\u30b0\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u300d\uff08\u6709\u6599\uff09\u3092\u4f7f\u3046\u3068\u6570\u5206\u4ee5\u5185\u306b\u53cd\u6620\u3055\u308c\u307e\u3059\u3002\u305f\u3060\u3057\u30b9\u30c8\u30ea\u30fc\u30df\u30f3\u30b0\u30c6\u30fc\u30d6\u30eb\uff08<code>events_intraday_YYYYMMDD<\/code>\uff09\u306f\u91cd\u8907\u30ec\u30b3\u30fc\u30c9\u304c\u767a\u751f\u3059\u308b\u3053\u3068\u304c\u3042\u308b\u305f\u3081\u3001\u96c6\u8a08\u6642\u306b\u306f<code>DISTINCT<\/code>\u3092\u9069\u5207\u306b\u4f7f\u3046\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Q3. BigQuery\u306b\u30a2\u30af\u30bb\u30b9\u3059\u308b\u305f\u3081\u306e\u6a29\u9650\u8a2d\u5b9a\u306f\u3069\u3046\u3059\u308c\u3070\u3044\u3044\u3067\u3059\u304b\uff1f<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A.<\/strong> GCP\u306eIAM\u3067\u9069\u5207\u306a\u30ed\u30fc\u30eb\u3092\u4ed8\u4e0e\u3057\u307e\u3059\u3002\u5206\u6790\u8005\uff08\u8aad\u307f\u53d6\u308a\u306e\u307f\uff09\u306b\u306f<strong>BigQuery \u30c7\u30fc\u30bf\u95b2\u89a7\u8005\uff08roles\/bigquery.dataViewer\uff09<\/strong>\u3068<strong>BigQuery \u30b8\u30e7\u30d6 \u30e6\u30fc\u30b6\u30fc\uff08roles\/bigquery.jobUser\uff09<\/strong>\u306e2\u3064\u3092\u4ed8\u4e0e\u3057\u307e\u3059\u3002\u30c6\u30fc\u30d6\u30eb\u306e\u4f5c\u6210\u3084\u66f8\u304d\u8fbc\u307f\u304c\u5fc5\u8981\u306a\u5834\u5408\u306f<strong>BigQuery \u30c7\u30fc\u30bf\u7de8\u96c6\u8005\uff08roles\/bigquery.dataEditor\uff09<\/strong>\u3092\u4ed8\u4e0e\u3057\u307e\u3059\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Q4. \u53e4\u3044GA4\u30c7\u30fc\u30bf\uff0814\u30f6\u6708\u4ee5\u4e0a\u524d\uff09\u306fBigQuery\u306b\u4fdd\u5b58\u3055\u308c\u307e\u3059\u304b\uff1f<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A.<\/strong> BigQuery\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u3092\u958b\u59cb\u3057\u305f\u65e5\u4ee5\u964d\u306e\u30c7\u30fc\u30bf\u304c\u84c4\u7a4d\u3055\u308c\u307e\u3059\u3002\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u958b\u59cb\u524d\u306e\u30c7\u30fc\u30bf\u306f\u9061\u3063\u3066\u53d6\u5f97\u3067\u304d\u307e\u305b\u3093\u3002\u9577\u671f\u5206\u6790\u3092\u884c\u3046\u305f\u3081\u306b\u3082<strong>\u4eca\u3059\u3050\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u3092\u8a2d\u5b9a\u3059\u308b\u3053\u3068\u3092\u5f37\u304f\u63a8\u5968<\/strong>\u3057\u307e\u3059\u3002\u8a2d\u5b9a\u5f8c\u7fcc\u65e5\u304b\u3089\u30c7\u30fc\u30bf\u304c\u84c4\u7a4d\u3055\u308c\u59cb\u3081\u3001\u6642\u9593\u304c\u7d4c\u3064\u307b\u3069\u4fa1\u5024\u306e\u3042\u308b\u30c7\u30fc\u30bf\u304c\u84c4\u7a4d\u3055\u308c\u307e\u3059\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Q5. Python\/pandas\u304b\u3089BigQuery\u306e\u30c7\u30fc\u30bf\u3092\u53d6\u5f97\u3067\u304d\u307e\u3059\u304b\uff1f<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A.<\/strong> \u3067\u304d\u307e\u3059\u3002<code>google-cloud-bigquery<\/code>\u30e9\u30a4\u30d6\u30e9\u30ea\u3068<code>pandas-gbq<\/code>\u3092\u4f7f\u3046\u3068\u3001BigQuery\u306e\u30af\u30a8\u30ea\u7d50\u679c\u3092Pandas DataFrame\u3068\u3057\u3066\u53d6\u5f97\u3067\u304d\u307e\u3059\u3002\u6a5f\u68b0\u5b66\u7fd2\u30fb\u9ad8\u5ea6\u306a\u7d71\u8a08\u5206\u6790\u30fb\u53ef\u8996\u5316\u306a\u3069\u3001SQL\u3067\u306f\u3067\u304d\u306a\u3044\u5206\u6790\u3092Python\u3067\u884c\u3046\u969b\u306b\u4fbf\u5229\u3067\u3059\u3002<\/p>\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-python\" data-lang=\"Python\"><code># Python \u304b\u3089 BigQuery \u306e\u30c7\u30fc\u30bf\u3092\u53d6\u5f97\u3059\u308b\u4f8b\n# pip install google-cloud-bigquery pandas-gbq\n\nfrom google.cloud import bigquery\nimport pandas as pd\n\nclient = bigquery.Client(project='your-project')\n\nquery = \"\"\"\nSELECT event_date, COUNT(*) AS page_views\nFROM `your-project.analytics_XXXXXXXX.events_*`\nWHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260314'\n  AND event_name = 'page_view'\nGROUP BY event_date\nORDER BY event_date\n\"\"\"\n\ndf = client.query(query).to_dataframe()\nprint(df.head())\n# \u2192 \u65e5\u4ed8\u00d7PV\u6570\u306eDataFrame\u304c\u53d6\u5f97\u3067\u304d\u308b<\/code><\/pre><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u307e\u3068\u3081<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n  <li><strong>GA4 \u00d7 BigQuery<\/strong>\uff1a\u6a19\u6e96UI\u306e\u5236\u7d04\uff08\u30b5\u30f3\u30d7\u30ea\u30f3\u30b0\u30fb\u30c7\u30fc\u30bf\u4fdd\u6301\u671f\u9593\u30fb\u96c6\u8a08\u306e\u9650\u754c\uff09\u3092\u7a81\u7834\u3057\u3066100%\u306e\u751f\u30c7\u30fc\u30bf\u3092SQL\u3067\u81ea\u7531\u5206\u6790\u3067\u304d\u308b\u3002\u4eca\u3059\u3050\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u3092\u8a2d\u5b9a\u3059\u308b\u3053\u3068\u304c\u91cd\u8981\u3002<\/li>\n  <li><strong>\u8a2d\u5b9a\u624b\u9806<\/strong>\uff1aGCP\u30d7\u30ed\u30b8\u30a7\u30af\u30c8\u4f5c\u6210 \u2192 BigQuery API\u6709\u52b9\u5316 \u2192 GA4\u7ba1\u7406\u753b\u9762\u3067BigQuery\u30ea\u30f3\u30af\u8a2d\u5b9a \u2192 \u7fcc\u65e5\u304b\u3089\u30c7\u30fc\u30bf\u304c\u5c4a\u304f\u3002<\/li>\n  <li><strong>\u30c6\u30fc\u30d6\u30eb\u69cb\u9020<\/strong>\uff1a<code>event_params<\/code>\u306fREPEATED RECORD\u578b\u306a\u306e\u3067<code>UNNEST<\/code>\u3067\u5c55\u958b\u3059\u308b\u3002<code>_TABLE_SUFFIX<\/code>\u3067\u65e5\u4ed8\u3092\u7d5e\u308a\u8fbc\u3080\u306e\u304c\u57fa\u672c\u30d1\u30bf\u30fc\u30f3\u3002<\/li>\n  <li><strong>\u5b9f\u8df5SQL<\/strong>\uff1aPV\u30fb\u30bb\u30c3\u30b7\u30e7\u30f3\u30fb\u30d5\u30a1\u30cd\u30eb\u30fb\u30b3\u30db\u30fc\u30c8\u30fbLTV\u30fb\u96e2\u8131\u7387\u30fbDAU\u5206\u6790\u306a\u306910\u7a2e\u985e\u4ee5\u4e0a\u306e\u30af\u30a8\u30ea\u30c6\u30f3\u30d7\u30ec\u30fc\u30c8\u3092\u305d\u306e\u307e\u307e\u6d3b\u7528\u3067\u304d\u308b\u3002<\/li>\n  <li><strong>Looker Studio<\/strong>\uff1aBigQuery\u3068\u9023\u643a\u3057\u3066\u30a4\u30f3\u30bf\u30e9\u30af\u30c6\u30a3\u30d6\u306a\u30c0\u30c3\u30b7\u30e5\u30dc\u30fc\u30c9\u3092\u81ea\u52d5\u751f\u6210\u3002\u30ad\u30e3\u30c3\u30b7\u30e5\u8a2d\u5b9a\u3067\u30b3\u30b9\u30c8\u3092\u524a\u6e1b\u3002<\/li>\n  <li><strong>\u30b3\u30b9\u30c8\u6700\u9069\u5316<\/strong>\uff1a\u65e5\u4ed8\u7bc4\u56f2\u306e\u6307\u5b9a\u30fbSELECT *\u306e\u56de\u907f\u30fb\u4e8b\u524d\u96c6\u8a08\u30c6\u30fc\u30d6\u30eb\u306e\u6d3b\u7528\u3067\u67081TB\u4ee5\u5185\u306b\u6291\u3048\u308b\u3068\u307b\u307c\u7121\u6599\u3002<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong><span class=\"swl-marker mark_yellow\">GA4 \u00d7 BigQuery\u306e\u9023\u643a\u306f\u8a2d\u5b9a\u5f8c\u7fcc\u65e5\u304b\u3089\u30c7\u30fc\u30bf\u304c\u5c4a\u304d\u59cb\u3081\u307e\u3059\u3002\u307e\u305a\u306f\u57fa\u672c\u7684\u306aPV\u96c6\u8a08\u30af\u30a8\u30ea\u304b\u3089\u59cb\u3081\u3066\u3001\u5f90\u3005\u306b\u30d5\u30a1\u30cd\u30eb\u5206\u6790\u30fb\u30b3\u30db\u30fc\u30c8\u5206\u6790\u3078\u3068\u767a\u5c55\u3055\u305b\u3066\u3044\u304d\u307e\u3057\u3087\u3046\u3002\u8a2d\u5b9a\u304c\u65e9\u3044\u307b\u3069\u84c4\u7a4d\u30c7\u30fc\u30bf\u304c\u5897\u3048\u3001\u3088\u308a\u6df1\u3044\u5206\u6790\u304c\u53ef\u80fd\u306b\u306a\u308a\u307e\u3059\u3002<\/span><\/strong><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<!-- withmedia-related -->\n<h2 class=\"wp-block-heading\">\u95a2\u9023\u8a18\u4e8b<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/withcode.tech\/media\/multilingual-seo-hreflang-guide\/\">\u591a\u8a00\u8a9e\u30b5\u30a4\u30c8\u306eSEO\u5bfe\u7b56\u5b8c\u5168\u30ac\u30a4\u30c9\uff5chreflang\u5c5e\u6027\u306e\u8a2d\u5b9a\u65b9\u6cd5\u30fbURL\u69cb\u9020\u8a2d\u8a08\u30fb\u30ed\u30fc\u30ab\u30e9\u30a4\u30bc\u30fc\u30b7\u30e7\u30f3\u307e\u3067\u5fb9\u5e95\u89e3\u8aac<\/a><\/li>\n<li><a href=\"https:\/\/withcode.tech\/media\/301-redirect-seo-guide\/\">301\u30ea\u30c0\u30a4\u30ec\u30af\u30c8\u306e\u8a2d\u5b9a\u65b9\u6cd5\u3010WordPress\u30fb.htaccess\u5bfe\u5fdc\u3011\uff5cSEO\u8a55\u4fa1\u3092\u5f15\u304d\u7d99\u3050\u5b8c\u5168\u30ac\u30a4\u30c9<\/a><\/li>\n<li><a href=\"https:\/\/withcode.tech\/media\/website-renewal-seo-protection\/\">\u3010\u5b8c\u5168\u4fdd\u5b58\u7248\u3011\u30b5\u30a4\u30c8\u30ea\u30cb\u30e5\u30fc\u30a2\u30eb\u3067SEO\u9806\u4f4d\u3092\u4e0b\u3052\u306a\u304412\u306e\u9632\u6b62\u7b56<\/a><\/li>\n<li><a href=\"https:\/\/withcode.tech\/media\/ga4-exploration-report-templates\/\">\u3010\u5b8c\u5168\u4fdd\u5b58\u7248\u3011GA4\u63a2\u7d22\u30ec\u30dd\u30fc\u30c8\u6d3b\u7528\u4e8b\u4f8b\uff5c7\u3064\u306e\u30c6\u30f3\u30d7\u30ec\u30fc\u30c8\u3067\u5b9f\u73fe\u3059\u308b\u30c7\u30fc\u30bf\u5206\u6790<\/a><\/li>\n<li><a href=\"https:\/\/withcode.tech\/media\/llmo-structured-data-schema-org-2026\/\">\u30102026\u5e74\u6700\u65b0\u3011LLMO\u5bfe\u7b56\u306e\u69cb\u9020\u5316\u30c7\u30fc\u30bf\u5b9f\u88c5\u65b9\u6cd5\uff5cAI\u306b\u6b63\u3057\u304f\u7406\u89e3\u3055\u308c\u308b\u305f\u3081\u306eSchema.org\u5b8c\u5168\u30ac\u30a4\u30c9<\/a><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<!-- withmedia-references -->\n<h2 class=\"wp-block-heading\">\u53c2\u8003\u30ea\u30f3\u30af\uff08\u516c\u5f0f\u30fb\u4e00\u6b21\u60c5\u5831\uff09<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/developers.google.com\/search\/docs\/appearance\/structured-data?hl=ja\" target=\"_blank\" rel=\"noopener noreferrer\">Google \u691c\u7d22\u30bb\u30f3\u30c8\u30e9\u30eb \u2014 \u69cb\u9020\u5316\u30c7\u30fc\u30bf<\/a><\/li>\n<li><a href=\"https:\/\/developers.google.com\/search\/docs\/fundamentals\/seo-starter-guide?hl=ja\" target=\"_blank\" rel=\"noopener noreferrer\">Google \u691c\u7d22\u30bb\u30f3\u30c8\u30e9\u30eb\uff08SEO\u30b9\u30bf\u30fc\u30bf\u30fc\u30ac\u30a4\u30c9\uff09<\/a><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">WithCode\u3092\u4f53\u9a13\u3067\u304d\u308b\u521d\u7d1a\u30b3\u30fc\u30b9\u516c\u958b\u4e2d\uff01<\/h2>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"alignleft size-full\"><img decoding=\"async\" width=\"600\" height=\"500\" src=\"https:\/\/withcode.tech\/media\/wp-content\/uploads\/2024\/04\/\u30d0\u30ca\u30fc_300\u00d7250_20240425.png\" alt=\"\" class=\"wp-image-2650\"\/><\/figure>\n<\/div>\n\n\n<h3 class=\"wp-block-heading\">\u521d\u7d1a\u30b3\u30fc\u30b9(\u00a549,800)\u304c\u5b8c\u5168\u7121\u6599\u306b\uff01<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u671f\u9593\uff1a1\u9031\u9593<\/strong><\/li>\n<li><strong>\u5b66\u7fd2\u5185\u5bb9\uff1a<\/strong><br><strong>\u30ed\u30fc\u30c9\u30de\u30c3\u30d7\/\u57fa\u790e\u77e5\u8b58\/\u74b0\u5883\u69cb\u7bc9\/HTML\/CSS\/LP\u30fb\u30dd\u30fc\u30c8\u30d5\u30a9\u30ea\u30aa\u4f5c\u6210<\/strong><br><strong>\u2192 <span class=\"swl-marker mark_yellow\">\u6b63\u3057\u3044\u5b66\u7fd2\u65b9\u6cd5\u3067\u300c\u78ba\u304b\u306a\u6210\u9577\u300d\u3092\u5b9f\u611f\u3067\u304d\u308b\u30ab\u30ea\u30ad\u30e5\u30e9\u30e0\u3002<\/span><\/strong><\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">\u526f\u696d\u30fb\u30d5\u30ea\u30fc\u30e9\u30f3\u30b9\u304c\u4e3b\u6d41\u306b\u306a\u3063\u3066\u3044\u308b\u4eca\u3053\u305d\u3001\u81ea\u3089\u306e\u30b9\u30ad\u30eb\u3067\u7a3c\u3052\u308b\u4eba\u6750\u3092\u76ee\u6307\u3057\u3066\u307f\u307e\u305b\u3093\u304b\uff1f<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\u672a\u7d4c\u9a13\u3067\u3082\u5fc3\u914d\u3059\u308b\u3053\u3068\u306f\u3042\u308a\u307e\u305b\u3093\u3002\u521d\u7d1a\u30b3\u30fc\u30b9\u3092\u53d7\u8b1b\u3055\u308c\u308b\u65b9\u306e\u5927\u591a\u6570\u306f\u30d7\u30ed\u30b0\u30e9\u30df\u30f3\u30b0\u672a\u7d4c\u9a13\u3067\u3059\u3002\u307e\u305a\u306f\u7121\u6599\u30ab\u30a6\u30f3\u30bb\u30ea\u30f3\u30b0\u3067\u3001\u60a9\u307f\u3084\u4e0d\u5b89\u3092\u304a\u805e\u304b\u305b\u304f\u3060\u3055\u3044\uff01<\/p>\n\n\n\n<div class=\"wp-block-buttons is-layout-flex wp-block-buttons-is-layout-flex\">\n<div class=\"wp-block-button\"><a class=\"wp-block-button__link has-white-color has-text-color has-background has-link-color wp-element-button\" href=\"https:\/\/withcode.tech\/reservation\/\" style=\"background-color:#ffbf00\"><strong>\u516c\u5f0f\u30b5\u30a4\u30c8\u304b\u3089\u7121\u6599\u30ab\u30a6\u30f3\u30bb\u30ea\u30f3\u30b0\u306b\u7533\u3057\u8fbc\u3080 \u2192<\/strong><\/a><\/div>\n<\/div>\n\n\n\n<!-- withmedia-faq-schema -->\n<script type=\"application\/ld+json\">\n{\n  \"@context\": \"https:\/\/schema.org\",\n  \"@type\": \"FAQPage\",\n  \"mainEntity\": [\n    {\n      \"@type\": \"Question\",\n      \"name\": \"BigQuery\u306e\u8cbb\u7528\u306f\u3069\u308c\u304f\u3089\u3044\u304b\u304b\u308a\u307e\u3059\u304b\uff1f\",\n      \"acceptedAnswer\": {\n        \"@type\": \"Answer\",\n        \"text\": \"\u30af\u30a8\u30ea\u51e6\u7406\u306f \u6bce\u6708\u6700\u521d\u306e1TB\u304c\u7121\u6599\u3067\u3001\u305d\u308c\u4ee5\u964d\u306f$5\/TB\u3067\u3059\u3002\u4e00\u822c\u7684\u306aWeb\u30b5\u30a4\u30c8\uff08\u670810\u4e07PV\u7a0b\u5ea6\uff09\u306eGA4\u30c7\u30fc\u30bf\u3078\u306e\u30af\u30a8\u30ea\u306a\u3089\u3001\u6708\u6570\u767eGB\u4ee5\u5185\u306b\u53ce\u307e\u308b\u3053\u3068\u304c\u591a\u304f\u307b\u307c\u7121\u6599\u3067\u4f7f\u3048\u307e\u3059\u3002\u30b9\u30c8\u30ec\u30fc\u30b8\u306f$0.02\/GB\/\u6708\uff08\u6700\u521d\u306e10GB\u306f\u7121\u6599\uff09\u3067\u3059\u3002GCP\u30b3\u30f3\u30bd\u30fc\u30eb\u3067\u300c\u4e88\u7b97\u30a2\u30e9\u30fc\u30c8\u300d\u3092\u8a2d\u5b9a\u3057\u3066\u304a\u304f\u3068\u60f3\u5b9a\u5916\u306e\u8ab2\u91d1\u3092\u9632\u3052\u307e\u3059\u3002\"\n      }\n    },\n    {\n      \"@type\": \"Question\",\n      \"name\": \"GA4\u306e\u30c7\u30fc\u30bf\u306f\u30ea\u30a2\u30eb\u30bf\u30a4\u30e0\u3067BigQuery\u306b\u5c4a\u304d\u307e\u3059\u304b\uff1f\",\n      \"acceptedAnswer\": {\n        \"@type\": \"Answer\",\n        \"text\": \"\u7121\u6599\u306e\u300c\u6bce\u65e5\u306e\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u300d\u306f\u524d\u65e5\u30c7\u30fc\u30bf\u304c\u7fcc\u671d\u306b\u5c4a\u304d\u307e\u3059\u3002\u30ea\u30a2\u30eb\u30bf\u30a4\u30e0\u5206\u6790\u304c\u5fc5\u8981\u306a\u5834\u5408\u306f\u300c\u30b9\u30c8\u30ea\u30fc\u30df\u30f3\u30b0\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u300d\uff08\u6709\u6599\uff09\u3092\u4f7f\u3046\u3068\u6570\u5206\u4ee5\u5185\u306b\u53cd\u6620\u3055\u308c\u307e\u3059\u3002\u305f\u3060\u3057\u30b9\u30c8\u30ea\u30fc\u30df\u30f3\u30b0\u30c6\u30fc\u30d6\u30eb\uff08events_intraday_YYYYMMDD\uff09\u306f\u91cd\u8907\u30ec\u30b3\u30fc\u30c9\u304c\u767a\u751f\u3059\u308b\u3053\u3068\u304c\u3042\u308b\u305f\u3081\u3001\u96c6\u8a08\u6642\u306b\u306fDISTINCT\u3092\u9069\u5207\u306b\u4f7f\u3046\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u3002\"\n      }\n    },\n    {\n      \"@type\": \"Question\",\n      \"name\": \"BigQuery\u306b\u30a2\u30af\u30bb\u30b9\u3059\u308b\u305f\u3081\u306e\u6a29\u9650\u8a2d\u5b9a\u306f\u3069\u3046\u3059\u308c\u3070\u3044\u3044\u3067\u3059\u304b\uff1f\",\n      \"acceptedAnswer\": {\n        \"@type\": \"Answer\",\n        \"text\": \"GCP\u306eIAM\u3067\u9069\u5207\u306a\u30ed\u30fc\u30eb\u3092\u4ed8\u4e0e\u3057\u307e\u3059\u3002\u5206\u6790\u8005\uff08\u8aad\u307f\u53d6\u308a\u306e\u307f\uff09\u306b\u306fBigQuery \u30c7\u30fc\u30bf\u95b2\u89a7\u8005\uff08roles\/bigquery.dataViewer\uff09\u3068BigQuery \u30b8\u30e7\u30d6 \u30e6\u30fc\u30b6\u30fc\uff08roles\/bigquery.jobUser\uff09\u306e2\u3064\u3092\u4ed8\u4e0e\u3057\u307e\u3059\u3002\u30c6\u30fc\u30d6\u30eb\u306e\u4f5c\u6210\u3084\u66f8\u304d\u8fbc\u307f\u304c\u5fc5\u8981\u306a\u5834\u5408\u306fBigQuery \u30c7\u30fc\u30bf\u7de8\u96c6\u8005\uff08roles\/bigquery.dataEditor\uff09\u3092\u4ed8\u4e0e\u3057\u307e\u3059\u3002\"\n      }\n    },\n    {\n      \"@type\": \"Question\",\n      \"name\": \"\u53e4\u3044GA4\u30c7\u30fc\u30bf\uff0814\u30f6\u6708\u4ee5\u4e0a\u524d\uff09\u306fBigQuery\u306b\u4fdd\u5b58\u3055\u308c\u307e\u3059\u304b\uff1f\",\n      \"acceptedAnswer\": {\n        \"@type\": \"Answer\",\n        \"text\": \"BigQuery\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u3092\u958b\u59cb\u3057\u305f\u65e5\u4ee5\u964d\u306e\u30c7\u30fc\u30bf\u304c\u84c4\u7a4d\u3055\u308c\u307e\u3059\u3002\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u958b\u59cb\u524d\u306e\u30c7\u30fc\u30bf\u306f\u9061\u3063\u3066\u53d6\u5f97\u3067\u304d\u307e\u305b\u3093\u3002\u9577\u671f\u5206\u6790\u3092\u884c\u3046\u305f\u3081\u306b\u3082\u4eca\u3059\u3050\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u3092\u8a2d\u5b9a\u3059\u308b\u3053\u3068\u3092\u5f37\u304f\u63a8\u5968\u3057\u307e\u3059\u3002\u8a2d\u5b9a\u5f8c\u7fcc\u65e5\u304b\u3089\u30c7\u30fc\u30bf\u304c\u84c4\u7a4d\u3055\u308c\u59cb\u3081\u3001\u6642\u9593\u304c\u7d4c\u3064\u307b\u3069\u4fa1\u5024\u306e\u3042\u308b\u30c7\u30fc\u30bf\u304c\u84c4\u7a4d\u3055\u308c\u307e\u3059\u3002\"\n      }\n    },\n    {\n      \"@type\": \"Question\",\n      \"name\": \"Python\/pandas\u304b\u3089BigQuery\u306e\u30c7\u30fc\u30bf\u3092\u53d6\u5f97\u3067\u304d\u307e\u3059\u304b\uff1f\",\n      \"acceptedAnswer\": {\n        \"@type\": \"Answer\",\n        \"text\": \"\u3067\u304d\u307e\u3059\u3002google-cloud-bigquery\u30e9\u30a4\u30d6\u30e9\u30ea\u3068pandas-gbq\u3092\u4f7f\u3046\u3068\u3001BigQuery\u306e\u30af\u30a8\u30ea\u7d50\u679c\u3092Pandas DataFrame\u3068\u3057\u3066\u53d6\u5f97\u3067\u304d\u307e\u3059\u3002\u6a5f\u68b0\u5b66\u7fd2\u30fb\u9ad8\u5ea6\u306a\u7d71\u8a08\u5206\u6790\u30fb\u53ef\u8996\u5316\u306a\u3069\u3001SQL\u3067\u306f\u3067\u304d\u306a\u3044\u5206\u6790\u3092Python\u3067\u884c\u3046\u969b\u306b\u4fbf\u5229\u3067\u3059\u3002\"\n      }\n    }\n  ]\n}\n<\/script>\n\n","protected":false},"excerpt":{"rendered":"<p>GA4\u30c7\u30fc\u30bf\u3092BigQuery\u306b\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u3057\u3066SQL\u3067\u30e6\u30fc\u30b6\u30fc\u884c\u52d5\u3092\u5206\u6790\u3059\u308b\u65b9\u6cd5\u3092\u5b8c\u5168\u89e3\u8aac\u3002\u30a8\u30af\u30b9\u30dd\u30fc\u30c8\u8a2d\u5b9a\u624b\u9806\u30fbevent_params\u306eUNNEST\u53d6\u5f97\u30fb\u30d5\u30a1\u30cd\u30eb\/\u30b3\u30db\u30fc\u30c8\/LTV\/DAU\u5206\u6790\u306a\u3069\u5b9f\u8df5SQL\u30af\u30a8\u30ea10\u9078\u306e\u5168\u6587\u30fbLooker Studio\u9023\u643a\u30fb\u30b3\u30b9\u30c8\u6700\u9069\u5316\u30c6\u30af\u30cb\u30c3\u30af\u307e\u3067\u7db2\u7f85\u3057\u305f2026\u5e74\u6700\u65b0\u30ac\u30a4\u30c9\u3002<\/p>\n","protected":false},"author":2,"featured_media":14505,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"swell_btn_cv_data":"","footnotes":"","vk-ltc-link":"","vk-ltc-target":""},"categories":[9,370],"tags":[],"class_list":["post-13187","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-learning-content","category-seo"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/withcode.tech\/media\/wp-json\/wp\/v2\/posts\/13187","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/withcode.tech\/media\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/withcode.tech\/media\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/withcode.tech\/media\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/withcode.tech\/media\/wp-json\/wp\/v2\/comments?post=13187"}],"version-history":[{"count":10,"href":"https:\/\/withcode.tech\/media\/wp-json\/wp\/v2\/posts\/13187\/revisions"}],"predecessor-version":[{"id":16213,"href":"https:\/\/withcode.tech\/media\/wp-json\/wp\/v2\/posts\/13187\/revisions\/16213"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/withcode.tech\/media\/wp-json\/wp\/v2\/media\/14505"}],"wp:attachment":[{"href":"https:\/\/withcode.tech\/media\/wp-json\/wp\/v2\/media?parent=13187"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/withcode.tech\/media\/wp-json\/wp\/v2\/categories?post=13187"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/withcode.tech\/media\/wp-json\/wp\/v2\/tags?post=13187"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}