# Python專案實作 資料分析與爬蟲
###### tags: `Python/SQL商業資料分析` `資料分析` `Python` `專案` `網頁爬蟲`

[TOC]

# 網路爬蟲與資料分析專案I

## JSON 檔案操作基礎

`JSON` 是一種常用的輕量資料格式，常用於網路 API 資料傳遞。

範例 JSON 檔案：

{
     "name": "Jack",
     "address": 
     {
         "streetAddress": "21 2nd Street",
         "city": "New York",
         "state": "NY",
         "postalCode": "10021"
     },
     "phoneNumber": 
     [
         {
           "type": "home",
           "number": "212 555-1234"
         },
         {
           "type": "fax",
           "number": "646 555-4567"
         }
     ]
 }

## 寫入 JSON 檔案

使用 Python 寫入 JSON 檔案可以使用搭配檔案處理的機制和 `dump()` 方法實現：

# dict 透過寫入後轉成 JSON 檔案格式
data = {
     'name': 'Jack',
     'address': 
     {
         'streetAddress': '21 2nd Street',
         'city': 'New York',
         'state': 'NY',
         'postalCode': '10021'
     },
     'phoneNumber': 
     [
         {
           'type': 'home',
           'number': '212 555-1234'
         },
         {
           'type': 'fax',
           'number': '646 555-4567'
         }
     ]
 }

# 寫入 JSON 檔案
with open('demo.json', 'w') as f:
    json.dump(data, f)

若希望將 dict 轉換成 JSON 字串可使用 dumps()：

import json

data = {
    'name' : 'Jack',
    'age' : 26,
}

json_str = json.dumps(data)

print(json_str)

#執行結果：
{"name": "Jack", "age": 26}

## 讀取 JSON 檔案

若要將 JSON 檔案讀入可以使用 `load()`：

import json

with open('demo.json') as f:
    data = json.loads(f.read())

print(data)
print(data['name'])

## Python 定期執行程式 I

定期執行程式顧名思義就是在固定一段時間於背景自動執行的程式（例如：例如每小時定期更新資料）。可以使用定期程式來執行我網路爬蟲程式，就可以減少人為的介入。

**如何在 Python 定期執行程式？**

**Step1. 安裝 APScheduler 套件**

pip install apscheduler

**Step2. 建立專案資料夾**

- 於本機電腦檔案管理員新增一個資料夾
- 使用 VS Code 編輯器開啟該資料夾為工作空間：檔案 > 開啟工作空間/資料夾

**Step3. 在本機電腦端執行定期程式**

`apscheduler` 定期執行預設是 UTC+0 時區，若需要更改時區為台灣常用時區需設定是 `Asia/Taipei` 時區，則就會是 UTC+8 時區。

- `interval` 為間隔多久執行：可以設定 seconds、minutes、hours、days 等參數決定間隔。
- `cron` 為定期什麼時間執行：可以指定特定時間或週末、週間執行，例如：year、month、day、開始 start_date、結束 end_date 等參數。

於工作資料夾下建立一個 `clock.py` 檔案：

# 引用 BlockingScheduler 類別
from apscheduler.schedulers.blocking import BlockingScheduler

# 創建一個 Scheduler 物件實例
sched = BlockingScheduler({'apscheduler.timezone': 'Asia/Taipei'})

# decorator 設定 Scheduler 的類型和參數，例如 interval 間隔多久執行
@sched.scheduled_job('interval', seconds=1)
def timed_job():
    print('每 1 秒鐘執行一次程式工作區塊')

# decorator 設定 Scheduler 為 cron 固定每週週間 6pm
@sched.scheduled_job('cron', day_of_week='mon-fri', hour=18)
def scheduled_job():
    print('每週週間 6 PM. 執行此程式工作區塊')

# 開始執行
sched.start()

**Step4. 測試我們的定期程式**

cd 您程式檔案所在的資料路徑（例如 C:\Users\(username)\Desktop）
#執行程式：
python clock.py

## 政府公開資料網路 API 抓取專案

### 設定目標和觀察資料

**Step1. 設定目標和觀察資料**

首先，我們先設定我們的目標：定期每 1 小時抓取[自動雨量站-雨量觀測資料 API 資料](https://opendata.cwb.gov.tw/api/v1/rest/datastore/O-A0002-001?Authorization=rdec-key-123-45678-011121314)。


- [自動雨量站-雨量觀測資料資料相關說明](https://data.gov.tw/dataset/9177)
- [自動雨量站-雨量觀測資料資料欄位名稱說明](https://opendata.cwb.gov.tw/opendatadoc/DIV2/A0002-001.pdf)

在抓取資料前可以先將 JSON 資料內容複製到 [jsonformatter](https://jsonformatter.curiousconcept.com/) 點選 Process 按鈕，讓 JSON 格式可以看的更清楚。

可以觀察發現資料格式主要為外層一個 {} 物件內含 `success`、`result` 和 `records` 的 key。其中我們想要抓取的資料主要是在 records 下的 location key 的陣列值中。

希望抓取時間地點資訊和每小時降雨量：`locationName`、`obsTime`、`RAIN（60分鐘累積雨量，單位 毫米）`。

> 注意若值為 -998.00 表示 `RAIN=MIN_10=HOUR_3= HOUR_6= 0.00`。

接著先建立一個專案資料夾，並新增一個空的 `app.py` 檔案。

### 抓取 API 資料

使用 `requests` 套件發出網路請求到 API 取得回傳的 JSON 資料，並解析內容整理成我們想要的格式。

import requests

headers = {
    'user-agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/84.0.4147.105 Safari/537.36'
}

API_URL = 'https://opendata.cwb.gov.tw/api/v1/rest/datastore/O-A0002-001?Authorization=rdec-key-123-45678-011121314'

# 發出網路請求
resp = requests.get(API_URL, headers=headers)

# 使用 json 方法可以將回傳值從 JSON 格式轉成 Python dict 字典格式方便存取
data = resp.json()
# 印出結果
print(data)

### 將資料存成檔案

將抓取的資料使用 `csv` 套件，將資料整理後存成 `csv` 檔案。注意若值為 -998.00 表示 `RAIN=MIN_10=HOUR_3= HOUR_6= 0.00`。

import requests
import csv

headers = {
    'user-agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/84.0.4147.105 Safari/537.36'
}

API_URL = 'https://opendata.cwb.gov.tw/api/v1/rest/datastore/O-A0002-001?Authorization=rdec-key-123-45678-011121314'

# 發出網路請求
resp = requests.get(API_URL, headers=headers)

# 使用 json 方法可以將回傳值從 JSON 格式轉成 Python dict 字典格式方便存取
data = resp.json()
location_records = data['records']['location']

# 宣告一個暫存列表 list
row_list = []

# 一一取出 r-ent 區塊
for location_record in location_records:
    # 從 JSON 檔案轉成 dict/list 中取值
    time = location_record['time']['obsTime']
    location_name = location_record['locationName']
    # 取得 RAIN 值（在 weatherElement 串列 index 1 的元素內）
    rain = location_record['weatherElement'][1]['elementValue']
    # 將資料整理成一個 dict
    data = {}
    data['time'] = time
    data['location_name'] = location_name
    data['rain'] = rain
    # 存入 row_list 方便之後寫入 csv 檔案使用
    row_list.append(data)

# 印出目前資料內容
print(data)

# CSV 檔案第一列標題記得要和 dict 的 key 相同，不然會出現錯誤
headers = ['time', 'location_name', 'rain']

# 使用檔案 with ... open 開啟 write (w) 寫入檔案模式，透過 csv 模組將資料寫入
with open('weather_rain.csv', 'w') as output_file:
    dict_writer = csv.DictWriter(output_file, headers)
    # 寫入標題
    dict_writer.writeheader()
    # 寫入值
    dict_writer.writerows(row_list)

執行結果（weather_rain.csv）：

time,location_name,rain
2020-08-27 09:10:00,福山,-998.00
2020-08-27 09:10:00,安平,-998.00
2020-08-27 09:10:00,龍安,-998.00
2020-08-27 09:10:00,嘉義,-998.00

### 修改成定期抓取資料

將執行抓取的程式和儲存成 csv 檔案的程式分別寫成函式，讓我們的定期程式可以呼叫使用，每 10 分鐘（minutes=10）執行一次（練習時可以修改成每 10 秒（seconds）一次加快除錯和開發速度）。

import requests
import csv
# 引用 BlockingScheduler 類別
from apscheduler.schedulers.blocking import BlockingScheduler

def get_api_data(API_URL):
    headers = {
        'user-agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/84.0.4147.105 Safari/537.36'
    }
    # 發出網路請求
    resp = requests.get(API_URL, headers=headers)

    # 使用 json 方法可以將回傳值從 JSON 格式轉成 Python dict 字典格式方便存取
    data = resp.json()
    return data

def get_parse_data(data):
    location_records = data['records']['location']

    # 宣告一個暫存列表 list
    row_list = []

    # 一一取出 r-ent 區塊
    for location_record in location_records:
        # 從 JSON 檔案轉成 dict/list 中取值
        time = location_record['time']['obsTime']
        location_name = location_record['locationName']
        # 取得 RAIN 值（在 weatherElement 串列 index 1 的元素內）
        rain = location_record['weatherElement'][1]['elementValue']
        # 將資料整理成一個 dict
        data = {}
        data['time'] = time
        data['location_name'] = location_name
        data['rain'] = rain
        # 存入 row_list 方便之後寫入 csv 檔案使用
        row_list.append(data)

    return row_list

def save_data_to_csv(row_list):
    # CSV 檔案第一列標題記得要和 dict 的 key 相同，不然會出現錯誤
    headers = ['time', 'location_name', 'rain']

    # 使用檔案 with ... open 開啟 write (w) 寫入檔案模式，透過 csv 模組將資料寫入
    with open('weather_rain.csv', 'w') as output_file:
        dict_writer = csv.DictWriter(output_file, headers)
        # 寫入標題
        dict_writer.writeheader()
        # 寫入值
        dict_writer.writerows(row_list)

# 創建一個 Scheduler 物件實例
sched = BlockingScheduler({'apscheduler.timezone': 'Asia/Taipei'})

# decorator 設定 Scheduler 的類型和參數，例如 interval 間隔多久執行
@sched.scheduled_job('interval', minutes=10)
def timed_job():
    print('每 10 分鐘執行一次程式工作區塊')
    API_URL = 'https://opendata.cwb.gov.tw/api/v1/rest/datastore/O-A0002-001?Authorization=rdec-key-123-45678-011121314'
    data = get_api_data(API_URL)
    row_list = get_parse_data(data)
    save_data_to_csv(row_list)

# 開始執行
sched.start()

# 網路爬蟲與資料分析專案II

## Scrapy 網路爬蟲框架基礎概論

若我們希望爬取的是**一整個網站內容甚至是同時建立多個網路爬蟲**的話，Python 提供了一個非常好用的套件：`Scrapy`。

> Scrapy 主要是使用在伺服器產生內容的網路爬蟲，若是前端程式產生的網頁內容，瀏覽器檢視原始碼無法看到 HTML 內容標籤需使用 selenium。所謂的框架可以想成是程式的骨架，讓我們可以使用內建定義好的函式方法、類別和結構等並專注在應用邏輯的撰寫上，讓程式寫起來更加有系統和結構

本文使用的是 `Scrapy 2.3.0` 版本，相關資訊可以參考 [官方技術文件](https://docs.scrapy.org/en/latest/) 和[官方網站](https://scrapy.org/)

pip install scrapy==2.3.0

## Scrapy 網路爬蟲框架架構


- Spiders 網路爬蟲程式發出網路請求到 Scrapy Engine 引擎
- Scrapy Engine 引擎接收後轉發到 Scheduler 調度器
- Scheduler 調度器接收後轉發到 Scrapy Engine 引擎
- 將網路請求送出抓取網路資料 Request
- 回傳網頁內容 Response
- 將回傳內容送到 Spiders 網路爬蟲程式
- 解析內容成定義的資料物件 Items 並繼續送出網路請求
- 將爬取的內容在 pipeline 進一步處理或加工
- 持續 3-9 步驟

## Scrapy 常用指令

安裝過 `Scrapy` 可以透過 `Anaconda`

- `scrapy shell`：啟動互動式對話指令介面
- `scrapy startproject`：建立 Scrapy 專案，後面接專案名稱
- `scrapy genspider`：在 Scrapy 專案中建立爬蟲，後面接爬蟲名稱和限制的網域 `allowed_domains`
- `scrapy crawl`：執行 Scrapy 專案的爬蟲（若加上 -o 後面接輸出檔案格式）

## 主要 Scrapy 專案檔案結構


- `items.py`：定義要抓取的爬蟲內容結構屬性於爬蟲中引用使用
- `settings.py`：Scrapy 專案設定檔案
- `pipelines.py`：如果需要處理 items 建立的物件資料，可以在這邊撰寫處理邏輯
- `spiders 目錄`：內容為主要網路爬蟲程式（主要負責發送網路請求和解析內容）

## Scrapy 網路爬蟲專案實作

### 環境設置和設定目標

以爬取整個 PTT 棒球版網頁內容為目標（先鎖定文章標題、作者、發文日期和文章內文網址），使用 `Scrapy` 網路爬蟲為框架進行實作介紹。

> 網路爬蟲五步驟：設定目標、觀察網頁、發出請求、解析內容 和 儲存資料。

**Step1. 環境設置和設定目標**

#若尚未安裝 Scrapy 套件，請先打開 Anaconda Prompt / 終端機安裝套件：
pip install scrapy==2.6.1

### 建立專案和爬蟲

**Step2. 建立專案和爬蟲**

1. 移動到想要建立專案的資料夾下，透過指令建立 Scrapy 專案（專案名稱為 ptt，可以自行定義）：

scrapy startproject ptt


2. 移動到 ptt 專案資料夾下：

cd ptt

3. 建立網路爬蟲
> （Scrapy 可以建立多個網路爬蟲，這邊簡化先建立單一 ptt_baseball 爬蟲，並限定爬蟲只能爬取 ptt.cc 的網域名稱，此網域會出現在allowed_domains 設定中）：

scrapy genspider ptt_baseball ptt.cc

4. 使用 VS Code 編輯器開啟 ptt 爬蟲資料夾專案

可以看到已經建立了一個專案資料夾（spiders 資料夾下有 ptt_baseball.py 爬蟲）：


### 設定 User Agent 和 robots.txt

在繼續撰寫爬蟲程式之前我們先設定專案資料夾下的 `settings.py。`這邊要設定 `USER_AGENT` 和 `ROBOTSTXT_OBEY`。

[robots.tx](https://zh.wikipedia.org/zh-tw/Robots.txt) 是網站主來規範搜尋引擎和爬蟲的檔案，若 ROBOTSTXT_OBEY 設定為 `False` 可以不遵守規範（**有些網站會阻擋**）。USER_AGENT 則是可以讓爬蟲更像人類的瀏覽器操作的版本資訊。

# Crawl responsibly by identifying yourself (and your website) on the user-agent
USER_AGENT = 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36'


# Obey robots.txt rules
ROBOTSTXT_OBEY = False

**若沒設定 ROBOTSTXT_OBEY = False 有可能會遇到被阻擋問題：**

### 網域名稱

網站是由一台台永不關機的電腦（伺服器）來提供服務，而 IP 位址（140.112.xxx.xxx）則是伺服器的位址，可以讓網路連線互相溝通的地址，可以想成是有電話號碼才能互相聯絡。

但通常要記憶一段 IP 數字很麻煩，網域名稱（domain）是方便記憶網站伺服器位址的名字（一般每個網站伺服器會有一個 IP 位置定位，由網域對應到該 IP），例如：wikipedia.org 網域名稱比 IP 位置 208.80.152.2 好記。zh.wikipedia.org 中 org 為一級網域名，wikipedia 為二級網域名稱，zh 為三級網域，合起來 zh.wikipedia.org 稱網域名稱。若 allowed_domains 指定 zh.wikipedia.org 代表只能爬取 zh.wikipedia.org 網域名稱下的內容，例如：zh.wikipedia.org/xxxx，en.wikipedia.org 下內容則視為不允許爬取。若 allowed_domains 指定 wikipedia.org 則 zh.wikipedia.org 和 en.wikipedia.org 皆可。

而 ptt.cc 則為 PTT 網站的網域，其中 cc 為一級網域名稱（原本是代表 印度洋科科斯群島 的意思，後來也通用給不同種類網站使用），ptt.com 的 .com （.com 通常代表公司行號等商業網站，是最著名的網域，但由於太多人使用，好用的名稱稀少，所以通常會使用替代方案例如 .cc 或 .net 或 .io，若服務台灣本地網站也會使用例如：.tw 代表台灣網域）。

## 定義網路爬蟲抓取項目

**Step3. 定義網路爬蟲抓取項目**

在建立好專案和爬蟲後，接著檢視網頁內容的部分。我們進入 [PTT 棒球板](https://www.ptt.cc/bbs/Baseball/index.html)的首頁 觀察網頁內容結構。

使用 Chrome 瀏覽器於畫面點選右鍵選單檢視元素：

- 文章標題：class 名稱為 title 元素下有一個超連結元素 a 其文字內容為標題
- 作者名稱：class 名稱為 author 元素的文字
- 日期：class 名稱為 date 元素的文字
- 文章網址：class 名稱為 title 元素下有一個超連結元素 a，屬性 href 為網址

接著我們打開 items.py 檔案 定義抓取的內容結構成一個類別 `PttPostItem`：

# Define here the models for your scraped items
#
# See documentation in:
# https://docs.scrapy.org/en/latest/topics/items.html
import scrapy
class PttPostItem(scrapy.Item):
    # define the fields for your item here like:
    title = scrapy.Field()
    author = scrapy.Field()
    date = scrapy.Field()
    url = scrapy.Field()

使用 `scrapy.Field()` 定義類別屬性內容，之後會將抓取的內容設定為物件屬性。

## 撰寫網路請求和內容解析程式

**Step4. 撰寫網路請求和內容解析程式**

接著到了主要撰寫網路請求和內容解析程式邏輯的部分 `ptt_baseball.py`。

在 Scrapy 中一個爬蟲是一個 `class` 類別，繼承於 (scrapy.Spider)，在這邊我們的爬蟲是：`PttBaseballSpider`。

- `name`：為爬蟲名稱
- `allowed_domains`：為爬蟲允許的網域（讓爬蟲不會爬取到不該爬的網域名稱）
- `start_urls`：定義爬蟲開始的網路請求網址

class PttBaseballSpider(scrapy.Spider):
    name = 'ptt_baseball'
    allowed_domains = ['ptt.cc']
    start_urls = ['https://www.ptt.cc/bbs/Baseball/index.html']

在 Scrapy 中解析內容的常用工具介紹：

- `get()`：取出一個元素
- `getall()`：取出多個元素內容
- `::text`：搭配 CSS 選擇器可以取得選取元素的文字內容
- `::attr(屬性)`：搭配 CSS 選擇器可以取得選取元素的指定屬性內容值

    def parse(self, response):
        # 取出 class 名稱為 .r-ent' 的元素
        posts = response.css('.r-ent')
        # 透過迴圈將內容取出
        for post in posts:
            # 進一步解析內容，使用 ::text 取出文字，使用 .get() 方法取值
            title = post.css('.title a::text').get()
            author = post.css('.author::text').get()
            date = post.css('.date::text').get()
            # 進一步解析內容，使用 ::attr 取出 href 屬性值，使用 .get() 方法取值
            url = post.css('.title a::attr(href)').get()
            print(title, author, date, url)

完整程式碼：

import scrapy


class PttBaseballSpider(scrapy.Spider):
    name = 'ptt_baseball'
    allowed_domains = ['ptt.cc']
    start_urls = ['https://www.ptt.cc/bbs/Baseball/index.html']

    def parse(self, response):
        posts = response.css('.r-ent')
        for post in posts:
            title = post.css('.title a::text').get()
            author = post.css('.author::text').get()
            date = post.css('.date::text').get()
            url = post.css('.title a::attr(href)').get()
            print(title, author, date, url)

## 輸出爬蟲

**Step5. 輸出爬蟲**

修改一下 `ptt_baseball.py` 引入 `PttPostItem` 並建立物件，將抓取的屬性給定給該物件對應屬性值。

import scrapy
# 引入定義好的 PttPostItem 類別
from ptt.items import PttPostItem

class PttBaseballSpider(scrapy.Spider):
    name = 'ptt_baseball'
    allowed_domains = ['ptt.cc']
    start_urls = ['https://www.ptt.cc/bbs/Baseball/index.html']

    def parse(self, response):
        posts = response.css('.r-ent')
        for post in posts:
            # 每篇文章都是一個 PttPostItem 物件
            item = PttPostItem()
            title = post.css('.title a::text').get()
            author = post.css('.author::text').get()
            date = post.css('.date::text').get()
            url = post.css('.title a::attr(href)').get()
            # 設定屬性值
            item['title'] = title
            item['author'] = author
            item['date'] = date
            item['url'] = url
            print(title, author, date, url)
            # 使用 yield 將函式轉換成 `generator` 產生器
            yield item

若函式有使用 `yield` 則會將函式轉換成 `generator` 產生器，關於 `yield` 的使用方式，可以[參考文章說明](https://https://liam.page/2017/06/30/understanding-yield-in-python/)

打開 Anaconda Prompt 終端機執行爬蟲並輸出：

#在專案資料夾下產生一個輸出檔案（demo.csv）：
scrapy crawl ptt_baseball -o demo.csv

#亦可輸出成 JSON 檔案：
scrapy crawl ptt_baseball -o demo.json

## 多頁網路爬蟲

**Step6. 多頁網路爬蟲**

其中 `response` 物件中有一個 `follow` 方法**可以傳入接續的網址和要處理的 callback 回呼函式**，在解析完成後繼續接續爬取下一個頁面（parse 為解析函式）。

response.follow(next_page,  self.parse)

可以**取出上頁的超連結網址**，當作每一次爬取的入口網址：class 名稱 `wide` 第二個元素的 `href` 屬性值。


修改程式 `ptt_baseball.py`：

import time
import scrapy
from ptt.items import PttPostItem


class PttBaseballSpider(scrapy.Spider):
    name = 'ptt_baseball'
    allowed_domains = ['ptt.cc']
    start_urls = ['https://www.ptt.cc/bbs/Baseball/index.html']

    def parse(self, response):
        # 取出上一頁網址，也可以自行定義網頁網址
        next_page = response.css('.wide::attr(href)').getall()[1]
        posts = response.css('.r-ent')
        for post in posts:
            item = PttPostItem()
            title = post.css('.title a::text').get()
            author = post.css('.author::text').get()
            date = post.css('.date::text').get()
            url = post.css('.title a::attr(href)').get()
            item['title'] = title
            item['author'] = author
            item['date'] = date
            item['url'] = url
            yield item

        print(next_page)
        # 讓爬蟲可以暫停休息幾秒繼續，避免太暴力爬取網站造成網站負擔
        time.sleep(3)
        # 讓爬蟲可以繼續發出網路請求爬取下一頁資料並解析內容
        yield response.follow(next_page,  self.parse)

打開 Anaconda Prompt 終端機執行爬蟲並輸出：

scrapy crawl ptt_baseball -o demo.csv

## 補充：迴圈條件判斷停止

class PttBaseballSpider(scrapy.Spider):
    name = 'ptt_baseball'
    allowed_domains = ['ptt.cc']
    start_urls = ['https://www.ptt.cc/bbs/Baseball/index.html']

    def parse(self, response):
        # 取出上一頁網址，也可以自行定義網頁網址
        next_page = response.css('.wide::attr(href)').getall()[1]
        posts = response.css('.r-ent')
        for post in posts:
            item = PttPostItem()
            title = post.css('.title a::text').get()
            author = post.css('.author::text').get()
            date = post.css('.date::text').get()
            url = post.css('.title a::attr(href)').get()
            if date == '指定日期:
                break
            item['title'] = title
            item['author'] = author
            item['date'] = date
            item['url'] = url
            yield item

        print(next_page)
        # 讓爬蟲可以暫停休息幾秒繼續，避免太暴力爬取網站造成網站負擔
        time.sleep(3)
        # 讓爬蟲可以繼續發出網路請求爬取下一頁資料並解析內容
        yield response.follow(next_page,  self.parse)

# 網站營運指標資料分析I

## 學習環境建置

### 名詞定義

- **Cookie**：於瀏覽器端紀錄的資料，除非時間到過期或主動清理，不然關掉瀏覽器仍然存在，若使用 Cookie 儲存 ID 資料則一般同一個 Cookie ID 可以視為同一個使用者（常用於數位廣告追蹤）
- **Session**：儲存於伺服器端的資料，若是關掉瀏覽器再次開啟則視為新的工作階段。若使用 Session 儲存 ID 資料，新的工作階段會產生新的 ID
- **Page View（PV）**：代表重複瀏覽次數（只要有瀏覽就計算一次）
- **Unique Page View**：代表不重複的瀏覽次數（同一個 Session 工作階段瀏覽同一個網頁只算一次）
- **Unique User（UU）**：代表不重複的訪問使用者（不同的 Cookie 代表不同瀏覽者）
- **CPC（Cost Per Click）**：為廣告每次被點擊的成本
- **CPM（Cost Per Thousand Impression）**：為每千次曝光成本，也就是廣告展示千次的成本
- **CVR（Conversion Rate）**：購買數量/透過廣告訪問流量（點擊廣告次數）

### 新增資料

新增一個資料庫 `demo_shop_logs`（若已有建立則延續使用），專門存放使用者瀏覽我們電子商務網頁的使用行為日誌相關資訊（可能是從 Google Analytics 或是伺服器 access log 整理出來的資料）：

同時我們也創建資料表 `user_access_logs` 存放使用者的使用瀏覽網站的日誌：

- `user_id`：使用者 id
- `cookie_id`：cookie id
- `session_id`：session id
- `url`：使用者瀏覽的網頁網址
- `referrer`：使用者上一個瀏覽的網頁，若無則為 NULL
- `create_at`：事件發生時間

CREATE table user_access_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id VARCHAR(30),
    cookie_id VARCHAR(30) NOT NULL,
    session_id VARCHAR(30) NOT NULL,
    url VARCHAR(1024) NOT NULL,
    referrer VARCHAR(1024),
    create_at DATETIME
);

INSERT INTO user_access_logs (user_id, cookie_id, session_id, url, referrer, create_at)
VALUES
(NULL, 'jUZtzREZIr', 'vJGboAiAzf', 'https://www.demo-shop.com/', NULL, '2020-06-27 17:12:35'),
(NULL, 'jUZtzREZIr', 'tMtlseIupQ', 'https://www.demo-shop.com/', NULL, '2020-06-27 17:12:35'),
(NULL, 'awKfkDtVBv', 'LhUaogKmPv', 'https://www.demo-shop.com/', NULL, '2020-06-27 17:12:35'),
(NULL, 'xBVauZzynI', 'EkzhrJVfvH', 'https://www.demo-shop.com/products/84', NULL, '2020-06-27 17:12:33'),
(NULL, 'xBVauZzynI', 'EkzhrJVfvH', 'https://www.demo-shop.com/', NULL, '2020-06-27 17:12:31'),
(NULL, 'xBVauZzynI', 'EkzhrJVfvH', 'https://www.demo-shop.com/products/57', NULL, '2020-06-27 17:12:32'),
(NULL, 'DuuGzbBbea', 'xBVauZzynI', 'https://www.demo-shop.com/products/12', NULL, '2020-06-27 17:12:32'),
(1, 'DuuGzbBbea', 'VIrAloEqEJ', 'https://www.demo-shop.com/products', NULL, '2020-06-27 17:12:31'),
(NULL, 'PsJiPRrAxV', 'KVbwvCnwVd', 'https://www.demo-shop.com/products/7234', NULL, '2020-06-27 17:12:45'),
(NULL, 'PsJiPRrAxV', 'KVbwvCnwVd', 'https://www.demo-shop.com/products/134', NULL, '2020-06-27 17:11:03'),
(NULL, 'PsJiPRrAxV', 'KVbwvCnwVd', 'https://www.demo-shop.com/products/234', NULL, '2020-06-27 17:10:21'),
(2, 'DuuGzbBbea', 'otWUtigRTm', 'https://www.demo-shop.com/products', NULL, '2020-06-26 18:21:01'),
(3, 'PsJiPRrAxV', 'IJVcqPcKAS', 'https://www.demo-shop.com/', NULL, '2020-06-26 17:42:01'),
(NULL, 'qSsZuTFGoN', 'cvAscDSDYt', 'https://www.demo-shop.com/search', NULL, '2020-06-26 17:11:01'),
(1, 'DuuGzbBbea', 'xmBbIfTnMH', 'https://www.demo-shop.com/', NULL, '2020-06-26 17:10:31'),
(NULL, 'DuuGzbBbea', 'mSAhxtmLBY', 'https://www.demo-shop.com/products/234', NULL, '2020-06-26 17:10:22'),
(2, 'qZaSfnTgrN', 'ItAOxLdTTq', 'https://www.demo-shop.com/products', NULL, '2020-06-26 17:10:09'),
(3, 'djGXozBuIX', 'gFlOngCxDd', 'https://www.demo-shop.com/', NULL, '2020-06-26 17:10:08'),
(NULL, 'djGXozBuIX', 'oGgmytSuLt', 'https://www.demo-shop.com/search', NULL, '2020-06-26 17:10:01');

## 彙總瀏覽次數分析

### Python SQL 彙總

在建立好學習環境資料後，透過 SQL 語法可以計算使用者瀏覽網站數據，進而了解使用者的瀏覽情形，以下主要計算：`Page View（PV）`、`Unique Page View` 和 `Unique User（UU）` 數值。

**Step1. Python SQL 彙總**

SELECT
    url,
    COUNT(DISTINCT cookie_id) AS unique_user,
    COUNT(DISTINCT session_id) AS unique_page_views,
    COUNT(*) AS page_views
FROM user_access_logs
GROUP BY url;


**Step2. 輸出成 CSV 檔案**

使用 `phpMyAdmin` 匯出結果成為 CSV 檔案，並改名為 `traffic_data.csv`。


### 資訊視覺化

**Step3. 資訊視覺化**

建立一個專案工作資料夾將 `traffic_data.csv` CSV 檔案放置到專案資料夾下並於資料夾下建立一個 `app.py` 程式將資料讀取進來，並轉換成長條圖來呈現。

import pandas as pd
import matplotlib.pyplot as plt


# 讀取 CSV 資料
df = pd.read_csv('traffic_data.csv')

# 以 index 為列相加（axis=0 為以索引列為基準，axis=1 為以欄為基準），skipna=True 忽略非數值
sum_df = df.sum(axis=0, skipna=True) 

# 取出加總數值資料
sum_df_data = sum_df[['unique_user', 'unique_page_views', 'page_views']]

# 產生 bar chart
sum_df_data.plot(kind='bar')
# 設定圖表標頭
plt.title('traffic performance')
# 顯示圖表
plt.show()

# 網站營運指標資料分析II

## 學習環境建置

本節聚焦在數位行銷流量來源成效分析（UTM Tag）。

### 名詞定義

**參考上一節「網站營運指標資料分析I」的名詞解釋。**

### 新增資料

新增一個資料庫 `demo_shop_logs`（若已有建立則延續使用），專門**存放使用者瀏覽我們電子商務網頁的使用行為日誌相關資訊**（可能是從 Google Analytics 或是伺服器 access log 整理出來的資料）：

創建資料表 `user_purchase_logs` 存放使用者的購買商品的日誌：

- `amount`：顧客消費金額
- `referrer`：使用者上一個瀏覽的網頁網址（代表從網頁來源是哪裡過來）
- `create_at`：資料創建時間

CREATE table user_purchase_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    amount INT NOT NULL,
    referrer VARCHAR(1024),
    create_at DATETIME
);

INSERT INTO user_purchase_logs (amount, referrer, create_at)
VALUES
(2340, 'https://www.demo-shop/?utm_source=newsletter-weekly&utm_medium=email&utm_campaign=spring-summer', '2020-09-27 17:11:01'),
(12340, 'https://www.demo-shop/?utm_source=newsletter-weekly&utm_medium=email&utm_campaign=spring-summer', '2020-08-27 17:11:01'),
(7040, 'https://www.google.com/?utm_source=google-search&utm_medium=CPC&utm_campaign=campaign-1', '2020-08-27 17:11:01'),
(640, 'https://www.google.com/?utm_source=google-search&utm_medium=CPC&utm_campaign=campaign-1', '2020-07-27 17:11:01'),
(5640, 'https://www.demo-shop.com/?utm_source=newsletter-weekly&utm_medium=email&utm_campaign=spring-summer', '2020-06-27 17:11:01'),
(6340, 'https://www.facebook.com/?utm_source=facebook-demo-fans-page&utm_medium=facebook&utm_campaign=daily-posts', '2020-04-27 17:11:01'),
(1640, 'https://www.demo-web/?utm_source=demo-website&utm_medium=banner&utm_campaign=campaign-2', '2020-04-27 17:11:01'),
(840, 'https://www.demo-web/?utm_source=demo-website&utm_medium=banner&utm_campaign=campaign-2', '2020-04-27 17:11:01'),
(1100, 'https://www.demo-shop.com/?utm_source=demo-website&utm_medium=banner&utm_campaign=campaign-2', '2020-04-27 17:11:01'),
(1830, 'https://www.demo-shop.com/?utm_source=demo-website&utm_medium=banner&utm_campaign=campaign-2', '2020-04-27 17:11:01'),
(330, 'https://www.demo-shop.com', '2020-04-27 17:11:01');

## 數位行銷流量成效分析基礎簡介

為了追蹤數位廣告成效或流量來源，我們常會使用 `Google Analytics` 流量分析工具搭配 UTM 標籤來追蹤流量來源的狀況。一般來說 UTM 標籤主要有以下五種（詳細資訊可以參考 [Google Analytics](https://analytics.google.com/analytics/web/provision/#/provision) 文件）和 [Campaign URL Builder](https://ga-dev-tools.web.app/campaign-url-builder/) 網站。

以上代表網址 `Query String` 為 key1 參數的值為 value1，key2 參數的值為 value2，key3 參數的值為 value3。

接著以下介紹主要的 UTM 標籤：

- **utm_source**：用來辨識為您的資源帶來流量的廣告客戶、網站來源等，例如 Google、newsletter4、billboard、ptt。
- **utm_medium**：代表行銷媒介的種類，例如單次點擊出價廣告（CPC）、橫幅廣告（banner）、電子佈告欄（bbs）、論壇（forum）或電子報（email/newsletter）。
- **utm_campaign**：產品的個別廣告活動名稱、廣告標語和促銷代碼等。
- **utm_term**：辨識付費搜尋關鍵字。如果您打算手動為付費關鍵字廣告活動添加代碼，建議您一併使用 utm_term 來指定關鍵字。
- **utm_content**：用來區分同一個廣告中的相似內容或連結。
- 舉例來說，要是同一封電子郵件中包含兩個行動號召文字連結，只要使用 utm_content 並為這兩個連結分別設定不同的值，您就能判斷哪個版本的行動號召文字效果較好。

> 範例 1：https://example.com?utm_source=news123&utm_medium=email&utm_campaign=spring-summer
>
> 以上代表的是透過 news123（utm_source） 這個 Email 電子報媒介 email（utm_medium） 的 spring-summer（utm_campaign） 行銷活動所帶來的流量。
>
> 範例 2：https://example.com?utm_source=Google&utm_medium=CPC&utm_campaign=click-ads-2020-summer
>
> 以上代表的是透過 Google（utm_source） 這個 搜尋引擎關鍵字廣告媒介 CPC（utm_medium） 的 click-ads-2020-summer（utm_campaign） 行銷活動所帶來的流量。

## 專案實作 I

**Step1. 目標設定**

目標希望了解數位行銷流量來源主要是來自哪一個網站、媒介和行銷活動，進而了解哪一個媒介管道是比較好的行銷通路，進而提升網站流量。

**Step2. Python SQL 彙總**
使用 SQL 取出 `referrer` 網址，代表使用者是從此網址進入到目標頁面（例如：商品下單頁面、活動頁面、表單註冊頁面等）。

使用 PySQL 讀取資料：

# 引用 pymsql 套件
import pymysql

# 建立和資料庫連線，參數為資料庫系統位址(localhost 為本機電腦別名), 帳號(預設為 root，實務上不建議直接使用), 密碼(預設為空), 資料庫名稱
# charset 為使用編碼，cursorclass 則使用 dict 取代 tuple 當作回傳資料格式
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='',
                             # 資料庫預設為 3306 若自己有更改不同 port 請依照需求更改
                             port=3306,
                             db='demo_shop_logs',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
# 使用 try...finally 錯誤處理，可以讓程式即便錯誤最後會關閉資料庫連線避免浪費資源
try:
    # 使用　with...as 可以讓我們程式正確執行下自動關閉資料庫連線
    with connection.cursor() as cursor:
        # 執行 SQL 敘述查詢資料
        sql = 'SELECT * FROM user_purchase_logs'
        cursor.execute(sql)
        # 取出所有結果
        items = cursor.fetchall()
finally:
    # 即便程式錯誤也會執行到這行關閉資料庫連線
    connection.close()

## 專案實作 II

import pandas as pd
import matplotlib.pyplot as plt
# 引入 Python 內建網址解析套件
from urllib.parse import urlsplit, parse_qs
# 引用 pymsql 套件
import pymysql

# 建立和資料庫連線，參數為資料庫系統位址(localhost 為本機電腦別名), 帳號(預設為 root，實務上不建議直接使用), 密碼(預設為空), 資料庫名稱
# charset 為使用編碼，cursorclass 則使用 dict 取代 tuple 當作回傳資料格式
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='',
                             # 資料庫預設為 3306 若自己有更改不同 port 請依照需求更改
                             port=3306,
                             db='demo_shop_logs',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
# 使用 try...finally 錯誤處理，可以讓程式即便錯誤最後會關閉資料庫連線避免浪費資源
try:
    # 使用　with...as 可以讓我們程式正確執行下自動關閉資料庫連線
    with connection.cursor() as cursor:
        # 執行 SQL 敘述查詢資料
        sql = 'SELECT * FROM user_purchase_logs'
        cursor.execute(sql)
        # 取出所有結果
        items = cursor.fetchall()
finally:
    # 即便程式錯誤也會執行到這行關閉資料庫連線
    connection.close()

# 建立 dict 儲存 UTM 統計資料
utm_stats = {
    'utm_source': {},
    'utm_medium': {},
    'utm_campaign': {}
}

# 將 SQL 查詢資料一一取出
for item in items:
    # 取出 referrer 欄位資料
    referrer = item['referrer']
    # 使用 urlsplit 將 referrer 網址分解成網址物件，取出屬性 query
    query_str = urlsplit(referrer).query
    # 將網址後面所接的參數轉為 dict：{}
    query_dict = parse_qs(query_str)

    # 將 query string 一一取出 {'utm_source': ['newsletter-weekly'], 'utm_medium': ['email'], 'utm_campaign': ['spring-summer']}
    for query_key, query_value in query_dict.items():
        # 取第 0 個 index 取出內容值
        utm_value = query_value[0]
        # 若參數值曾經出現過在 dict 的 key 中則累加 1
        if utm_value in utm_stats[query_key]:
            utm_stats[query_key][utm_value] += 1
        # 否則初始化成 1
        else:
            utm_stats[query_key][utm_value] = 1

# utm_stats: {'utm_source': {'newsletter-weekly': 3, 'google-search': 2, 'facebook-demo-fans-page': 1, 'demo-website': 4}, 'utm_medium': {'email': 3, 'CPC': 2, 'facebook': 1, 'banner': 4}, 'utm_campaign': {'spring-summer': 3, 'campaign-1': 2, 'daily-posts': 1, 'campaign-2': 4}}

# 將 utm_source 資料轉為 pandas Series
df_utm_source = pd.Series(utm_stats['utm_source'])

# 將 utm_medium 資料轉為 pandas Series
df_utm_medium = pd.Series(utm_stats['utm_medium'])

# 將 utm_campaign 資料轉為 pandas Series
df_utm_campaign = pd.Series(utm_stats['utm_campaign'])

# 使用 matplotlib 建立圖表
plt.title('UTM Stats')
# 建立單一圖表
df_utm_source.plot(kind='bar')

單一圖表呈現：

## 專案完整程式碼

import pandas as pd
import matplotlib.pyplot as plt
# 引入 Python 內建網址解析套件
from urllib.parse import urlsplit, parse_qs
# 引用 pymsql 套件
import pymysql

# 建立和資料庫連線，參數為資料庫系統位址(localhost 為本機電腦別名), 帳號(預設為 root，實務上不建議直接使用), 密碼(預設為空), 資料庫名稱
# charset 為使用編碼，cursorclass 則使用 dict 取代 tuple 當作回傳資料格式
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='',
                             # 資料庫預設為 3306 若自己有更改不同 port 請依照需求更改
                             port=3306,
                             db='demo_shop_logs',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
# 使用 try...finally 錯誤處理，可以讓程式即便錯誤最後會關閉資料庫連線避免浪費資源
try:
    # 使用　with...as 可以讓我們程式正確執行下自動關閉資料庫連線
    with connection.cursor() as cursor:
        # 執行 SQL 敘述查詢資料
        sql = 'SELECT * FROM user_purchase_logs'
        cursor.execute(sql)
        # 取出所有結果
        items = cursor.fetchall()
finally:
    # 即便程式錯誤也會執行到這行關閉資料庫連線
    connection.close()

# 建立 dict 儲存 UTM 統計資料
utm_stats = {
    'utm_source': {},
    'utm_medium': {},
    'utm_campaign': {}
}

# 將 SQL 查詢資料一一取出
for item in items:
    # 取出 referrer 欄位資料
    referrer = item['referrer']
    # 使用 urlsplit 將 referrer 網址分解成網址物件，取出屬性 query
    query_str = urlsplit(referrer).query
    # 將網址後面所接的參數轉為 dict：{}
    query_dict = parse_qs(query_str)

    # 將 query string 一一取出 {'utm_source': ['newsletter-weekly'], 'utm_medium': ['email'], 'utm_campaign': ['spring-summer']}
    for query_key, query_value in query_dict.items():
        # 取第 0 個 index 取出內容值
        utm_value = query_value[0]
        # 若參數值曾經出現過在 dict 的 key 中則累加 1
        if utm_value in utm_stats[query_key]:
            utm_stats[query_key][utm_value] += 1
        # 否則初始化成 1
        else:
            utm_stats[query_key][utm_value] = 1

# utm_stats: {'utm_source': {'newsletter-weekly': 3, 'google-search': 2, 'facebook-demo-fans-page': 1, 'demo-website': 4}, 'utm_medium': {'email': 3, 'CPC': 2, 'facebook': 1, 'banner': 4}, 'utm_campaign': {'spring-summer': 3, 'campaign-1': 2, 'daily-posts': 1, 'campaign-2': 4}}

# 將 utm_source 資料轉為 pandas Series
df_utm_source = pd.Series(utm_stats['utm_source'])

# 將 utm_medium 資料轉為 pandas Series
df_utm_medium = pd.Series(utm_stats['utm_medium'])

# 將 utm_campaign 資料轉為 pandas Series
df_utm_campaign = pd.Series(utm_stats['utm_campaign'])

# 使用 matplotlib 建立圖表
plt.title('UTM Stats')
# 建立單一圖表
df_utm_source.plot(kind='bar')

# 若希望建立多個子圖表 subplots 於同一個畫面中，可以使用 subplots
# nrows 代表列，代表 ncols 行
fig, axes = plt.subplots(nrows=1, ncols=3)
# 建立子圖表 axes[0] 第一個
df_utm_source.plot(ax=axes[0], kind='bar')
# 建立子圖表 axes[0] 第二個
df_utm_medium.plot(ax=axes[1], kind='bar')
# 建立子圖表 axes[0] 第三個
df_utm_campaign.plot(ax=axes[2], kind='bar')
plt.show()


# 商品推薦與搜尋引擎優化 I

在電腦科學領域有幾個領域專門處理資料過多的問題：其中最知名的就是搜尋引擎和推薦系統。以電子商務網站為例，透過提供良好的搜尋引擎服務，可以讓消費者可以更容易找到想要的商品進而提升購買機率。而推薦系統則有機會透過客製化的推薦服務增加消費者的購買金額和數量

## 學習環境建置

新增一個資料庫 `demo_shop_logs`（若已有建立則延續使用），專門存放使用者瀏覽和操作我們電子商務網頁的使用行為日誌相關資訊（可能是從 Google Analytics 或是伺服器 access log 整理出來的資料）。

同時我們也創建資料表 `user_search_logs` 存放使用者的搜尋相關事件的日誌：

- `session_id`：使用者 session id
- `action`：使用者操作事件（`VIEW_PAGE` 代表觀看商品頁面事件、`SEARCH` 代表搜尋事件）
- `url`：使用者瀏覽網址
- `referrer`：使用者上一個瀏覽的網頁網址（代表從網頁來源是哪裡過來）
- `result_num`：搜尋結果數量
- `keyword`：搜尋關鍵字
- `created_at`：建立時間

CREATE table user_search_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    session_id VARCHAR(30) NOT NULL,
    action VARCHAR(30) NOT NULL,
    url VARCHAR(1024) NOT NULL,
    referrer VARCHAR(1024),
    keyword VARCHAR(255),
    result_num INT,
    created_at DATETIME
);

INSERT INTO user_search_logs (session_id, action, url, referrer, keyword, result_num, created_at)
VALUES
('HvDuNvWQkx', 'SEARCH', 'https://www.demo-shop/search?query=notebook', 'https://www.demo-shop/', 'notebook', 19380, '2020-09-27 17:11:01'),
('HvDuNvWQkx', 'VIEW_PAGE', 'https://www.demo-shop/products/2345', 'https://www.demo-shop/search?query=notebook', NULL, NULL, '2020-09-27 17:11:01'),
('ugWzQgrSHA', 'SEARCH', 'https://www.demo-shop/search?query=3C', NULL, '3C', 4321, '2020-09-27 17:11:01'),
('awKfkDtVBv', 'SEARCH', 'https://www.demo-shop/search?query=蘋果手機', NULL, '蘋果手機', 11323, '2020-09-27 17:11:01'),
('XnLvPnvVbq', 'SEARCH', 'https://www.demo-shop/search?query=枕頭', NULL, '枕頭', 34790, '2020-09-27 17:11:01'),
('XnLvPnvVbq', 'VIEW_PAGE', 'https://www.demo-shop/products/111', 'https://www.demo-shop/search?query=枕頭', NULL, NULL, '2020-09-27 17:11:01'),
('XnLvPnvVbq', 'SEARCH', 'https://www.demo-shop/search?query=記憶枕頭', 'https://www.demo-shop/products/111', '記憶枕頭', 9764, '2020-09-27 17:11:01'),
('XnLvPnvVbq', 'VIEW_PAGE', 'https://www.demo-shop/products/2311', 'https://www.demo-shop/search?query=記憶枕頭', NULL, NULL, '2020-09-27 17:11:01'),
('LqeVkoLqtq', 'SEARCH', 'https://www.demo-shop/search?query=無線耳機', NULL, '無線耳機', 23122, '2020-09-27 17:11:01'),
('LqeVkoLqtq', 'SEARCH', 'https://www.demo-shop/search?query=外掛耳機', 'https://www.demo-shop/search?query=無線耳機', '外掛耳機', 313,  '2020-09-27 17:11:01'),
('ihLdTonvlR', 'SEARCH', 'https://www.demo-shop/search?query=水冷風扇', NULL, '水冷風扇', 3441, '2020-09-27 17:11:01'),
('ihLdTonvlR', 'VIEW_PAGE', 'https://www.demo-shop/products/8989', 'https://www.demo-shop/search?query=水冷風扇', NULL, NULL, '2020-09-27 17:11:01'),
('DMedwlsSGr', 'SEARCH', 'https://www.demo-shop/search?query=吹風機', NULL, '吹風機', 9991, '2020-09-27 17:11:01'),
('YLAjvwLAea', 'SEARCH', 'https://www.demo-shop/search?query=電動牙刷 國際牌', NULL, '電動牙刷 國際牌', 3441, '2020-09-27 17:11:01'),
('MKhpnMoRga', 'SEARCH', 'https://www.demo-shop/search?query=垃圾桶', NULL, '垃圾桶', 3441, '2020-09-27 17:11:01'),
('WwkrinINtW', 'SEARCH', 'https://www.demo-shop/search?query=超級無敵酒瓶', NULL, '超級無敵酒瓶', 0, '2020-09-27 17:11:01'),
('nnDevbpucz', 'SEARCH', 'https://www.demo-shop/search?query=iphone17', NULL, 'iphone17', 0, '2020-09-27 17:11:01'),
('WJiOMKolyi', 'SEARCH', 'https://www.demo-shop/search?query=asus339手機套', NULL, 'asus339手機套', 0, '2020-09-27 17:11:01'),
('WJiOMKolyi', 'SEARCH', 'https://www.demo-shop/search?query=asus339手機', NULL, 'asus339手機', 0, '2020-09-27 17:11:01'),
('iXoVgWuuoW', 'SEARCH', 'https://www.demo-shop/search?query=asus339手機', NULL, 'asus339手機', 0, '2020-09-27 17:11:01'),
('xQazSzlSQQ', 'SEARCH', 'https://www.demo-shop/search?query=asus339手機', NULL, 'asus339手機', 0, '2020-09-27 17:11:01'),
('gNVjGDBMWZ', 'SEARCH', 'https://www.demo-shop/search?query=asus339手機', NULL, 'asus339手機', 0, '2020-09-27 17:11:01'),
('DMedwlsSGr', 'SEARCH', 'https://www.demo-shop/search?query=哈利波特10', NULL, '哈利波特10', 0, '2020-09-27 17:11:01'),
('loVHqkxvdU', 'SEARCH', 'https://www.demo-shop/search?query=哈利波特10', NULL, '哈利波特10', 0, '2020-09-27 17:11:01'),
('zrAypKskup', 'SEARCH', 'https://www.demo-shop/search?query=哈利波特10', NULL, '哈利波特10', 0, '2020-09-27 17:11:01'),
('eopeEUQgDe', 'SEARCH', 'https://www.demo-shop/search?query=花園別墅', NULL, '花園別墅', 0, '2020-09-27 17:11:01');

## 使用者搜尋行為分析

在開始進行搜尋關鍵字資料分析之前，先看一下整體資料的未搜尋到比例：

未搜尋到比例 = 使用者搜尋結果為零次數 / 搜尋總次數

SELECT
    COUNT(*) AS search_count,
    /* 若搜尋結果數量 result_num = 0 算一次 */
    SUM(CASE WHEN result_num = 0 THEN 1 ELSE 0 END) AS no_match_result_count,
    AVG(CASE WHEN result_num = 0 THEN 1.0 ELSE 0.0 END) AS no_match_rate
FROM
    user_search_logs
WHERE
    action = 'SEARCH';

## 未搜尋到關鍵字排行分析 I

**Step1. 目標設定**

希望了解使用者有哪些未搜尋到關鍵字，進而了解有哪些商品是使用者需要但尚未滿足的，可以適時導引使用者到類似或相關的商品頁面進而提升業績，採購部門也可以針對使用者需要的商品進行盤點和進貨。

**Step2. 彙總資料**

查詢語法：

SELECT
    keyword,
    result_num,
    COUNT(*) AS search_count
FROM user_search_logs
WHERE action = 'SEARCH' AND result_num = 0
GROUP BY keyword, result_num
ORDER BY search_count DESC;

使用 PyMySQL 讀取資料：

# 引用 pymsql 套件
import pymysql

# 建立和資料庫連線，參數為資料庫系統位址(localhost 為本機電腦別名), 帳號(預設為 root，實務上不建議直接使用), 密碼(預設為空), 資料庫名稱
# charset 為使用編碼，cursorclass 則使用 dict 取代 tuple 當作回傳資料格式
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='',
                             # 資料庫預設為 3306 若自己有更改不同 port 請依照需求更改
                             port=3306,
                             db='demo_shop_logs',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
# 使用 try...finally 錯誤處理，可以讓程式即便錯誤最後會關閉資料庫連線避免浪費資源
try:
    # 使用　with...as 可以讓我們程式正確執行下自動關閉資料庫連線
    with connection.cursor() as cursor:
        # 執行 SQL 敘述查詢資料，使用 """ raw string 符號將字串包起可以維持跨行
        sql = """
        SELECT
            keyword,
            result_num,
            COUNT(*) AS search_count
        FROM user_search_logs
        WHERE action = 'SEARCH' AND result_num = 0
        GROUP BY keyword, result_num
        ORDER BY search_count DESC;
        """
        cursor.execute(sql)
        # 取出所有結果
        items = cursor.fetchall()
finally:
    # 即便程式錯誤也會執行到這行關閉資料庫連線
    connection.close()

print(items)

**Step3. 資訊視覺化**
將資料取出後，計算每個搜尋結果為零的搜尋關鍵字各自的累計數據，最後使用圖表呈現。

import pandas as pd
import matplotlib.pyplot as plt
# 引用 pymsql 套件
import pymysql

# 建立和資料庫連線，參數為資料庫系統位址(localhost 為本機電腦別名), 帳號(預設為 root，實務上不建議直接使用), 密碼(預設為空), 資料庫名稱
# charset 為使用編碼，cursorclass 則使用 dict 取代 tuple 當作回傳資料格式
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='',
                             # 資料庫預設為 3306 若自己有更改不同 port 請依照需求更改
                             port=3306,
                             db='demo_shop_logs',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
# 使用 try...finally 錯誤處理，可以讓程式即便錯誤最後會關閉資料庫連線避免浪費資源
try:
    # 使用　with...as 可以讓我們程式正確執行下自動關閉資料庫連線
    with connection.cursor() as cursor:
        # 執行 SQL 敘述查詢資料，使用 """ raw string 符號將字串包起可以維持跨行
        sql = """
        SELECT
            keyword,
            result_num,
            COUNT(*) AS search_count
        FROM user_search_logs
        WHERE action = 'SEARCH' AND result_num = 0
        GROUP BY keyword, result_num
        ORDER BY search_count DESC;
        """
        cursor.execute(sql)
        # 取出所有結果
        items = cursor.fetchall()
finally:
    # 即便程式錯誤也會執行到這行關閉資料庫連線
    connection.close()

keyword_stats = {}

# 將 SQL 查詢資料一一取出
for item in items:
    # 取出欄位資料
    keyword = item['keyword']
    search_count = item['search_count']
    keyword_stats[keyword] = search_count

series_search_keyword = pd.Series(keyword_stats)
# 使用 matplotlib 建立圖表
plt.title('Search No Match keyword')
# 建立單一圖表
series_search_keyword.plot(kind='bar')
plt.show()


# 商品推薦與搜尋引擎優化 II

關聯式分析（Association rule） 又稱為購物籃分析，主要是**透過分析商品購物交易資料中購買的關聯性發掘有趣的關聯模式，進而制定行銷策略和商品推薦和擺放**，例如：將洋蔥和馬鈴薯以及漢堡肉放在同一區塊。本課程將介紹如何使用關聯式分析/購物籃分析中的 Apriori 演算法，找尋交易資料中的關聯規則。

## 關聯式分析/購物籃分析

關聯式分析是透過分析產品之間出現的規則來進行分析尋找潛在的關聯規則。

假設商店中有 A、B、C 和 D 四個商品，如 `item_set` 中表示。商店中有顧客交易資料如 `transaction_set` 中表示，每一個 list 中的 set 為一筆交易所含的商品，總共 5 筆交易。

# 所有商品的集合
item_set = ('A', 'B', 'C', 'D')

# 交易集合的串列，每一個集合代表一筆交易中所購買的商品。注意商品集合不會重複，即便這筆交易買了同一個商品多次也只算一次
transaction_set = [
    ('A', 'B'),
    ('A', 'B', 'C'),
    ('B', 'C', 'D'),
    ('A', 'C', 'D'),
    ('C')
]

通常在進行關聯分析時我們會先設定好**最小支持度**（Min Support）與**最小信賴度**（Min Confidence），這部份會依照該領域專家建議或是根據持續的測試回饋觀察的結果來設定的條件值。

> 要特別注意若最小支持度（Min Support）與最小信賴度（Min Confidence）設定太高會造成符合規則的關聯很多無法判斷，而若設定太小，則有可能沒有任何符合條件的關聯規則。

一般情況下**會先把最小支持度與最小信賴度設高一點**，再慢慢測試調整到最佳的條件。

## 支持度（Support）

支持度表示為指定 `item-set` 在整個交易資料中出現的數量。

公式：

Support(X) = 數量(X) / 數量(所有交易)

# U 代表取聯集。支持度為 item-set 在整個交易資料中出現的數量
Support(X, Y) = 數量(X U Y) / 數量(所有交易)

# U 代表取聯集
Support(X, Y, Z) = 數量(X U Y U Z) / 數量(所有交易)

# 更多集合的狀況依此類推

範例：
Support(A 商品) = number(買 A 商品) / number(所有交易)

Support(A 商品, B 商品) = number(買 A 商品 U 買 B 商品) / number(所有交易)

Support(A 商品, B 商品, C 商品) = number(買 A 商品 U 買 B 商品 U 買 C 商品) / number(所有交易)

# 更多集合的狀況依此類推

**計算支持度（Support）：**

# 所有商品的集合
item_set = ('A', 'B', 'C', 'D')

# 交易集合的串列，每一個集合代表一筆交易中所購買的商品
transaction_set = [
    ('A', 'B'),
    ('A', 'B', 'C'),
    ('B', 'C', 'D'),
    ('A', 'C', 'D'),
    ('C')
]

# 共有 5 筆交易，分母為 5。 A, B 出現的交易有 2 筆：('A', 'B')、('A', 'B', 'C')
support_a_b = 2 / 5
# 共有 5 筆交易，分母為 5。 A, C 出現的交易有 2 筆：('A', 'B', 'C')、('A', 'C', 'D')
support_a_c = 2 / 5
support_a_d = 1 / 5
support_b_c = 2 / 5
support_b_d = 1 / 5
support_c_d = 2 / 5

print(support_a_b, support_a_c, support_a_d, support_b_c, support_b_d, support_c_d)

#執行結果：
0.4 0.4 0.2 0.4 0.2 0.4

# 信賴度（Confidence）

信賴度表示當事件 X 發生的情況下，同時會發生 Y 的可能機率。

公式：

# P(Y|X) 為條件機率，意思為當 X 發生 Y 發生的機率。公式為 P(X ∩ Y) / P(X)
Confidence(X → Y) = P(Y|X) = P(X ∩ Y) / P(X)

# 更多集合的狀況依此類推
範例：

Confidence(買 A 商品 → 買 B 商品) = P(買 B 商品 | 買 A 商品) = P(買 A 商品 ∩ 買 B 商品) / P(買 A 商品)

# 更多集合的狀況依此類推

**計算信賴度（Confidence）：**

# 所有商品的集合
item_set = ('A', 'B', 'C', 'D')

# 交易集合的串列，每一個集合代表一筆交易中所購買的商品
transaction_set = [
    ('A', 'B'),
    ('A', 'B', 'C'),
    ('B', 'C', 'D'),
    ('A', 'C', 'D'),
    ('C')
]

#  P(買 A 商品 ∩ 買 B 商品) / P(買 A 商品)，
# 分母為 A 於所有交易中出現的機率 3 / 5
# A 和 B 同時出現機率為 2 / 5 
confidence_a_b = (2 / 5) / (3 / 5)
confidence_a_c = (2 / 5) / (4 / 5)
confidence_a_d = (1 / 5) / (2 / 5)
confidence_b_c = (2 / 5) / (4 / 5)
confidence_b_d = (1 / 5) / (2 / 5)
confidence_c_d = (2 / 5) / (2 / 5)

print(confidence_a_b, confidence_a_c, confidence_a_d, confidence_b_c, confidence_b_d, confidence_c_d)

#執行結果：
0.6666666666666667 0.5 0.5 0.5 0.5 1.0

其中 `confidence_c_d` 最高。

## 使用 Apriori 關聯分析演算法

在關聯式分析中最經典的演算法為 Apriori 演算法。由於本身關聯式分析需要持續重覆計算不同種的可能，這邊可以使用 Python 第三方套件，減少自己撰寫的時間。

pip install efficient-apriori

# 引入套件
from efficient_apriori import apriori

# 所有商品的集合
item_set = ('A', 'B', 'C', 'D')

# 交易集合的串列，每一個集合代表一筆交易中所購買的商品
transaction_set = [
    ('A', 'B'),
    ('A', 'B', 'C'),
    ('B', 'C', 'D'),
    ('A', 'C', 'D'),
    ('C')
]

# 定義最小 support 為 0.4
# 定義最小 confidence 為 0.7
itemsets, rules = apriori(transaction_set, min_support=0.4, min_confidence=0.7)

print(rules)

#執行結果（符合的關聯規則）：
[{D} -> {C}]

根據假設的條件（最小 support 和 最小 confidence 可以根據領域專家或經驗調整），關聯規則分析可以推測 D 和 C 商品或許可以一起推薦販售或是擺放在相同區域。

# 延伸閱讀

- [SQL 資料庫](https://hackmd.io/fQbgqRSYTDi2Ouis0fE3rg?view)
- [Python 網頁爬蟲](https://hackmd.io/JOc4g8AjSZiokS6vZckFSw?view)
- [Python 資料分析](https://hackmd.io/C5I9OzXzQCe6wJuZhwRV3Q)
- [Python 基礎語法](https://hackmd.io/4FH3w4_pQP6_dsandd45LA)
- [Python 進階語法使用](https://hackmd.io/QYVYsxE8QyWNsnIhukt-2Q)
- [Python 資料科學與探索式資料分析](https://hackmd.io/qSceMWZWQcWsMIA9QiO1Nw?view)
- [營收與使用者行為資料分析專案](https://hackmd.io/i6kRZN8JQsq57uDrKeKoLQ)