Crear un flujo de procesamiento ELT para datos de analíticas de marketing

En este tutorial se explica cómo configurar un flujo de trabajo de extracción, carga y transformación (ELT) que extrae, carga y transforma datos de analíticas de marketing en BigQuery.

En un flujo de trabajo de ELT típico, se extraen periódicamente nuevos datos de clientes de tu fuente de datos y se cargan en BigQuery. Los datos sin estructurar se procesan para obtener métricas útiles. En este tutorial, crearás un flujo de trabajo de ELT configurando una transferencia de datos de analíticas de marketing mediante BigQuery Data Transfer Service. A continuación, programa Dataform para que ejecute transformaciones periódicas en los datos.

En este tutorial, se usa Google Ads como fuente de datos, pero puede usar cualquiera de las fuentes de datos compatibles con BigQuery Data Transfer Service.

Antes de empezar

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

Roles obligatorios

Para obtener los permisos que necesitas para completar este tutorial, pide a tu administrador que te asigne los siguientes roles de gestión de identidades y accesos en el proyecto:

• Administrador de BigQuery (roles/bigquery.admin)
• Administrador de Dataform (roles/dataform.admin)

Para obtener más información sobre cómo conceder roles, consulta el artículo Gestionar acceso a proyectos, carpetas y organizaciones.

También puedes conseguir los permisos necesarios a través de roles personalizados u otros roles predefinidos.

Programar transferencias de datos periódicas

Para mantener BigQuery actualizado con los datos de marketing más recientes de tu fuente de datos, configura transferencias de datos periódicas con BigQuery Data Transfer Service para extraer y cargar datos de forma programada.

En este tutorial, usará Google Ads como fuente de datos de ejemplo. Para ver una lista completa de las fuentes de datos compatibles con BigQuery Data Transfer Service, consulte Fuentes de datos admitidas.

1. Ve a la página Transferencias de datos de la Google Cloud consola.

   Ir a Transferencias de datos

2. Haz clic en Crear transferencia.

3. En la sección Tipo de fuente, en Fuente, elige Google Ads.

4. En la sección Detalles de la fuente de datos:

   1. En ID de cliente, introduce tu ID de cliente de Google Ads.
   2. En Tipo de informe, selecciona Estándar. El informe estándar incluye el conjunto de informes y campos estándar que se detallan en el artículo Transformación de informes de Google Ads.
      • En Periodo de actualización, introduce 5.

5. En la sección Configuración de destino, en Conjunto de datos, seleccione el conjunto de datos que ha creado para almacenar sus datos.

6. En la sección Nombre de la configuración de transferencia, en Nombre visible, introduzca Marketing tutorial.

7. En la sección Opciones de programación:

   • En Frecuencia de repetición, selecciona Días.
   • En En, escribe 08:00.

8. Haz clic en Guardar.

Una vez que guardes la configuración, BigQuery Data Transfer Service empezará a transferir los datos. Según los ajustes de la configuración de la transferencia, esta se ejecuta una vez al día a las 08:00 (UTC) y extrae datos de Google Ads de los últimos cinco días.

Puedes monitorizar las tareas de transferencia en curso para comprobar el estado de cada transferencia de datos.

Consultar datos de tablas

Cuando tus datos se transfieren a BigQuery, se escriben en tablas con particiones por hora de ingestión. Para obtener más información, consulta el artículo sobre la introducción a las tablas con particiones.

Si consultas tus tablas directamente en lugar de usar las vistas generadas automáticamente, debes usar la pseudocolumna _PARTITIONTIME en tu consulta. Para obtener más información, consulta Consultar tablas con particiones.

En las siguientes secciones se muestran consultas de ejemplo que puede usar para examinar los datos transferidos.

Rendimiento de las campañas

La siguiente consulta de ejemplo analiza el rendimiento de las campañas de Google Ads de los últimos 30 días.

Consola

SELECT
  c.customer_id,
  c.campaign_name,
  c.campaign_status,
  SUM(cs.metrics_impressions) AS Impressions,
  SUM(cs.metrics_interactions) AS Interactions,
  (SUM(cs.metrics_cost_micros) / 1000000) AS Cost
FROM
  `DATASET.ads_Campaign_CUSTOMER_ID` c
LEFT JOIN
  `DATASET.ads_CampaignBasicStats_CUSTOMER_ID` cs
ON
  (c.campaign_id = cs.campaign_id
  AND cs._DATA_DATE BETWEEN
  DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY) AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
WHERE
  c._DATA_DATE = c._LATEST_DATE
GROUP BY
  1, 2, 3
ORDER BY
  Impressions DESC

bq

  bq query --use_legacy_sql=false '
  SELECT
    c.customer_id,
    c.campaign_name,
    c.campaign_status,
    SUM(cs.metrics_impressions) AS Impressions,
    SUM(cs.metrics_interactions) AS Interactions,
    (SUM(cs.metrics_cost_micros) / 1000000) AS Cost
  FROM
    `DATASET.ads_Campaign_CUSTOMER_ID` c
  LEFT JOIN
    `DATASET.ads_CampaignBasicStats_CUSTOMER_ID` cs
  ON
    (c.campaign_id = cs.campaign_id
    AND cs._DATA_DATE BETWEEN
    DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY) AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
  WHERE
    c._DATA_DATE = c._LATEST_DATE
  GROUP BY
    1, 2, 3
  ORDER BY
    Impressions DESC'

Haz los cambios siguientes:

• DATASET: el nombre del conjunto de datos que has creado para almacenar la tabla transferida
• CUSTOMER_ID: tu ID de cliente de Google Ads.

Número de palabras clave

La siguiente consulta de ejemplo analiza las palabras clave por campaña, grupo de anuncios y estado de la palabra clave. Esta consulta usa la función KeywordMatchType. Los tipos de concordancia con palabras clave permiten controlar qué búsquedas pueden activar tus anuncios. Para obtener más información sobre las opciones de concordancia con palabras clave, consulta el artículo Acerca de las opciones de concordancia con palabras clave.

Consola

  SELECT
    c.campaign_status AS CampaignStatus,
    a.ad_group_status AS AdGroupStatus,
    k.ad_group_criterion_status AS KeywordStatus,
    k.ad_group_criterion_keyword_match_type AS KeywordMatchType,
    COUNT(*) AS count
  FROM
    `DATASET.ads_Keyword_CUSTOMER_ID` k
    JOIN
    `DATASET.ads_Campaign_CUSTOMER_ID` c
  ON
    (k.campaign_id = c.campaign_id AND k._DATA_DATE = c._DATA_DATE)
  JOIN
    `DATASET.ads_AdGroup_CUSTOMER_ID` a
  ON
    (k.ad_group_id = a.ad_group_id AND k._DATA_DATE = a._DATA_DATE)
  WHERE
    k._DATA_DATE = k._LATEST_DATE
  GROUP BY
    1, 2, 3, 4

bq

  bq query --use_legacy_sql=false '
  SELECT
    c.campaign_status AS CampaignStatus,
    a.ad_group_status AS AdGroupStatus,
    k.ad_group_criterion_status AS KeywordStatus,
    k.ad_group_criterion_keyword_match_type AS KeywordMatchType,
    COUNT(*) AS count
  FROM
    `DATASET.ads_Keyword_CUSTOMER_ID` k
  JOIN
    `DATASET.ads_Campaign_CUSTOMER_ID` c
  ON
    (k.campaign_id = c.campaign_id AND k._DATA_DATE = c._DATA_DATE)
  JOIN
    `DATASET.ads_AdGroup_CUSTOMER_ID` a
  ON
    (k.ad_group_id = a.ad_group_id AND k._DATA_DATE = a._DATA_DATE)
  WHERE
    k._DATA_DATE = k._LATEST_DATE
  GROUP BY
    1, 2, 3, 4'

Haz los cambios siguientes:

• DATASET: el nombre del conjunto de datos que has creado para almacenar la tabla transferida
• CUSTOMER_ID: tu ID de cliente de Google Ads.

Crear un repositorio de Dataform

Después de crear la configuración de transferencia de datos para transferir los datos más recientes de Google Ads, configura Dataform para que transforme periódicamente tus datos de analíticas de marketing. Dataform te permite programar transformaciones de datos periódicas y definirlas con SQL mientras colaboras con otros analistas de datos.

Crea un repositorio de Dataform para almacenar las consultas SQLX que componen tu código de transformación.

1. En la Google Cloud consola, ve a la página Dataform.

   Ir a Dataform

2. Haz clic en Crear repositorio.

3. En la página Crear repositorio, haz lo siguiente:

   1. En el campo Repository ID (ID de repositorio), introduce marketing-tutorial-repository.
   2. En la lista Región, selecciona una región.
   3. Haz clic en Crear.

El repositorio marketing-tutorial-repository ahora aparece en tu lista de repositorios de Dataform.

Para obtener más información sobre los repositorios de Dataform, consulta el artículo Acerca de los repositorios de Dataform.

Crear e inicializar un espacio de trabajo de desarrollo de Dataform

Crea un espacio de trabajo de desarrollo de Dataform para poder trabajar en el código de transformación de tu repositorio antes de confirmar e insertar los cambios en él.

1. En la Google Cloud consola, ve a la página Dataform.

   Ir a Dataform

2. Haz clic en marketing-tutorial-repository.

3. Haz clic en Crear espacio de trabajo de desarrollo.

4. En la ventana Crear espacio de trabajo de desarrollo, haga lo siguiente:

   1. En el campo ID de espacio de trabajo, introduce marketing-tutorial-workspace.
   2. Haz clic en Crear.

   Aparecerá la página del espacio de trabajo de desarrollo.

5. Haz clic en Inicializar espacio de trabajo.

El espacio de trabajo de desarrollo marketing-tutorial-workspace ahora aparece en tu repositorio marketing-tutorial-repository en la pestaña Espacios de trabajo de desarrollo, junto con dos archivos de ejemplo en el directorio definitions llamados *first_view.sqlx y *second_view.sqlx.

Para obtener más información sobre los espacios de trabajo de desarrollo de Dataform, consulta el artículo Descripción general de los espacios de trabajo de desarrollo.

Declarar la tabla de Google Ads como fuente de la tabla

Conecte la tabla de Google Ads que acaba de transferir a Dataform declarándola como fuente de datos. Para ello, siga estos pasos:

Crear un archivo SQLX para la declaración de la fuente de datos

En Dataform, declaras un destino de fuente de datos creando un archivo SQLX en el directorio definitions/:

1. En la Google Cloud consola, ve a la página Dataform.

   Ir a la página de Dataform

2. Selecciona marketing-tutorial-repository.

3. Selecciona marketing-tutorial-workspace.

4. En el panel Archivos, junto a definitions/, haz clic en el menú Más.

5. Haz clic en Crear archivo.

6. En el panel Crear archivo, haz lo siguiente:

   1. En el campo Añadir una ruta de archivo, después de definitions/, introduce el nombre definitions/googleads-declaration.sqlx.
   2. Haz clic en Crear archivo.

Declarar una fuente de datos

Edita el definitions/googleads-declaration.sqlx para declarar una tabla de Google Ads transferida como fuente de datos. En este ejemplo se declara la tabla ads_Campaign como fuente de datos:

1. En el espacio de trabajo de desarrollo, en el panel Archivos, haz clic en el archivo SQLX para la declaración de la fuente de datos.

2. En el archivo, introduce el siguiente fragmento de código:

       config {
           type: "declaration",
           database: "PROJECT_ID",
           schema: "DATASET",
           name: "ads_Campaign_CUSTOMER_ID",
       }

Define tu transformación

Define tus transformaciones de datos creando un archivo SQLX en el directorio definitions/. En este tutorial, crearás una transformación diaria que agregue métricas como clics, impresiones, costes y conversiones mediante un archivo llamado daily_performance.sqlx.

Crear el archivo SQLX de transformación

1. En el panel Archivos, junto a definitions/, haz clic en el menú Más y, a continuación, selecciona Crear archivo.
2. En el campo Añadir una ruta de archivo, introduce definitions/daily_performance.sqlx.
3. Haz clic en Crear archivo.

Definir el archivo SQLX de transformación

1. En el panel Archivos, despliega el directorio definitions/.

2. Selecciona daily_performance.sqlx y, a continuación, introduce la siguiente consulta:

       config {
           type: "table",
           schema: "reporting",
           tags: ["daily", "google_ads"]
       }
       SELECT
           date,
           campaign_id,
           campaign_name,
       SUM(clicks) AS total_clicks
       FROM
           `ads_Campaign_CUSTOMER_ID`
       GROUP BY
           date,
           campaign_id,
           campaign_name
           ORDER BY
           date DESC

Confirmar y enviar los cambios

Una vez que hayas hecho los cambios en tu espacio de trabajo de desarrollo, puedes confirmarlos e insertarlos en tu repositorio siguiendo estos pasos:

1. En el espacio de trabajo marketing-tutorial-workspace, haz clic en Confirmar 1 cambio.
2. En el panel Nuevo commit, introduce una descripción del commit en el campo Añadir un mensaje de commit.
3. Haz clic en Confirmar todos los cambios.
4. En el espacio de trabajo marketing-tutorial-workspace, haz clic en Enviar a la rama predeterminada.

Una vez que los cambios se hayan enviado correctamente al repositorio, aparecerá el mensaje Workspace está actualizado.

Programar la transformación de datos

Una vez que haya definido el archivo de transformación de datos, programe las transformaciones de datos.

Crear una versión de producción

Una versión de producción en Dataform asegura que tu entorno se actualice de forma constante con los resultados de tus transformaciones de datos. En los pasos siguientes se muestra cómo especificar la rama main del repositorio marketing-tutorial-repository para almacenar las transformaciones de datos:

1. En la Google Cloud consola, ve a la página Dataform.

   Ir a la página de Dataform

2. Selecciona marketing-tutorial-repository.

3. Haz clic en la pestaña Lanzamientos y programación.

4. Haz clic en Crear versión de producción.

5. En el panel Crear configuración de lanzamiento, configura los siguientes ajustes:

   1. En el campo Release ID (ID de lanzamiento), introduce transformations.
   2. En el campo Git commitish, deja el valor predeterminado main .
   3. En la sección Frecuencia de programación, seleccione Bajo demanda.

6. Haz clic en Crear.

Crear una configuración de flujo de trabajo

Una vez que hayas creado una versión de producción, podrás crear una configuración de flujo de trabajo que ejecute las transformaciones de datos según una programación especificada en tu repositorio. Sigue estos pasos para programar transformaciones diarias a partir del archivo transformations:

1. En la Google Cloud consola, ve a la página Dataform.

   Ir a la página de Dataform

2. Selecciona marketing-tutorial-repository.

3. Haz clic en la pestaña Lanzamientos y programación.

4. En la sección Configuraciones de flujo de trabajo, haz clic en Crear.

5. En el panel Crear configuración de flujo de trabajo, en el campo ID de configuración, introduce transformations.

6. En el menú Configuración de la versión, selecciona transformations.

7. En Autenticación, selecciona Ejecutar con credenciales de usuario.

8. En la sección Frecuencia de la programación, haga lo siguiente:

   1. Select **Repeat**.
   1. For **Repeats**, select `Daily`.
   1. For **At time**, enter `10:00 AM`.
   1. For **Timezone**, select `Coordinated Universal Time (UTC)`.
   

9. Haz clic en Selección de etiquetas.

10. En el campo Seleccionar etiquetas que se van a ejecutar, selecciona Diario.

11. Haz clic en Crear.

La configuración del flujo de trabajo que has creado ejecuta todo el resultado de la última compilación creada por la configuración de lanzamiento transformations.

Limpieza

Para evitar que se apliquen cargos en tu cuenta de Google Cloud por los recursos utilizados en esta página, sigue estos pasos.

Eliminar el conjunto de datos creado en BigQuery

Para evitar que se te cobren los recursos de BigQuery, elimina el conjunto de datos llamado dataform.

1. En la Google Cloud consola, ve a la página BigQuery.

   Ir a BigQuery

2. En el panel Explorador, expande tu proyecto y selecciona dataform.

3. Haz clic en el menú Acciones y, a continuación, selecciona Eliminar.

4. En el cuadro de diálogo Eliminar conjunto de datos, introduce delete en el campo y, a continuación, haz clic en Eliminar.

Eliminar el espacio de trabajo de desarrollo y las configuraciones de Dataform

Crear un espacio de trabajo de desarrollo de Dataform no tiene ningún coste, pero para eliminarlo, puedes seguir estos pasos:

1. En la Google Cloud consola, ve a la página Dataform.

   Ir a Dataform

2. Haz clic en quickstart-repository.

3. Haz clic en la pestaña Lanzamiento y programación.

4. En la sección Configuraciones de lanzamiento, haz clic en el menú Más junto a la configuración production y, a continuación, en Eliminar.

5. En la sección Configuraciones de flujo de trabajo, haz clic en el menú Más junto a la configuración transformations y, a continuación, en Eliminar.

6. En la pestaña Espacios de trabajo de desarrollo, haz clic en el menú Más quickstart-workspace y, a continuación, selecciona Eliminar.

7. Para confirmar la acción, haz clic en Eliminar.

Eliminar el repositorio de Dataform

Crear un repositorio de Dataform no tiene ningún coste, pero para eliminarlo, puedes seguir estos pasos:

1. En la Google Cloud consola, ve a la página Dataform.

   Ir a Dataform

2. En quickstart-repository, haz clic en el menú Más y, a continuación, selecciona Eliminar.

3. En la ventana Eliminar repositorio, introduce el nombre del repositorio para confirmar la eliminación.

4. Para confirmar la acción, haz clic en Eliminar.