16.2 Erstellen Sie Ihre erste Abfrage in BigQuery

Ziele

  • Die Benutzeroberfläche von BigQuery
  • SQL-Abfrage in BigQuery erstellen
  • Speichern Sie die Ergebnisse Ihrer SQL-Abfrage in einem Datensatz in BigQuery

Kontext

Wenn sich die Daten der Google Analytics in BigQuery befinden, werden Dimensionen, Metriken und andere Variablen verschachtelt. Außerdem werden die Daten zu Google Analytics täglich in verschiedene Tabellen geladen. Das bedeutet, dass der Versuch, Google Analytics-Tabellen in BigQuery direkt mit Adobe Experience Platform zu verbinden, sehr schwierig ist und keine gute Idee ist.

Die Lösung für dieses Problem besteht darin, die Daten von Google Analytics in ein lesbares Format umzuwandeln, um die Aufnahme in Adobe Experience Platform zu erleichtern.

Übung 16.2.1 - Erstellen Sie einen Datensatz, um neue BigQuery-Tabellen zu speichern

Gehen Sie zur BigQuery Console.

demo

In Explorer wird Ihre Projekt-ID angezeigt. Klicken Sie auf Ihre Projekt-ID (klicken Sie nicht auf den Dataset bigquery-public-data).

demo

Sie können sehen, dass es noch keinen Datensatz gibt, also erstellen wir jetzt einen.
Klicken Sie auf DATASET ERSTELLEN.

demo

Auf der rechten Seite Ihres Bildschirms sehen Sie das Menü Datensatz erstellen.

demo

Verwenden Sie für die Datenbestand-ID die folgende Benennungskonvention. Bei den anderen Feldern sollten Sie die Standardeinstellungen beibehalten.

Namenskonvention Beispiel
ldap_BigQueryDataSets vangeluw_BigQueryDataSets

demo

Klicken Sie anschließend auf Datensatz erstellen.

demo

Anschließend werden Sie mit dem erstellten Datensatz wieder in der BigQuery-Konsole gespeichert.

demo

Übung 16.2.2 - Erstellen Sie Ihre erste SQL BigQuery

Als Nächstes erstellen Sie Ihre erste Abfrage in BigQuery. Ziel dieser Abfrage ist es, den Google Analytics Beispieldaten zu entnehmen und sie so zu transformieren, dass sie in Adobe Experience Platform aufgenommen werden können. Wechseln Sie zur Registerkarte EDITOR.

demo

Bitte kopieren Sie die folgende SQL-Abfrage und fügen Sie sie in diesen Abfrage Editor ein. Lesen Sie die Abfrage und verstehen Sie die Google Analytics BigQuery Syntax.

SELECT
  CONCAT(fullVisitorId, CAST(hitTime AS String), '-', hitNumber) AS _id,
  TIMESTAMP(DATETIME(Year_Current, Month_Current, Day_Current, Hour, Minutes, Seconds)) AS timeStamp,
  fullVisitorId as GA_ID,
  -- Fake CUSTOMER ID
  CONCAT('3E-D4-',fullVisitorId, '-1W-93F' ) as customerID,
  Page,
  Landing_Page,
  Exit_Page,
  Device,
  Browser,
  MarketingChannel,
  TrafficSource,
  TrafficMedium,
  -- Enhanced Ecommerce
  TransactionID,
  CASE
      WHEN EcommerceActionType = '2' THEN 'Product_Detail_Views'
      WHEN EcommerceActionType = '3' THEN 'Adds_To_Cart'
      WHEN EcommerceActionType = '4' THEN 'Product_Removes_From_Cart'
      WHEN EcommerceActionType = '5' THEN 'Product_Checkouts'
      WHEN EcommerceActionType = '6' THEN 'Product_Refunds'
    ELSE
    NULL
  END
     AS Ecommerce_Action_Type,
  -- Entrances (metric)
  SUM(CASE
      WHEN isEntrance = TRUE THEN 1
    ELSE
    0
  END
    ) AS Entries,
    
--Pageviews (metric)
    COUNT(*) AS Pageviews,
    
 -- Exits 
    SUM(
    IF
      (isExit IS NOT NULL,
        1,
        0)) AS Exits,
        
 --Bounces
   SUM(CASE
      WHEN isExit = TRUE AND isEntrance = TRUE THEN 1
    ELSE
    0
  END
    ) AS Bounces,
        
  -- Unique Purchases (metric)
  COUNT(DISTINCT TransactionID) AS Unique_Purchases,
  -- Product Detail Views (metric)
  COUNT(CASE
      WHEN EcommerceActionType = '2' THEN fullVisitorId
    ELSE
    NULL
  END
    ) AS Product_Detail_Views,
  -- Product Adds To Cart (metric)
  COUNT(CASE
      WHEN EcommerceActionType = '3' THEN fullVisitorId
    ELSE
    NULL
  END
    ) AS Adds_To_Cart,
  -- Product Removes From Cart (metric)
  COUNT(CASE
      WHEN EcommerceActionType = '4' THEN fullVisitorId
    ELSE
    NULL
  END
    ) AS Product_Removes_From_Cart,
  -- Product Checkouts (metric)
  COUNT(CASE
      WHEN EcommerceActionType = '5' THEN fullVisitorId
    ELSE
    NULL
  END
    ) AS Product_Checkouts,
  -- Product Refunds (metric)
  COUNT(CASE
      WHEN EcommerceActionType = '7' THEN fullVisitorId
    ELSE
    NULL
  END
    ) AS Product_Refunds
  FROM (
  SELECT
    -- Landing Page (dimension)
    CASE
      WHEN hits.isEntrance = TRUE THEN hits.page.pageTitle
    ELSE NULL
  END
    AS Landing_page,
    
        -- Exit Page (dimension)
    CASE
      WHEN hits.isExit = TRUE THEN hits.page.pageTitle
    ELSE
    NULL
  END
    AS Exit_page,
    
    hits.page.pageTitle AS Page,
    hits.isEntrance,
    hits.isExit,
    hits.hitNumber as hitNumber,
    hits.time as hitTime,
    date as Fecha,
    fullVisitorId,
    visitStartTime,
    device.deviceCategory AS Device,
    device.browser AS Browser,
    channelGrouping AS MarketingChannel,
    trafficSource.source AS TrafficSource,
    trafficSource.medium AS TrafficMedium,
    hits.transaction.transactionId AS TransactionID,
    CAST(EXTRACT(YEAR FROM CURRENT_DATE()) AS INT64) AS Year_Current,
    CAST(EXTRACT(MONTH FROM CURRENT_DATE()) AS INT64) AS Month_Current,
     CAST(EXTRACT(DAY FROM CURRENT_DATE()) AS INT64) AS Day_Current,
    CAST(EXTRACT(DAY FROM DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY)) AS INT64) AS Day_Current_Before,
    CAST(FORMAT_DATE('%Y', PARSE_DATE("%Y%m%d", date)) AS INT64) AS Year,
  CAST(FORMAT_DATE('%m', PARSE_DATE("%Y%m%d",date)) AS INT64) AS Month,
  CAST(FORMAT_DATE('%d', PARSE_DATE("%Y%m%d",date)) AS INT64) AS Day,
    CAST(EXTRACT (hour FROM TIMESTAMP_SECONDS(hits.time)) AS INT64) AS Hour,
  CAST(EXTRACT (minute FROM TIMESTAMP_SECONDS(hits.time)) AS INT64) AS Minutes,
  CAST(EXTRACT (second FROM TIMESTAMP_SECONDS(hits.time)) AS INT64) AS SecondS,
    hits.eCommerceAction.action_type AS EcommerceActionType
  
  FROM
    `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
     UNNEST(hits) AS hits
  WHERE
    _table_suffix BETWEEN '20170101'
    AND '20170331'
    AND totals.visits = 1
    AND hits.type = 'PAGE'
    )
    
GROUP BY
  1,
  2,
  3,
  4,
  5,
  6,
  7,
  8,
  9,
  10,
  11,
  12,
  13,
  14
    
  ORDER BY 2 DESC

Wenn Sie bereit sind, klicken Sie auf Ausführen, um die Abfrage auszuführen:

demo

Die Ausführung der Abfrage kann einige Minuten dauern.

Nachdem die Abfrage ausgeführt wurde, sehen Sie die folgende Ausgabe in den Abfragen a1/>.

demo

Übung 16.2.3 - Speichern Sie die Ergebnisse Ihrer BigQuery SQL-Abfrage

Im nächsten Schritt speichern Sie die Ausgabe Ihrer Abfrage, indem Sie auf die Schaltfläche ERGEBNISSE SPEICHERN klicken.

demo

Als Speicherort für Ihre Ausgabe wählen Sie BigQuery table.

demo

Anschließend wird ein neues Popup angezeigt, in dem Ihr Projektname und Dataset-Name vorausgefüllt sind. Der Datensatzname sollte der Datensatz sein, den Sie zu Beginn dieser Übung mit der folgenden Benennungsregel erstellt haben:

Namenskonvention Beispiel
ldap_BigQueryDataSets vangeluw_BigQueryDataSets

Sie müssen jetzt einen Tabellennamen eingeben. Bitte verwenden Sie diese Benennungsregel:

Namenskonvention Beispiel
ldap_GAdataTableBigQuery vangeluw_GAdataTableBigQuery

demo

Klicken Sie auf SAVE.

Es kann einige Zeit dauern, bis die Daten in der von Ihnen erstellten Tabelle fertig sind. Aktualisieren Sie den Browser nach einigen Minuten. Anschließend sollte die Tabelle ldap_GAdataTableBigquery im Datensatz unter Explorer in Ihrem BigQuery-Projekt angezeigt werden.

demo

Sie fahren nun mit der nächsten Übung fort, bei der Sie diesen Tisch mit Adobe Experience Platform verbinden.

Nächster Schritt: 16.3 Verbinden Sie GCP & BigQuery mit Adobe Experience Platform

Zurück zu Modul 16

Zurück zu allen Modulen

Auf dieser Seite