טרנספורמציה וטעינה של התשובות לסקר ב-Google Forms ל-BigQuery

1. מבוא

יש הרבה סיבות להריץ סקרים: להעריך את שביעות רצון הלקוחות, לבצע מחקר שוק, לשפר מוצר או שירות או להעריך את מידת המעורבות של העובדים. עם זאת, אם ניסיתם לעבוד עם נתוני סקרים בעבר, אתם בטח יודעים שקשה לעבוד עם הפורמט הרגיל. במדריך הזה נבנה צינור עיבוד נתונים אוטומטי שמתעד את התוצאות של Google Forms, מכין את הנתונים לניתוח באמצעות Cloud Dataprep, טוען אותם ל-BigQuery ומאפשר לצוות לבצע ניתוח חזותי באמצעות כלים כמו Looker או Data Studio.

מה תפתחו

ב-Codelab הזה תשתמשו ב-Dataprep כדי להמיר את התשובות מהסקר לדוגמה ב-Google Forms לפורמט שמתאים לניתוח נתונים. תעבירו את הנתונים שעברו טרנספורמציה ל-BigQuery, שם תוכלו לשאול שאלות מעמיקות יותר באמצעות SQL ולצרף אותם למערכי נתונים אחרים כדי לבצע ניתוחים מתקדמים יותר. בסיום, תוכלו לעיין בלוחות בקרה מוכנים מראש או לחבר כלי בינה עסקית משלכם ל-BigQuery כדי ליצור דוחות חדשים.

מה תלמדו

  • איך משנים את נתוני הסקר באמצעות Dataprep
  • איך להעביר נתוני סקרים ל-BigQuery
  • איך לקבל יותר תובנות מנתוני הסקרים

הדרישות

  • פרויקט ב-Google Cloud שבו מופעלים החיוב, BigQuery ו-Dataprep
  • ידע בסיסי ב-Dataprep יכול לעזור, אבל הוא לא חובה
  • ידע בסיסי ב-BigQuery וב-SQL יכול לעזור, אבל לא חובה

2. ניהול התשובות ב-Google Forms

נתחיל בבחינה מקרוב של התגובות לטופס ב-Google Forms לסקר לדוגמה.

f3d25efd2cc923f5.png

כדי לייצא את תוצאות הסקר, לוחצים על סמל Google Sheets בכרטיסייה "תשובות" ויוצרים גיליון אלקטרוני חדש או טוענים את התוצאות לגיליון קיים. התשובות ימשיכו להתווסף לגיליון האלקטרוני ב-Google Forms כל עוד הכפתור "מקבל תגובות" מופעל.

d499e5a4dccdf5fd.png4939332a5d8f9f19.png

עכשיו נסקור כל סוג של תשובה ונראה איך היא מופיעה בקובץ Google Sheets.

3. המרת התשובות לסקר

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

שאלות עם תשובה אחת: תשובה קצרה, פסקה, תפריט נפתח, סולם ליניארי וכו'.

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

3eeedc50b0fd54fd.png

שאלות אמריקאיות: שאלות אמריקאיות, תיבת סימון

  • שם השאלה: שם העמודה
  • תשובה: רשימת ערכים שמופרדים באמצעות נקודה ופסיק (למשל, "תשובה 1; תשובה 4; תשובה 6")
  • דרישות טרנספורמציה: צריך לחלץ את רשימת הערכים ולשנות את הציר שלהם, כך שכל תשובה תהפוך לשורה חדשה.

cab8a38a96a13ce4.png

שאלות של רשת שאלות אמריקאיות

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

c6ea3d47d4dd5e78.png

  • שם השאלה: כל שאלה בנפרד הופכת לשם של עמודה בפורמט "שאלה [אפשרות]".
  • תשובה: כל תשובה בנפרד ברשת הופכת לעמודה עם ערך ייחודי.
  • דרישות לטרנספורמציה: כל שאלה או תשובה צריכה להפוך לשורה חדשה בטבלה, ולהתפצל לשתי עמודות. עמודה אחת עם האפשרות של השאלה ועמודה שנייה עם התשובה.

9223d0271516c58d.png

שאלות מסוג רשת של תיבות סימון עם כמה אפשרויות

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

4e3189b8cc2d4a8b.png

  • שם השאלה: כל שאלה בנפרד הופכת לשם של עמודה בפורמט "שאלה [אפשרות]".
  • תשובה: כל תשובה בנפרד ברשת הופכת לעמודה עם רשימת ערכים שמופרדים בנקודה ופסיק.
  • דרישות טרנספורמציה: סוגי השאלות האלה משלבים את הקטגוריות "תיבת סימון" ו "רשת של שאלות אמריקאיות" וצריך לפתור אותן בסדר הזה.

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

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

3c3c2bd098e03003.png

בהמשך נראה איך הטרנספורמציות האלה מטופלות באמצעות Cloud Dataprep.

4. יצירת התהליך ב-Cloud Dataprep

ייבוא של Google Forms Analytics Design Pattern ב-Cloud Dataprep

מורידים את חבילת התהליך Google Forms Analytics Design Pattern (בלי לפתוח את קובץ ה-ZIP). באפליקציית Cloud Dataprep, לוחצים על הסמל Flows בסרגל הניווט הימני. אחר כך, בדף Flows, בוחרים באפשרות Import בתפריט ההקשר.

ba7c0cb0eec398df.png

אחרי שמייבאים את התהליך, בוחרים את התהליך המיובא כדי לערוך אותו. המסך אמור להיראות כך:

44978861eb34ec71.png

קישור גיליון אלקטרוני של תוצאות סקר ב-Google Sheets

בצד שמאל של התהליך, צריך לקשר מחדש את מקור הנתונים לגיליון ב-Google Sheets שמכיל את התוצאות מ-Google Forms. לוחצים לחיצה ימנית על האובייקט של מערכי הנתונים ב-Google Sheets ובוחרים באפשרות "החלפה".

55c16f0c04366f0c.png

לאחר מכן לוחצים על הקישור "Import Datasets" (ייבוא מערכי נתונים) בתחתית החלון. לוחצים על סמל העיפרון "עריכת דרך".

8afeef260c96277f.png

שם, מחליפים את הערך הנוכחי בקישור הזה שמפנה לגיליון אלקטרוני ב-Google Sheets עם תוצאות מ-Google Forms. אפשר להשתמש בדוגמה שלנו או בעותק משלכם: https://docs.google.com/spreadsheets/d/1DgIlvlLceFDqWEJs91F8rt1B-X0PJGLY6shkKGBPWpk/edit?usp=sharing

לוחצים על Go (המשך) ואז על Import & Add to Flow (ייבוא והוספה לתהליך) בפינה השמאלית התחתונה. כשחוזרים למודל, לוחצים על הכפתור "החלפה" בפינה השמאלית התחתונה.

קישור טבלאות ב-BigQuery

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

קודם כול, עורכים את "היעדים הידניים"

a3fc2cb80153ec25.png

במסך "הגדרות פרסום" שמופיע, לוחצים על כפתור העריכה.

85791e6162a370de.png

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

1f3e4887baaeaffd.png

בוחרים את מערך הנתונים ב-BigQuery שאליו רוצים לטעון את התוצאות מ-Google Forms. אם עדיין לא יצרתם מערך נתונים ב-BigQuery, אתם יכולים לבחור באפשרות default (ברירת מחדל).

f4eaa05ecf9de162.png

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

46edea1b8ca63270.png

חוזרים על אותן הפעולות בכל פלט. בסך הכול צריך לערוך 8 יעדים.

5. הסבר על תהליך העבודה ב-Cloud Dataprep

הרעיון הבסיסי של תהליך "תבנית העיצוב של ניתוח נתונים ב-Google Forms" הוא לבצע את הטרנספורמציות בתשובות לסקר כפי שתואר קודם – על ידי פירוק כל קטגוריית שאלות למתכון ספציפי של טרנספורמציית נתונים ב-Cloud Dataprep.

התהליך הזה מחלק את השאלות ל-4 טבלאות (בהתאם ל-4 קטגוריות השאלות, למטרות פשטות)

afa421849b1bd398.png

אנחנו ממליצים לעבור על כל אחד מהמתכונים בזה אחר זה, החל מ-"Clean Headers" (כותרות נקיות) ואז "SingleChoiceSELECT-Questions" (שאלות SingleChoiceSELECT) ואחריהם כל אחד מהמתכונים האחרים שמתחת.

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

449da06d96cd520e.png4ac6e14f578d0707.png

6. הרצת התהליך ב-Cloud Dataprep

עכשיו, אחרי שהגדרתם את המקור והיעדים, אתם יכולים להריץ את התהליך כדי להמיר את התשובות ולטעון אותן ל-BigQuery. בוחרים כל אחד מהפלטים ולוחצים על הכפתור Run (הפעלה). אם הטבלה שצוינה ב-BigQuery קיימת, Dataprep יוסיף שורות חדשות. אחרת, הוא ייצור טבלה חדשה.

47cf50f6d17a5b1e.png

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

afc79eeb27202fb4.png

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

7. ניתוח נתוני הסקר ב-BigQuery

במסוף Google ל-BigQuery, אתם אמורים לראות את הפרטים של כל אחת מהטבלאות החדשות.

df370873572511ac.png

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

SELECT
   programming_answers.Language  AS programming_answers_language,
   project_answers.Title  AS project_answers_title,
   AVG((case when programming_answers.Level='None' then 0 
when programming_answers.Level='beginner' then 1
when programming_answers.Level='competent' then 2 
when programming_answers.Level='proficient' then 3
when programming_answers.Level='expert' then 4 
else null end) ) AS programming_answers_average_level_value
FROM `my-project.DesignPattern.A000111_ProjectAnswers` AS project_answers
INNER JOIN `my-project.A000111_ProgrammingAnswers` AS programming_answers
ON programming_answers.RESPONSE_ID = project_answers.RESPONSE_ID
GROUP BY 1,2
ORDER BY 3 DESC

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

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

SELECT
   account.TYPE  AS account_type,
   COUNT(DISTINCT project_answers.Domainname) AS project_answers_count_domains
FROM `my-project.A000111_ProjectAnswers` AS project_answers
LEFT JOIN `my-project.testing.account` AS account 
ON project_answers.Domainname=account.website
GROUP BY 1

8. ניתוח חזותי

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

Looker

אם כבר יש לכם מכונת Looker, אתם יכולים להשתמש ב-LookML בתיקייה הזו כדי להתחיל לנתח את נתוני הסקר ונתוני ה-CRM לדוגמה של התבנית הזו. פשוט יוצרים פרויקט Looker חדש, מוסיפים את ה-LookML ומחליפים את שמות החיבור והטבלה בקובץ כך שיתאימו להגדרות של BigQuery. אם אין לכם מכונה של Looker אבל אתם רוצים לקבל מידע נוסף, אתם יכולים לקבוע הדגמה (דמו) כאן.

129db05d6f85f484.png

Data Studio

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

5e744869e3fe3f8f.png

9. ניקוי

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

  1. במסוף Cloud, נכנסים לדף Manage resources.
  2. ברשימת הפרויקטים, בוחרים את הפרויקט שרוצים למחוק ולוחצים על Delete.
  3. כדי למחוק את הפרויקט, כותבים את מזהה הפרויקט בתיבת הדו-שיח ולוחצים על Shut down.