Summary

2Market is a fictionalised in-store and online supermarket with a global presence. It wanted to better understand its customer demographics, advertising effectiveness, and product popularity so it could increase sales and optimise its marketing spend. My task was to generate insights for a targeted advertising campaign using Excel, SQL, and Tableau to analyse and visualise the provided data through interactive dashboards.


By calculating sales per customer as a Key Performance Indicator (KPI) across a range of demographic features, I identified high-income earners ages 26-35 in Canada, Germany, and the United States as the target audience for social media ads. Growing the number of customers in this cohort has the potential to increase 2Market’s sales revenue by 10%.


The approach I used in this project could be applied to maximise donations to a charity’s fundraising campaign or optimise a local authority’s outreach initiative to ensure that vulnerable communities have access to the services they need.


Could your organisation benefit from interactive dashboards to track its KPIs? Send me an email and we can arrange a meeting to discuss solutions.


Analytical Approach

I distilled the business problem into three questions that are answerable with the data provided:


• Which customers generate the most sales?

• Which ad channels generate the most sales?

• Which products generate the most sales?


Using the Mutually Exclusive, Completely Exhaustive (MECE) framework to guide my analysis, I grouped customers according to different features and relied on a sales per customer calculation as the key performance indicator (KPI) when making recommendations for optimising the targeted advertising campaign.


Here are some examples of how I used each tool to cleanse, validate, wrangle, and explore the data:


Excel

• Conducted a “Special” search for blanks using the Go To dialog box

• Scanned for duplicate rows using Conditional Formatting to highlight duplicate values in the ID column

• Checked for outliers in numerical fields using pivot tables and box-and-whisker charts: replaced Ages > 120 and Incomes > $175,000 with #N/A


• Checked for anomalies in categorical fields using pivot tables to show count for unique values: in Marital Status field changed “Absurd” and “YOLO” to #N/A and changed “Alone” to “Single” using Find and Replace.


SQL

• Conducted Date and Numeric Range Tests on joined table: no rows returned

• Queried total sales for each Product Category by Country


• Queried total Ad Channel Conversions by Country


Tableau

• Created tables analysing sales patterns for nearly every demographic field, including education


• Created a calculated field to group customers who only used one ad channel by ad channel to explore its relationship to sales per customer


Results

The main deliverable of this project was a set of interactive dashboards to help nontechnical stakeholders make data-driven decisions.


The first dashboard focuses on customer demographics and reveals a couple of important insights:


• Customers aged 26-35 generate the second-highest sales per customer after customers aged 76-85

• Despite being a global supermarket, 2Market’s customers are concentrated in Spain


The second dashboard demonstrates that while alcohol is the product category that generates the most sales, meat sales per customer are more strongly correlated with total sales per customer.


The third dashboard shows how customers who make purchases through social media ads, especially on Instragram, generate more sales than customers who make purchases in response to bulkmail or brochure ads.


Recommendations

Based on this analysis, 2Market should:


• Use targeted social media ads to reach potential customers ages 26-35 with relatively high incomes ($60,000+). These potential customers will likely be found in Canada, Germany, and the United States because young adults in these countries where 2Market has a presence tend to have higher incomes than young adults in Spain, where most 2Market’s customers are located.

• Launch a campaign to encourage existing customers, especially those ages 36-75, to purchase more meat products, because sales of these products are strongly correlated with total sales per customer.


Project Context

I submitted this project for the final assignment of the six-week Data Analytics for Business course in the LSE Data Analytics Career Accelerator programme.


Github repository