2019: Week 29
At Chin & Beard Suds Co we are developing a growing base of customers ordering on a regular basis, as a result we have decided to release a subscription option. Customers are now able to choose a package of soap(s) they wish to be sent from our range and the frequency they would like to receive their basket of soapiness.
We have a total of 7 different types of products available to choose from and customers can select any number of, or variation. A current list of subscriptions made can be found in our customers table. Reference tables for product aliases and frequency aliases are available in our subscription products table and subscription packages table.
One option is our mystery package…this could be any of the other 6 package types. To make our customers happy, we have decided to mimic our current customers’ choice of package type. Therefore, if more people like the Active, it should be more likely that we would send this out as the Mystery subscription type.
So how much should we be charging for our mystery package? Well some products are more expensive than others so work out the cost of the mystery product based on the frequency of customer orders of each type of subscription and weight this by cost of the type.
We have a total of 7 different types of products available to choose from and customers can select any number of, or variation. A current list of subscriptions made can be found in our customers table. Reference tables for product aliases and frequency aliases are available in our subscription products table and subscription packages table.
One option is our mystery package…this could be any of the other 6 package types. To make our customers happy, we have decided to mimic our current customers’ choice of package type. Therefore, if more people like the Active, it should be more likely that we would send this out as the Mystery subscription type.
So how much should we be charging for our mystery package? Well some products are more expensive than others so work out the cost of the mystery product based on the frequency of customer orders of each type of subscription and weight this by cost of the type.
Customer Table
Subscriptions Reference table
Frequency Reference table
Requirements
- Input the data file
- Calculate the price of the mystery package ***Jonathan rounded the price down to provide value of money for Chin & Beard Suds Co customers***
- Join back to original table containing product aliases and prices
- Calculate total cost of each customers subscription normalised on an annual basis
- Output the file
Output
Two files:
1. Subscription Pricing Table:
- 3 fields (subscription package, product, price)
- 7 rows ( 8 including headers)
2. Annual Cost To Customer:
- 2 fields (subscription cost per annum, Name)
- 10 rows (11 including headers)
The output can be found here for comparison. Don't to forget to fill in our participation tracker and share your solutions with us using #PreppinData on Twitter!
