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.
Gehen Sie zur BigQuery Console.
In Explorer wird Ihre Projekt-ID angezeigt. Klicken Sie auf Ihre Projekt-ID (klicken Sie nicht auf den Dataset bigquery-public-data).
Sie können sehen, dass es noch keinen Datensatz gibt, also erstellen wir jetzt einen.
Klicken Sie auf DATASET ERSTELLEN.
Auf der rechten Seite Ihres Bildschirms sehen Sie das Menü Datensatz erstellen.
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 |
Klicken Sie anschließend auf Datensatz erstellen.
Anschließend werden Sie mit dem erstellten Datensatz wieder in der BigQuery-Konsole gespeichert.
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.
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:
Die Ausführung der Abfrage kann einige Minuten dauern.
Nachdem die Abfrage ausgeführt wurde, sehen Sie die folgende Ausgabe in den Abfragen a1/>.
Im nächsten Schritt speichern Sie die Ausgabe Ihrer Abfrage, indem Sie auf die Schaltfläche ERGEBNISSE SPEICHERN klicken.
Als Speicherort für Ihre Ausgabe wählen Sie BigQuery table.
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 |
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.
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