Transformar e carregar as respostas das pesquisas dos Formulários Google no BigQuery

1. Introdução

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

O que você vai criar

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

O que você vai aprender

  • Como transformar dados de pesquisas usando o Dataprep
  • Como enviar dados de pesquisa para o BigQuery
  • Como extrair mais insights dos dados da pesquisa

O que é necessário

  • Um projeto na nuvem do Google Cloud com o faturamento, o BigQuery e o Dataprep ativados.
  • É útil ter um conhecimento básico do Dataprep, mas não é obrigatório.
  • Conhecimentos básicos de BigQuery e SQL são recomendáveis, mas não obrigatórios.

2. Gerenciar respostas dos Formulários Google

Vamos começar analisando as respostas do Google Formulários à nossa pesquisa de exemplo.

f3d25efd2cc923f5.png

Para exportar os resultados da pesquisa, clique no ícone do Google Planilhas na guia "Respostas" e crie uma nova planilha ou carregue os resultados em uma que já existe. O Google Formulários vai continuar adicionando respostas à planilha até que você desmarque o botão "Aceitando respostas".

d499e5a4dccdf5fd.png4939332a5d8f9f19.png

Agora vamos analisar cada tipo de resposta e como ela é traduzida no arquivo do Google Planilhas.

3. Transformar respostas da pesquisa

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

Perguntas de escolha única: resposta curta, parágrafo, menu suspenso, 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 como está.

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 com grade de múltipla escolha

Veja um exemplo de pergunta de múltipla escolha. É preciso selecionar um único valor em cada linha.

c6ea3d47d4dd5e78.png

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

9223d0271516c58d.png

Perguntas de múltipla escolha com grade de caixas de seleção

Veja um exemplo de grade de caixas de seleção. É possível selecionar de nenhum a vários valores em cada linha.

4e3189b8cc2d4a8b.png

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

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

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

3c3c2bd098e03003.png

Em seguida, vamos mostrar como essas transformações são processadas com o Cloud Dataprep.

4. Criar o fluxo do Cloud Dataprep

Importar o "Padrão de design de análise do Google Formulários" no Cloud Dataprep

Faça o download do pacote de fluxo Padrão de design do Google Forms Analytics (sem descompactar). No aplicativo Cloud Dataprep, clique no ícone "Fluxos" 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 para edição. Sua tela vai ficar assim:

44978861eb34ec71.png

Conectar a planilha de resultados da pesquisa do Google Planilhas

No lado esquerdo do fluxo, a fonte de dados precisa ser reconectada a um arquivo do Google Planilhas que contenha os resultados do Google Formulários. Clique com o botão direito do mouse no objeto de conjuntos de dados do Google Planilhas e selecione "Substituir".

55c16f0c04366f0c.png

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

8afeef260c96277f.png

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

Clique em "Go" e em "Import & Add to Flow" no canto inferior direito. Quando voltar ao modal, clique no botão "Substituir" no canto inferior direito.

Conectar tabelas do BigQuery

No lado direito do fluxo, é preciso conectar as saídas à sua própria instância do BigQuery. Para cada uma das saídas, clique no ícone e edite as propriedades da seguinte maneira.

Primeiro, comece editando os "Destinos manuais"

a3fc2cb80153ec25.png

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

85791e6162a370de.png

Quando a tela "Ação de publicação" aparecer, mude as configurações de conexão clicando na conexão do BigQuery e editando as propriedades dela.

1f3e4887baaeaffd.png

Selecione o conjunto de dados do BigQuery em que você quer carregar os resultados do Google Formulários. Selecione "padrão" se você ainda não tiver criado um conjunto de dados do BigQuery.

f4eaa05ecf9de162.png

Depois de editar os "Destinos manuais", faça o mesmo para a saída "Destinos programados".

46edea1b8ca63270.png

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

5. Explicação do fluxo do Cloud Dataprep

A ideia básica do fluxo "Padrão de design de análise do Google Formulários" é realizar as transformações nas respostas da pesquisa conforme descrito anteriormente, dividindo cada categoria de pergunta em uma receita específica de transformação de dados do Cloud Dataprep.

Esse fluxo divide as perguntas em quatro tabelas (correspondentes às quatro categorias de perguntas, para fins de simplicidade)

afa421849b1bd398.png

Sugerimos que você explore cada uma das receitas, uma por uma, começando com "Clean Headers" e depois "SingleChoiceSELECT-Questions", seguidas pelas outras receitas abaixo.

Todas as receitas são comentadas para explicar as várias etapas de transformação. Em uma receita, é possível editar uma etapa e visualizar o estado antes/depois de uma coluna específica.

449da06d96cd520e.png4ac6e14f578d0707.png

6. Executar o fluxo do Cloud Dataprep

Agora que a origem e os 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 do BigQuery especificada existir, o Dataprep vai anexar novas linhas. Caso contrário, ele vai criar uma nova tabela.

47cf50f6d17a5b1e.png

Clique no ícone "Histórico de jobs" no painel à esquerda para monitorar os jobs. Aguarde alguns minutos para que as tabelas do BigQuery sejam carregadas.

afc79eeb27202fb4.png

Quando todos os jobs forem concluídos, os resultados da pesquisa serão carregados no BigQuery em um formato limpo, estruturado e normalizado, prontos para análise.

7. Analisar os dados da pesquisa no BigQuery

No Console do Google para BigQuery, você pode ver os detalhes de cada uma das novas tabelas.

df370873572511ac.png

Com os dados da pesquisa no BigQuery, é fácil fazer perguntas de avaliação mais abrangentes para entender as respostas em um nível mais profundo. Por exemplo, digamos que você queira entender qual linguagem de programação é mais usada com frequência por pessoas com diferentes cargos profissionais. É possível 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 eficientes, é possível juntar as respostas da pesquisa aos dados do CRM para ver se os participantes estão associados a contas que já estão incluídas no seu data warehouse. Isso pode ajudar sua empresa a tomar decisões mais fundamentadas sobre o suporte ao cliente ou a segmentação de usuários para novos lançamentos.

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

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. Realizar análises visuais

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

Looker

Se você já tiver uma instância do Looker, use o LookML nesta pasta para começar a analisar os dados de amostra da pesquisa e do CRM para esse padrão. Basta criar um novo projeto do Looker, adicionar o LookML e substituir os nomes de conexão e tabela no arquivo para que correspondam à sua configuração do BigQuery. Se você não tiver uma instância do Looker, mas quiser saber mais, agende uma demonstração aqui.

129db05d6f85f484.png

Data Studio

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

5e744869e3fe3f8f.png

9. Como fazer a limpeza

A maneira mais fácil de eliminar o faturamento é excluir o projeto na nuvem que você criou para o tutorial. A outra opção é excluir os recursos individuais.

  1. No console do Cloud, acesse Gerenciar recursos.
  2. Na lista de projetos, selecione o projeto que você quer excluir e clique em Excluir.
  3. Na caixa de diálogo, digite o ID do projeto e clique em Encerrar para excluí-lo.