電商分析5大應用的SQL代碼及數據結構

發布時間:2019-11-22 15:08:24   發布者: juan    文章來源: MarTechApe

想要實現數據分析的多樣化,非常重要的一步是先搭建出一個有效的數據結構,從而就可以在此基礎上執行不同類型的分析。這一點在電子商務分析中非常重要。

電子商務分析主要圍繞5塊不同的重點領域,分別是留存研究、用戶參與度研究、增長計算、定價研究和庫存管理。在對每一塊進行具體分析之前,如果能用SQL搭建好一個完善且可持續的數據結構框架,那在此基礎上進行分析就會事半功倍。

那么每一塊具體搭建的思路是怎樣的呢?本文將會一一進行介紹。

留存研究

用戶留存率是電商分析中經常會用到的一個分析方法。它可以幫助你了解哪些用戶還在繼續使用產品或服務,哪些用戶已經離開了。電商平臺試圖在這項數據中找到用戶流失的假設原因,這樣就可以根據假設,在實際情況中測試潛在的優化方法。

用戶留存率的核心在于同期群(cohort )這一概念,“同期群”通常定義為一組在指定時間內完成了某一次行為的用戶,比如所有2018年1月在平臺上發生了購買的客戶。對不同的同期群之間進行比較能夠幫助電商分析師判斷留存率的表現究竟如何,因而,同期群模型是數據結構中必須要考慮到的一部分。同期群模型以用戶層面的表為基礎,其中包含每個用戶的獲客日期,如下圖所示:

上述用戶獲客日期可以基于你要考慮的任何活動指標來確定,可以是何時注冊、第一次購買或第一次登錄網頁等等。像這樣的表格,可以從任何的每日活動統計表中根據新增用戶來增添數據,從而對最終這張統一的用戶表格進行補充。

下圖是如何具體利用SQL來完成上述所說的所有步驟:

可以看出,將事件先聚合在一起,會讓之后持續地使用dim_customer這張表進行計算時更加方便和直接。

上方的這張衰減直方圖顯示了自獲客以來給定時間內,隨著時間過去,處于活躍狀態的用戶百分比。

下圖顯示了如何用SQL語言搭建出能夠用來計算上圖衰減直方圖所需的數據集:

以獲客日期當天所有新用戶數量為計算分母,接下來只需將之后每一天活躍的用戶量表示為獲客日期當天所有用戶的一個比例,便能計算衰減直方圖。

下面的三角圖是之前衰減直方圖的演變,此時它加入了同期群的概念,可以進行縱向比較。

只需在前面顯示的SQL代碼中增加一行將“獲客日期”選出來的查詢語句即可搭建出三角圖所需的數據集。SQL代碼如下??:

除此之外,還可以計算重復購買率并做成如下的直方圖。

為了計算復購率,你需要計算出用戶第一次購買到第二次購買之間所間隔的天數。在這種情況下,仍然要保持同期群組的概念,與此同時還需要計算購買次數。

完成上述所有步驟的SQL代碼如下??:

首先要計算每個客戶從獲客日期以來總共的購買次數。然后第二步,計算到完成第二次購買所需的天數。

對于每個客戶,你需要計算其所有日常購買的次數總和。同時,由于每天的購買次數可能就會超過一次,所以在具體設計如何計算時,你必須添加一些邏輯來檢查之前那天的購買次數是否少于2次,以此來確定回購的日期究竟是哪一天。

假設你每天都要跑一遍上述代碼,并且其搭建出來的數據集結果是一張叫做每日同期群組活動(daily cohort activity)的表。你只需在活動日期上增加一行條件(AND ActivityDate = ‘<RUN_DATE>‘)作為篩選器,然后每天運行下圖的SQL查詢即可,同時該活動日期也會單獨在表格中顯示一列。

然后搭建一個每日匯總同期群活動數據集。

在此之后,可以有效計算復購率。

以上步驟和查詢提供了自獲客以來,N天以內發生回購的時間點和復購人數。一旦你獲得了這個數據集,剩下要做的工作就是,使其成為一個總和,并且將其標準化。

設置一些數據結構能夠幫助電商數據分析師更好地了解整個平臺的用戶留存行為。在所有設置中,最重要的設置是客戶級別表、客戶事件聚合表和客戶同期群集合表。有了這些設置,就可以很容易地檢索出計算不同的留存表所需要的信息,比如上面提到過的衰減曲線、三角圖到回購直方圖等。

用戶參與度研究

用戶參與度是指用戶在平臺上的點擊、瀏覽、互動以及購買等等行為。例如,對于一個電商平臺來說,一個有效的參與用戶可能是指每天登陸瀏覽商品或者時常進行購買。數據分析框架可以幫助電商平臺更好地理解客戶參與度,兩個最常見的框架就是RFM分析和活躍用戶曲線。

RFM是一種通過計算3個特定緯度 —— 最近購買(Recency)、總購買頻率(Frequency)和總購買金額(Monetary)來對客戶進行分群的方法。RFM在每個緯度中會為每一個客戶打分,其中得分越高,代表此用戶對于企業的價值越高。

下圖代碼顯示了如何用SQL語句獲得每一個緯度的數據,以最近購買(Recency)為例:

總購買頻率和總購買金額通常被視為跟蹤指標,比如,跟蹤測量過去十二個月的數據。

活躍用戶曲線是用于獲取客戶群在活躍方面趨勢信息的一種方法,它依賴于一個“用戶活躍天數”的指標。Facebook定義了一個“L30”指標來衡量用戶在指定月份內活躍的總天數。通過活躍用戶曲線的形狀和趨勢,你可以得知在某時間段內用戶的參與程度,并且能識別出整個用戶群中的忠實用戶。例如,如果你的產品存在高頻的忠實用戶,那么在30天曲線的尾巴部分,它會呈現上揚趨勢。

由于“L28”在計算上的優勢,有時會被用來代替L30指標。L28可計算為四個L7周指標的總和,L7本身可理解為7個L1日指標的總和。具體SQL代碼如下:

從中可看出,實際上用戶參與度研究只需在留存研究的數據結構基礎上稍作調整。

增長計算

增長計算是將你的客戶群分成不同的階段狀態,并通過這些狀態了解你的活躍客戶群的變化趨勢的一種方法。客戶可能處于這樣幾個階段:新客戶、被復活的客戶、留存下來的客戶、流失或潛在流失的客戶。

增長計算可以幫助你了解你平臺現在的狀態:是處于正在有效地留住或復活客戶以實現增長呢?還是因客戶流失而面臨著增長上的阻礙。

增長計算中,活躍用戶通常被定義為在過去X天內執行了指定操作的用戶。以下兩個等式定義了如何在整個生命周期內劃分增長計算:

活躍(t)=新(t)+召回(t)+留存(t)

留存(t)=活躍(t-1) - 流失(t)

實質上,增長計算會根據用戶今天和昨天的活躍度來為其分配狀態。通常會有兩張表格,一張是客戶每日的狀態,另一張則是用來存儲用戶過去所有依照增長計算得出來的狀態。

具體SQL代碼如下:

增長計算的狀態列表可以添加到已存在的任何日期/客戶表中,以提供具體執行時的決策參考。

定價研究

企業對于產品的最初定價,通常是從企業角度出發的成本再加上一定利潤。認識到消費者對于品牌認知和對于商品價格的敏感性,電商企業發現,如果能識別出顧客的支付意愿,就能夠制定出更有效的定價策略或者促銷折扣,從而提高銷售額。

定價研究的分析有很多,從價格折扣的感知(銷售價格/推薦銷售價格)變化研究,價格彈性,收入拆分等等。

對于定價研究,擁有快照表(snapshot table)或II型歷史表(type II history table)非常重要,包含網站上每個商品的定價歷史。

快照表包含在指定日期/小時內不同價格的活躍狀態。如果價格在該時間區間內發生了改變,則只會捕獲到其中一個值。盡管如此,使用快照表來研究定價行為是非常有效的。它允許運行簡單的查詢,并能查看價格是如何影響購買行為的。

根據所使用的源系統,可能很難理解價格何時發生變化,以及某些價格的變化歷史。在這些情況下,建議使用快照表,在該表中輸入的數據,將會以特定的重復間隔獲取。可以在這些類型的表上運行簡單查詢,以便了解價格何時發生變化。

如果想快速了解網站上折扣活動的活躍度級別,價格快照表可以使這些查詢變得更加容易計算。

另一種對定價研究有用的數據結構是II型表(又稱歷史表)。II型表包含在特定時間范圍內有效的定價信息。如下圖所示:

因為II型表包含的是一個范圍,所以它們可以進行精細的切換,并且不會出現任何問題。例如, 它們可以承載以毫秒為單位的開始時間,以便提供有關給定價格有效期的信息。除此之外,與價格快照相比,此數據結構主要的優勢在于:當價格變化發生在中低頻率時,表格范圍會明顯地減小。

II型歷史表對定價研究特別有用,根據每個定價期,你可以清楚地了解每天應該定價多少,得到此表的SQL代碼如下:

總體而言,快照表和歷史表都有助于理解不同的定價研究。擁有這兩種數據結構有助于提高分析定價變動的效率,但是也有必要考慮到在這些分析中,快照表所存在的局限性。

庫存管理

庫存管理是供應鏈管理的重要組成部分,主要的目的是計劃和控制庫存,維持企業經營活動。

電商中的庫存管理是為了保證前臺商品的正常售賣。運營一個網絡商店至少需要擁有兩個數據源,一個是產品代碼(SKU),另一個是可銷售庫存。出于分析目的,理想情況下獲取越多屬性越好。

以易腐損貨物為例,批次號和有效期限能幫助盤點需要報損的不良品,它們在系統中也必須有相應的狀態,標注其從可銷售庫存轉化為不可銷售庫存;而盤點貨物收據和處于不可銷售狀態的庫存便于我們理解有需要修理或棄置的單品數量,以便修好后再銷售或補貨。

這種數據結構的一個潛在用途是計算存貨的在庫天數。在庫天數可以用來衡量某件貨品是否庫存積壓或庫存不足。比如我們想利用這個數據結構,基于過去七天的銷售額計算在庫天數:

庫存變化是庫存管理中的另一個重要課題,它需要有自己的數據結構。由于庫存的管理和倉庫密不可分,而倉庫又和采購、銷售相關,所以采購、預售、銷售訂單、售后退貨、倉間調撥、貨品損壞等因素都會影響庫存變動。識別庫存變動的原因能幫助運營層面的決策。

庫存數據的變動需要有相關單據作為依據,例如銷售訂單、發貨單、采購入庫單等等。數據結構至少應該包含單品(SKU)信息、貨品狀態碼、數量和變動時間:

以上信息不僅能幫助我們根據變動事件相應地協調庫存,還可以得出一些關鍵指標,例如計算貨品損壞率和其他運營指標。

總結

留存、參與度和增長都是以用戶為中心方面重要的課題,因此需要建立類似的數據結構以便進行分析和報告,這些即為:

用戶表

用戶事件集合表

用戶同期群集合表

定價研究可以根據價格快照和價格歷史表兩種類型的數據結構的幫助來進行分析,它們基本上包含了相同的信息,但可以幫助你更容易地進行一些特定的查詢。

庫存管理也依賴于兩種數據結構:庫存表和庫存變動表。庫存表高度依賴于可以從源系統獲得的數據的屬性數量和層次,而庫存變化表可以解釋為多個事件的合并。

電子商務是一個巨大的領域,它能夠進行廣泛的分析,擁有如上面列出的那些數據結構將為企業發展提供重要價值。



推薦了解
百度推廣
百度推廣介紹

百度是國內最大的搜索引擎,也是全球最大的中文搜索引擎,占有國內搜索市場70%的市場份額,是國內搜索引擎的領跑者,SEM廣告流量巨大,可以根據用戶搜索的內容提供相應的廣告,營銷效果顯著。

QQ咨詢
在線咨詢
咨詢熱線
關注微信
TOP