Extracting Insights for Sprocket Central Pty Ltd
About this project
Sprocket Central Pty Ltd is a medium-sized bikes & cycling accessories organization, and it needs help with its customer and transaction data. The organization has a large dataset relating to its customers, but its team is unsure how to analyze it to help optimize its marketing strategy effectively.
My primary objectives were to assess and address data quality issues within the provided datasets, devise a strategy to identify 1000 customer lists and the broader market segment to reach out to, and create a comprehensive dashboard to present my findings.
STARTING WITH THE FIRST
I undertook the task of identifying data quality issues within each of the datasets, which included distinct tables such as:
Transaction Information Dataset
Customer Address Dataset
Customer Demographic Dataset
New Customer List Dataset
The data cleaning focused on handling missing or inconsistent values, standardizing formats for easy integration across datasets, identifying duplicates, and ensuring the reliability of key identifiers like customer IDs.
By the end of this stage, the datasets were ready for deeper analysis.
ANALYSING THE DATA SETS USING PIVOT TABLES
As part of my analysis, I incorporated a ‘Profit’ column by calculating the difference between the ‘List Price’ and ‘Standard Price’ columns.
“To enhance the effectiveness of data analysis using pivot tables, I employed the VLOOKUP function to integrate information from the ‘Customer Address’ table and the ‘Customer Demographic’ table into the ‘Transactions' table.
After completing both processes, I ended up with a total of 28 columns in the transaction dataset.
To determine which of the 1000 customers Sprocket Central Pty Ltd should target, my analysis was guided by the following questions:
Which customers bring in the most profit?
When are bike purchases most common?
Which job types match the company’s target market?
How do spending habits differ by wealth level?
Where are the most profitable customers located?
How do men and women shop differently?