Hướng dẫn về BigQuery

Tính năng này chỉ có trong Analytics 360 (thuộc Google Marketing Platform).
Tìm hiểu thêm về Google Marketing Platform.

Bài viết này chứa các ví dụ về cách tạo truy vấn của dữ liệu Analytics mà bạn xuất sang BigQuery. Chúng tôi đã tạo sẵn tập dữ liệu mẫu để bạn có thể thực hành với một số truy vấn trong bài viết này.

Nội dung trong bài viết này:

Tối ưu hóa truy vấn

Mỗi truy vấn bạn chạy đóng góp vào phụ cấp xử lý dữ liệu hàng tháng. Nếu bạn chọn các trường không liên quan, số lượng dữ liệu cần được xử lý sẽ tăng lên. Khi đó, bạn sẽ sử dụng mức dữ liệu được phép hằng tháng nhiều hơn cần thiết. Truy vấn được tối ưu hoá giúp bạn sử dụng mức dữ liệu được phép hằng tháng một cách hiệu quả hơn.

Tìm hiểu thêm về mức giá.

Chỉ chọn những gì bạn cần

Khi bạn xây dựng truy vấn, hãy chọn các trường có liên quan trong câu lệnh SELECT. Khi không gọi các trường không liên quan, bạn sẽ giảm lượng dữ liệu và thời gian cần để xử lý truy vấn.

Ví dụ: tránh sử dụng toán tử ký tự đại diện

Dạng không hợp lệ: sử dụng toán tử ký tự đại diện
SELECT *
FROM [table name];

 

Dạng hợp lệ: sử dụng tên trường để tránh việc xử lý không cần thiết
SELECT field1, field2
FROM [table name];

Cho phép lưu vào bộ nhớ đệm

Khi cần thiết, tránh sử dụng các hàm dưới dạng trường. Các hàm (như NOW() hoặc TODAY()) trả lại các kết quả khác nhau, ngăn chặn các truy vấn được lưu vào bộ nhớ cache và do đó được trả lại nhanh hơn. Thay vào đó, hãy sử dụng thời gian và ngày tháng cụ thể.

Hiện tại, chúng tôi không hỗ trợ kết quả được lưu trong bộ nhớ đệm cho các truy vấn dựa trên nhiều bảng sử dụng ký tự đại diện, kể cả khi bạn đã chọn mục Sử dụng kết quả được lưu trong bộ nhớ đệm. Nếu bạn chạy cùng một truy vấn bao gồm ký tự đại diện nhiều lần, thì bạn sẽ thanh toán cho mỗi truy vấn. Tìm hiểu thêm

Sử dụng bảng trung gian cho các truy vấn con thường được sử dụng

Nếu nhận thấy bạn liên tục sử dụng một truy vấn cụ thể dưới dạng truy vấn con, bạn có thể lưu truy vấn đó dưới dạng bảng trung gian bằng cách nhấp vào Lưu dưới dạng bảng phía trên kết quả truy vấn. Sau đó, bạn có thể tham chiếu đến bảng đó trong mục FROM của truy vấn. Thao tác này có thể giúp giảm bớt lượng dữ liệu cũng như thời gian cần thiết để xử lý.

sử dụng bảng trung gian
SELECT field1, field2
FROM [Dataset name.table name];

Gỡ lỗi truy vấn

BigQuery gỡ lỗi mã của bạn khi bạn tạo mã. Trong cửa sổ cấu trúc, phần gỡ lỗi được biểu thị ngay bên dưới truy vấn. Việc gỡ lỗi cũng có sẵn thông qua API với cờ dryRun.

Truy vấn hợp lệ có chỉ báo màu xanh lá cây mà bạn có thể nhấp vào để xem lượng dữ liệu được xử lý theo truy vấn. Tính năng này cung cấp cho bạn cơ hội để tối ưu hóa dữ liệu trước khi chạy truy vấn để bạn có thể tránh việc xử lý dữ liệu không cần thiết.

Query Debugging - Success

 

Truy vấn không hợp lệ có chỉ báo màu đỏ mà bạn có thể nhấp vào để xem thông tin về lỗi, đồng thời tìm dòng và cột nơi xảy ra lỗi. Trong ví dụ bên dưới, câu lệnh GROUP BY trống và lỗi được xác định chính xác.

Query Debugging - Error

 

Mẹo và phương pháp hay nhất

Sử dụng tập dữ liệu mẫu

Các ví dụ sau sử dụng tập dữ liệu mẫu của Google Analytics.

Để sử dụng các truy vấn về dữ liệu của riêng bạn, chỉ cần thay thế tên dự án và tập dữ liệu trong các ví dụ bằng tên dự án và tập dữ liệu của riêng bạn.

Sử dụng SQL chuẩn so với SQL cũ

BigQuery hỗ trợ hai phương ngữ SQL:

Bài viết về Cách di chuyển sang SQL chuẩn giải thích sự khác biệt giữa hai phương ngữ.

SQL chuẩn hiện là phương ngữ SQL ưa thích để truy vấn dữ liệu được lưu trữ trong BigQuery.

Hãy xem bài viết về Cách bật SQL chuẩn để biết thông tin về cách bật SQL chuẩn trong CLI, API, giao diện người dùng BigQuery hoặc bất kỳ giao diện nào bạn đang sử dụng.

Cách dễ nhất để bắt đầu là thêm nhận xét "standardSQL" vào đầu các truy vấn SQL chuẩn của bạn như được trình bày trong các ví dụ sau.

Khi bạn sử dụng SQL cũ, dữ liệu Google Analytics 360 sẽ được chuyển qua một bảng mới hằng ngày. Để truy vấn nhiều bảng cùng một lúc, bạn có thể phân tách tên các bảng bằng dấu phẩy, sử dụng hàm ký tự đại diện bảng TABLE_DATE_RANGE, hoặc sử dụng nhiều hàm TABLE_DATE_RANGE được phân tách bằng dấu phẩy, như trong các ví dụ sau.

Truy vấn nhiều bảng

Các ví dụ sau đây thể hiện các truy vấn SQL chuẩn và SQL cũ cho cùng một dữ liệu.

3 ngày

SQL chuẩn
3 ngày sử dụng 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
SQL cũ
3 ngày sử dụng tên bảng được phân tách bằng dấu phẩy
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

 

1095 ngày qua

SQL chuẩn
1095 ngày qua sử dụng _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
SQL cũ
1095 ngày qua sử dụng 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

 

36 tháng qua

SQL chuẩn
36 tháng qua sử dụng _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
SQL cũ
36 tháng qua sử dụng 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

 

3 năm qua

SQL chuẩn
3 năm qua sử dụng _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
SQL cũ
3 năm qua sử dụng 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

 

Phạm vi ngày cụ thể

SQL chuẩn
Phạm vi ngày cụ thể sử dụng _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
SQL cũ
Phạm vi ngày cụ thể sử dụng 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

Dữ liệu 3 năm qua cộng với dữ liệu hôm nay (trong ngày)

SQL chuẩn
Dữ liệu 3 năm qua cộng với dữ liệu hôm nay (trong ngày) sử dụng UNION ALL & _TABLE_SUFFIX
Lưu ý: truy vấn mẫu này sẽ không hoạt động với tập dữ liệu công khai của Google Analytics vì hiện không có bảng trong ngày.
#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

SQL cũ

Dữ liệu 3 năm qua cộng với dữ liệu hôm nay (trong ngày) sử dụng nhiều TABLE_DATE_RANGE
Lưu ý: truy vấn mẫu này sẽ không hoạt động với tập dữ liệu công khai của Google Analytics vì hiện không có bảng trong ngày.
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

 

 

Ví dụ về truy vấn cơ bản

Phần này giải thích cách xây dựng các truy vấn cơ bản bằng cách sử dụng chỉ số và thứ nguyên từ dữ liệu Analytics mẫu.

Tổng số [chỉ số] trên mỗi [phương diện]?

Dưới đây là các tập lệnh mẫu cho câu hỏi: Tổng số giao dịch được tạo cho mỗi trình duyệt thiết bị trong tháng 7 năm 2017 là bao nhiêu?

SQL chuẩn

Tổng số giao dịch trên mỗi trình duyệt thiết bị vào tháng 7 năm 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

SQL cũ

Tổng số giao dịch trên mỗi trình duyệt thiết bị vào tháng 7 năm 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

 

 

Tỷ lệ thoát trung bình trên mỗi [ phương diện ]?

Tỷ lệ thoát thực tế được định nghĩa là tỷ lệ phần trăm lượt truy cập có một lần truy cập trang duy nhất. Dưới đây là các tập lệnh mẫu cho câu hỏi: Tỷ lệ thoát thực tế trên mỗi nguồn lưu lượng truy cập là bao nhiêu?

SQL chuẩn

Tỷ lệ thoát trên mỗi nguồn lưu lượng truy cập trong tháng 7 năm 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

SQL cũ

Tỷ lệ thoát trên mỗi nguồn lưu lượng truy cập trong tháng 7 năm 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

 

 

Số lượt xem trang sản phẩm trung bình theo kiểu người mua (người mua so với người dùng không mua)

Dưới đây là các tập lệnh mẫu cho câu hỏi: Số lần truy cập trung bình vào trang sản phẩm cho người dùng đã thực hiện mua hàng trong tháng 7 năm 2017 là bao nhiêu?

SQL chuẩn

Số lượt xem trang sản phẩm trung bình cho những người dùng đã mua hàng trong tháng 7 năm 2017
#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 )

SQL cũ

Số lượt xem trang sản phẩm trung bình cho những người dùng đã mua hàng trong tháng 7 năm 2017
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 )

 

 

Dưới đây là các tập lệnh mẫu cho câu hỏi: Số lượt xem trang sản phẩm trung bình cho những người dùng không mua hàng trong tháng 7 năm 2017 là bao nhiêu?

SQL chuẩn

Số lượt xem trang sản phẩm trung bình cho những người dùng không mua hàng trong tháng 7 năm 2017
#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 )

SQL cũ

Số lượt xem trang sản phẩm trung bình cho những người dùng không mua hàng trong tháng 7 năm 2017
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 )

 

 

Số giao dịch trung bình trên mỗi người mua

Dưới đây là các tập lệnh mẫu cho câu hỏi: Tổng số giao dịch trung bình trên mỗi người dùng đã thực hiện mua hàng trong tháng 7 năm 2017 là bao nhiêu?

SQL chuẩn

Số giao dịch trung bình trên mỗi người dùng đã mua hàng trong tháng 7 năm 2017
#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 )

SQL cũ

Số giao dịch trung bình trên mỗi người dùng đã mua hàng trong tháng 7 năm 2017
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 )

 

 

Số tiền trung bình được chi tiêu trên mỗi phiên hoạt động

Dưới đây là các tập lệnh mẫu cho câu hỏi: Số tiền trung bình được chi tiêu cho mỗi phiên trong tháng 7 năm 2017 là bao nhiêu?

SQL chuẩn

Số tiền trung bình được chi tiêu trên mỗi phiên hoạt động trong tháng 7 năm 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 )

SQL cũ

Số tiền trung bình được chi tiêu trên mỗi phiên hoạt động trong tháng 7 năm 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 )

 

 

Trình tự các lượt truy cập

Dưới đây là các tập lệnh mẫu cho câu hỏi: Chuỗi lượt xem trang là gì?.

SQL chuẩn

Trình tự các trang được người dùng xem trong tháng 7 năm 2017
#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

SQL cũ

Trình tự các trang được người dùng xem trong tháng 7 năm 2017
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

Trong truy vấn này, bạn giới hạn loại lượt truy cập thành PAGES để tránh xem các lượt tương tác sự kiện hoặc giao dịch. Mỗi dòng của kết quả đại diện cho một lượt xem trang, và được hiển thị theo thứ tự mặc định của các trường trong câu lệnh SELECT.

 

 

Nhiều thứ nguyên tùy chỉnh ở cấp lần truy cập hoặc phiên

phương diện tuỳ chỉnh ở cấp lượt truy cập
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

 

phương diện tuỳ chỉnh ở cấp phiên hoạt động
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

Trong mỗi truy vấn:

Câu lệnh SELECT truy vấn để biết các trường phương diện và chỉ số có liên quan.

Hàm MAX:

  • Trả lại thứ nguyên tùy chỉnh dưới dạng cột mới. Bạn có thể lặp lại hàm để trả lại nhiều thứ nguyên tùy chỉnh dưới dạng cột mới.
  • WITHIN hits và WITHIN RECORD đánh giá điều kiện bên trong các trường lặp lại trong BigQuery.
  • Điều kiện trong hàm MAX được đánh giá cho mỗi phương diện tuỳ chỉnh, nhưng đối với bất kỳ điều kiện nào không phải là index=1 (hits) hoặc index=2 (sessions), thì giá trị trả về sẽ là NULL.
  • Giá trị trả về là giá trị tối đa. Đây là giá trị của Phương diện tuỳ chỉnh 1 cho số lượt truy cập, hoặc Phương diện tuỳ chỉnh 2 cho số phiên hoạt động, vì tất cả các giá trị khác đều là NULL.

Ví dụ về truy vấn nâng cao

Bây giờ bạn đã quen thuộc với các truy vấn đơn giản, bạn có thể tạo truy vấn bằng cách sử dụng các hàm và tính năng nâng cao khả dụng trong BigQuery.

Sản phẩm được mua bởi khách hàng đã mua sản phẩm A (Thương mại điện tử cổ điển)

Dưới đây là tập lệnh sườn cho câu hỏi: Khách hàng đã mua sản phẩm A mua những sản phẩm nào khác?

sản phẩm được mua bởi khách hàng đã mua sản phẩm A (Thương mại điện tử cũ)
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. Trong dòng đầu tiên, bạn chọn tất cả các mặt hàng khác đã được người dùng mua và sử dụng hàm tổng hợp COUNT() để tính số lượng của từng mặt hàng khác đã được mua. Sau đó, kết quả sẽ hiển thị trong trường gắn nhãn quantity, thể hiện mặt hàng liên quan trong trường sản phẩm gắn nhãn other_purchased_products.
  2. Trong truy vấn con màu xám, bạn chỉ chọn những người dùng duy nhất (fullVisitorId) đã thực hiện giao dịch (totals.transactions>=1) và trong giao dịch đã mua sản phẩm A (WHERE hits.item.productName CONTAINS 'Product Item Name A').

Các quy tắc (câu lệnh WHERE và AND) trong truy vấn cấp cao nhất (màu xanh lá cây) bỏ qua các giá trị trong hits.item.productName có giá trị rỗng và chứa sản phẩm A.

Dưới đây là ví dụ về truy vấn Nếu khách hàng mua Bút Brighton Metallic - Một bộ 4 bút, thì (các) sản phẩm khác mà họ đã mua là gì?

các sản phẩm được mua bởi khách hàng đã mua "Bút Brighton Metallic (Một bộ 4 bút)" vào ngày 24 tháng 6 năm 2013
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;

Trong Dremel/BigQuery, việc sử dụng WHERE expr IN sẽ kích hoạt một JOIN, và các giới hạn về kích thước sẽ áp dụng. Cụ thể, kích thước bên phải của JOIN (trong trường hợp này là số người truy cập) phải ít hơn 8 MB. Trong Dremel, đây được gọi là broadcast JOIN. Khi kích thước này vượt quá 8MB, bạn cần kích hoạt shuffled JOIN bằng cách sử dụng cú pháp JOIN EACH. Rất tiếc, bạn không thể thực hiện cách này bằng cách sử dụng IN, nhưng có thể viết lại chính truy vấn này bằng JOIN.

Sản phẩm được mua bởi khách hàng đã mua sản phẩm A (Thương mại điện tử nâng cao)

Điều này tương tự với truy vấn sườn trước đó nhưng hoạt động cho Thương mại điện tử nâng cao. Bạn có thể sử dụng TABLE_DATE_RANGE để tạo truy vấn dữ liệu trong nhiều ngày.

sản phẩm được mua bởi một khách hàng đã mua sản phẩm A (Thương mại điện tử nâng cao)
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;

Số lượt tương tác trung bình của người dùng trước khi mua hàng

Đây là ví dụ về một truy vấn chứa lệnh JOIN() [...] ON, lệnh này chỉ phụ thuộc và dữ liệu Analytics.

Bên dưới là tập lệnh sườn cho câu hỏi: Số lượt tương tác trung bình với người dùng trước khi mua hàng là bao nhiêu?

số lượt tương tác của người dùng trước khi mua hàng
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. Dòng đầu tiên thực hiện phép toán chính để tìm số lượt tương tác của người dùng trung bình trên mỗi sản phẩm và truy vấn này sẽ tạo kết hợp giữa hai truy vấn phụ có tên là ‘Alias_Name_1’ và ‘Alias_Name_2’.
  2. ‘Alias_Name_1’ được sử dụng để tạo một trường sử dụng hàm tổng hợp SUM() để tổng hợp tất cả số lần truy cập được ghi lại cho một sản phẩm.
  3. ‘Alias_Name_2’ được sử dụng để tìm số lần truy cập được người dùng thực hiện cho mỗi sản phẩm bằng cách sử dụng hàm COUNT().
  4. Dòng cuối cùng hiển thị trường chung (hits.item.productSku) được chia sẻ giữa hai tập dữ liệu được kết hợp.

Dưới đây là ví dụ về truy vấn Vào ngày 10 tháng 9 năm 2013, số lần tương tác của người dùng trung bình trước khi mua hàng là bao nhiêu?

số lượt tương tác của người dùng trước khi mua hàng vào ngày 10 tháng 9 năm 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;

Tỷ lệ phần trăm hàng đã bán trên mỗi sản phẩm

Đây là ví dụ về truy vấn không chỉ phụ thuộc vào dữ liệu Analytics, mà còn phụ thuộc vào dữ liệu không phải của Analytics. Bằng cách kết hợp cả hai tập dữ liệu, bạn có thể bắt đầu hiểu hành vi của người dùng ở cấp được phân đoạn nhiều hơn. Bạn có thể nhập dữ liệu không phải của Analytics vào BigQuery, nhưng lưu ý rằng điều này sẽ góp phần vào chi phí lưu trữ dữ liệu hàng tháng của bạn.

Bên dưới là tập lệnh sườn cho câu hỏi: Tỷ lệ phần trăm hàng đã bán trên mỗi sản phẩm là bao nhiêu?

tỷ lệ phần trăm hàng đã bán trên mỗi sản phẩm
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. Dòng đầu tiên trả về hai trường: một trường chứa tất cả các mã sản phẩm và trường còn lại là phép toán sẽ cho thấy tỷ lệ phần trăm hàng đã bán cho mã sản phẩm đó.
  2. Do truy vấn này phụ thuộc vào hai tập dữ liệu, bạn cần sử dụng hàm JOIN() ... ON. Lệnh này liên kết các hàng từ hai tập dữ liệu này dựa trên trường mà cả hai tập dữ liệu đều có. Trong trường hợp này, hai tập dữ liệu này là [ ‘Imported_DataSet’ ] và ‘Alias_Name’.
  3. [ ‘Imported_DataSet’ ] là dữ liệu không phải của Analytics. Đây là tập dữ liệu chứa trường chỉ số về số lượng hàng còn lại (Imported DataSet.’stock_left_field’) và trường phương diện mã sản phẩm (Imported_DataSet.’productId_field’).
  4. ‘Alias_Name’ là tên được chỉ định cho dữ liệu được truy vấn con màu xám trả về. Truy vấn con này sử dụng dữ liệu Analytics để tìm hiểu tổng số lượng mặt hàng đã bán trên mỗi sản phẩm.
  5. Dòng cuối cùng sử dụng câu lệnh ON để hiển thị trường chung giữa hai tập dữ liệu và nơi hai tập dữ liệu được kết hợp.

Trong truy vấn này, nhiều biến có tên tập dữ liệu kèm theo biến dưới dạng tiền số (ví dụ: Imported_DataSet.’productId_field’, Alias_Name.quantity_sold). Tiền số này làm rõ trường bạn đang chọn, cũng làm rõ nguồn của tập dữ liệu.

Dưới đây là ví dụ về truy vấn Tỷ lệ phần trăm hàng đã bán trên mỗi sản phẩm vào ngày 28 tháng 7 năm 2013 là bao nhiêu?

tỷ lệ phần trăm hàng đã bán trên mỗi sản phẩm vào ngày 28 tháng 7 năm 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;

Khả năng sinh lời của mỗi sản phẩm

Bên dưới là tập lệnh sườn cho câu hỏi: Khả năng sinh lời của mỗi sản phẩm là gì?

lợi nhuận của sản phẩm
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. Dòng đầu tiên chứa phép toán để tính tổng lợi nhuận được tạo trên mỗi sản phẩm.
  2. Truy vấn con màu xám sử dụng dữ liệu không phải của Analytic thu thập dữ liệu về bao nhiêu lợi nhuận sẽ được tạo khi sản phẩm được bán.
  3. Truy vấn con màu đỏ là truy vấn con dữ liệu Analytics, sẽ được kết hợp với dữ liệu không phải của Analytics. truy vấn sẽ tính số lượng mặt hàng đã bán trên mỗi sản phẩm.
  4. Hàng cuối cùng sử dụng câu lệnh ON để làm rõ trường mà hai tập dữ liệu chia sẻ. Trong trường hợp này, trường đó là mã sản phẩm.

Dưới đây là ví dụ về truy vấn Khả năng sinh lời của mỗi sản phẩm vào ngày 28 tháng 7 năm 2013 là bao nhiêu?

lợi nhuận theo sản phẩm vào ngày 28 tháng 7 năm 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 );

Lợi nhuận được tính bằng cách tìm ra chênh lệch giữa giá sản phẩm được bán và chi phí sản xuất sản phẩm. Thông tin này được lưu trên tập dữ liệu không phải của GA.

Khả năng sinh lời thực tế của mỗi sản phẩm (có tính đến tiền hoàn lại)

Bên dưới là tập lệnh sườn cho câu hỏi: Khả năng sinh lời thực tế của mỗi sản phẩm là bao nhiêu?

lợi nhuận thực tế theo sản phẩm
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. Đây là câu hỏi rất giống với câu hỏi Khả năng sinh lời của mỗi sản phẩm là bao nhiêu? Sự khác biệt duy nhất là trong tập dữ liệu không phải của Analytics trong truy vấn con màu xám và phép toán tính lợi nhuận thực tế trong dòng đầu tiên.
  2. Trong tập dữ liệu không phải của Analytics, bạn cũng đang tính toán tổng số tiền đã chi tiêu trên tiền hoàn lại (trong câu lệnh SELECT của truy vấn con màu đỏ).
  3. Sau đó, bạn thực hiện phép toán trong dòng 1 để tìm lợi nhuận thực tế bằng cách lấy tổng lợi nhuận trừ đi doanh thu đã chi tiêu cho tiền hoàn lại.

Để biết thêm thông tin về truy vấn, vui lòng xem phần về khả năng sinh lời của từng sản phẩm.

Dưới đây là ví dụ về truy vấn sau Khả năng sinh lời thực tế của mỗi sản phẩm vào ngày 28 tháng 7 năm 2013 là bao nhiêu?

lợi nhuận thực tế của sản phẩm vào ngày 28 tháng 7 năm 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 ) );

Lợi nhuận thực tế sẽ tính đến khả năng sinh lời của sản phẩm sau khi xét đến sản phẩm được hoàn tiền lại. Để tính tổng doanh thu hoàn loại cho một sản phẩm:

tổng doanh thu hoàn lại cho một sản phẩm = ( giá của sản phẩm + giá vận chuyển trả lại cho sản phẩm ) * số lượng sản phẩm bị trả lại

Thông tin này có hữu ích không?

Chúng tôi có thể cải thiện trang này bằng cách nào?

Bạn cần trợ giúp thêm?

Hãy thử các bước tiếp theo sau:

Đăng lên cộng đồng trợ giúp Nhận câu trả lời từ các thành viên cộng đồng
Liên hệ với chúng tôi Hãy giải thích thêm để chúng tôi hỗ trợ bạn tốt hơn
true
learning path
Chọn lộ trình học tập của riêng bạn

Mời bạn cùng tìm hiểu google.com/analytics/learn, một trang thông tin mới giúp bạn khai thác tối đa Google Analytics 4. Trang web mới này gồm nhiều video, bài viết và quy trình có hướng dẫn, đồng thời cung cấp đường liên kết đến các nguồn thông tin về Google Analytics (Discord, Blog, kênh YouTube và kho lưu trữ GitHub).

Hãy bắt đầu học hỏi ngay từ hôm nay!

Tìm kiếm
Xóa nội dung tìm kiếm
Đóng tìm kiếm
Trình đơn chính
47327122764707999
true
Tìm kiếm trong Trung tâm trợ giúp
true
true
true
true
true
69256
false
false