Преобразование и загрузка ответов на опросы Google Forms в BigQuery

Есть много причин для проведения опросов: оценить удовлетворенность клиентов, провести исследование рынка, улучшить продукт или услугу или оценить вовлеченность сотрудников. Однако, если вы пробовали работать с данными опроса, прежде чем вы, вероятно, знаете, что со стандартным форматом трудно работать. В этом руководстве мы создаем автоматизированный конвейер, который собирает результаты Google Forms, подготавливает данные для анализа с помощью Cloud Dataprep, загружает их в BigQuery и позволяет вашей команде выполнять визуальную аналитику с помощью таких инструментов, как Looker или Data Studio.

Что ты построишь

В этой кодовой лаборатории вы будете использовать Dataprep для преобразования ответов из нашего примера опроса Google Forms в формат, полезный для анализа данных. Вы отправите преобразованные данные в BigQuery, где сможете задавать более глубокие вопросы с помощью SQL и объединить их с другими наборами данных для более эффективного анализа. В конце вы можете изучить готовые информационные панели или подключить собственный инструмент бизнес-аналитики к BigQuery для создания новых отчетов.

Что ты узнаешь

  • Как преобразовать данные опроса с помощью Dataprep
  • Как отправить данные опроса в BigQuery
  • Как получить больше информации из данных опроса

Что вам понадобится

Мы начнем с более подробного изучения ответов Google Forms на наш пример опроса .

f3d25efd2cc923f5.png

Результаты опроса можно экспортировать из вкладки «Ответы», щелкнув значок Google Таблиц и создав новую электронную таблицу или загрузив результаты в существующую. Google Forms будет продолжать добавлять ответы в таблицу по мере того, как респонденты отправляют свои ответы, пока вы не отмените выбор кнопки «Принимать ответы».

d499e5a4dccdf5fd.png

4939332a5d8f9f19.png

Теперь давайте рассмотрим каждый тип ответа и то, как он переводится в файле Google Таблиц.

Вопросы анкеты можно сгруппировать в четыре семейства, которые будут иметь определенный формат экспорта. В зависимости от типа вопроса вам нужно будет определенным образом реструктурировать данные. Здесь мы рассмотрим каждую из групп и типы преобразований, которые нам нужно применить.

Вопросы с одним выбором: короткий ответ, абзац, раскрывающийся список, линейная шкала и т. Д.

  • Название вопроса : название столбца
  • Ответ : значение ячейки
  • Требования к трансформации : трансформация не требуется; ответ загружается как есть.

3eeedc50b0fd54fd.png

Вопросы с несколькими вариантами ответов: несколько вариантов, флажок

  • Название вопроса : название столбца
  • Ответ : список значений, разделенных точкой с запятой (например, «Resp 1; Resp 4; Resp 6»).
  • Требования к преобразованию : список значений необходимо извлечь и повернуть, чтобы каждый ответ превращался в новую строку.

cab8a38a96a13ce4.png

Вопросы по сетке с несколькими вариантами ответов

Вот пример вопроса с несколькими вариантами ответов. Необходимо выбрать одно значение из каждой строки.

c6ea3d47d4dd5e78.png

  • Название вопроса : каждый отдельный вопрос становится названием столбца в формате «Вопрос [Вариант]».
  • Ответ : каждый отдельный ответ в сетке становится столбцом с уникальным значением.
  • Требования к трансформации : каждый вопрос / ответ должен стать новой строкой в ​​таблице и разбит на две колонки. В одном столбце указан вариант вопроса, а в другом - ответ.

9223d0271516c58d.png

Вопросы о сетке флажка "Множественный выбор"

Вот пример сетки флажков. Можно выбрать от одного до нескольких значений из каждой строки.

4e3189b8cc2d4a8b.png

  • Название вопроса : каждый отдельный вопрос становится названием столбца в формате «Вопрос [Вариант]».
  • Ответ : каждый отдельный ответ в сетке становится столбцом со списком значений, разделенных точкой с запятой.
  • Требования к преобразованию : эти типы вопросов объединяют категории «флажок» и «сетка с несколькими вариантами ответов» и должны решаться в этом порядке.

Во-первых, необходимо извлечь и повернуть список значений каждого ответа, чтобы каждый ответ стал новой строкой для конкретного вопроса.

Во-вторых: каждый отдельный ответ должен стать новой строкой в ​​таблице и разбит на два столбца. В одном столбце указан вариант вопроса, а в другом - ответ.

3c3c2bd098e03003.png

Далее мы покажем, как эти преобразования обрабатываются с помощью Cloud Dataprep.

Импортируйте шаблон проектирования Google Forms Analytics в Cloud Dataprep.

Загрузите пакет потока шаблонов проектирования Google Forms Analytics (не распаковывая его). В приложении Cloud Dataprep щелкните значок Потоки на левой панели навигации. Затем на странице «Потоки» выберите «Импорт» в контекстном меню.

ba7c0cb0eec398df.png

После того, как вы импортировали поток, выберите импортированный поток, чтобы отредактировать его, ваш экран должен выглядеть следующим образом:

44978861eb34ec71.png

Таблица результатов опроса Connect Google Sheets

В левой части потока источник данных должен быть повторно подключен к Google Таблицам, содержащим результаты Google Forms. Щелкните правой кнопкой мыши объект наборов данных Google Sheet и выберите «Заменить».

55c16f0c04366f0c.png

Затем щелкните ссылку «Импортировать наборы данных» внизу модального окна. Щелкните карандашом «Изменить путь».

8afeef260c96277f.png

Оттуда замените текущее значение этой ссылкой, которая указывает на Google Таблицы с некоторыми результатами Google Forms, вы можете использовать наш пример или свою собственную копию: https://docs.google.com/spreadsheets/d/1DgIlvlLceFDqWEJs91F8rt1B-X0PJGLY6shkKGBPWpk / edit? usp = совместное использование

Нажмите «Перейти», а затем «Импортировать и добавить в Flow» в правом нижнем углу. Когда вы вернетесь в модальное окно, нажмите кнопку «Заменить» в правом нижнем углу.

Подключите таблицы BigQuery

В правой части потока вам нужно подключить выходы к вашему собственному экземпляру BigQuery. Для каждого из выходов щелкните значок, а затем отредактируйте его свойства следующим образом.

Сначала начните с редактирования "Назначения вручную"

a3fc2cb80153ec25.png

На следующем экране «Настройки публикации» нажмите кнопку редактирования.

85791e6162a370de.png

Когда вы видите экран «Действие публикации», вам нужно изменить настройки подключения, щелкнув подключение BigQuery и отредактировав его свойства.

1f3e4887baaeaffd.png

Выберите набор данных BigQuery, в который вы хотите загрузить результаты Google Forms. Вы можете выбрать вариант «по умолчанию», если вы еще не создали набор данных BigQuery.

f4eaa05ecf9de162.png

После того, как вы отредактировали «Назначения вручную», действуйте таким же образом для вывода «Назначения по расписанию».

46edea1b8ca63270.png

Выполните итерации по каждому выходу, выполнив те же действия. Всего вам нужно отредактировать 8 направлений.

Основная идея схемы «Шаблон проектирования Google Forms Analytics» заключается в выполнении преобразований ответов на опрос, как описано выше, путем разбиения каждой категории вопросов на определенный рецепт преобразования данных Cloud Dataprep.

В этом потоке вопросы разбиты на 4 таблицы (соответствующие 4 категориям вопросов для простоты).

afa421849b1bd398.png

Мы предлагаем вам изучить каждый из рецептов один за другим, начиная с «Очистить заголовки», а затем «SingleChoiceSELECT-Questions», а затем друг за другом рецепты.

Все рецепты снабжены комментариями для объяснения различных этапов преобразования. Находясь в рецепте, вы можете редактировать шаг и предварительно просматривать состояние до / после определенного столбца.

449da06d96cd520e.png

4ac6e14f578d0707.png

Теперь, когда ваш источник и места назначения правильно настроены, вы можете запустить поток для преобразования и загрузки ответов в BigQuery. Выберите каждый из выходов и нажмите кнопку «Выполнить». Если указанная таблица BigQuery существует, Dataprep добавит новые строки, в противном случае он создаст новую таблицу.

47cf50f6d17a5b1e.png

Щелкните значок «История заданий» на левой панели, чтобы отслеживать задания. Продолжение и загрузка таблиц BigQuery займет несколько минут.

afc79eeb27202fb4.png

Когда все задания будут завершены, результаты опроса будут загружены в 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

Теперь, когда данные ваших опросов централизованы в хранилище данных, вы можете легко анализировать данные с помощью инструмента бизнес-аналитики. Мы создали несколько примеров отчетов в Data Studio и Looker .

Смотритель

Если у вас уже есть экземпляр Looker, вы можете использовать LookML в этой папке, чтобы начать анализировать образец опроса и данные CRM для этого шаблона. Просто создайте новый проект Looker, добавьте LookML и замените имена соединений и таблиц в файле, чтобы они соответствовали вашей конфигурации BigQuery. Если у вас нет экземпляра Looker, но вы хотите узнать больше, вы можете запланировать демонстрацию здесь.

129db05d6f85f484.png

Студия данных

Кроме того, чтобы создать отчет в Студии данных , щелкните фрейм с крестиком Google «Пустой отчет» и подключитесь к BigQuery. Следуйте всем инструкциям Data Studio. Если вы хотите узнать больше, краткое руководство и введение в основные функции Data Studio можно найти здесь . Вы также можете найти здесь наши готовые информационные панели студии данных.

5e744869e3fe3f8f.png

Самый простой способ отказаться от выставления счетов - это удалить облачный проект, созданный вами для учебника. Кроме того, вы можете удалить отдельные ресурсы.

  1. В Cloud Console перейдите к управлению ресурсами
  2. В списке проектов выберите проект, который вы хотите удалить, и нажмите « Удалить» .
  3. В диалоговом окне введите идентификатор проекта и нажмите « Завершение работы», чтобы удалить проект.