# **【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;


#### 查看轉換率

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;


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


<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 


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


如果想知道數量，要加上 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;


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;


週和週開始的日期

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


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

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

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;


### 每週流量表現
假設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)


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


分年、週，不同設備的表現
假設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);


#### 不同搜尋引擎轉換率、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;


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

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;


分週，查看不同搜尋裝置、不同搜尋引擎的轉換率，相對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);


分週，查看不同搜尋引擎訪問量

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


#### 是否為直接流量
搭配條件(類似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;


#### 品牌廣告、直接流量、自然流量站廣告多少比例

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


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

最後一列可以解釋為，編號為 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;


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

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;


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

查看某一日期前，/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';


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


#### 查看每週訂單

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 


#### 每天各點入站的數量

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 ;


#### 每小時平均訪問數量、分天看

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;


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


#### 查看每月網站訂單、不重複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';


找出每筆「主要產品訂單」所帶動的「非主要產品」的銷售情況

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;


找到與每個產品的組合比例

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;


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


<br/>

### 關於消費者

#### 是否為重複訪問