Compila una canalización de ELT para datos de análisis de marketing

En este instructivo, se muestra cómo configurar un flujo de trabajo de ELT que extrae, carga y transforma datos de análisis de marketing en BigQuery.

Un flujo de trabajo de ELT típico extrae periódicamente datos de clientes nuevos de tu fuente de datos y los carga en BigQuery. Luego, los datos no estructurados se procesan en métricas significativas. En este instructivo, crearás un flujo de trabajo de ELT configurando una transferencia de datos de análisis de marketing con el Servicio de transferencia de datos de BigQuery. Luego, programarás Dataform para que ejecute transformaciones periódicas en los datos.

En este instructivo, usarás Google Ads como fuente de datos, pero puedes usar cualquiera de las fuentes de datos compatibles con el Servicio de transferencia de datos de BigQuery.

Antes de comenzar

1. In the Cloud de Confiance console, on the project selector page, select or create a Cloud de Confiance 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 role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

   Go to project selector

2. Verify that billing is enabled for your Cloud de Confiance project.

Roles obligatorios

Si quieres obtener los permisos que necesitas para completar este instructivo, pídele a tu administrador que te otorgue los siguientes roles de IAM en el proyecto:

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

Para obtener más información sobre cómo otorgar roles, consulta Administra el acceso a proyectos, carpetas y organizaciones.

También puedes obtener los permisos necesarios a través de roles personalizados o cualquier otro rol predefinido.

Programa transferencias de datos recurrentes

Para mantener BigQuery actualizado con los datos de marketing más recientes de tu fuente de datos, configura transferencias de datos recurrentes con el Servicio de transferencia de datos de BigQuery para extraer y cargar datos según una programación.

En este instructivo, usarás Google Ads como fuente de datos de ejemplo. Para obtener una lista completa de las fuentes de datos compatibles con el Servicio de transferencia de datos de BigQuery, consulta Fuentes de datos compatibles.

1. Ve a la página Transferencia de datos en la Cloud de Confiance consola de.

   Ir a Transferencias de datos

2. Haz clic en add Crear transferencia.

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

4. En la sección Detalles de fuente de datos (Data source details):

   1. En ID de cliente, ingresa tu ID de cliente de Google Ads.
   2. En Tipo de informe, selecciona Estándar. El informe estándar incluye el conjunto estándar de informes y campos, como se detalla en Transformación de informes de Google Ads.
      • En Período de actualización, ingresa 5.

5. En la sección Configuración de destino, en Conjunto de datos, selecciona el conjunto de datos que creaste para almacenar tus datos.

6. En la sección Nombre de configuración de la transferencia (Transfer config name), en Nombre visible (Display name), ingresa Marketing tutorial.

7. En la sección Opciones de programación , haz lo siguiente:

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

8. Haz clic en Guardar.

Después de guardar la configuración, el Servicio de transferencia de datos de BigQuery comienza la transferencia de datos. Según la configuración de la transferencia, la transferencia de datos se ejecuta una vez al día a las 8:00 a.m. UTC y extrae datos de Google Ads de los últimos cinco días.

Puedes supervisar los trabajos de transferencia en curso para verificar el estado de cada transferencia de datos.

Consultar datos de tablas

Cuando tus datos se transfieren a BigQuery, se escriben en tablas particionadas por tiempo de transferencia. Para obtener más información, consulta Introducción a tablas con particiones.

Si consultas tus tablas directamente en lugar de usar las vistas generadas de manera automática, debes usar la seudocolumna _PARTITIONTIME en tu consulta. Para obtener más información, consulta tablas particionadas.

En las siguientes secciones, se muestran consultas de muestra que puedes usar para examinar los datos transferidos.

Rendimiento de la campaña

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

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 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'

Reemplaza lo siguiente:

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

Recuento de palabras clave

La siguiente consulta de muestra analiza las palabras clave por campaña, grupo de anuncios y estado de las palabras clave. Esta consulta usa la función KeywordMatchType. Los tipos de concordancia de palabras clave te ayudan a controlar cuáles son las búsquedas que activan la publicación de tu anuncio. Para obtener más información sobre las opciones de coincidencia de palabras clave, consulta Acerca de las opciones de concordancia de palabras clave.

  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 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'

Reemplaza lo siguiente:

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

Crea 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 transformar periódicamente tus datos de análisis 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 Cloud de Confiance consola de, ve a la página Dataform.

   Ir a Dataform

2. Haz clic en add Crear repositorio.

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

   1. En el campo ID del repositorio, ingresa 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 Acerca de los repositorios de Dataform.

Crea e inicializa 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 dentro de tu repositorio antes de confirmar y enviar los cambios al repositorio.

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

   Ir a Dataform

2. Haz clic en marketing-tutorial-repository.

3. Haz clic en add Crear lugar de trabajo de desarrollo.

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

   1. En el campo ID del lugar de trabajo, ingresa 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 Lugares 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 Descripción general de los espacios de trabajo de desarrollo.

Declara tu tabla de Google Ads como fuente de la tabla

Para conectar tu tabla de Google Ads recién transferida a Dataform, declárala como fuente de datos. Para ello, sigue estos pasos:

Crea 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 Cloud de Confiance consola de, 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 un archivo nuevo, haz lo siguiente:

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

Declara una fuente de datos

Edita 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 tu espacio de trabajo de desarrollo, en el panel Archivos, haz clic en tu archivo SQLX para la declaración de la fuente de datos.

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

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

Define tu transformación

Para definir tus transformaciones de datos, crea un archivo SQLX en el directorio definitions/. En este instructivo, crearás una transformación diaria que agregue métricas como clics, impresiones, costos y conversiones con un archivo llamado daily_performance.sqlx.

Crea el archivo SQLX de transformación

1. En el panel Archivos, junto a definitions/, haz clic en el menú more_vert Más y, luego, selecciona Crear archivo.
2. En el campo Agregar una ruta de archivo, ingresa definitions/daily_performance.sqlx.
3. Haz clic en Crear archivo.

Define el archivo SQLX de transformación

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

2. Selecciona daily_performance.sqlx y, luego, ingresa 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

Confirma y envía los cambios

Después de realizar los cambios en tu espacio de trabajo de desarrollo, puedes confirmarlos y enviarlos a tu repositorio. Para ello, sigue estos pasos:

1. En el espacio de trabajo marketing-tutorial-workspace, haz clic en Confirmar 1 cambio.
2. En el panel Confirmación nueva, ingresa una descripción de la confirmación en el campo Agregar un mensaje de confirmación.
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 envíen correctamente al repositorio, aparecerá el mensaje El espacio de trabajo está actualizado.

Programa la transformación de datos

Después de definir el archivo de transformación de datos, programa las transformaciones de datos.

Crea un lanzamiento de producción

Un lanzamiento de producción en Dataform garantiza que tu entorno se actualice de manera coherente con los resultados de tus transformaciones de datos. En los siguientes pasos, se muestra cómo especificar la rama main del repositorio marketing-tutorial-repository para almacenar tus transformaciones de datos:

1. En la Cloud de Confiance consola de, 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 lanzamiento de producción.

5. En el panel Crear configuración de lanzamiento, establece la siguiente configuración:

   1. En el campo ID de lanzamiento, ingresa transformations.
   2. En el campo Git commitish, deja el valor predeterminado main .
   3. En la sección Frecuencia de programación, selecciona A pedido.

6. Haz clic en Crear.

Crea una configuración de flujo de trabajo

Una vez que hayas creado un lanzamiento de producción, podrás crear una configuración de flujo de trabajo que ejecute tus transformaciones de datos según una programación especificada en tu repositorio. En los siguientes pasos, se muestra cómo programar transformaciones diarias desde el archivo transformations:

1. En la Cloud de Confiance consola de, 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 Configuración del 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, ingresa transformations.

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

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

8. En la sección Frecuencia de programación, haz 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 para ejecutar, selecciona Diariamente.

11. Haz clic en Crear.

La configuración del flujo de trabajo que creaste ejecuta el resultado de la compilación más reciente que creó la configuración de lanzamiento de transformations.

Limpia

Sigue estos pasos para evitar que se apliquen cargos a tu Cloud de Confiance cuenta por los recursos que usaste en esta página.

Borra el conjunto de datos creado en BigQuery

Para evitar que se generen cargos por los recursos de BigQuery, borra el conjunto de datos llamado dataform.

1. En la Cloud de Confiance consola de, ve a la BigQueryBigQuery.

   Ir a BigQuery

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

3. Haz clic en el menú more_vert Acciones y, luego, selecciona Borrar.

4. En el cuadro de diálogo Borrar conjunto de datos , ingresa delete en el campo y, luego, haz clic en Borrar.

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

La creación de espacios de trabajo de desarrollo de Dataform no genera costos, pero para borrar el espacio de trabajo de desarrollo, puedes seguir estos pasos:

1. En la Cloud de Confiance consola de, 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 Configuración de lanzamiento, haz clic en el menú more_vert Más junto a la production configuración y, luego, en Borrar.

5. En la sección Configuración del flujo de trabajo, haz clic en el menú more_vert Más junto a la transformations configuración y, luego, en Borrar.

6. En la pestaña Lugares de trabajo de desarrollo, haz clic en el menú more_vert Más de quickstart-workspace y, luego, selecciona Borrar.

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

Borra el repositorio de Dataform

La creación de repositorios de Dataform no genera costos, pero para borrar el repositorio, puedes seguir estos pasos:

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

   Ir a Dataform

2. Para las quickstart-repository, haz clic en el more_vert Más menú, y, luego, selecciona Borrar.

3. En la ventana Borrar repositorio, ingresa el nombre del repositorio para confirmar su eliminación.

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