เปลี่ยนรูปแบบและโหลดคำตอบจากแบบสำรวจ Google ฟอร์มไปยัง BigQuery

1. บทนำ

การทำแบบสำรวจมีประโยชน์หลายอย่าง เช่น ประเมินความพึงพอใจของลูกค้า ทำการวิจัยตลาด ปรับปรุงผลิตภัณฑ์หรือบริการ หรือประเมินการมีส่วนร่วมของพนักงาน อย่างไรก็ตาม หากคุณเคยลองทำงานกับข้อมูลแบบสำรวจมาก่อน คุณอาจทราบว่ารูปแบบมาตรฐานนั้นทำงานด้วยได้ยาก ในคู่มือนี้ เราจะสร้างไปป์ไลน์อัตโนมัติที่บันทึกผลลัพธ์ของ Google ฟอร์ม เตรียมข้อมูลสำหรับการวิเคราะห์ด้วย Cloud Dataprep โหลดข้อมูลลงใน BigQuery และช่วยให้ทีมของคุณทำการวิเคราะห์ภาพโดยใช้เครื่องมืออย่าง Looker หรือ Data Studio ได้

สิ่งที่คุณจะสร้าง

ใน Codelab นี้ คุณจะใช้ Dataprep เพื่อเปลี่ยนรูปแบบคำตอบจากแบบสำรวจ Google ฟอร์มตัวอย่างของเราให้อยู่ในรูปแบบที่เป็นประโยชน์สำหรับการวิเคราะห์ข้อมูล คุณจะพุชข้อมูลที่แปลงแล้วไปยัง BigQuery ซึ่งคุณสามารถถามคำถามที่ลึกซึ้งยิ่งขึ้นด้วย SQL และรวมเข้ากับชุดข้อมูลอื่นๆ เพื่อการวิเคราะห์ที่มีประสิทธิภาพมากขึ้น ในตอนท้าย คุณสามารถสำรวจแดชบอร์ดที่สร้างไว้ล่วงหน้า หรือเชื่อมต่อเครื่องมือ Business Intelligence ของคุณเองกับ BigQuery เพื่อสร้างรายงานใหม่

สิ่งที่คุณจะได้เรียนรู้

  • วิธีเปลี่ยนรูปแบบข้อมูลแบบสำรวจโดยใช้ Dataprep
  • วิธีพุชข้อมูลแบบสำรวจไปยัง BigQuery
  • วิธีรับข้อมูลเชิงลึกเพิ่มเติมจากข้อมูลแบบสำรวจ

สิ่งที่คุณต้องมี

  • โปรเจ็กต์ Google Cloud ที่เปิดใช้การเรียกเก็บเงิน, BigQuery และ Dataprep
  • ความรู้พื้นฐานเกี่ยวกับ Dataprep จะเป็นประโยชน์ แต่ไม่จำเป็นต้องมี
  • ความรู้พื้นฐานเกี่ยวกับ BigQuery และ SQL จะเป็นประโยชน์ แต่ไม่จำเป็น

2. จัดการการตอบกลับแบบฟอร์มใน Google ฟอร์ม

เราจะเริ่มด้วยการดูการตอบกลับแบบฟอร์มใน Google ฟอร์มสำหรับแบบสำรวจตัวอย่างอย่างละเอียด

f3d25efd2cc923f5.png

คุณส่งออกผลการสำรวจได้จากแท็บ "คำตอบ" โดยคลิกไอคอน Google ชีต แล้วสร้างสเปรดชีตใหม่หรือโหลดผลการสำรวจลงในสเปรดชีตที่มีอยู่ Google ฟอร์มจะเพิ่มคำตอบลงในสเปรดชีตต่อไปเมื่อผู้ตอบส่งคำตอบ จนกว่าคุณจะยกเลิกการเลือกปุ่ม "ยอมรับคำตอบ"

d499e5a4dccdf5fd.png4939332a5d8f9f19.png

ตอนนี้เรามาดูคำตอบแต่ละประเภทและวิธีแปลคำตอบในไฟล์ Google ชีตกัน

3. เปลี่ยนรูปแบบคำตอบในแบบสำรวจ

คำถามในแบบสำรวจสามารถจัดกลุ่มได้เป็น 4 ตระกูลซึ่งจะมีรูปแบบการส่งออกที่เฉพาะเจาะจง คุณจะต้องปรับโครงสร้างข้อมูลในลักษณะที่เฉพาะเจาะจงโดยขึ้นอยู่กับประเภทของคำถาม ในส่วนนี้ เราจะตรวจสอบแต่ละกลุ่มและประเภทของการเปลี่ยนรูปแบบที่ต้องใช้

คำถามแบบเลือกได้คำตอบเดียว: คำตอบสั้นๆ, ย่อหน้า, เมนูแบบเลื่อนลง, สเกลเชิงเส้น ฯลฯ

  • ชื่อคำถาม: ชื่อคอลัมน์
  • คำตอบ: ค่าของเซลล์
  • ข้อกำหนดในการเปลี่ยนรูปแบบ: ไม่จำเป็นต้องเปลี่ยนรูปแบบ ระบบจะโหลดการตอบกลับตามที่เป็นอยู่

3eeedc50b0fd54fd.png

คำถามแบบหลายตัวเลือก: หลายตัวเลือก, ช่องทำเครื่องหมาย

  • ชื่อคำถาม: ชื่อคอลัมน์
  • คำตอบ: รายการค่าที่มีเครื่องหมายเซมิโคลอนเป็นตัวคั่น (เช่น "Resp 1; Resp 4; Resp 6")
  • ข้อกำหนดในการเปลี่ยนรูปแบบ: ต้องดึงข้อมูลและเปลี่ยนรูปแบบรายการค่า เพื่อให้คำตอบแต่ละรายการกลายเป็นแถวใหม่

cab8a38a96a13ce4.png

คำถามในตารางกริดแบบหลายตัวเลือก

ต่อไปนี้เป็นตัวอย่างคำถามแบบหลายตัวเลือก ผู้ใช้ต้องเลือกค่าเดียวจากแต่ละแถว

c6ea3d47d4dd5e78.png

  • ชื่อคำถาม: คำถามแต่ละข้อจะกลายเป็นชื่อคอลัมน์ที่มีรูปแบบ "คำถาม [ตัวเลือก]"
  • คำตอบ: คำตอบแต่ละรายการในตารางกริดจะกลายเป็นคอลัมน์ที่มีค่าไม่ซ้ำกัน
  • ข้อกำหนดในการเปลี่ยนรูปแบบ: คำถาม/คำตอบแต่ละรายการต้องกลายเป็นแถวใหม่ในตารางและแบ่งออกเป็น 2 คอลัมน์ คอลัมน์หนึ่งระบุตัวเลือกคำถาม และอีกคอลัมน์หนึ่งระบุคำตอบ

9223d0271516c58d.png

คำถามในตารางกริดแบบช่องทำเครื่องหมายหลายตัวเลือก

ตัวอย่างตารางกริดช่องทำเครื่องหมายมีดังนี้ ผู้ใช้สามารถเลือกค่าได้ตั้งแต่ 0 ถึงหลายค่าจากแต่ละแถว

4e3189b8cc2d4a8b.png

  • ชื่อคำถาม: คำถามแต่ละข้อจะกลายเป็นชื่อคอลัมน์ที่มีรูปแบบ "คำถาม [ตัวเลือก]"
  • คำตอบ: คำตอบแต่ละรายการในตารางกริดจะกลายเป็นคอลัมน์ที่มีรายการค่าที่คั่นด้วยเซมิโคลอน
  • ข้อกำหนดในการเปลี่ยนรูปแบบ: คำถามประเภทนี้จะรวมหมวดหมู่ "ช่องทำเครื่องหมาย" และ "ตารางกริดแบบหลายตัวเลือก" เข้าด้วยกัน และต้องได้รับการแก้ไขตามลำดับนี้

ก่อนอื่น คุณต้องดึงข้อมูลและเปลี่ยนรูปแบบรายการค่าของคำตอบแต่ละรายการ เพื่อให้คำตอบแต่ละรายการกลายเป็นแถวใหม่สำหรับคำถามนั้นๆ

2: คำตอบแต่ละรายการต้องกลายเป็นแถวใหม่ในตารางและแบ่งออกเป็น 2 คอลัมน์ คอลัมน์หนึ่งระบุตัวเลือกคำถาม และอีกคอลัมน์หนึ่งระบุคำตอบ

3c3c2bd098e03003.png

ต่อไปเราจะแสดงวิธีจัดการการเปลี่ยนรูปแบบเหล่านี้ด้วย Cloud Dataprep

4. สร้างโฟลว์ Cloud Dataprep

นำเข้า "รูปแบบการออกแบบการวิเคราะห์ Google ฟอร์ม" ใน Cloud Dataprep

ดาวน์โหลดแพ็กเกจโฟลว์รูปแบบการออกแบบ Google ฟอร์ม Analytics (โดยไม่ต้องแตกไฟล์) ในแอปพลิเคชัน Cloud Dataprep ให้คลิกไอคอนโฟลว์ในแถบนำทางด้านซ้าย จากนั้นในหน้าโฟลว์ ให้เลือก "นำเข้า" จากเมนูตามบริบท

ba7c0cb0eec398df.png

หลังจากนําเข้าโฟลว์แล้ว ให้เลือกโฟลว์ที่นําเข้าเพื่อแก้ไข หน้าจอควรมีลักษณะดังนี้

44978861eb34ec71.png

เชื่อมต่อสเปรดชีตผลการสำรวจของ Google ชีต

ทางด้านซ้ายของโฟลว์ คุณต้องเชื่อมต่อแหล่งข้อมูลกับ Google ชีตที่มีผลลัพธ์ของ Google ฟอร์มอีกครั้ง คลิกขวาที่ออบเจ็กต์ชุดข้อมูล Google ชีต แล้วเลือก "แทนที่"

55c16f0c04366f0c.png

จากนั้นคลิกลิงก์ "นำเข้าชุดข้อมูล" ที่ด้านล่างของโมดัล คลิกดินสอ "แก้ไขเส้นทาง"

8afeef260c96277f.png

จากนั้นแทนที่ค่าปัจจุบันด้วยลิงก์นี้ซึ่งชี้ไปยัง Google ชีตที่มีผลลัพธ์บางส่วนจาก Google ฟอร์ม โดยคุณจะใช้ตัวอย่างของเราหรือสำเนาของคุณเองก็ได้: https://docs.google.com/spreadsheets/d/1DgIlvlLceFDqWEJs91F8rt1B-X0PJGLY6shkKGBPWpk/edit?usp=sharing

คลิก "ไป" แล้วคลิก "นำเข้าและเพิ่มลงในโฟลว์" ที่ด้านขวาล่าง เมื่อกลับมาที่โมดัลแล้ว ให้คลิกปุ่ม "แทนที่" ที่ด้านขวาล่าง

เชื่อมต่อตาราง BigQuery

ทางด้านขวาของโฟลว์ คุณต้องเชื่อมต่อเอาต์พุตกับอินสแตนซ์ BigQuery ของคุณเอง สำหรับเอาต์พุตแต่ละรายการ ให้คลิกไอคอน แล้วแก้ไขพร็อพเพอร์ตี้ดังนี้

ก่อนอื่น ให้เริ่มด้วยการแก้ไข "ปลายทางที่กำหนดเอง"

a3fc2cb80153ec25.png

ในหน้าจอ "การตั้งค่าการเผยแพร่" ต่อไปนี้ ให้คลิกปุ่มแก้ไข

85791e6162a370de.png

เมื่อเห็นหน้าจอ "การดำเนินการเผยแพร่" คุณต้องเปลี่ยนการตั้งค่าการเชื่อมต่อโดยคลิกการเชื่อมต่อ BigQuery และแก้ไขพร็อพเพอร์ตี้

1f3e4887baaeaffd.png

เลือกชุดข้อมูล BigQuery ที่ต้องการโหลดผลลัพธ์ของ Google ฟอร์มเข้าไป คุณสามารถเลือก "ค่าเริ่มต้น" ได้หากยังไม่ได้สร้างชุดข้อมูล BigQuery

f4eaa05ecf9de162.png

หลังจากแก้ไข "ปลายทางที่กำหนดเอง" แล้ว ให้ดำเนินการในลักษณะเดียวกันสำหรับเอาต์พุต "ปลายทางที่กำหนดเวลาไว้"

46edea1b8ca63270.png

ทำซ้ำเอาต์พุตแต่ละรายการโดยทำตามขั้นตอนเดียวกัน คุณต้องแก้ไขปลายทางทั้งหมด 8 รายการ

5. คำอธิบายเกี่ยวกับโฟลว์ของ Cloud Dataprep

แนวคิดพื้นฐานของขั้นตอน "รูปแบบการออกแบบ Google ฟอร์ม Analytics" คือการแปลงคำตอบแบบสำรวจตามที่อธิบายไว้ก่อนหน้านี้ โดยแบ่งหมวดหมู่คำถามแต่ละหมวดหมู่ออกเป็นสูตรการแปลงข้อมูล Cloud Dataprep ที่เฉพาะเจาะจง

ขั้นตอนการทำงานนี้จะแบ่งคำถามออกเป็น 4 ตาราง (สอดคล้องกับหมวดหมู่คำถาม 4 หมวดหมู่เพื่อความเรียบง่าย)

afa421849b1bd398.png

เราขอแนะนำให้คุณสำรวจสูตรแต่ละสูตรทีละสูตร โดยเริ่มจาก "Clean Headers" จากนั้นจึงไปที่ "SingleChoiceSELECT-Questions" ตามด้วยสูตรอื่นๆ ที่อยู่ด้านล่าง

สูตรอาหารทั้งหมดจะมีการแสดงความคิดเห็นเพื่ออธิบายขั้นตอนการเปลี่ยนรูปแบบต่างๆ เมื่ออยู่ในสูตรอาหาร คุณสามารถแก้ไขขั้นตอนและดูตัวอย่างสถานะก่อน/หลังของคอลัมน์หนึ่งๆ ได้

449da06d96cd520e.png4ac6e14f578d0707.png

6. เรียกใช้โฟลว์ Cloud Dataprep

เมื่อกำหนดค่าแหล่งที่มาและปลายทางอย่างถูกต้องแล้ว คุณจะเรียกใช้โฟลว์เพื่อแปลงและโหลดคำตอบลงใน BigQuery ได้ เลือกเอาต์พุตแต่ละรายการ แล้วคลิกปุ่ม "เรียกใช้" หากมีตาราง BigQuery ที่ระบุอยู่แล้ว Dataprep จะเพิ่มแถวใหม่ ไม่เช่นนั้นจะสร้างตารางใหม่

47cf50f6d17a5b1e.png

คลิกไอคอน "ประวัติงาน" ในแผงด้านซ้ายเพื่อตรวจสอบงาน ระบบจะใช้เวลา 2-3 นาทีในการดำเนินการและโหลดตาราง BigQuery

afc79eeb27202fb4.png

เมื่องานทั้งหมดเสร็จสมบูรณ์แล้ว ระบบจะโหลดผลการสำรวจใน BigQuery ในรูปแบบที่สะอาด มีโครงสร้าง และปรับให้เป็นมาตรฐานพร้อมสำหรับการวิเคราะห์

7. วิเคราะห์ข้อมูลแบบสำรวจใน BigQuery

ใน Google Console สำหรับ 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. ทำการวิเคราะห์ภาพ

เมื่อข้อมูลแบบสำรวจรวมอยู่ในระบบคลังข้อมูลแล้ว คุณจะวิเคราะห์ข้อมูลในเครื่องมือ Business Intelligence ได้อย่างง่ายดาย เราได้สร้างรายงานตัวอย่างบางส่วนใน 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 Console ให้ไปที่จัดการทรัพยากร
  2. ในรายการโปรเจ็กต์ ให้เลือกโปรเจ็กต์ที่ต้องการลบ แล้วคลิกลบ
  3. ในกล่องโต้ตอบ ให้พิมพ์รหัสโปรเจ็กต์ แล้วคลิกปิดเพื่อลบโปรเจ็กต์