Cookbook BigQuery

30/11/2019
Tính năng này chỉ có sẵn trong Analytics 360, một phần của 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.

Những 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 chọn các trường không liên quan, bạn sẽ làm tăng số lượng dữ liệu cần được xử lý và do đó sử dụng mức phụ cấp hàng tháng nhiều hơn mức cần thiết. Truy vấn được tối ưu hóa giúp sử dụng hiệu quả phụ cấp xử lý dữ liệu hàng tháng.

Tìm hiểu thêm về đặt 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ớ cache

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

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 khảo bảng đó trong phần FROM của truy vấn. Điều này sẽ làm giảm cả lượng dữ liệu phải được xử lý và thời gian bắt buộc cho việc 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ổ thành phần, gỡ lỗi được biểu thị ngay bên dưới truy vấn. Gỡ lỗi cũng khả dụng 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:

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ậ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à đưa nhận xét "standardSQL" ở đầu truy vấn SQL chuẩn của bạn như được trình bày trong các ví dụ sau.

>Với SQL cũ, dữ liệu Google Analytics 360 được truyền vào 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 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ũ
7 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ố] cho mỗi [thứ nguyê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 cho mỗi [ thứ nguyê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ần truy cập trang sản phẩm trung bình theo kiểu người mua hàng (người mua hàng so với người không mua hàng)

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ần trung bình truy cập 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
#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ần trung bình truy cập 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
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ần trung bình truy cập vào trang sản phẩm cho 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ần trung bình truy cập vào trang sản phẩm cho 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ần trung bình truy cập vào trang sản phẩm cho 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 cho mỗi người mua hàng

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 cho 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 cho 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 cho mỗi phiên

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 cho mỗi phiên 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 cho mỗi phiên 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ần 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

Chuỗi lượt xem trang của người dùng 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ũ

Chuỗi lượt xem trang của người dùng 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 các loại lần truy cập đối với PAGES để tránh nhìn thấy các 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 số lần truy cập 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

thứ nguyên tùy chỉnh ở cấp lần 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

 

thứ nguyên tùy chỉnh ở cấp phiên
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 cho các trường thứ nguyê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 bên trong MAX được đánh giá cho từng thứ nguyên tùy chỉnh, nhưng đối với bất điều kiện nào không phải là index=1 (hits) hoặc index=2 (sessions), nó sẽ trả về NULL.
  • Trả về giá trị tối đa, là giá trị của Thứ nguyên tùy chỉnh 1 cho lần truy cập hoặc Thứ nguyên tùy chỉnh 2 cho phiên vì tất cả các giá trị khác 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)

Bên dưới là tập lệnh sườn cho câu hỏi: Các sản phẩm khác được mua bởi khách hàng mua sản phẩm A là gì?

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)
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. Kết quả khi đó được hiển thị trong trường được gắn nhãn quantity, cùng với mặt hàng liên quan trong trường sản phẩm được 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ị bằng 0 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, sử dụng WHERE expr IN kích hoạt JOIN và áp dụng hạn chế kích thước, đặc biệt là kích thước của phía bên phải JOIN (trong trường hợp này là số lượng khách truy cập) cần phải nhỏ hơn 8 MB. Trong Dremel, điều này được gọi là broadcast JOIN. Khi kích thước vượt quá 8 MB, bạn cần phải kích hoạt shuffled JOIN, mà bạn có thể thực hiện bằng cách sử dụng cú pháp JOIN EACH. Rất tiếc là bạn không thể thực hiện bằng cách sử dụng IN, nhưng có thể viết lại cùng một truy vấn 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. Điều này cũng tạo nên việc sử dụng TABLE_DATE_RANGE để truy vấn dữ liệu trong nhiều ngày.

sản phẩm được mua bởi người tiêu dù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ần tương tác của người dùng trung bình trước khi mua hàng

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

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

số lần 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ần tương tác của người dùng trung bình cho 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ần 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 cho ra hai trường: một trường chứa tất cả các ID 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 ID sản phẩm đó.
  2. Do truy vấn này phụ thuộc vào hai tập dữ liệu, nên bạn cần sử dụng hàm JOIN() ... ON. Lệnh này sẽ kết hợp các hàng từ hai tập dữ liệu dựa trên trường chung giữa chúng. Trong trường hợp này, hai tập dữ liệu 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ố cho số lượng hàng còn lại (Imported DataSet.’stock_left_field’) và trường thứ nguyên ID 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.

Nhiều biến trong truy vấn này có tên tập dữ liệu kèm theo chúng dưới dạng tiền tố (ví dụ: Imported_DataSet.’productId_field’, Alias_Name.quantity_sold). Điều này là để làm rõ bạn đang chọn trường nào và để làm rõ trường thuộc về tập dữ liệu nào.

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à số id 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 của 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 sự chênh lệch giữa giá sản phẩm đã 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 (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ế của 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 Lợi nhuận của mỗi sản phẩm là gì? 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 trừ doanh thu đã chi tiêu trên tiền hoàn lại cho tổng lợi nhuận.

Để 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 tính đến sản phẩm được hoà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

* Nguồn: Google Analytics