import duckdb
def create_csv_path(table_name):
return f"../raw_data/{table_name}.csv"
customers = create_csv_path("olist_customers_dataset")
geolocation = create_csv_path("olist_geolocation_dataset")
order_items = create_csv_path("olist_order_items_dataset")
order_payments = create_csv_path("olist_order_payments_dataset")
order_reviews = create_csv_path("olist_order_reviews_dataset")
orders = create_csv_path("olist_orders_dataset")
products = create_csv_path("olist_products_dataset")
sellers = create_csv_path("olist_sellers_dataset")
category_translation = create_csv_path("product_category_name_translation")https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce https://www.kaggle.com/datasets/olistbr/marketing-funnel-olist
Olist connects small businesses from all over Brazil to channels without hassle and with a single contract. Those merchants are able to sell their products through the Olist Store and ship them directly to the customers using Olist logistics partners.
An order might have multiple items.
Each item might be fulfilled by a distinct seller.
All text identifying stores and partners where replaced by the names of Game of Thrones great houses.Basically, the customer flow is 1. customers browse through products provided by various sellers. 2. If they like them, they’ll order. They can have multiple order_items. 3. Once customers are ready, they’ll head through order_payments process and wait for delivery. 4. After customers confirm that their received their items OR after estimated delivary date, customers will be given prompt to order_reviews.
Database Findings
Since I do not have full information, I need to make sure and do sanity checks on the database relationship
order_payments
duckdb.sql(f'''
SELECT COUNT(*) total_entry, COUNT(DISTINCT order_id) AS unique_order_id
FROM '{order_payments}'
LIMIT 10
''')┌─────────────┬─────────────────┐
│ total_entry │ unique_order_id │
│ int64 │ int64 │
├─────────────┼─────────────────┤
│ 103886 │ 99440 │
└─────────────┴─────────────────┘In order_payments, order_id is NOT unique. So, how would the payment_value works?
duckdb.sql(f'''
SELECT order_id, COUNT(*)
FROM '{order_payments}'
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY order_id
''')┌──────────────────────────────────┬──────────────┐
│ order_id │ count_star() │
│ varchar │ int64 │
├──────────────────────────────────┼──────────────┤
│ 0016dfedd97fc2950e388d2971d718c7 │ 2 │
│ 002f19a65a2ddd70a090297872e6d64e │ 2 │
│ 0071ee2429bc1efdc43aa3e073a5290e │ 2 │
│ 009ac365164f8e06f59d18a08045f6c4 │ 6 │
│ 00b4a910f64f24dbcac04fe54088a443 │ 2 │
│ 00bd50cdd31bd22e9081e6e2d5b3577b │ 3 │
│ 00c405bd71187154a7846862f585a9d4 │ 7 │
│ 00c95282163553a982f38481f9488481 │ 4 │
│ 00e6bc6b166eb28b4502c1cad4457248 │ 2 │
│ 013037c3f1773cc8fdbaf8ff54d8013b │ 2 │
│ · │ · │
│ · │ · │
│ · │ · │
│ fef89b64a9e94a0533f8c0afa152f524 │ 2 │
│ fefbe15ebcd87ab3fb8577e635a8b31c │ 6 │
│ ff027db47fd74b1bcf2dd6c24f0b809e │ 2 │
│ ff1f8f328e5a59ac754240b6dd31bcb4 │ 4 │
│ ff20ee702706d9e407a34de9fe2ff768 │ 2 │
│ ff7400d904161b62b6e830b3988f5cbd │ 2 │
│ ff978de32e717acd3b5abe1fb069d2b6 │ 4 │
│ ffa1dd97810de91a03abd7bd76d2fed1 │ 2 │
│ ffa39020fe7c8a3e907320e1bec4b985 │ 2 │
│ ffc730a0615d28ec19f9cad02cb41442 │ 2 │
└──────────────────────────────────┴──────────────┘
2961 rows (20 shown) 2 columnsduckdb.sql(f'''
SELECT order_id, COUNT(*) AS total_entry, SUM(payment_value) AS total_payment
FROM '{order_payments}'
WHERE order_id = '009ac365164f8e06f59d18a08045f6c4'
GROUP BY order_id
''')┌──────────────────────────────────┬─────────────┬───────────────┐
│ order_id │ total_entry │ total_payment │
│ varchar │ int64 │ double │
├──────────────────────────────────┼─────────────┼───────────────┤
│ 009ac365164f8e06f59d18a08045f6c4 │ 6 │ 32.0 │
└──────────────────────────────────┴─────────────┴───────────────┘duckdb.sql(f'''
SELECT *, price + freight_value AS total_payment
FROM '{order_items}'
WHERE order_id = '009ac365164f8e06f59d18a08045f6c4'
''')┌──────────────────────────────────┬───────────────┬──────────────────────────────────┬──────────────────────────────────┬─────────────────────┬────────┬───────────────┬───────────────┐
│ order_id │ order_item_id │ product_id │ seller_id │ shipping_limit_date │ price │ freight_value │ total_payment │
│ varchar │ int64 │ varchar │ varchar │ timestamp │ double │ double │ double │
├──────────────────────────────────┼───────────────┼──────────────────────────────────┼──────────────────────────────────┼─────────────────────┼────────┼───────────────┼───────────────┤
│ 009ac365164f8e06f59d18a08045f6c4 │ 1 │ 35557c68a22ecebcf066e25ca2ddc144 │ f8db351d8c4c4c22c6835c19a46f01b0 │ 2017-11-29 00:11:24 │ 16.9 │ 15.1 │ 32.0 │
└──────────────────────────────────┴───────────────┴──────────────────────────────────┴──────────────────────────────────┴─────────────────────┴────────┴───────────────┴───────────────┘So order_items has one-to-many with order_payments. order_payments only concerns with order_id and its sequential payments.
order_items
duckdb.sql(f'''
SELECT *
FROM '{order_items}'
LIMIT 10
''')┌──────────────────────────────────┬───────────────┬──────────────────────────────────┬──────────────────────────────────┬─────────────────────┬────────┬───────────────┐
│ order_id │ order_item_id │ product_id │ seller_id │ shipping_limit_date │ price │ freight_value │
│ varchar │ int64 │ varchar │ varchar │ timestamp │ double │ double │
├──────────────────────────────────┼───────────────┼──────────────────────────────────┼──────────────────────────────────┼─────────────────────┼────────┼───────────────┤
│ 00010242fe8c5a6d1ba2dd792cb16214 │ 1 │ 4244733e06e7ecb4970a6e2683c13e61 │ 48436dade18ac8b2bce089ec2a041202 │ 2017-09-19 09:45:35 │ 58.9 │ 13.29 │
│ 00018f77f2f0320c557190d7a144bdd3 │ 1 │ e5f2d52b802189ee658865ca93d83a8f │ dd7ddc04e1b6c2c614352b383efe2d36 │ 2017-05-03 11:05:13 │ 239.9 │ 19.93 │
│ 000229ec398224ef6ca0657da4fc703e │ 1 │ c777355d18b72b67abbeef9df44fd0fd │ 5b51032eddd242adc84c38acab88f23d │ 2018-01-18 14:48:30 │ 199.0 │ 17.87 │
│ 00024acbcdf0a6daa1e931b038114c75 │ 1 │ 7634da152a4610f1595efa32f14722fc │ 9d7a1d34a5052409006425275ba1c2b4 │ 2018-08-15 10:10:18 │ 12.99 │ 12.79 │
│ 00042b26cf59d7ce69dfabb4e55b4fd9 │ 1 │ ac6c3623068f30de03045865e4e10089 │ df560393f3a51e74553ab94004ba5c87 │ 2017-02-13 13:57:51 │ 199.9 │ 18.14 │
│ 00048cc3ae777c65dbb7d2a0634bc1ea │ 1 │ ef92defde845ab8450f9d70c526ef70f │ 6426d21aca402a131fc0a5d0960a3c90 │ 2017-05-23 03:55:27 │ 21.9 │ 12.69 │
│ 00054e8431b9d7675808bcb819fb4a32 │ 1 │ 8d4f2bb7e93e6710a28f34fa83ee7d28 │ 7040e82f899a04d1b434b795a43b4617 │ 2017-12-14 12:10:31 │ 19.9 │ 11.85 │
│ 000576fe39319847cbb9d288c5617fa6 │ 1 │ 557d850972a7d6f792fd18ae1400d9b6 │ 5996cddab893a4652a15592fb58ab8db │ 2018-07-10 12:30:45 │ 810.0 │ 70.75 │
│ 0005a1a1728c9d785b8e2b08b904576c │ 1 │ 310ae3c140ff94b03219ad0adc3c778f │ a416b6a846a11724393025641d4edd5e │ 2018-03-26 18:31:29 │ 145.95 │ 11.65 │
│ 0005f50442cb953dcd1d21e1fb923495 │ 1 │ 4535b0e1091c278dfd193e5a1d63b39f │ ba143b05f0110f0dc71ad71b4466ce92 │ 2018-07-06 14:10:56 │ 53.99 │ 11.4 │
└──────────────────────────────────┴───────────────┴──────────────────────────────────┴──────────────────────────────────┴─────────────────────┴────────┴───────────────┘
10 rows 7 columnsduckdb.sql(f'''
SELECT COUNT(*) AS total_entries, COUNT(DISTINCT order_id) AS unique_orders, COUNT(DISTINCT product_id) AS unique_products, COUNT(DISTINCT seller_id) AS unique_sellers
FROM '{order_items}'
''')┌───────────────┬───────────────┬─────────────────┬────────────────┐
│ total_entries │ unique_orders │ unique_products │ unique_sellers │
│ int64 │ int64 │ int64 │ int64 │
├───────────────┼───────────────┼─────────────────┼────────────────┤
│ 112650 │ 98666 │ 32951 │ 3095 │
└───────────────┴───────────────┴─────────────────┴────────────────┘order_payments have: - Have non-unique orders - Have non-unique products per orders - Have non-unique products per sellers
duckdb.sql(f'''
SELECT order_id, product_id, COUNT(*)
FROM '{order_items}'
GROUP BY order_id, product_id
HAVING COUNT(*) > 1
''')┌──────────────────────────────────┬──────────────────────────────────┬──────────────┐
│ order_id │ product_id │ count_star() │
│ varchar │ varchar │ int64 │
├──────────────────────────────────┼──────────────────────────────────┼──────────────┤
│ 85c467a504e5796387bb68fe2adcc256 │ dafc867209fb20b8331f1edaebc95b58 │ 2 │
│ 873c916c2bff3ea61a59f7bf7f0879c2 │ e7cc48a9daff5436f63d3aad9426f28b │ 2 │
│ 893c5d0f1a2fd75917c6b37fcd220601 │ 958cf0f5527f413e08d0cde6bfdbdde2 │ 4 │
│ 89e7c89d4642d0f6de33b1314a209e96 │ 4fa33915031a8cde03dd0d3e8fb27f01 │ 2 │
│ 8c27da2e91c92bdf51f131d0d24d9af7 │ c7fd13b5e515bffdab855d0812842edb │ 2 │
│ 8e213471c11bcad28837b1442a6f041a │ fbce52c4e7a3898c2ade51f177f77012 │ 2 │
│ 8e3f8be6e1ce8ec5b39dae59e0aabcff │ 9d838a7fbaba7fca5f1fad259984063a │ 2 │
│ 8e60a511ac30aacb9ded963801a906af │ 232a5adb0fc1881bbfeb03560c639c31 │ 2 │
│ 8e96b8e465f71da5a9cde973c3a8d726 │ 5272e64de887c95d7a1c9c64886b90f5 │ 2 │
│ 8fb4d17d3419dda57ca57e6a230152c7 │ 363218ba55c610b750224f90bdd34be1 │ 2 │
│ · │ · │ · │
│ · │ · │ · │
│ · │ · │ · │
│ 7515c1d8cfed88ade550c3fb589598f0 │ a4b5771bb3bd270e2baacfc4430798cf │ 2 │
│ 767ea088b6cc91e6b7631c6d30cb980c │ aa5c7ccc97c30087e5183ed27708905a │ 2 │
│ 787da13655afdfe6f549e008c69ebca7 │ 8156795e2a2cba07750ff2fa3ad6e373 │ 2 │
│ 7b04a7ac2acca6b694c8e7495f57369a │ 781afe929e3016a667f5f439afd55fce │ 2 │
│ 7c3fe5194219d774a7d238f80d4c9919 │ 4c692d2f14f767ecfe67b427fe305e3d │ 2 │
│ 7c91640daf4cb53f476cd9db5eb0baf5 │ 8b0db3fb68c6d4b494281666287e4c72 │ 2 │
│ 7e61a4c6d93d6ad8a4ca4a8d00094339 │ d4ad9721bda56413600c43c11006cb78 │ 5 │
│ 7fc930983a4062abd3407e7d6a0141a5 │ 3b7929cc079c14493fb6fb3373dbc1e1 │ 2 │
│ 81694353e4bd6b9c70314404bee7367e │ bbc9e653c2f5794542ac3867ab5ab757 │ 2 │
│ 837d1f8cf82a0c97441b80b64d287b94 │ a19b6951c75da43aad691622dd2f6abe │ 3 │
└──────────────────────────────────┴──────────────────────────────────┴──────────────┘
7088 rows (20 shown) 3 columnsduckdb.sql(f'''
SELECT *
FROM '{order_items}'
WHERE order_id = '85c467a504e5796387bb68fe2adcc256'
''')┌──────────────────────────────────┬───────────────┬──────────────────────────────────┬──────────────────────────────────┬─────────────────────┬────────┬───────────────┐
│ order_id │ order_item_id │ product_id │ seller_id │ shipping_limit_date │ price │ freight_value │
│ varchar │ int64 │ varchar │ varchar │ timestamp │ double │ double │
├──────────────────────────────────┼───────────────┼──────────────────────────────────┼──────────────────────────────────┼─────────────────────┼────────┼───────────────┤
│ 85c467a504e5796387bb68fe2adcc256 │ 1 │ dafc867209fb20b8331f1edaebc95b58 │ 0691148aee60ca47977c187804f935ae │ 2018-01-18 15:08:20 │ 883.9 │ 218.12 │
│ 85c467a504e5796387bb68fe2adcc256 │ 2 │ dafc867209fb20b8331f1edaebc95b58 │ 0691148aee60ca47977c187804f935ae │ 2018-01-18 15:08:20 │ 883.9 │ 218.12 │
└──────────────────────────────────┴───────────────┴──────────────────────────────────┴──────────────────────────────────┴─────────────────────┴────────┴───────────────┘Additionally, if a product is ordered in multiple quantity, it will match the quantity as well. What about the total payment value though? are they added together or are they only counted once.
duckdb.sql(f'''
SELECT *
FROM '{order_payments}'
WHERE order_id = '85c467a504e5796387bb68fe2adcc256'
''')┌──────────────────────────────────┬────────────────────┬──────────────┬──────────────────────┬───────────────┐
│ order_id │ payment_sequential │ payment_type │ payment_installments │ payment_value │
│ varchar │ int64 │ varchar │ int64 │ double │
├──────────────────────────────────┼────────────────────┼──────────────┼──────────────────────┼───────────────┤
│ 85c467a504e5796387bb68fe2adcc256 │ 1 │ credit_card │ 6 │ 2204.04 │
└──────────────────────────────────┴────────────────────┴──────────────┴──────────────────────┴───────────────┘So, the freight_value is counted multiple times. And the total matches with order_payments.
duckdb.sql(f'''
SELECT CORR(products.product_weight_g, order_items.freight_value)
FROM '{order_items}' AS order_items
JOIN '{products}' AS products USING (product_id);
''')┌────────────────────────────────────────────────────────────┐
│ corr(products.product_weight_g, order_items.freight_value) │
│ double │
├────────────────────────────────────────────────────────────┤
│ 0.6104202350972835 │
└────────────────────────────────────────────────────────────┘Decent correlation, probably paired up with distance as well. Charging per item is really odd though.
order_customer
How many total unique customers?
duckdb.sql(f'''
SELECT COUNT(DISTINCT customer_unique_id)
FROM '{customers}'
''')┌────────────────────────────────────┐
│ count(DISTINCT customer_unique_id) │
│ int64 │
├────────────────────────────────────┤
│ 96096 │
└────────────────────────────────────┘Interestingly, customer_id and customer_unique_id are not the same. customer_id are generated per order while customer_unique_id is the true user id
duckdb.sql(f'''
SELECT customer_unique_id, COUNT(DISTINCT customer_id)
FROM '{customers}'
GROUP BY customer_unique_id
HAVING COUNT(DISTINCT customer_id) > 1
''')┌──────────────────────────────────┬─────────────────────────────┐
│ customer_unique_id │ count(DISTINCT customer_id) │
│ varchar │ int64 │
├──────────────────────────────────┼─────────────────────────────┤
│ e836a4279bd9127752d8949d46f7a5a5 │ 2 │
│ 771f344c1ac13d8ccdf2d14c48e2c6c3 │ 2 │
│ dc450fdb03562a6d52752858f7855348 │ 2 │
│ d0be859f85cda9d896ab14e4ffba955d │ 2 │
│ b4be8ffe5a39196f0098f30316f7d7a1 │ 2 │
│ 2ae6a57dcdd2f1424598fc480cbf698a │ 2 │
│ cb06773dbb3415543b78720a3986344e │ 2 │
│ 17a2d93cb1aab45e3d21d5eba7daaed2 │ 2 │
│ 13473af264dea0facd893d6516900b22 │ 2 │
│ 7cb20dd79b4b5bfda52b712000a30de5 │ 2 │
│ · │ · │
│ · │ · │
│ · │ · │
│ a0ef77ac3d3662b055dc12b045da37ed │ 2 │
│ 7b05e0471103ce0ef346e87c357273fe │ 2 │
│ 4ce423721c74231c174d0bb22367645a │ 2 │
│ 5a6b37816cefa8497b74b1ef78dbb202 │ 2 │
│ 35e9d2caa31b87cc3d1ad10d72271140 │ 2 │
│ 0bc9c245cd69735af23eefdd89d74e7e │ 2 │
│ d8ca95d946a44bf4af460bc5eea8c00f │ 2 │
│ e06e62694cd8ce637a1c3f4a624c647e │ 2 │
│ 21560f93adbd9ada040a40909ac8619d │ 2 │
│ 90c205b0bed658a20ba141d2960d6811 │ 2 │
└──────────────────────────────────┴─────────────────────────────┘
2997 rows (20 shown) 2 columnsorder_reviews
duckdb.sql(f'''
SELECT order_id, COUNT(DISTINCT review_id) AS ref_count
FROM '{order_reviews}'
GROUP BY order_id
HAVING ref_count > 1
''')┌──────────────────────────────────┬───────────┐
│ order_id │ ref_count │
│ varchar │ int64 │
├──────────────────────────────────┼───────────┤
│ 2f8f31eb2f7b6572836d662a6625c8e4 │ 2 │
│ 273177c9b2d2eb2a68a77ee39e726b52 │ 2 │
│ 2e3258f81be08e497abb202cef0c0303 │ 2 │
│ aae84f9096ee934bec7b48604a47e6e1 │ 2 │
│ 3ee6f1a94cd7b1aa622553fd94a3aaf3 │ 2 │
│ 013056cfe49763c6f66bda03396c5ee3 │ 2 │
│ 9b24c8496d87684865617cdb3eff7065 │ 2 │
│ acbaf2e843c77c9749c79030163c8b81 │ 2 │
│ 95e7f49dc56e12097c265c45527a3941 │ 2 │
│ 96f5be02bc9ffc589f3274500a64a7e2 │ 2 │
│ · │ · │
│ · │ · │
│ · │ · │
│ 029863af4b968de1e5d6a82782e662f5 │ 2 │
│ d04aa7a56c20579debaf2cd69ce4dd73 │ 2 │
│ bf593abe2928b23b60cee1ff3471a4dd │ 2 │
│ 80fe5b2fe4869330fabbceea5e5e7c0c │ 2 │
│ 78cf5dc2baadfbac2c47c6ef7c2a2282 │ 2 │
│ 3ab974ac744bd19a111d5e19f0a90649 │ 2 │
│ c1deab56eafcb4bec7703bbe7bd25137 │ 2 │
│ 574c4ecf7699daffc21fc04eb876b8a8 │ 2 │
│ ee6830242df204d5290f7c4704464b56 │ 2 │
│ aa2ae4efd578f1c304128a899e4d1a17 │ 2 │
└──────────────────────────────────┴───────────┘
547 rows (20 shown) 2 columnsAn order can have multiple reviews. This is probably caused by orders with multiple products.
duckdb.sql(f'''
WITH multiple_reviews AS (
SELECT order_id, COUNT(DISTINCT review_id) AS ref_count, ARRAY_AGG(review_score) AS scores
FROM '{order_reviews}'
GROUP BY order_id
HAVING ref_count > 1
)
SELECT a.*, multiple_reviews.scores
FROM (
SELECT multiple_reviews.order_id, COUNT(DISTINCT product_category_name) AS category_count,
ARRAY_AGG(DISTINCT product_category_name) AS categories
FROM multiple_reviews
LEFT JOIN '{order_items}' AS order_items ON multiple_reviews.order_id = order_items.order_id
LEFT JOIN '{products}' AS products ON order_items.product_id = products.product_id AND order_items.product_id IS NOT NULL
WHERE order_items.product_id IS NOT NULL
GROUP BY multiple_reviews.order_id
HAVING category_count > 1
) AS a
LEFT JOIN multiple_reviews ON a.order_id = multiple_reviews.order_id
''')┌──────────────────────────────────┬────────────────┬──────────────────────────────────────────────────────────────────┬─────────┐
│ order_id │ category_count │ categories │ scores │
│ varchar │ int64 │ varchar[] │ int64[] │
├──────────────────────────────────┼────────────────┼──────────────────────────────────────────────────────────────────┼─────────┤
│ 39948b49e7e68844f9f5a31d12a3c4c6 │ 2 │ [moveis_decoracao, cama_mesa_banho] │ [3, 4] │
│ ed63d1955429b2d9b6172c755b4fa1b2 │ 2 │ [relogios_presentes, moveis_decoracao] │ [3, 5] │
│ bcb0343717a9e0265e156e34c25c3dc0 │ 2 │ [esporte_lazer, alimentos_bebidas] │ [4, 5] │
│ 2f8f31eb2f7b6572836d662a6625c8e4 │ 3 │ [instrumentos_musicais, fashion_bolsas_e_acessorios, automotivo] │ [5, 5] │
│ 79675ac76b2c97ba601ce736de298724 │ 2 │ [cama_mesa_banho, moveis_decoracao] │ [5, 2] │
│ 4420cbe16c262f724b648cd1294c88b6 │ 2 │ [moveis_decoracao, perfumaria] │ [1, 3] │
│ 8e350e1e4254bd7c68913b98bde7d3a7 │ 2 │ [casa_conforto, cama_mesa_banho] │ [1, 5] │
│ 3df55fc07ff463109ce0422439693aee │ 2 │ [cool_stuff, cama_mesa_banho] │ [1, 4] │
│ 7d4101163410b1380268da3933e691f1 │ 2 │ [cama_mesa_banho, casa_conforto] │ [1, 3] │
└──────────────────────────────────┴────────────────┴──────────────────────────────────────────────────────────────────┴─────────┘There is a 3 distinct categories order with two scores. While it is logically possible to only review a subset of the ordered items, it is safe to check if it is the case.
duckdb.sql(f'''
SELECT order_reviews.review_id, order_items.product_id, product_category_name, order_reviews.review_creation_date
FROM '{order_reviews}' AS order_reviews
INNER JOIN '{order_items}' AS order_items ON order_reviews.order_id = order_items.order_id
INNER JOIN '{products}' AS products ON order_items.product_id = products.product_id
WHERE order_reviews.order_id = '2f8f31eb2f7b6572836d662a6625c8e4'
ORDER BY product_id
''')┌──────────────────────────────────┬──────────────────────────────────┬─────────────────────────────┬──────────────────────┐
│ review_id │ product_id │ product_category_name │ review_creation_date │
│ varchar │ varchar │ varchar │ timestamp │
├──────────────────────────────────┼──────────────────────────────────┼─────────────────────────────┼──────────────────────┤
│ 7b606b0d57b078384f0b58eac1d41d78 │ 3c025396e63ff6925a3b68781e8b3456 │ instrumentos_musicais │ 2017-02-15 00:00:00 │
│ 1f13da30e937fb82ac15ca7cc20e4537 │ 3c025396e63ff6925a3b68781e8b3456 │ instrumentos_musicais │ 2017-02-15 00:00:00 │
│ 7b606b0d57b078384f0b58eac1d41d78 │ 5415fa4d6c000c97cddafcf0419b09f7 │ fashion_bolsas_e_acessorios │ 2017-02-15 00:00:00 │
│ 1f13da30e937fb82ac15ca7cc20e4537 │ 5415fa4d6c000c97cddafcf0419b09f7 │ fashion_bolsas_e_acessorios │ 2017-02-15 00:00:00 │
│ 7b606b0d57b078384f0b58eac1d41d78 │ f59ff3f667dc8dc2b42117736585c1ba │ automotivo │ 2017-02-15 00:00:00 │
│ 1f13da30e937fb82ac15ca7cc20e4537 │ f59ff3f667dc8dc2b42117736585c1ba │ automotivo │ 2017-02-15 00:00:00 │
└──────────────────────────────────┴──────────────────────────────────┴─────────────────────────────┴──────────────────────┘Separating reviews per product from timestamp seems impossible with what we have.
duckdb.sql(f'''
SELECT *
FROM '{order_reviews}'
''')┌──────────────────────────────────┬──────────────────────────────────┬──────────────┬──────────────────────┬─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬──────────────────────┬─────────────────────────┐
│ review_id │ order_id │ review_score │ review_comment_title │ review_comment_message │ review_creation_date │ review_answer_timestamp │
│ varchar │ varchar │ int64 │ varchar │ varchar │ timestamp │ timestamp │
├──────────────────────────────────┼──────────────────────────────────┼──────────────┼──────────────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼──────────────────────┼─────────────────────────┤
│ 7bc2406110b926393aa56f80a40eba40 │ 73fc7af87114b39712e6da79b0a377eb │ 4 │ NULL │ NULL │ 2018-01-18 00:00:00 │ 2018-01-18 21:46:59 │
│ 80e641a11e56f04c1ad469d5645fdfde │ a548910a1c6147796b98fdf73dbeba33 │ 5 │ NULL │ NULL │ 2018-03-10 00:00:00 │ 2018-03-11 03:05:13 │
│ 228ce5500dc1d8e020d8d1322874b6f0 │ f9e4b658b201a9f2ecdecbb34bed034b │ 5 │ NULL │ NULL │ 2018-02-17 00:00:00 │ 2018-02-18 14:36:24 │
│ e64fb393e7b32834bb789ff8bb30750e │ 658677c97b385a9be170737859d3511b │ 5 │ NULL │ Recebi bem antes do prazo estipulado. │ 2017-04-21 00:00:00 │ 2017-04-21 22:02:06 │
│ f7c4243c7fe1938f181bec41a392bdeb │ 8e6bfb81e283fa7e4f11123a3fb894f1 │ 5 │ NULL │ Parabéns lojas lannister adorei comprar pela Internet seguro e prático Parabéns a todos feliz Páscoa │ 2018-03-01 00:00:00 │ 2018-03-02 10:26:53 │
│ 15197aa66ff4d0650b5434f1b46cda19 │ b18dcdf73be66366873cd26c5724d1dc │ 1 │ NULL │ NULL │ 2018-04-13 00:00:00 │ 2018-04-16 00:39:37 │
│ 07f9bee5d1b850860defd761afa7ff16 │ e48aa0d2dcec3a2e87348811bcfdf22b │ 5 │ NULL │ NULL │ 2017-07-16 00:00:00 │ 2017-07-18 19:30:34 │
│ 7c6400515c67679fbee952a7525281ef │ c31a859e34e3adac22f376954e19b39d │ 5 │ NULL │ NULL │ 2018-08-14 00:00:00 │ 2018-08-14 21:36:06 │
│ a3f6f7f6f433de0aefbb97da197c554c │ 9c214ac970e84273583ab523dfafd09b │ 5 │ NULL │ NULL │ 2017-05-17 00:00:00 │ 2017-05-18 12:05:37 │
│ 8670d52e15e00043ae7de4c01cc2fe06 │ b9bf720beb4ab3728760088589c62129 │ 4 │ recomendo │ aparelho eficiente. no site a marca do aparelho esta impresso como 3desinfector e ao chegar esta com outro nome...atualizar com a marca correta uma vez que é o mesmo aparelho │ 2018-05-22 00:00:00 │ 2018-05-23 16:45:47 │
│ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │
│ f8ac2ca757c6702a8f7b9f82a3361b54 │ b77e196ee314322dcec6124fa3e38b6b │ 1 │ A boneca │ A boneca não é igual a imagem que está no site, por isso fiquei bem desapontado, pois sempre que comprei algumas coisas por esse site eram bem atendidas │ 2018-08-29 00:00:00 │ 2018-09-02 16:27:56 │
│ c5b14992eb69dc68e3e855cd95f0ed57 │ 7b374a3cd8b325fd49e6789ccfa88639 │ 5 │ NULL │ NULL │ 2018-05-19 00:00:00 │ 2018-05-21 18:16:28 │
│ c210b4ed36c4ce629a8ce851be0d5828 │ de1c6db658e84e0ba847b2c1bc857bb5 │ 1 │ NULL │ Não veio o que foi pedido!! │ 2018-04-17 00:00:00 │ 2018-04-17 22:55:46 │
│ 110160bcffa1c9bef8b1868aba109cf1 │ 930fff097a9427c8a9a70559aa36fa76 │ 5 │ NULL │ Produto lindo! De ótima qualidade │ 2018-08-31 00:00:00 │ 2018-09-03 00:11:20 │
│ 3dd6dd8bd30dd50a2b15328239cfcd91 │ acfa87544c19d596707d25cf2f7dab61 │ 1 │ nao entregou │ nao entregou o pedido dentro do prazo ja solicitei cancelamento │ 2018-08-25 00:00:00 │ 2018-08-27 03:32:14 │
│ 6a1edd1c07b9ceb1a031a9e3d0a0c69a │ 4ac735cd851ba30e7b280f7e045544a0 │ 5 │ NULL │ NULL │ 2017-09-29 00:00:00 │ 2017-09-30 09:51:15 │
│ a2e0d982abe0a650156ec056ca4d1b85 │ a4244008bc97ae9cdc3807237fa83229 │ 5 │ Satisfeita │ Entrega no prazo combinado , chegou certinho recomendo esse loja │ 2018-07-04 00:00:00 │ 2018-07-07 15:50:37 │
│ c5ab0934968065d986a65f00567d4c54 │ 8d74231228bdbc2e89fc0125abfc1c87 │ 3 │ NULL │ entrega rápida, produto certo. Só não dou mais estrelas pra stark, pois uma vez comprei um produto que depois arrependi e eles nao trocaram p mim ate hj o produto esta parado fechado lacrado. │ 2017-08-11 00:00:00 │ 2017-08-12 03:30:12 │
│ db5a5956ff1b3b42cea50a82a54bf781 │ 1644056a29868cbba3251a81fae0e1f8 │ 2 │ NULL │ NULL │ 2017-05-24 00:00:00 │ 2017-05-24 13:02:42 │
│ 78fa666f7808563cf40c8eac105316bf │ 2a19516124bb3d5f4d0d07555104e322 │ 4 │ NULL │ NULL │ 2017-03-17 00:00:00 │ 2017-03-19 21:48:51 │
└──────────────────────────────────┴──────────────────────────────────┴──────────────┴──────────────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────────────┴─────────────────────────┘
? rows (>9999 rows, 20 shown) 7 columnsSo, how different are reviews in the same order?
duckdb.sql(f'''
WITH multiple_reviews AS (
SELECT order_id, COUNT(DISTINCT review_id) AS ref_count, MIN(review_score) AS min, MAX(review_score) AS max, MEAN(review_score) AS mean,
MAX(review_score) - MIN(review_score) AS range
FROM '{order_reviews}'
GROUP BY order_id
HAVING ref_count > 1
)
SELECT range, COUNT(DISTINCT order_id) AS order_count
FROM multiple_reviews
GROUP BY range
''')┌───────┬─────────────┐
│ range │ order_count │
│ int64 │ int64 │
├───────┼─────────────┤
│ 0 │ 345 │
│ 2 │ 47 │
│ 3 │ 32 │
│ 4 │ 33 │
│ 1 │ 90 │
└───────┴─────────────┘Out of 547 orders with multiple reviews, 345 of them have 0 range (max - min).
duckdb.sql(f'''
SELECT COUNT(DISTINCT order_id)
FROM '{order_reviews}'
''')┌──────────────────────────┐
│ count(DISTINCT order_id) │
│ int64 │
├──────────────────────────┤
│ 98673 │
└──────────────────────────┘So, from ~98K orders that are reviewed (where orders can be reviewed multiple time because there is no product granularity), we saw 547 orders have multiple reviews. Out of 547 orders, 345 of them have 0 range (max - min). The number of orders where the scores differs much is so few that we will be handling which reviews to take by taking the latest review.
orders
What are the status looking like
duckdb.sql(f'''
SELECT order_status, COUNT(DISTINCT order_id) AS order_count
FROM '{orders}'
GROUP BY order_status
''')┌──────────────┬─────────────┐
│ order_status │ order_count │
│ varchar │ int64 │
├──────────────┼─────────────┤
│ shipped │ 1107 │
│ delivered │ 96478 │
│ created │ 5 │
│ canceled │ 625 │
│ unavailable │ 609 │
│ approved │ 2 │
│ invoiced │ 314 │
│ processing │ 301 │
└──────────────┴─────────────┘Mostly delivered orders, which makes sense. What if we cut to the recent 2 months
duckdb.sql(f'''
SELECT order_status, COUNT(DISTINCT order_id) AS order_count
FROM '{orders}'
WHERE DATE(order_purchase_timestamp) >= (SELECT MAX(order_purchase_timestamp) - INTERVAL '2 months' FROM '{orders}')
GROUP BY order_status
''')┌──────────────┬─────────────┐
│ order_status │ order_count │
│ varchar │ int64 │
├──────────────┼─────────────┤
│ unavailable │ 1 │
│ canceled │ 47 │
│ delivered │ 1567 │
│ shipped │ 8 │
└──────────────┴─────────────┘