מדריך: יצירת שדות מחושבים ב-Data Studio

מטרות

במדריך הזה תלמדו:

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

תרחיש

נניח שאתם מנהלים חנות וירטואלית לציוד לחיות מחמד. יכול להיות שתמדדו נתוני מכירות, כמו קודי מוצרים (SKU), שמות פריטים, מחיר וכמות שנמכרה. הנה דוגמה:

מק"ט פריט מחיר כמות שנמכרה
D-001 מזון בריא לכלבים

79.96

16

B-002 מקל לתוכי

74.97

8

B-001 Pretty Bird Bird Seed

31.96

20

C-002 Hungry Kitty Cat Food

29.98

3

D-002 צעצוע גור שמח

17.97

11

C-001

Happy Cat Catnip

14.97

4

שדות מחושבים ב-Data Studio יכולים לעזור לכם לעבד את הנתונים האלה ולהרחיב אותם. בתרגיל הזה תשתמשו בשדות שמכילים חישובים כדי לבצע את המשימות הבאות:

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

נתונים לדוגמה

כדי לבצע את התרגיל הזה כמו שהוא כתוב, צריך להשתמש בנתונים לדוגמה הבאים. אחרת, משתמשים בנתונים שלכם (ומתאימים את שמות השדות בהתאם). צריכה להיות לכם גישה ל-Google Sheets. אפשר גם להשתמש במחבר להעלאת קבצים כדי לייבא את הנתונים ל-Data Studio.

מעתיקים את הנתונים האלה לקובץ טקסט:

SKU,Item,Price,Qty Sold
D-001,Healthy Dog Dog Food,79.96,16
B-002,Parrot Perch,74.97,8
B-001,Pretty Bird Bird Seed,31.96,20
C-002,Hungry Kitty Cat Food,29.98,3
D-002,Playful Puppy Toy,17.97,11
C-001,Happy Cat Catnip,14.97,4

שלב 1: הכנת הנתונים

  1. מעתיקים את הנתונים לדוגמה שבתיבה הקודמת.
  2. פותחים כלי לעריכת טקסט, מדביקים את נתוני הדגימה ושומרים את הקובץ בשם pet-store-data.csv.

  • יוצרים גיליון אלקטרוני חדש ב-Google Sheets.

  • ב-Sheets, בוחרים באפשרות קובץ > ייבוא.

  • בוחרים באפשרות העלאה ומעלים את קובץ נתוני הדוגמה שיצרתם בשלב 2.

  • בוחרים באפשרות החלפת גיליון אלקטרוני.

  • נותנים שם לגיליון האלקטרוני Pet Store.

שלב 2: יוצרים דוח ומוסיפים את הנתונים

  1. יוצרים דוח חדש ב-Data Studio.
  2. בחלונית הוספת נתונים לדוח, בוחרים במחבר של Google Sheets.
  3. בוחרים את Pet Store הגיליון האלקטרוני ואת גיליון העבודה שיצרתם.
  4. משאירים את האפשרויות שמוגדרות כברירת מחדל מסומנות.
  5. לוחצים על ADD.

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

שלב 3: יצירת שדה של סכום ביניים

  1. בתפריט, בוחרים באפשרות Resource > Manage added data sources.

  2. מאתרים את מקור הנתונים Pet Store ולוחצים על סמל העריכה. עריכה.

    מופיעה החלונית data source fields (שדות של מקור הנתונים).

  3. לוחצים על הוספת שדה.

    יופיע עורך השדות המחושבים.

  4. בשדה שם השדה, מזינים Subtotal.

  5. בשדה נוסחה, מזינים Price * Qty Sold.

  6. לוחצים על SAVE.

  7. כדי לחזור לעורך הראשי של מקור הנתונים, לוחצים על כל השדות.

שלב 4: יצירת שדה ההנחה

נניח שאתם רוצים לתת שיעורי הנחה שונים בהתאם לערך ההזמנה. יוצרים עוד שדה מחושב בשם Discount, שבודק את השדה Subtotal ומחיל את ההנחות הבאות.

ערך סכום הביניים שיעור ההנחה
‫0-99 0
‫100-499

‫5%

‫500 או יותר ‫10%

הערות:

  • בשלב הזה, מחשבים רק את גובה ההנחה. תשתמשו בו בשלב הבא כדי להכפיל את הערך בשדה Subtotal (סיכום ביניים) בערך בשדה Discount (הנחה).
  • משתמשים בביטוי CASE כדי להעריך את השדה Subtotal.
    • צריך להשתמש בסעיף WHEN לכל שיעור הנחה.
    • משתמשים בפסוקית ELSE כדי להחזיר את המחיר המלא (הנחה של 0%).
  • ההנחה מבוטאת כערך עשרוני. לדוגמה, הנחה של 25% תהיה ‎0 .25.

פתרון

‫
CASE
WHEN Subtotal > 499 THEN .9
WHEN Subtotal > 100 THEN .95
ELSE 1
END
 THEN

שלב 5: יצירת שדה הסכום הכולל

יוצרים שדה מחושב נוסף בשם Total, שמכפיל את השדה Subtotal בשדה Discount.

פתרון

Subtotal * Discount

שלב 6: יצירת השדה 'מחלקה'

יוצרים שדה מחושב סופי בשם Department שמנתח את שדה המק"ט ומקצה ערכים חדשים בהתאם למק"ט. תקבצו פריטים שקשורים לכלבים, לחתולים ולציפורים במחלקות נפרדות.

טיפ: אפשר להשתמש שוב בהצהרה CASE, הפעם עם הפונקציה STARTS_WITH (או REGEXP_MATCH).

פתרון 

CASE
WHEN STARTS_WITH(SKU, "C") THEN "Cat"
WHEN STARTS_WITH(SKU, "D") THEN "Dog"
WHEN STARTS_WITH(SKU, "B") THEN "Bird"
ELSE "Misc"
END

שלב 7: הצגת הנתונים בדוח

  1. אחרי ששומרים את השדה Department, בצד ימין, לוחצים על ALL FIELDS כדי לחזור לכלי לעריכת מקורות נתונים.
  2. לוחצים על סיום ואז סוגרים את החלונית 'ניהול מקורות נתונים'.

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

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

פתרונות חלופיים

בפתרון הקודם, הבעיה מחולקת לחלקים נפרדים ונוצרים שדות נפרדים לטיפול בכל חלק, כמו שעשיתם עם השדות Subtotal, ‏ Discount ו-Total.

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

CASE
WHEN Price * Qty Sold > 499 THEN (Price * Qty Sold) * .9
WHEN Price * Qty Sold > 100 THEN (Price * Qty Sold) * .95
ELSE Price * Qty Sold
END

לכל גישה יש יתרונות וחסרונות:

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

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

הדרך האמצעית

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

CASE
WHEN Subtotal > Large Order THEN Subtotal * Large Order Discount
WHEN Subtotal > Medium Order THEN Subtotal * Medium Order Discount
ELSE Subtotal
END

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

שדה מחושב בשם Large Order עם הערך 499 שמוזן בתיבת הנוסחה.

שדה מחושב בשם Large Order Discount (הנחה על הזמנה גדולה) עם הערך ‎ .9 שהוזן בתיבה Formula (נוסחה).

היתרון של הפתרון הזה הוא שהוא קל לקריאה, גמיש וקל יחסית להתאמה לפי הצורך.