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

1. מבוא

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

מה תפַתחו

בסדנת הקוד הזו נשתמש ב-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.png

4939332a5d8f9f19.png

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

3. טרנספורמציה של תשובות לסקר

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

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

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

3eeedc50b0fd54fd.png

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

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

cab8a38a96a13ce4.png

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

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

c6ea3d47d4dd5e78.png

  • שם השאלה: כל שאלה הופכת לשם עמודה בפורמט הזה: 'Question [Option]'.
  • תגובה: כל תגובה ספציפית ברשימה הופכת לעמודה עם ערך ייחודי.
  • דרישות הטרנספורמציה: כל שאלה/תשובה צריכה להפוך לשורה חדשה בטבלה ולהתפצל לשתי עמודות. עמודה אחת עם האפשרות של השאלה, והעמודה השנייה עם התשובה.

9223d0271516c58d.png

שאלות אמריקאיות ברשת עם תיבות סימון

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

4e3189b8cc2d4a8b.png

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

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

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

3c3c2bd098e03003.png

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

4. פיתוח תהליך העבודה של Cloud Dataprep

ייבוא של 'תבנית העיצוב של Google Forms Analytics' ב-Cloud Dataprep

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

ba7c0cb0eec398df.png

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

44978861eb34ec71.png

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

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

55c16f0c04366f0c.png

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

8afeef260c96277f.png

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

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

חיבור טבלאות BigQuery

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

קודם כל, מתחילים לערוך את 'יעדים ידניים'.

a3fc2cb80153ec25.png

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

85791e6162a370de.png

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

1f3e4887baaeaffd.png

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

f4eaa05ecf9de162.png

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

46edea1b8ca63270.png

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

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

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

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

afa421849b1bd398.png

מומלץ לבדוק כל אחד מהמתכונים בנפרד, החל מ'ניקוי כותרות', לאחר מכן 'SingleChoiceSELECT-Questions' ואז את כל שאר המתכונים שמופיעים מתחת.

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

449da06d96cd520e.png

4ac6e14f578d0707.png

6. הפעלת תהליך Cloud Dataprep

עכשיו, אחרי שהמקור והיעדים מוגדרים כראוי, אפשר להריץ את התהליך כדי לבצע טרנספורמציה של התשובות ולטעון אותן ל-BigQuery. בוחרים כל אחד מהפלטים ולוחצים על הלחצן 'הפעלה'. אם טבלת 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. ניקוי

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

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