GSP407

Descripción general

BigQuery es la base de datos analítica de bajo costo, no-ops y completamente administrada de Google. Con BigQuery, puedes consultar muchos terabytes de datos sin tener que administrar infraestructuras y sin necesitar un administrador de base de datos. BigQuery usa SQL y puede aprovechar el modelo de pago por uso. BigQuery te permite enfocarte en el análisis de datos para encontrar estadísticas valiosas.

En una tabla de BigQuery, se cargó un conjunto de datos de comercio electrónico recientemente disponible que incluye millones de registros de Google Analytics para Google Merchandise Store. En este lab, usarás una copia de ese conjunto de datos. Se proporcionan situaciones de muestra, en las cuales observarás los datos y buscarás maneras de quitar la información duplicada. Luego, en el lab, se te guiará para que analices aún más los datos.

Si deseas seguir las consultas de BigQuery que se proporcionan para analizar los datos y experimentar con ellas, consulta la Referencia de sintaxis de consultas de BigQuery.

Aprendizajes esperados

En este lab, usarás BigQuery para hacer lo siguiente:

• Acceder a un conjunto de datos de comercio electrónico
• Observar los metadatos del conjunto de datos
• Quitar entradas duplicadas
• Escribir y ejecutar consultas

Configuración y requisitos

Antes de hacer clic en el botón Comenzar lab

Lee estas instrucciones. Los labs son cronometrados y no se pueden pausar. El cronómetro, que comienza a funcionar cuando haces clic en Comenzar lab, indica por cuánto tiempo tendrás a tu disposición los recursos de Google Cloud.

Este lab práctico te permitirá realizar las actividades correspondientes en un entorno de nube real, no en uno de simulación o demostración. Para ello, se te proporcionan credenciales temporales nuevas que utilizarás para acceder a Google Cloud durante todo el lab.

Para completar este lab, necesitarás lo siguiente:

• Acceso a un navegador de Internet estándar (se recomienda el navegador Chrome)

Nota: Usa una ventana de navegador privada o de Incógnito para ejecutar este lab. Así evitarás cualquier conflicto entre tu cuenta personal y la cuenta de estudiante, lo que podría generar cargos adicionales en tu cuenta personal.

• Tiempo para completar el lab: Recuerda que, una vez que comienzas un lab, no puedes pausarlo.

Nota: Si ya tienes un proyecto o una cuenta personal de Google Cloud, no los uses en este lab para evitar cargos adicionales en tu cuenta.

Cómo iniciar su lab y acceder a la consola de Google Cloud

1. Haga clic en el botón Comenzar lab. Si debe pagar por el lab, se abrirá una ventana emergente para que seleccione su forma de pago. A la izquierda, se encuentra el panel Detalles del lab que tiene estos elementos:

   • El botón Abrir la consola de Google
   • Tiempo restante
   • Las credenciales temporales que debe usar para el lab
   • Otra información para completar el lab, si es necesaria

2. Haga clic en Abrir la consola de Google. El lab inicia recursos y abre otra pestaña en la que se muestra la página de acceso.

   Sugerencia: Ordene las pestañas en ventanas separadas, una junto a la otra.

   Nota: Si ve el diálogo Elegir una cuenta, haga clic en Usar otra cuenta.

3. Si es necesario, copie el nombre de usuario del panel Detalles del lab y péguelo en el cuadro de diálogo Acceder. Haga clic en Siguiente.

4. Copie la contraseña del panel Detalles del lab y péguela en el cuadro de diálogo de bienvenida. Haga clic en Siguiente.

   Importante: Debe usar las credenciales del panel de la izquierda. No use sus credenciales de Google Cloud Skills Boost. Nota: Usar su propia Cuenta de Google podría generar cargos adicionales.

5. Haga clic para avanzar por las páginas siguientes:

   • Acepte los términos y condiciones.
   • No agregue opciones de recuperación o autenticación de dos factores (esta es una cuenta temporal).
   • No se registre para obtener pruebas gratuitas.

Después de un momento, se abrirá la consola de Cloud en esta pestaña.

Nota: Para ver el menú con una lista de los productos y servicios de Google Cloud, haga clic en el Menú de navegación que se encuentra en la parte superior izquierda de la pantalla.

Tarea 1. Fija el proyecto del lab en BigQuery

En esta sección, agregarás el proyecto data-to-insights a tus recursos de entorno.

1. Haz clic en el menú de navegación > BigQuery.

Se abrirá el cuadro de mensaje de bienvenida a BigQuery en la consola de Cloud.

Nota: Allí, se proporciona un vínculo para acceder a la guía de inicio rápido y actualizaciones de IU.

2. Haz clic en Listo.
3. Los conjuntos de datos públicos de BigQuery no se muestran de forma predeterminada en la IU web de BigQuery. Para abrir el proyecto de conjuntos de datos públicos, copia “data-to-insights”.
4. Haz clic en + Agregar > Destaca un proyecto por nombre y, luego, establece el nombre en data-to-insights. Haz clic en DESTACAR.

Ahora en la sección Explorador se muestra el proyecto data-to-insights.

Tarea 2. Explora los datos de comercio electrónico y también identifica los registros duplicados

Situación: Tu equipo de analistas de datos exportó a BigQuery los registros de Google Analytics para un sitio web de comercio electrónico y creó una nueva tabla de todos los datos de comercio electrónico sin procesar que provienen de sesiones de visitantes.

Explora los datos de la tabla all_sessions_raw:

1. Haz clic en el ícono Expandir nodo cerca de data-to-insights para expandir el proyecto.
2. Expande ecommerce.
3. Haz clic en all_sessions_raw.

En el panel de la derecha, se abrirá una sección que ofrece 3 vistas de los datos de la tabla:

• Pestaña Esquema: Nombre del campo, Tipo, Modo y Descripción; las restricciones lógicas que se usan para organizar los datos
• Pestaña Detalles: Metadatos de la tabla
• Pestaña Vista previa: Vista previa de la tabla

4. Haz clic en la pestaña Detalles para ver los metadatos de la tabla.

Preguntas:

Identifica filas duplicadas

Ver una muestra de la cantidad de datos puede darte una mayor intuición de lo que se incluye en el conjunto de datos.

1. Para obtener una vista previa de las filas de muestra de la tabla sin usar SQL, haz clic en la pestaña Vista previa.

2. Desplázate por las filas y analízalas. No hay ningún campo singular que identifique una fila de manera inequívoca, por lo que necesitas lógica avanzada para identificar las filas duplicadas.

3. La consulta que usarás (debajo) utiliza la función GROUP BY de SQL en todos los campos y cuenta (COUNT) las filas que tienen los mismos valores en todos los campos:

• Si cada campo es único, COUNT devuelve 1, ya que no hay otras agrupaciones de filas con el mismo valor en todos los campos.
• Si hay varias filas con los mismos valores en todos los campos, se agrupan con las otras y COUNT será mayor que 1.

La última parte de la consulta es un filtro de agregación en el que se usa HAVING para mostrar solo los resultados que tienen un COUNT de duplicados mayor que 1. Por lo tanto, la cantidad de registros que tienen duplicados será la misma que la cantidad de filas de la tabla resultante.

4. Copia y pega la siguiente consulta en el Editor de consultas y, luego, EJECÚTALA para encontrar los registros que están duplicados en todas las columnas.

#standardSQL SELECT COUNT(*) as num_duplicate_rows, * FROM `data-to-insights.ecommerce.all_sessions_raw` GROUP BY fullVisitorId, channelGrouping, time, country, city, totalTransactionRevenue, transactions, timeOnSite, pageviews, sessionQualityDim, date, visitId, type, productRefundAmount, productQuantity, productPrice, productRevenue, productSKU, v2ProductName, v2ProductCategory, productVariant, currencyCode, itemQuantity, itemRevenue, transactionRevenue, transactionId, pageTitle, searchKeyword, pagePathLevel1, eCommerceAction_type, eCommerceAction_step, eCommerceAction_option HAVING num_duplicate_rows > 1;

Nota: En tus propios conjuntos de datos, incluso si tienes una clave única, aún es conveniente confirmar la unicidad de las filas con COUNT, GROUP BY y HAVING antes de comenzar con tu análisis.

Haz clic en Revisar mi progreso para verificar el objetivo. Identificar filas duplicadas

Analiza la nueva tabla all_sessions

En esta sección, usarás una tabla con los duplicados anulados llamada all_sessions.

Situación: Tu equipo de analistas de datos te dio esta consulta y los expertos en esquemas identificaron los campos clave que deben ser únicos para cada registro en función de tu esquema.

• Ejecuta la consulta para confirmar que no existan duplicados y, esta vez, hazlo en la tabla all_sessions:

#standardSQL # schema: https://support.google.com/analytics/answer/3437719?hl=en SELECT fullVisitorId, # the unique visitor ID visitId, # a visitor can have multiple visits date, # session date stored as string YYYYMMDD time, # time of the individual site hit (can be 0 to many per visitor session) v2ProductName, # not unique since a product can have variants like Color productSKU, # unique for each product type, # a visitor can visit Pages and/or can trigger Events (even at the same time) eCommerceAction_type, # maps to ‘add to cart', ‘completed checkout' eCommerceAction_step, eCommerceAction_option, transactionRevenue, # revenue of the order transactionId, # unique identifier for revenue bearing transaction COUNT(*) as row_count FROM `data-to-insights.ecommerce.all_sessions` GROUP BY 1,2,3 ,4, 5, 6, 7, 8, 9, 10,11,12 HAVING row_count > 1 # find duplicates

Esta consulta no devuelve ningún registro.

Nota: En SQL, puedes usar ORDER BY o GROUP BY en el índice de la columna, por ejemplo, GROUP BY 1 en lugar de GROUP BY fullVisitorId.

Tarea 3. Escribe SQL básico en los datos de comercio electrónico

En esta sección, harás consultas para obtener estadísticas sobre el conjunto de datos de comercio electrónico.

Escribe una consulta que muestre el total de visitantes únicos

Tu consulta hace un recuento de product_views para determinar el total de vistas y un recuento de fullVisitorID para determinar la cantidad de visitantes únicos.

1. Haz clic en el ícono “+” (Redactar consulta nueva).
2. Escribe esta consulta en el editor:

#standardSQL SELECT COUNT(*) AS product_views, COUNT(DISTINCT fullVisitorId) AS unique_visitors FROM `data-to-insights.ecommerce.all_sessions`;

3. Para asegurarte de que tu sintaxis sea correcta, confirma que el validador de consultas en tiempo real muestre el ícono de verificación verde.
4. Haz clic en Ejecutar. Lee los resultados para ver la cantidad de visitantes únicos.

Resultados:

5. Ahora, escribe una consulta que muestre el total de visitantes únicos (fullVisitorID) en función del sitio de referencia (channelGrouping):

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS unique_visitors, channelGrouping FROM `data-to-insights.ecommerce.all_sessions` GROUP BY channelGrouping ORDER BY channelGrouping DESC;

Resultados:

6. Escribe una consulta para enumerar todos los nombres de productos únicos (v2ProductName) en orden alfabético:

#standardSQL SELECT (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` GROUP BY ProductName ORDER BY ProductName

Sugerencia: En SQL, la configuración predeterminada de las cláusulas ORDER BY es ascendente (ASC) de la A a la Z. Si quieres la opción inversa, prueba con ORDER BY field_name DESC.

Resultados:

Esta consulta devuelve un total de 633 productos (filas).

7. Escribe una consulta para enumerar los cinco productos con la mayor cantidad de vistas (product_views) por parte de todos los visitantes (incluidas las personas que vieron el mismo producto más de una vez). Tu consulta cuenta la cantidad de veces que se vio (product_views) un producto (v2ProductName), usa el orden descendente para la lista y enumera las 5 entradas principales:

Sugerencia: En Google Analytics, un visitante puede “ver” un producto durante los siguientes tipos de interacción: “page”, “screenview”, “event”, “transaction”, “item”, “social”, “exception”, “timing”. Para nuestros fines, solo filtra por el tipo = “PAGE”.

#standardSQL SELECT COUNT(*) AS product_views, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY v2ProductName ORDER BY product_views DESC LIMIT 5;

Resultados:

8. Actividad adicional: Ahora define mejor la consulta para dejar de contar por duplicado las vistas de visitantes que vieron un producto muchas veces. Cada vista de producto diferente solo debe registrarse una vez por visitante:

WITH unique_product_views_by_person AS ( -- find each unique product viewed by each visitor SELECT fullVisitorId, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY fullVisitorId, v2ProductName ) -- aggregate the top viewed products and sort them SELECT COUNT(*) AS unique_view_count, ProductName FROM unique_product_views_by_person GROUP BY ProductName ORDER BY unique_view_count DESC LIMIT 5

Sugerencia: Puedes usar la cláusula WITH de SQL para dividir una consulta compleja en varios pasos. Aquí, primero creamos una consulta para buscar cada producto único por visitante y contarlo una vez. Luego, con la segunda consulta, se lleva a cabo la agregación de todos los visitantes y productos.

Resultados:

9. Por último, expande la consulta anterior para incluir la cantidad total de productos diferentes pedidos y la cantidad total de unidades pedidas (productQuantity):

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS orders, SUM(productQuantity) AS quantity_product_ordered, v2ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY v2ProductName ORDER BY product_views DESC LIMIT 5;

Resultados:

Preguntas:

10. Expande la consulta para que incluya la cantidad promedio de productos por pedido (cantidad total de unidades pedidas/cantidad total de pedidos o SUM(productQuantity)/COUNT(productQuantity)):

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS orders, SUM(productQuantity) AS quantity_product_ordered, SUM(productQuantity) / COUNT(productQuantity) AS avg_per_order, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY v2ProductName ORDER BY product_views DESC LIMIT 5;

Resultados

Pregunta:

El producto 22 oz YouTube Bottle Infuser tuvo el promedio por pedido (avg_per_order) más alto: 9.38 unidades por pedido.

Haz clic en Revisar mi progreso para verificar el objetivo. Escribir SQL básico en los datos de comercio electrónico

¡Felicitaciones!

Utilizaste BigQuery para ver y consultar los datos para obtener estadísticas valiosas sobre distintos aspectos del marketing de productos.

Próximos pasos/Más información

• Explora los conjuntos de datos públicos de BigQuery.
• ¿Tienes una cuenta de Google Analytics y deseas consultar tus propios conjuntos de datos en BigQuery? Sigue esta guía de exportación.
• Lee el artículo 15 Awesome things you probably didn’t know about Google BigQuery.
• Consulta otros labs para obtener más información sobre BigQuery:
  • Cómo solucionar errores comunes de SQL con BigQuery
  • Datos meteorológicos en BigQuery

Capacitación y certificación de Google Cloud

Recibe la formación que necesitas para aprovechar al máximo las tecnologías de Google Cloud. Nuestras clases incluyen habilidades técnicas y recomendaciones para ayudarte a avanzar rápidamente y a seguir aprendiendo. Para que puedas realizar nuestros cursos cuando más te convenga, ofrecemos distintos tipos de capacitación de nivel básico a avanzado: a pedido, presenciales y virtuales. Las certificaciones te ayudan a validar y demostrar tus habilidades y tu conocimiento técnico respecto a las tecnologías de Google Cloud.

Última actualización del manual: 26 de enero de 2024

Prueba más reciente del lab: 24 de agosto de 2023

Copyright 2024 Google LLC. All rights reserved. Google y el logotipo de Google son marcas de Google LLC. Los demás nombres de productos y empresas pueden ser marcas de las respectivas empresas a las que estén asociados.