MY KPMG AU DATA ANALYTICS VIRTUAL INTERNSHIP
Background Information
Sprocket Central Pty Ltd (fictional company), a medium size bikes & cycling accessories organization based in Australia.
Primarily, Sprocket Central Pty Ltd needs help with its customer and transactions data. The organization has a large dataset relating to its customers, but their team is unsure how to effectively analyze it to help optimize its marketing strategy.
The client provided KPMG with 3 datasets:
- Customer Demographic
- Customer Addresses
- Transactions data in the past 3 months
This is a project from my KPMG data analytics virtual internship program where work as a Data Analyst to performed three tasks:
- Data Quality Assessment: Assessment of data quality and completeness in preparation for analysis.
- Data Insights: Targeting high value customers based on customers demographic and attributes.
- Data Insights and Presentation: Using visualizations to present insights.
Data Quality Assessment
During this task, assessment of data quality and completeness was done on the three (3) datasets and recommendations are provided in preparation for analysis using Excel Spreadsheet.
Some of the data issues:
Quality issues
Transaction dataset
- Online order, brand, product line, product class, product size, list price, standard cost, and product first sold date fields contains blank cells.
- Inconsistent data type for Product first sold date field.
- Duplicated values in Customer Id field.
Customer Demographic dataset
- Last name, DOB, Job title, tenure fields contains blank cells.
- Incorrect data (U, F, M, Femal) in the Gender field.
- Incorrect data in the DOB field.
Additional customer_ids in the ‘Transactions table’ and ‘Customer Address table’ but not in ‘Customer Master (Customer Demographic)’.
Data Insights
During this task, the existing 3 datasets (Customer demographic, customer address and transactions) was used as a labeled dataset to provide recommendations on which of the 1,000 new customers dataset should be targeted to drive the most value for the organization by following 3 phases:
- Data Exploration
- Data Model Development
- Data Interpretation.
Data Exploration
In this phase, the datasets was explore by understanding the data, checking for missing values, outliers, data inconsistencies and performing data transformation.
After cleaning the data issues listed above;
Customer Demographic dataset
Transaction dataset
Data Modeling
In this phase, data model was created on the three datasets using Power Pivot in Excel to identify and create relationship between them.
Relationship created based on their Customer IDs
Created calculated fields based on the existing data:
Age and Age group columns created from DOB column in Customer Demographic dataset for analysis.
Profit column created from List price and Standard cost column in Transaction dataset.
Data Interpretation
In this phase, analyze the datasets using pivot tables and charts to provide findings that will help achieve the organization goals.
I used Pivot tables and charts to answer some key analysis questions for the organization. Here are some of the results:
Total profit generated by Age group
Observation: Age group (35–48) generated the most profits followed by 49–62.
Past years bike purchases by Age group
Observation: The most past 3 years bike related purchases is under age 35–48 by 40.54%.
Total profits and orders by State
Observation: From the distribution above, NSW State has the most orders and generated the most profits.
Past years bike purchases by State
Observation: From the above chart, 54% of the past 3 year’s bike related purchases is in NSW state while 25% in VIC and 21% in QLD state.
% of Orders by Gender
Observation: 51.12% of orders made by Female while 48.88% by Male.
Number of Orders per Month
Observation: More than 1,000 orders are made in each month. October, August and July have the most orders of 1,631, 1,621 and 1,580 respectively. September has the lowest orders of 1,448.
Data Insights and Presentations
During this task, created a dashboard using Excel Spreadsheet to communicate and convey key findings from the analysis and also give recommendations to the stakeholders of the organization.
Conclusion:
This virtual internship program was a challenging and great one for me. I was able to work with a group of analytics team on a project that help an organization optimized its marketing strategy for high-value new customers with their existing customer and transactions data and drive company growth. It also help sharpening my data analysis skills including communication and presentation skills.
I am looking forward to work more on real-world projects to continuing learning and growing my data analysis skills and career.
Special thanks to KPMG AU and Forage for this opportunity.
Recommendation:
• Targeted ads and campaigns should be focused new customers within age group (35–48) since they have generated the most profits and orders for the company followed by age 49–62.
• From the Old Customer data, past 3 years bike purchases range between 0–99. New Customers with minimum of 50 bike purchases in the past 3 years should be regarded as high-value customers.
• New customers in NSW state should be targeted since old customers in the same state have generated the most profits and orders for the company.
• Targeted ads and campaigns should be focused more on customer with no cars since they have no means of transportation. Car owners should also be focused on as they may opt-in for an alternative means of transportation such as bikes.
• New customers that are mass customers under wealth segment category should be targeted, since old customers in the same category have the highest rate of orders by 53%.
Here is the link for anyone interested in the virtual internship: KPMG AU Data
You can check out other project on my Github repository and website portfolio.
Thank you for taking your time to read it, kindly comment your suggestion on this project. I will also appreciate a like/follow from you.
