介绍

最近在数据技术领域举办了一场数据分析挑战赛，作为参与者，我们的任务是探索、清理和分析 Olist 电子商务公共数据集。该数据集旨在帮助分析师和研究人员更好地了解巴西的电子商务形势，并确定增长和优化的机会。为了帮助 Olist 更好地了解其电子商务平台并优化可用的增长机会，我们将执行探索性数据分析，并回答一些业务问题，从而提供见解和建议。

Olist 是一个巴西电子商务平台，将中小型企业与巴西各地的客户联系起来。该平台作为一个市场运作，商家可以在其中列出他们的产品和服务，客户可以在线浏览和购买它们。

数据集描述

用于此挑战的数据集是从 KAGGLE 中提取的。它包含 2016 年至 2018 年在巴西多个商城下达的 100k 订单的信息。它的功能允许从多个维度查看订单：从订单状态、价格、付款和货运绩效到客户位置、产品属性，最后是客户撰写的评论。

对于这个挑战，我使用 SQL 来访问数据集、清理和执行探索性数据分析。

以下是挑战赛的概要

1. 探索数据集并执行数据验证
2. 数据清理
3. 回答业务问题并执行探索性数据分析
4. 分享见解并提出建议

数据架构

第一阶段：数据探索和验证

我使用 Excel 探索了 9 个不同的表格，从这个过程中，我发现数据集存在以下质量问题：

- Null & 空值

- 不需要的特殊字符

- 不相关列

- 拼写错误

已发现的数据质量问题

第 2 阶段：数据清理

在清理数据时，我使用了 PostgreSQL （pgadmin14）

1. 替换不需要的字符

我为unaccent功能创建了一个扩展，并应用了reg_expreplace和unaccent功能来替换不需要的字符。

清洁过程在左侧。右侧的结果

2. 检查 Null 记录并将其替换为正确的字符串或数值

检查 null 值后，products 表的 category、name_length、descriptn_len 和 photos_qty 列中有 610 条记录具有 null 值。
我使用 coalesce 函数将类别中的 null 值替换为 N/A，将 0 替换为 name_length、descriptn_len 和 photos_qty 列中的 null 值。

替换 Null 记录

3. 将我们的类别列转换为翻译表中的英文翻译

category 列中有一些记录需要我们替换为 category_name_translatn 表中葡萄牙语字符串的英文翻译版本，但在确认翻译表中的不同记录后，products 表中有 3 条类别记录，我们在翻译表中没有找到。
他们是 1.pc_gamer 2.portateis_cozinha_e_preparadores_de_alimentos 和 3.products 表中的 Null 记录。

更新的产品表

现在我们已经清理了数据集，我们可以开始数据探索和分析。

第 3 阶段：回答业务问题并执行探索性数据分析

为了帮助 Olist 更好地了解其电子商务平台并优化可用的增长机会，我们将为以下业务问题提供答案：

1： Olist 产生的总收入是多少，随着时间的推移它是如何变化的？

2： Olist 上下了多少订单，每个月或季节有什么变化？

3： Olist 上最受欢迎的产品类别有哪些，它们的销量如何？

4：Olist 上的平均订单价值 （AOV） 是多少，它如何因产品类别或付款方式而异？

5： Olist 上有多少活跃的卖家，这个数字如何随时间变化？

6： Olist 上卖家评分的分布情况如何，这对销售业绩有何影响？

7： 有多少客户在 Olist 上进行了重复购买，他们占总销售额的百分比是多少？

8： 在 Olist 上销售的产品的平均客户评分是多少，这对销售业绩有何影响？

9： Olist 上的平均订单取消率是多少，这对卖家的表现有何影响？

10： Olist 上最畅销的产品是什么，它们的销售趋势随着时间的推移发生了怎样的变化？

11： Olist 客户最常使用哪些付款方式，不同产品类别或地理区域有何不同？

12： 客户评论和评级如何影响 Olist 上的销售和产品性能？

13： 哪些产品品类在 Olist 上的利润率最高，公司如何提高不同品类的盈利能力？

14： Olist 的营销支出和渠道组合如何影响销售和客户获取成本，公司如何优化其营销策略以提高投资回报率？

15：客户密度高的地理位置。根据地理位置计算客户保留率

商业解决方案

问题 1.Olist 产生的总收入是多少，随着时间的推移它发生了怎样的变化？为了回答第一个问题，我按年、月和年月的串联值汇总了付款价值列的总和，以一起显示每一年和它们各自的月份。从我们的收入总和中，Olist 在 2 年内总共产生了 $16M。下面，我们了解该值的周期性趋势

一段时间内的总收入

解释：随着时间的推移，Olist 的总收入持续增长，几个月内有所下降，但在 2018 年 9 月急剧下降。这主要是因为 2018 年 9 月是数据集交易记录的结束日期。然而，我们不能完全得出结论，因为前几个月的收入一直很高。下面，我们可视化了一个季节性趋势

收入随时间变化的季度趋势

从折线图可以明显看出，O-list 销售表现最好的时期是 2018 年第二季度，这是其高峰期，其次是 2018 年第一季度。进一步的分析将使我们更好地了解这一趋势

问题 2 的解答：

Olist 平台上共有 99,441 个订单。下面，我们看到这些订单按月份划分的明细（整体外观）

问题2的查询和数据输出

解读：从整体趋势来看，8月的高峰期在Olist上的订单数量最多，其次是5月和7月。9 月份的下降归因于根据我们之前的结果，该月结束的业务。

黑色星期五效应

• 需要注意的一个非常重要的注意事项是多年来下订单的趋势。下面我们看到黑色星期五效应

订单多年来一直呈上升趋势，然而，2017 年第 47 周的急剧增长可归因于黑色星期五，该日期与 11 月 24 日相吻合，落在该周内。预计客户将利用折扣销售，该折扣销售显示订单量是前一周和 2017 年其他周的两倍多

问题 3 的答案： Olist 上最受欢迎的产品类别有哪些，它们的销量如何？为了回答这个问题，我按每个类别汇总了订单计数和收入总和，并按订单计数降序对结果进行排序。

按类别划分的销量

使用每个类别的订单数来衡量受欢迎程度，bed_bath_table类别最受欢迎。但是，这个品类是否为 Olist 带来了最高的平均收入/平均订单价值？让我们在下一个问题中研究一下。

问题 4 的答案：

Olist 的平均订单价值 （AOV） 是多少，它如何因产品类别或付款方式而异？

按类别和付款类型划分的平均订单价值

解释： 电脑品类的avg_order_val最好，这表明顾客在购买该品类商品时平均花费更多，即电脑品类的商品比床头巾品类更贵。

虽然bed_bath_table品类从该品类购买的商品数量最高，但它并没有转化为更好的收入与订单数比率（即 AOV）。这意味着买家购买的商品bed_bath_table更多，但在计算机类别的商品上花费更多。O-list 可能必须考虑优化更昂贵产品的销售。从 bed_bath_table 和其他低 AOV 类别的计算机中复制 AOV 的成功率对于销售增长至关重要。为这些品类分配更多营销资源，以吸引更多流量，通过折扣优惠和其他促销策略鼓励更高的消费。

• 按付款类型变化

按付款类型划分的 AOV

Olist 客户最常用的付款方式是信用卡。在这个技术驱动的时代，这是意料之中的。此外，信用卡和牛肝菌是 Olist 客户的热门支付方式，因为它们在 Olist 总部所在的巴西非常方便且可用性高。

信用卡被广泛用于在线交易，因为它们快速且安全，使客户能够快速、自信地完成交易。此外，信用卡还提供奖励计划、现金返还和购买保护等好处，可以激励客户使用它们。

牛肝菌是巴西另一种流行的支付方式，特别是对于无法使用信用卡或不习惯使用信用卡进行在线交易的客户。Boletos 本质上是客户可以在任何银行、邮局或便利店支付的银行代金券。它们易于使用，没有银行账户或信用卡的客户也可以使用。

问题 5 的解答：

Olist 上有多少活跃的卖家，这个数字如何随时间变化？

对于这个问题，我使用至少 3 个月的活跃销售作为阈值来确定在短期内持续销售的活跃卖家，并使用 6 个月来捕捉较长时间的持续销售。但是，我知道除了季节性销售变化等其他因素之外，还可以使用其他因素来衡量活跃卖家。这就是我使用 2 个不同时期的原因。3 个月来捕捉较短的时间框架（新卖家或季节性卖家），6 个月来更全面地评估他们在平台上的表现。

• 至少 3 个月的销售评估

在下面的查询中，我创建了一个子查询来计算不同的订单、最早/首次购买他们的产品、最晚时间、产品数量及其总收入，并根据每个卖家 ID 将这些记录聚合到。结果还根据 3 个月的最短销售活动期限进行筛选。从这个子查询中，我提取了满足此条件的卖家总数

活跃卖家总数

符合至少 3 个月销售活动标准的活跃卖家总数为 1,508 名

• 至少 6 个月的销售评估

活跃卖家总数达到 791 人。

• 按年份划分的变化

解释： 结果显示，在 2016 年至 2018 年期间，活跃卖家的数量持续增加，这意味着更多的卖家加入 Olist 市场并积极销售他们的商品。这表明 Olist 正在成为卖家接触客户和发展业务的更受欢迎和更具吸引力的平台。

活跃卖家的增加还可能导致在 Olist 上销售更多种类的产品，这可能会吸引买家，并可能导致整个市场的销售额增加。

问题 6 的解决方案：Olist 上卖家评分的分布情况如何，这对销售业绩有何影响？

下面，我创建了一个 Case 语句来显示归属于每个评级级别的字符串值。我还查询了每个评级的订单数、总收入和平均收入/AOV，以显示其分布情况。

查询评级与销售表现的输入和输出

按订单数量和总收入进行评级分布

平均订单价值与评分

解释：

从前两个条形图视觉效果来看，高评价产品产生的收入最高，占客户在 Olist 平台上的订单的 80% 以上，这表明销售业绩良好。然而，从评分分布与平均订单价值（即客户在 Olist 平台上的订单上花费的平均美元）来看，结果恰恰相反，因为与低评价产品相比，高评价产品的平均订单价值较低。这可能表明低评级产品是最昂贵的项目，尽管公司制作了更好的 AOV，但客户并没有获得良好的体验。这种情况表明，买家优先考虑成本较低、评级较高的商品，而这些买家平均在更昂贵的商品上花费了更多的钱。

或者，评价高的商品可能处于竞争更激烈的市场中，尽管销量较高，但可能导致价格降低和利润率降低。在这种情况下，即使评价高的商品的订单数最高，平均订单价值也会更低。

问题 7 的解决方案：有多少客户在 Olist 上重复购买，他们占总销售额的百分比是多少？

共有 2962 位客户进行了重复购买

重复购买的客户

大约 6% 的回头客占总销售额

问题 8 的答案：

在 Olist 上销售的产品的平均客户评分是多少，这对销售业绩有何影响？

在下面的查询中，我汇总了每个产品类别的评级、总收入和平均收入的平均值。

对于 PostgreSQL （pgadmin4） 中散点图的限制，我们使用了问题 6 中的按总收入划分的评级分布条形图来推断平均客户评级与销售业绩之间存在正相关关系。评价最高的产品在 2 年期间的收入增长最好。几周后将上传进一步的可视化分析，以显示两个变量之间的相关性。

问题 9 的答案：

Olist 上的平均订单取消率是多少，这对卖家的表现有何影响？

平均订单取消率

解读：Olist 上的平均订单取消率为 0.63%（小于 1%），这是一个很好的指标，表明客户满意度很高，Olist 平台的效率水平普遍较高。这意味着，客户收到的是他们订购的产品，并且整个购买过程的问题很小。这可能会提高客户忠诚度和提高公司的整体声誉，但一个关键发现是客户保留率很低。Olist 在将新客户转化为忠实客户方面还有一些工作要做，因为客户可能从竞争对手那里获得了更好的交易，或者他们从其他公司获得了更好的客户服务体验

问题 10 的答案：

Olist 上最畅销的产品有哪些，他们的销售趋势如何随着时间的推移而变化

Olist 上最畅销的产品

2016 年最畅销产品

2017 年最畅销产品

2018 年最畅销产品

结果解释：2016 年，按类别划分最畅销的产品是家具（装饰）。2017 年是 bed_bath_tables。2016 年至 2018 年期间，电脑配件的销售增长率最高，增长率达到 196%。Olist 必须优先考虑利用这一点。下面，我们显示了图表

增长率排名前 3 的品类

由于 bed_bath_table 的整体产生收入最高，电脑配件在运营的 2 年内增长率最快。必须注意的是，这是平均订单价值最佳的主要类别。为了让Olist最大化利润并提高销售业绩，他们必须提供策略性的促销活动，如批量折扣、交叉销售和追加销售。

问题 11 的解决方案：Olist 客户最常使用哪些支付方式，不同产品类别或地理区域有何不同？

Olist 客户使用的付款类型

显示 Olist 客户使用的付款类型的饼图

视觉解读：Olist 客户最常用的支付方式是信用卡

按类别划分的付款类型

按类别显示付款类型的条形图

按州显示付款类型的条形图

视觉解读：在所有类别和地区中，最常用的支付类型是信用卡。这是意料之中的，因为它提供了便利、安全和效率

问题 12 的解决方案：客户评论和评级如何影响 Olist 上的销售和产品绩效？

从第 6 个回答的问题中可以得到类似的解释。

问题 13 的解决方案：哪些产品类别在 Olist 上的利润率最高，公司如何提高不同类别的盈利能力？

按类别显示最有利可图的产品的结果

结果解释：利润率最高 （79%） 的产品类别是 home_comfort_2 p。最低的是小家电。这令人担忧，因为它的利润率约为 10%。O-list 可以为低利润率产品实施动态定价策略，例如在淡季提供折扣

问题 14 的解决方案：Olist 的营销支出和渠道组合如何影响销售和客户获取成本，以及公司如何优化其营销策略以提高投资回报率？

为了具有市场竞争力和盈利能力，实施战略营销方法来吸引更多客户势在必行，因为这将提高平均订单价值，这就是 Olist 如何抵消客户获取成本以缩短投资回收期并提高投资回报率。从我们在业务问题 10 中的分析中可以看出，计算机配件的产品具有如此大的潜力。利用像这样快速增长的产品势在必行。Olist 可以将更多的营销资源投入到电脑配件中的产品上，同时提供交叉销售和追加销售等促销技术

问题 15 的答案： 具有高客户密度的地理位置。根据地理位置计算客户留存率

按位置计算客户密度的查询代码

按州显示客户密度的条形图

按州显示客户保留率的条形图（值以百分比表示）

图表解释：从第一个视觉对象开始，圣保罗对 Olist 的客户表示数量最多。近 50% 的 Olist 客户来自圣保罗。

然而，Acre 和 Rondônia 是客户保留率排名前 2 的州，两者的保留率均为 10%。里约热内卢以 7% 排名第三。

第四阶段：洞察和建议

见解：

1. Olist 不是通过大量销售少数产品来赚更多的钱，而是通过销售很多不同的产品
2. Olist 总体收入多年来持续增长，但在 2018 年达到顶峰（尤其是在黑色星期五期间，即 11 月的最后一周）
3. Olist 的平台上平均每月下达 4,143 个订单
4. 在 Olist 电子商务平台上，平均订单价值最高的产品类别是 computers。这归因于 2 年内快速的销售增长率，增长率为 195%
5. Bed Bath Table 是 Olist 上最受欢迎的产品类别，具体取决于订单数量。
6. 虽然 bed_bath_table 品类的购买商品数量最高，但它并没有转化为更好的收入与订单数比率（即 AOV）。这意味着客户购买了更多的bed_bath_table商品，但平均在计算机等昂贵商品上花费了更多的钱。
7. 多年来，活跃卖家的数量持续增长。越来越多的卖家加入 Olist 市场并积极销售他们的产品。这表明 Olist 正在成为卖家接触客户和发展业务的更受欢迎和更具吸引力的平台。
8. 活跃卖家的增加还可能导致在 Olist 上销售更多种类的产品，这可能会吸引买家，并可能导致整个市场的销售额增加。
9. 高评价产品产生的收入最高，占客户在 Olist 平台上下达的订单的 80% 以上，这表明销售业绩良好。
10. 迹象表明，低评级产品是最昂贵的项目，尽管该公司在昂贵项目上做出了更好的 AOV。这种情况表明，买家优先考虑成本较低、评级较高的商品，并且平均在价格更高的商品上花费更多。
11. 共有 2962 位顾客进行了重复购买。大约 6% 的回头客占总销售额。
12. Olist 的平均订单取消率为 0.63%（小于 1%），这是一个很好的指标，表明客户满意度很高，Olist 平台的效率水平普遍很高。这意味着，客户收到的是他们订购的产品，并且整个购买过程的问题很小。这可能会提高客户忠诚度和提高公司的整体声誉，但一个关键发现是客户保留率很低。Olist 在将新客户转化为忠实客户方面还有一些工作要做，因为客户可能从竞争对手那里获得了更好的交易，或者他们从其他公司获得了更好的客户服务体验

建议：

1. 为了让 Olist 最大限度地提高销售业绩、盈利能力和客户满意度，公司需要谨慎采用不同的定价策略，例如对计算机等具有高 AOV 的产品和科技产品采用交叉销售促销策略来提高销售额和盈利能力。
2. Olist 需要采用忠诚度计划来帮助提高客户保留率。客户似乎根据客户取消率低的结果对他们的订单感到满意。Olist 必须利用这一点，在其平台上提供积极的客户反馈，以帮助吸引新客户和潜在客户。

3. Olist 可以通过在淡季提供折扣或在旺季提高价格，为家用电器等低利润率产品实施动态定价策略，以提高盈利能力并优化销售。

4. 客户取消率低，客户保留率同样低。Olist 将不得不采用策略来提高客户保留率，方法是提供更多种类的产品并研究他们的客户服务体验，因为一些客户可能会因他们的客户服务体验而望而却步。他们还应该采用折扣技术和其他促销
方法，例如对计算机、其配件和其他平均订单价值较高的商品进行批量折扣。这将确保他们在市场上具有价格竞争力。客户可能已经从竞争对手那里获得
了更好的交易和促销活动

5. 圣保罗在 Olist 客户中保留了超过 45% 的客户密度，但留存率非常低。Olist 必须通过提供销售折扣、免费送货和退货来优先考虑圣保罗和里约热内卢等州。

挑战/限制

1. 为了更好地了解 Olist 电子商务市场环境，我们需要更长的数据集。
2. 需要获取同行业其他类似公司的数据集以进行比较。
3. 这个项目挑战要求只是执行探索性数据分析。获得更深入的见解，尤其是不同变量之间的关系，可以通过 Power BI 等可视化工具实现。从现在开始的几周内，我将亲自可视化此 EDA 的结果，以更好地推动数据驱动的决策。