สูตรการแก้ปัญหา BigQuery

ฟีเจอร์นี้ใช้ได้เฉพาะใน Analytics 360 ซึ่งเป็นส่วนหนึ่งของ Google Marketing Platform
ดูข้อมูลเพิ่มเติมเกี่ยวกับ Google Marketing Platform

บทความนี้ประกอบด้วยตัวอย่างของวิธีสร้างการค้นหาข้อมูล Analytics ที่คุณส่งออกไปที่ BigQuery เราได้จัดทำชุดข้อมูลตัวอย่างเพื่อให้คุณฝึกฝนกับการค้นหาในบทความนี้ได้

บทความนี้ประกอบด้วย

การเพิ่มประสิทธิภาพการค้นหา

การค้นหาที่คุณเรียกใช้แต่ละครั้งจะรวมอยู่ในการประมวลผลข้อมูลที่ได้รับอนุญาตเป็นรายเดือน หากคุณเลือกช่องมากเกินความจำเป็น จะเป็นการเพิ่มจำนวนข้อมูลที่ต้องประมวลผล ส่งผลให้คุณใช้จำนวนข้อมูลที่ได้รับอนุญาตเป็นรายเดือนมากขึ้นเกินความจำเป็น การค้นหาที่มีการเพิ่มประสิทธิภาพจะใช้การประมวลผลข้อมูลที่ได้รับอนุญาตเป็นรายเดือนอย่างมีประสิทธิภาพ

ดูข้อมูลเพิ่มเติมเกี่ยวกับการตั้งราคา

เลือกเฉพาะสิ่งที่คุณต้องการ

เมื่อคุณสร้างการค้นหา ให้เลือกช่องที่เกี่ยวข้องภายในคำสั่ง SELECT การไม่เรียกช่องที่ไม่จำเป็นจะช่วยลดจำนวนข้อมูลและเวลาที่ต้องใช้ในการประมวลผลการค้นหา

ตัวอย่าง: หลีกเลี่ยงการใช้โอเปอเรเตอร์ที่เป็นไวลด์การ์ด

รูปแบบไม่ถูกต้อง: ใช้โอเปอเรเตอร์ที่เป็นไวลด์การ์ด
SELECT *
FROM [ชื่อตาราง];

 

รูปแบบที่ดีกว่า: ใช้ชื่อช่องเพื่อหลีกเลี่ยงการประมวลผลที่ไม่จำเป็น
SELECT ช่อง1, ช่อง2
FROM [ชื่อตาราง];

อนุญาตให้แคช

หลีกเลี่ยงการใช้ฟังก์ชันเป็นช่องเมื่อเป็นไปได้ ฟังก์ชัน (เช่น NOW() หรือ TODAY()) จะแสดงผลลัพธ์ของตัวแปร ซึ่งจะทำให้ไม่มีการแคชการค้นหาที่ช่วยให้สามารถแสดงผลลัพธ์ได้เร็วขึ้น ให้ใช้เวลาและวันที่ที่เฉพาะเจาะจงแทน

ปัจจุบันยังไม่รองรับผลการค้นหาที่แคชไว้ของการค้นหาในหลายตารางโดยใช้ไวลด์การ์ด แม้ว่าจะมีการเลือกตัวเลือก "ใช้ผลการค้นหาที่แคชไว้" ก็ตาม หากคุณเรียกใช้การค้นหาด้วยไวลด์การ์ดเดียวกันหลายครั้ง ระบบจะเรียกเก็บเงินสำหรับการค้นหาแต่ละครั้ง ดูข้อมูลเพิ่มเติม

ใช้ตารางข้อมูลชั่วคราวสำหรับการค้นหาย่อยที่ใช้บ่อย

หากคุณพบว่ามีการใช้การค้นหาใดเป็นการค้นหาย่อยบ่อยครั้ง คุณสามารถบันทึกการค้นหานั้นเป็นตารางข้อมูลชั่วคราวโดยคลิกบันทึกเป็นตารางเหนือผลการค้นหา จากนั้นคุณสามารถอ้างอิงตารางนั้นในส่วน FROM ของการค้นหา ซึ่งจะลดทั้งจำนวนข้อมูลที่ต้องประมวลผล และระยะเวลาสำหรับการประมวลผล

ใช้ตารางข้อมูลชั่วคราว
SELECT ช่อง 1, ช่อง2
FROM [ชื่อชุดข้อมูล.ชื่อตาราง];

การแก้ไขข้อบกพร่องในการค้นหา

BigQuery จะแก้ไขข้อบกพร่องในโค้ดขณะที่คุณสร้าง ในหน้าต่างการสร้าง การแก้ไขข้อบกพร่องจะแสดงอยู่ใต้การค้นหา โดยคุณยังสามารถดูจาก API โดยใช้ค่าสถานะ dryRun ได้อีกด้วย

การค้นหาที่ถูกต้องจะมีสัญญาณบอกสถานะเป็นสีเขียวที่คุณสามารถคลิกเพื่อดูจำนวนข้อมูลที่ประมวลผลโดยการค้นหาได้ ฟีเจอร์นี้ช่วยให้คุณเพิ่มประสิทธิภาพข้อมูลก่อนที่จะเรียกใช้การค้นหาเพื่อหลีกเลี่ยงการประมวลผลข้อมูลที่ไม่จำเป็นได้

Query Debugging - Success

 

การค้นหาที่ไม่ถูกต้องจะมีสัญญาณบอกสถานะเป็นสีแดงที่คุณสามารถคลิกเพื่อดูข้อมูลเกี่ยวกับข้อผิดพลาด และดูบรรทัดและคอลัมน์ที่เกิดข้อผิดพลาด ในตัวอย่างด้านล่าง คำสั่ง GROUP BY ว่างเปล่า โดยมีการบอกตำแหน่งข้อผิดพลาดที่แน่นอน

Query Debugging - Error

 

เคล็ดลับและแนวทางปฏิบัติที่ดีที่สุด

การใช้ชุดข้อมูลตัวอย่าง

ตัวอย่างต่อไปนี้ใช้ชุดข้อมูลตัวอย่างของ Google Analytics

หากต้องการใช้การค้นหาในข้อมูลของคุณเอง เพียงแทนที่ชื่อชุดข้อมูลและโครงการในตัวอย่างด้วยชื่อชุดข้อมูลและโครงการของคุณเอง

การใช้ SQL มาตรฐานเทียบกับ SQL แบบเดิม

BigQuery รองรับ SQL 2 ภาษา ได้แก่

การย้ายข้อมูลไปยัง SQL มาตรฐานจะอธิบายความแตกต่างระหว่างทั้ง 2 ภาษา

ปัจจุบัน SQL มาตรฐานเป็นภาษา SQL ที่ระบบต้องการสำหรับการค้นหาข้อมูลที่จัดเก็บไว้ใน BigQuery

ดูการเปิดใช้ SQL มาตรฐานเพื่อดูข้อมูลเกี่ยวกับการเปิดใช้ SQL มาตรฐานใน BigQuery UI, CLI, API หรืออินเทอร์เฟซใดก็ตามที่คุณใช้อยู่

วิธีที่ง่ายที่สุดในการเริ่มต้นคือการใส่ความคิดเห็น "standardSQL" ไว้ที่ด้านบนของการค้นหา SQL มาตรฐาน ดังที่แสดงในตัวอย่างต่อไปนี้

หากใช้ SQL เดิม ระบบจะส่งข้อมูล Google Analytics 360 ไปไว้ในตารางใหม่ทุกวัน หากต้องการค้นหาหลายตารางพร้อมกัน ให้ใส่เครื่องหมายคอมมาคั่นชื่อตาราง ใช้ฟังก์ชันไวลด์การ์ดของตาราง TABLE_DATE_RANGE หรือใช้ฟังก์ชัน TABLE_DATE_RANGE ที่คั่นด้วยเครื่องหมายคอมมาหลายรายการ ตามที่แสดงในตัวอย่างต่อไปนี้

สืบค้นตารางข้อมูลหลายรายการ

ตัวอย่างต่อไปนี้แสดงการค้นหาของ SQL มาตรฐานและ SQL แบบเดิมสำหรับข้อมูลเดียวกัน

3 วัน

SQL มาตรฐาน
3 วันโดยใช้ 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 เดิม
3 วันโดยใช้ชื่อตารางคั่นด้วยคอมมา
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

 

1,095 วันล่าสุด

SQL มาตรฐาน
1,095 วันล่าสุดโดยใช้ _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 เดิม
1,095 วันล่าสุดโดยใช้ 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 เดือนล่าสุด

SQL มาตรฐาน
36 เดือนล่าสุดโดยใช้ _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 เดิม
36 เดือนล่าสุดโดยใช้ 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 ปีล่าสุด

SQL มาตรฐาน
3 ปีล่าสุดโดยใช้ _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 เดิม
3 ปีล่าสุดโดยใช้ 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

 

ช่วงวันที่เฉพาะเจาะจง

SQL มาตรฐาน
ช่วงวันที่ที่เฉพาะเจาะจงโดยใช้ _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 เดิม
ช่วงวันที่ที่เฉพาะเจาะจงโดยใช้ 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

3 ปีล่าสุดบวกด้วยข้อมูลของวันนี้ (ระหว่างวัน)

SQL มาตรฐาน
3 ปีล่าสุดบวกด้วยข้อมูลของวันนี้ (ระหว่างวัน) โดยใช้ UNION ALL & _TABLE_SUFFIX
โปรดทราบว่าการค้นหาตัวอย่างนี้จะไม่ทำงานกับชุดข้อมูลสาธารณะของ Google Analytics เนื่องจากปัจจุบันไม่มีตารางระหว่างวัน
#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 เดิม

3 ปีล่าสุดบวกด้วยข้อมูลของวันนี้ (ระหว่างวัน) โดยใช้ TABLE_DATE_RANGE หลายรายการ
โปรดทราบว่าการค้นหาตัวอย่างนี้จะไม่ทำงานกับชุดข้อมูลสาธารณะของ Google Analytics เนื่องจากปัจจุบันไม่มีตารางระหว่างวัน
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

 

 

ตัวอย่างการค้นหาพื้นฐาน

ส่วนนี้อธิบายวิธีสร้างการค้นหาพื้นฐานโดยใช้เมตริกและมิติข้อมูลจากตัวอย่างข้อมูลของ Analytics

จำนวน [เมตริก] ทั้งหมดต่อ [มิติข้อมูล]

ด้านล่างคือสคริปต์ตัวอย่างสำหรับคำถาม เบราว์เซอร์แต่ละตัวบนอุปกรณ์มีการสร้างธุรกรรมในเดือนกรกฎาคม 2017 เป็นจำนวนเท่าใด

SQL มาตรฐาน

จำนวนธุรกรรมรวมต่อเบราว์เซอร์อุปกรณ์ในเดือนกรกฎาคม 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 เดิม

จำนวนธุรกรรมรวมต่อเบราว์เซอร์อุปกรณ์ในเดือนกรกฎาคม 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

 

 

อัตราตีกลับโดยเฉลี่ยต่อ [มิติข้อมูล]

อัตราตีกลับที่แท้จริงคือเปอร์เซ็นต์ของการเข้าชมที่มีการดูหน้าเว็บเพียงหน้าเดียว ด้านล่างคือสคริปต์ตัวอย่างสำหรับคำถามแหล่งที่มาของการเข้าชมแต่ละแหล่งมีอัตราตีกลับจริงเป็นอย่างไร

SQL มาตรฐาน

อัตราตีกลับต่อแหล่งที่มาของการเข้าชมในเดือนกรกฎาคม 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 เดิม

อัตราตีกลับต่อแหล่งที่มาของการเข้าชมในเดือนกรกฎาคม 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

 

 

จำนวนการดูหน้าเว็บผลิตภัณฑ์โดยเฉลี่ยตามประเภทผู้ซื้อ (ผู้ซื้อเทียบกับผู้ใช้ที่ไม่ต้องการซื้อ)

ด้านล่างคือสคริปต์ตัวอย่างสำหรับคำถามผู้ใช้ที่ซื้อมีการดูหน้าเว็บผลิตภัณฑ์โดยเฉลี่ยในเดือนกรกฎาคม 2017 เป็นจำนวนเท่าใด

SQL มาตรฐาน

จำนวนการดูหน้าเว็บผลิตภัณฑ์โดยเฉลี่ยของผู้ใช้ที่ซื้อในเดือนกรกฎาคม 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 เดิม

จำนวนการดูหน้าเว็บผลิตภัณฑ์โดยเฉลี่ยของผู้ใช้ที่ซื้อในเดือนกรกฎาคม 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 )

 

 

ด้านล่างคือสคริปต์ตัวอย่างสำหรับคำถามผู้ใช้ที่ไม่ได้ซื้อมีการดูหน้าเว็บผลิตภัณฑ์โดยเฉลี่ยในเดือนกรกฎาคม 2017 เป็นจำนวนเท่าใด

SQL มาตรฐาน

จำนวนการดูหน้าเว็บผลิตภัณฑ์โดยเฉลี่ยของผู้ใช้ที่ไม่ได้ซื้อในเดือนกรกฎาคม 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 เดิม

จำนวนการดูหน้าเว็บผลิตภัณฑ์โดยเฉลี่ยของผู้ใช้ที่ไม่ได้ซื้อในเดือนกรกฎาคม 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 )

 

 

จำนวนการทำธุรกรรมโดยเฉลี่ยต่อผู้ซื้อ

ด้านล่างคือสคริปต์ตัวอย่างสำหรับคำถามผู้ใช้ที่ซื้อแต่ละคนมีจำนวนการทำธุรกรรมโดยเฉลี่ยในเดือนกรกฎาคม 2017 เป็นเท่าใด

SQL มาตรฐาน

จำนวนการทำธุรกรรมโดยเฉลี่ยต่อผู้ใช้ที่ซื้อในเดือนกรกฎาคม 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 เดิม

จำนวนการทำธุรกรรมโดยเฉลี่ยต่อผู้ใช้ที่ซื้อในเดือนกรกฎาคม 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 )

 

 

จำนวนเงินที่ใช้โดยเฉลี่ยต่อเซสชัน

ด้านล่างคือสคริปต์ตัวอย่างสำหรับคำถามแต่ละเซสชันในเดือนกรกฎาคม 2017 มีการใช้เงินโดยเฉลี่ยเป็นจำนวนเท่าใด

SQL มาตรฐาน

จำนวนเงินที่ใช้โดยเฉลี่ยต่อเซสชันในเดือนกรกฎาคม 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 เดิม

จำนวนเงินที่ใช้โดยเฉลี่ยต่อเซสชันในเดือนกรกฎาคม 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 )

 

 

ลำดับของ Hit

ด้านล่างคือสคริปต์ตัวอย่างสำหรับคำถามหน้าเว็บมีลำดับการดูเป็นอย่างไร

SQL มาตรฐาน

ลำดับการดูหน้าเว็บของผู้ใช้ในเดือนกรกฎาคม 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 เดิม

ลำดับการดูหน้าเว็บของผู้ใช้ในเดือนกรกฎาคม 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

ในการค้นหานี้ เรามีการจำกัดประเภทของ Hit เป็นPAGESเพื่อแยกเหตุการณ์หรือการทำธุรกรรมออกไป เอาต์พุตแต่ละบรรทัดจะแสดงการดูหน้าเว็บโดยแสดงช่องตามลำดับที่เป็นค่าเริ่มต้นในคำสั่ง SELECT

 

 

มิติข้อมูลที่กำหนดเองหลายรายการที่ระดับ Hit หรือเซสชัน

มิติข้อมูลที่กำหนดเองที่ระดับ Hit
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

 

มิติข้อมูลที่กำหนดเองที่ระดับเซสชัน
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

ในการค้นหาแต่ละรายการ

คำสั่ง SELECT จะค้นหาช่องมิติข้อมูลและเมตริกที่เกี่ยวข้อง

ฟังก์ชัน MAX

  • จะแสดงมิติข้อมูลที่กำหนดเองเป็นคอลัมน์ใหม่ คุณสามารถใช้ฟังก์ชันนี้ซ้ำเพื่อให้แสดงมิติข้อมูลที่กำหนดเองเป็นคอลัมน์ใหม่หลายรายการ
  • WITHIN hits และ WITHIN RECORD จะประเมินเงื่อนไขที่อยู่ภายในช่องต่างๆ ใน BigQuery
  • จะมีการประเมินเงื่อนไขภายใน MAX สำหรับมิติข้อมูลที่กำหนดเองแต่ละรายการ โดยรายการที่ไม่ใช่ index=1 (hits) หรือ index=2 (sessions) จะแสดงเป็น NULL
  • จะแสดงค่าสูงสุด ซึ่งเป็นค่าของ Custom Dimension 1 สำหรับ Hit หรือ Custom Dimension 2 สำหรับเซสชัน เนื่องจากค่าอื่นๆ ทั้งหมดเป็น NULL

ตัวอย่างการค้นหาขั้นสูง

เมื่อคุณคุ้นเคยกับการค้นหาพื้นฐานแล้ว คุณสามารถสร้างการค้นหาโดยใช้ฟังก์ชันและคุณลักษณะขั้นสูงที่มีอยู่ใน BigQuery

ผลิตภัณฑ์ที่ซื้อโดยลูกค้าที่ซื้อผลิตภัณฑ์ A (อีคอมเมิร์ซแบบคลาสสิก)

ข้อมูลด้านล่างคือโครงสร้างสคริปต์สำหรับคำถามที่ว่าลูกค้าที่ซื้อผลิตภัณฑ์ A มีการซื้อผลิตภัณฑ์ใดเพิ่มเติม

ผลิตภัณฑ์ที่ซื้อโดยลูกค้าที่ซื้อผลิตภัณฑ์ A (อีคอมเมิร์ซแบบคลาสสิก)
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. ในบรรทัดแรก คุณจะเลือกรายการอื่นๆ ทั้งหมดที่ผู้ใช้ซื้อ และใช้ฟังก์ชันการรวม COUNT() ในการคำนวณจำนวนการซื้อรายการอื่นๆ แต่ละรายการ ผลลัพธ์ที่ได้จะแสดงอยู่ในช่อง quantity พร้อมด้วยรายการที่เกี่ยวข้องในช่องผลิตภัณฑ์ other_purchased_products
  2. ในการค้นหาย่อยที่เป็นสีเทา คุณจะเลือกเฉพาะผู้ใช้ที่ไม่ซ้ำ (fullVisitorId) ที่มีการทำธุรกรรม (totals.transactions>=1) ในระหว่างที่ซื้อผลิตภัณฑ์ A (WHERE hits.item.productName CONTAINS 'Product Item Name A')

กฎ (คำสั่ง WHERE และ AND) ในการค้นหาระดับบนสุด (สีเขียว) จะไม่สนใจค่าใน hits.item.productName ซึ่งเป็น Null และมีผลิตภัณฑ์ A

ข้อมูลด้านล่างคือตัวอย่างของการค้นหาหากลูกค้าซื้อ Brighton Metallic Pens - Set of 4 พวกเขาจะซื้อผลิตภัณฑ์ใดเพิ่มเติม

ผลิตภัณฑ์ที่ซื้อโดยลูกค้าที่ซื้อ "Brighton Metallic Pens (Set of 4)" ในวันที่ 24 มิถุนายน 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;

ใน Dremel/BigQuery การใช้ WHERE expr IN จะทริกเกอร์ JOIN และจะมีการนำข้อจำกัดเกี่ยวกับขนาดไปใช้ โดยเฉพาะขนาดที่อยู่ทางด้านขวาของ JOIN (ในกรณีนี้คือจำนวนผู้เข้าชม) จะต้องน้อยกว่า 8 MB ใน Dremel ซึ่งเรียกว่า broadcast JOIN เมื่อไฟล์มีขนาดเกิน 8 MB คุณต้องทริกเกอร์shuffled JOIN ซึ่งทําได้โดยใช้ไวยากรณ์ JOIN EACH แต่ไม่สามารถใช้ IN ซึ่งสามารถเขียนการค้นหาใหม่ได้โดยใช้ JOIN

ผลิตภัณฑ์ที่ซื้อโดยลูกค้าที่ซื้อผลิตภัณฑ์ A (อีคอมเมิร์ซที่เพิ่มประสิทธิภาพ)

ส่วนนี้คล้ายกับโครงสร้างการค้นหาก่อนหน้า แต่ทำงานกับอีคอมเมิร์ซที่เพิ่มประสิทธิภาพ นอกจากนี้ยังใช้ TABLE_DATE_RANGE เพื่อการค้นหาข้อมูลเป็นเวลาหลายวัน

ผลิตภัณฑ์ที่ซื้อโดยลูกค้าที่ซื้อผลิตภัณฑ์ A (อีคอมเมิร์ซที่เพิ่มประสิทธิภาพ)
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;

จำนวนการโต้ตอบของผู้ใช้โดยเฉลี่ยก่อนซื้อ

ข้อมูลด้านล่างนี้คือตัวอย่างของการค้นหาที่ใช้คำสั่ง JOIN() [...] ON ซึ่งจะใช้ข้อมูลใน Google Analytics เท่านั้น

โครงสร้างสคริปต์สำหรับคำถามที่ว่าผู้ใช้มีการโต้ตอบโดยเฉลี่ยก่อนซื้อกี่ครั้งมีดังนี้

จำนวนการโต้ตอบของผู้ใช้ก่อนซื้อ
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. ในบรรทัดแรกจะใช้การคำนวณทางคณิตศาสตร์หลักเพื่อหาการโต้ตอบของผู้ใช้โดยเฉลี่ยต่อผลิตภัณฑ์ และการค้นหานี้จะสร้างการรวมระหว่างการค้นหาย่อย 2 รายการ "Alias_Name_1" และ "Alias_Name_2"
  2. "Alias_Name_1" จะใช้ในการสร้างช่องที่ใช้ฟังก์ชันการรวม SUM() เพื่อรวมจำนวน Hit ทั้งหมดที่บันทึกไว้สำหรับผลิตภัณฑ์หนึ่ง
  3. "Alias_Name_2" จะใช้ในการหาจำนวน Hit ที่เกิดจากผู้ใช้ต่อผลิตภัณฑ์โดยใช้ฟังก์ชัน COUNT()
  4. บรรทัดสุดท้ายจะแสดงช่องร่วม (hits.item.productSku) ที่ใช้ร่วมกันระหว่างชุดข้อมูล 2 ชุดในการรวม

ข้อมูลด้านล่างคือตัวอย่างของการค้นหาที่ว่าผู้ใช้มีการโต้ตอบโดยเฉลี่ยก่อนซื้อกี่ครั้งในวันที่ 10 กันยายน 2013

จำนวนการโต้ตอบของผู้ใช้ก่อนซื้อในวันที่ 10 กันยายน 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;

เปอร์เซ็นต์การขายจากสต็อกสินค้าต่อผลิตภัณฑ์

ข้อมูลด้านล่างคือตัวอย่างของการค้นหาที่ไม่ได้ใช้เฉพาะข้อมูล Analytics แต่ใช้ข้อมูลที่ไม่ได้มาจาก Analytics ด้วย การรวมชุดข้อมูลทั้งสองจะช่วยให้คุณเข้าใจพฤติกรรมของผู้ใช้ในระดับกลุ่มมากขึ้น คุณสามารถนำเข้าข้อมูลที่ไม่ได้มาจาก Analytics เข้าสู่ BigQuery แต่โปรดทราบว่าข้อมูลนี้จะมีค่าบริการจัดเก็บข้อมูลรายเดือนด้วย

โครงสร้างสคริปต์สำหรับคำถามที่ว่าผลิตภัณฑ์แต่ละรายการมีการขายจากสต็อกสินค้ากี่เปอร์เซ็นต์มีดังนี้

เปอร์เซ็นต์การขายจากสต็อกสินค้าต่อผลิตภัณฑ์
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. บรรทัดแรกให้ช่องข้อมูลออกมา 2 ช่อง โดยช่องหนึ่งจะมีรหัสผลิตภัณฑ์ทั้งหมด และอีกช่องหนึ่งจะเป็นการคำนวณทางคณิตศาสตร์ซึ่งแสดงเปอร์เซ็นต์การขายจากสต็อกสินค้าสำหรับรหัสผลิตภัณฑ์นั้น
  2. เนื่องจากการค้นหานี้จะใช้ชุดข้อมูล 2 ชุด คุณจึงต้องใช้ฟังก์ชัน JOIN() ... ON คำสั่งนี้จะรวมแถวจากชุดข้อมูล 2 ชุดเข้าด้วยกันตามเงื่อนไขของช่องร่วมที่ใช้ร่วมกัน ในกรณีนี้ ชุดข้อมูล 2 ชุดคือ [ ‘Imported_DataSet’ ] และ ‘Alias_Name’
  3. [ ‘Imported_DataSet’ ] เป็นข้อมูลที่ไม่ได้มาจาก Analytics ชุดข้อมูลนี้มีช่องเมตริกที่บอกให้ทราบว่ามีสินค้าเหลืออยู่ในสต็อกเป็นจำนวนเท่าใด (Imported DataSet.’stock_left_field’) และช่องมิติข้อมูลรหัสผลิตภัณฑ์ (Imported_DataSet.’productId_field’)
  4. "Alias_Name" คือชื่อที่กำหนดให้กับข้อมูลที่ได้จากการค้นหาย่อยที่เป็นสีเทา การค้นหาย่อยนี้ใช้ข้อมูลจาก Analytics ในการหาจำนวนสินค้าที่ขายต่อผลิตภัณฑ์
  5. ในบรรทัดสุดท้ายจะใช้คำสั่ง ON เพื่อแสดงช่องร่วมที่ใช้ร่วมกันระหว่างชุดข้อมูล 2 ชุด และเป็นที่ที่มีการรวมชุดข้อมูล 2 ชุดเข้าด้วยกัน

ตัวแปรที่ใช้ในการค้นหานี้จะมีชื่อชุดข้อมูลปรากฏอยู่ที่ด้านหน้า (เช่น Imported_DataSet.’productId_field’, Alias_Name.quantity_sold). ซึ่งเป็นการบอกให้ทราบว่าคุณกำลังเลือกช่องใด และเป็นของชุดข้อมูลใด

ข้อมูลด้านล่างคือตัวอย่างของการค้นหาผลิตภัณฑ์แต่ละรายการมีการขายจากสต็อกสินค้ากี่เปอร์เซ็นต์ในวันที่ 28 กรกฎาคม 2013

เปอร์เซ็นต์การขายจากสต็อกสินค้าต่อผลิตภัณฑ์ในวันที่ 28 กรกฎาคม 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;

ความสามารถในการทำกำไรของแต่ละผลิตภัณฑ์

ข้อมูลด้านล่างคือโครงสร้างสคริปต์สำหรับคำถามที่ว่าแต่ละผลิตภัณฑ์มีความสามารถในการทำกำไรเป็นอย่างไร

กำไรตามผลิตภัณฑ์
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. บรรทัดแรกมีการคำนวณทางคณิตศาสตร์ที่ใช้ในการคำนวณหากำไรรวมที่ได้รับจากแต่ละผลิตภัณฑ์
  2. การค้นหาย่อยที่เป็นสีเทาจะใช้ข้อมูลที่ไม่ใช่ของ Analytics ซึ่งรวบรวมข้อมูลเกี่ยวกับกำไรที่ได้รับเมื่อขายผลิตภัณฑ์
  3. การค้นหาย่อยสีแดงคือการค้นหาย่อยที่ใช้ข้อมูลจาก Analytics ซึ่งจะมีการนำไปรวมกับข้อมูลที่ไม่ใช่ของ Analytics การค้นหาย่อยนี้เป็นการคำนวณจำนวนสินค้าที่ขายต่อผลิตภัณฑ์
  4. บรรทัดสุดท้ายจะใช้คำสั่ง ON เพื่อระบุช่องที่ชุดข้อมูล 2 ชุดใช้ร่วมกัน ในกรณีนี้จะเป็นหมายเลขรหัสผลิตภัณฑ์

ข้อมูลด้านล่างคือตัวอย่างของการค้นหาแต่ละผลิตภัณฑ์มีความสามารถในการทำกำไรเป็นอย่างไรในวันที่ 28 กรกฎาคม 2013

กำไรตามผลิตภัณฑ์ในวันที่ 28 กรกฎาคม 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 );

คำนวณกำไรได้โดยการหาผลต่างระหว่างราคาขายผลิตภัณฑ์กับต้นทุนการผลิต ข้อมูลนี้ถูกจัดเก็บไว้ในชุดข้อมูลที่ไม่ใช่ของ GA

ความสามารถในการทำกำไรได้จริงของแต่ละผลิตภัณฑ์ (คำนึงถึงการขอเงินคืนด้วย)

ข้อมูลด้านล่างคือโครงสร้างสคริปต์สำหรับคำถามที่ว่าแต่ละผลิตภัณฑ์มีความสามารถในการทำกำไรได้จริงเป็นอย่างไร

กำไรที่แท้จริงตามผลิตภัณฑ์
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. ข้อมูลนี้มีความคล้ายคลึงอย่างมากกับการค้นหาแต่ละผลิตภัณฑ์มีความสามารถในการทำกำไรเป็นอย่างไร โดยมีข้อแตกต่างแค่เพียงในชุดข้อมูลที่ไม่ใช่ของ Analytics ในการค้นหาย่อยที่เป็นสีเทา และการคำนวณทางคณิตศาสตร์ในการคำนวณหากำไรที่แท้จริงในบรรทัดแรก
  2. ในชุดข้อมูลที่ไม่ได้มาจาก Analytics คุณจะคำนวณจำนวนเงินทั้งหมดที่ใช้ในการคืนเงิน (ในคำสั่ง SELECT ของการค้นหาย่อยสีแดง)
  3. จากนั้นคุณจะใช้การคำนวณทางคณิตศาสตร์ในบรรทัดที่ 1 เพื่อหากำไรที่แท้จริงโดยนำรายได้ที่นำไปใช้ในการคืนเงินหักออกจากกำไรขั้นต้น

สำหรับข้อมูลเพิ่มเติมเกี่ยวกับการค้นหา โปรดดูที่ส่วนความสามารถในการทำกำไรของแต่ละผลิตภัณฑ์

ข้อมูลด้านล่างคือตัวอย่างของการค้นหาแต่ละผลิตภัณฑ์มีความสามารถในการทำกำไรได้จริงเป็นอย่างไรในวันที่ 28 กรกฎาคม 2013

กำไรที่แท้จริงตามผลิตภัณฑ์ในวันที่ 28 กรกฎาคม 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 ) );

กำไรที่แท้จริงจะคำนึงถึงความสามารถในการทำกำไรของผลิตภัณฑ์หลังจากที่หักออกจากผลิตภัณฑ์ที่มีการขอคืนเงินแล้ว รายได้ของผลิตภัณฑ์ก่อนหักเงินคืนสามารถคำนวณได้ดังนี้

รายได้ทั้งหมดของผลิตภัณฑ์ก่อนหักเงินคืน = ( ราคาผลิตภัณฑ์ + ค่าจัดส่งผลิตภัณฑ์ที่ขอเงินคืน ) * จำนวนผลิตภัณฑ์ที่ขอเงินคืน

ข้อมูลนี้มีประโยชน์ไหม

เราจะปรับปรุงได้อย่างไร

หากต้องการความช่วยเหลือเพิ่มเติม

ลองทำตามขั้นตอนต่อไปนี้

โพสต์ในชุมชนความช่วยเหลือ ดูคําตอบจากสมาชิกในชุมชน
ติดต่อเรา บอกเราเพิ่มเติมและเราจะช่วยเหลือคุณ
true
learning path
เลือกเส้นทางการเรียนรู้ของคุณเอง

โปรดไปที่ google.com/analytics/learn ซึ่งเป็นแหล่งข้อมูลใหม่ที่จะช่วยให้คุณได้รับประโยชน์สูงสุดจาก Google Analytics 4 เว็บไซต์ใหม่นี้มีทั้งวิดีโอ บทความ และขั้นตอนพร้อมคำแนะนำ รวมถึงลิงก์ไปยังแหล่งข้อมูลต่างๆ เกี่ยวกับ Google Analytics เช่น Discord, บล็อก, ช่อง YouTube ตลอดจนที่เก็บ GitHub

เริ่มเรียนรู้วันนี้เลย

ค้นหา
ล้างการค้นหา
ปิดการค้นหา
เมนูหลัก
4395808138721358795
true
ค้นหาศูนย์ช่วยเหลือ
true
true
true
true
true
69256
false
false