想要实现数据分析的多样化，非常重要的一步是先搭建出一个有效的数据结构，从而就可以在此基础上执行不同类型的分析。这一点在电子商务分析中非常重要。电子商务分析主要围绕5块不同的重点领域，分别是留存研究、用户参与度研究、增长计算、定价研究和库存管理。在对每一块进行具体分析之前，如果能用SQL搭建好一个完善且可持续的数据结构框架，那在此基础上进行分析就会事半功倍。
那么每一块具体搭建的思路是怎样的呢？本文将会一一进行介绍。
用户留存率是电商分析中经常会用到的一个分析方法。它可以帮助你了解哪些用户还在继续使用产品或服务，哪些用户已经离开了。电商平台试图在这项数据中找到用户流失的假设原因，这样就可以根据假设，在实际情况中测试潜在的优化方法。
用户留存率的核心在于同期群（cohort ）这一概念，“同期群”通常定义为一组在指定时间内完成了某一次行为的用户，比如所有2018年1月在平台上发生了购买的客户。对不同的同期群之间进行比较能够帮助电商分析师判断留存率的表现究竟如何，因而，同期群模型是数据结构中必须要考虑到的一部分。同期群模型以用户层面的表为基础，其中包含每个用户的获客日期，如下图所示：
上述用户获客日期可以基于你要考虑的任何活动指标来确定，可以是何时注册、第一次购买或第一次登录网页等等。像这样的表格，可以从任何的每日活动统计表中根据新增用户来增添数据，从而对最终这张统一的用户表格进行补充。下图是如何具体利用SQL来完成上述所说的所有步骤：
可以看出，将事件先聚合在一起，会让之后持续地使用dim_customer这张表进行计算时更加方便和直接。
上方的这张衰减直方图显示了自获客以来给定时间内，随着时间过去，处于活跃状态的用户百分比。下图显示了如何用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天内执行了指定操作的用户。以下两个等式定义了如何在整个生命周期内划分增长计算：
实质上，增长计算会根据用户今天和昨天的活跃度来为其分配状态。通常会有两张表格，一张是客户每日的状态，另一张则是用来存储用户过去所有依照增长计算得出来的状态。增长计算的状态列表可以添加到已存在的任何日期/客户表中，以提供具体执行时的决策参考。
企业对于产品的最初定价，通常是从企业角度出发的成本再加上一定利润。认识到消费者对于品牌认知和对于商品价格的敏感性，电商企业发现，如果能识别出顾客的支付意愿，就能够制定出更有效的定价策略或者促销折扣，从而提高销售额。定价研究的分析有很多，从价格折扣的感知（销售价格/推荐销售价格）变化研究，价格弹性，收入拆分等等。对于定价研究，拥有快照表（snapshot table）或II型历史表（type II history table）非常重要，包含网站上每个商品的定价历史。快照表包含在指定日期/小时内不同价格的活跃状态。如果价格在该时间区间内发生了改变，则只会捕获到其中一个值。尽管如此，使用快照表来研究定价行为是非常有效的。它允许运行简单的查询，并能查看价格是如何影响购买行为的。
根据所使用的源系统，可能很难理解价格何时发生变化，以及某些价格的变化历史。在这些情况下，建议使用快照表，在该表中输入的数据，将会以特定的重复间隔获取。可以在这些类型的表上运行简单查询，以便了解价格何时发生变化。如果想快速了解网站上折扣活动的活跃度级别，价格快照表可以使这些查询变得更加容易计算。
另一种对定价研究有用的数据结构是II型表（又称历史表）。II型表包含在特定时间范围内有效的定价信息。如下图所示：
因为II型表包含的是一个范围，所以它们可以进行精细的切换，并且不会出现任何问题。例如, 它们可以承载以毫秒为单位的开始时间，以便提供有关给定价格有效期的信息。除此之外，与价格快照相比，此数据结构主要的优势在于：当价格变化发生在中低频率时，表格范围会明显地减小。II型历史表对定价研究特别有用，根据每个定价期，你可以清楚地了解每天应该定价多少，得到此表的SQL代码如下：
总体而言，快照表和历史表都有助于理解不同的定价研究。拥有这两种数据结构有助于提高分析定价变动的效率，但是也有必要考虑到在这些分析中，快照表所存在的局限性。库存管理
库存管理是供应链管理的重要组成部分，主要的目的是计划和控制库存，维持企业经营活动。电商中的库存管理是为了保证前台商品的正常售卖。运营一个网络商店至少需要拥有两个数据源，一个是产品代码（SKU），另一个是可销售库存。出于分析目的，理想情况下获取越多属性越好。以易腐损货物为例，批次号和有效期限能帮助盘点需要报损的不良品，它们在系统中也必须有相应的状态，标注其从可销售库存转化为不可销售库存；而盘点货物收据和处于不可销售状态的库存便于我们理解有需要修理或弃置的单品数量，以便修好后再销售或补货。
这种数据结构的一个潜在用途是计算存货的在库天数。在库天数可以用来衡量某件货品是否库存积压或库存不足。比如我们想利用这个数据结构，基于过去七天的销售额计算在库天数：
库存变化是库存管理中的另一个重要课题，它需要有自己的数据结构。由于库存的管理和仓库密不可分，而仓库又和采购、销售相关，所以采购、预售、销售订单、售后退货、仓间调拨、货品损坏等因素都会影响库存变动。识别库存变动的原因能帮助运营层面的决策。库存数据的变动需要有相关单据作为依据，例如销售订单、发货单、采购入库单等等。数据结构至少应该包含单品（SKU）信息、货品状态码、数量和变动时间：
以上信息不仅能帮助我们根据变动事件相应地协调库存，还可以得出一些关键指标，例如计算货品损坏率和其他运营指标。留存、参与度和增长都是以用户为中心方面重要的课题，因此需要建立类似的数据结构以便进行分析和报告，这些即为：定价研究可以根据价格快照和价格历史表两种类型的数据结构的帮助来进行分析，它们基本上包含了相同的信息，但可以帮助你更容易地进行一些特定的查询。库存管理也依赖于两种数据结构：库存表和库存变动表。库存表高度依赖于可以从源系统获得的数据的属性数量和层次，而库存变化表可以解释为多个事件的合并。电子商务是一个巨大的领域，它能够进行广泛的分析，拥有如上面列出的那些数据结构将为企业发展提供重要价值。关于电商领域的分析还涉及非常多方面的专业内容，如果你想具体学习关于电商分析的各项工作内容、商业模型、分析框架、指标衡量手段，以及如何准备电商分析岗位求职，就来报名这门由MarTechApe携手美国互联网零售电商公司Moda Operandi的高级数据分析经理，开设的《从0到1：手把手入门电商数据分析》课程，带你掌握电商数据分析的必备技能与行业知识！
第一讲 电商中的数据分析E-commerce Analytics
Data-Informed vs Data-Driven 我们应该数据启示还是数据驱动？
Product Surface Analysis 表层电商分析
Product Analytics Frameworks 电商分析框架
Business Model Flipbook 电商商业模型
Product Type 科技驱动的“产品”类型
Product Growth Stage 电商增长阶段
Case Study 案例分析
第二讲 电商数据分析平台与商业模式 E-commerce Foundation
UTM/ Traffic Tracking/ Web Analytics Tool UTM/流量追踪/网页分析工具
Clickstream Event Tracking Tool 数字用户行为分析/点击流事件追踪工具
Product Intelligence Tool 产品智能分析工具
BI Self-Service Platform 商务智能自助平台
Product Analytics Common Stack 产品分析栈
Typical Product Analytics Questions 电商数据分析回答的经典问题
E-commerce Mode 有哪些电商模式？
第三与第四讲 电商关注的关键指标 E-commerce KPI
Product Lifecycle 产品生命周期
KPI by User Acquisition Channel 用户获客渠道关键指标
KPI by Product Funnel Engagement 产品转化漏斗关键指标
KPI by Product Retention 产品留存的关键指标
Offline KPIs 电商关注的线下关键指标
KPI Best Practices 如何最好地设置关键指标？
The Discipline of One Metric That Matters “唯一重要指标”原则
Traditional E-commerce VS Subscription E-commerce 传统电商与订阅制电商在关键指标上的选择
第五讲 产品测试与高阶数据分析 Product Testing & Advanced Analytics
A/B Test: Best Practice & Painful Learnings A/B测试的最佳做法与教训
Cluster Analysis 电商中的聚类分析
Cohort analysis 电商中的群组分析
Retention 电商关注的挑战：用户留存
Customer Lifetime Value 如何提高电商平台的用户终身价值？
Case Study 案例研究
第六讲 Product Analytics Miscellaneous 电商分析——除了数据，你还需要知道
How To Work With PMs 如何与产品经理协作
How To Sizing Product Initiatives 如何规划产品创新？
How To Evaluate Financial Lift From Product & Planning 如何从产品与规划中评估财务提升？
Marketing Analysis within Product 在产品部门中的营销分析
Lower Hanging Fruit vs Roadmap 容易实现的目标与路线图
第七讲 开启你的电商数据分析事业！Start Your Career In E-commerce
How To Find A Job In E-commerce? 求职电商的注意事项
How To Crack The Interview of BI Analyst/Data Analyst/Product Analyst in E-commerce? 如何攻克电商BI分析师/数据分析师/产品分析师的面试？
Analytics Jobs In E-commerce: What You Should Expect? 电商分析类工作：你应该期待什么？
Sunny：美国奢侈品电商Moda Operandi高级数据分析经理（Senior Analytics Manager）。6年美国数据分析行业从业经历。
新课上线预售优惠
课程原价：¥339元人民币
预售优惠：¥239元人民币（双人团购价）11月1日～11月30日
12月1日后，恢复原价
团购步骤：
*若24小时内组团不成功，则系统会自动退款。因此，开团无风险。若需美元支付，请联系小助手：
坚持学习，保持职场竞争力，十一月燃学狂欢，MarTechApe期待你的到来！
前往“发现”-“看一看”浏览“朋友在看”