Crea una canalización de ELT para los 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 estadísticas 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 para generar métricas significativas. En este instructivo, crearás un flujo de trabajo de ELT configurando una transferencia de datos de estadísticas de marketing con el Servicio de transferencia de datos de BigQuery. Luego, programas 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. 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.

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

    Go to project selector

  3. Verify that billing is enabled for your Google Cloud project.

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

    Go to project selector

  5. Verify that billing is enabled for your Google Cloud project.

    6. Roles requeridos

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

    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 un programa.

    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 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 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 la configuración de transferencia, en Nombre visible, ingresa Marketing tutorial.

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

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

    8. Haz clic en Guardar.

    Después de guardar la configuración, el Servicio de transferencia de datos de BigQuery comenzará la transferencia de datos. Según la configuración de la transferencia, esta se ejecuta una vez al día a las 8 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, visita la página 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.

    Console

    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'

    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.

    Console 

      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'

    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 que transforme 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 de SQLX que componen tu código de transformación.

    1. En la consola de Google Cloud , 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 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 que puedas trabajar en el código de transformación dentro de tu repositorio antes de confirmar y enviar tus cambios al repositorio.

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

      Ir a Dataform

    2. Haz clic en marketing-tutorial-repository.

    3. Haz clic en 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. Haga 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 lugares de trabajo de desarrollo de Dataform, consulta Descripción general de los lugares 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 una fuente de datos siguiendo 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 consola de Google Cloud , 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 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 una 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

    Define tus transformaciones de datos creando un archivo SQLX en el directorio definitions/. En este instructivo, crearás una transformación diaria que agrega 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ú Más y, luego, selecciona Crear archivo.
    2. En el campo Agregar una ruta de acceso al 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 siguiendo estos pasos:

    1. En el espacio de trabajo marketing-tutorial-workspace, haz clic en Confirmar 1 cambio.
    2. En el panel New commit, ingresa una descripción de la confirmación en el campo Add a commit message.
    3. Haz clic en Confirmar todos los cambios.
    4. En el espacio de trabajo marketing-tutorial-workspace, haz clic en Push to default branch.

    Después de que los cambios se envíen correctamente al repositorio, aparecerá el mensaje Workspace is up to date.

    Programa la transformación de tus datos

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

    Crea una versión de producción

    Una versión de producción en Dataform garantiza que tu entorno se actualice de forma 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 consola de Google Cloud , 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 Versiones 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 versión, 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 una versión de producción, puedes crear una configuración de flujo de trabajo que ejecute tus transformaciones de datos según un programa especificado en tu repositorio. En los siguientes pasos, se muestra cómo programar transformaciones diarias desde el archivo transformations:

    1. En la consola de Google Cloud , 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 Versiones 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 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 Diario.

    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

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

    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 consola de Google Cloud , 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, 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 lugar de trabajo de desarrollo y la configuración de Dataform

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

    1. En la consola de Google Cloud , 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 de production y, luego, en Borrar.

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

    6. En la pestaña Lugares de trabajo de desarrollo, haz clic en el menú 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 consola de Google Cloud , ve a la página Dataform.

      Ir a Dataform

    2. En quickstart-repository, haz clic en el menú Más 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.