דוח מכירות ריל טיים לאתרי איקומרס

שוקי מן

2 חודשים ago

כמו שאתם בטח יודעים, הריל טיים בגוגל אנליטיקס 4 הוא לא משהו, בלשון המעטה.

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

בזמן האחרון קיבלתי כמה בקשות לייצר דוח מכירות בזמן אמת, אז הרמתי את הכפפה ויצרתי דוח ב-Looker Studio שנותן לכם את הנתונים הבאים בזמן אמת:

1. כמה טראפיק/מכירות/רווח נכנס היום עד עכשיו.

2. השוואה ליום המקביל בשבוע שעבר, כדי שאם למשל היום יום שלישי ומכרתם X, וביום שלישי שעבר מכרתם 0.9X – תדעו שהיום יותר חלש ואתם צריכים לעשות משהו.

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

ככה הדוח נראה:

שלב ראשון – חיבור ל-BigQuery

מכיוון שהנתונים לא זמינים בגוגל אנליטיקס באופן מיידי, השתמשתי כמובן ב-BigQuery.

בהנחה וחיברתם את האנליטיקס 4 שלכם לביג קוורי, ובחרתם שאתם רוצים לייצא גם Streaming, האיוונטים יגיעו לטבלה מיוחדת, נקרא לה intraday, בזמן אמת:

שלב שני – יצירת ה-query שמביא את הדאטה

למי שמתעניין, הנה הסבר על השאילתה:

קודם כל אני מביא את הדאטה של ה-device_category (כי אני רוצה להיות מסוגל לסגמנט לפי מכשיר בהמשך).

אחרי זה אני ממיר את event_timestamp לשעה (1,2,3 וכו׳) בהתאם ל-timezone של ישראל.

אחרי זה אני סופר את כמות הסשנים הייחודיים שיש לי, כמות הרכישות והרווח.

שימו לב שה-FROM מסתיים ב-events_intraday_*, מה שאומר שהדאטה ילקח מתוך הטבלה של ה-streaming שהגדרנו בהתחלה.

לאחר מכן יהיה WHERE שיגדיר את התאריך של היום.

ואז GROUP BY הכרחי

SELECT
    device.category AS device_category,
    EXTRACT(HOUR FROM DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Jerusalem")) AS event_hour,
    COUNT(DISTINCT CONCAT(user_pseudo_id, '-', (SELECT value.int_value FROM UNNEST(event_params) WHERE key = "ga_session_id"))) AS session_count,
    COUNTIF(event_name = "purchase") AS purchase_count,
    SUM(CASE WHEN event_name = "purchase" THEN COALESCE((SELECT value.int_value FROM UNNEST(event_params) WHERE key = "value"),(SELECT value.double_value FROM UNNEST(event_params) WHERE key = "value")) ELSE 0 END) AS total_purchase_value
  FROM
    `YOUR_DATASET_ID.events_intraday_*`
  WHERE
    _table_suffix = FORMAT_DATE('%Y%m%d', CURRENT_DATE())
  GROUP BY
    device_category,
    event_hour

SELECT

device.category AS device_category,

EXTRACT(HOUR FROM DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Jerusalem")) AS event_hour,

COUNT(DISTINCT CONCAT(user_pseudo_id, '-', (SELECT value.int_value FROM UNNEST(event_params) WHERE key = "ga_session_id"))) AS session_count,

COUNTIF(event_name = "purchase") AS purchase_count,

SUM(CASE WHEN event_name = "purchase" THEN COALESCE((SELECT value.int_value FROM UNNEST(event_params) WHERE key = "value"),(SELECT value.double_value FROM UNNEST(event_params) WHERE key = "value")) ELSE 0 END) AS total_purchase_value

FROM

`YOUR_DATASET_ID.events_intraday_*`

WHERE

_table_suffix = FORMAT_DATE('%Y%m%d', CURRENT_DATE())

GROUP BY

device_category,

event_hour

זה יתן לנו טבלה כזאת:

עכשיו אנחנו צריכים להביא את אותו דאטה בדיוק – אבל על היום המקביל בשבוע שעבר.

מעבר לשמות העמודות, צריך לשנות את ה-FROM ולהוריד את המילה intraday (כי עכשיו אתם רוצים לקחת דאטה מטבלאות רגילות, ולא מהטבלה של ה-streaming):

FROM `YOUR_DATASET_ID.events_*`

1	FROM `YOUR_DATASET_ID.events_*`

ולשנות את ה-WHERE שצריך לתת את התאריך של היום מינוס 7 ימים:

WHERE _table_suffix = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))

1	WHERE _table_suffix = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))

לבסוף נעשה JOIN בין 2 הטבלאות וככה זה יראה:

WITH current_day_data AS (
  SELECT
    device.category AS device_category,
    EXTRACT(HOUR FROM DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Jerusalem")) AS event_hour,
    COUNT(DISTINCT CONCAT(user_pseudo_id, '-', (SELECT value.int_value FROM UNNEST(event_params) WHERE key = "ga_session_id"))) AS session_count,
    COUNTIF(event_name = "purchase") AS purchase_count,
    SUM(CASE WHEN event_name = "purchase" THEN COALESCE((SELECT value.int_value FROM UNNEST(event_params) WHERE key = "value"),(SELECT value.double_value FROM UNNEST(event_params) WHERE key = "value")) ELSE 0 END) AS total_purchase_value
  FROM `YOUR_DATASET_ID.events_intraday_*`
  WHERE _table_suffix = FORMAT_DATE('%Y%m%d', CURRENT_DATE())
  GROUP BY
    device_category,
    event_hour
),
last_week_data AS (
  SELECT
    device.category AS device_category,
    EXTRACT(HOUR FROM DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Jerusalem")) AS event_hour,
    COUNT(DISTINCT CONCAT(user_pseudo_id, '-', (SELECT value.int_value FROM UNNEST(event_params) WHERE key = "ga_session_id"))) AS last_week_session_count,
    COUNTIF(event_name = "purchase") AS last_week_purchase_count,
    SUM(CASE WHEN event_name = "purchase" THEN COALESCE((SELECT value.int_value FROM UNNEST(event_params) WHERE key = "value"),(SELECT value.double_value FROM UNNEST(event_params) WHERE key = "value")) ELSE 0 END) AS last_week_total_purchase_value
  FROM `YOUR_DATASET_ID.events_*`
  WHERE _table_suffix = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
  GROUP BY
    device_category,
    event_hour
)
SELECT
  c.device_category,
  c.event_hour,
  c.session_count,
  c.purchase_count,
  c.total_purchase_value,
  COALESCE(lw.last_week_session_count, 0) AS last_week_session_count,
  COALESCE(lw.last_week_purchase_count, 0) AS last_week_purchase_count,
  COALESCE(lw.last_week_total_purchase_value, 0) AS last_week_total_purchase_value
FROM
  current_day_data c
LEFT JOIN
  last_week_data lw
ON
  c.device_category = lw.device_category AND c.event_hour = lw.event_hour
ORDER BY
  c.device_category,
  c.event_hour;

WITH current_day_data AS (

SELECT

device.category AS device_category,

EXTRACT(HOUR FROM DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Jerusalem")) AS event_hour,

COUNT(DISTINCT CONCAT(user_pseudo_id, '-', (SELECT value.int_value FROM UNNEST(event_params) WHERE key = "ga_session_id"))) AS session_count,

COUNTIF(event_name = "purchase") AS purchase_count,

FROM `YOUR_DATASET_ID.events_intraday_*`

WHERE _table_suffix = FORMAT_DATE('%Y%m%d', CURRENT_DATE())

GROUP BY

device_category,

event_hour

last_week_data AS (

SELECT

device.category AS device_category,

EXTRACT(HOUR FROM DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Jerusalem")) AS event_hour,

COUNT(DISTINCT CONCAT(user_pseudo_id, '-', (SELECT value.int_value FROM UNNEST(event_params) WHERE key = "ga_session_id"))) AS last_week_session_count,

COUNTIF(event_name = "purchase") AS last_week_purchase_count,

FROM `YOUR_DATASET_ID.events_*`

WHERE _table_suffix = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))

GROUP BY

device_category,

event_hour

)

SELECT

c.device_category,

c.event_hour,

c.session_count,

c.purchase_count,

c.total_purchase_value,

COALESCE(lw.last_week_session_count, 0) AS last_week_session_count,

COALESCE(lw.last_week_purchase_count, 0) AS last_week_purchase_count,

COALESCE(lw.last_week_total_purchase_value, 0) AS last_week_total_purchase_value

FROM

current_day_data c

LEFT JOIN

last_week_data lw

c.device_category = lw.device_category AND c.event_hour = lw.event_hour

ORDER BY

c.device_category,

c.event_hour;

נריץ בביג קוורי וזה מה שנקבל:

עכשיו נלך ללוקר סטודיו וניצור data source חדש:

נבחר Custom Query ונסמן Enable date range parameters:

וכמובן נדביק את ה-query שלנו.

הערת סוגריים: באופן עקרוני, אם אתם רוצים שהגרפים יראו רק את היום לעומת היום המקביל בשבוע שעבר, אתם לא צריכים לסמן את הצ׳קבוקס של enable date range וגם לא צריכים לשנות שום דבר בשאילתה.

אבל, אם אתם רוצים להיות מסוגלים לבחור תאריך אחר להשוואה, תסמנו את הצ׳קבוקס ותחליפו את השורה של ה-WHERE ב-last_week_data בשורה הבאה:

_table_suffix between @DS_START_DATE AND @DS_END_DATE

1	_table_suffix between @DS_START_DATE AND @DS_END_DATE

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

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

וזהו. הדאטה אצלכם.

שלב שלישי: יצירת הגרפים

עכשיו אתם יכולים לשחק עם זה איך שאתם רוצים.

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

אל תשכחו להגדיל את כמות העמודות ל-24 כדי שכל השעות יופיעו:

זה מה שנקבל:

שלב רביעי: תוסף לכרום

כדי להציג את הדשבורד ב-new tab של הכרום, הלכתי ל-ChatGPT וביקשתי ממנו ליצור לי תוסף לכרום שיכול להציג עמודי אינטרנט, עם אפשרות ליצור כמה טאבים.

את הקבצים של התוסף תוכלו להוריד מכאן >>

ואז לכו ל chrome://extensions

הדליקו את המתג של dev mode:

ואז Load unpacked, בחרו את הקובץ שהורדתם ופתחו טאב חדש בדפדפן

לחצו על הכפתור הירוק של Add Report, תנו לו שם כלשהו (למשל Client A real time) וב-Report URL הכניסו את הקישור לדוח.

כדי ליצור את הקישור לדוח, לכו לדוח שלכם בלוקר סטודיו, לחצו על Share ואז Embed report

בחרו באופציה Embed URL והעתיקו את ה-URL שזה נותן לכם לשדה של Report URL ב-טאב החדש.

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

שימו לב שאפשר ליצור כמה טאבים ב-new tab, למקרה שאתם רוצים לעקוב אחרי כמה לקוחות או משהו כזה.

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