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, wird auf Ihr monatliches Kontingent zur Datenverarbeitung angerechnet. 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 können Sie Ihr monatliches Kontingent 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. Wenn 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 [table name];

 

Besser: mit Feldnamen, um die Verarbeitung nicht benötigter Daten zu vermeiden
SELECT field1, field2
FROM [table name];

Caching ermöglichen

Vermeiden Sie möglichst die Verwendung von Funktionen als Felder. Funktionen wie 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 beim 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 Abschnitt FROM der Abfrage auf diese Tabelle. Dadurch reduzieren Sie sowohl die zu verarbeitende Datenmenge als auch die Verarbeitungszeit.

Beispiel mit Zwischentabelle
SELECT field1, field2
FROM [Dataset name.table name];

Fehlerbehebung bei Abfragen

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 genau angegeben.

Query Debugging - Error

 

Tipps und Best Practices

Beispiel-Dataset verwenden

In den folgenden Szenarien wird das Beispiel-Dataset für Google Analytics 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 verwenden

In BigQuery werden zwei SQL-Dialekte unterstützt:

Im Artikel Zu Google-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.

In diesem Artikel finden Sie Informationen dazu, wie Standard-SQL in der BigQuery-Benutzeroberfläche, einer Kommandozeile, einer 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.

Bei Legacy-SQL werden Google Analytics 360-Daten täglich in einer neuen Tabelle erfasst. Wenn Sie mehrere Tabellen gleichzeitig abfragen möchten, können Sie die Tabellennamen durch Kommas voneinander trennen, den Tabellenplatzhalter TABLE_DATE_RANGE nutzen oder mehrere kommagetrennte TABLE_DATE_RANGE-Funktionen verwenden. Im Folgenden finden Sie Beispiele hierzu.

Mehrere Tabellen abfragen

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

3 Tage

Standard-SQL
3 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
Legacy-SQL
3 Tage mit kommagetrennten 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
Legacy-SQL
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
Legacy-SQL
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 3 Jahre

Standard-SQL
Letzte 3 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
Legacy-SQL
Letzte 3 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
Legacy-SQL
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 3 Jahre sowie Daten für „Heute“ (Tagesverlauf)

Standard-SQL
Letzte 3 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, weil es derzeit keine Tabelle mit Tagesverlaufsdaten umfasst.
#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

Legacy-SQL

Letzte 3 Jahre sowie Daten für „Heute“ (Tagesverlauf) mithilfe mehrerer TABLE_DATE_RANGE-Funktionen
Hinweis: Dieses Beispiel funktioniert nicht mit dem öffentlichen Google Analytics-Dataset, weil es derzeit keine Tabelle mit Tagesverlaufsdaten umfasst.
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

Legacy-SQL

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: Wie hoch war die Absprungrate pro Besucherquelle?

Standard-SQL

Absprungrate pro Besucherquelle im Juli 2017
#standardSQL
SELECT
source,
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

Legacy-SQL

Absprungrate pro Besucherquelle im Juli 2017
SELECT
source,
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 (Käufer und Nicht-Käufer)

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 )

Legacy-SQL

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 )

Legacy-SQL

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 )

Legacy-SQL

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 )

Legacy-SQL

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

Legacy-SQL

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, weil 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 Felder für Dimensionen und Messwerte 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 (hits) oder index=2 (sessions) entsprechen, wird NULL zurückgegeben.
  • Gibt den maximalen Wert 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 [‘Dataset Name’ ]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [‘Dataset Name’ ]
  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 werden alle anderen Produkte erfasst, die ein Nutzer gekauft hat, und mit der COUNT()-Funktion gezählt. 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 das 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 einemJOIN 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 bei ihr werden mithilfe von TABLE_DATE_RANGE Daten über mehrere Tage abgerufen.

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

Das 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 Dataset Name’ ]
 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 Dataset Name’ ]
 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 von 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;

Prozentsatz des verkauften Lagerbestands pro Produkt

Dies ist ein Beispiel für eine Abfrage, bei der neben Analytics-Daten auch andere Daten berücksichtigt werden. Wenn Sie beide Datasets 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?

Prozentsatz des verkauften Lagerbestands 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 [ ‘Imported_DataSet’ ]
JOIN (
  SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity_sold
  FROM [‘GA Dataset’ ]
  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, mit denen der vom Lager verkaufte Prozentsatz der einzelnen Produkte berechnet wird.
  2. Da bei dieser Abfrage zwei Datasets 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 Datasets sind in diesem Fall [ ‘Imported_DataSet’ ] und 'Alias_Name'.
  3. [ ‘Imported_DataSet’ ] bezieht sich auf Daten, die nicht aus Analytics stammen. Das ist das Dataset, das 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 grau formatierten 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 Datasets verbunden werden.

Bei vielen Variablen in dieser Abfrage ist der Name des Datasets als Präfix vorangestellt, z. B. Imported_DataSet.’productId_field’, Alias_Name.quantity_sold. Dadurch machen Sie deutlich, welches Feld Sie auswählen und zu welchem Dataset es gehört.

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

Prozentsatz des verkauften Lagerbestands 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, ( Imported_DataSet.’product profit field’ * Alias_Name.quantity ) AS profit
FROM (
  SELECT Alias_Name.hits.item.productSku, Imported_DataSet.’product profit field’
  FROM [ ‘Imported Data Set’ ]
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM [ ‘GA Dataset Name’ ]
    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 Datasets 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

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, ( ( Imported_DataSet.productprice - Imported_DataSet.productcost ) * Alias_Name.quantity ) AS gross_profit, ( ( Imported_DataSet.refunddeliveryprice + Imported_DataSet.productprice ) * Imported_DataSet.refundquantity ) AS total_refund_revenue
  FROM (
    SELECT Alias_Name.hits.item.productSku, Imported_DataSet.productcost, Alias_Name.quantity, Imported_DataSet.productprice, Imported_DataSet.refunddeliveryprice, Imported_DataSet.refundquantity
    FROM [ ‘Imported DataSet Name’ ] AS 'Imported_DataSet'
    JOIN (
      SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM [‘GA Dataset Name’ ]
      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 ähnelt der folgenden: Wie rentabel sind die einzelnen Produkte? 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 Dataset, das nicht aus Analytics stammt, wird auch der Gesamtbetrag der Erstattungen 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 Erstattungen 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 Erstattungen 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?

Benötigen Sie weitere Hilfe?

Mögliche weitere Schritte:

Im Hilfeforum posten Antworten von Forenmitgliedern erhalten
Kontakt Weitere Informationen angeben und Hilfe erhalten
true
learning path
Lernpfad auswählen

Unter google.com/analytics/learn finden Sie eine neue Hilfeseite, mit der Sie Google Analytics 4 optimal nutzen können. Die Website enthält Videos, Artikel, interaktive Abläufe sowie Links zum Google Analytics Discord, zum Blog, zum YouTube-Kanal und zum GitHub-Repository.

Jetzt loslegen

Suche
Suche löschen
Suche schließen
Hauptmenü
4370699978105194544
true
Suchen in der Hilfe
true
true
true
true
true
69256
false
false