In this challenge, learners are asked to analyze data from Bespoke Spokes’ (a regional bicycle shop) CRM. The data includes information about transactions, customers, product info, and cost. Ideally, this challenge will give learners an understanding of the basics of using Excel or other similar spreadsheet programs to critically view data.
Learners are first asked to write a description of steps to “clean” the data. This could involve (but is not limited to):
Removing Extra Spaces
Dealing w/ Blank Cells (exactly how is up to them - in this case, my preference would be to delete rows with incomplete cost/price data since that will affect the summary statistics below)
Converting Numbers Stored as Text into Numbers
Removing Duplicates
Standardizing Text to Lower/Upper/Proper Case & Spell Check
Next, learners create a summary statistics table with the mean, median stdev, max, and min for several fields, as well as COUNTs of distinct orders/order statuses/product lines. Choose a few summary statistics to check to ensure your learners completed these correctly. Also chat with your team to ensure they know what these statistics actually mean (both in general and in this particular context). Similarly, learners are also asked to add summary statistics for a new column, Profit, which they calculate by subtracting cost from price.
Note - based on how learners cleaned the data, the results may differ slightly.
Pivot Tables
Learners are then tasked with creating a pivot table showing the mean “list_price” and “standard_profit” by “wealth_segment”, as well as four graphs, which include:
The transaction count by brand (as either a bar or column chart)
A comparison of “list_price” and “standard_profit” by “wealth_segment”
A histogram of customer ages
A visualization of their choice - (they’re asked to be creative!)
Finally, learners are asked to interpret their data by proposing hypotheses for improving Bespoke Spokes’ marketing efforts. These hypotheses may include:
Focusing in on specific age or wealth demographics
Increasing promotion to customers without cars
Determining which brands are the most successful and increasing promotion on those
Determining which customer segments are most likely to return as customers
Want to print your doc? This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
Week 2 Mentor Guide
