2Market Project

Consumer Behaviour & Market Trend Analysis: Retail Industry

Executive summary:

Using Excel, Python and Tableau, I used proprietary customer data to create a series of dashboards to track customer demographics, purchasing behaviour and social media engagement for a global supermarket. After identifying that the largest revenue opportunities lie in expanding smaller but established markets, I recommended that the marketing team implement a few adjustments that would lead to higher sales:

1. Country specific targeted marketing in established markets
2. Promotion of most popular products
3. Improved social media interaction with largest demographic groups

Business problem:

2Market are a global supermarket company in the retail industry with both instore and online purchasing. In the last financial year sales have declined and a new rival online supermarket retailer has emerged. 2Market wish to use a data driven approach to better understand their customers, purchasing behaviour, demographic and advertising impact.

Target questions:

1. Identify the size and average spend of 2Market customers by country, age and marital status to inform resource priority for future marketing campaigns.

2. Find the market share of different product lines and compare this share across different countries to discern regional preferences and tailor marketing to diversify risk on over reliance on one particular product line.

3. Determine the top performing social media platform for advertising by country to establish their impact and to determine regional preferences and target marketing to specific social media platforms that have shown to be most effective in lead conversions.

Methodology:

1. Inspection, initial cleaning, transformation and EDA in Excel using csv files of raw data.
2. SQL query that further transforms and performs EDA on cleaned data.
3. Build dashboards in Tableau that provides a breakdown of customers and orders by status and social media marketing impact.

Skills:

• Excel: import, conditional formatting, ISBLANK(), LEN(), TRIM(), CLEAN()), TEXTSPLIT(), COUNTIF() and aggregate functions, PivotTables
• SQL: Joins, Case, aggregate functions, union
• Tableau: calculated columns, data visualisation, data modelling, dashboarding with interactive elements

Results and Business Recommendations:

Creating a series of dashboards of customer demographic, product spend and social media impact gives management and marketing stakeholders visibility into the market share and insights into targeted sales avenues to pursue further. Highest total spend was by customers from Spain, who make up half of all customers, with South Africa and Canada 2nd and 3rd. The US form a smaller portion but have high income and spend, a potential established market to target.

Across all countries, half of sales came from alcohol, followed by meat and commodities and should be targeted as they make 85% of sales. The sales of these items could be increased in the largest demographic group of married/together customer over the age of 50.

Platform data was confused by multiple successful conversions for some customers using a variety of social media, making it unclear which had the most influence in conversion. Twitter (now X) was most successful at generating conversions.

Dashboard created in Tableau outlining customer demographics

As a result, I made the following recommendations:

• Diversification of country target market to the three countries (South Africa, Canada and the US) with established smaller market share, but with good upsell potential.
• Target sales of the most popular products (alcohol, meat and commodities) which make up 85% of market sales share.
• Focus media advertising to specific platforms and largest demographic groups which historically have provided the majority of conversions.

Next Steps:

• Develop data capture systems and customer surveys to improve reliability of data
• AB Test social media campaign and click rates on targeted emails
• Measure footfall in physical store locations