做完這個案例，從此再也不怕業務問題

​【業務問題】

下面的Excel記錄了某款電商産品在1月1日釋出，1個月後的新增及留存資料、商品銷售資料、商品詳情頁浏覽資料、及商品資訊表：

（文末有資料下載下傳）

現在業務部門需要你分析出：

(1)1月5日的DAU是多少？

(2)從留存的角度來看，品質最高的新增使用者來自哪一天？

(3)在1月15日當天，SKU銷售激活率是多少？

(4)商品“品類T582”的詳情頁購買轉化率在哪天最高？

(5)1月10日當天的ARPU值是多少？

【分析思路】

1. 1月5日的DAU是多少？

DAU是日活躍使用者數。定義名額日活躍使用者數=當日新增的使用者數+之前每日的留存老使用者數。

求1月5日的日活躍使用者數=1月5日當日新增使用者數+1月5日前每一日新增使用者在1月5日的留存使用者。

(1)1月5日當日新增使用者數

在“新增及留存”表格中，可直接得1月5日當日新增使用者數是6680。

(2)1月5日前每一日新增使用者在1月5日的留存使用者

也就是1月4日的1日留存數（1月4日的1日就是1月5日），1月3日的2日留存數（1月3日的2日就是1月5日），1月2日的3日留存數（1月2日的3日就是1月5日），1月1日的4日留存（1月1日的4日以後是1月5日），這些留存數可以在“新增及留存”表中可得到，如下圖。

是以，1月5日前每一日新增使用者在1月5日的留存使用者數= 1月4日的1日留存數（2966）+1月3日的2日留存數（2628）+1月2日的3日留存（2775）+1月1日的4日留存數（3432）=11801

是以，1月5日DAU=1月5日新增使用者數+1月5日的留存老使用者數=6680+11801=18481。

2.從留存的角度來看，品質最高的新增使用者來自哪一天？

如何定義品質高的新增使用者呢？

可以用留存率這個名額來比較，看哪天的留存率最高。

使用者在某天開始使用某産品，一段時間後仍繼續使用即被認為是留存使用者數，也就是有多少使用者留下來了。這部分留存下來的使用者占當時新增使用者的比例即是留存率。

在本案例中，我們可以用7日留存來定義新增使用者的品質。在計算過程中将每日的7日留存作為比較的基礎。

7日留存指的是一個使用者留存了7日，第1日新增的使用者，在第7日仍然活躍，那麼他就可算做是7日留存的使用者。

第7日留存率=(第一天新增的使用者中，在第7天還有登入的使用者數)/第一天新增總使用者數=6日留存數/當日新增數

在Excel中如何計算留存率呢？

以下圖為例，用6日留存資料（C3列）/當日新增（B3列）

在“新增及留存”表中計算出的第7日留存率如下：

對第7日留存率進行降序排序，結果如下：

可以看到，在這一個月内，根據第7日留存率來比較，1月9日的第7日留存率最高，達到52.35%，是以，該日的新增使用者的品質最高；其次是1月17日。

3.在1月15日當天，SKU銷售激活率是多少？

SKU=stock keeping unit(庫存量機關)，針對電商而言，一款商品每個品類就是一個SKU，用以區分單品，便于電商品牌識别商品。

SKU銷售激活率=當日有銷售記錄的品類數/SKU總數

(1)SKU總數

在本案例中，可将一個品類認為是一個SKU，是以，由“商品資訊表”可知，該款電商産品的SKU數量=108（也就是商品名這一列有多少行資料）

(2)當日有銷售記錄的品類數

“商品銷售情況”表，記錄的是每個商品品類在1月1日到1月31日的銷售數量，如果某品類當天銷售記錄為0，表示當天該品類沒有銷售記錄。

例如下面的“品類T441”在1月1日的銷量為0，說明在這一天該品類沒有銷售記錄。

我們要求1月15日當天有銷售記錄的品類數，就是求“商品銷售情況”表中，1月15日的有多少行資料是大于0的。

是以，可以用條件計數函數（countif）來對“1月15日”這一列銷售記錄進行計數。公式為：

=countif(商品銷售情況!$P$2:$P$109,">0")

傳回結果是90，也就是說1月15日當天有銷售記錄的品類數為90。

(3)SKU銷售激活率

是以，1月15日當天，該款電商産品的SKU銷售激活率=當日有銷售記錄的品類數/SKU總數=90/108=83.3%

4.商品“品類T582”的詳情頁購買轉化率在哪天最高？

在本案例中，定義詳情頁購買轉化率=當日售賣件數/當日頁面浏覽次數，用于衡量商品在當天的售賣情況。

(1)品類T582的每日售賣數

在這裡，我們要用查找函數(vlookup)來查找品類T582的每日售賣數，公式如下：

=vlookup("品類T582",商品銷售情況!$A$2:$AF$109,column (B1),0)

公式解讀：vlookup文法為

vlookup(要查找誰,在哪個範圍裡找,傳回第幾列,是否精确比對)

第一個參數：要查找誰，要查找品類T582；

第二個參數：在哪個範圍裡找，在“商品銷售情況”表的A2:AF109這個範圍裡找，也就是說，在“商品銷售情況”表裡1月1日到1月31日的所有商品銷售記錄裡找；

第三個參數：傳回第幾列，找到之後，傳回第幾列的記錄呢？要傳回1月1日，1月2日，1月3日…直到1月31日的銷售記錄，也就是說，要傳回第2列，第3列，第4列…第N列的記錄。是以，在這裡，我用列函數column函數來建構一個自然數序列。

column函數傳回給定單元格引用的列号。如公式=column（B1），傳回的是單元格B1所在的列号，即第2列，是以，傳回2；公式=column（B10），傳回的是單元格B10所在的列号，同樣也是傳回2；公式=column（C1）,傳回的是單元格C1所在的列号，即第3列，是以傳回3。

在此案例中，我就是用列函數column函數來建構一個自然數序列，當公式往右填充時，column (B1)就會自然地變成column (C1), column (D1),column (E1)…作為vlookup函數的第三個參數，為其提供自然數序列。

第四個參數：是否精确比對。0，代表精确比對，1，代表模糊比對。在本案例示範中，要精确比對，是以用0

(2)品類T582的每日頁面浏覽次數

同樣的原理，我們用vlookup函數來查找品類T582在1月1日到1月31日每日的浏覽次數，公式為：

=vlookup("品類T582",商品浏覽情況!$A$2:$AF$109,column(B1),0)

(3)每日詳情頁購買轉化率

如下圖我們已分别查找出品類T582在1月1日到1月31日的每日售賣數及每日浏覽數。是以，當日的詳情頁購買轉化率=當日售賣件數/當日頁面浏覽次數，結果如下：

可對詳情頁購買轉化率進行降序排序，得到轉化率最大值是1月29日的71.1%，也就是說，“品類T582”這個商品的詳情頁購買轉化率在1月29日這天最高。

5.計算1月9日當天的ARPU值

ARPU=當日銷售總額/當日DAU=銷售數量*單價/DAU

(1)當日銷售總額

銷售總額=銷售數量*單價。

銷售數量在“商品銷售情況”裡，單價在“商品資訊表”裡，涉及到多個Excel表，需要使用查找函數（vlookup），找到比對每個商品的單價，公式為

=vlookup(A2,商品資訊表!$A$2:$B$109,2,0)

然後，當日銷售總額=銷售數量*單價，求得每日銷售總額，如圖：

1月9日的DAU，根據第一個問題的方式可得：24053

那麼，1月9日的ARPU=當日銷售總額/當日DAU=銷售數量*單價/DAU=242900/24053=10.1元。

6.總結

(1)首先需要知道常用的名額有哪些，就像《業務名額》裡講過的，要從下面4個問題出發來明确名額的統計口徑。

業務含義：該名額在業務上表示什麼；

資料來源：從什麼地方收集的原始資料；

統計時間：統計資料的時間範圍是什麼。

名額定義：如果是占比、比例，得定義清楚是什麼除以什麼；如果是相加，得定義清楚是什麼加上什麼。

(2)用Excel來計算名額會涉及到常用的函數，要把資料分析常用的函數掌握才能高效完成職場中的工作。

（3）案例資料如何下載下傳？

推薦：常用的業務名額有哪些？