אוסף פתרונות ל-BigQuery

התכונה הזו זמינה רק במוצר Analytics 360, שהוא חלק מ-Google Marketing Platform.
מידע נוסף על Google Marketing Platform.

המאמר הזה כולל דוגמאות ליצירת שאילתות על נתוני Analytics שמיועדים לייצוא אל BigQuery. הכנו מערך נתונים לדוגמה כדי שתוכלו לתרגל את הדברים בעזרת השאילתות שמופיעות במאמר.

הנושאים במאמר:

אופטימיזציה של שאילתות

כל שאילתה שאתם מריצים מנצלת חלק מיתרת עיבוד הנתונים החודשית שלכם. אם תבחרו בשדות שמחוץ לתחום, תגדילו בכך את כמות הנתונים לעיבוד וכתוצאה מכך תתבזבז ההקצבה החודשית שלכם של עיבוד נתונים. אופטימיזציה של השאילתות מובילה לשימוש יעיל יותר במכסת עיבוד הנתונים החודשית.

מידע נוסף על תמחור

בחרו רק את מה שנחוץ לכם

בזמן יצירת שאילתה, צריך לבחור רק את השדות הרלוונטיים בהצהרה SELECT. אם לא משתמשים בשדות מיותרים, מפחיתים את כמות הנתונים ואת פרק הזמן הדרושים לעיבוד השאילתה.

דוגמה: הימנעו משימוש באופרטור 'תו כללי לחיפוש'

מבנה שאילתה לא-מוצלח: שימוש באופרטור 'תו כללי לחיפוש'
SELECT *
FROM [table name];

 

מבנה שאילתה מוצלח יותר: שימוש בשמות השדות כדי להימנע מעיבוד מיותר
SELECT field1, field2
FROM [table name];

שמירה במטמון

יש להימנע ככל האפשר משימוש בפונקציות כשדות. פונקציות (כגון NOW() או TODAY()) מחזירות תוצאות משתנות, דבר המונע שמירה של שאילתות במטמון ולפיכך החזרה מהירה יותר שלהן. במקום פונקציות יש להשתמש בשעות ובתאריכים ספציפיים.

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

שימוש בטבלאות ביניים לשאילתות משנה נפוצות

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

שימוש בטבלת ביניים
SELECT field1, field2
FROM [Dataset name.table name];

ניפוי באגים בשאילתות

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

בשאילתות חוקיות מופיע סימון ירוק, וניתן ללחוץ עליו כדי לראות את כמות הנתונים שהשאילתה מעבדת. התכונה הזו מאפשרת לכם לבצע אופטימיזציה של הנתונים לפני שאתם מריצים את השאילתה, וכך להימנע מעיבוד מיותר של נתונים.

Query Debugging - Success

 

בשאילתות לא חוקיות מופיע סימון אדום, וניתן ללחוץ עליו כדי לראות מידע על השגיאה ולמצוא את השורה והעמודה שבהן השגיאה נמצאת. בדוגמה הבאה, ההצהרה GROUP BY ריקה, והשגיאה מסומנת.

Query Debugging - Error

 

טיפים ושיטות מומלצות

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

בדוגמאות הבאות אנחנו משתמשים במערך הנתונים לדוגמה של Google Analytics.

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

SQL סטנדרטי לעומת SQL מדור קודם

BigQuery תומך בשני דיאלקטים של SQL:

במאמר מעבר ל-SQL סטנדרטי מוסבר ההבדל בין שני הפורמטים.

SQL סטנדרטי הוא כיום דיאלקט ה-SQL המועדף לביצוע שאילתות על נתונים שמאוחסנים ב-BigQuery.

במאמר הפעלת SQL סטנדרטי ניתן לקבל מידע על הפעלת SQL סטנדרטי בממשק שבו אתם משתמשים ב-BigQuery: ממשק משתמש (UI),‏ CLI או API.

הדרך הקלה ביותר להתחיל היא לכלול את ההערה 'standardSQL' בחלק העליון של שאילתות בדיאלקט 'SQL סטנדרטי', כפי שמתואר בדוגמאות הבאות.

בדיאלקט 'SQL מדור קודם', נתוני Google Analytics 360 מועברים מדי יום לטבלה חדשה. כדי לבצע שאילתות על כמה טבלאות בבת אחת, מפרידים בין שמות הטבלאות באמצעות פסיק, משתמשים בפונקציית התו הכללי לחיפוש בטבלאות TABLE_DATE_RANGE, או משתמשים במספר פונקציות TABLE_DATE_RANGE שמופרדות בפסיקים, כמו בדוגמאות הבאות.

הרצת שאילתות על יותר מטבלה אחת

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

3 ימים

SQL סטנדרטי
3 ימים – שימוש בפונקציה UNION ALL
#standardSQL
WITH ga_tables AS (
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`
GROUP BY date

UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160802`
GROUP BY date

UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160803`
GROUP BY date

)
SELECT
date,
visits,
pageviews,
transactions,
revenue,
FROM ga_tables
ORDER BY date ASC
SQL מדור קודם
3 ימים – שימוש בשמות טבלאות שמופרדים בפסיקים
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
[bigquery-public-data.google_analytics_sample.ga_sessions_20160801],
[bigquery-public-data.google_analytics_sample.ga_sessions_20160802],
[bigquery-public-data.google_analytics_sample.ga_sessions_20160803]
GROUP BY
date
ORDER BY
date ASC

 

1,095 הימים האחרונים

SQL סטנדרטי
1,095 הימים האחרונים – שימוש בפונקציה ‎_TABLE_SUFFIX
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1095 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
SQL מדור קודם
1,095 הימים האחרונים – שימוש בפונקציה TABLE_DATE_RANGE
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -1095, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

36 החודשים האחרונים

SQL סטנדרטי
36 החודשים האחרונים – שימוש בפונקציה ‎_TABLE_SUFFIX
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 36 MONTH))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
SQL מדור קודם
36 החודשים האחרונים – שימוש בפונקציה TABLE_DATE_RANGE
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -36, 'MONTH'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

3 השנים האחרונות

SQL סטנדרטי
3 השנים האחרונות – שימוש בפונקציה ‎_TABLE_SUFFIX
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
SQL מדור קודם
3 השנים האחרונות – שימוש בפונקציה TABLE_DATE_RANGE
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -3, 'YEAR'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

טווח תאריכים מסוים

SQL סטנדרטי
טווח תאריכים ספציפי – שימוש בפונקציה ‎_TABLE_SUFFIX
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20160801' AND '20170731'
GROUP BY date
ORDER BY date ASC
SQL מדור קודם
טווח תאריכים ספציפי – שימוש בפונקציה TABLE_DATE_RANGE
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2016-08-01'), TIMESTAMP('2017-07-31')))
GROUP BY
date
ORDER BY
date ASC

3 השנים האחרונות והנתונים מהיום (שמתקבלים במשך היום)

SQL סטנדרטי
3 השנים האחרונות והנתונים מהיום (המתקבלים במשך היום) - שימוש ב-UNION ALL וב-‏‎_TABLE_SUFFIX
הערה: השאילתה הזאת לא תעבוד במערך הנתונים הציבורי של Google Analytics, כי כרגע אין טבלה לנתונים שמתקבלים במשך היום.
#standardSQL
WITH ga_tables AS ( SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))
GROUP BY date
)
SELECT
date,
visits,
pageviews,
transactions,
revenue,
FROM ga_tables
ORDER BY date ASC

SQL מדור קודם

3 השנים האחרונות והנתונים מהיום (המתקבלים במשך היום) - שימוש במספר רכיבי TABLE_DATE_RANGE
הערה: השאילתה הזאת לא תעבוד במערך הנתונים הציבורי של Google Analytics, כי כרגע אין טבלה לנתונים שמתקבלים במשך היום.
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -3, 'YEAR'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))),
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_intraday_],
DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'), CURRENT_TIMESTAMP()))
GROUP BY
date
ORDER BY
date ASC

 

 

שאילתות בסיסיות לדוגמה

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

מהו המספר הכולל של [metric] לכל [dimension]?

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

SQL סטנדרטי

מספר העסקאות הכולל בכל דפדפן של מכשיר ביולי 2017
#standardSQL
SELECT
device.browser,
SUM ( totals.transactions ) AS total_transactions
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
GROUP BY
device.browser
ORDER BY
total_transactions DESC

SQL מדור קודם

מספר העסקאות הכולל בכל דפדפן של מכשיר ביולי 2017
SELECT
device.browser,
SUM ( totals.transactions ) AS total_transactions
FROM TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
GROUP BY
device.browser
ORDER BY
total_transactions DESC

 

 

מהו שיעור העזיבה הממוצע לכל [dimension]?

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

SQL סטנדרטי

שיעור העזיבה בפועל בכל מקור תנועה ביולי 2017
#standardSQL
SELECT
source,
total_visits,
total_no_of_bounces,
( ( total_no_of_bounces / total_visits ) * 100 ) AS bounce_rate
FROM (
SELECT
trafficSource.source AS source,
COUNT ( trafficSource.source ) AS total_visits,
SUM ( totals.bounces ) AS total_no_of_bounces
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
GROUP BY
source )
ORDER BY
total_visits DESC

SQL מדור קודם

שיעור העזיבה בפועל בכל מקור תנועה ביולי 2017
SELECT
source,
total_visits,
total_no_of_bounces,
( ( total_no_of_bounces / total_visits ) * 100 ) AS bounce_rate
FROM (
SELECT
trafficSource.source AS source,
COUNT ( trafficSource.source ) AS total_visits,
SUM ( totals.bounces ) AS total_no_of_bounces
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
GROUP BY
source )
ORDER BY
total_visits DESC

 

 

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

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

SQL סטנדרטי

המספר הממוצע של צפיות בדפי מוצר שהגיעו ממשתמשים שביצעו רכישה ביולי 2017
#standardSQL
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.transactions >=1
GROUP BY
users )

SQL מדור קודם

המספר הממוצע של צפיות בדפי מוצר שהגיעו ממשתמשים שביצעו רכישה ביולי 2017
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') ) WHERE
totals.transactions >=1
GROUP BY
users )

 

 

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

SQL סטנדרטי

המספר הממוצע של צפיות בדפי מוצר שהגיעו ממשתמשים שלא ביצעו רכישה ביולי 2017
#standardSQL
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.transactions IS NULL
GROUP BY
users )

SQL מדור קודם

המספר הממוצע של צפיות בדפי מוצר שהגיעו ממשתמשים שלא ביצעו רכישה ביולי 2017
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') ) WHERE
totals.transactions IS NULL
GROUP BY
users )

 

 

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

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

SQL סטנדרטי

מספר העסקאות הממוצע לכל משתמש שביצע רכישה ביולי 2017
#standardSQL
SELECT
(SUM (total_transactions_per_user) / COUNT(fullVisitorId) ) AS avg_total_transactions_per_user
FROM (
SELECT
fullVisitorId,
SUM (totals.transactions) AS total_transactions_per_user
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND totals.transactions IS NOT NULL
GROUP BY
fullVisitorId )

SQL מדור קודם

מספר העסקאות הממוצע לכל משתמש שביצע רכישה ביולי 2017
SELECT
(SUM (total_transactions_per_user) / COUNT(fullVisitorId) ) AS avg_total_transactions_per_user
FROM (
SELECT
fullVisitorId,
SUM (totals.transactions) AS total_transactions_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
totals.transactions IS NOT NULL
GROUP BY
fullVisitorId )

 

 

ההוצאה הכספית הממוצעת לכל סשן

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

SQL סטנדרטי

ההוצאה הכספית הממוצעת לכל סשן ביולי 2017
#standardSQL
SELECT
( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS
avg_revenue_by_user_per_visit
FROM (
SELECT
fullVisitorId,
SUM( totals.visits ) AS total_visits_per_user,
SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.visits > 0
AND totals.transactions >= 1
AND totals.transactionRevenue IS NOT NULL
GROUP BY
fullVisitorId )

SQL מדור קודם

ההוצאה הכספית הממוצעת לכל סשן ביולי 2017
SELECT
( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS
avg_revenue_by_user_per_visit
FROM (
SELECT
fullVisitorId,
SUM( totals.visits ) AS total_visits_per_user,
SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
totals.visits > 0
AND totals.transactions >= 1
AND totals.transactionRevenue IS NOT NULL
GROUP BY
fullVisitorId )

 

 

רצף היטים

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

SQL סטנדרטי

רצף הדפים שנצפו על ידי משתמשים ביולי 2017
#standardSQL
SELECT
fullVisitorId,
visitId,
visitNumber,
hits.hitNumber AS hitNumber,
hits.page.pagePath AS pagePath
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`,
UNNEST(hits) as hits
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
hits.type="PAGE"
ORDER BY
fullVisitorId,
visitId,
visitNumber,
hitNumber

SQL מדור קודם

רצף הדפים שנצפו על ידי משתמשים ביולי 2017
SELECT
fullVisitorId,
visitId,
visitNumber,
hits.hitNumber AS hitNumber,
hits.page.pagePath AS pagePath
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
hits.type="PAGE"
ORDER BY
fullVisitorId,
visitId,
visitNumber,
hitNumber

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

 

 

כמה מאפיינים מותאמים אישית ברמת ההיט או הביקור

מאפיין מותאם אישית ברמת ההיט
SELECT fullVisitorId, visitId, hits.hitNumber, hits.time,
MAX(IF(hits.customDimensions.index=1,
hits.customDimensions.value,
NULL)) WITHIN hits AS customDimension1,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

 

מאפיין מותאם אישית ברמת הסשן
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

בכל שאילתה:

בהצהרה SELECT מתבצעות שאילתות על השדות של המאפיינים והמדדים הרלוונטיים.

הפונקציה MAX:

  • מחזירה את המאפיין המותאם אישית כעמודה חדשה. ניתן לחזור על הפונקציה כדי להחזיר כמה מאפיינים מותאמים אישית כעמודות חדשות.
  • המשפטים WITHIN hits ו-WITHIN RECORD משמשים להערכת התנאי בתוך שדות חוזרים ב-BigQuery.
  • התנאי בתוך הפונקציה MAX מוערך לכל מאפיין מותאם אישית. אם המאפיין המותאם אישית הוא לא index=1 (hits) או index=2 (sessions), התנאי מחזיר את הערך NULL.
  • הפונקציה מחזירה את הערך המקסימלי, שהוא הערך של מאפיין מותאם אישית 1 להיטים או של מאפיין מותאם אישית 2 לסשנים, מאחר שכל שאר הערכים הם NULL.

שאילתות מתקדמות לדוגמה

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

מוצרים שנרכשו על ידי לקוחות שרכשו את מוצר A (מסחר אלקטרוני קלאסי)

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

מוצרים שנרכשו על ידי לקוח שרכש את מוצר A (מסחר אלקטרוני קלאסי)
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM [‘Dataset Name’ ]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [‘Dataset Name’ ]
  WHERE hits.item.productName CONTAINS 'Product Item Name A'
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName != 'Product Item Name A'
GROUP BY other_purchased_products
ORDER BY quantity DESC;
  1. בשורה הראשונה צריך לבחור את כל הפריטים האחרים שנרכשו על ידי המשתמש, ולחשב את הכמות של כל פריט שנרכש באמצעות פונקציית הצבירה COUNT()‎‏. לאחר מכן התוצאה מוצגת בשדה quantity, כאשר הפריט המשויך מופיע בשדה המוצר כ-other_purchased_products.
  2. בשאילתת המשנה האפורה בוחרים רק במשתמשים הייחודיים (fullVisitorId) שביצעו עסקאות (totals.transactions>=1) ורכשו את המוצר A במהלך עסקה (‎WHERE hits.item.productName CONTAINS 'Product Item Name A'‎‏‎).

הכללים (הצהרות WHERE ו-AND) בשאילתה ברמה העליונה (בירוק) מתעלמים מערכים ב-hits.item.productName שהם ריקים ומכילים את מוצר A.

דוגמה לשאילתה אם לקוח רוכש את המוצר Brighton Metallic Pens - Set of 4, אילו מוצרים אחרים הוא רכש?

מוצרים שנרכשו על ידי לקוח שרכש את המוצר ‎'Brighton Metallic Pens (Set of 4)'‎‏‎ ב-24 ביוני 2013
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM [GoogleStore.ga_sessions_20130624]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [GoogleStore.ga_sessions_20130624]
  WHERE hits.item.productName CONTAINS 'Brighton Metallic Pens - Set of 4'
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName !='Brighton Metallic Pens - Set of 4'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

ב-Dremel/BigQuery, השימוש ב-WHERE expr IN מפעיל את הפונקציה JOIN עם מגבלות גודל. באופן ספציפי, המגבלות מתייחסות לצד הימני של JOIN (במקרה הזה, מספר המבקרים), שצריך להיות קטן מ-8MB. ב-Dremel, הפעולה הזו נקראת broadcast JOIN. כאשר הגודל חורג מ-8MB, צריך להפעיל shuffled JOIN. ניתן לעשות זאת באמצעות התחביר JOIN EACH. לצערנו, לא ניתן לעשות זאת באמצעות IN, אבל ניתן לשכתב את השאילתה באמצעות JOIN.

מוצרים שנרכשו על ידי לקוחות שרכשו את מוצר A (מסחר אלקטרוני משופר)

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

מוצרים שנרכשים על ידי לקוח שרכש את מוצר A (מסחר אלקטרוני משופר)
SELECT hits.product.productSKU AS other_purchased_products, COUNT(hits.product.productSKU) AS quantity
FROM SELECT fullVisitorId, hits.product.productSKU, hits.eCommerceAction.action_type FROM TABLE_DATE_RANGE([73156703.ga_sessions_], TIMESTAMP('2015-07-01'), TIMESTAMP('2015-07-14')))
WHERE fullVisitorId IN (
  SELECT fullVisitorId
FROM TABLE_DATE_RANGE([73156703.ga_sessions_], TIMESTAMP('2015-07-01'), TIMESTAMP('2015-07-14'))
WHERE hits.product.productSKU CONTAINS '10 15103'
AND hits.eCommerceAction.action_type = '6'
GROUP BY fullVisitorId )
AND hits.product.productSKU IS NOT NULL
AND hits.product.productSKU !='10 15103'
AND hits.eCommerceAction.action_type = '6'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

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

זוהי דוגמה לשאילתה של פקודת JOIN() [...] ON, שתלויה רק בנתוני Analytics.

למטה מופיע סקריפט בסיסי לשאלה: מהו המספר הממוצע של אינטראקציות משתמש לפני רכישה?

המספר של אינטראקציות משתמש לפני רכישה
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number
FROM (
 SELECT hits.item.productSku, SUM(hits.hitNumber) AS sum_of_hit_number
 FROM [‘GA Dataset Name’ ]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS ‘Alias_Name_1’
JOIN (
 SELECT hits.item.productSku, COUNT( fullVisitorId ) AS total_hits
 FROM [‘GA Dataset Name’ ]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS ‘Alias_Name_2’
ON Alias_Name_1.hits.item.productSku = Alias_Name_2.hits.item.productSku;
  1. השורה הראשונה מבצעת את הפעולה המתמטית העיקרית של מציאת המספר הממוצע של אינטראקציות משתמש לכל מוצר, והשאילתה הזו יוצרת איחוד (join) בין שתי שאילתות משנה שנקראות ‘Alias_Name_1’ ו-‘Alias_Name_2’.
  2. שאילתת המשנה ‘Alias_Name_1’ משמשת להפקת שדה המשתמש בפונקציית הצבירה SUM() כדי לסכם את כל נתוני ההתאמות שתועדו למוצר.
  3. שאילתת המשנה ‘Alias_Name_2’ מוצאת את מספר ההתאמות שביצעו המשתמשים לכל מוצר, באמצעות הפונקציה COUNT()‎‏.
  4. בשורה האחרונה מופיע השדה (hits.item.productSku) המשותף שאותו חולקים שני מערכי הנתונים שאוחדו על ידי הפקודה JOIN.

הנה דוגמה לשאילתה מהו המספר הממוצע של אינטראקציות משתמש לפני רכישה, ב-10 בספטמבר 2013?

המספר הממוצע של אינטראקציות משתמש לפני רכישה, ב-10 בספטמבר 2013
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number
FROM (
 SELECT hits.item.productSku, SUM(hits.hitNumber) AS sum_of_hit_number
 FROM [GoogleStore.ga_sessions_20130728]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku  ) AS one
JOIN (
 SELECT hits.item.productSku, COUNT( fullVisitorId ) AS total_hits
 FROM [GoogleStore.ga_sessions_20130728]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS two
ON one.hits.item.productSku = two.hits.item.productSku;

אחוז המלאי שנמכר לכל מוצר

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

למטה מופיע סקריפט בסיסי לשאלה: מהו אחוז המלאי שנמכר לכל מוצר?

אחוז המלאי שנמכר לכל מוצר
SELECT AnalyticsImport.product_data_20130728.productId, ((( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold )) * 100 ) AS percentage_of_stock_sold
FROM [ ‘Imported_DataSet’ ]
JOIN (
  SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity_sold
  FROM [‘GA Dataset’ ]
  WHERE hits.item.productSku IS NOT NULL
   AND totals.transactions>=1
  GROUP BY hits.item.productSku ) AS ‘Alias_Name’
ON Imported_DataSet.’productId_field’ = Alias_Name.hits.item.productSku;
  1. בשורת הפלט הראשונה מוצגים שני שדות: שדה אחד שמכיל את כל מזהי המוצרים, ושדה שני שמהווה פעולה מתמטית שמציגה את אחוז המלאי שנמכר לכל מזהה מוצר.
  2. השאילתה הזו תלויה בשני מערכי נתונים, ולכן צריך להשתמש בפונקציה JOIN() ... ON. הפקודה מאחדת את השורות משני מערכי הנתונים על סמך השדה המשותף לשניהם. במקרה הזה, שני מערכי הנתונים הם [ ‘Imported_DataSet’ ] ו-‘Alias_Name’.
  3. הקבוצה ‎[ ‘Imported_DataSet’ ]‎ מכילה את הנתונים שאינם מ-Analytics. זה מערך הנתונים שכולל את שדה המדד של המלאי שנותר (Imported DataSet.’stock_left_field’) ואת שדה המאפיין של מזהה המוצר (Imported_DataSet.’productId_field’).
  4. ‘Alias_Name’ הוא השם שהוקצה לנתונים שהוחזרו על ידי שאילתת המשנה האפורה. שאילתת המשנה הזו משתמשת בנתוני Analytics כדי למצוא את סך כל הפריטים שנמכרו לכל מוצר.
  5. השורה האחרונה משתמשת בהצהרה ON כדי להציג את השדה המשותף לשני מערכי הנתונים, ואת נקודת האיחוד של שני מערכי הנתונים.

למשתנים רבים בשאילתה הזו מצורף השם של מערך הנתונים שלהם כקידומות (למשל, Imported_DataSet.’productId_field’, Alias_Name.quantity_sold). השם נועד להבהיר באיזה שדה בחרתם, ולהראות לאיזה מערך נתונים הוא שייך.

דוגמה לשאילתה מהו אחוז המלאי שנמכר לכל מוצר ב-28 ביולי 2013?

אחוז המלאי שנמכר לכל מוצר ב-28 ביולי 2013
SELECT AnalyticsImport.product_data_20130728.productId, ( ( ( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold ) ) * 100 ) AS percentage_of_stock_sold
FROM AnalyticsImport.product_data_20130728
JOIN (
  SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity_sold
  FROM GoogleStore.ga_sessions_20130728
  WHERE hits.item.productSku IS NOT NULL
   AND totals.transactions>=1
  GROUP BY hits.item.productSku ) AS one
ON AnalyticsImport.product_data_20130728.productId = one.hits.item.productSku
ORDER BY percentage_of_stock_sold DESC;

הרווחיות של כל מוצר

בהמשך מופיע סקריפט בסיסי לשאלה: מהי הרווחיות של כל מוצר?

הרווחיות של כל מוצר
SELECT Alias_Name.hits.item.productSku, ( Imported_DataSet.’product profit field’ * Alias_Name.quantity ) AS profit
FROM (
  SELECT Alias_Name.hits.item.productSku, Imported_DataSet.’product profit field’
  FROM [ ‘Imported Data Set’ ]
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM [ ‘GA Dataset Name’ ]
    WHERE hits.item.productSku IS NOT NULL
    GROUP BY hits.item.productSku ) AS ‘Alias_Name’
  ON Imported_DataSet.productId = Alias_Name.hits.item.productSku );
  1. השורה הראשונה כוללת את הפעולה המתמטית לחישוב הרווח הכולל שהתקבל מכל מוצר.
  2. שאילתת המשנה האפורה מבוססת על נתונים שאינם מ-Google Analytics, שכוללים נתונים לגבי הרווח ממכירת מוצרים.
  3. שאילתת המשנה האדומה מכילה את נתוני Google Analytics, שיצורפו לנתונים שאינם מ-Google Analytics. בשאילתה הזו מחושבת כמות הפריטים שנמכרו לכל מוצר.
  4. השורה האחרונה משתמשת בהצהרה ON כדי לציין את השדה המשותף לשני מערכי הנתונים. במקרה הזה, השדה הוא מזהה המוצר.

דוגמה לשאילתה מה הייתה הרווחיות של כל מוצר ב-28 ביולי 2013?

הרווח לכל מוצר ב-28 ביולי 2013
SELECT two.hits.item.productSku, ((AnalyticsImport.product_data_20130728.productprice-AnalyticsImport.product_data_20130728.productcost)*two.quantity) AS profit
FROM (
  SELECT two.hits.item.productSku, AnalyticsImport.product_data_20130728.productcost, two.quantity, AnalyticsImport.product_data_20130728.productprice
  FROM AnalyticsImport.product_data_20130728
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM GoogleStore.ga_sessions_20130728
    WHERE hits.item.productSku IS NOT NULL
    GROUP BY hits.item.productSku ) AS two
  ON AnalyticsImport.product_data_20130728.productId = two.hits.item.productSku );

הרווח מחושב על ידי מציאת ההפרש בין המחיר שבו נמכר המוצר לבין עלות הייצור שלו. המידע הזה שמור במערך הנתונים שאינם של Google Analytics.

רווחיות ריאלית של כל מוצר כולל החזרים כספיים

בהמשך מופיע סקריפט בסיסי לשאלה: מהי הרווחיות הריאלית של כל מוצר?

הרווחיות הריאלית של כל מוצר
SELECT Alias_Name.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit
FROM (
  SELECT Alias_Name.hits.item.productSku, ( ( Imported_DataSet.productprice - Imported_DataSet.productcost ) * Alias_Name.quantity ) AS gross_profit, ( ( Imported_DataSet.refunddeliveryprice + Imported_DataSet.productprice ) * Imported_DataSet.refundquantity ) AS total_refund_revenue
  FROM (
    SELECT Alias_Name.hits.item.productSku, Imported_DataSet.productcost, Alias_Name.quantity, Imported_DataSet.productprice, Imported_DataSet.refunddeliveryprice, Imported_DataSet.refundquantity
    FROM [ ‘Imported DataSet Name’ ] AS 'Imported_DataSet'
    JOIN (
      SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM [‘GA Dataset Name’ ]
      WHERE hits.item.productSku IS NOT NULL
       AND totals.transactions >=1
      GROUP BY hits.item.productSku ) AS 'Alias_Name'
    ON Imported_DataSet.productId = Alias_Name.hits.item.productSku ) );
  1. השאילתה הזו דומה מאוד לשאילתה מהי הרווחיות של כל מוצר? ההבדלים היחידים הם בקבוצת הנתונים שאינם של Google Analytics בשאילתת המשנה האפורה, ובפעולה המתמטית של חישוב הרווח הריאלי בשורה הראשונה.
  2. במערך הנתונים שאינם מ-Google Analytics מחושב גם סכום הכסף הכולל שהוצאתם על החזרים כספיים (בהצהרה SELECT של שאילתת המשנה האדומה).
  3. לאחר מכן, בשורה 1 מתבצעת פעולה מתמטית כדי למצוא את הרווח הריאלי, על-ידי הפחתת ההכנסה שהוצאתם על החזרים כספיים מתוך הרווח ברוטו.

מידע נוסף על השאילתה זמין בקטע הרווחיות של כל מוצר.

דוגמה לשאילתה מה הייתה הרווחיות הריאלית של כל מוצר ב-28 ביולי 2013?

הרווחיות הריאלית של כל מוצר ב-28 ביולי 2013
SELECT two.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit
FROM (
  SELECT two.hits.item.productSku, ( ( AnalyticsImport.product_data_20130728.productprice - AnalyticsImport.product_data_20130728.productcost ) * two.quantity ) AS gross_profit, ( ( AnalyticsImport.product_data_20130728.refunddeliveryprice + AnalyticsImport.product_data_20130728.productprice ) * AnalyticsImport.product_data_20130728.refundquantity ) AS total_refund_revenue
  FROM (
    SELECT two.hits.item.productSku, Analytics.product_data_20130728.productcost, two.quantity, AnalyticsImport.product_data_20130728.productprice, AnalyticsImport.product_data_20130728.refunddeliveryprice, AnalyticsImport.product_data_20130728.refundquantity
    FROM AnalyticsImport.product_data_20130728
    JOIN (
      SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM GoogleStore.ga_sessions_20130728
      WHERE hits.item.productSku IS NOT NULL
       AND totals.transactions >=1
      GROUP BY hits.item.productSku ) AS two
    ON AnalyticsImport.product_data_20130728.productId = two.hits.item.productSku ) );

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

סה"כ הכנסות מהחזר כספי למוצר = ( מחיר המוצר + מחיר ההחזר הכספי למוצר ) * כמות המוצרים שבוצע עבורם החזר כספי

האם המידע הועיל?

איך נוכל לשפר את המאמר?

צריכים עזרה נוספת?

תוכלו לנסות את האפשרויות הבאות:

פרסום בפורום העזרה מהקהילה קבלת תשובות מחברי הקהילה
פנייה אלינו אפשר לספר לנו עוד על הבעיה, ונעזור לכם לפתור אותה
true
learning path
בחירת תוכנית הלימודים האישית שלכם

מומלץ לנסות את google.com/analytics/learn, משאב חדש שיעזור לכם להפיק כמה שיותר מ-Google Analytics 4. באתר החדש תמצאו סרטונים, מאמרים ותהליכים מודרכים, וגם קישורים ל-Discord, לבלוג, לערוץ ה-YouTube ולמאגר ה-GitHub של Google Analytics.

מתחילים ללמוד כבר היום!

חיפוש
ניקוי החיפוש
סגירת החיפוש
התפריט הראשי
16007247118544245532
true
חיפוש במרכז העזרה
true
true
true
true
true
69256
false
false