Transforme e carregue as respostas da pesquisa de formulários do Google no BigQuery

Há muitos motivos para fazer pesquisas: avaliar a satisfação do cliente, fazer pesquisas de mercado, melhorar um produto ou serviço ou avaliar o envolvimento dos funcionários. No entanto, se você já tentou trabalhar com dados de pesquisa antes, provavelmente sabe que é difícil trabalhar com o formato padrão. Neste guia, construímos um pipeline automatizado que captura os resultados do Formulários Google, prepara os dados para análise com o Cloud Dataprep, carrega-os no BigQuery e permite que sua equipe execute análises visuais usando ferramentas como Looker ou Data Studio.

O que você vai construir

Neste codelab, você usará o Dataprep para transformar as respostas de nossa pesquisa de exemplo do Formulários Google em um formato útil para análise de dados. Você enviará os dados transformados para o BigQuery, onde poderá fazer perguntas mais profundas com SQL e juntá-los a outros conjuntos de dados para análises mais poderosas. No final, você pode explorar painéis pré-construídos ou conectar sua própria ferramenta de business intelligence ao BigQuery para criar novos relatórios.

O que você aprenderá

  • Como transformar dados de pesquisa usando Dataprep
  • Como enviar dados de pesquisa para o BigQuery
  • Como obter mais informações dos dados da pesquisa

O que você precisará

  • Um projeto do Google Cloud com faturamento, BigQuery e Dataprep ativados
  • Um conhecimento básico de Dataprep é útil, mas não obrigatório
  • Um conhecimento básico de BigQuery e SQL é útil, mas não obrigatório

Começaremos examinando mais de perto as respostas dos Formulários Google ao nosso exemplo de pesquisa .

f3d25efd2cc923f5.png

Os resultados da pesquisa podem ser exportados da guia "respostas" clicando no ícone do Planilhas Google e criando uma nova planilha ou carregando os resultados em uma existente. O Formulários Google continuará adicionando respostas à planilha conforme os respondentes enviam suas respostas até que você desmarque o botão "Aceitando respostas".

d499e5a4dccdf5fd.png

4939332a5d8f9f19.png

Agora, vamos revisar cada tipo de resposta e como isso se traduz no arquivo do Planilhas Google.

As perguntas da pesquisa podem ser agrupadas em quatro famílias que terão um formato de exportação específico. Com base no tipo de pergunta, você precisará reestruturar os dados de uma determinada maneira. Aqui, revisamos cada um dos grupos e os tipos de transformações que precisamos aplicar.

Perguntas de escolha única: resposta curta, parágrafo, lista suspensa, escala linear, etc.

  • Nome da pergunta : nome da coluna
  • Resposta : valor da célula
  • Requisitos de transformação : nenhuma transformação é necessária; a resposta é carregada no estado em que se encontra.

3eeedc50b0fd54fd.png

Perguntas de múltipla escolha: múltipla escolha, caixa de seleção

  • Nome da pergunta : nome da coluna
  • Resposta : lista de valores com separador de ponto e vírgula (por exemplo, "Resp 1; Resp 4; Resp 6")
  • Requisitos de transformação : a lista de valores precisa ser extraída e dinamizada, para que cada resposta se torne uma nova linha.

cab8a38a96a13ce4.png

Perguntas da grade de múltiplas escolhas

Aqui está um exemplo de uma questão de múltipla escolha. Deve-se selecionar um único valor de cada linha.

c6ea3d47d4dd5e78.png

  • Nome da pergunta : cada pergunta individual se torna um nome de coluna com este formato "Pergunta [Opção]".
  • Resposta : cada resposta individual na grade torna-se uma coluna com um valor único.
  • Requisitos de transformação : cada pergunta / resposta deve se tornar uma nova linha na tabela e dividida em duas colunas. Uma coluna mencionando a opção de pergunta e a outra coluna com a resposta.

9223d0271516c58d.png

Perguntas sobre a grade da caixa de seleção de múltiplas escolhas

Aqui está um exemplo de uma grade de caixa de seleção. Pode-se selecionar nenhum para vários valores de cada linha.

4e3189b8cc2d4a8b.png

  • Nome da pergunta : cada pergunta individual se torna um nome de coluna com este formato "Pergunta [Opção]".
  • Resposta : cada resposta individual na grade torna-se uma coluna com uma lista de valores separados por ponto e vírgula.
  • Requisitos de transformação : esses tipos de perguntas combinam as categorias "caixa de seleção" e "Grade de escolhas múltiplas" e devem ser resolvidos nesta ordem.

Primeiro, a lista de valores de cada resposta precisa ser extraída e dinamizada, de modo que cada resposta se torne uma nova linha para a pergunta específica.

Segundo: cada resposta individual deve se tornar uma nova linha na tabela e dividida em duas colunas. Uma coluna mencionando a opção da pergunta e a outra coluna com a resposta.

3c3c2bd098e03003.png

A seguir, mostraremos como essas transformações são tratadas com o Cloud Dataprep.

Importe o "Google Forms Analytics Design Pattern" no Cloud Dataprep

Baixe o pacote de fluxo do Google Forms Analytics Design Pattern (sem descompactá-lo). No aplicativo Cloud Dataprep , clique no ícone Flows na barra de navegação esquerda. Em seguida, na página Fluxos, selecione Importar no menu de contexto.

ba7c0cb0eec398df.png

Depois de importar o fluxo, selecione o fluxo importado para editá-lo, sua tela deve ficar assim:

44978861eb34ec71.png

Planilha de resultados da pesquisa do Connect Google Sheets

No lado esquerdo do fluxo, a fonte de dados deve ser reconectada a um Planilhas Google contendo os resultados do Formulários Google. Clique com o botão direito no objeto dos conjuntos de dados da Planilha Google e selecione "Substituir".

55c16f0c04366f0c.png

Em seguida, clique no link "Importar conjuntos de dados" na parte inferior do modal. Clique no lápis "Editar caminho".

8afeef260c96277f.png

A partir daí, substitua o valor atual por este link que aponta para um Planilhas Google por alguns resultados do Formulários Google, você pode usar nosso exemplo ou sua própria cópia: https://docs.google.com/spreadsheets/d/1DgIlvlLceFDqWEJs91F8rt1B-X0PJGLY6shkKGBPWpk / edit? usp = compartilhamento

Clique em "Ir" e em "Importar e adicionar ao fluxo" no canto inferior direito. Quando você estiver de volta ao modal, clique no botão "Substituir" no canto inferior direito.

Conectar tabelas do BigQuery

No lado direito do fluxo, você precisa conectar as saídas à sua própria instância do BigQuery. Para cada uma das saídas, clique no ícone e edite suas propriedades conforme a seguir.

Primeiro, comece editando os "Destinos manuais"

a3fc2cb80153ec25.png

Na tela "Configurações de publicação" a seguir, clique no botão de edição

85791e6162a370de.png

Ao ver a tela "Publishing Action", você precisa alterar as configurações de conexão clicando na conexão do BigQuery e editando suas propriedades.

1f3e4887baaeaffd.png

Selecione o conjunto de dados do BigQuery em que deseja que os resultados do Formulários Google sejam carregados. Você pode selecionar "padrão" se ainda não criou nenhum conjunto de dados do BigQuery.

f4eaa05ecf9de162.png

Depois de editar os "Destinos manuais", proceda da mesma forma para a saída "Destinos programados".

46edea1b8ca63270.png

Repita em cada saída seguindo as mesmas etapas. No total, você deve editar 8 destinos.

A ideia básica do fluxo "Google Forms Analytics Design Pattern" é realizar as transformações nas respostas da pesquisa conforme descrito anteriormente - dividindo cada categoria de pergunta em uma receita de transformação de dados do Cloud Dataprep específica.

Este fluxo divide as questões em 4 tabelas (correspondendo às 4 categorias de questões, para fins de simplicidade)

afa421849b1bd398.png

Sugerimos que você explore cada uma das receitas, uma a uma, começando com "Limpar cabeçalhos" e depois com "SingleChoiceSELECT-Questions", seguidas umas das outras receitas abaixo.

Todas as receitas são comentadas para explicar as várias etapas de transformação. Quando estiver em uma receita, você pode editar uma etapa e visualizar o estado antes / depois de uma coluna específica.

449da06d96cd520e.png

4ac6e14f578d0707.png

Agora que sua origem e destinos estão configurados corretamente, você pode executar o fluxo para transformar e carregar as respostas no BigQuery. Selecione cada uma das saídas e clique no botão "Executar". Se a tabela especificada do BigQuery existir, o Dataprep acrescentará novas linhas, caso contrário, criará uma nova tabela.

47cf50f6d17a5b1e.png

Clique no ícone "histórico de trabalhos" no painel esquerdo para monitorar os trabalhos. Deve levar alguns minutos para prosseguir e carregar as tabelas do BigQuery.

afc79eeb27202fb4.png

Quando todas as tarefas forem concluídas, os resultados da pesquisa serão carregados no BigQuery em um formato limpo, estruturado e normalizado pronto para análise.

No Console do Google para BigQuery , você deve ser capaz de ver os detalhes de cada uma das novas tabelas

df370873572511ac.png

Com os dados da pesquisa no BigQuery, você pode facilmente fazer perguntas mais abrangentes para entender as respostas da pesquisa em um nível mais profundo. Por exemplo, digamos que você esteja tentando entender qual linguagem de programação é mais comumente usada por pessoas com diferentes títulos profissionais - você pode escrever uma consulta como esta:

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

Para tornar suas análises ainda mais poderosas, você pode juntar as respostas da pesquisa aos dados de CRM para ver se os participantes mapeiam para alguma conta que já está incluída em seu data warehouse. Isso pode ajudar sua empresa a tomar decisões mais informadas sobre suporte ao cliente ou direcionar usuários para novos lançamentos.

Aqui, mostramos como você pode juntar os dados da pesquisa em uma tabela de conta com base no domínio do respondente e no site da conta. Agora, você pode ver a distribuição das respostas por tipo de conta, o que ajuda a entender quantos respondentes pertencem a contas de clientes existentes.

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

Agora que seus dados de pesquisa estão centralizados em um data warehouse, você pode analisá-los facilmente em uma ferramenta de business intelligence. Criamos alguns relatórios de exemplo no Data Studio e no Looker .

Looker

Se você já tem uma instância do Looker, pode usar o LookML nesta pasta para começar a analisar a pesquisa de amostra e os dados de CRM para este padrão. Basta criar um novo projeto Looker, adicionar LookML e substituir os nomes de conexão e tabela no arquivo para corresponder à sua configuração do BigQuery. Se você não tem uma instância do Looker, mas está interessado em aprender mais, pode agendar uma demonstração aqui.

129db05d6f85f484.png

Data Studio

Como alternativa, para criar um relatório no Data Studio , clique no quadro com a cruz do Google "Relatório em branco" e conecte-se ao BigQuery. Siga todas as instruções do Data Studio. Se você quiser saber mais, um início rápido e uma introdução aos principais recursos do Data Studio podem ser encontrados aqui . Você também pode encontrar nossos painéis de data studio pré-construídos aqui.

5e744869e3fe3f8f.png

A maneira mais fácil de eliminar o faturamento é excluir o projeto em nuvem que você criou para o tutorial. Como alternativa, você pode excluir os recursos individuais.

  1. No Cloud Console, vá para gerenciar recursos
  2. Na lista de projetos, selecione o projeto que deseja excluir e clique em Excluir .
  3. Na caixa de diálogo, digite a ID do projeto e clique em Encerrar para excluí-lo.