数据分析 | 用 SeaTable 分析在线订单数据

SeaTable 是一个支持多种数据类型的协同表格。它不仅方便用户记录大量的结构化的数据,也提供了多种数据处理、分析和可视化的功能。本文中,我们介绍如何综合的使用 SQL 插件、数据链接功能、函数计算的功能来从一份在线订单数据中分析各年的复购比例、用户留存比例。

通过这篇文章,我们可以看到,相对于 Excel, SeaTable 在数据分析方面有以下的优势

  • 支持 SQL 查询,可以完成相对复杂的数据查询
  • 支持数据的扩表关联,一些场景下,只有扩表关联才可以完成数据的分析 (比如订单表需要和用户表关联)
  • 能够处理 10 万行甚至百万行以上的记录

相对于传统的数据库,SeaTable 则更加的直观易用:

  • 可以通过界面直接把中间结果导出为新的表格,然后再进行分析
  • 可以直接建立公式列,对数据进行计算加工,然后再进一步分析

数据集介绍

如下图,这是一张软件产品的在线订单数据表,记录了所有订单的详细信息,如 ID、用户名、付费金额、创建时间(首次付费时间)等等。其中软件是以年付费的形式销售的,用户需要每年续费一次以便继续使用软件。Price 记录的是美元价格。

我们添加 SQL 插件,并且放置到工具栏后,就可以进行快速的数据查询了。

首先,我们想查询所有订单的数量,那么 SQL 语句如下:

select count(*) from Orders

count(*) 表示对所有条目数量的计数,结果为 1855,表示目前为止总共的订单数量有 1855 个。

接下来,我们想看一下,这些订单来自多少个不同的用户,那么语句如下:

select count(distinct User) from Orders limit 1000

同上,用 count 函数,distinct 表示去重,相同的值只保留一次。结果为 598,表示总共购买的用户有 598 人。

那么在这么多用户中,我们想看一下, 2020 年付费能力最强的前十个用户是哪些人,则可以写入如下语句:

select User, sum(Paid) from Orders where `Created At` > '2020-01-01' and `Created At` <= '2021-01-01' group by User order by sum(Paid) desc limit 10

我们将付费时间限定为 2020 年,对用户的付费金额求和,并且以付费金额的大小来降序显示十条记录。

用 ISOMonth 函数分析本月和上月数据

SeaTable 中日期一般用 ISO 国际标准格式来保存。它提供了 ISODate 和 ISOMonth 两个函数来帮助我们来按照日期对数据进行统计分析。

当我们想要查看本月的付费金额时,我们可以用时间函数 ISOMonth 来对月份进行过滤。

计算本月付费金额,语句如下:

select sum(Paid) from Orders where ISOMonth(`Created At`) = ISOMonth(today())

计算上月付费金额,语句如下:

select sum(`Paid`)  from Orders where ISOMONTH(`Created At`) = ISOMONTH(dateAdd(today(), -1, "months"))

分析 2019 年首次购买的用户的续订比例

订单表中的数据是以订单为单位记录的,要分析用户的续订比例,我们首先需要得到一张以用户为单位的表。

我们用下面的 group by 语句,可以得到每个用户的首次购买时间和最后购买时间,查询出结果后,导出为一个新的子表——用户购买时间表。

select User, min(`Created At`), max(`Created At`) from Orders group by User limit 2000

接下来新建 {First year} 和 {Last year} 两个公式列 ,求出首次购买年份和最后购买年份。

这样我们就可以进行查询了,首先得到 2019 年新增的购买人数为 110 人。

select count(*) from Users where `First year`=2019

然后可以查找出首次购买时间在 2019 年,且最后购买时间是 2019 年之后的人,是 65 人。由此我们得出 2019 年新用户的续订比例为 59.0%。

select count(*) from Users where `First year`=2019 and `Last year`>2019

分析 2020 年复购金额占比

下面来进行一个比较复杂的分析,也就是 2020 年复购金额占比。我们用数据处理的"自动关联"建立订单表和上面得到的用户表的关联关系。对于 2020 年的每个订单,如果这个用户首次购买时间早于 2020 年,那么我们认为这是一次复购。

因此,首先我们在已得到的用户表中,使用链接列和自动添加链接功能,为 Orders 表和用户购买时间表建立关联,以方便引用 {First year} 列到 Orders 表中进行计算。

建立关联后,我们将 {First year} 列用链接公式引用到 Orders 表中,然后再新建两个公式列,

  • 用日期公式计算出 {Created year}——购买年份
  • 标记出复购的用户:当 {Created year} 大于 {First year} 时,那么我们认为属于复购情况,显示为 1,否则显示为 0

接下来我们就可以进行查询了,首先查询 2020 年订单总金额,为 106627.5 。

然后我们查询 2020 年的复购订单金额,为 89915.5 。因此 2020 年复购金额占比为 84.3%。

这样,我们就完成了在线订单数据的分析工作,是不是非常方便呢~