E-Commerce Sales Analysis
You are the new Data Analyst at Verken Superstore. Your manager has handed you two messy CSV files and needs clean reports for the CEO and an investor presentation — by end of week.
Step 1 — Get the Files
Download both CSV files before you begin
You'll need both files open in Excel to complete any phase of this project.
Project Phases
Clean up the mess
Import both CSV files using Power Query. The dates aren't consistent, there are duplicates, and some values look wrong. Fix it before anything else.
Pull meaning out of the SKU column
Every SKU encodes a style and size — but they're all in one field. Use text functions to split them into separate columns you can actually filter by.
Connect the two files
The sales data has no prices. The product file does. Use XLOOKUP to bring category and unit price into every row so you can calculate revenue.
Give the CEO his numbers — no PivotTables
He doesn't trust them. Build the summary sheet using SUMIFS and COUNTIFS only, formatted well enough to hand to someone who will print it out.
Make it interactive for the investors
Plans changed. Now it needs to be a live dashboard they can click through during a presentation. PivotTables, charts, slicers — the full thing.
Before you start
- Read the emails carefully — they contain context clues that aren't explicitly listed in the task list.
- Format as Tables: Convert your raw datasets into Excel Tables (Ctrl + T) immediately.
- No hard coding: never type a number directly into a formula column. Use a reference cell.
- Check your work: does the total revenue in Phase 4 match the total in Phase 5? If not, something is wrong.
Your Instructions
Read these in order. Requirements will evolve as you go.
Maame Boateng
manager@verkensuperstore.com
Manager, Verken Superstore
Time-sensitive Project — We Need Your Help
We've got a bit of a data situation. Sales needs accurate reports to make informed decisions, but right now, the data is raw, inconsistent, and all over the place.
Your task is to clean, organize, and analyze the data so it's actually useful.
To start, try importing both files into a single Excel workbook. Standard procedure for this kind of data is to use Power Query — it's worth looking into if you haven't used it before, as it will save you time on cleanup.
Looking forward to seeing how you tackle this. Let me know if you need anything along the way.
Best,
Maame Boateng
Manager, Verken Superstore
Kwame Adjei
kwame.adjei@verkensuperstore.com
Data Associate, Verken Superstore
Sales Data — First Observations & Action Points
I've gone through the Sales file, and while it isn't a total mess, there are a few formatting inconsistencies that could throw off your analysis.
Start by reviewing the SKU column. We need to derive two new fields from it to categorize our inventory:
- Style: The first segment of the SKU — everything before the first hyphen. In T-001-M, the style is T. This will help us group and analyze sales by product style.
- Size: The last segment of the SKU (everything after the final hyphen). If this is a standard size (S, M, L, XL, etc.), keep it as is. If not, label it as "Free".
You might need to apply some pattern logic to extract these consistently. Let me know if you run into any odd cases.
Best,
Kwame Adjei
Data Associate, Verken Superstore
Carla Owusu
carla.owusu@verkensuperstore.com
Product Team, Verken Superstore
Re: Missing Product Details
We've noticed that the Sales Report only includes product codes (SKU), which makes it impossible to calculate revenue by category.
Could you pull the relevant product details — Category, Unit Price, and Cost — from the Product Info file into the main sales table?
Important: The Product team frequently adds new columns to their master file. Please use a lookup method that is robust and won't break if the source columns are reorganized (i.e., avoid basic column counting).
Once that is done, please calculate the Total Transaction Value for every row.
Additionally, feel free to add any other columns that might help us draw useful insights.
Best,
Carla Owusu
Product Team, Verken Superstore
Carla Owusu
carla.owusu@verkensuperstore.com
Product Team, Verken Superstore
Simple Sales Insights Request
The CEO needs a quick summary of the sales data.
He is notoriously skeptical of "hidden" data in Pivot Tables and prefers to see the raw calculations. Please focus on standard formula-based aggregation (SUMIFS, COUNTIFS, etc.) for this request — no Pivot Tables allowed yet.
He has asked for the following metrics on a new sheet called "Sales Insights Summary":
- Total Revenue
- Total Revenue by Fulfilment Type
- Total Revenue by Category
- Total Revenue by Style (using the column you created)
- Total Revenue by Size
- Top 5 SKUs by Revenue
- Most Common Style Sold
- Number of Sales Transactions per Fulfilment Type
Please format the sheet nicely so it can be printed for his morning review.
Best,
Carla Owusu
Product Team, Verken Superstore
Carla Owusu
carla.owusu@verkensuperstore.com
Product Team, Verken Superstore
Interactive Dashboard for CEO Presentation
Great work on the summary sheet. We have a change of plans for the final deliverable.
The CEO has an upcoming presentation with external investors. Unlike the internal review, the investors expect an interactive dashboard to answer ad-hoc questions during the meeting.
For this version, you have full approval to use Pivot Tables, Pivot Charts, and Slicers.
Required views:
- Total Revenue (Overall)
- Revenue by Category
- Revenue by Fulfilment Type
- Top 5 SKUs by Revenue
- Most Common Style Sold
- Average Revenue per Transaction
- Trend Analysis: Monthly Revenue Trend
Interactivity: The dashboard must include Slicers so the investors can drill down into specific Categories or Time Periods.
Keep it clean, professional, and Verken-branded.
Best,
Carla Owusu
Product Team, Verken Superstore
Ready to Start?
Download the files and get to work.
No answer key. No walkthroughs. Just the data and what you know.
Done?
Show your work. Get feedback.
Finished the project? Share it on LinkedIn to add it to your portfolio — or send Isaac your Google Drive link and he'll give you personal feedback.