Scenario and Task

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.

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