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 Beispiel-Datasets verwendet. Sie sollten zu gültigen Ergebnissen führen. BigQuery der eigenen Google Analytics-Property verwenden suchen Sie in jeder Abfrage nach dem Kommentar -- Replace table und ersetzen Sie die Beispieltabelle. So kopieren Sie den Tabellennamen aus Ihrem Dataset:

1. Rufen Sie die BigQuery-UI auf und wählen Sie das Ihr Dataset enthält.
2. Suchen Sie im Explorer nach der Tabelle.
3. Klicken Sie auf das Dreipunkt-Menü rechts neben der Tabelle und dann auf ID kopieren:
4. Fügen Sie den Tabellennamen anstelle der Beispieltabelle in die Abfrage ein.
5. Ersetzen Sie den Datumsteil der Tabelle durch *.

Wenn beispielsweise Copy ID den BigQuery-Tabellennamen kopiert hat my-first-gcp-project:analytics_28239234.events_20240718 und ersetzen Sie dann:

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

mit

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

Hinweis: Ersetzen Sie beim Hinzufügen der Tabelle in den Beispielabfragen ":" durch „.“.

Einen bestimmten Zeitraum abfragen

Um einen bestimmten Zeitraum aus einem BigQuery-Dataset für den Ereignisexport abzufragen, verwenden Sie die Methode _TABLE_SUFFIX Pseudospalte in der WHERE-Klausel Ihrer Abfrage. Weitere Informationen Ansicht Ausgewählte Tabellen mithilfe von _TABLE_SUFFIX filtern.

Die folgende Abfrage zählt beispielsweise eindeutige Ereignisse nach Datum und nach Ereignisname für einen bestimmten Zeitraum und ausgewählte Ereignisse:

-- 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;

Anzahl der Nutzer und neuen Nutzer

• Die Gesamtzahl der Nutzer ermitteln Sie, indem Sie die Anzahl der einzelnen user_id zählen. Wenn Ihr Google Analytics-Client jedoch keine user_id mit oder, wenn Sie sich nicht sicher sind, die Anzahl der einzelnen user_pseudo_id.
• Für neue Nutzer können Sie wie oben beschrieben vorgehen, die folgenden Werte für event_name: <ph type="x-smartling-placeholder">
  </ph>
  • first_visit
  • first_open

-- Example: Get 'Total User' count and 'New User' count.

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 user_count,
  SUM(is_new_user) AS new_user_count
FROM UserInfo;

Durchschnittliche Anzahl der Transaktionen pro Käufer

Die folgende Abfrage zeigt die durchschnittliche Anzahl der Transaktionen pro Käufer.

-- 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 den event_timestamp für alle purchase-Ereignisse und der zugehörige Ereignisparameter Werte:

-- 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 geändert werden, um die Gesamtzahl der Ereignisparameter anzuzeigen Werte statt einer Liste:

-- 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';

Top-10-Artikel, die in den Einkaufswagen gelegt wurden

Die folgende Abfrage zeigt die 10 Artikel, die von der meisten Nutzer in den Einkaufswagen 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_web_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 nach Käufertyp (Käufer und Nicht-Käufer)

Die folgende Abfrage zeigt die durchschnittliche Anzahl der Seitenaufrufe des Käufertyps. (Käufer und Nicht-Käufer) der Nutzer:

-- 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

Diese Abfrage zeigt die Reihenfolge der Seitenaufrufe jedes Nutzers. Die Reihenfolge der Abfragen die Ergebnisse mithilfe der folgenden Felder, sodass die Ereignisse in der Reihenfolge aufgelistet werden, für den Nutzer aufgetreten, auch wenn die Ereignisse im selben Batch gesendet wurden:

• user_pseudo_id
• user_id
• batch_page_id
• batch_ordering_id
• batch_event_index

Obwohl die Ergebnisse im Beispiel auf page_view-Ereignisse beschränkt sind, können Sie ORDER BY-Klausel verwenden, um alle Ereignisse korrekt zu ordnen, indem der Parameter Bedingung der WHERE-Klausel für event_name.

Die Abfrage zeigt auch, wie benutzerdefinierte Funktionen GetParamString und GetParamInt, um Duplikate zu reduzieren und Abfragen zu erstellen zu verstehen und zu pflegen.

Hinweis: Das Schema des Beispiel-Datasets enthält nicht batch_page_id, batch_ordering_id- oder batch_event_index-Feldern. Ersetzen Sie die Tabelle in der FROM-Klausel der Abfrage durch Ihren Tabelle.

-- 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 aufgelistet, die im Dataset vorkommen:

-- 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 teilnehmen

Wenn Sie zusätzliche Google Ads-Daten für Ihre Google Analytics-Ereignisse abrufen möchten, müssen Sie die BigQuery Data Transfer Service für Google Ads, dann collected_traffic_source.gclid aus Google Analytics-Ereignisdaten mit gclid zusammenführen von ads_ClickStats_customer_id aus der Google Ads-Übertragung.

Beim Exportieren von Ereignisdaten aus Google Analytics wird eine Tabelle für jeden Tag erstellt, während wird bei der Google Ads-Übertragung ein einzelnes ads_ClickStats_customer_id Tabelle pro Kunde.