Cookbook zu BigQuery

Diese Funktion ist nur in Analytics 360 verfügbar, einem Bestandteil der Google Marketing Platform.
Weitere Informationen zur Google Marketing Platform.

Dieser Artikel enthält Beispiele zur Erstellung von Abfragen der Analytics-Daten, die Sie in BigQuery exportieren. Darüber hinaus stellen wir Ihnen ein Beispiel-Dataset zur Verfügung, damit Sie die in diesem Artikel behandelten Abfragen üben können.

Themen in diesem Artikel:

Abfrageoptimierung

Jede Abfrage, die Sie ausführen, beiträgt, schlägt in Ihrem monatlichen Datenverarbeitungskontingent zu Buche. Das heißt, wenn Sie irrelevante Felder auswählen, steigt die zu verarbeitende Datenmenge, und Sie verbrauchen mehr Kontingent als nötig. Mit optimierten Abfragen sorgen Sie dafür, dass Sie Ihr monatliches Datenverarbeitungskontingent effizient nutzen.

Weitere Informationen zu Preisen

Nur erforderliche Felder auswählen

Wenn Sie eine Abfrage erstellen, wählen Sie die relevanten Felder innerhalb der SELECT-Anweisung aus. Indem Sie keine irrelevanten Felder aufrufen, reduzieren Sie die Datenmenge und damit die für die Verarbeitung der Abfrage benötigte Zeit.

Beispiel: Vermeiden Sie es, den Platzhalteroperator zu verwenden.

Ungünstig: Mit Platzhalteroperator
SELECT *
FROM [Tabellenname];

 

Besser: Mit Feldnamen, um die Verarbeitung unnötiger Daten zu vermeiden
SELECT field1, field2
FROM [Tabellenname];

Caching ermöglichen

Vermeiden Sie möglichst die Verwendung von Funktionen als Felder. Funktionen (zum Beispiel NOW() oder TODAY()) geben variable Ergebnisse zurück, die eine Zwischenspeicherung der Abfragen verhindern, sodass die Verarbeitung länger dauert. Verwenden Sie stattdessen konkrete Uhrzeit- und Datumsangaben.

Im Cache gespeicherte Ergebnisse werden bei Abfragen mehrerer Tabellen mit einem Platzhalter derzeit nicht unterstützt, auch wenn die Option "Im Cache gespeicherte Ergebnisse verwenden" aktiviert ist. Wenn Sie die gleiche Platzhalterabfrage mehrmals ausführen, wird Ihnen jede Abfrage in Rechnung gestellt. Weitere Informationen

Zwischentabellen für häufig verwendete Unterabfragen verwenden

Abfragen, die Sie wiederholt als Unterabfragen verwenden, können Sie als Zwischentabelle speichern, indem Sie über den Abfrageergebnissen auf Als Tabelle speichern klicken. Verweisen Sie anschließend im FROM-Abschnitt der Abfrage auf diese Tabelle. Dadurch reduzieren Sie sowohl die zu verarbeitende Datenmenge als auch die Verarbeitungszeit.

Beispiel mit Zwischentabelle
SELECT field1, field2
FROM [Datensatzname.Tabellenname];

Fehlerbehebung

In BigQuery werden Fehler während der Codeerstellung behoben. Sie können die Fehlerbehebung in dem Fenster, in dem Sie die Abfrage erstellen, direkt unter der Abfrage verfolgen. Die Fehlerbehebung ist auch über die API mit der dryRun-Markierung verfügbar.

Gültige Abfragen sind grün markiert. Klicken Sie auf die Markierung, um die mit der Abfrage verarbeitete Datenmenge aufzurufen. Diese Funktion bietet Ihnen die Möglichkeit, Ihre Daten zu optimieren, bevor Sie die Abfrage tatsächlich ausführen, um unnötige Datenverarbeitung zu vermeiden.

Query Debugging - Success

 

Ungültige Abfragen sind rot markiert. Wenn Sie auf die Markierung klicken, werden Informationen zum Fehler angezeigt, einschließlich der betroffenen Zeile und Spalte. Im unten aufgeführten Beispiel ist die GROUP BY-Anweisung leer und der Fehler ist markiert.

Query Debugging - Error

 

Tipps und Best Practices

Beispiel-Dataset verwenden

In den folgenden Beispielen wird das Google Analytics-Beispiel-Dataset verwendet.

Wenn Sie die Abfragen mit eigenen Daten verwenden möchten, müssen Sie lediglich die Projekt- und Dataset-Namen in den Beispielen durch eigene Projekt- und Dataset-Namen ersetzen.

Standard SQL vs. alter SQL-Dialekt

In BigQuery werden zwei SQL-Dialekte unterstützt:

Im Hilfeartikel Zu Standard-SQL migrieren werden die Unterschiede zwischen den beiden Dialekten erläutert.

Standard-SQL ist jetzt der bevorzugte SQL-Dialekt für die Abfrage von in BigQuery gespeicherten Daten.

Unter Standard-SQL aktivieren finden Sie Informationen dazu, wie Standard-SQL in der BigQuery-Benutzeroberfläche, Befehlszeilenschnittstelle, API oder einer beliebig anderen Schnittstelle, die Sie verwenden, aktiviert wird.

Die einfachste Methode besteht darin, den Kommentar "standardSQL" am Anfang von Standard-SQL-Abfragen einzufügen. Sie sehen das in den folgenden Beispielen.

Beim alten SQL-Dialekt werden Google Analytics 360-Daten täglich in einer neuen Tabelle erfasst. Wenn Sie mehrere Tabellen auf einmal abfragen möchten, können Sie die Tabellennamen durch Kommas voneinander trennen, die Tabellenplatzhalterfunktion TABLE_DATE_RANGE nutzen oder mehrere durch Kommas voneinander getrennte TABLE_DATE_RANGE-Funktionen verwenden. Nachstehend finden Sie Beispiele zu diesen unterschiedlichen Methoden.

Mehrere Tabellen abfragen

In den folgenden Beispielen sehen Sie Standard-SQL-Abfragen und Abfragen im alten SQL-Dialekt für dieselben Daten.

Drei Tage

Standard-SQL
Drei Tage mit UNION ALL
#standardSQL
WITH ga_tables AS (
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM 'bigquery-public-data.google_analytics_sample.ga_sessions_20160801'
GROUP BY date

UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM 'bigquery-public-data.google_analytics_sample.ga_sessions_20160802'
GROUP BY date

UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM 'bigquery-public-data.google_analytics_sample.ga_sessions_20160803'
GROUP BY date

)
SELECT
date,
visits,
pageviews,
transactions,
revenue,
FROM ga_tables
ORDER BY date ASC
Alter SQL-Dialekt
Drei Tage mithilfe durch Kommas getrennter Tabellennamen
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
[bigquery-public-data.google_analytics_sample.ga_sessions_20160801],
[bigquery-public-data.google_analytics_sample.ga_sessions_20160802],
[bigquery-public-data.google_analytics_sample.ga_sessions_20160803]
GROUP BY
date
ORDER BY
date ASC

 

Letzte 1.095 Tage

Standard-SQL
Letzte 1.095 Tage mithilfe von _TABLE_SUFFIX
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM 'bigquery-public-data.google_analytics_sample.ga_sessions_*'
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1095 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
Alter SQL-Dialekt
Letzte 1.095 Tage mithilfe von TABLE_DATE_RANGE
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -1095, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

Letzte 36 Monate

Standard-SQL
Letzte 36 Monate mithilfe von _TABLE_SUFFIX
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM 'bigquery-public-data.google_analytics_sample.ga_sessions_*'
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 36 MONTH))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
Alter SQL-Dialekt
Letzte 36 Monate mithilfe von TABLE_DATE_RANGE
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -36, 'MONTH'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

Letzte drei Jahre

Standard-SQL
Letzte drei Jahre mithilfe von _TABLE_SUFFIX
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM 'bigquery-public-data.google_analytics_sample.ga_sessions_*'
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
Alter SQL-Dialekt
Letzte drei Jahre mithilfe von TABLE_DATE_RANGE
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -3, 'YEAR'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

Bestimmter Zeitraum

Standard-SQL
Bestimmter Zeitraum mithilfe von _TABLE_SUFFIX
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM 'bigquery-public-data.google_analytics_sample.ga_sessions_*'
WHERE
_TABLE_SUFFIX BETWEEN '20160801' AND '20170731'
GROUP BY date
ORDER BY date ASC
Alter SQL-Dialekt
Bestimmter Zeitraum mithilfe von TABLE_DATE_RANGE
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2016-08-01'), TIMESTAMP('2017-07-31')))
GROUP BY
date
ORDER BY
date ASC

Letzte drei Jahre sowie Daten für "Heute" (Tagesverlauf)

Standard-SQL
Letzte drei Jahre sowie Daten für "Heute" (Tagesverlauf) mithilfe von UNION ALL und _TABLE_SUFFIX
Hinweis: Dieses Beispiel funktioniert nicht mit dem öffentlichen Google Analytics-Dataset, da es derzeit keine Tabelle mit Tagesverlaufsdaten gibt.
#standardSQL
WITH ga_tables AS ( SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM 'bigquery-public-data.google_analytics_sample.ga_sessions_*'
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM 'bigquery-public-data.google_analytics_sample.ga_sessions_*'
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))
GROUP BY date
)
SELECT
date,
visits,
pageviews,
transactions,
revenue,
FROM ga_tables
ORDER BY date ASC

Alter SQL-Dialekt

Letzte drei Jahre sowie Daten für "Heute" (Tagesverlauf) mithilfe mehrerer TABLE_DATE_RANGE-Funktionen
Hinweis: Dieses Beispiel funktioniert nicht mit dem öffentlichen Google Analytics-Dataset, da es derzeit keine Tabelle mit Tagesverlaufsdaten gibt.
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -3, 'YEAR'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))),
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_intraday_],
DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'), CURRENT_TIMESTAMP()))
GROUP BY
date
ORDER BY
date ASC

 

 

Einfache Abfragebeispiele

In diesem Abschnitt wird erläutert, wie Sie einfache Abfragen mit Messwerten und Dimensionen aus Analytics-Daten erstellen.

Summe [Messwert] pro [Dimension]?

Es folgen Beispielskripts für die Frage: Wie viele Transaktionen wurden im Juli 2017 pro Browser generiert?

Standard-SQL

Anzahl der Transaktionen pro Browser insgesamt im Juli 2017
#standardSQL
SELECT
device.browser,
SUM ( totals.transactions ) AS total_transactions
FROM 'bigquery-public-data.google_analytics_sample.ga_sessions_*'
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
GROUP BY
device.browser
ORDER BY
total_transactions DESC

Alter SQL-Dialekt

Anzahl der Transaktionen pro Browser insgesamt im Juli 2017
SELECT
device.browser,
SUM ( totals.transactions ) AS total_transactions
FROM TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
GROUP BY
device.browser
ORDER BY
total_transactions DESC

 

 

Durchschnittliche Absprungrate pro [Dimension]?

Die tatsächliche Absprungrate ist definiert als die Anzahl der Besuche mit einem einzigen Seitenaufruf in Prozent. Es folgen Beispielskripts für die Frage: Was war die durchschnittliche Absprungrate pro Besucherquelle?

Standard-SQL

Absprungrate pro Besucherquelle im Juli 2017
#standardSQL
SELECT
Quelle,
total_visits,
total_no_of_bounces,
( ( total_no_of_bounces / total_visits ) * 100 ) AS bounce_rate
FROM (
SELECT
trafficSource.source AS source,
COUNT ( trafficSource.source ) AS total_visits,
SUM ( totals.bounces ) AS total_no_of_bounces
FROM 'bigquery-public-data.google_analytics_sample.ga_sessions_*'
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
GROUP BY
source )
ORDER BY
total_visits DESC

Alter SQL-Dialekt

Absprungrate pro Besucherquelle im Juli 2017
SELECT
Quelle,
total_visits,
total_no_of_bounces,
( ( total_no_of_bounces / total_visits ) * 100 ) AS bounce_rate
FROM (
SELECT
trafficSource.source AS source,
COUNT ( trafficSource.source ) AS total_visits,
SUM ( totals.bounces ) AS total_no_of_bounces
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
GROUP BY
source )
ORDER BY
total_visits DESC

 

 

Durchschnittliche Anzahl der Produktseitenaufrufe pro Käufertyp (Käufer im Vergleich zu Nutzern, die nichts kaufen)

Es folgen Beispielskripts für die Frage: Wie oft haben Nutzer, die im Juli 2017 etwas kauften, die Produktseiten durchschnittlich aufgerufen?

Standard-SQL

Durchschnittliche Anzahl der Produktseitenaufrufe durch Nutzer, die im Juli 2017 etwas kauften
#standardSQL
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM 'bigquery-public-data.google_analytics_sample.ga_sessions_*'
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.transactions >=1
GROUP BY
users )

Alter SQL-Dialekt

Durchschnittliche Anzahl der Produktseitenaufrufe durch Nutzer, die im Juli 2017 etwas kauften
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') ) WHERE
totals.transactions >=1
GROUP BY
users )

 

 

Es folgen Beispielskripts für die Frage: Wie oft haben Nutzer, die im Juli 2017 nichts kauften, die Produktseiten durchschnittlich aufgerufen?

Standard-SQL

Durchschnittliche Anzahl der Produktseitenaufrufe durch Nutzer, die im Juli 2017 nichts kauften
#standardSQL
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM 'bigquery-public-data.google_analytics_sample.ga_sessions_*'
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.transactions IS NULL
GROUP BY
users )

Alter SQL-Dialekt

Durchschnittliche Anzahl der Produktseitenaufrufe durch Nutzer, die im Juli 2017 nichts kauften
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') ) WHERE
totals.transactions IS NULL
GROUP BY
users )

 

 

Durchschnittliche Anzahl der Transaktionen pro Käufer

Es folgen Beispielskripts für die Frage: Was war die durchschnittliche Anzahl der Transaktionen pro Nutzer, die im Juli 2017 etwas gekauft haben?

Standard-SQL

Durchschnittliche Anzahl der Transaktionen pro Nutzer, die im Juli 2017 etwas gekauft haben
#standardSQL
SELECT
(SUM (total_transactions_per_user) / COUNT(fullVisitorId) ) AS avg_total_transactions_per_user
FROM (
SELECT
fullVisitorId,
SUM (totals.transactions) AS total_transactions_per_user
FROM
'bigquery-public-data.google_analytics_sample.ga_sessions_*'
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND totals.transactions IS NOT NULL
GROUP BY
fullVisitorId )

Alter SQL-Dialekt

Durchschnittliche Anzahl der Transaktionen pro Nutzer, die im Juli 2017 etwas gekauft haben
SELECT
(SUM (total_transactions_per_user) / COUNT(fullVisitorId) ) AS avg_total_transactions_per_user
FROM (
SELECT
fullVisitorId,
SUM (totals.transactions) AS total_transactions_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
totals.transactions IS NOT NULL
GROUP BY
fullVisitorId )

 

 

Durchschnittlicher ausgegebener Geldbetrag pro Sitzung

Es folgen Beispielskripts für die Frage: Welcher Geldbetrag wurde im Juli 2017 pro Sitzung durchschnittlich ausgegeben?

Standard-SQL

Durchschnittlich ausgegebener Geldbetrag pro Sitzung im Juli 2017
#standardSQL
SELECT
( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS
avg_revenue_by_user_per_visit
FROM (
SELECT
fullVisitorId,
SUM( totals.visits ) AS total_visits_per_user,
SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user
FROM
'bigquery-public-data.google_analytics_sample.ga_sessions_*'
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.visits > 0
AND totals.transactions >= 1
AND totals.transactionRevenue IS NOT NULL
GROUP BY
fullVisitorId )

Alter SQL-Dialekt

Durchschnittlich ausgegebener Geldbetrag pro Sitzung im Juli 2017
SELECT
( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS
avg_revenue_by_user_per_visit
FROM (
SELECT
fullVisitorId,
SUM( totals.visits ) AS total_visits_per_user,
SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
totals.visits > 0
AND totals.transactions >= 1
AND totals.transactionRevenue IS NOT NULL
GROUP BY
fullVisitorId )

 

 

Trefferreihenfolge

Es folgen Beispielskripts für die Frage: In welcher Reihenfolge wurden Seiten aufgerufen?

Standard-SQL

Reihenfolge der von Nutzern im Juli 2017 aufgerufenen Seiten
#standardSQL
SELECT
fullVisitorId,
visitId,
visitNumber,
hits.hitNumber AS hitNumber,
hits.page.pagePath AS pagePath
FROM
'bigquery-public-data.google_analytics_sample.ga_sessions_*',
UNNEST(hits) as hits
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
hits.type="PAGE"
ORDER BY
fullVisitorId,
visitId,
visitNumber,
hitNumber

Alter SQL-Dialekt

Reihenfolge der von Nutzern im Juli 2017 aufgerufenen Seiten
SELECT
fullVisitorId,
visitId,
visitNumber,
hits.hitNumber AS hitNumber,
hits.page.pagePath AS pagePath
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
hits.type="PAGE"
ORDER BY
fullVisitorId,
visitId,
visitNumber,
hitNumber

In dieser Abfrage wurden die Treffer auf den Typ PAGES beschränkt, da Ereignis- oder Transaktionsinteraktionen in diesem Fall nicht ausgegeben werden sollen. Jede Zeile der Ausgabe entspricht einem Seitenaufruf. Die Reihenfolge entspricht der Standardreihenfolge der Felder in der SELECT-Anweisung.

 

 

Mehrere benutzerdefinierte Dimensionen auf Treffer- oder Sitzungsebene

Benutzerdefinierte Dimension auf Trefferebene
SELECT fullVisitorId, visitId, hits.hitNumber, hits.time,
MAX(IF(hits.customDimensions.index=1,
hits.customDimensions.value,
NULL)) WITHIN hits AS customDimension1,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

 

Benutzerdefinierte Dimension auf Sitzungsebene
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

In jeder Abfrage:

Mit der SELECT-Anweisung werden die relevanten Dimensions- und Messwertfelder abgerufen.

MAX-Funktion:

  • Gibt die benutzerdefinierte Dimension als neue Spalte zurück. Sie können diese Funktion wiederholt aufrufen, um mehrere benutzerdefinierte Dimensionen als neue Spalten zurückzugeben.
  • Mit WITHIN hits und WITHIN RECORD wird in BigQuery die Bedingung in wiederholten Feldern ausgewertet.
  • Die Bedingung in MAX wird für jede benutzerdefinierte Dimension ausgewertet. Für alle Dimensionen, die nicht index=1 (Treffer) oder index=2 (Sitzungen) entsprechen, wird NULL zurückgegeben.
  • Gibt den Maximalwert zurück (benutzerdefinierte Dimension 1 für Treffer bzw. benutzerdefinierte Dimension 2 für Sitzungen). Alle anderen Werte sind NULL.

Erweiterte Abfragebeispiele

Wenn Sie mit einfachen Abfragen vertraut sind, können Sie sich den Abfragen zuwenden, die Sie mit den erweiterten Funktionen und Elementen in BigQuery erstellen können.

Produkte, die von Kunden gekauft wurden, die bereits Produkt A gekauft haben (klassischer E-Commerce)

Das folgende Skript ist ein grundlegendes Skript für die Frage: Welche anderen Produkte wurden von Kunden gekauft, die Produkt A gekauft haben?

Produkte, die von einem Kunden gekauft wurden, der bereits Produkt A gekauft hat (klassischer E-Commerce)
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM [Datensatzname]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [Datensatzname]
  WHERE hits.item.productName CONTAINS 'Product Item Name A'
   AND totals.transactions>=1
  GROUP BY fullVisitorId
)
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName != 'Product Item Name A'
GROUP BY other_purchased_products
ORDER BY quantity DESC;
  1. In der ersten Zeile erfassen Sie alle anderen Produkte, die ein Nutzer gekauft hat, und zählen diese mit der COUNT()-Funkt. Das Ergebnis wird in einem Feld mit der Bezeichnung quantity ausgegeben. Der zugehörige Artikel wird im Produktfeld mit der Bezeichnung other_purchased_products ausgegeben.
  2. In der grauen Unterabfrage wählen Sie nur die eindeutigen Nutzer (fullVisitorId) aus, die Transaktionen abgeschlossen haben (totals.transactions>=1) und die in einer Transaktion Produkt A gekauft haben (WHERE hits.item.productName CONTAINS 'Product Item Name A').

Mit den Regeln (WHERE- und AND-Anweisungen) in der übergeordneten (grünen) Abfrage werden die Werte in hits.item.productName verworfen, die NULL sind und Produkt A enthalten.

Es folgt ein Beispiel für die Abfrage: Welche anderen Produkte haben Kunden gekauft, die Lackmarker im 4er-Set gekauft haben?

Von einem Kunden gekaufte Produkte, der am 24. Juni 2013 Lackmarker im 4er-Set gekauft hat
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM [GoogleStore.ga_sessions_20130624]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [GoogleStore.ga_sessions_20130624]
  WHERE hits.item.productName CONTAINS 'Brighton Metallic Pens - Set of 4'
   AND totals.transactions>=1
  GROUP BY fullVisitorId
)
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName !='Brighton Metallic Pens - Set of 4'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

In Dremel/BigQuery wird mit WHERE expr IN ein JOIN ausgelöst und es gelten Größenbeschränkungen. Insbesondere die Größe der rechten Seite des JOIN (in diesem Fall die Anzahl der Besucher) darf 8 MB nicht überschreiten. In Dremel wird dies als broadcast JOIN bezeichnet. Wenn die Größe 8 MB überschreitet, müssen Sie einen shuffled JOIN auslösen. Hierzu können Sie die JOIN EACH-Syntax verwenden. Die Abfrage kann mit IN nicht ausgeführt werden. Sie kann jedoch mit einem JOIN neu geschrieben werden.

Produkte, die von Kunden gekauft wurden, die bereits Produkt A gekauft haben (erweiterter E-Commerce)

Diese Abfrage ähnelt der oben dargestellten grundlegenden Abfrage, ist aber für den erweiterten E-Commerce geeignet. Auch sie fragt mithilfe von TABLE_DATE_RANGE Daten über mehrere Tage ab.

Produkte, die von einem Kunden gekauft wurden, der bereits Produkt A gekauft hat (erweiterter E-Commerce)
SELECT hits.product.productSKU AS other_purchased_products, COUNT(hits.product.productSKU) AS quantity
FROM SELECT fullVisitorId, hits.product.productSKU, hits.eCommerceAction.action_type FROM TABLE_DATE_RANGE([73156703.ga_sessions_], TIMESTAMP('2015-07-01'), TIMESTAMP('2015-07-14')))
WHERE fullVisitorId IN (
  SELECT fullVisitorId
FROM TABLE_DATE_RANGE([73156703.ga_sessions_], TIMESTAMP('2015-07-01'), TIMESTAMP('2015-07-14'))
WHERE hits.product.productSKU CONTAINS '10 15103'
AND hits.eCommerceAction.action_type = '6'
GROUP BY fullVisitorId
)
AND hits.product.productSKU IS NOT NULL
AND hits.product.productSKU !='10 15103'
AND hits.eCommerceAction.action_type = '6'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

Durchschnittliche Anzahl der Nutzerinteraktionen vor dem Kauf

Dies ist ein Beispiel für eine Abfrage mit dem Befehl JOIN() [...] ON, die ausschließlich von Analytics-Daten abhängig ist.

Das folgende Skript ist ein grundlegendes Skript für die Frage: Wie hoch ist die durchschnittliche Anzahl der Nutzerinteraktionen vor einem Kauf?

Anzahl der Nutzerinteraktionen vor einem Kauf
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number
FROM (
 SELECT hits.item.productSku, SUM(hits.hitNumber) AS sum_of_hit_number
 FROM [GA-Datensatzname]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku
) AS ‘Alias_Name_1’
JOIN (
 SELECT hits.item.productSku, COUNT( fullVisitorId ) AS total_hits
 FROM [GA-Datensatzname]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku
) AS ‘Alias_Name_2’
ON Alias_Name_1.hits.item.productSku = Alias_Name_2.hits.item.productSku;
  1. Die erste Zeile enthält die mathematische Hauptoperation zur Berechnung der durchschnittlichen Anzahl an Nutzerinteraktionen pro Produkt. Mit dieser Abfrage wird ein JOIN zwischen den zwei Unterabfragen Alias_Name_1 und Alias_Name_2 erstellt.
  2. Mithilfe von Alias_Name_1 wird ein Feld erstellt, in dem mit der Summenfunktion SUM() alle Trefferzahlen für ein Produkt summiert werden.
  3. Mit Alias_Name_2 wird die Trefferanzahl pro Nutzer und Produkt ermittelt. Hierfür wird die COUNT()-Funktion verwendet.
  4. Die letzte Zeile enthält das Feld, das die beiden Datensätze des JOIN gemeinsam haben (hits.item.productSku).

Es folgt ein Beispiel für die Abfrage: Was ist die durchschnittliche Anzahl der Nutzerinteraktionen vor einem Kauf am 10. September 2013?

Anzahl der Nutzerinteraktionen vor einem Kauf am 10. September 2013
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number
FROM (
 SELECT hits.item.productSku, SUM(hits.hitNumber) AS sum_of_hit_number
 FROM [GoogleStore.ga_sessions_20130728]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku 
) AS one
JOIN (
 SELECT hits.item.productSku, COUNT( fullVisitorId ) AS total_hits
 FROM [GoogleStore.ga_sessions_20130728]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku
) AS two
ON one.hits.item.productSku = two.hits.item.productSku;

Prozentanteil des verkauften Lagerbestands nach Produkt

Dies ist ein Beispiel für eine Abfrage, bei der neben Analytics-Daten auch andere Daten berücksichtigt werden. Wenn Sie beide Datensätze miteinander kombinieren, erhalten Sie detailliertere Informationen zum Nutzerverhalten. Sie können Daten, die nicht aus Analytics stammen, in BigQuery importieren. Dies wirkt sich allerdings auf Ihre monatliche Gebühr zur Datenspeicherung aus.

Das folgende Skript ist ein grundlegendes Skript für die Frage: Wie viel Prozent des Lagerbestands wurden bei den jeweiligen Produkten verkauft?

Vom Lager verkaufter Prozentanteil pro Produkt
SELECT AnalyticsImport.product_data_20130728.productId, ((( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold )) * 100 ) AS percentage_of_stock_sold
FROM [Importierter Datensatz]
JOIN (
  SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity_sold
  FROM [GA-Datensatz]
  WHERE hits.item.productSku IS NOT NULL
   AND totals.transactions>=1
  GROUP BY hits.item.productSku
) AS ‘Alias_Name’
ON Imported_DataSet.’productId_field’ = Alias_Name.hits.item.productSku;
  1. Die Ausgabe der ersten Zeile sind zwei Felder: ein Feld mit allen Produkt-IDs und eines mit einer mathematischen Operation, die den vom Lager verkauften Prozentanteil der einzelnen Produkte berechnet.
  2. Da bei dieser Abfrage zwei Datensätze berücksichtigt werden, müssen Sie die Funktion JOIN() ... ON verwenden. Dieser Befehl verbindet die Zeilen aus den beiden Datensätzen basierend auf dem gemeinsamen Feld. Die beiden Datensätze sind in diesem Fall [Importierter_Datensatz] und 'Alias_Name'.
  3. [ ‘Imported_DataSet’ ] bezieht sich auf Daten, die nicht aus Analytics stammen. Dies ist der Datensatz, der das Messwertfeld mit dem Restbestand (Imported DataSet.'stock_left_field') sowie das Dimensionsfeld mit der Produkt-ID (Imported_DataSet.'productId_field') enthält.
  4. ‘Alias_Name’ ist der Name, der den von der grauen Unterabfrage zurückgegebenen Daten zugewiesen wird. Mit dieser Unterabfrage wird anhand von Analytics-Daten ermittelt, wie viel pro Produkt verkauft wurde.
  5. In der letzten Zeile wird mit der ON-Anweisung das gemeinsame Feld angegeben, über das die beiden Datensätze verbunden werden.

Bei vielen Variablen in dieser Abfrage ist der Datensatzname als Präfix vorangestellt (z. B. Importierter_Datensatz.’productId_field’, Alias_Name.quantity_sold). Hierdurch machen Sie deutlich, welches Feld Sie auswählen und zu welchem Datensatz es gehört.

Es folgt ein Beispiel für die Abfrage: Wie viel (in Prozent) wurde pro Produkt am 28. Juli 2013 verkauft?

Vom Lager verkaufter Prozentanteil pro Produkt am 28. Juli 2013
SELECT AnalyticsImport.product_data_20130728.productId, ( ( ( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold ) ) * 100 ) AS percentage_of_stock_sold
FROM AnalyticsImport.product_data_20130728
JOIN (
  SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity_sold
  FROM GoogleStore.ga_sessions_20130728
  WHERE hits.item.productSku IS NOT NULL
   AND totals.transactions>=1
  GROUP BY hits.item.productSku
) AS one
ON AnalyticsImport.product_data_20130728.productId = one.hits.item.productSku
ORDER BY percentage_of_stock_sold DESC;

Gewinn der einzelnen Produkte

Das folgende Skript ist ein grundlegendes Skript für die Frage: Wie rentabel sind die einzelnen Produkte?

Gewinn pro Produkt
SELECT Alias_Name.hits.item.productSku, ( Importierter_Datensatz.’product profit field’ * Alias_Name.quantity ) AS profit
FROM (
  SELECT Alias_Name.hits.item.productSku, Importierter_Datensatz.’product profit field’
  FROM [Importierter_Datensatz]
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM [GA-Datensatzname]
    WHERE hits.item.productSku IS NOT NULL
    GROUP BY hits.item.productSku
) AS ‘Alias_Name’
  ON Imported_DataSet.productId = Alias_Name.hits.item.productSku
);
  1. Die erste Zeile enthält die mathematische Operation zur Berechnung des Gesamtgewinns je Produkt.
  2. Für die graue Unterabfrage werden Daten verwendet, die nicht aus Analytics stammen. Hiermit wird ermittelt, wie hoch der Gewinn beim Verkauf eines Produkts ist.
  3. Die rote Unterabfrage basiert auf Analytics-Daten, die mit den Daten, die nicht aus Analytics stammen, verbunden werden. Hiermit wird die je Produkt verkaufte Stückzahl ermittelt.
  4. In der letzten Zeile wird das gemeinsame Feld der beiden Datensätze per ON-Anweisung angegeben. In diesem Fall ist dies die Produkt-ID.

Es folgt ein Beispiel für die Abfrage: Wie hoch war der Gewinn der einzelnen Produkte am 28. Juli 2013?

Gewinn pro Produkt am 28. Juli 2013
SELECT two.hits.item.productSku, ((AnalyticsImport.product_data_20130728.productprice-AnalyticsImport.product_data_20130728.productcost)*two.quantity) AS profit
FROM (
  SELECT two.hits.item.productSku, AnalyticsImport.product_data_20130728.productcost, two.quantity, AnalyticsImport.product_data_20130728.productprice
  FROM AnalyticsImport.product_data_20130728
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM GoogleStore.ga_sessions_20130728
    WHERE hits.item.productSku IS NOT NULL
    GROUP BY hits.item.productSku
) AS two
  ON AnalyticsImport.product_data_20130728.productId = two.hits.item.productSku
);

Der Gewinn entspricht der Differenz zwischen dem Verkaufspreis und den Produktionskosten. Diese Informationen werden in dem Datensatz gespeichert, der nicht aus Google Analytics stammt.

Nettogewinn der einzelnen Produkte (unter Berücksichtigung von Rückbuchungen)

Das folgende Skript ist ein grundlegendes Skript für die Frage: Wie hoch ist der Nettogewinn der einzelnen Produkte?

Nettogewinn pro Produkt
SELECT Alias_Name.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit
FROM (
  SELECT Alias_Name.hits.item.productSku, ( ( Importierter_Datensatz.productprice - Importierter_Datensatz.productcost ) * Alias_Name.quantity ) AS gross_profit, ( ( Importierter_Datensatz.refunddeliveryprice + Importierter_Datensatz.productprice ) * Importierter_Datensatz.refundquantity ) AS total_refund_revenue
  FROM (

    SELECT Alias_Name.hits.item.productSku, Importierter_Datensatz.productcost, Alias_Name.quantity, Importierter_Datensatz.productprice, Importierter_Datensatz.refunddeliveryprice, Importierter_Datensatz.refundquantity
    FROM [Name des importierten Datensatzes] AS 'Importierter_Datensatz'
    JOIN (
      
SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM [GA-Datensatzname]
      WHERE hits.item.productSku IS NOT NULL
       AND totals.transactions >=1
      GROUP BY hits.item.productSku
) AS 'Alias_Name'
    ON Imported_DataSet.productId = Alias_Name.hits.item.productSku )
);
  1. Diese Abfrage ist der Abfrage Wie rentabel sind die einzelnen Produkte? sehr ähnlich. Die einzigen Unterschiede befinden sich im Datensatz in der grauen Unterabfrage, der nicht aus Analytics stammt, und in der mathematischen Operation zur Berechnung des Nettogewinns in der ersten Zeile.
  2. Im Datensatz, der nicht aus Analytics stammt, wird auch der Gesamtbetrag der Rückbuchungen berechnet (in der SELECT-Anweisung der roten Unterabfrage).
  3. In Zeile 1 wird dann der Nettogewinn berechnet. Hierzu wird vom Bruttogewinn der Betrag für Rückbuchungen abgezogen.

Weitere Informationen zu dieser Abfrage finden Sie im Abschnitt zur Abfrage Wie rentabel sind die einzelnen Produkte?

Es folgt ein Beispiel für die Abfrage: Wie hoch war der Nettogewinn der einzelnen Produkte am 28. Juli 2013?

Nettogewinn pro Produkt am 28. Juli 2013
SELECT two.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit
FROM (
  SELECT two.hits.item.productSku, ( ( AnalyticsImport.product_data_20130728.productprice - AnalyticsImport.product_data_20130728.productcost ) * two.quantity ) AS gross_profit, ( ( AnalyticsImport.product_data_20130728.refunddeliveryprice + AnalyticsImport.product_data_20130728.productprice ) * AnalyticsImport.product_data_20130728.refundquantity ) AS total_refund_revenue
  FROM (

    SELECT two.hits.item.productSku, Analytics.product_data_20130728.productcost, two.quantity, AnalyticsImport.product_data_20130728.productprice, AnalyticsImport.product_data_20130728.refunddeliveryprice, AnalyticsImport.product_data_20130728.refundquantity
    FROM AnalyticsImport.product_data_20130728
    JOIN (

      SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM GoogleStore.ga_sessions_20130728
      WHERE hits.item.productSku IS NOT NULL
       AND totals.transactions >=1
      GROUP BY hits.item.productSku
) AS two
    ON AnalyticsImport.product_data_20130728.productId = two.hits.item.productSku )
);

Der Nettogewinn ergibt sich aus dem Gewinn eines Produkts abzüglich des für Rückbuchungen ausgegebenen Betrags. So berechnen Sie den gesamten Rückbuchungsbetrag für ein Produkt:

Rückbuchungsbetrag für ein Produkt = (Produktpreis + Rückbuchungskosten für das Produkt) * Menge der Produkte mit Rückbuchungen

War das hilfreich?
Wie können wir die Seite verbessern?