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

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

שליחת שאילתה לקבוצת הנתונים במקום לקבוצת הנתונים לדוגמה

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

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

לדוגמה, אם מזהה ההעתקה העתיק את שם הטבלה ב-BigQuery ‏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_SUFFIX במשפט WHERE של השאילתה. למידע נוסף, אפשר לעיין במאמר בנושא סינון טבלאות נבחרות באמצעות ‎_TABLE_SUFFIX.

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

-- 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' (Active User), 'New User', and 'Returning User' counts.

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 active_users,
  SUM(is_new_user) AS new_users,
  COUNT(*) - SUM(is_new_user) AS returning_users
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_sample_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 Ads, ואז לאחד את collected_traffic_source.gclid מנתוני האירועים ב-Google Analytics עם השדה gclid של ads_ClickStats_customer_id מההעברה ב-Google Ads.

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