2024: Week 22 - Top 5 Loyal Customers
Challenge by: Alexandra Skelly
We're continuing with DS43's challenges so over to Alex to explain the her next challenge.
_____________________________________
Each SuperBytes store is interested in determining their most loyal customers so that they can send them all a gift. They'll need to bring many tables of data together in order to do this.
Inputs
There are 3 tables of data needed to achieve SuperBytes goal:
- Loyalty Points
- Customer Details
- Store Data
Requirements
- Input the data (updated 3rd June)
- Start with the Loyalty Points table:
- Change the DateTime_Out field to a Date data type
- Extract the numeric part of the Loyalty Points field
- Extract the First Name and Last Name Initial from the Email Address
- Join to the Customer Details table, ensuring the number of rows remains at 999
- Join on the Store Data table
- Remove unnecessary fields
- Filter out customers without postcodes (it will be difficult to send gifts to these customers!)
- For each store, rank the customers
- Customers with the highest number of loyalty points should be ranked #1
- Filter to the top 5 customers for each store
- This may result in more than 5 customers per store if there has been ties in the number of loyalty points. SuperBytes wants to reward all these customers.
- Output the data
Output
- 10 fields
- City
- Store
- Rank
- Email Address
- First Name
- Last Name
- Loyalty Points
- Date
- Postcode
- Address
- 65 rows (66 including headers)
You can view the output here.
After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14 & @TomProwse1
You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!
