SQL技术揭示巴西在线购物的见解

介绍

领先的零售公司Target扩大了其在巴西的电子商务业务以迎合不断增长的在线购物趋势。为了在这个竞争激烈的市场上取得成功，Target必须了解巴西电子商务的动态，并利用数据驱动的洞见来提升其业务。本文将使用结构化查询语言(SQL)动力查询分析Target的电子商务数据集，为改善其在巴西的业务提供可行建议。

学习目标

1. 通过分析Target的电子商务数据集，掌握SQL的熟练程度。
2. 学习如何使用SQL查询对数据集进行初始探索，包括数据清洗和准备。
3. 了解如何使用SQL查询分析和解释巴西的电子商务趋势。
4. 发展识别和分析电子商务数据中季节性模式的技能。
5. 通过SQL分析学习提取有关客户购买模式和偏好的有价值见解。
6. 发展基于数据的建议能力，针对数据集的SQL分析提供可行的见解，以改善电子商务业务。

阅读更多: SQL for Data Science Beginners !

本文是Data Science Blogathon的一部分。

数据集的初始探索

在深入分析之前，我们对Target的电子商务数据集进行了初始探索。这包括检查数据、清洗数据并为分析做好准备。我们使用以下SQL查询在BigQuery中验证了“客户”表的列数据类型：

SELECT
  column_name,
  data_type
FROM
  `target-analysis-sql.target.INFORMATION_SCHEMA.COLUMNS`
WHERE
  table_name = 'customers';

通过了解每个表的数据类型，我们确保了对数据集的准确分析和解释。

为了了解数据集所覆盖的时间段，我们执行了以下SQL查询：

SELECT
  DISTINCT MIN(order_purchase_timestamp) start_date,
  MAX(order_purchase_timestamp) end_date
FROM
  `target.orders`;

这使我们能够确定数据的开始和结束日期，即从2016年9月4日到2018年10月17日，为我们的分析提供了背景。

此外，我们还检查了在指定时间段内下订单的客户所在的城市和州。以下SQL查询帮助我们识别客户分布：

SELECT
  DISTINCT c.customer_city,
  c.customer_state,
  COUNT(o.customer_id) order_count
FROM
  target.orders o
JOIN
  target.customers c
ON
  o.customer_id = c.customer_id
GROUP BY
  1, 2
ORDER BY
  3 DESC;

在这里，我们可以看到SP州的圣保罗市的订单数量比其他五个城市的订单数量总和还要多。这是因为圣保罗是巴西人口最多、最富裕的州。

了解更多: From Basics to Advance of SQL

探索巴西电子商务的增长趋势

巴西的电子商务市场近年来经历了显著的增长。为了全面了解情况，必须分析各个方面，例如订单数、收入和季节性等。在本节中，我们深入数据集，揭示有关巴西电子商务趋势的见解。

巴西的电子商务趋势是否正在增长？

为了确定巴西电子商务的增长趋势，我们分析了订单数量随时间的变化。使用SQL查询，我们从订单购买时间戳中提取了年份和月份，并计算了不同订单ID的数量。执行了以下查询：

SELECT
  EXTRACT(YEAR FROM o.order_purchase_timestamp) AS year,
  EXTRACT(MONTH FROM o.order_purchase_timestamp) AS month,
  COUNT(DISTINCT o.order_id) AS order_count
FROM
  `target.orders` o
JOIN
  `target.customers` c
ON
  o.customer_id = c.customer_id
GROUP BY
  year, month
ORDER BY
  year, month;

通过订单数量的分析，可以观察到巴西的电子商务呈增长趋势。购买数量总体呈上升趋势，但有些波动。但是，需要注意的是，仅凭订单数量不能表明业务增长的速度。为了更准确地了解情况，我们还应考虑收入增长。

SELECT
  EXTRACT(YEAR FROM o.order_purchase_timestamp) AS year, 
  EXTRACT(MONTH FROM o.order_purchase_timestamp) AS month, 
  ROUND(SUM(p.payment_value), 2) as revenue
FROM
  `target.orders` o
JOIN
  `target.payments` p ON
  o.order_id = p.order_id
GROUP BY
  year, month
ORDER BY
  year, month;
 

在这里，我们可以看到与上面类似的趋势。

我们如何描述完整的场景？

要全面了解巴西的电子商务情况，必须考虑影响销售的多个因素。这些因素包括客户人口统计学、客户基数的增加、技术进步、卖家数量、订购便捷性、客户满意度、信任度随时间变化、退换货政策、付款选项、交货时间、订单取消和整体经济状况。对这些方面的全面分析将提供对巴西电子商务领域更全面的理解。

我们能看到一些在特定月份有高峰的季节性吗？

通过对数据集进行分析，我们探讨了特定月份内的季节性存在。通过从订单购买时间戳中提取月份，我们计算了每个月不同订单ID的数量。执行了以下查询：

SELECT
  EXTRACT(MONTH FROM order_purchase_timestamp) AS month,
  COUNT(DISTINCT order_id) AS order_count
FROM
  `target.orders`
GROUP BY
  month
ORDER BY
  month;

考虑到提供的数据集有限，很难对季节性趋势进行明确的结论。但是，通过Tableau中的分析和可视化，我们可以观察到电子商务订单中的一些季节性。订单数量通常从三月到八月增加，其中有些波动。值得注意的是，与巴西的狂欢节季节相一致，二月和三月的订单数量有所增加。此外，八月份的订单数量有所增加，可能与献给国酒Cachaça的Cachaça节有关。需要注意的是，需要使用更大的数据集进行进一步的分析以验证这些季节性趋势。

了解巴西客户的购买模式

为了获得有关巴西客户购买模式的洞察，我们分析了大多数订单下单时间。执行以下SQL查询，将订单购买时间戳分类为四个时段：黎明、早晨、下午和晚上。

SELECT
  CASE
    WHEN EXTRACT(HOUR FROM o.order_purchase_timestamp) BETWEEN 0 AND 5 THEN '黎明'
    WHEN EXTRACT(HOUR FROM o.order_purchase_timestamp) BETWEEN 6 AND 11 THEN '早晨'
    WHEN EXTRACT(HOUR FROM o.order_purchase_timestamp) BETWEEN 12 AND 17 THEN '下午'
    WHEN EXTRACT(HOUR FROM o.order_purchase_timestamp) BETWEEN 18 AND 23 THEN '晚上'
  END AS hour,
  COUNT(o.order_id) AS order_count
FROM
  target.orders o
JOIN
  target.customers c
ON o.customer_id = c.customer_id
GROUP BY
  hour
ORDER BY
  order_count DESC;

根据分析，我们发现巴西客户倾向于在白天下订单，尤其是下午和晚上。这表明客户喜欢在闲暇时间或完成日常活动后在线购物。值得注意的是，这里的假设是记录的时间戳反映了购买时的正确时区。

了解客户的购买模式有助于电子商务企业优化其运营。通过确定高峰购买时间，公司可以更有效地分配资源，例如客户服务代表和库存，以满足客户需求并提供无缝的购物体验。

以这种方式分析数据可以为了解巴西客户的行为和偏好提供有价值的洞察。有了这些信息，像Target这样的电子商务公司可以根据特定的时间段定制其营销策略和促销活动，最大化其覆盖范围和潜在销售。

总之，分析巴西客户的购买模式揭示了电子商务在该国的增长趋势，强调了考虑电子商务情景的各种因素的重要性，并阐明了在线购物的首选时间段。凭借这些洞察，Target和其他电子商务企业可以做出数据驱动的决策，以增强其运营并提高客户满意度。

巴西地区电子商务订单的发展：揭示各州趋势和客户分布

分析各州的月度订单

为了了解巴西地区电子商务订单的发展，我们分析了每个州每月的订单数量。以下是SQL查询的执行：

SELECT
  c.customer_state,
  EXTRACT(month FROM o.order_purchase_timestamp) AS month,
  COUNT(o.order_purchase_timestamp) AS order_count
FROM
  target.orders o
JOIN
  target.customers c
ON
  o.customer_id = c.customer_id
GROUP BY
  c.customer_state, month
ORDER BY
  c.customer_state, month;

上图显示了巴西各州每月订单数，为逐州了解客户购买趋势提供了有价值的洞察。显然，圣保罗（SP）始终是任何月份订单数量最多的州，其次是里约热内卢（RJ）和米纳斯吉拉斯（MG）。

巴西各州的客户分布

为了进一步探索巴西的电子商务景观，我们研究了各州客户的分布。以下SQL查询被执行：

SELECT
  c.customer_state,
  COUNT(c.customer_id) AS no_of_customers
FROM
  `target.customers` c
GROUP BY
  c.customer_state
ORDER BY
  no_of_customers DESC;

数据显示圣保罗州有最多的客户，这可以归因于它作为巴西人口最多的州的地位。这一发现也与先前的分析相一致，表明一个州的人口与其订单数量之间存在正相关关系。

了解电子商务订单的演变以及客户在巴西各州的分布对于像Target这样的企业来说至关重要，以便定制其营销策略，优化物流，增强客户体验。通过利用这种基于SQL的分析，电子商务公司可以有效地针对特定地区，战略性地分配资源，并提供个性化的体验，以迎合不同州客户的独特偏好和需求。

总之，分析巴西各州电子商务订单的演变和客户分布为市场的动态提供了宝贵的见解。通过利用SQL和数据驱动的方法，企业可以获得竞争优势，在巴西地区快速发展的电子商务领域中推动增长并最大化影响。

对经济的影响：分析成本趋势以及按客户州进行价格和运费价值分析

审查订单成本从2017年1月到2018年8月的百分比增长

为了了解对经济的影响，我们计算了订单成本在2017年1月到2018年8月期间的百分比增长，只考虑这些月份。执行了以下SQL查询：

SELECT
  EXTRACT(MONTH FROM o.order_purchase_timestamp) AS month,
  (
    (
      SUM(CASE WHEN EXTRACT(YEAR FROM o.order_purchase_timestamp) = 2018 AND
      EXTRACT(MONTH FROM o.order_purchase_timestamp) BETWEEN 1 AND 8 THEN
      p.payment_value END)
      -
      SUM(CASE WHEN EXTRACT(YEAR FROM o.order_purchase_timestamp) = 2017 AND
      EXTRACT(MONTH FROM o.order_purchase_timestamp) BETWEEN 1 AND 8 THEN
      p.payment_value END)
    )
    /
    SUM(CASE WHEN EXTRACT(YEAR FROM o.order_purchase_timestamp) = 2017 AND
    EXTRACT(MONTH FROM o.order_purchase_timestamp) BETWEEN 1 AND 8 THEN
    p.payment_value END)
  )*100 AS percent_increase
FROM
  `target.orders` o
JOIN
  `target.payments` p ON o.order_id = p.order_id
WHERE
  EXTRACT(YEAR FROM o.order_purchase_timestamp) IN (2017, 2018) AND
  EXTRACT(MONTH FROM o.order_purchase_timestamp) BETWEEN 1 AND 8
GROUP BY 1
ORDER BY 1;

订单成本从2017年1月到2018年8月期间的总体百分比增长率为138.53%。在考察每个月的增长率时，1月份显示了最高的百分比增长率，其次是2月份和4月份。

按客户州分析价格和运费价值的平均值和总和

为了了解各州价格和运费价值的情况，我们按客户州计算了这些值的平均值和总和。以下是SQL查询的执行：

SELECT
  c.customer_state,
  ROUND(AVG(i.price), 2) AS mean_price,
  ROUND(SUM(i.price), 2) AS total_price,
  ROUND(AVG(i.freight_value), 2) AS mean_freight_value,
  ROUND(SUM(i.freight_value), 2) AS total_freight_value
FROM
  `target.orders` o
JOIN
  `target.order_items` i ON o.order_id = i.order_id
JOIN
  `target.customers` c ON o.customer_id = c.customer_id
GROUP BY
  c.customer_state;

分析结果显示了一些有趣的发现。尽管圣保罗（SP）拥有最高的总价值和总运费价值，但它却惊人地拥有所有州中最低的平均价格值和平均运费价值。另一方面，Paraíba州（PB）拥有最高的平均价格值和平均运费价值。

了解对经济的影响需要全面分析成本趋势以及价格和运费价值。通过利用SQL查询并检查各州的模式，企业可以获得有价值的经济洞察力，识别潜在的增长机会，并做出数据驱动的决策，以优化定价策略，增强物流能力并推动整体经济影响。

分析销售、运费和交货时间：巴西洞察力

计算购买、交货和预计交货之间的天数

为了了解购买订单、交货和预计交货之间的时间长度，我们使用以下SQL查询计算了天数：

SELECT
  order_id,
  DATE_DIFF(order_delivered_customer_date, order_purchase_timestamp, DAY) 
  AS delivered_in_days,
  DATE_DIFF(order_estimated_delivery_date, order_purchase_timestamp, DAY) 
  AS estimated_delivery_in_days,
  DATE_DIFF(order_estimated_delivery_date, order_delivered_customer_date, DAY) 
  AS estimated_minus_actual_delivery_days
FROM
  `target.orders`
WHERE
  DATE_DIFF(order_delivered_customer_date, order_purchase_timestamp, DAY) IS NOT NULL
ORDER BY
  delivered_in_days;

按州分组数据并计算平均交货时间和预计交货差异的平均值

为了获得交货的平均时间以及预计交货和实际交货之间的平均差异的洞察力，我们使用以下SQL查询在州级别上计算平均值：

SELECT
  c.customer_state,
  ROUND(AVG(DATE_DIFF(order_delivered_customer_date, order_purchase_timestamp, DAY)), 2) 
  AS avg_time_to_delivery,
  ROUND(AVG(DATE_DIFF(order_estimated_delivery_date, order_delivered_customer_date, DAY)), 2) 
  AS avg_diff_estimated_delivery
FROM
  `target.orders` o
JOIN
  `target.customers` c ON o.customer_id = c.customer_id
WHERE
  DATE_DIFF(order_purchase_timestamp, order_delivered_customer_date, DAY) IS NOT NULL
  AND
  DATE_DIFF(order_estimated_delivery_date, order_delivered_customer_date, DAY) IS NOT NULL
GROUP BY
  c.customer_state
ORDER BY
  avg_time_to_delivery;

从结果中，我们可以观察到圣保罗（SP）具有最低的平均交货时间，而罗赖马州（RR）具有最高的平均交货时间。

按州分组数据并计算平均运费价值、交货时间和预计交货差异

为了分析州级别上运费价值、交货时间和预计交货与实际交货之间的关系，我们使用以下SQL查询计算了平均值：

SELECT
  c.customer_state,
  ROUND(AVG(i.freight_value), 2) AS mean_freight_value,
  ROUND(AVG(DATE_DIFF(o.order_delivered_customer_date, o.order_purchase_timestamp, DAY)), 2) 
  AS time_to_delivery,
  ROUND(AVG(DATE_DIFF(o.order_estimated_delivery_date, o.order_delivered_customer_date, DAY)), 2) 
  AS diff_estimated_delivery
FROM
  `target.orders` o
JOIN
  `target.order_items` i ON o.order_id = i.order_id
JOIN
  `target.customers` c ON o.customer_id = c.customer_id
GROUP BY
  c.customer_state
ORDER BY
  mean_freight_value;

分析表明，平均货运价值和交货时间之间存在较弱的正相关关系。圣保罗（SP）的平均货运价值最低，而罗赖马（RR）的平均货运价值最高。

了解销售、货运和交货时间的动态对于企业优化其运营并提高客户满意度至关重要。通过利用SQL查询和分析州级模式，公司可以获取有价值的销售趋势洞察，优化其物流，做出数据驱动的决策，以提高其整体效率和客户体验。

分析付款类型：订单和付款分期的洞察

不同付款类型的月度订单计数

为了了解付款类型的趋势，我们分析了不同付款类型的月度订单计数。以下是SQL查询的执行：

SELECT
  p.payment_type,
  EXTRACT(MONTH FROM o.order_purchase_timestamp) AS month,
  COUNT(DISTINCT o.order_id) AS order_count
FROM
  `target.orders` o
JOIN
  `target.payments` p
ON
  o.order_id = p.order_id
GROUP BY
  1, 2
ORDER BY
  1, 2;

分析表明，从1月到8月总体上呈上升趋势，从9月到11月又呈上升趋势。信用卡交易是最受欢迎的付款方式，其次是UPI。借记卡交易是最不受欢迎的选择。值得注意的是，与其他付款方式相比，信用卡交易正在迅速增加，可能是因为信用卡提供了“现在购买，以后付款”选项或使用信用卡获得的返现。

基于付款分期数的订单计数

为了了解付款分期的分布情况，我们分析了基于付款分期数的订单计数。以下是SQL查询的执行：

SELECT
  p.payment_installments,
  COUNT(o.order_id) AS order_count
FROM
  `target.orders` o
JOIN
  `target.payments` p
ON
  o.order_id = p.order_id
WHERE
  o.order_status != 'canceled'
GROUP BY
  1
ORDER BY
  2 DESC;

分析表明，大多数订单（最大计数）只有一个付款分期。最高的分期数是24，与18个订单相关联。

了解付款类型和分期偏好对于企业优化其付款流程和迎合客户偏好至关重要。通过利用SQL查询和分析付款趋势，公司可以做出明智的决策，以改进付款选项、简化流程并提高整体客户体验。

总之，分析提供了有价值的付款类型和分期偏好洞察。它突显了信用卡交易的流行程度、信用卡使用趋势的增长以及单一付款分期订单的普遍存在。这些洞察可以帮助企业调整其付款策略并提高客户满意度。

基于分析的可操作洞察和建议

可操作洞察

1. 数据显示，SP州的订单数量显著高于其他五个州的订单数量总和。这表明其他州存在改进的机会。关注这些州可以帮助增加订单数量并扩大客户群。
2. 销售季节性变化明显，在节日期间销售额增加。企业应该制定相应的营销和销售策略，以利用这些高峰期并提高客户满意度，从而实现总体销售增长。
3. 在交货时间较长的地区改善交货时间可以对客户满意度产生积极影响，并鼓励重复购买。简化物流并实施高效的运输流程是实现这一目标的关键。
4. 像SP和RJ这样的州已经拥有高订单数量。为了进一步推动销售并培养品牌忠诚度，建议专注于客户保留策略，如个性化营销活动、忠诚度计划和卓越的客户服务体验。
5. 分析客户人口统计信息可以为针对特定目标受众量身定制产品和营销策略提供有价值的洞察。这种定制可以带来增加的销售和客户满意度。
6. 数据显示9月和10月订单下降。在淡季期间提供折扣或促销可以激励客户在这些时期进行购买，从而提高销售。
7. 虽然数据不包括经济状况信息，但分析其对销售的影响可以帮助确定改进和投资的领域，确保在经济波动期间的韧性。

建议

1. 改善物流和运输流程，缩短交货时间，提高客户满意度。这包括优化仓库操作，精细化发货路线和与可靠的快递服务合作。
2. 实施客户保持策略，鼓励重复购买并培养忠诚度。这可以通过忠诚度计划、推荐奖励和个性化优惠实现。
3. 评估定价和运费，确保市场竞争力，同时最大化收入和盈利能力。考虑适当地提高价格或调整运费。
4. 投资于技术和基础设施，以提升电子商务体验。这包括为客户支持实施聊天机器人、提高网站性能以及根据客户行为提供个性化产品推荐。
5. 与卖家合作，扩大产品供应和提高产品质量，满足不同的客户需求和偏好。
6. 利用社交媒体平台和影响者推广产品，增加品牌知名度，因为他们在巴西的购买决策中具有强大的影响力。
7. 通过提供聊天支持服务并确保对客户询问做出及时有效的回应，增强客户服务体验。
8. 监测竞争对手的活动并相应调整业务策略，例如匹配或提供更好的定价、扩大产品供应或改进客户服务，以保持市场竞争力。

通过实施这些可行的见解和建议，企业可以优化其运营、提高客户满意度并推动巴西电子商务市场的整体销售增长。

结论

综上所述，对巴西市场的电子商务数据分析提供了有价值的见解，包括客户购买模式、销售趋势、支付偏好和交货体验。通过了解这些模式和趋势，企业可以做出明智的决策并实施策略，以优化其运营并推动增长。以下是分析的关键点：

关键点

• 圣保罗州主导了巴西的电子商务市场，表明需要关注其他州的潜在增长机会。
• 分析客户人口统计数据可以帮助针对特定目标受众量身定制产品和营销策略，从而增加销售。
• 在淡季期间提供折扣可以激励客户并在较慢的时期增加销售。

本文中展示的媒体不归Analytics Vidhya所有，由作者自行决定。