Questions Hint: for all three questions, you will

Questions
Hint: for all three questions, you will have to create new variables (mostly the date variables (hour,
month, year, day, …), aggregate the data (with pandasql) and perform some data transformations in a
particular way to get the right insights.
Q1: SellCategory to put forward
The online retailer would like to know what category of products (SellCategory) he should put forward
on his website, every hour of the day (i.e. what category of products should he show in priority on the
home page to increase its turnover). You should choose an appropriate graph type to compare some key
metrics for each SellCategory depending on the hour of the transaction. You might need to compute an
additional key metric that would measure the revenue/turnover of each category. Provide your
recommendations in the interpretation part.
Q2: Cost savings Small-Sellers
The online retailer would like to know if his profit would increase or decrease by removing all “SmallSeller” products from its website. He knows that he would reduce his costs (logistics, inventory, …) by £
5 million every year by removing all “Small-Seller” products. You can assume that when removing all the
“Small-Seller” products, all the transactions containing at least 1 “Small-Seller” product, would
disappear. He asks you to analyse what would be the impact of this decision over the last 12 complete
months (complete or full months). Please analyse the number of transactions, as well as, the cumulative
revenue of both transaction types (i.e. the ones containing a “Small-Seller” product vs. the ones not
containing any “Small-Seller” product) on a monthly time-axis. Provide your recommendation.
Q3: Free delivery
The online retailer wants to know if it would make sense to offer free delivery during the “early” hours
on his website. He would offer free delivery to all transactions over £ 220, in order to incite the
customers to buy for at least £ 220. He seems to think that this £ 220 threshold is adapted to the
3
STATS406 – Data Management and Business Analytics – Practices Overview
distribution of the transactions amounts over the early hours (early: all the hours (strictly) before 16;
late: all the hours after (including) 16). He wants you to analyse whether this threshold is rightly chosen.
Actually, he estimates that if this threshold is higher than the amount of at least 50% of the transactions
occurring in the early hours for at least 4 weekdays it would be a good idea. Indeed, he does not want to
give too many free deliveries if the people buy anyway over £ 220, even without the free delivery. For
simplicity reasons, you can perform this analysis (only for this question) on the transactions that have a
transaction amount strictly bigger than £ 0 and strictly smaller than £ 500.