Detected Anomalies#
Duplicates
In the geolocation table, 26% of the rows are complete duplicates.
The geolocation table contains 97% duplicate entries in the geolocation_zip_code_prefix column. This occurs because a single geolocation_zip_code_prefix can have multiple coordinates. This is not an anomaly but a characteristic of the data.
There are 827 duplicate review_id entries in the reviews table.
This means that one review was left for different orders.
In March 2018, there was a significant spike in the number of such duplicates.
The same review was left for different orders that have the same rating and review description but different products, prices, and other characteristics.
It is possible that one review was left for several orders at once, or this could be an error in data collection.
Missing Values
In February 2017 and August 2018, there was a spike in orders that do not have a payment approval time. The payment type voucher has a stronger influence on missing payment approval times compared to other types. This is likely a characteristic of this payment type.
14 delivered orders have missing payment approval times. All of these orders have the payment type boleto. This might be a characteristic of using boleto. All these orders were made in January and February 2017. There might have been a system failure, resulting in the payment approval times not being recorded.
In November 2017, there was a spike in orders that do not have a delivery handover time to the carrier. This might be related to Black Friday.
There are 2 delivered orders with missing delivery handover times to the carrier.
A review rating of 1 is more strongly associated with missing delivery handover times and delivery times to the buyer. This might indicate that these orders were not delivered, and users are very dissatisfied. This is also evident from the review messages, which are mostly negative. The messages often mention that the order was not delivered.
In the state of SĂŁo Paulo, missing delivery handover times to the carrier are more pronounced.
In November 2017, there was a spike in orders that do not have a delivery time to the buyer. This is likely related to Black Friday.
There are 8 orders that have a status of delivered but are missing delivery times to the buyer. 7 of these were paid with a credit card, and one was paid with a debit card.
There are 610 products with missing values in the product category name, product name length, product description length, and product photo count. All these missing values are in the same rows, meaning they are missing simultaneously across all these product characteristics.
There are 2 products with missing values in the product length, height, width, and volume. All these missing values are in the same rows, meaning they are missing simultaneously across all these product characteristics.
Order Status Anomalies
In March and April 2018, there was a sharp peak in orders stuck in the “shipped” status.
In February and August 2018, there were peaks in the “canceled” status.
In November 2017, there was a peak in the “unavailable” status. Black Friday occurred this month.
86% of orders stuck in the “processing” status have a rating of 1. 6% of orders have a rating of 2. Clearly, customers are dissatisfied. Based on review messages, orders were not delivered. Some reviews also mention that the product was out of stock.
74% of orders stuck in the “invoiced” status have a rating of 1. 9% of orders have a rating of 2. Clearly, customers are dissatisfied. Review messages indicate that orders were not delivered. Some reviews suggest the product was out of stock.
99 orders stuck in the “unavailable” status lack a category. This means they are not in the items table. This situation is more pronounced in the state of Sao Paulo compared to other states. Orders with the “boleto” payment type also stand out. 78% of orders with the “unavailable” status have a rating of 1. 8% have a rating of 2. Clearly, customers are dissatisfied. Review messages indicate that orders were not delivered. Some reviews suggest the product was out of stock.
For orders stuck in the “canceled” status:
The process is interrupted at different stages. Most interruptions occur between payment approval and carrier handover.
From December 2017 to March 2018, there was a significant spike in interruptions after carrier handover. During this period, canceled orders had a carrier handover time but no delivery time, suggesting delivery issues.
In most months, interruptions occur after payment approval.
In August 2018, there was a sharp peak in orders canceled immediately after purchase, possibly due to payment issues.
The issue was more pronounced in the state of Sao Paulo and for the “boleto” payment type.
69% of orders with the “canceled” status have a rating of 1. 7% have a rating of 2. Clearly, customers are dissatisfied. Review messages indicate that orders were not delivered. Some reviews suggest the product was out of stock.
For orders stuck in the “shipped” status:
The issue is more pronounced in the state of Rio de Janeiro compared to other states.
62% of orders with the “shipped” status have a rating of 1. 8% have a rating of 2. Clearly, customers are dissatisfied. Review messages indicate that most orders were not delivered.
Status and Delivery Mismatches
Some orders are not marked as “delivered” but have a delivery date. Likely, these orders were canceled after delivery. Review messages indicate that orders were not delivered.
There are 8 orders with the “delivered” status but no delivery time. Review messages suggest the products were delivered.
There are 6 orders with the “canceled” status but a delivery time. Reviews indicate that some products were delivered, while others were not.
Date Inconsistencies
There are 166 orders where the carrier handover time is earlier than the purchase time. These anomalies occurred only between April 25 and August 24, 2018. Possibly a system bug. Over 90% of these orders were paid with a credit card.
There are 1359 orders where the carrier handover time is earlier than the payment approval time. Over 99% of these orders have the “delivered” status. Days with the highest number of anomalies: April 19–23, 2018, and July 3–4, 2018. Possible payment approval delays.
There are 61 orders where the delivery time is earlier than the payment approval time. The issue is more pronounced for the “boleto” payment type. 87% of these anomalies occurred in the state of Sao Paulo.
There are 23 orders where the delivery time is earlier than the carrier handover time.
There are 65 orders where reviews were created before the orders themselves. Of these, 58 were canceled, 6 were delivered, and 1 has the “shipped” status.
Other Anomalies
There are 9 payments with a zero amount. These have either the “voucher” or “not_defined” payment type. Possibly a payment feature.
Over 1000 products were sold by multiple sellers located in different cities and states.
4 orders have an abnormally long carrier handover time, though their estimated delivery time is normal.
383 delivery fees are zero, possibly indicating free delivery. Most occurred between April and July 2018.
4 products have a zero weight. They belong to the “cama_mesa_banho” category (home textiles).
Some orders were placed by customers with zip code prefixes outside South America.
7877 orders have a payment total that does not match the order total. Most of these have the “voucher” payment type, likely a feature of this payment method.
The orders table contains one order not present in the payments table.
The orders table contains 775 order IDs not present in the order items table: 603 “Unavailable,” 164 “Canceled,” 5 “Created,” 2 “Invoiced,” and 1 “Shipped.”
The customers table contains 157 zip_code_prefix values not present in the geolocation table.
The geolocation table contains 4178 zip_code_prefix values not present in the customers table.
The sellers table contains 7 zip_code_prefix values not present in the geolocation table.
The geolocation table contains 16,776 zip_code_prefix values not present in the sellers table.