Podcast 數據自修: 七日流量圖
大家好，這次依然以頻道主的角度切入，從「這邊」所提到的數據紀錄做出相關圖表，自製前綴服務在上線一季之後累積了足夠數據量可以提供多樣化的展示，我選擇針對最近在台灣相關 podcast 社群討論的圖表話題: 「EP上線七日內流量展示」實作之。
將大綱分成以下數個部分：
- 需求釐清
- 圖表選擇
- 結語
1. 需求釐清
七日流量圖是以最近各 EP 為出發點，展示各 EP 從「上線第一天」到「上線第七天」期間，每一天的「不重複下載數」，例如這張圖：
從這張圖可以得出很多重要的行銷用指標，然而依照不同性質的頻道(e.g., 更新頻率 / 更新時段 / 單集長短)，各大頻道主或多或少對於這樣的圖表會有想要優化的願望，以我自己而言，我是日更型頻道，會想要一次看到近期較多 EP 的分別表現，原先預設的 4-5 首歌對於我來說數量太少；對於其他人來說，或許會有不同的使用情境導致他們希望能看到留存超過 7 天以上的是數據。
訂定數據需求之後，隨即開始盤點之前利用自製前綴服務取得的資料中，是否包含了能夠表述的紀錄訊息：
參照上圖，單一造訪紀錄除了載明造訪時間之外，另外也記下了造訪者的來源國別、使用的播放器軟體、經由何處而來等等資訊。這些資訊可以延伸做出不同訴求的圖表，例如「播放器分析餅圖」或者「聽眾分布地圖」，但就不在本次的討論範圍中。
2. 圖表選擇
在各 EP 想保留較多天數資料的需求下，若採用橫向卷軸則對於我自己使用電腦滑鼠操作有點麻煩，因此改用縱向呈現，這樣只需要使用滑鼠滾輪就可以快速檢視。
橫軸方面，我將定義不同色塊為各 EP，越靠近左邊的色塊就是留存最多天的單集，越靠近右邊的色塊就是最新的單集，在這個定義下，最新一集的色塊就只會在上面數來第一條長條圖中出現(且一定在最右邊)、昨天一集的色塊就會在上面數來第一以及第二條長條圖中出現(且一定在最右邊的一個或兩個)，其他以此類推。
在這個範例網頁裡，原作者已提供圖表架構所需的程式碼，以及需要呈現的資料的格式，剩下的就是如何從自製前綴所保存的聽眾造訪資訊轉換成圖表所需格式，這邊附上相關的 SQL(需要視不同情況斟酌修改)：
select f.title, f.pub,
(DATEDIFF(LEFT(CONVERT_TZ(from_unixtime(round(a.timestamp / 1000)), ‘+00:00’, ‘-04:00’), 10), f.pub) + 1) as AT,
count(*) as CNT
from feed as f
left join access as a on f.episode = a.episode
where (
f.pub >= DATE_ADD(CURDATE(), INTERVAL -7 DAY) and f.pub <= DATE_ADD(CURDATE(), INTERVAL -0 DAY)
)
and (
a.timestamp >= UNIX_TIMESTAMP(CONVERT_TZ(CONCAT(DATE_ADD(CURDATE(), INTERVAL -7 DAY), ‘ 00:00:00’), ‘-04:00’, ‘+00:00’)) * 1000
and a.timestamp <= UNIX_TIMESTAMP(CONVERT_TZ(CONCAT(DATE_ADD(CURDATE(), INTERVAL -0 DAY), ‘ 23:59:59’), ‘-04:00’, ‘+00:00’)) * 1000
)
group by f.title, f.pub, (DATEDIFF(LEFT(CONVERT_TZ(from_unixtime(round(a.timestamp / 1000)), ‘+00:00’, ‘-04:00’), 10), f.pub) + 1)
order by f.pub asc, AT;
它所呈現出來的效果大致如下：
之後就將這些取得的資訊，填入範例圖表所要求的資料格式即可，需要注意的地方是盡量在色塊的選擇上使用高對比色，避免顏色相近無法在圖表中區分各 EP。
這個範例現在擺在這邊方便參考(已關閉)，我依照自己的需求將留存的單集從 5 集變成 10 集，也將留存時間從 7 天變成 10 天，這樣可以讓我檢查更大範圍的近期 EP 表現。
3. 結語
不同的圖表呈現方式能看出不同的洞見，這需要有經驗的行銷人才提出或訂定需求，且圖表指標對於頻道業務性質也會有很強的相依性，舉例「短期爆紅」單集偵測功能就對於「聽眾描述性資訊」有強相關，例如國別、性別、來源等等，方便行銷或媒體人員定位實際的受眾分布。
二維圖表能呈現的資訊有限，針對三個或以上維度的比較將難以顯示，未來的洞見圖表針對更加複雜的需求可能會加入時間軸，變成類似這樣的動畫：
謝謝收看。
相關資源
https://bl.ocks.org/Andrew-Reid/0aedd5f3fb8b099e3e10690bd38bd458