Einfache Abfragen für den Export von Google Analytics-Ereignisdaten

Die Beispielabfragen auf dieser Seite beziehen sich auf die BigQuery-Ereignisexportdaten für Google Analytics.

Dataset anstelle des Beispiel-Datasets abfragen

Sofern nicht anders angegeben, werden für alle hier aufgeführten Abfragen Beispieldatasets verwendet, die gültige Ergebnisse liefern sollten. Wenn Sie die BigQuery-Daten für den Ereignisexport Ihrer eigenen Google Analytics-Property verwenden möchten, suchen Sie in jeder Abfrage nach dem Kommentar -- Replace table und ersetzen Sie die Beispielstabelle. So kopieren Sie den Tabellennamen aus Ihrem Dataset:

  1. Rufen Sie die BigQuery-Benutzeroberfläche auf und wählen Sie das Projekt aus, das Ihr Dataset enthält.
  2. Suchen Sie die Tabelle im Explorer.
  3. Klicken Sie rechts neben der Tabelle auf das Dreipunkt-Menü und dann auf ID kopieren.
  4. Fügen Sie den Tabellennamen anstelle der Beispielstabelle in die Abfrage ein.
  5. Ersetzen Sie den Datumsbereich der Tabelle durch *.

Wenn beispielsweise mit ID kopieren der BigQuery-Tabellenname my-first-gcp-project:analytics_28239234.events_20240718 kopiert wurde, ersetzen Sie:

  -- Replace table
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`

mit

  -- Replace table
  `my-first-gcp-project.analytics_28239234.events_*`

Einen bestimmten Zeitraum abfragen

Wenn Sie einen bestimmten Zeitraum aus einem BigQuery-Dataset für den Ereignisexport abfragen möchten, verwenden Sie die Pseudospalte _TABLE_SUFFIX in der WHERE-Klausel Ihrer Abfrage. Weitere Informationen finden Sie unter Ausgewählte Tabellen mit _TABLE_SUFFIX filtern.

Mit der folgenden Abfrage werden beispielsweise eindeutige Ereignisse nach Datum und Ereignisname für einen bestimmten Zeitraum und ausgewählte Ereignisse gezählt:

-- Example: Query a specific date range for selected events.
--
-- Counts unique events by date and by event name for a specifc period of days and
-- selected events(page_view, session_start, and purchase).

SELECT
  event_date,
  event_name,
  COUNT(*) AS event_count
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name IN ('page_view', 'session_start', 'purchase')
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
GROUP BY 1, 2;

Nutzerzahl, neue Nutzer und wiederkehrende Nutzer

  • Um die Gesamtzahl der aktiven Nutzer zu ermitteln, zählen Sie die Anzahl der eindeutigen user_id-Werte. Wenn Ihr Google Analytics-Client jedoch nicht mit jedem Treffer ein user_id zurücksendet oder Sie sich nicht sicher sind, zählen Sie die Anzahl der eindeutigen user_pseudo_id-Werte.
  • Zählen Sie für neue Nutzer die Anzahl der einzelnen Nutzer, deren event_name Folgendes ist:
  • Bei wiederkehrenden Nutzern wird die Anzahl der Nutzer gezählt, die im Zeitraum aktiv waren, aber ihre erste Sitzung nicht in diesem Zeitraum gestartet oder die App nicht zum ersten Mal in diesem Zeitraum gestartet haben (aktive Nutzer minus neue Nutzer).
-- Example: Get 'Total User' (Active User), 'New User', and 'Returning User' counts.

WITH
  UserInfo AS (
    SELECT
      user_pseudo_id,
      MAX(IF(event_name IN ('first_visit', 'first_open'), 1, 0)) AS is_new_user
    -- Replace table name.
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    -- Replace date range.
    WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20201130'
    GROUP BY 1
  )
SELECT
  COUNT(*) AS active_users,
  SUM(is_new_user) AS new_users,
  COUNT(*) - SUM(is_new_user) AS returning_users
FROM UserInfo;

Durchschnittliche Anzahl der Transaktionen pro Käufer

Mit der folgenden Abfrage wird die durchschnittliche Anzahl der Transaktionen pro Käufer ermittelt.

-- Example: Average number of transactions per purchaser.

SELECT
  COUNT(*) / COUNT(DISTINCT user_pseudo_id) AS avg_transaction_per_purchaser
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name IN ('in_app_purchase', 'purchase')
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201231';

Werte für einen bestimmten Ereignisnamen

Die folgende Abfrage zeigt die event_timestamp für alle purchase-Ereignisse und die zugehörigen Ereignisparameterwerte:

-- Example: Query values for a specific event name.
--
-- Queries the individual timestamps and values for all 'purchase' events.

SELECT
  event_timestamp,
  (
    SELECT COALESCE(value.int_value, value.float_value, value.double_value)
    FROM UNNEST(event_params)
    WHERE key = 'value'
  ) AS event_value
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'purchase'
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202';

Die vorherige Abfrage kann so geändert werden, dass anstelle einer Liste die Summe der Werte für den Ereignisparameter angezeigt wird:

-- Example: Query total value for a specific event name.
--
-- Queries the total event value for all 'purchase' events.

SELECT
  SUM(
    (
      SELECT COALESCE(value.int_value, value.float_value, value.double_value)
      FROM UNNEST(event_params)
      WHERE key = 'value'
    ))
    AS event_value
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'purchase'
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20201201' AND '20201202';

Die 10 am häufigsten in den Einkaufswagen gelegten Artikel

Die folgende Abfrage zeigt die 10 Artikel, die von den meisten Nutzern in den Warenkorb gelegt wurden.

-- Example: Top 10 items added to cart by most users.

SELECT
  item_id,
  item_name,
  COUNT(DISTINCT user_pseudo_id) AS user_count
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(items)
WHERE
  -- Replace date range.
  _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  AND event_name IN ('add_to_cart')
GROUP BY
  1, 2
ORDER BY
  user_count DESC
LIMIT 10;

Durchschnittliche Anzahl der Seitenaufrufe (Käufer und Nicht-Käufer)

Mit der folgenden Abfrage wird die durchschnittliche Anzahl der Seitenaufrufe nach Käufertyp (Käufer und Nicht-Käufer) von Nutzern ermittelt:

-- Example: Average number of pageviews by purchaser type.

WITH
  UserInfo AS (
    SELECT
      user_pseudo_id,
      COUNTIF(event_name = 'page_view') AS page_view_count,
      COUNTIF(event_name IN ('in_app_purchase', 'purchase')) AS purchase_event_count
    -- Replace table name.
    FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
    -- Replace date range.
    WHERE _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
    GROUP BY 1
  )
SELECT
  (purchase_event_count > 0) AS purchaser,
  COUNT(*) AS user_count,
  SUM(page_view_count) AS total_page_views,
  SUM(page_view_count) / COUNT(*) AS avg_page_views,
FROM UserInfo
GROUP BY 1;

Reihenfolge der Seitenaufrufe

In dieser Abfrage wird die Reihenfolge der Seitenaufrufe jedes Nutzers angezeigt. Die Ergebnisse werden anhand der folgenden Felder sortiert, damit Ereignisse in der Reihenfolge aufgeführt werden, in der sie für den Nutzer aufgetreten sind, auch wenn sie im selben Batch gesendet wurden:

  • user_pseudo_id
  • user_id
  • batch_page_id
  • batch_ordering_id
  • batch_event_index

Obwohl durch die Stichprobe die Ergebnisse auf page_view-Ereignisse beschränkt werden, können Sie dieselbe ORDER BY-Klausel verwenden, um alle Ereignisse richtig zu sortieren. Entfernen Sie dazu die Bedingung der WHERE-Klausel für event_name.

Die Abfrage zeigt auch, wie Sie nutzerdefinierte Funktionen GetParamString und GetParamInt verwenden können, um Duplikate zu vermeiden und Ihre Abfragen leichter verständlich und wartungsfreundlicher zu machen.

-- Example: Sequence of pageviews.

/** Temporary function to retrieve the string_value of an event parameter by event name. */
CREATE TEMP FUNCTION GetParamString(event_params ANY TYPE, param_name STRING)
AS ((SELECT ANY_VALUE(value.string_value) FROM UNNEST(event_params) WHERE key = param_name));

/** Temporary function to retrieve the int_value of an event parameter by event name. */
CREATE TEMP FUNCTION GetParamInt(event_params ANY TYPE, param_name STRING)
AS ((SELECT ANY_VALUE(value.int_value) FROM UNNEST(event_params) WHERE key = param_name));

SELECT
  user_pseudo_id,
  user_id,
  batch_page_id,
  batch_ordering_id,
  batch_event_index,
  event_name,
  GetParamInt(event_params, 'ga_session_id') as ga_session_id,
  GetParamString(event_params, 'page_location') as page_location,
  GetParamString(event_params, 'page_title') as page_title,
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
  event_name = 'page_view'
  -- Replace date range.
  AND _TABLE_SUFFIX BETWEEN '20240718' AND '20240731'
ORDER BY
  user_pseudo_id,
  user_id,
  batch_page_id,
  batch_ordering_id,
  batch_event_index;

Liste der Ereignisparameter

Mit der folgenden Abfrage werden alle Ereignisparameter in Ihrem Dataset aufgelistet:

-- Example: List all available event parameters and count their occurrences.

SELECT
  EP.key AS event_param_key,
  COUNT(*) AS occurrences
FROM
  -- Replace table name.
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`, UNNEST(event_params) AS EP
WHERE
  -- Replace date range.
  _TABLE_SUFFIX BETWEEN '20201201' AND '20201202'
GROUP BY
  event_param_key
ORDER BY
  event_param_key ASC;

Mit Google Ads beitreten

Wenn Sie zusätzliche Google Ads-Daten für Ihre Google Analytics-Ereignisse abrufen möchten, richten Sie den BigQuery Data Transfer Service für Google Ads ein und führen Sie dann collected_traffic_source.gclid aus Google Analytics-Ereignisdaten mit dem Feld gclid von ads_ClickStats_customer_id aus der Google Ads-Übertragung zusammen.

Beim Exportieren von Google Analytics-Ereignisdaten wird eine Tabelle für jeden Tag erstellt. Bei der Google Ads-Übertragung wird hingegen eine einzige ads_ClickStats_customer_id-Tabelle pro Kunde erstellt.

Zurück
Los gehts
Weiter
Erweiterte Ereignisabfragen