Kueri dasar untuk ekspor data peristiwa Google Analytics

Contoh kueri di halaman ini berlaku untuk data ekspor peristiwa BigQuery untuk di Google Analytics.

Mengkueri set data Anda, bukan set data sampel

Kecuali dinyatakan lain, semua kueri yang tercantum di sini menggunakan {i>dataset <i}sampel dan harus memberikan hasil yang valid. Untuk menggunakan BigQuery properti Google Analytics Anda sendiri data ekspor peristiwa, cari komentar -- Replace table di setiap kueri dan mengganti tabel contoh. Untuk menyalin nama tabel dari set data Anda:

1. Buka UI BigQuery dan pilih project yang berisi set data Anda.
2. Cari tabel di Explorer.
3. Klik tiga titik vertikal di sebelah kanan tabel, lalu klik Salin ID.
4. Tempelkan nama tabel di tempat contoh tabel dalam kueri.
5. Ganti bagian tanggal di tabel dengan *.

Misalnya, jika Copy ID menyalin nama tabel BigQuery my-first-gcp-project:analytics_28239234.events_20240718, lalu ganti:

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

dengan:

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

Catatan: Saat menambahkan tabel di contoh kueri, ganti ':' dengan '.'.

Membuat kueri untuk rentang tanggal tertentu

Untuk membuat kueri rentang tanggal tertentu dari set data ekspor peristiwa BigQuery, gunakan kolom pseudo _TABLE_SUFFIX di klausa WHERE kueri Anda. Untuk mendapatkan info selengkapnya, lihat Memfilter tabel yang dipilih menggunakan _TABLE_SUFFIX.

Misalnya, kueri berikut menghitung peristiwa unik menurut tanggal dan nama peristiwa untuk periode hari tertentu serta peristiwa yang dipilih:

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

Jumlah pengguna dan jumlah pengguna baru

• Untuk mendapatkan total jumlah pengguna, hitung jumlah user_id unik. Namun, jika klien Google Analytics Anda tidak mengirim kembali user_id dengan setiap hit atau jika Anda tidak yakin, hitung jumlah user_pseudo_id unik.
• Untuk pengguna baru, Anda dapat menggunakan pendekatan penghitungan yang sama seperti yang dijelaskan di atas, tetapi untuk nilai event_name berikut:
  • 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;

Jumlah rata-rata transaksi per pembeli

Kueri berikut menunjukkan jumlah rata-rata transaksi per pembeli.

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

Nilai untuk nama peristiwa tertentu

Kueri berikut menampilkan event_timestamp untuk semua peristiwa purchase dan nilai parameter peristiwa yang terkait:

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

Kueri sebelumnya dapat diubah untuk menampilkan total nilai parameter peristiwa, bukan daftar:

-- 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 item teratas yang ditambahkan ke keranjang

Kueri berikut menampilkan 10 item teratas yang ditambahkan ke keranjang menurut jumlah pengguna terbanyak.

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

Jumlah rata-rata kunjungan halaman menurut jenis pembeli (pembeli vs bukan pembeli)

Kueri berikut menunjukkan jumlah rata-rata jenis pembeli kunjungan halaman (pembeli vs bukan pembeli) dari total pengguna:

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

Urutan kunjungan halaman

Kueri ini menampilkan urutan kunjungan halaman yang dibuat oleh setiap pengguna. Urutan kueri hasilnya menggunakan kolom berikut sehingga peristiwa terdaftar dalam urutan peristiwa tersebut terjadi pada pengguna, meskipun peristiwa dikirim dalam batch yang sama:

• user_pseudo_id
• user_id
• batch_page_id
• batch_ordering_id
• batch_event_index

Meskipun contoh membatasi hasil hanya untuk peristiwa page_view, Anda dapat menggunakan klausa ORDER BY yang sama untuk mengurutkan semua peristiwa secara benar dengan menghapus Kondisi klausa WHERE untuk event_name.

Kueri tersebut juga menunjukkan cara menggunakan kueri yang ditentukan pengguna fungsi-fungsi GetParamString dan GetParamInt untuk mengurangi duplikasi dan membuat kueri Anda lebih mudah dipahami dan dikelola.

Catatan: Skema set data sampel tidak berisi batch_page_id, Kolom batch_ordering_id, atau batch_event_index. Ganti tabel di klausa FROM kueri dengan 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;

Daftar parameter peristiwa

Kueri berikut mencantumkan semua parameter peristiwa yang muncul di set data Anda:

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

Bergabung dengan Google Ads

Untuk mengambil data Google Ads tambahan untuk peristiwa Google Analytics Anda, siapkan BigQuery Data Transfer Service untuk Google Google Ads, lalu gabungkan collected_traffic_source.gclid dari data peristiwa Google Analytics ke gclid kolom ads_ClickStats_customer_id dari transfer Google Ads.

Perhatikan bahwa ekspor data peristiwa Google Analytics membuat tabel untuk setiap hari, sedangkan transfer Google Ads mengisi satu ads_ClickStats_customer_id tabel per pelanggan.