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

1. Introdução

Há muitos motivos para fazer pesquisas: avaliar a satisfação do cliente, realizar pesquisas de mercado, melhorar um produto ou serviço ou avaliar o engajamento dos funcionários. No entanto, se você já trabalhou com dados de pesquisa, provavelmente sabe que o formato padrão é difícil de usar. Neste guia, criamos um pipeline automatizado que captura os resultados do Google Formulários, prepara os dados para análise com o Cloud Dataprep, carrega-os no BigQuery e permite que sua equipe realize 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 Formulários Google 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 combiná-los com outros conjuntos de dados para análises mais avançadas. No final, você poderá explorar painéis pré-criados 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 o Dataprep
• Como enviar dados de pesquisa para o BigQuery
• Como extrair mais insights dos dados de pesquisa

O que é necessário

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

2. Gerenciar respostas do Google Formulários

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

Os resultados da pesquisa podem ser exportados da guia "Respostas" clicando no ícone do Google Planilhas e criando uma nova planilha ou carregando os resultados em uma planilha já existente. O Google Formulários vai continuar adicionando respostas à planilha à medida que os participantes enviarem as respostas até que você desmarque o botão "Aceitar respostas".

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. Com base no tipo de pergunta, você 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, 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 como está.

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.

Perguntas de grade de múltipla escolha

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

• Nome da pergunta: cada pergunta individual se torna um nome de coluna com este 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 mencionando a opção da pergunta e a outra coluna com a resposta.

Perguntas de grade de caixa de seleção de múltipla escolha

Confira um exemplo de grade de caixa de seleção. É possível selecionar nenhum ou vários valores de cada linha.

• Nome da pergunta: cada pergunta individual se torna um nome de coluna com este 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 perguntas combinam as categorias "caixa de seleção" e "grade de múltipla escolha" e precisam ser resolvidas 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 mencionando a opção da pergunta e a outra coluna com a resposta.

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 do padrão de design de análise do Formulários Google (sem descompactá-lo). 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.

Depois de importar o fluxo, selecione-o para editar. A tela vai ficar assim:

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 Planilhas Google e selecione "Substituir".

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

Em seguida, substitua o valor atual por este link que aponta para uma planilha do Google 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 "Acessar" e em "Importar e adicionar ao fluxo" no canto inferior direito. Quando você voltar ao modal, clique no botão "Substituir" no canto inferior direito.

Conectar tabelas do BigQuery

No lado direito do fluxo, é necessário 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, edite os "Destinos manuais".

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

Quando a tela "Ação de publicação" aparecer, clique na conexão do BigQuery e edite as propriedades dela para mudar as configurações de conexão.

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

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

Os destinos manuais permitem executar cada job de saída individual de forma independente e sob demanda. Isso é conveniente para testar uma receita de preparação de dados e validar um resultado durante o design do fluxo.

Os destinos programados são usados quando você quer executar o fluxo de forma rotineira. Por exemplo, talvez você queira recarregar os resultados do Google Formulários no BigQuery todos os dias para receber uma atualização dos relatórios com as respostas mais recentes.

Repita cada saída seguindo as mesmas etapas. No total, você precisa editar oito destinos.

5. Fluxo do Cloud Dataprep explicado

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 um roteiro de transformação de dados específico do Cloud Dataprep.

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

Sugerimos que você explore cada um dos roteiros um por um, começando com "Limpar cabeçalhos" e "SingleChoiceSELECT-Questions", seguida de cada um dos outros roteiros 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.

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.

Clique no ícone "Histórico de jobs" no painel esquerdo para monitorar os jobs. Isso leva alguns minutos para continuar e carregar as tabelas do BigQuery.

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

7. Analisar os dados da pesquisa no BigQuery

No console do Google para BigQuery, você poderá conferir os detalhes de cada uma das novas tabelas.

Com os dados da pesquisa no BigQuery, é possível 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 usada com frequência por pessoas de diferentes cargos 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 combinar as respostas da pesquisa com os dados do CRM para saber se os participantes são mapeados para alguma conta já incluída no data warehouse. Isso pode ajudar sua empresa a tomar decisões mais informadas sobre o suporte ao cliente ou a segmentação de usuários para novos lançamentos.

Aqui, mostramos como combinar os dados da pesquisa em uma tabela de contas com base no domínio do respondente e no site da conta. Agora, é possível conferir 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

Observação: se você quiser executar essa consulta, use nossa tabela de exemplo de conta do Salesforce e adicione-a ao BigQuery usando estas instruções.

8. Realizar análises visuais

Agora que os dados da pesquisa estão centralizados em um data warehouse, é possível analisar os dados em uma ferramenta de Business Intelligence. Criamos alguns relatórios de exemplo no Data Studio e no Looker.

Looker

Se você já tiver uma instância do Looker, poderá usar o LookML nesta pasta para começar a analisar os dados de pesquisa e CRM de exemplo 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 corresponder à configuração do BigQuery. Se você não tiver uma instância do Looker, mas quiser saber mais, você pode agendar uma demonstração aqui.

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 aqui. Você também pode encontrar nossos painéis do Data Studio pré-criados aqui.

9. Como fazer a limpeza

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

Cuidado: excluir um projeto tem os seguintes efeitos:

• Tudo no projeto é excluído. Se você tiver usado um projeto atual para seguir este tutorial, a exclusão dele incluirá quaisquer outras ações feitas no projeto.
• Os IDs do projeto personalizados são perdidos. Ao criar o projeto, você pode ter criado um ID do projeto personalizado para ser usado no futuro. Para preservar os URLs que usam o ID do projeto, como um URL appspot.com, exclua os recursos selecionados dentro do projeto em vez de excluir o projeto inteiro.

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 Desligar para excluir o projeto.