「案例分享」一步步拆解會員數據，看見數位行銷的潛力

本篇實作「試算表分析會員數據＋影響行銷決策」。

提醒1. 為了做到「以後只要把清理過後的資料貼上，表格及特定指標的數字即自動更新」，接下來也會提到試算表的實用函數，無論你是用Microsoft Excel, Google Sheets, 或Apple Numbers都適用，幾乎人人都能上手(此篇操作步驟以Google Sheets為例。)

提醒2. 下述會員分析的邏輯與指標，是根據刻薄女本身的實作(詞句經過化名、數字經過不影響比例的調整)，不可能吻合所有產業、所有情境的需求，而是提供概念上的參考。

最基本的「數據+行銷」應用，大概是會員資料進行分類，並針對不同的會員屬性，寄發不同內容的電子報：
• 第一類「曾經購買」：告知優惠活動之外，表達我們關心他上次購買時/後的體驗。
• 第二類「未曾購買、已填會員資料」：提供優惠及說明網站有趣的改變，邀請來逛。
• 第三類「未曾購買、未填會員資料」：提供優惠並提醒「嘿！先填寫會員資料，以後下單就更輕鬆迅速了！」。

這是促使這群「已進站(註冊)」的客戶一步一步的在轉換流程上推進，努力極大化漏斗(Marketing Funnel)的過濾量！

不過，有鑒於老闆總不肯簽下Google聯播網廣告的預算(根據其本人說法：要是可以啊，一個禮拜看不到成效就收掉。)(病入膏肓，以為自己是FMCG產業嗎？)(主管及主主管已多次說出：消費者需要被教育，這種很糟糕的話，然一方面也反映他們自覺產品很難懂…，所以說聯播網廣告是不可能一週有成效的)，我開始對會員數據進一步分析，最後肯定是要導出結論：數位廣告的必要。雷一下～最後數據是驗證此論點，繼續看下去吧！

前言：會員的來源，依照會員資料的[推薦人]欄位可分為業務與行銷團隊。而依客戶的選擇，[會員類型]欄位可分為企業客戶、以及個人客戶。這次的假設是，行銷團隊若具有帶進會員的潛力，則值得投遞數位廣告。至於何謂帶進會員的潛力？後續也將以不同面向分析說明。

以下開始依序處理資料(清理、分類)、產出數據(分析)、最後驗證論點(判讀) :

▐ 第一步. 清理

這案例的資料來源很簡單，是從後台下載來的會員報表(試算表的分頁1)。
(如果是自己架設的後台，務必審慎設計「報表」功能，不怕改後台程式、到每次下載後要清理老半天才欲哭無淚。)

需要的清理，是「排除」大量的測試及員工帳號。

作法為將這些需要排除的帳號，事先整理在試算表的另一張分頁(分頁2)，並利用分頁1、2都有的[會員編號]欄位，在會員報表(分頁1)內利用【函數】、比對出需排除帳號在茫茫原始資料中的所在位置。

在A2儲存格寫下公式：=IF(ISERROR(MATCH(B2,’排除帳號’!$A$2:$A$1000,0)),"",B2)

意思是說如果會員報表(分頁1)的[會員編號](B2)與排除帳號(分頁2)的任一[會員編號](‘排除帳號’!$A$2:$A$1000)吻合，那麼就在會員報表(分頁1)的A2，秀出其[會員編號](B2)，換句話說，若A2有值則表示其是要被排除的會員帳號。

記得要將A2儲存格下拉以填滿所有與會員資料相對應的A欄儲存格。

接著在會員報表(分頁1)的A欄使用【篩選器】，在「依值篩選」處取消勾選【空白】，此時畫面秀出的都是需要被排除的帳號(*驗算*是否與分頁2的排除帳號有相同數量、並快速瀏覽兩邊內容是否吻合），全選這些欄位並右鍵【刪除列】，再回到【篩選器】勾選【空白】，現在眼前這些就是珍貴的會員資料！

備註：別忘了後續新增的會員中也有可能是需要被排除的會員帳號(比方說新進員工)，因此持續維護「排除帳號」也是非常重要的。或者當員工帳號有實際購買紀錄，是否仍需將其排除？值得探討。

只要A欄已填上公式，往後只要從B欄開始貼上新的會員報表，就輕鬆的利用篩選器完成原理資料的清理。

▐ 第二步. 分類

延續文章開題案例提到的分類邏輯，為什麼呢？藉此了解業務或行銷團隊分別帶來會員的「會員潛在價值」。
(一方面是電子報、fb廣告等可以經由email進行的再行銷，也會需要這樣整理。)

為了要一次將會員分成「曾經購買」、「未曾購買、已填會員資料」、「未曾購買、未填會員資料」，利用【資料】→【排序範圍】以「多個排序依據欄位」的方式對會員進行分類，其依序為[購買次數]、[上次購買日期]、[中文姓名]、[上次登入日期]、以及[會員編號]欄位，記得要選取Z→A，才會是從大到小。

為什麼是以這些欄位為排序依據？

首先依[購買次數]排序後，可以想像整張試算表的上半部都是「曾經購買」的會員，他們的購買次數會從某個最大值排到1，並搭配依[上次購買時間]排序，表示購買次數的相同的會員是依[上次購買日期]做子集合的排序。如此一來分類出「曾經購買」的會員。

接著為了在[購買次數]是0的會員中，分類出「未曾購買、已填會員資料」的會員，利用[中文姓名]欄位，因為有中文名稱表示其已填寫會員資料，這時候整張試算表的中段，正是「未曾購買、已填會員資料」的會員，並再藉由[上次登入日期]，有邏輯的在子集合內依序排列出近期有可能轉換的會員。

最後，試算表的後段就會是屬於「未曾購買、未填會員資料」的會員，至於再借由[會員編號]排列，是為了看出他們的救回程度，愈近期註冊、愈有機會推進下一個階段(填寫會員資料)。( *會員編號，是依註冊次序所賦予的流水號。)

排序完成後，將會員資料依據分類，貼至不同的試算表分頁(依序為分頁3、4、5)。

▐ 第三步. 分析

表格「會員數」


以往要計算符合特定條件的人數，很多人會想到直接使用【篩選器】再計算篩選後的列數(全選後右下角會有計數)，但是當有2個以上條件時，【篩選器】就不敷使用，也就是說【篩選器】一次只能對一個欄位進行篩選。除此之外，若希望表格能隨著新的會員資料做自動更新，那麼總結最好的方式就是利用【函數】：

(A1)「曾經購買」的總會員數
▴公式：=COUNTIF(‘分頁3’!A$2:$A,"曾經購買")

函數COUNTIF的作用是計算符合條件的儲存格個數。而(A1)公式的意思是指計算分頁3的A2儲存格開始的欄位A中有文字「曾經購買」的儲存格個數。

這時你可能會想，會員資料不會有這樣的內容啊？此分類不是根據[購買次數]而判斷的嗎？沒錯，我事先在分頁3、4、5手動新增了A欄作為索引(index)之用，並在每一位會員對應到A欄的儲存格標上其會員分類，如此一來就有了穩 健的計數標準。

(A2)「曾經購買」的會員中，[推薦人](B欄)是行銷
▴公式：=COUNTIFS(‘分頁3′!A$2:$A,"曾經購買",’分頁3’!$B$2:$B,"行銷")

注意到了嗎？這裏有兩個條件-曾經購買而且來源是行銷，所以函數COUNTIF結尾加上S。

於是(A3)很好理解，把(A2)公式中的行銷改成業務即可。

接下來，(B1)、(C1)、和(C2)是一組的，而(B2)、(C3)、和(C4)則是前者的企業改為個人。

(B1)「曾經購買」的會員中，[會員類型](C欄)是企業客戶
▴公式：=COUNTIFS(‘分頁3′!A$2:$A,"曾經購買",’分頁3’!$C$2:$C,"企業客戶")

(C1)「曾經購買」的會員中，[會員類型](C欄)是企業客戶且[推薦人](B欄)是行銷
▴公式：=COUNTIFS(‘分頁3′!A$2:$A,"曾經購買",’分頁3′!$C$2:$C,"企業客戶",’分頁3’!$B$2:$B,"行銷")

這裡是三個條件，而函數也是很直覺的依寫下範圍1、條件1、範圍2、條件2、範圍3、條件3，當然若還有其他條件，就依樣畫葫蘆了。

(C2)「曾經購買」的會員中，[會員類型](C欄)是企業客戶且[推薦人](B欄)是業務
▴公式：=COUNTIFS(‘分頁3′!A$2:$A,"曾經購買",’分頁3′!$C$2:$C,"企業客戶",’分頁3’!$B$2:$B,"業務")

從(A1)到(C4)涵蓋了表格上「1. 曾經購買」那列數據的公式，其下方兩列只要把資料來源改成分頁4或5、參照的文字改成個別分類名稱即可，例如：

(D1)「未曾購買、已填會員資料」的會員中，[會員類型]是企業且[推薦人]是行銷
▴公式：=COUNTIFS(‘分頁4′!A$2:$A,"未曾購買、已填會員資料",’分頁4′!$C$2:$C,"企業客戶",’分頁4’!$B$2:$B,"行銷")

(D2)「未曾購買、未填會員資料」的會員中，[會員類型]是企業且[推薦人]是行銷
▴公式：=COUNTIFS(‘分頁5′!A$2:$A,"未曾購買、未填會員資料",’分頁5′!$C$2:$C,"企業客戶",’分頁4’!$B$2:$B,"行銷")

表格繼續看下去，會發現Y軸變成購買次數，至於級距如何設定？要端看實際購買次數、行業特性、以及是否數字背後有特殊意涵等。

這部分的公式，一樣使用函數COUNTIFS，然而條件變成數字比大小。
至於要從哪個分頁計算數據呢？想想分頁3、4、5的會員分類，只有分頁3「曾經購買」才會有0以上的購買次數吧！反之，「未曾購買、已填會員資料」和「未曾購買、未填會員資料」的會員都是購買次數0，所以可以先解決購買次數0那列，比方說(G1)的公式：＝儲存格(D1)+儲存格(D2)。

(E1)購買次數:31次以上的總會員數 (*[購買次數]為C欄。)
▴公式：=COUNTIF(‘分頁3’!$C$2:$C,">=31″)

(E2)購買次數:31次以上的會員中，[推薦人]是行銷
▴公式：=COUNTIFS(‘分頁3′!$C$2:$C,">=31″,’分頁3’!$B$2:$B,"行銷")


接下來，一口氣以企業客戶且來源為行銷為例，說明所有購買次數級距的公式：

(F1)購買次數:31次以上的會員
▴公式：=COUNTIFS(‘分頁3′!$C$2:$C,">=31″,’分頁3′!$C$2:$C,"企業客戶",’分頁3’!$B$2:$B,"行銷")

(F2)購買次數:11-30次的會員
▴公式：=COUNTIFS(‘分頁3′!$C$2:$C,">=11″,’分頁3′!$C$2:$C,"企業客戶",’分頁3’!$B$2:$B,"行銷")-COUNTIFS(‘分頁3′!$C$2:$C,">30″,’分頁3′!$C$2:$C,"企業客戶",’分頁3’!$B$2:$B,"行銷")

這裡的條件是「當數字落在某範圍內」，一般我們會直覺地想到這樣的數學式：(例如) 10>X>30，但試算表的公式中不會有未知數X啊！因此需要轉個彎，變成大於等於11次的會員數減去大於30次的會員數。

(F3)購買次數:6-10次的會員
▴公式：=COUNTIFS(‘分頁3′!$C$2:$C,">=10″,’分頁3′!$C$2:$C,"企業客戶",’分頁3’!$B$2:$B,"行銷")-COUNTIFS(‘分頁3′!$C$2:$C,">6″,’分頁3′!$C$2:$C,"企業客戶",’分頁3’!$B$2:$B,"行銷")

(F4)購買次數:2-5次的會員
▴公式：=COUNTIFS(‘分頁3′!$C$2:$C,">=2″,’分頁3′!$C$2:$C,"企業客戶",’分頁3’!$B$2:$B,"行銷")-COUNTIFS(‘分頁3′!$C$2:$C,">5″,’分頁3′!$C$2:$C,"企業客戶",’分頁3’!$B$2:$B,"行銷")

(F5)購買次數:1次的會員
▴公式：=COUNTIFS(‘分頁3′!$C$2:$C,"=1″,’分頁3′!$C$2:$C,"企業客戶",’分頁3’!$B$2:$B,"行銷")

最後左上角的(Z)是總會員數的總會員數，就是所有會員(清理後)有多少人。

也別忘了藉由各種總和，進行交叉驗證，以確保數據無誤。

一步步完成這樣基本的表格，是還不夠一眼看出有力的訊息，於是在這表格之上，還必須顯示「比例分配」、「隨時間的會員數、比例變化」。

表格「比例分配」

這裡的數據完全是奠基於表格「會員數」，比方說所有會員中，來源是行銷的會員數佔比，其公式是1022那儲存格除以(Z)；如果是企業客戶中，來源是行銷，則公式為值為383的儲存格除以值為794的儲存格，意思是企業客戶且來源是行銷的會員數除以屬於企業客戶的總會員數。

表格「隨時間的會員數、比例變化」


「時間是非常重要的維度。」

這表格的公式和表格「會員數」相似，不同的地方在於Y軸的條件，不再是依照會員分類或購買次數，而是[註冊日期]，所以公式中的資料來源變成清理後的分頁1、而範圍1與條件1改變成欄位D-[註冊日期]與日期範圍。

(H1)在1月註冊的會員總數
▴公式：=COUNTIFs(‘分頁1’!$D$2:$D,">=2018-01-01″)

(H2)在1月註冊的會員中，[會員類型]是企業且[推薦人]是行銷
▴公式：=COUNTIFS(‘分頁1′!$D$2:$D,">=2018-01-01″,’分頁1′!$C$2:$C,"企業客戶",’分頁1’!$B$2:$B,"行銷")

(H3)在12月註冊的會員總數
▴公式：=COUNTIFs(‘分頁1’!$D$2:$D,">=2017-12-01″)-COUNTIFs(‘分頁1’!$D$3:$D,">2017-12-31″)

更往前的月份，其公式依此類推。另外當然也可以計算出每月的比例變化。

第四步. 判讀

這些數據告訴我們什麼？

直接先從比例看起：總的來說行銷帶進六成的會員；細分來看，企業客戶是近乎1:1、個人客戶則是超過七成是歸功於行銷，所以說行銷多帶進的會員是落在個人客戶。比照現實情況，一般的確多半公司的分工會是業務著重大型客戶、而行銷利用數位工具接觸符合TA設定的社會大眾。這麼看來，行銷在招攬企業客戶上的能力不亞於業務、同時帶進的高個人客戶數也反映了行銷的潛力。

當然觀察隨著時間的比例變化，可以得到更多訊息。

根據依購買次數分類的會員數據，會發現業務帶來的會員，其購買力比較高，這一方面應證業務的特性，他們通常藉由面對面的直接銷售(Direct Sale)*，所成功促使客人註冊、成為會員之前，是經過一番對談，於是多少會過濾掉些沒有需求或興趣的人，同時這些註冊會員也相較更了解產品而更容易達成購買的轉換。然而數位行銷的套路就不同了，它是針對第一、三手資料以及演算法認為符合的TA，努力把他們圈進池子裡(促使註冊)，再進一步加上客製化行銷手法(例如文章開頭的電子報、或程序化購買)以促使購買。於是乎，行銷與業務在各購買次數級距的會員數差別不算天差地遠的情況下，行銷帶進的會員是有兼俱質量的。

*備註：直接銷售(Direct Sale)不等於直效行銷(Direct Marketing)。

看來是應證了我們的假設，這時若能再提出業界、第三方相關數據來佐證就更有力了！而簡報的最後一頁要自信的寫上「數位廣告是值得投資的！」。(很想立馬遞上廣告預算案的簽呈)

投資有風險這是當然的，畢竟Google聯播網廣告要做得還行、好、或非常好，其中牽涉的因素還多的呢！

◦

希望你們看完這篇文章，會對如何運用數據協助行銷決策更有感覺、更具信心。

◦

疑惑預算是否被簽過嗎？現階段是沒拒絕也沒簽名的泥淖狀態啊…接下來要再準備預計執行報告，同志仍需努力…