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 útil para el análisis de datos. Enviarás los datos transformados a BigQuery, donde podrás hacer preguntas más profundas con SQL y unirlos a otros conjuntos de datos para realizar análisis más potentes. Al final, puedes explorar los 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 estadísticas a partir de los datos de encuestas

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 por analizar más de cerca las respuestas de Formularios de Google a nuestra encuesta de ejemplo.

Los resultados de la encuesta se pueden exportar desde la pestaña "Respuestas" haciendo clic en el ícono de Hojas de cálculo de Google y creando una hoja de cálculo nueva o cargando los resultados en una existente. Formularios de Google seguirá agregando respuestas a la hoja de cálculo a medida que los encuestados envíen sus respuestas hasta que desmarques el botón "Aceptar respuestas".

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 selecció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 transformación; la respuesta se carga tal como está.

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 con separador de punto y coma (p.ej., "Resp 1; Resp 4; Resp 6")
• Requisitos de transformación: La lista de valores debe extraerse y reorganizarse, de modo que cada respuesta se convierta en una fila nueva.

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.

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

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 pueden seleccionar de cero a varios valores de cada fila.

• Nombre de la pregunta: Cada pregunta individual se convierte en un nombre de columna con este 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 deben resolverse en este orden.

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

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

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

4. Compila el flujo de Cloud Dataprep

Importa el "Patrón de diseño de análisis de Formularios de Google" en Cloud Dataprep

Descarga el paquete de flujo del patrón de diseño de Google Forms Analytics (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 Flujos, selecciona Importar en el menú contextual.

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

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

En el lado izquierdo del flujo, la fuente de datos debe volver a conectarse 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 la Hoja de cálculo de Google y selecciona "Reemplazar".

Luego, haz clic en el vínculo "Import Datasets" en la parte inferior de la ventana modal. Haz clic en el lápiz "Editar ruta de acceso".

Desde allí, reemplaza el valor actual por este vínculo que apunta 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 "Ir" y, luego, en "Importar y agregar al flujo" en la parte inferior derecha. Cuando vuelvas al 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".

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

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

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

Después de editar los "Destinos manuales", continúa de la misma manera con el resultado de los "Destinos programados".

Los destinos manuales te permiten ejecutar cada trabajo de salida individual de forma independiente y a pedido. Esto es conveniente para probar una receta de preparación de datos y validar un resultado durante el diseño de tu flujo.

Los destinos programados se usan cuando deseas ejecutar tu flujo de forma rutinaria. Por ejemplo, es posible que desees volver a cargar los resultados de Formularios de Google en BigQuery todos los días para actualizar tus informes con las respuestas más recientes.

Sigue los mismos pasos para iterar en cada resultado. En total, debes editar 8 destinos.

5. Explicación del flujo de Cloud Dataprep

La idea básica del flujo del "Patrón de diseño de análisis de Formularios de Google" es realizar las transformaciones en las respuestas de la encuesta como se describió anteriormente, es decir, desglosando cada categoría de preguntas 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).

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

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.

6. Ejecuta el flujo de Cloud Dataprep

Ahora que tus fuentes y 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 "Ejecutar". Si la tabla de BigQuery especificada existe, Dataprep agregará filas nuevas. De lo contrario, creará una tabla nueva.

Haz clic en el ícono "Historial de trabajos" en el panel izquierdo para supervisar los trabajos. El proceso de carga de las tablas de BigQuery debería tardar unos minutos.

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.

Con los datos de la encuesta en BigQuery, puedes hacer preguntas más completas para comprender las respuestas de la encuesta a un nivel más profundo. Por ejemplo, supongamos que deseas comprender qué lenguaje de programación usan con mayor frecuencia las personas con diferentes títulos profesionales. Puedes escribir una consulta como la siguiente:

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 del CRM y 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 atención al cliente o la segmentación de usuarios para los 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

Nota: Si deseas ejecutar esta consulta por tu cuenta, puedes usar nuestra tabla de muestra de cuentas de Salesforce y agregarla a BigQuery siguiendo estas instrucciones.

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 en esta carpeta para comenzar a analizar los datos de la encuesta de muestra y del CRM para este patrón. Solo tienes que crear un proyecto nuevo de Looker, agregar el LookML y reemplazar los nombres de la conexión y 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í.

Data Studio

Como alternativa, para crear un informe en Data Studio, haz clic en el marco con la cruz de Google "Informe en blanco" y conéctate a BigQuery. Sigue todas las instrucciones de Data Studio. Si quieres obtener más información, puedes consultar 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 prediseñados de Data Studio aquí.

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.

Precaución: Borrar un proyecto tiene las siguientes consecuencias:

• Se borra todo en el proyecto. Si utilizaste un proyecto existente para este instructivo, cuando lo borres, también borrarás cualquier otro trabajo que hayas realizado en el proyecto.
• Se pierden los ID personalizados de proyectos. Cuando creaste este proyecto, es posible que hayas creado un ID del proyecto personalizado que desees utilizar en el futuro. Para conservar las URLs que usan el ID del proyecto, como una URL de appspot.com, borra los recursos seleccionados dentro del proyecto en lugar de borrar todo el proyecto.

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.