שאילתות בסיסיות לייצוא נתוני אירועים ב-Google Analytics

השאילתות לדוגמה בדף הזה חלות על נתוני ייצוא האירועים ב-BigQuery עבור ב-Google Analytics.

הרצת שאילתות במערך הנתונים במקום במערך הנתונים לדוגמה

אלא אם צוין אחרת, כל השאילתות שמפורטות כאן מתבססות על מערכי נתונים לדוגמה, כדי לספק תוצאות תקינות. כדי להשתמש ב-BigQuery של נכס Google Analytics שלכם נתונים של ייצוא אירועים, מחפשים את התגובה -- Replace table בכל שאילתה להחליף את הטבלה לדוגמה. כדי להעתיק את שם הטבלה ממערך הנתונים:

1. נכנסים אל ממשק המשתמש של BigQuery ובוחרים בפרויקט שמכיל את מערך הנתונים.
2. מחפשים את הטבלה ב-Explorer.
3. לוחצים על סמל שלוש הנקודות האנכיות שמשמאל לטבלה ואז לוחצים על העתקת המזהה.
4. מדביקים את שם הטבלה במקום הטבלה לדוגמה בשאילתה.
5. מחליפים את תאריך החלק בטבלה במחרוזת *.

לדוגמה, אם השם של הטבלה ב-BigQuery הועתק על ידי Copy ID my-first-gcp-project:analytics_28239234.events_20240718, ולאחר מכן מחליפים את:

  -- Replace table
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

עם:

  -- Replace table
  `my-first-gcp-project.analytics_28239234.events_*`

הערה: כשמוסיפים את הטבלה לשאילתות לדוגמה, צריך להחליף את ':' עם '.'.

שאילתה לגבי טווח תאריכים ספציפי

כדי לשלוח שאילתה על טווח תאריכים ספציפי ממערך נתונים של ייצוא אירועים ב-BigQuery, צריך להשתמש בפונקציה עמודת פסאודו _TABLE_ שינוי במשפט WHERE של השאילתה שלך. למידע נוסף, לעיין בקטע סינון הטבלאות שנבחרו באמצעות _TABLE_ כותבים.

לדוגמה, השאילתה הבאה סופרת אירועים ייחודיים לפי תאריך ולפי שם האירוע בתקופה ספציפית של ימים ואירועים נבחרים:

-- Example: Query a specific date range for selected events.
--
-- Counts unique events by date and by event name for a specifc period of days and
-- selected events(page_view, session_start, and purchase).

SELECT
  event_date,
  event_name,
  COUNT(*) AS event_count
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name IN ('page_view', 'session_start', 'purchase')
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
GROUP BY 1, 2;

ספירת משתמשים וספירת משתמשים חדשים

• כדי לקבל את המספר הכולל של המשתמשים, סופרים את ערכי ה-user_id הייחודיים. עם זאת, אם לקוח Google Analytics שלך לא שולח חזרה user_id עם כל התאמה, או אם לא בטוחים, ספרו את מספר ה-user_pseudo_id הייחודיים.
• לגבי משתמשים חדשים, אפשר להשתמש באותה גישת ספירה שמתוארת למעלה, אבל הערכים הבאים של event_name:
  • first_visit
  • first_open

-- Example: Get 'Total User' count and 'New User' count.

WITH
  UserInfo AS (
    SELECT
      user_pseudo_id,
      MAX(IF(event_name IN ('first_visit', 'first_open'), 1, 0)) AS is_new_user
    -- Replace table name.
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    -- Replace date range.
    WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20201130'
    GROUP BY 1
  )
SELECT
  COUNT(*) AS user_count,
  SUM(is_new_user) AS new_user_count
FROM UserInfo;

מספר העסקאות הממוצע לכל רוכש

השאילתה הבאה מציגה את מספר העסקאות הממוצע לכל רוכש.

-- Example: Average number of transactions per purchaser.

SELECT
  COUNT(*) / COUNT(DISTINCT user_pseudo_id) AS avg_transaction_per_purchaser
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name IN ('in_app_purchase', 'purchase')
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201231';

ערכים של שם אירוע ספציפי

השאילתה הבאה מציגה את event_timestamp עבור כל purchase אירועים ופרמטר האירוע המשויך ערכים:

-- Example: Query values for a specific event name.
--
-- Queries the individual timestamps and values for all 'purchase' events.

SELECT
  event_timestamp,
  (
    SELECT COALESCE(value.int_value, value.float_value, value.double_value)
    FROM UNNEST(event_params)
    WHERE key = 'value'
  ) AS event_value
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'purchase'
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202';

אפשר לשנות את השאילתה הקודמת כך שיוצג בה הסכום הכולל של הפרמטרים של האירוע במקום רשימה:

-- Example: Query total value for a specific event name.
--
-- Queries the total event value for all 'purchase' events.

SELECT
  SUM(
    (
      SELECT COALESCE(value.int_value, value.float_value, value.double_value)
      FROM UNNEST(event_params)
      WHERE key = 'value'
    ))
    AS event_value
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'purchase'
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202';

10 הפריטים המובילים שנוספו לעגלת הקניות

השאילתה הבאה מציגה את 10 הפריטים המובילים שנוספו לעגלת הקניות לפי מספר המשתמשים הגדול ביותר.

-- Example: Top 10 items added to cart by most users.

SELECT
  item_id,
  item_name,
  COUNT(DISTINCT user_pseudo_id) AS user_count
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_web_ecommerce.events_*`, UNNEST(items)
WHERE
  -- Replace date range.
  _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  AND event_name IN ('add_to_cart')
GROUP BY
  1, 2
ORDER BY
  user_count DESC
LIMIT 10;

המספר הממוצע של צפיות בדפים לפי סוג הרוכשים (משתמשים שמבצעים רכישות לעומת משתמשים שלא מבצעים רכישה)

השאילתה הבאה מציגה את המספר הממוצע של צפיות בדפים מסוג רוכש (משתמשים שקונים לעומת משתמשים שלא מבצעים רכישה):

-- Example: Average number of pageviews by purchaser type.

WITH
  UserInfo AS (
    SELECT
      user_pseudo_id,
      COUNTIF(event_name = 'page_view') AS page_view_count,
      COUNTIF(event_name IN ('in_app_purchase', 'purchase')) AS purchase_event_count
    -- Replace table name.
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    -- Replace date range.
    WHERE _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
    GROUP BY 1
  )
SELECT
  (purchase_event_count > 0) AS purchaser,
  COUNT(*) AS user_count,
  SUM(page_view_count) AS total_page_views,
  SUM(page_view_count) / COUNT(*) AS avg_page_views,
FROM UserInfo
GROUP BY 1;

רצף של צפיות בדפים

שאילתה זו מציגה את רצף הצפיות בדף שביצע כל משתמש. סידור השאילתות את התוצאות באמצעות השדות הבאים, כדי שהאירועים יופיעו לפי הסדר הם התרחשו אצל המשתמש, גם אם האירועים נשלחו יחד:

• user_pseudo_id
• user_id
• batch_page_id
• batch_ordering_id
• batch_event_index

למרות שהדגימה מגבילה את התוצאות ל-page_view אירועים בלבד, אפשר להשתמש להשתמש באותו תנאי ORDER BY כדי לסדר את כל האירועים בצורה תקינה באמצעות הסרת המאפיין תנאי WHERE של תנאי עבור event_name.

השאילתה גם מראה איך להשתמש בפונקציות משתמש פונקציות GetParamString ו-GetParamInt כדי לצמצם כפילויות וליצור שאילתות קל יותר להבנה ולתחזוקה.

הערה: הסכימה של מערך הנתונים לדוגמה לא מכילה את הערכים batch_page_id, batch_ordering_id, או batch_event_index שדות. מחליפים את הטבלה בקטע FROM של השאילתה עם טבלה.

-- Example: Sequence of pageviews.

/** Temporary function to retrieve the string_value of an event parameter by event name. */
CREATE TEMP FUNCTION GetParamString(event_params ANY TYPE, param_name STRING)
AS ((SELECT ANY_VALUE(value.string_value) FROM UNNEST(event_params) WHERE key = param_name));

/** Temporary function to retrieve the int_value of an event parameter by event name. */
CREATE TEMP FUNCTION GetParamInt(event_params ANY TYPE, param_name STRING)
AS ((SELECT ANY_VALUE(value.int_value) FROM UNNEST(event_params) WHERE key = param_name));

SELECT
  user_pseudo_id,
  user_id,
  batch_page_id,
  batch_ordering_id,
  batch_event_index,
  event_name,
  GetParamInt(event_params, 'ga_session_id') as ga_session_id,
  GetParamString(event_params, 'page_location') as page_location,
  GetParamString(event_params, 'page_title') as page_title,
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'page_view'
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20240718' AND '20240731'
ORDER BY
  user_pseudo_id,
  user_id,
  batch_page_id,
  batch_ordering_id,
  batch_event_index;

רשימת פרמטרים של אירועים

בשאילתה הבאה מפורטים כל הפרמטרים של האירועים שמופיעים במערך הנתונים:

-- Example: List all available event parameters and count their occurrences.

SELECT
  EP.key AS event_param_key,
  COUNT(*) AS occurrences
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(event_params) AS EP
WHERE
  -- Replace date range.
  _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
GROUP BY
  event_param_key
ORDER BY
  event_param_key ASC;

הצטרפות באמצעות Google Ads

כדי לאחזר נתונים נוספים מ-Google Ads עבור האירועים ב-Google Analytics, צריך להגדיר את שירות העברת נתונים ל-BigQuery ב-Google ואז מודעות. לאחד את collected_traffic_source.gclid מנתוני האירועים ב-Google Analytics אל gclid של ads_ClickStats_customer_id מההעברה אל Google Ads.

חשוב לזכור: כשמייצאים את נתוני האירועים מ-Google Analytics, נוצרת טבלה לכל יום. ההעברה דרך Google Ads מאכלסת מודל אחד ads_ClickStats_טבלה אחת (customer_id) לכל לקוח.