Dataset Description:

The dataset, titled “Ask a Manager Salary Survey 2021 (Responses),” is a real-world collection of survey data featuring 17 variables focused on salaries and workplace demographics. Sourced from AskAManager.org, the data primarily reflects responses from the United States, with additional inputs from international participants.

• ‘Gender’ tells us the gender of workers.
• ‘Industry’ tells us the industry of the workers.
• ‘Age Groups’ tells us the age group of the workers.
• ‘Work Year Experience’ tells us the year of experience of the workers.
• ‘Location (Country, State, City)’ tells us the location of workers.
• ‘Salary’ tells us the Annual salary of workers.
• ‘Education’ tells us the highest Education qualifications of workers.
• ‘Monetary compensation’ tells us the bonuses or overtime in an average year.
• ‘Race’ tells us the race of the workers.
• ‘Job Title’ tells us the job title of the workers.

See the Datasets here: Ask a Manager Survey 2021(Responses)

Tools and Frameworks:

• Python, Jupyter notebook (Cleaning and exploratory analysis)
• Libraries: Pandas, Numpy, matplotlib and, Seaborn.
• Power BI (Data Visualization)

Key Question(s) for Analysis

• How does age group influences salary?
• Which industry pay the most?
• Which Educational Qualification earns the most salary?
• What is the most popular industry?
• How does gender vary by educational qualification?
• How do work year experience influences salary?

Key Steps and Challenges:

• Imported Libraries needed like seaborn, pandas, numpy, and matplotlib.
• Handling Encoding Errors: The first hurdle was an encoding issue. I resolved it by using the latin-1 encoding to ensure proper reading of the data.
• Renaming Irregular Columns: The column names were inconsistent, so I standardized them using Python’s rename() function with a dictionary argument. Additionally, I applied the lower() function to make them lowercase and strip() to remove trailing spaces.
• Exploring Categories: Using value_counts(), I examined the distribution of categories in variables like "Country" and "Job title." This step helped me identify inconsistencies, such as duplicate entries (e.g., "United States", "US". "USA", "UK", "United Kingdom", "United kinkdom"), and regroup them for clarity to the standard name.
• Defined and handled the inconsistent data type assigned to my columns by assessing the data programmatically using the info() function.
• In the race column, I regrouped the column to multiracial, Biracial, and moniracial for easy readability. Categories with one race was named monoracial, Categories with two races was named Biracial, and categories with more than two races was renamed multiracial.
• Changed the salary column from string to float using replace function to remove the commas. Then I converted all the salary to USD. Then I filled in null values with the mean before converting the data type from string to integer. I handled outlier in our salary column by calculating the interquartile range.
• The monetary compensation, I converted it to USD using the current exchange rate, using lambda function. Then I also filled in null values with the mean before converting to integer.
• Recategorized some rows in my work_year_exeperience column for easy understanding eg 41yearsormore to 41years+ using the replace function.
• I dropped unnecessary columns in my datafrom such as other_job_context which was not needed for my analysis.

See my python codes here: Data Cleaning and Exploratory Analysis

Data Visualization With Power BI

After transforming my data with python, I created dashboard with Power BI to uncover insight into average salary by education, age group, work experience, and Top 5 industries by salary.

Insights

• The survey datasets highlight a clear relationship between salary, age group, and work experience. The 45–54 age group earns the highest average salary, reflecting their peak earning potential, while the under 18 age group has the lowest, likely due to limited work experience and entry-level roles. Similarly, work experience significantly impacts earnings, with those having 21–30 years of experience earning the most, followed by 31–40 years of experience. Interestingly, salary is likely to be influenced more by work experience than age. The slight decline in salary for the latter group may indicate that earnings begin to plateau or decrease as individuals approach retirement.
• Individuals with Professional degrees have the highest average salaries, closely followed by those with PhDs, emphasizing the financial benefits of advanced education. In contrast, individuals with only a high school education earn the least on average, reflecting the influence of educational attainment on earning potential. This trend highlights the vital role of higher education in unlocking better-paying opportunities, highlighting its value in career advancement and income growth.
• There is an imbalance in gender distribution in education levels, particularly in advanced degrees like PhDs and professional degrees, where men are more represented. However, women lead in the Master’s degree category, suggesting their pursuit of higher education. The low representation of non-binary individuals and undisclosed gender groups across all education levels highlighting the need for inclusivity and opportunities for these groups to bridge educational gaps.
• The science and technology sector offers the highest salary opportunities, making it the fastest growing industry in this era.
• Education and Salary: Individuals with higher educational qualifications, such as Professional degrees and PhDs, earn more on average. Encouraging and supporting individuals to pursue advanced education could improve earning potential. This is especially relevant for industries requiring specialized skills.
• Work Experience and Salary: Salaries tend to peak between 21–30 years of work experience, suggesting the importance of consistent career progression and skill development. Employers should consider implementing mentorship and growth opportunities for employees to help them maximize their earning potential within this range.
• Age and Salary: Salaries increase steadily across age groups, peaking around 45–54 years. Employers might consider leveraging the expertise of this group while also investing in younger employees to ensure a skilled workforce over time.
• Industry Trends: The science and technology sector offers the highest salary opportunities, followed by education and NGOs. Individuals should be guided toward industries with higher earning potentials based on their skills and interests. Policymakers and educators might also emphasize STEM (Science, Technology, Engineering, and Math) education to prepare individuals for these lucrative fields.
• Gender Representation: Efforts should be made to address gender gaps in high-paying industries and leadership roles to ensure equity and diversity. Programs to empower underrepresented groups to access better education and career opportunities would contribute to societal balance.

Recommendation

• Education and Salary: Individuals with higher educational qualifications, such as Professional degrees and PhDs, earn more on average. Encouraging and supporting individuals to pursue advanced education could improve earning potential. This is especially relevant for industries requiring specialized skills.
• Work Experience and Salary: Salaries tend to peak between 21–30 years of work experience, suggesting the importance of consistent career progression and skill development. Employers should consider implementing mentorship and growth opportunities for employees to help them maximize their earning potential within this range.
• Age and Salary: Salaries increase steadily across age groups, peaking around 45–54 years. Employers might consider leveraging the expertise of this group while also investing in younger employees to ensure a skilled workforce over time.
• Industry Trends: The science and technology sector offers the highest salary opportunities, followed by education and NGOs. Individuals should be guided toward industries with higher earning potentials based on their skills and interests. Policymakers and educators might also emphasize STEM (Science, Technology, Engineering, and Math) education to prepare individuals for these lucrative fields.
• Gender Representation: Efforts should be made to address gender gaps in high-paying industries and leadership roles to ensure equity and diversity. Programs to empower underrepresented groups to access better education and career opportunities would contribute to societal balance.

Originally published at https://github.com.

Dataset Description:

This dataset is a snapshot of a fictional retail landscape sourced from kaggle, capturing essential attributes that drive retail operations and customer interactions. It includes key details such as Transaction ID, Date, Customer ID, Gender, Age, Product Category, Quantity, Price per Unit, and Total Amount. These attributes enable a multifaceted exploration of sales trends, demographic influences, and purchasing behaviors.

Datasets: Link

Goal of Analysis:

The goal of the Analysis is to uncover insights into customer’s demographics, purchase behaviour, and lastly product performance.

Methods

Here are some processes and method I took to achieve my goal.

1. Data Understanding: To be able to carry out this analysis, I needed to understand my data. This will enable me to be sure I have all the columns needed for my analysis and also check for inconsistencies in the dataset. I reviewed the dataset description and identified key attributes (e.g., Transaction ID, Date, Customer ID, Gender, Age, Product Category, Quantity, Price per Unit, Total Amount).
2. Data loading and Transformation: Since the datasets is relatively small. I opted to use Excel for the analysis. I loaded and formatted the datasets as a table using Ctrl + T.

• Checked for null values, inconsistencies, and anomalies using Excel’s filter functionality.
• Extracted the month and year from the Date column using the TEXT function.
• Created an Age Group column to categorize customers into age brackets using the IFS function

3. Used Descriptive Statistics to answer quick questions for stakeholders:

• Average revenue for May 2023 using the AVERAGEIFS function:

• Total revenue generated from the Beauty product category using the SUMIF function.

• The number of female customers in November 2023 using the COUNTIFS function:

Data Aggregation:

Created pivot tables on two different Sheets to aggregate data for customer insights and product performance:

• Customer Segmentation Analysis: Aggregated data by gender, age group, and revenue.

• Product Performance: Aggregated data by product category, gender, and revenue.

Data Visualization

Created two dashboards in Excel to visualize insights:

• Customer Analysis Dashboard: Focused on customer demographics and purchasing behavior.

• Product Performance Dashboard: Focused on product category performance and sales trends.

• Used charts such as donut charts, bar charts, line charts, KPIs to present insights effectively.

Insights:

Customer Analysis: Who’s Driving Our Sales?

When it comes to retail sales, understanding your customers is the key to success. Our analysis of a fictional retail dataset revealed some fascinating trends about who’s shopping, what they’re buying, and why.

Gender Distribution: Women Lead the Way

• 51% of our customers are women, and they contribute 51% of total revenue. This slight majority isn’t just about numbers — it’s about purchasing power. Women aren’t just shopping more frequently; they’re spending more per transaction, making them our most valuable customer segment.

Age Matters: The 51+ Group Dominates

• The most active and loyal customers? Those aged 51 and above. This group, largely female, is likely in or nearing retirement, with more time and disposable income to spend. They’re followed by customers aged 41–50, who are financially stable and highly engaged.
• Younger adults (21–30) are the third most active group. Early in their careers, they’re building their lifestyles and are more willing to spend. On the flip side, teenagers (15–20) are the least active, as they’re often financially dependent on parents.

Revenue Breakdown: Who’s Spending the Most?

• The 51+ age group generates the highest revenue, followed by 21–30-year-olds. Older customers have more disposable income, while younger adults are in a spending phase. Customers aged 31–40 and 41–50 are more cautious, balancing spending with responsibilities like mortgages and family expenses. The 15–20 age group contributes the least, as they’re still financially dependent.

Behavioral Insights: What Drives Purchases?

• 51+ Customers: They value convenience and quality. Think loyalty programs or home delivery services to keep them engaged.
• 21–30 Customers: They’re trend-driven and respond well to lifestyle-oriented campaigns.
• 15–20 Customers: While they’re the least active now, targeted strategies like student discounts could unlock their potential.

Product Analysis: What’s Selling and Why?

Total Revenue and Sales Volume

• Total revenue: $456,000 (51% from women).
• Total units sold: 2,514 (52% from women).
  Women aren’t just our most valuable customers — they’re also driving the majority of sales volume.

Product Preferences by Gender

• Electronics: Men buy slightly more, but women aren’t far behind. Electronics are a household staple, and women often make these decisions.
• Beauty Products: Women dominate this category, while men purchase beauty items less frequently — likely for themselves or as gifts.
• Clothing: Men outspend women here, suggesting a strong interest in fashion and functionality.

Pricing and Demand

• Beauty Products: At $184.06 on average, they’re the most expensive but sell the least (771 units).
• Clothing: The most affordable at $174.29, it’s also the top-selling category with 894 units.
• Electronics: Priced at $181.90, they strike a balance, selling 849 units and contributing 34% of revenue.

Seasonal Trends

• May was our best month, with $53,150 in revenue and 259 units sold. This spike suggests seasonal opportunities — think holiday promotions or targeted campaigns.

Recommendations: Turning Insights into Action

Our analysis of retail sales data reveals clear trends about who’s shopping, what they’re buying, and how we can better serve them. Here’s how we can turn these insights into actionable strategies:

1. Prioritize Female Customers

Why: Women make up 51% of our customer base and contribute 51% of total revenue. They’re not just frequent shoppers — they’re high spenders, likely due to their role in household purchasing decisions.

How:

• Continue offering home-related products that appeal to women.
• Expand marketing efforts to highlight convenience and quality, which resonate with this segment.

2. Don’t Overlook Male Customers

• Why: While men are slightly less active, they still show strong interest in categories like clothing and electronics.

How:

• Introduce more male-targeted products, especially in clothing and electronics.
• Market convenience-focused solutions (e.g., fast delivery, easy returns) to appeal to their busy lifestyles.

3. Cater to the 51+ Age Group

Why: Customers aged 51 and above are our most active and loyal shoppers, with the highest purchasing power. Their financial stability and free time make them a key demographic.

How:

• Offer home delivery options to make shopping easier for them.
• Create loyalty programs or exclusive discounts to reward their continued patronage.

4. Engage Younger Adults (21–30)

• Why: This group has high purchasing power, likely because they’re early in their careers with fewer financial responsibilities.

How:

• Launch trendy, lifestyle-oriented marketing campaigns.
• Offer promotions or bundles that appeal to their desire for value and style.

5. Don’t Ignore Teenagers (15–20)

Why: While this group contributes the least to sales, they represent a future customer base. Parents may also purchase on their behalf.

How:

• Introduce student discounts or back-to-school promotions.
• Partner with schools or youth organizations to build brand awareness.

6. Optimize Product Offerings

Why:

• Clothing is the top-selling category, driven by its affordability and necessity.
• Electronics are in high demand, especially for home entertainment and convenience.
• Beauty products have the highest average price but the lowest sales volume.

How:

• Prioritize clothing and electronics, as they consistently drive revenue.
• Boost beauty product sales through discounts, bundles, or loyalty rewards.

7. Leverage Seasonal Trends

Why: Sales peak in May and October, suggesting strong seasonal demand.

How:

• Launch targeted promotions and marketing campaigns during these months.
• Offer holiday-themed discounts or limited-time deals to capitalize on increased spending.