ELT-Pipeline für Marketinganalysedaten erstellen

In dieser Anleitung erfahren Sie, wie Sie einen ELT-Workflow einrichten, mit dem Marketinganalysedaten in BigQuery extrahiert, geladen und transformiert werden.

In einem typischen ELT-Workflow werden regelmäßig neue Kundendaten aus Ihrer Datenquelle extrahiert und in BigQuery geladen. Die unstrukturierten Daten werden dann in aussagekräftige Messwerte umgewandelt. In dieser Anleitung erstellen Sie einen ELT-Workflow, indem Sie mit dem BigQuery Data Transfer Service eine Datenübertragung für Marketinganalysen einrichten. Anschließend planen Sie, dass Dataform regelmäßig Transformationen für die Daten ausführt.

In dieser Anleitung verwenden wir Google Ads als Datenquelle. Sie können aber auch eine der anderen Datenquellen verwenden, die vom BigQuery Data Transfer Service unterstützt werden.

Hinweis

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.

Erforderliche Rollen

Bitten Sie Ihren Administrator, Ihnen die folgenden IAM-Rollen für das Projekt zuzuweisen, um die Berechtigungen zu erhalten, die Sie zum Ausführen dieser Anleitung benötigen:

• BigQuery-Administrator (roles/bigquery.admin)
• Dataform-Administrator (roles/dataform.admin)

Weitere Informationen zum Zuweisen von Rollen finden Sie unter Zugriff auf Projekte, Ordner und Organisationen verwalten.

Sie können die erforderlichen Berechtigungen auch über benutzerdefinierte Rollen oder andere vordefinierte Rollen erhalten.

Wiederkehrende Datenübertragungen planen

Damit BigQuery immer die neuesten Marketingdaten aus Ihrer Datenquelle enthält, richten Sie mit dem BigQuery Data Transfer Service wiederkehrende Datenübertragungen ein, um Daten nach Zeitplan zu extrahieren und zu laden.

In dieser Anleitung verwenden wir Google Ads als Beispieldatenquelle. Eine vollständige Liste der Datenquellen, die vom BigQuery Data Transfer Service unterstützt werden, finden Sie unter Unterstützte Daten quellen.

1. Rufen Sie in der Cloud de Confiance Console die Seite „Datenübertragungen“ auf.

   Zu „Datenübertragungen”

2. Klicken Sie auf add Übertragung erstellen.

3. Wählen Sie im Abschnitt Quelltyp als Quelle die Option Google Ads aus.

4. Im Abschnitt Data source details (Details zur Datenquelle):

   1. Geben Sie als Kundennummer Ihre Google Ads-Kundennummer ein.
   2. Wählen Sie unter Berichtstyp die Option Standard aus. Der Standardbericht umfasst die standardmäßigen Berichte und Felder, die unter Google Ads-Berichte umwandeln beschrieben sind.
      • Geben Sie für Aktualisierungsfenster den Wert 5 ein.

5. Wählen Sie im Abschnitt Zieleinstellungen für Dataset das Dataset aus, das Sie zum Speichern Ihrer Daten erstellt haben.

6. Geben Sie im Abschnitt Konfigurationsname für Übertragung für Anzeigename den Wert Marketing tutorial ein.

7. Im Abschnitt Zeitplanoptionen:

   • Wählen Sie für Wiederholungshäufigkeit die Option Tage aus.
   • Geben Sie für Um den Wert 08:00 ein.

8. Klicken Sie auf Speichern.

Nachdem Sie die Konfiguration gespeichert haben, beginnt der BigQuery Data Transfer Service mit der Datenübertragung. Entsprechend den Einstellungen in der Übertragungskonfiguration wird die Datenübertragung einmal täglich um 8:00 Uhr UTC ausgeführt und Daten aus Google Ads der letzten fünf Tage werden extrahiert.

Sie können laufende Übertragungsjobs überwachen, um den Status der einzelnen Datenübertragungen zu prüfen.

Tabellendaten abfragen

Bei der Übertragung Ihrer Daten in BigQuery werden die Daten in zeitpartitionierte Tabellen geschrieben. Weitere Informationen finden Sie unter Einführung in partitionierte Tabellen.

Wenn Sie die Tabellen direkt abfragen, anstatt die automatisch generierten Ansichten zu verwenden, müssen Sie in der Abfrage die Pseudospalte _PARTITIONTIME verwenden. Weitere Informationen finden Sie unter Partitionierte Tabellen abfragen.

In den folgenden Abschnitten finden Sie Beispielabfragen, mit denen Sie die übertragenen Daten untersuchen können.

Kampagnenleistung

Die folgende Beispielabfrage analysiert die Google Ads-Kampagnenleistung der letzten 30 Tage.

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'

Ersetzen Sie Folgendes:

• DATASET: der Name des Datasets, das Sie zum Speichern der übertragenen Tabelle erstellt haben
• CUSTOMER_ID: Ihre Google Ads-Kundennummer

Anzahl Keywords

Die folgende Beispielabfrage analysiert die Keywords unter Berücksichtigung der Kampagne, Anzeigengruppe und des Keyword-Status. Diese Abfrage verwendet die Funktion KeywordMatchType. Über die Keyword-Option können Sie definieren, welche Suchanfragen dazu führen, dass Ihre Anzeige angezeigt wird. Weitere Informationen finden Sie unter Keyword-Optionen.

  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'

Ersetzen Sie Folgendes:

• DATASET: der Name des Datasets, das Sie zum Speichern der übertragenen Tabelle erstellt haben
• CUSTOMER_ID: Ihre Google Ads-Kundennummer

Dataform-Repository erstellen

Nachdem Sie die Datenübertragungskonfiguration zum Übertragen der neuesten Daten aus Google Ads erstellt haben, richten Sie Dataform ein, um Ihre Marketinganalysedaten regelmäßig zu transformieren. Mit Dataform können Sie regelmäßige Datentransformationen planen und diese Transformationen mit SQL definieren, während Sie mit anderen Datenanalysten zusammenarbeiten.

Erstellen Sie ein Dataform-Repository, um die SQLX-Abfragen zu speichern, aus denen Ihr Transformationscode besteht.

1. Rufen Sie in der Cloud de Confiance Console die Seite Dataform auf.

   Zu Dataform

2. Klicken Sie auf add Repository erstellen.

3. Führen Sie auf der Seite Repository erstellen die folgenden Schritte aus:

   1. Geben Sie im Feld Repository-ID den Wert marketing-tutorial-repository ein.
   2. Wählen Sie in der Liste Region eine Region aus.
   3. Klicken Sie auf Erstellen.

Das Repository marketing-tutorial-repository wird jetzt in der Liste der Dataform-Repositories angezeigt.

Weitere Informationen zu Dataform-Repositories finden Sie unter Dataform-Repositories.

Entwicklungsarbeitsbereich für Dataform erstellen und initialisieren

Erstellen Sie einen Dataform-Entwicklungsarbeitsbereich, damit Sie am Transformationscode in Ihrem Repository arbeiten können, bevor Sie Ihre Änderungen übernehmen und in Ihr Repository übertragen.

1. Rufen Sie in der Cloud de Confiance Console die Seite Dataform auf.

   Zu Dataform

2. Klicken Sie auf marketing-tutorial-repository.

3. Klicken Sie auf add Entwicklungs arbeitsbereich erstellen.

4. Führen Sie im Fenster Entwicklungsarbeitsbereich erstellen die folgenden Schritte aus:

   1. Geben Sie im Feld Workspace-ID den Wert marketing-tutorial-workspace ein.
   2. Klicken Sie auf Erstellen.

   Die Seite „Entwicklungsarbeitsbereich“ wird angezeigt.

5. Klicken Sie auf Arbeitsbereich initialisieren.

Der Entwicklungsarbeitsbereich marketing-tutorial-workspace wird jetzt in Ihrem Repository marketing-tutorial-repository auf dem Tab Entwicklungsarbeitsbereiche zusammen mit zwei Beispieldateien im Verzeichnis definitions mit den Namen *first_view.sqlx und *second_view.sqlx angezeigt.

Weitere Informationen zu Dataform-Entwicklungsarbeitsbereichen, siehe Übersicht über Entwicklungsarbeitsbereiche.

Google Ads-Tabelle als Tabellenquelle deklarieren

Verbinden Sie Ihre neu übertragene Google Ads-Tabelle mit Dataform, indem Sie sie mit den folgenden Schritten als Datenquelle deklarieren:

SQLX-Datei für die Deklaration der Datenquelle erstellen

In Dataform deklarieren Sie ein Datenquellenziel, indem Sie eine SQLX-Datei im Verzeichnis definitions/ erstellen:

1. Rufen Sie in der Cloud de Confiance Console die Seite Dataform auf.

   Zur Seite „Dataform“

2. Wählen Sie marketing-tutorial-repository aus.

3. Wählen Sie marketing-tutorial-workspace aus.

4. Klicken Sie im Bereich Dateien neben definitions/ auf das Menü Mehr.

5. Klicken Sie auf Datei erstellen.

6. Führen Sie im Bereich Neue Datei erstellen die folgenden Schritte aus:

   1. Geben Sie im Feld Dateipfad hinzufügen nach definitions/ den Namen definitions/googleads-declaration.sqlx ein.
   2. Klicken Sie auf Datei erstellen.

Datenquelle deklarieren

Bearbeiten Sie die Datei definitions/googleads-declaration.sqlx, um eine übertragene Google Ads-Tabelle als Datenquelle zu deklarieren. In diesem Beispiel wird die Tabelle ads_Campaign als Datenquelle deklariert:

1. Klicken Sie in Ihrem Entwicklungsarbeitsbereich im Bereich Dateien auf die SQLX-Datei für die Deklaration der Datenquelle.

2. Geben Sie in die Datei das folgende Code-Snippet ein:

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

Transformation definieren

Definieren Sie Ihre Datentransformationen, indem Sie eine SQLX-Datei im Verzeichnis definitions/ erstellen. In dieser Anleitung erstellen Sie eine tägliche Transformation, die Messwerte wie Klicks, Impressionen, Kosten und Conversions mit einer Datei namens daily_performance.sqlx zusammenfasst.

SQLX-Datei für die Transformation erstellen

1. Klicken Sie im Bereich Dateien neben definitions/ auf das more_vert Menü „Mehr“ und wählen Sie dann Datei erstellen aus.
2. Geben Sie im Feld Dateipfad hinzufügen den Wert definitions/daily_performance.sqlx ein.
3. Klicken Sie auf Datei erstellen.

SQLX-Datei für die Transformation definieren

1. Maximieren Sie im Bereich Dateien das Verzeichnis definitions/.

2. Wählen Sie daily_performance.sqlx aus und geben Sie die folgende Abfrage ein:

       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

Änderungen übernehmen und übertragen

Nachdem Sie Ihre Änderungen im Entwicklungsarbeitsbereich vorgenommen haben, können Sie sie mit den folgenden Schritten übernehmen und in Ihr Repository übertragen:

1. Klicken Sie im Arbeitsbereich marketing-tutorial-workspace auf 1 Änderung übernehmen.
2. Geben Sie im Bereich Neuer Commit im Feld Commit-Nachricht hinzufügen eine Commit-Beschreibung ein.
3. Klicken Sie auf Alle Änderungen übernehmen.
4. Klicken Sie im Arbeitsbereich marketing-tutorial-workspace auf In Standardzweig übertragen.

Nachdem Ihre Änderungen erfolgreich in Ihr Repository übertragen wurden, wird die Meldung Arbeitsbereich ist auf dem neuesten Stand angezeigt.

Datentransformation planen

Nachdem Sie die Datentransformationsdatei definiert haben, planen Sie die Datentransformationen.

Produktionsrelease erstellen

Ein Produktionsrelease in Dataform sorgt dafür, dass Ihre Umgebung immer mit den Ergebnissen Ihrer Datentransformationen aktualisiert wird. In den folgenden Schritten wird beschrieben, wie Sie den Zweig main des Repositorys marketing-tutorial-repository angeben, um Ihre Datentransformationen zu speichern:

1. Rufen Sie in der Cloud de Confiance Console die Seite Dataform auf.

   Zur Seite „Dataform“

2. Wählen Sie marketing-tutorial-repository aus.

3. Klicken Sie auf den Tab Releases und Planung.

4. Klicken Sie auf Produktionsrelease erstellen.

5. Konfigurieren Sie im Bereich Releasekonfiguration erstellen die folgenden Einstellungen:

   1. Geben Sie im Feld Veröffentlichungs-ID den Wert transformations ein.
   2. Übernehmen Sie im Feld Git commitish den Standardwert main .
   3. Wählen Sie im Abschnitt Planungshäufigkeit die Option Bei Bedarf aus.

6. Klicken Sie auf Erstellen.

Workflowkonfiguration erstellen

Nachdem Sie ein Produktionsrelease erstellt haben, können Sie eine Workflowkonfiguration erstellen, mit der Ihre Datentransformationen nach einem bestimmten Zeitplan in Ihrem Repository ausgeführt werden. In den folgenden Schritten wird beschrieben, wie Sie tägliche Transformationen aus der Datei transformations planen:

1. Rufen Sie in der Cloud de Confiance Console die Seite Dataform auf.

   Zur Seite „Dataform“

2. Wählen Sie marketing-tutorial-repository aus.

3. Klicken Sie auf den Tab Releases und Planung.

4. Klicken Sie im Abschnitt Workflowkonfigurationen auf Erstellen.

5. Geben Sie im Bereich Workflowkonfiguration erstellen im Feld Konfigurations-ID den Wert transformations ein.

6. Wählen Sie im Menü Releasekonfiguration die Option transformations aus.

7. Wählen Sie unter Authentifizierung die Option Mit Nutzeranmeldedaten ausführen aus.

8. Führen Sie im Abschnitt Planungshäufigkeit folgende Schritte aus:

   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. Klicken Sie auf Auswahl von Tags.

10. Wählen Sie im Feld Auszuführende Tags auswählen die Option Täglich aus.

11. Klicken Sie auf Erstellen.

Die von Ihnen erstellte Workflowkonfiguration führt jeden Tag um 16:00 Uhr UTC+1 das gesamte aktuelle Kompilierungsergebnis aus, das von der Releasekonfiguration transformations erstellt wird.

Bereinigen

Mit den folgenden Schritten vermeiden Sie, dass Ihrem Cloud de Confiance Konto die auf dieser Seite verwendeten Ressourcen in Rechnung gestellt werden:

In BigQuery erstelltes Dataset löschen

Löschen Sie das Dataset dataform, um zu vermeiden, dass für BigQuery-Assets Gebühren in Rechnung gestellt werden.

1. Rufen Sie in der Cloud de Confiance Console die Seite BigQuery auf.

   BigQuery aufrufen

2. Maximieren Sie im Bereich Explorer Ihr Projekt und wählen Sie dataform aus.

3. Klicken Sie auf das Menü more_vert Aktionen und wählen Sie dann Löschen aus.

4. Geben Sie im Dialogfeld Dataset löschen den Wert delete in das Feld ein und klicken Sie auf Löschen.

Dataform-Entwicklungsarbeitsbereich und -Konfigurationen löschen

Für das Erstellen von Dataform-Entwicklungsarbeitsbereichen fallen keine Kosten an. So löschen Sie den Entwicklungsarbeitsbereich:

1. Rufen Sie in der Cloud de Confiance Console die Seite Dataform auf.

   Zu Dataform

2. Klicken Sie auf quickstart-repository.

3. Klicken Sie auf den Tab Releases und Planung.

4. Klicken Sie im Abschnitt Releasekonfigurationen neben der production Konfiguration auf das Menü Mehr more_vert und dann auf Löschen.

5. Klicken Sie im Abschnitt Workflowkonfigurationen neben der transformations Konfiguration auf das Menü Mehr more_vert und dann auf Löschen.

6. Klicken Sie auf dem Tab Entwicklungsarbeitsbereiche neben quickstart-workspace auf das Menü more_vert Mehr und wählen Sie dann Löschen aus.

7. Klicken Sie zur Bestätigung auf Löschen.

Dataform-Repository löschen

Für das Erstellen von Dataform-Repositories fallen keine Kosten an. So löschen Sie das Repository:

1. Rufen Sie in der Cloud de Confiance Console die Seite Dataform auf.

   Zu Dataform

2. Klicken Sie neben quickstart-repository auf das Menü more_vert Mehr und wählen Sie dann Löschen aus.

3. Geben Sie im Fenster Repository löschen den Namen des Repositorys ein, um das Löschen zu bestätigen.

4. Klicken Sie zur Bestätigung auf Löschen.