---
# System prepended metadata

title: 【Part6 電商GA4分析 - Mysql 是什麼？Mysql 優勢、指令】

---

# **【Part6 電商GA4分析 - Mysql 是什麼？Mysql 優勢、指令】**
:::info
* [Part1](https://hackmd.io/@workcata/BJtzg-kyT)
* [Part2](https://hackmd.io/@workcata/ry4sDHr4gg)
* [Part3](https://hackmd.io/@workcata/r1n39SHNxx)
* [Part4](https://hackmd.io/@workcata/SkmdnHH4el)
* [Part5](https://hackmd.io/@workcata/HkQ56gV1a)
* 練習
* 電商網站流量練習
	- 關於廣告
		- 查看不同廣告UTM效益
		- 查看轉換率
	- 關於流量來源
		- 查看流量來源網站
		- 找到某段時間內的資料
		- 不同產品在「單件/兩件」購買模式下的訂單量 (樞紐分析)
		- 每週流量表現
	- 關於設備
		- 不同設備的表現、轉換率
		- 不同搜尋引擎轉換率、mobile/desktop 佔多少
		- 不同搜尋裝置、不同搜尋引擎的轉換率
		- 是否為直接流量
	- 關於網站優化
		- 找著陸頁訪問數
		- 每個頁面 URL 的總瀏覽量
		- 每個著陸頁所吸引的會話數
		- 查看某個特定時間或事件點之後的流量表現
	- 關於產品銷售
		- 查看訂單時間
		- 查看每週訂單
		- 每天各點入站的數量
		- 每小時平均訪問數量、分天看
	- 關於產品組合
		- 查看產品訂單數、總銷售金額、總利潤、平均金額/張訂單
		- 查看每月網站訂單、不重複id訪問數的轉換率、產品訂單
		- 主要產品的銷售情況以及它們所帶動的交叉銷售 (cross-sell) 產品
	- 關於產品退貨率
		- 分年、月，查看各產品退貨率
* [W3School SQL Tutorial](https://www.w3schools.com/sql/)
:::

## 練習

### 從表a中，選一些欄位，插入表b中
```=
table1_b 要先創建

INSERT INTO  table_b(field1,field2)  SELECT  field1,field2  FROM table_a
```

```=
CREATE TABLE table_b AS
SELECT field1, field2 FROM table_a; 
```

### 從表a中選一些欄位、表b中選一些欄位，插入表c中
```=
table_c 要先創建

INSERT INTO table_c (field1, field2, field3, field4)
SELECT table_a.field1, table_a.field2, table_b.field3, table_b.field4
FROM table_a
JOIN table_b ON (your_join_condition);
```

```=
INSERT INTO employee_department_summary (employee_name, department_name, hire_year)
SELECT
    e.employee_name,           -- 從 employees 表中選取 employee_name
    d.department_name,         -- 從 departments 表中選取 department_name
    YEAR(e.hire_date)          -- 從 employees 表中選取 hire_date 的年份
FROM
    employees AS e             -- 將 employees 表取別名為 e
JOIN
    departments AS d ON e.department_id = d.department_id; -- 將 departments 表取別名為 d，並根據 department_id 關聯
```

```=
CREATE TABLE table_c AS
SELECT field1, field2 FROM table_a
UNION
SELECT field3, field4 FROM table_b;
```

### 最早的五筆
```=
SELECT * FROM users
ORDER BY created at
LIMIT 5;
```

### 星期一~星期日，哪天最多人註冊?
```=
SELECT DAYNAME(created_at) As day, COUNT(*) AS total FROM users
GROUP BY day
ORDER BY total DESC;
```

### 註冊過用戶，但沒分享過照片
```=
SELECT username FROM users
LEFT JOIN photos ON users.id = photos.user_id
WHERE photos.id IS NULL;
```

### 最多Like的照片?
```=
SELECT photos.id, image_url, COUNT(*) AS total FROM photos
JOIN likes ON likes.photo_id = photos.id
GROUP BY photos.id
ORDER BY total DESC
LIMIT 1;
```

### 使用者平均發文
```=
SELECT (SELECT Count(*) FROM photos) / (SELECT Count(*) FROM users) AS avg;
```

### 誰按了每篇貼文讚?
```=
SELECT username, Count(*) AS num_likes FROM users
JOIN likes ON users.id = likes.user_id
GROUP BY likes.user_id
HAVING num_likes = (SELECT Count(*) FROM photos);

= SELECT users.id, username, COUNT(*) AS total FROM likes
JOIN users ON users.id = likes.user_id
GROUP BY users.id HAVING total = (SELECT COUNT(*) FROM photos);
```

### 兩者差異?
```=
選出名字中不包含e，且年齡>30的
SELECT NOT name LIKE '%e%' AND age > 30 FROM employee;

選出NOT(名字有e，且年齡大於30) => 名字不含e，且年齡不大於30
SELECT NOT (name LIKE '%e%' AND age > 30) FROM employee;
```

<br/>

## 電商網站流量來源

### 關於廣告

#### 查看不同廣告UTM效益
```=
SELECT utm_content, COUNT(DISTINCT website_session_id) AS sessions  -- 不重複網站會話數量
FROM website_sessions
WHERE website_session_id BETWEEN 1000 AND 2000
GROUP BY utm_content
ORDER BY COUNT(DISTINCT website_session_id) DESC;

# 同上
SELECT utm_content, COUNT(DISTINCT website_session_id) AS sessions
FROM website_sessions
WHERE website_session_id BETWEEN 1000 AND 2000
GROUP BY 1
ORDER BY 2 DESC;
```

![螢幕擷取畫面 2023-11-13 174725](https://hackmd.io/_uploads/rJeAxdkNa.png)

#### 查看轉換率
```=
SELECT 
	website_sessions.utm_content, 
	COUNT(DISTINCT website_sessions.website_session_id) AS sessions,
	COUNT(DISTINCT orders.order_id) AS orders,
        COUNT(DISTINCT orders.order_id) /COUNT(DISTINCT website_sessions.website_session_id) AS session_to_order_convert
FROM website_sessions
	LEFT JOIN orders
        ON orders.website_session_id = website_sessions.website_session_id
WHERE website_sessions.website_session_id BETWEEN 1000 AND 2000
GROUP BY 1
ORDER BY 2 DESC;
```
![螢幕擷取畫面 2023-11-13 181431](https://hackmd.io/_uploads/SyvMP_kVp.png)

<br/>

#### 查看轉換率_2
```=
SELECT 
	website_sessions.utm_content, 
	COUNT(DISTINCT website_sessions.website_session_id) AS sessions,
	COUNT(DISTINCT orders.order_id) AS orders,
    COUNT(DISTINCT orders.order_id) /COUNT(DISTINCT website_sessions.website_session_id) AS session_to_order_convert
FROM website_sessions
	LEFT JOIN orders
	ON orders.website_session_id = website_sessions.website_session_id
WHERE website_sessions.created_at < '2012-04-14'
	AND utm_source = 'gsearch'
	AND utm_campaign = 'nonbrand'
GROUP BY 1
ORDER BY 2 DESC;
```
![螢幕擷取畫面 2023-11-14 131230](https://hackmd.io/_uploads/HkH0bFxVp.png)

<br/>

#### 查看轉換率_3
```=
SELECT 
	utm_content,
	COUNT(DISTINCT website_sessions.website_session_id) AS sessions,
	COUNT(DISTINCT orders.order_id) AS orders,
	COUNT(DISTINCT orders.order_id)/COUNT(DISTINCT website_sessions.website_session_id) AS ses
FROM website_sessions
	LEFT JOIN orders
	ON orders.website_session_id = website_sessions.website_session_id
WHERE website_sessions.created_at BETWEEN '2014-01-01' AND '2014-02-01'  
GROUP BY 1
ORDER BY sessions DESC 
```
![螢幕擷取畫面 2023-11-14 171809](https://hackmd.io/_uploads/B1KPi3xVp.png)

<br/>

### 關於流量來源

#### 查看流量來源網站
```=
SELECT DISTINCT
    utm_source,
    utm_campaign,
    http_referer
FROM website_sessions
WHERE created_at < '2012-04-12'
ORDER BY utm_source, utm_campaign, http_referer;
```
![螢幕擷取畫面 2023-11-14 122759](https://hackmd.io/_uploads/Hywwvue4a.png)


如果想知道數量，要加上 COUNT(DISTINCT...) + GROUP BY

```=
SELECT
	utm_source,
	utm_campaign,
	http_referer,
	COUNT(DISTINCT website_session_id) As number_of_sessions
FROM website_sessions
WHERE created_at < '2012-04-12'
GROUP BY
	utm_source,
	utm_campaign,
	http_referer
ORDER BY number_of_sessions DESC;
```
![螢幕擷取畫面 2023-11-13 180805](https://hackmd.io/_uploads/r1lcS_14p.png)

PS 3、5 只有網址、沒有具體的 "utm_source" 或 "utm_campaign" ，可能是直接訪問網站，而非通過特定搜索或廣告活動


#### 找到某段時間內的資料

有幾個月份有資料
```=
SELECT 
    MONTH(created_at) AS month,
    COUNT(DISTINCT website_session_id) AS session_count
FROM website_sessions
WHERE website_session_id BETWEEN 100000 AND 115000
GROUP BY 1;
```
![螢幕擷取畫面 2023-11-14 140925](https://hackmd.io/_uploads/HkZ4JqxN6.png)

週和週開始的日期
```=
SELECT 
    YEAR(created_at),
    WEEK(created_at),
    MIN(DATE(created_at)) AS week_start,
    COUNT(DISTINCT website_session_id) AS sessions
FROM website_sessions
WHERE website_session_id BETWEEN 100000 AND 115000
GROUP BY 1,2
```
![螢幕擷取畫面 2023-11-14 141227](https://hackmd.io/_uploads/ByOAyclVT.png)

### 不同產品在「單件/兩件」購買模式下的訂單量 (樞紐分析)

有多少筆訂單是顧客買了一件
有多少筆訂單是顧客買了兩件...

PS GROUP 可以分出不同的列，但如果要不同的欄(類似EXCEL 樞紐分析欄位），不同值從行 (rows) 轉換成不同的欄 (columns)，要使用　COUNT、DISTINCT + (CASE WHEN...ELSE)

前面先算出 每筆訂單的總購買數量
SELECT order_id, SUM(quantity) AS items_purchased
```=
SELECT
    primary_product_id,
    COUNT(DISTINCT CASE WHEN items_purchased = 1 THEN order_id ELSE NULL END) AS order_w_1_item,
    COUNT(DISTINCT CASE WHEN items_purchased = 2 THEN order_id ELSE NULL END) AS order_w_2_item,
    COUNT(DISTINCT order_id) AS total_orders
FROM orders
WHERE order_id BETWEEN 31000 AND 32000
GROUP BY 1;
```
![螢幕擷取畫面 2023-11-14 143654](https://hackmd.io/_uploads/SJI5SceNp.png)


### 每週流量表現
假設4/15那週開始降低【非品牌關鍵字】的廣告預算，能看出廣告預算對流量真的有影響
```=
SELECT
	YEAR(created_at) AS yr,
	WEEK(created_at) AS wk,
	MIN(DATE(created_at)) AS week_started_at,
	COUNT(DISTINCT website_session_id) As sessions
FROM website_sessions
WHERE created_at < '2012-05-12'
	AND utm_source = 'gsearch'
	AND utm_campaign = 'nonbrand'
GROUP BY
	YEAR(created_at),
	WEEK(created_at)
```
![螢幕擷取畫面 2023-11-14 151643](https://hackmd.io/_uploads/H19ykse4T.png)

<br/>

### 關於設備

#### 不同設備的表現、轉換率
```=
SELECT
	website_sessions.device_type,
	COUNT(DISTINCT website_sessions.website_session_id) AS sessions,
	COUNT(DISTINCT orders.order_id) As orders,
	COUNT(DISTINCT orders.order_id)/COUNT(DISTINCT website_sessions.website_session_id) AS conv_rt
FROM website_sessions
	LEFT JOIN orders
		ON orders.website_session_id = website_sessions.website_session_id
WHERE website_sessions.created_at < '2012-05-11'
AND utm_source = 'gsearch'
AND utm_campaign = 'nonbrand'
GROUP BY 1;
```
![螢幕擷取畫面 2023-11-14 161506](https://hackmd.io/_uploads/ByZ62ilVp.png)


分年、週，不同設備的表現
假設5/20那週開始增加【desktop】的廣告預算，能看出設備對流量真的有影響
```=
SELECT
  MIN(DATE(created_at)) AS week_start_date,
  COUNT(DISTINCT CASE WHEN device_type = 'mobile' THEN website_session_id ELSE NULL END) AS mobile_sessions,
  COUNT(DISTINCT CASE WHEN device_type = 'desktop' THEN website_session_id ELSE NULL END) AS desktop_sessions
FROM website_sessions
WHERE created_at < '2012-06-09'
  AND created_at > '2012-04-15'
  AND utm_source = 'gsearch'
  AND utm_campaign = 'nonbrand'
GROUP BY
  YEAR(created_at),
  WEEK(created_at);
```
![螢幕擷取畫面 2023-11-14 163839](https://hackmd.io/_uploads/SJRzG3g4a.png)


#### 不同搜尋引擎轉換率、mobile/desktop 佔多少
```=
SELECT
	utm_source,
	COUNT(DISTINCT website_sessions.website_session_id) As sessions,
	COUNT(DISTINCT CASE WHEN device_type = 'mobile' THEN website_sessions.website_session_id ELSE NULL END) AS mobile_session,
	COUNT(DISTINCT CASE WHEN device_type = 'mobile' THEN website_sessions.website_session_id ELSE NULL END) / 
	COUNT(DISTINCT website_sessions.website_session_id) AS pct_mobile,
        
        COUNT(DISTINCT CASE WHEN device_type = 'desktop' THEN website_sessions.website_session_id ELSE NULL END) AS desktop_session,
	COUNT(DISTINCT CASE WHEN device_type = 'desktop' THEN website_sessions.website_session_id ELSE NULL END) / 
	COUNT(DISTINCT website_sessions.website_session_id) AS pct_desktop
FROM website_sessions
WHERE created_at >  '2012-08-22'
AND created_at  < '2012-11-30'
AND utm_campaign = 'nonbrand'
GROUP BY 1;
```
![螢幕擷取畫面 2023-11-14 181921](https://hackmd.io/_uploads/r1thFpxNa.png)


#### 不同搜尋裝置、不同搜尋引擎的轉換率

```=
SELECT
	website_sessions.device_type,
	website_sessions.utm_source,
	COUNT(DISTINCT website_sessions.website_session_id) AS sessions,
	COUNT(DISTINCT orders.order_id) AS orders,
	COUNT(DISTINCT orders.order_id)/COUNT(DISTINCT website_sessions.website_session_id) AS conv_rate
FROM Website_sessions
	LEFT JOIN orders
		ON orders.website_session_id = website_sessions.website_session_id
WHERE website_sessions.created_at > '2012-08-27' 
AND website_sessions.created_at < '2012-09-19'  
AND website_sessions.utm_campaign = 'nonbrand'  
GROUP BY 1,2;
```
![螢幕擷取畫面 2023-11-14 181921](https://hackmd.io/_uploads/HkVzh6x4T.png)

分週，查看不同搜尋裝置、不同搜尋引擎的轉換率，相對desktop，mobile比例
```=
SELECT
	YEARWEEK(created_at) AS year_week,
	MIN(DATE(created_at)) AS week_start_date,
	COUNT(DISTINCT CASE WHEN utm_source = 'gsearch' AND device_type = 'desktop' THEN website_session_id ELSE NULL END)AS g_dtop_sessions,
	COUNT(DISTINCT CASE WHEN utm_source = 'bsearch' AND device_type = 'desktop' THEN website_session_id ELSE NULL END) AS b_dtop_sessions,
	COUNT(DISTINCT CASE WHEN utm_source = 'bsearch' AND device_type = 'desktop' THEN website_session_id ELSE NULL END)/
	COUNT(DISTINCT CASE WHEN utm_source = 'gsearch' AND device_type ='desktop' THEN website_session_id ELSE NULL END) AS b_pct_of_g_dtop,
	COUNT(DISTINCT CASE WHEN utm_source = 'gsearch' AND device_type = 'mobile' THEN website_session_id ELSE NULL END)AS g_mob_sessions,
	COUNT(DISTINCT CASE WHEN utm_source ='bsearch' AND device_type = 'mobile' THEN website_session_id ELSE NULL END) AS b_mob_sessions,
	COUNT(DISTINCT CASE WHEN utm_source = 'bsearch' AND device_type = 'mobile' THEN website_session_id ELSE NULL END)/
	COUNT(DISTINCT CASE WHEN utm_source = 'gsearch' AND device_type = 'mobile' THEN website_session_id ELSE NULL END) AS b_pct_of_g_mob
FROM website_sessions
WHERE created_at > '2012-11-04' 
AND created_at < '2012-12-22' 
AND utm_campaign = 'nonbrand'
GROUP BY
	YEARWEEK(created_at);
```
![螢幕擷取畫面 2023-11-14 190538](https://hackmd.io/_uploads/SkQ5VClNT.png)


分週，查看不同搜尋引擎訪問量
```=
SELECT
	YEARWEEK(created_at) AS yrwk,
	COUNT(DISTINCT website_session_id) AS total_sessions,
	COUNT(DISTINCT CASE WHEN utm_source = 'gsearch' THEN website_session_id ELSE NULL END) AS gsearch_sessions,
	COUNT(DISTINCT CASE WHEN utm_source = 'bsearch' THEN website_session_id ELSE NULL END) AS bsearch_sessions
FROM website_sessions
WHERE created_at > '2012-08-22'  
AND created_at < '2012-11-29'  
AND utm_campaign = 'nonbrand' 
GROUP BY YEARWEEK(created_at);
```
![螢幕擷取畫面 2023-11-14 174703](https://hackmd.io/_uploads/rJJEfpeNp.png)


#### 是否為直接流量
搭配條件(類似EXCEL IFS) CASE WHEN...THEN...、ELSE...END
```=
SELECT
	CASE
		WHEN http_referer IS NULL THEN 'direct_type_in'
		WHEN http_referer = 'https://www.gsearch.com' THEN 'gsearch_organic'
		WHEN http_referer = 'https://ww.bsearch.com' THEN 'bsearch_organic'
		ELSE 'other'
	END AS referral_type,
	COUNT(DISTINCT website_session_id) AS sessions
FROM website_sessions
WHERE website_session_id BETWEEN 100000 AND 115000  
	AND utm_source IS NULL
GROUP BY 1
ORDER BY 2 DESC;
```
![螢幕擷取畫面 2023-11-14 194751](https://hackmd.io/_uploads/SJVdCRxVT.png)


#### 品牌廣告、直接流量、自然流量站廣告多少比例
```=
SELECT
	YEAR(created_at) AS yr,
	MONTH(created_at) AS mo,
	COUNT(DISTINCT CASE WHEN channel_group ='paid_nonbrand' THEN website_session_id ELSE NULL END) AS nonbrand,
	COUNT(DISTINCT CASE WHEN channel_group ='paid_brand' THEN website_session_id ELSE NULL END) AS brand, 
	COUNT(DISTINCT CASE WHEN channel_group='paid_brand' THEN website_session_id ELSE NULL END) 
	 /COUNT(DISTINCT CASE WHEN channel_group ='paid_nonbrand' THEN website_session_id ELSE NULL END) AS brand_pct_of_nonbrand, 
	
	COUNT(DISTINCT CASE WHEN channel_group ='direct_type_in' THEN website_session_id ELSE NULL END) AS direct, 
	COUNT(DISTINCT CASE WHEN channel_group ='direct_type_in' THEN website_session_id ELSE NULL END) 
	/COUNT(DISTINCT CASE WHEN channel_group ='paid_nonbrand' THEN website_session_id ELSE NULL END) AS direct_pct_of_nonbrand, 
	
	COUNT(DISTINCT CASE WHEN channel_group = 'organic_search' THEN website_session_id ELSE NULL END) AS organic,
	COUNT(DISTINCT CASE WHEN channel_group = 'organic search' THEN website_session_id ELSE NULL END)
	/COUNT(DISTINCT CASE WHEN channel_group ='paid_nonbrand' THEN website_session_id ELSE NULL END) AS direct_pct_of_nonbrand

FROM(
SELECT 
	website_session_id,
    created_at,
CASE
	WHEN utm_source IS NULL AND http_referer IN('https ://ww.gsearch.com', 'https://www.bsearch.com') THEN 'organic_search'
	WHEN utm_campaign = 'nonbrand' THEN 'paid_nonbrand'
	WHEN utm_campaign = 'brand' THEN 'paid_brand'
	WHEN utm_source IS NULL AND http_referer IS NULL THEN 'direct type in'
END AS channel_group,
utm_source,
utm_campaign,
http_referer
FROM website_sessions
WHERE created_at < '2012-12-23'
) AS sessions_w_channel_group
GROUP BY 
	YEAR(created_at),
    MONTH(created_at);
```
![螢幕擷取畫面 2023-11-15 145624](https://hackmd.io/_uploads/B16RiyzN6.png)



<br/>


## 關於網站優化

#### 找著陸頁訪問數
之後也可以將 first_pageview 表與原始的表自連接，透過 website_pageview_id = min_pv_id + 1 的條件，來找出每個會話中緊跟在第一個頁面之後的第二個訪問頁面
```=
CREATE TEMPORARY TABLE first_pageview;
SELECT
website_session_id,
MIN(website_pageview_id) AS min_pv_id
FROM website_pageviews
WHERE website_pageview_id < 1000  
GROUP BY website_session_id;
```
![螢幕擷取畫面 2023-11-16 161045](https://hackmd.io/_uploads/HJscCH7ET.png)
最後一列可以解釋為，編號為 8 的會話中，用戶訪問的第一個頁面的 website_pageview_id 是 12

#### 每個頁面 URL 的總瀏覽量

```=
SELECT
	pageview_url,
	COUNT(DISTINCT website_pageview_id) AS pvs
FROM website_pageviews
WHERE created_at < '2012-06-09'
GROUP BY
	pageview_url
ORDER BY
	pvs DESC;
```
![螢幕擷取畫面 2023-11-16 160844](https://hackmd.io/_uploads/rJgK0B74a.png)

#### 每個著陸頁所吸引的會話數

```=
SELECT
	website_pageviews.pageview_url AS landing_page_url,
	COUNT(DISTINCT first_pv_per_session.website_session_id) AS sessions_hitting_page	 
FROM first_pv_per_session
	LEFT JOIN website_pageviews
		ON first_pv_per_session.first_pv = website_pageviews.website_pageview_id 
GROUP BY website_pageviews.pageview_url;
```
![螢幕擷取畫面 2023-11-16 165712](https://hackmd.io/_uploads/S1i_YLmNp.png)

#### 查看某個特定時間或事件點之後的流量表現

查看某一日期前，/home 最大(最後頁面)的id
```=
SELECT
	MAX(website_sessions.website_session_id) AS most_recent_gsearch_nonbrand_home_pageview FROM website_sessions
	LEFT JOIN website_pageviews
	ON website_pageviews.website_session_id = website_sessions.website_session_id
WHERE utm_source = 'gsearch'
AND utm_campaign = 'nonbrand'
AND pageview_url = '/home'
AND website_sessions.created_at < '2012-11-27';

# 得到17145，代入下面
```

再算出訪問量，可以查看兩個日期，算出區間訪問量
```=
SELECT
	COUNT(website_session_id) AS sessions_since_test
FROM website_sessions
WHERE created_at < '2012-11-27'
AND website_session_id > 17145  
AND utm_source = 'gsearch'
AND utm_campaign = 'nonbrand';
```
![螢幕擷取畫面 2023-11-16 213204](https://hackmd.io/_uploads/HJUk5c7Na.png)


<br/>


## 關於產品銷售

#### 查看訂單時間
```=
SELECT
	website_session_id,
	created_at,
	HOUR(created_at) As hr,
	WEEKDAY(created_at) As wkday, 
	CASE
	WHEN WEEKDAY(created_at) = 0 THEN 'Monday'
	WHEN WEEKDAY(created_at) = 1 THEN 'Tuesday'
        WHEN WEEKDAY(created_at) = 2 THEN 'Wednesday'
        WHEN WEEKDAY(created_at) = 3 THEN 'Thursday'
        WHEN WEEKDAY(created_at) = 4 THEN 'Friday'
        WHEN WEEKDAY(created_at) = 5 THEN 'Saturday'
        WHEN WEEKDAY(created_at) = 6 THEN 'Sunday'
		ELSE 'other_day'
	END AS clean_weekday,
	QUARTER(created_at) As qtr,
	MONTH(created_at) As mo,
	DATE(created_at) As date,
	WEEK(created_at) AS wk
FROM website_sessions
WHERE website_session_id BETWEEN 150000 AND 155000 ;
```
![螢幕擷取畫面 2023-11-14 200529](https://hackmd.io/_uploads/HkLjzk-E6.png)


#### 查看每週訂單
```=
SELECT
	YEAR(website_sessions.created_at) AS yr,
	WEEK(website_sessions.created_at) AS wk,
	MIN(DATE(website_sessions.created_at)) AS week_start,
	COUNT(DISTINCT website_sessions.website_session_id) AS sessions,
	COUNT(DISTINCT orders.order_id) AS orders
FROM website_sessions
	LEFT JOIN orders
		ON website_sessions.website_session_id = orders.website_session_id
WHERE website_sessions.created_at < '2013-01-01'
GROUP BY 1,2 
```

![螢幕擷取畫面 2023-11-15 131347](https://hackmd.io/_uploads/S1tTXRb4T.png)


#### 每天各點入站的數量
```=
SELECT
	DATE(created_at) AS created_date,
	WEEKDAY(created_at) AS wkday,
	HOUR(created_at) AS hr,
	COUNT(DISTINCT website_session_id) AS website_sessions
FROM website_sessions
WHERE created_at BETWEEN '2012-09-15' AND '2012-11-15'
GROUP BY 1,2,3 ;
```
![螢幕擷取畫面 2023-11-15 133313](https://hackmd.io/_uploads/HkdXdAb4a.png)

#### 每小時平均訪問數量、分天看
```=
SELECT
    hr,
    AVG(website_sessions) AS avg_sessions,
    AVG(CASE WHEN wkday = 0 THEN website_sessions ELSE NULL END) AS mon,
    AVG(CASE WHEN wkday = 1 THEN website_sessions ELSE NULL END) AS tue,
    AVG(CASE WHEN wkday = 2 THEN website_sessions ELSE NULL END) AS wed,
    AVG(CASE WHEN wkday = 3 THEN website_sessions ELSE NULL END) AS thur,
    AVG(CASE WHEN wkday = 4 THEN website_sessions ELSE NULL END) AS fri,
    AVG(CASE WHEN wkday = 5 THEN website_sessions ELSE NULL END) AS sat,
    AVG(CASE WHEN wkday = 6 THEN website_sessions ELSE NULL END) AS sun
FROM (
SELECT
	DATE(created_at) AS created_date,
	WEEKDAY(created_at) AS wkday,
	HOUR(created_at) AS hr,
	COUNT(DISTINCT website_session_id) AS website_sessions
FROM website_sessions
WHERE created_at BETWEEN '2012-09-15' AND '2012-11-15'
GROUP BY 1,2,3 
) AS daily_hourly_sessions
GROUP BY 1
ORDER BY 1;
```
![螢幕擷取畫面 2023-11-15 134256](https://hackmd.io/_uploads/ByxF9RW4a.png)



<br/>


## 關於產品組合

#### 查看產品訂單數、總銷售金額、總利潤、平均金額/張訂單

>PS MARGIN = price_usd-cogs_usd
>AOV = AVG(price_usd)

```=
SELECT
	primary_product_id,
	COUNT(order_id) AS orders,
	SUM(price_usd) AS revenue,
	SUM(price_usd - cogs_usd) AS margin,
	AVG(price_usd) AS aov
FROM orders
WHERE order_id BETWEEN 10000 AND 11000  
GROUP BY 1
ORDER BY 2 DESC;
```

分月看
```=
SELECT
	YEAR(created_at) AS yr,
	MONTH(created_at) As mo,
	COUNT(DISTINCT order_id) As number_of_sales,
	SUM(price_usd) AS total_revenue,
	SUM(price_usd - cogs_usd) As total_margin
FROM orders
WHERE created_at < '2013-01-04'  
GROUP BY
YEAR(created_at),
MONTH(created_at);
```
![螢幕擷取畫面 2023-11-15 151650](https://hackmd.io/_uploads/H1kdxxGNp.png)


#### 查看每月網站訂單、不重複id訪問數的轉換率、產品訂單
```=
SELECT
	YEAR(website_sessions.created_at) AS yr,
	MONTH(website_sessions.created_at) AS mo,
	COUNT(DISTINCT website_sessions.website_session_id) AS sessions,
	COUNT(DISTINCT orders.order_id) AS orders,
	COUNT(DISTINCT orders.order_id)
    /COUNT(DISTINCT website_sessions.website_session_id) AS conv_rate,
	SUM(price_usd)
    /COUNT(DISTINCT website_sessions.website_session_id) AS revenue_per_session,
	COUNT(DISTINCT CASE WHEN primary_product_id = 1 THEN order_id ELSE NULL END) AS product_one_orders,
	COUNT(DISTINCT CASE WHEN primary_product_id = 2 THEN order_id ELSE NULL END) AS product_two_orders
FROM website_sessions
	LEFT JOIN orders
	ON website_sessions.website_session_id = orders.website_session_id
WHERE website_sessions.created_at < '2013-04-01'  
AND website_sessions.created_at > '2012-04-01'  
GROUP BY 1,2 
```

#### 主要產品的銷售情況以及它們所帶動的交叉銷售 (cross-sell) 產品

訂單的基本識別資訊（訂單 ID、主要產品 ID 和訂單時間）
```=
CREATE TEMPORARY TABLE primary_products;
SELECT
	order_id,
	primary_product_id,
	created_at AS ordered_at
FROM orders
WHERE created_at < '2014-12-05';
```
![螢幕擷取畫面 2023-11-16 214311](https://hackmd.io/_uploads/rk6ip97ET.png)

找出每筆「主要產品訂單」所帶動的「非主要產品」的銷售情況
```=
SELECT
primary_products.*, 
order_items.product_id As cross_sell_product_id
FROM primary_products
	LEFT JOIN order_items 
		ON order_items.order_id = primary_products.order_id 
        AND order_items.is_primary_item = 0;
```
![螢幕擷取畫面 2023-11-16 224244](https://hackmd.io/_uploads/H1tc5jm4T.png)


找到與每個產品的組合比例
```=
SELECT
	primary_product_id,
	COUNT(DISTINCT order_id) As total_orders,
	COUNT(DISTINCT CASE WHEN cross_sell_product_id = 1 THEN order_id ELSE NULL END) AS _xsold_p1,
	COUNT(DISTINCT CASE WHEN cross_sell_product_id = 2 THEN order_id ELSE NULL END) AS _xsold_p2,
	COUNT(DISTINCT CASE WHEN cross_sell_product_id = 3 THEN order_id ELSE NULL END) AS _xsold_p3,
	COUNT(DISTINCT CASE WHEN cross_sell_product_id = 4 THEN order_id ELSE NULL END) AS _xsold_p4,
	
	COUNT(DISTINCT CASE WHEN cross_sell_product_id = 1 THEN order_id ELSE NULL END)/COUNT(DISTINCT order_id) AS p1_xsell_rt,
	COUNT(DISTINCT CASE WHEN cross_sell_product_id = 2 THEN order_id ELSE NULL END)/COUNT(DISTINCT order_id) AS p2_xsell_rt,
	COUNT(DISTINCT CASE WHEN cross_sell_product_id = 3 THEN order_id ELSE NULL END)/COUNT(DISTINCT order_id) AS p3_xsell_rt,
	COUNT(DISTINCT CASE WHEN cross_sell_product_id = 4 THEN order_id ELSE NULL END)/COUNT(DISTINCT order_id) AS p4_xsell_rt
FROM
(
SELECT
	primary_products.*,
	order_items.product_id As cross_sell_product_id
FROM primary_products
	LEFT JOIN order_items
		ON order_items.order_id = primary_products.order_id
		AND order_items.is_primary_item = 0
) AS primary_w_cross_sell
GROUP BY 1;
```
![螢幕擷取畫面 2023-11-16 224309](https://hackmd.io/_uploads/HJes9i7E6.png)


<br/>


## 關於產品退貨率

#### 分年、月，查看各產品退貨率

```=
SELECT
YEAR(order_items.created_at) AS yr,
MONTH(order_items.created_at) AS mo,

COUNT(DISTINCT CASE WHEN product_id = 1 THEN order_items.order_item_id ELSE NULL END) AS p1_orders,
COUNT(DISTINCT CASE WHEN product_id =1 THEN order_item_refunds.order_item_id ELSE NULL END)
/COUNT(DISTINCT CASE WHEN product_id = 1 THEN order_items.order_item_id ELSE NULL END) AS p1_refund_rt,

COUNT(DISTINCT CASE WHEN product_id = 2 THEN order_items.order_item_id ELSE NULL END) AS p2_orders,
COUNT(DISTINCT CASE WHEN product_id = 2 THEN order_item_refunds.order_item_id ELSE NULL END)
/COUNT(DISTINCT CASE WHEN product_id = 2 THEN order_items.order_item_id ELSE NULL END) AS p2_refund_rt,

COUNT(DISTINCT CASE WHEN product_id = 9 THEN order_items.order_Item_Id ELSE NULL END) AS p3_orders,
COUNT(DISTINCT CASE WHEN product_id = 3 THEN order_item_refunds.order_item_id ELSE NULL END)
/COUNT(DISTINCT CASE WHEN product_id = 3 THEN order_items.order_item_id ELSE NULL END) AS p3_refund_rt,

COUNT(DISTINCT CASE WHEN product_id = 4 THEN order_items.order_item_id ELSE NULL END) AS p4_orders,
COUNT(DISTINCT CASE WHEN product_id = 4 THEN order_item_refunds.order_item_id ELSE NULL END)
/COUNT(DISTINCT CASE WHEN product_id = 4 THEN order_items.order_item_Id ELSE NULL END) AS p4_refund_rt

FROM order_items
LEFT JOIN order_item_refunds
ON order_items.order_item_id = order_item_refunds.order_item_id
WHERE order_items.created_at < '2014-10-15'
GROUP BY 1,2;
```
![螢幕擷取畫面 2023-11-15 134256](https://hackmd.io/_uploads/BkNekSQV6.png)


<br/>

### 關於消費者

#### 是否為重複訪問

