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, revisa la sintaxis de consultas en SQL estándar.

Actividades

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

En cada lab, recibirá un proyecto de Google Cloud y un conjunto de recursos nuevos por tiempo limitado y sin costo adicional.

1. Accede a Qwiklabs desde una ventana de incógnito.

2. Ten en cuenta el tiempo de acceso del lab (por ejemplo, 1:15:00) y asegúrate de finalizarlo en el plazo asignado.
   No existe una función de pausa. Si lo necesita, puede reiniciar el lab, pero deberá hacerlo desde el comienzo.

3. Cuando esté listo, haga clic en Comenzar lab.

4. Anote las credenciales del lab (el nombre de usuario y la contraseña). Las usarás para acceder a la consola de Google Cloud.

5. Haga clic en Abrir Google Console.

6. Haga clic en Usar otra cuenta, copie las credenciales para este lab y péguelas en el mensaje emergente que aparece.
   Si usa otras credenciales, se generarán errores o incurrirá en cargos.

7. Acepta las condiciones y omite la página de recursos de recuperación.

Tarea 1. Destaca el proyecto del lab en BigQuery

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

Abra BigQuery Console

1. En Google Cloud Console, seleccione Menú de navegación > BigQuery. Se abrirá el cuadro de mensaje Bienvenido a BigQuery en Cloud Console, que contiene un vínculo a la guía de inicio rápido y enumera las actualizaciones de la IU.
2. Haga clic en Listo.

Los conjuntos de datos públicos de BigQuery no se muestran de forma predeterminada en la IU web de BigQuery, por lo que abrirás el proyecto de conjuntos de datos públicos.

4. Haz clic en + Agregar datos.

5. Selecciona Destaca un proyecto por nombre.

6. En el Nombre del proyecto, ingresa data-to-insights.

7. Haz clic en Destacar.

8. En el panel explorador, verás el proyecto data-to-insights destacado.

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.

Vuelve a la sección Explorador clásico y revisa los datos de la tabla all_sessions_raw:

1. Expande el proyecto data-to-insights.
2. Expande el comercio electrónico.
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. Para obtener una vista previa de las filas de muestra de la tabla sin usar SQL, haz clic en la pestaña Vista previa.

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

En tu consulta, se usa la función GROUP BY de SQL en todos los campos y se cuentan (COUNT) las filas que tienen los mismos valores en todos los campos.

• Si cada campo es único, COUNT devolverá 1, ya que no hay otras agrupaciones de filas con el mismo valor en todos los campos.

• Si hay una fila con los mismos valores en todos los campos, se agrupará con las otras y COUNT será mayor de 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 de 1.

• Copia y pega la siguiente consulta en el campo EDITOR y, luego, selecciona EJECUTAR para buscar registros duplicados en todos los campos de las columnas. Si la pestaña EDITOR no está visible, haz clic en + Consulta en SQL.

#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 puede ser útil confirmar la unicidad de las filas con COUNT, GROUP BY y HAVING antes de comenzar con tu análisis.

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 tus 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 # esquema: https://support.google.com/analytics/answer/3437719?hl=en SELECT fullVisitorId, # el ID de visitante único visitId, # un visitante puede tener múltiples visitas date, # fecha de la sesión guardada como cadena YYYYMMDD time, # hora del hit con el sitio individual (puede ser de 0 a muchos por sesión de visitante) v2ProductName, # no es único, ya que un producto puede tener variantes, como el color (Color) productSKU, # único para cada producto type, # un visitante puede visitar páginas (Pages) y/o puede activar eventos (Events) (incluso al mismo tiempo) eCommerceAction_type, # se asigna a ‘add to cart' (agregar al carrito), ‘completed checkout' (compra completada) eCommerceAction_step, eCommerceAction_option, transactionRevenue, # ingreso del pedido transactionId, # identificador único de transacción generadora de ingresos 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 # encontrar duplicados

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 + Consulta en SQL.
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, haz clic en el ícono del validador de consultas en tiempo real.
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

Nota: En SQL, la configuración predeterminada de la cláusula 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

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

8. 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:

Nota: 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

9. Actividad adicional: 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

10. 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:

11. 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.

Tarea 4: Practica con SQL

¿Todo listo para poner a prueba tus habilidades de SQL? Prueba estos desafíos.

Desafío 1: Calcula un porcentaje de conversiones

1. Escribe una consulta de porcentaje de conversiones para productos con las siguientes características:

• Se agregaron al carrito o se pidieron más de 1,000 unidades.
• Y no son discos voladores.

2. Responde estas preguntas:

• ¿Cuántas veces fue parte de un pedido (completo o incompleto) el producto?
• ¿Cuántas unidades totales del producto se incluyeron en pedidos (ya sean completos o incompletos)?
• ¿Qué producto tuvo el porcentaje de conversiones más alto?

3. Completa la siguiente consulta parcial:

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS potential_orders, SUM(productQuantity) AS quantity_product_added, v2ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE v2ProductName NOT LIKE 'frisbee' GROUP BY v2ProductName HAVING quantity_product_added > ORDER BY conversion_rate LIMIT 10;

Solución posible:

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS potential_orders, SUM(productQuantity) AS quantity_product_added, (COUNT(productQuantity) / COUNT(*)) AS conversion_rate, v2ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE LOWER(v2ProductName) NOT LIKE '%frisbee%' GROUP BY v2ProductName HAVING quantity_product_added > 1000 ORDER BY conversion_rate DESC LIMIT 10;

Desafío 2: Haz un seguimiento del progreso de las confirmaciones de compra de los visitantes

• Escribe una consulta que muestre eCommerceAction_type y el recuento específico de fullVisitorId asociado a cada tipo.

Solución posible:

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

Contenido adicional

Recibes la siguiente asignación para el tipo de acción: Unknown (Desconocido) = 0 Click through of product lists (Clics en fichas de productos) = 1 Product detail views (Vistas de detalles del producto) = 2 Add product(s) to cart (Agregar productos al carrito) = 3 Remove product(s) from cart (Quitar productos del carrito) = 4 Check out (Pagar) = 5 Completed purchase (Compra completada) = 6 Refund of purchase (Reembolso de la compra) = 7 Checkout options (Opciones de confirmación de la compra) = 8

• Usa una instrucción Case para agregar una columna nueva a la consulta anterior y así mostrar la etiqueta eCommerceAction_type (por ejemplo, “Compra completada”).

Solución posible:

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS number_of_unique_visitors, eCommerceAction_type, CASE eCommerceAction_type WHEN '0' THEN 'Unknown' WHEN '1' THEN 'Click through of product lists' WHEN '2' THEN 'Product detail views' WHEN '3' THEN 'Add product(s) to cart' WHEN '4' THEN 'Remove product(s) from cart' WHEN '5' THEN 'Check out' WHEN '6' THEN 'Completed purchase' WHEN '7' THEN 'Refund of purchase' WHEN '8' THEN 'Checkout options' ELSE 'ERROR' END AS eCommerceAction_type_label FROM `data-to-insights.ecommerce.all_sessions` GROUP BY eCommerceAction_type ORDER BY eCommerceAction_type;

¿Qué porcentaje de visitantes que agregaron algo al carrito completó una compra?

Respuesta: 19988 / 56010 = .3568 o 35.68%

Desafío 3: Haz un seguimiento de carritos abandonados de sesiones de alta calidad

• Escribe una consulta con funciones de agregación que devuelva IDs de sesiones únicos de aquellos visitantes que agregaron un producto a su carrito, pero nunca completaron la confirmación de la compra (abandonaron su carrito de compras).

Solución posible:

#standardSQL # high quality abandoned carts SELECT #unique_session_id CONCAT(fullVisitorId,CAST(visitId AS STRING)) AS unique_session_id, sessionQualityDim, SUM(productRevenue) AS transaction_revenue, MAX(eCommerceAction_type) AS checkout_progress FROM `data-to-insights.ecommerce.all_sessions` WHERE sessionQualityDim > 60 # high quality session GROUP BY unique_session_id, sessionQualityDim HAVING checkout_progress = '3' # 3 = added to cart AND (transaction_revenue = 0 OR transaction_revenue IS NULL)

Finalice su lab

Cuando haya completado su lab, haga clic en Finalizar lab. Qwiklabs quitará los recursos que usó y limpiará la cuenta por usted.

Tendrá la oportunidad de calificar su experiencia en el lab. Seleccione la cantidad de estrellas que corresponda, ingrese un comentario y haga clic en Enviar.

La cantidad de estrellas indica lo siguiente:

• 1 estrella = Muy insatisfecho
• 2 estrellas = Insatisfecho
• 3 estrellas = Neutral
• 4 estrellas = Satisfecho
• 5 estrellas = Muy satisfecho

Puede cerrar el cuadro de diálogo si no desea proporcionar comentarios.

Para enviar comentarios, sugerencias o correcciones, use la pestaña Asistencia.

Copyright 2020 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.