Podstawowe zapytania o eksport danych o zdarzeniach w Google Analytics

Przykładowe zapytania na tej stronie dotyczą danych eksportowanych do BigQuery z Google Analytics.

Wykonywanie zapytań w zbiorze danych zamiast w przykładowym zbiorze danych

O ile nie zaznaczono inaczej, wszystkie zapytania wymienione poniżej korzystają z przykładowych zbiorów danych i powinny zwracać prawidłowe wyniki. Aby używać danych eksportowanych do BigQuery z Twojej usługi w Google Analytics, w każdym zapytaniu znajdź komentarz -- Replace table i zastąp nim przykładową tabelę. Aby skopiować nazwę tabeli ze zbioru danych:

1. Otwórz interfejs BigQuery i wybierz projekt zawierający zbiór danych.
2. Znajdź tabelę w Eksploratorze.
3. Kliknij 3 kropki w pionie po prawej stronie tabeli, a potem kliknij Skopiuj identyfikator.
4. Wklej nazwę tabeli w miejsce przykładowej tabeli w zapytaniu.
5. Zastąp część tabeli dotyczącą daty symbolem *.

Jeśli np. Copy ID skopiuje nazwę tabeli BigQuerymy-first-gcp-project:analytics_28239234.events_20240718, zastąp:

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

with:

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

Uwaga: dodając tabelę w przykładowych zapytaniach, zastąp znak „:” znakiem „.”.

Tworzenie zapytania dotyczącego konkretnego zakresu dat

Aby wysłać zapytanie dotyczące określonego zakresu dat z wyeksportowanego do BigQuery zbioru danych o zdarzeniach, użyj pseudokolumny _TABLE_SUFFIX w klauzuli WHERE zapytania. Więcej informacji znajdziesz w artykule Filtrowanie wybranych tabel za pomocą funkcji _TABLE_SUFFIX.

Na przykład to zapytanie zlicza niepowtarzalne zdarzenia według daty i nazwy zdarzenia w określonym przedziale czasu i w przypadku wybranych zdarzeń:

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

Liczba użytkowników, nowych użytkowników i powracających użytkowników

• Aby uzyskać łączną liczbę aktywnych użytkowników, zlicz liczbę różnych wartości w polu user_id. Jeśli jednak klient Google Analytics nie odsyła parametru user_id z każdym działaniem lub nie masz pewności, policz liczbę różnych wartości parametru user_pseudo_id.
• W przypadku nowych użytkowników policz liczbę unikalnych użytkowników, których event_name to:
  • first_visit
  • first_open
• W przypadku powracających użytkowników zlicz liczbę użytkowników, którzy byli aktywni w danym okresie, ale nie rozpoczęli w tym czasie pierwszej sesji ani nie uruchomili aplikacji po raz pierwszy (aktywni użytkownicy minus nowi użytkownicy).

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

Średnia liczba transakcji na kupującego

To zapytanie pokazuje średnią liczbę transakcji na kupującego.

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

Wartości dla konkretnej nazwy zdarzenia

To zapytanie pokazuje event_timestamp dla wszystkich zdarzeń purchase i powiązane z nimi wartości parametrów zdarzenia:

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

Poprzednie zapytanie można zmodyfikować, aby zamiast listy wyświetlało sumę wartości parametru zdarzenia:

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

10 najczęściej dodawanych produktów do koszyka

To zapytanie pokazuje 10 najpopularniejszych produktów dodanych do koszyka przez największą liczbę użytkowników.

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

Średnia liczba odsłon strony według typu kupującego (kupujący a użytkownicy, którzy niczego nie kupili)

To zapytanie pokazuje średnią liczbę odsłon strony według typu kupującego (kupujący a użytkownicy, którzy niczego nie kupili):

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

Kolejność wyświetleń strony

To zapytanie pokazuje sekwencję wyświetleń stron przez każdego użytkownika. Zapytanie porządkuje wyniki według tych pól, aby zdarzenia były wyświetlane w kolejności, w jakiej wystąpiły u użytkownika, nawet jeśli zostały wysłane w tej samej partii:

• user_pseudo_id
• user_id
• batch_page_id
• batch_ordering_id
• batch_event_index

Chociaż w przykładzie wyniki są ograniczone tylko do page_view zdarzeń, możesz użyć tej samej klauzuli ORDER BY, aby prawidłowo uporządkować wszystkie zdarzenia, usuwając warunek klauzuli WHERE dla event_name.

Zapytanie pokazuje też, jak używać funkcji zdefiniowanych przez użytkownika GetParamString i GetParamInt, aby ograniczyć duplikowanie i ułatwić zrozumienie oraz utrzymanie zapytań.

Uwaga: schemat przykładowego zbioru danych nie zawiera pól batch_page_id, batch_ordering_id ani batch_event_index. Zastąp tabelę w klauzuli FROM zapytania swoją tabelą.

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

Lista parametrów zdarzenia

To zapytanie zawiera listę wszystkich parametrów zdarzenia występujących w zbiorze danych:

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

Dołączanie do Google Ads

Aby pobrać dodatkowe dane Google Ads dotyczące zdarzeń Google Analytics, skonfiguruj usługę BigQuery Data Transfer Service dla Google Ads, a następnie połącz parametr collected_traffic_source.gclid z danych zdarzenia Google Analytics z polem gclid parametru ads_ClickStats_customer_id z transferu Google Ads.

Pamiętaj, że eksport danych zdarzeń Google Analytics tworzy tabelę dla każdego dnia, a przeniesienie z Google Ads wypełni jedną tabelę ads_ClickStats_customer_id na klienta.