SQL לאנשי שיווק על נתוני GA4 ב-BigQuery: השאילתות הראשונות

נכתב על ידי דני צ'רניאק, DigiKnowledge. נבדק לאחרונה: 10.11.2026

איך בנויים נתוני GA4 ב-BigQuery?

לפי התיעוד של Google, לכל נכס GA4 שמקושר ל-BigQuery נוצר dataset אחד בשם analytics_‎ ואחריו מזהה הנכס. בתוך ה-dataset נוצרת בכל יום טבלה בשם events_YYYYMMDD, אם הופעל הייצוא היומי. אם הופעל ייצוא Streaming נוצרת גם טבלה בשם events_intraday_YYYYMMDD, שמתמלאת לאורך היום ונמחקת כשהטבלה היומית מוכנה.

בטבלה אין עמודות של סשנים או משתמשים מסוכמים. יש אירועים. לכל אירוע יש שדות רגילים כמו event_date, event_name ו-user_pseudo_id, לצד שדות מקוננים. התיעוד מסביר שחלק מהשדות הם RECORD שחוזר על עצמו: event_params מכיל את הפרמטרים של האירוע, items מכיל את המוצרים. זאת הסיבה ש-SQL על GA4 נראה שונה מ-SQL על טבלה רגילה. איך מפעילים את הייצוא ומה הוא עולה הסברנו במדריך על ייצוא GA4 ל-BigQuery.

איפה מתרגלים בלי נתונים משלכם?

Google מפרסמת מערך נתונים ציבורי לתרגול: bigquery-public-data.ga4_obfuscated_sample_ecommerce. לפי התיעוד הוא מכיל דגימה של נתוני ייצוא מהחנות Google Merchandise Store לשלושה חודשים, מ-1 בנובמבר 2020 עד 31 בינואר 2021. הנתונים עברו טשטוש, כך שחלק מהשדות מכילים ערכי מציין מקום והעקביות הפנימית מוגבלת. התיעוד מציין גם שאי אפשר להשוות אותו לחשבון הדמו של GA4, כי הנתונים שונים.

כדי להריץ שאילתות לא צריך כרטיס אשראי. לפי התיעוד של Google Cloud, ה-BigQuery sandbox מאפשר לעבוד בלי חשבון חיוב, במגבלות: 10 GiB אחסון, 1 TiB של נתונים מעובדים בשאילתות בכל חודש וטבלאות שפג תוקפן אחרי 60 יום. לתרגול על המערך הציבורי זה מספיק.

מהן אבני הבניין של כל שאילתה?

איך סופרים אירועים ומשתמשים?

השאילתה הראשונה סופרת כמה אירועים מכל סוג נרשמו בכל יום. היא מבוססת על הדוגמה הרשמית של Google. זאת גם הדרך המהירה ביותר לראות אילו אירועים בכלל קיימים בנתונים.

SELECT
  event_date,
  event_name,
  COUNT(*) AS event_count
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107'
GROUP BY event_date, event_name
ORDER BY event_date, event_count DESC;

השאילתה השנייה סופרת משתמשים. לפי התיעוד, כשאין user_id סופרים ערכים ייחודיים של user_pseudo_id. משתמשים חדשים הם מי שרשמו את האירוע first_visit או first_open.

SELECT
  COUNT(DISTINCT user_pseudo_id) AS total_users,
  COUNT(DISTINCT IF(event_name IN ('first_visit', 'first_open'),
    user_pseudo_id, NULL)) AS new_users
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210131';

שימו לב למה שנספר. לפי התיעוד, ספירה של כל המזהים הייחודיים נותנת את Total users. המדד Users שבדוחות של GA4 הוא משתמשים פעילים. כדי להתקרב אליו התיעוד מציע לסנן לפי השדה is_active_user, שמתמלא רק בטבלאות היומיות. את ההבדל בין המדדים ריכזנו במילון מונחי GA4.

איך שולפים פרמטר של אירוע עם UNNEST?

כתובת העמוד לא שמורה בעמודה משלה. היא פרמטר בשם page_location בתוך event_params. כדי לשלוף אותה כותבים תת שאילתה קטנה שפותחת את הרשימה ובוחרת את ה-key הרצוי. השאילתה הבאה מחזירה את עשרת העמודים עם הכי הרבה צפיות.

SELECT
  (SELECT value.string_value
   FROM UNNEST(event_params)
   WHERE key = 'page_location') AS page_location,
  COUNT(*) AS page_views
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107'
  AND event_name = 'page_view'
GROUP BY page_location
ORDER BY page_views DESC
LIMIT 10;

שתי הערות על התוצאה. page_location היא הכתובת המלאה של העמוד, כך שאותו עמוד עם פרמטרים שונים בכתובת מופיע בכמה שורות. בנוסף, הספירה היא של אירועי page_view ולא של משתמשים או סשנים.

זאת התבנית שהכי כדאי לזכור. מחליפים את ה-key ואת סוג הערך לפי הפרמטר: string_value לטקסט, int_value למספר שלם, double_value למספר עשרוני. מי שבוחר סוג ערך לא נכון לא מקבל שגיאה אלא NULL, לכן עמודה ריקה היא הסימן הראשון לבדוק. לפי התיעוד, רשימת הפרמטרים ייחודית לכל הטמעה. לכן לפני שכותבים שאילתה על פרמטר מותאם בודקים שהוא באמת מגיע. הדוגמאות הרשמיות של Google כוללות שאילתה שמפרטת את כל הפרמטרים שמופיעים בנתונים.

איך סופרים סשנים?

בנתונים הגולמיים אין עמודת סשנים. לפי התיעוד של Google למפתחים, השיטה התקנית היא לספור צירופים ייחודיים של user_pseudo_id או user_id יחד עם ga_session_id. המזהה ga_session_id הוא פרמטר של אירוע מסוג מספר שלם, כך שגם אותו שולפים עם UNNEST. השאילתה הבאה סופרת סשנים לפי סוג מכשיר.

WITH events AS (
  SELECT
    user_pseudo_id,
    device.category AS device_category,
    (SELECT value.int_value
     FROM UNNEST(event_params)
     WHERE key = 'ga_session_id') AS ga_session_id
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107'
)
SELECT
  device_category,
  COUNT(DISTINCT CONCAT(user_pseudo_id, '-',
    CAST(ga_session_id AS STRING))) AS sessions
FROM events
GROUP BY device_category
ORDER BY sessions DESC;

אירועים שאין להם ga_session_id לא נספרים, כי צירוף עם ערך NULL מחזיר NULL. טעות נפוצה שהתיעוד מזהיר ממנה: לספור סשנים לכל יום בנפרד ולחבר את הימים. סשן שנמשך מעבר לחצות ייספר פעמיים, לכן סופרים על כל הטווח בבת אחת. לגבי מקור התנועה, השדה traffic_source מתאר לפי התיעוד את המקור שדרכו המשתמש נרכש לראשונה, לא את המקור של הסשן. מקור ברמת הסשן נמצא ברשומה session_traffic_source_last_click. על ההבדל בין הרמות הרחבנו במדריך על ייחוס ורמות מימד.

איך מחשבים רכישות והכנסות?

נתוני האיקומרס יושבים ברשומה ecommerce. לפי התיעוד, השדה ecommerce.purchase_revenue מכיל את ההכנסה מהרכישה במטבע המקומי ומתמלא רק באירוע purchase. השדה ecommerce.transaction_id מכיל את מזהה העסקה.

SELECT
  event_date,
  COUNT(*) AS purchase_events,
  COUNT(DISTINCT ecommerce.transaction_id) AS transactions,
  SUM(ecommerce.purchase_revenue) AS purchase_revenue
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210107'
  AND event_name = 'purchase'
GROUP BY event_date
ORDER BY event_date;

שתי העמודות הראשונות הן בדיקת איכות. כשלכל רכישה נשלח מזהה ו-purchase_events גבוה מ-transactions, אותה עסקה נשלחה יותר מפעם אחת. במצב כזה גם סכום ההכנסות מנופח, כי הוא מסכם כל אירוע. זה המקרה שתיארנו במדריך על רכישות כפולות ב-GA4. במערך לדוגמה חלק מהשדות מכילים ערכי מציין מקום, לכן פער שם הוא תרגול של הבדיקה ולא ממצא.

עוד שתי נקודות על הנתונים שלכם. אם האתר מוכר ביותר ממטבע אחד, מסכמים את ecommerce.purchase_revenue_in_usd, שלפי הסכמה מכיל את אותה הכנסה בדולרים. לניתוח ברמת המוצר פותחים את הרשומה items עם UNNEST(items) ומשתמשים בשדות כמו item_name ו-quantity.

שלושה תרגילים לפני שעוברים לנתונים שלכם. בשאילתת העמודים מחליפים את page_location ב-page_title. בשאילתת הסשנים מחליפים את device.category ב-geo.country. בשאילתת ההכנסות מרחיבים את הטווח לכל ינואר 2021 ובודקים כמה נתונים השאילתה תעבד לפני שמריצים.

איך מעבירים שאילתה לנתונים שלכם ושומרים על העלות?

1. מעתיקים את שם הטבלה. לפי התיעוד: ב-BigQuery מאתרים את הטבלה ב-Explorer, לוחצים על שלוש הנקודות ובוחרים Copy ID.
2. מתקנים את השם. מחליפים את הנקודתיים שאחרי שם הפרויקט בנקודה. את חלק התאריך מחליפים בכוכבית. כך events_20240718 הופך ל-events_*‎.
3. מגדירים טווח תאריכים קצר. מתחילים מיום אחד או שבוע ב-‎_TABLE_SUFFIX. מרחיבים רק אחרי שהשאילתה נכונה. הכוכבית תופסת גם טבלאות events_intraday_‎ אם הן קיימות, אבל סינון לטווח של תאריכים משאיר רק את הטבלאות היומיות.
4. בודקים את ההערכה לפני ההרצה. לפי התיעוד, בשאילתה תקינה מופיע סימן אישור יחד עם כמות הנתונים שהשאילתה תעבד. המספר הזה עוזר להעריך את העלות.
5. לא מריצים שוב ושוב בלי צורך. לפי התיעוד של BigQuery, תוצאות מהמטמון לא נתמכות בשאילתות wildcard על כמה טבלאות. כל הרצה חוזרת מחויבת מחדש.

כשהשאילתה יציבה אפשר לחבר אותה לדשבורד. את האפשרויות והעלויות פירטנו במדריך על חיבור BigQuery ל-Looker Studio.

למה התוצאה לא זהה לממשק של GA4?

כי הייצוא מכיל את הנתונים שנאספו, בלי התוספות שהממשק מחיל עליהם. התיעוד של Google למפתחים מפרט את הסיבות העיקריות. הממשק מעריך מדדים כמו משתמשים פעילים וסשנים באלגוריתם HyperLogLog++, כלומר בקירוב. ב-BigQuery הספירה מדויקת. נתונים ממודלים של Consent Mode לא נמצאים בייצוא. גם המידע של Google Signals לא נמצא בו, כך שאותו אדם בכמה דפדפנים מופיע עם כמה ערכי user_pseudo_id.

יש גם שתי סיבות טכניות שקל לפספס. לפי התיעוד, event_date נרשם לפי אזור הזמן של הדיווח, בזמן ש-event_timestamp הוא זמן UTC במיקרו שניות. מי שמסנן לפי event_timestamp בלי המרה מקבל ימים שונים מהממשק. בנוסף, הטבלאות היומיות יכולות להתעדכן עד 72 שעות אחרי התאריך שלהן, כך שהשוואה אמינה נעשית על נתונים ישנים יותר מזה. התיעוד מסכם שבחלק מהמקרים שני המקורות לא אמורים להתיישב לגמרי. מספיק שכל אחד מהם עקבי בתוך עצמו ותואם למה שאתם אוספים.

שאלות נפוצות

מה השורה התחתונה?

איש שיווק לא צריך להפוך למהנדס נתונים כדי לעבוד עם GA4 ב-BigQuery. מספיק להבין שהטבלה היא רשימת אירועים, לסנן תמיד לפי ‎_TABLE_SUFFIX ולפי event_name ולדעת לשלוף פרמטר עם UNNEST. חמש השאילתות שכאן מכסות את רוב השאלות הראשונות: מה נאסף, כמה משתמשים, אילו עמודים, כמה סשנים וכמה הכנסה. מתרגלים אותן על המערך הציבורי ורק אחר כך עוברים לנתונים שלכם, עם טווח תאריכים קצר ובדיקה של כמות הנתונים לפני כל הרצה. הדרכות אנליטיקס ונתונים לצוותים ולארגונים מרוכזות בעמוד הקורסים וההדרכות שלנו. אם אתם רוצים הדרכת SQL על הנתונים של הארגון שלכם, כתבו לנו.

מקורות

Analytics Help: BigQuery Export schema, Google for Developers: Basic queries for Google Analytics event data export, Google for Developers: Bridge the gap between the Google Analytics UI and BigQuery export, Google for Developers: BigQuery sample dataset for Google Analytics ecommerce web implementation, Google Cloud Documentation: Query multiple tables using a wildcard table, Google Cloud Documentation: Try BigQuery using the sandbox

דני צ'רניאק
יועץ אנליטיקה דיגיטלית ומייסד DigiKnowledge. מרצה לאנליטיקה דיגיטלית באוניברסיטת תל אביב, באוניברסיטה הפתוחה ובאוניברסיטת אריאל. לעמוד האישי