Преобразование и загрузка ответов опросов 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 на наш пример опроса .

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

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

3. Преобразуйте ответы опроса

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

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

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

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

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

Вопросы с множественным выбором ответов

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

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

Вопросы в виде сетки флажков с множественным выбором

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

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

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

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

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

4. Создайте процесс подготовки облачных данных

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

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

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

Подключите таблицу результатов опроса Google Sheets

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

Затем нажмите ссылку «Импортировать наборы данных» в нижней части модального окна. Нажмите на карандаш «Редактировать путь».

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

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

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

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

Сначала начните с редактирования «Назначений вручную».

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

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

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

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

Назначение вручную позволяет запускать каждое отдельное задание вывода независимо и по требованию. Это удобно для тестирования рецепта подготовки данных и проверки результата во время разработки вашего потока.

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

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

5. Объяснение процесса подготовки облачных данных

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

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

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

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

6. Запустите процесс подготовки облачных данных.

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

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

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

7. Анализируйте данные опроса в BigQuery.

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

Используя данные опроса в 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

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

8. Выполните визуальную аналитику

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

смотрящий

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

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

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

9. Уборка

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

Внимание : Удаление проекта имеет следующие последствия:

• Все в проекте удалено . Если для этого руководства вы использовали существующий проект, то при его удалении вы также удаляете всю другую работу, выполненную в проекте.
• Пользовательские идентификаторы проектов теряются . Создавая этот проект, вы, возможно, создали собственный идентификатор проекта, который хотите использовать в будущем. Чтобы сохранить URL-адреса, использующие идентификатор проекта, например URL-адрес appspot.com , удалите выбранные ресурсы внутри проекта, а не удаляйте весь проект.

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