Transforma y carga las respuestas de las encuestas de Formularios de Google en BigQuery

1. Introducción

Existen muchos motivos para realizar encuestas: evaluar la satisfacción del cliente, realizar investigaciones de mercado, mejorar un producto o servicio, o evaluar la participación de los empleados. Sin embargo, si ya intentaste trabajar con datos de encuestas, probablemente sepas que el formato estándar es difícil de usar. En esta guía, crearemos una canalización automatizada que capture los resultados de Formularios de Google, prepare los datos para el análisis con Cloud Dataprep, los cargue en BigQuery y permita que tu equipo realice análisis visuales con herramientas como Looker o Data Studio.

Qué compilarás

En este codelab, usarás Dataprep para transformar las respuestas de nuestra encuesta de ejemplo de Formularios de Google en un formato que sea útil para el análisis de datos. Enviarás los datos transformados a BigQuery, donde podrás hacer preguntas más detalladas con SQL y unirlos a otros conjuntos de datos para realizar análisis más potentes. Al final, puedes explorar paneles prediseñados o conectar tu propia herramienta de inteligencia empresarial a BigQuery para crear informes nuevos.

Qué aprenderás

  • Cómo transformar datos de encuestas con Dataprep
  • Cómo enviar datos de encuestas a BigQuery
  • Cómo obtener más información de los datos de la encuesta

Requisitos

  • Un proyecto de Google Cloud con la facturación, BigQuery y Dataprep habilitados
  • Es útil tener conocimientos básicos de Dataprep, pero no es obligatorio.
  • Es útil tener conocimientos básicos de BigQuery y SQL, pero no es obligatorio.

2. Administra las respuestas de Formularios de Google

Comenzaremos analizando más de cerca las respuestas de Formularios de Google a nuestra encuesta de ejemplo.

f3d25efd2cc923f5.png

Los resultados de la encuesta se pueden exportar desde la pestaña "Respuestas". Para ello, haz clic en el ícono de Hojas de cálculo de Google y crea una hoja de cálculo nueva o carga los resultados en una existente. Formularios de Google seguirá agregando respuestas a la hoja de cálculo a medida que los encuestados las envíen hasta que deselecciones el botón "Se aceptan respuestas".

d499e5a4dccdf5fd.png4939332a5d8f9f19.png

Ahora, revisemos cada tipo de respuesta y cómo se traduce en el archivo de Hojas de cálculo de Google.

3. Transforma las respuestas de la encuesta

Las preguntas de la encuesta se pueden agrupar en cuatro familias que tendrán un formato de exportación particular. Según el tipo de pregunta, deberás reestructurar los datos de una manera determinada. Aquí, revisamos cada uno de los grupos y los tipos de transformaciones que debemos aplicar.

Preguntas de opción única: respuesta corta, párrafo, menú desplegable, escala lineal, etcétera

  • Nombre de la pregunta: nombre de la columna
  • Respuesta: valor de la celda
  • Requisitos de transformación: No se necesita ninguna transformación; la respuesta se carga tal como está.

3eeedc50b0fd54fd.png

Preguntas de opción múltiple: opción múltiple, casilla de verificación

  • Nombre de la pregunta: nombre de la columna
  • Respuesta: Lista de valores separados por punto y coma (p.ej., "Resp 1; Resp 4; Resp 6")
  • Requisitos de transformación: Se debe extraer la lista de valores y dinamizarla para que cada respuesta se convierta en una fila nueva.

cab8a38a96a13ce4.png

Preguntas de cuadrícula de opción múltiple

Este es un ejemplo de una pregunta de opción múltiple. Se debe seleccionar un solo valor de cada fila.

c6ea3d47d4dd5e78.png

  • Nombre de la pregunta: Cada pregunta individual se convierte en un nombre de columna con el formato "Pregunta [Opción]".
  • Respuesta: Cada respuesta individual de la cuadrícula se convierte en una columna con un valor único.
  • Requisitos de transformación: Cada pregunta y respuesta debe convertirse en una fila nueva de la tabla y dividirse en dos columnas. Una columna que mencione la opción de la pregunta y la otra con la respuesta.

9223d0271516c58d.png

Preguntas de cuadrícula con casillas de verificación de opción múltiple

Este es un ejemplo de una cuadrícula de casillas de verificación. Se puede seleccionar desde ninguno hasta varios valores de cada fila.

4e3189b8cc2d4a8b.png

  • Nombre de la pregunta: Cada pregunta individual se convierte en un nombre de columna con el formato "Pregunta [Opción]".
  • Respuesta: Cada respuesta individual de la cuadrícula se convierte en una columna con una lista de valores separados por punto y coma.
  • Requisitos de transformación: Estos tipos de preguntas combinan las categorías "casilla de verificación" y "cuadrícula de opción múltiple", y se deben resolver en este orden.

Primero, se debe extraer y dinamizar la lista de valores de cada respuesta, de modo que cada respuesta se convierta en una fila nueva para la pregunta en particular.

Segundo: Cada respuesta individual debe convertirse en una fila nueva de la tabla y dividirse en dos columnas. Una columna con la opción de la pregunta y la otra con la respuesta

3c3c2bd098e03003.png

A continuación, mostraremos cómo se manejan estas transformaciones con Cloud Dataprep.

4. Compila el flujo de Cloud Dataprep

Importa el "patrón de diseño de estadísticas de Formularios de Google" en Cloud Dataprep

Descarga el paquete de flujo Google Forms Analytics Design Pattern (sin descomprimirlo). En la aplicación de Cloud Dataprep, haz clic en el ícono de flujos en la barra de navegación izquierda. Luego, en la página Flows, selecciona Importar en el menú contextual.

ba7c0cb0eec398df.png

Después de importar el flujo, selecciónalo para editarlo. Tu pantalla debería verse de la siguiente manera:

44978861eb34ec71.png

Conecta la hoja de cálculo de resultados de la encuesta de Hojas de cálculo de Google

En el lado izquierdo del flujo, la fuente de datos se debe volver a conectar a una hoja de cálculo de Google que contenga los resultados de Formularios de Google. Haz clic con el botón derecho en el objeto de conjuntos de datos de Hojas de cálculo de Google y selecciona "Reemplazar".

55c16f0c04366f0c.png

Luego, haz clic en el vínculo "Importar conjuntos de datos" en la parte inferior del modal. Haz clic en el lápiz "Editar ruta".

8afeef260c96277f.png

Desde allí, reemplaza el valor actual por este vínculo que dirige a una hoja de cálculo de Google con algunos resultados de Formularios de Google. Puedes usar nuestro ejemplo o tu propia copia: https://docs.google.com/spreadsheets/d/1DgIlvlLceFDqWEJs91F8rt1B-X0PJGLY6shkKGBPWpk/edit?usp=sharing

Haz clic en "Go" y, luego, en "Import & Add to Flow" en la parte inferior derecha. Cuando vuelvas al cuadro modal, haz clic en el botón "Reemplazar" en la parte inferior derecha.

Conecta tablas de BigQuery

En el lado derecho del flujo, debes conectar los resultados a tu propia instancia de BigQuery. Para cada uno de los resultados, haz clic en el ícono y, luego, edita sus propiedades de la siguiente manera.

Primero, comienza por editar los "Destinos manuales".

a3fc2cb80153ec25.png

En la siguiente pantalla, "Configuración de publicación", haz clic en el botón de edición.

85791e6162a370de.png

Cuando veas la pantalla "Publishing Action", deberás cambiar la configuración de la conexión. Para ello, haz clic en la conexión de BigQuery y edita sus propiedades.

1f3e4887baaeaffd.png

Selecciona el conjunto de datos de BigQuery en el que quieres que se carguen los resultados de Formularios de Google. Puedes seleccionar "predeterminado" si aún no creaste ningún conjunto de datos de BigQuery.

f4eaa05ecf9de162.png

Después de editar los "Destinos manuales", procede de la misma manera para el resultado de "Destinos programados".

46edea1b8ca63270.png

Itera en cada resultado siguiendo los mismos pasos. En total, debes editar 8 destinos.

5. Explicación del flujo de Cloud Dataprep

La idea básica del flujo "Patrón de diseño de Analytics para Formularios de Google" es realizar las transformaciones en las respuestas de la encuesta como se describió anteriormente, desglosando cada categoría de pregunta en una receta específica de transformación de datos de Cloud Dataprep.

Este flujo divide las preguntas en 4 tablas (que corresponden a las 4 categorías de preguntas, para simplificar el proceso).

afa421849b1bd398.png

Te sugerimos que explores cada una de las recetas, una por una, comenzando por "Clean Headers" y, luego, "SingleChoiceSELECT-Questions", seguidas de las demás recetas que se encuentran debajo.

Todas las recetas tienen comentarios para explicar los distintos pasos de transformación. Cuando estás en una receta, puedes editar un paso y obtener una vista previa del estado anterior y posterior de una columna en particular.

449da06d96cd520e.png4ac6e14f578d0707.png

6. Ejecuta el flujo de Cloud Dataprep

Ahora que tu origen y tus destinos están configurados correctamente, puedes ejecutar el flujo para transformar y cargar las respuestas en BigQuery. Selecciona cada uno de los resultados y haz clic en el botón "Run". Si la tabla de BigQuery especificada existe, Dataprep agregará filas nuevas; de lo contrario, creará una tabla nueva.

47cf50f6d17a5b1e.png

Haz clic en el ícono de "Historial de trabajos" en el panel izquierdo para supervisar los trabajos. Debería tardar unos minutos en continuar y cargar las tablas de BigQuery.

afc79eeb27202fb4.png

Cuando se completen todos los trabajos, los resultados de la encuesta se cargarán en BigQuery en un formato limpio, estructurado y normalizado, listo para el análisis.

7. Analiza los datos de la encuesta en BigQuery

En la consola de Google para BigQuery, deberías poder ver los detalles de cada una de las tablas nuevas.

df370873572511ac.png

Con los datos de la encuesta en BigQuery, puedes hacer preguntas de comprensión más completas con facilidad para entender las respuestas de la encuesta en un nivel más profundo. Por ejemplo, supongamos que quieres comprender qué lenguaje de programación usan con más frecuencia las personas con diferentes cargos profesionales. Puedes escribir una 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 que tus análisis sean aún más potentes, puedes unir las respuestas de la encuesta a los datos de CRM para ver si los participantes se asignan a alguna cuenta que ya esté incluida en tu almacén de datos. Esto puede ayudar a tu empresa a tomar decisiones más fundamentadas sobre la asistencia al cliente o la segmentación de usuarios para nuevos lanzamientos.

Aquí, mostramos cómo puedes unir los datos de la encuesta a una tabla de cuentas según el dominio del encuestado y el sitio web de la cuenta. Ahora, puedes ver la distribución de las respuestas por tipo de cuenta, lo que te ayuda a comprender cuántos encuestados pertenecen a cuentas 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

8. Realiza análisis visuales

Ahora que los datos de tu encuesta están centralizados en un almacén de datos, puedes analizarlos fácilmente en una herramienta de inteligencia empresarial. Creamos algunos informes de ejemplo en Data Studio y en Looker.

Looker

Si ya tienes una instancia de Looker, puedes usar el LookML de esta carpeta para comenzar a analizar los datos de la encuesta de muestra y de CRM para este patrón. Simplemente crea un proyecto de Looker nuevo, agrega el LookML y reemplaza los nombres de la conexión y de la tabla en el archivo para que coincidan con tu configuración de BigQuery. Si no tienes una instancia de Looker, pero te interesa obtener más información, puedes programar una demostración aquí.

129db05d6f85f484.png

Data Studio

Como alternativa, para crear un informe en Data Studio, haz clic en el marco con la cruz de Google "Informe vacío" y conéctate a BigQuery. Sigue todas las instrucciones de Data Studio. Si quieres obtener más información, puedes encontrar una guía de inicio rápido y una introducción a las funciones principales de Data Studio aquí. También puedes encontrar nuestros paneles de Looker Studio prediseñados aquí.

5e744869e3fe3f8f.png

9. Limpieza

La manera más fácil de eliminar la facturación es borrar el proyecto de Cloud que creaste para el instructivo. Como alternativa, puedes borrar los recursos individuales.

  1. En la consola de Cloud, ve a Administrar recursos.
  2. En la lista de proyectos, elige el proyecto que deseas borrar y haz clic en Borrar.
  3. En el diálogo, escribe el ID del proyecto y, luego, haz clic en Cerrar para borrarlo.