Webbo3 Data Analysis Bootcamp · Capstone Build Day · Final Project

Final Project Build Day: Excel Data Cleaning, MySQL Queries, and Power BI Dashboard

A complete independent project bringing together every skill from this bootcamp: clean raw data in Excel, query it with MySQL, and build an interactive Power BI dashboard. Students work independently with instructor support available.

This is the final project of the entire bootcamp. Not just the Excel module, not just the SQL module, but everything together. You will receive a raw business dataset, clean it in Excel, load it into MySQL, write analytical queries, connect MySQL to Power BI, model the data, and build a dashboard that tells a clear story. This is the exact workflow used in real data analyst roles. Companies do not hire people who only know Excel, or only know SQL, or only know Power BI. They hire people who can move data through the entire pipeline, from messy source to clean insight. Today you prove you are that person.

1. The Project Brief and Dataset

You are a data analyst at NovaMart Retail Nigeria, a company with fifteen stores across six regions. The company sells Electronics, Clothing, Home Goods, and Groceries. Your manager has given you a raw dataset exported from three different systems, the POS system, the inventory system, and the customer loyalty program. The data is messy, inconsistent, and spread across multiple files. Your task is to unify it, analyze it, and present actionable insights to the executive team.

The business questions you must answer:

1. Which product categories and regions are driving revenue, and which are eroding profit margins?

2. What are the monthly sales trends, and can we forecast the next quarter based on historical patterns?

3. Which stores are top performers and which are underperforming relative to their region's average?

4. How do payment methods and customer segments correlate with average order value and repeat purchase behavior?

Your final deliverable is a Power BI dashboard connected to a MySQL database, which is itself fed by your cleaned Excel data. The dashboard must be interactive, visually professional, and answer all four questions at a glance.

2. Phase One: Data Cleaning in Excel

You will receive three CSV files: sales_data.csv, customers.csv, and stores.csv. Your first task is to clean and standardize them in Excel before loading into MySQL.

sales_data.csv contains: Transaction_ID, Date, Store_Name, Product_Category, Product_Name, Quantity, Unit_Price, Discount_Percent, Payment_Method, Customer_ID. Common issues include mixed date formats, inconsistent store names, text-stored numbers, negative quantities representing returns, discounts above 100 percent, and missing Customer_IDs for cash transactions.

customers.csv contains: Customer_ID, First_Name, Last_Name, Email, Phone, Registration_Date, Region, Customer_Segment. Issues include duplicate Customer_IDs, malformed emails, phone numbers in various formats, inconsistent region names, and blank segments.

stores.csv contains: Store_Name, Region, Store_Type, Opening_Date, Square_Meters. Issues include store names that do not match sales_data.csv, missing square meter values, and region abbreviations instead of full names.

Required cleaning steps:

Standardize all dates to YYYY-MM-DD format using Excel's Text to Columns or DATEVALUE functions. Remove or flag rows with future dates or dates before the company's founding year of 2020.

Standardize store names using Find and Replace or Flash Fill. Create a mapping table if needed to align store names across files.

Standardize region names to North, South, East, West, Central, and Lagos only. Remove or correct entries like "Lagos State" or "South-West."

Convert Quantity and Unit_Price from text to numbers. Handle negative quantities by creating a separate Returns column rather than deleting them, because returns are analytically valuable.

Cap discounts at 100 percent and flag any row with a discount above 50 percent as requiring manager review.

Create calculated columns: Revenue = Quantity * Unit_Price, Net_Revenue = Revenue - (Revenue * Discount_Percent / 100), Month = TEXT(Date, "YYYY-MM"), and Year = YEAR(Date).

Remove exact duplicates using Excel's Remove Duplicates tool. For near-duplicates in the customers file, for example the same person registered twice with slightly different emails, use your judgment and document your decision.

Export each cleaned file as a new CSV with the naming convention cleaned_sales_data.csv, cleaned_customers.csv, and cleaned_stores.csv. These are the files you will load into MySQL.

3. Phase Two: MySQL Database Setup and Queries

With your cleaned data ready, you now build the analytical layer in MySQL. This phase tests your ability to design a schema, load data, and write queries that answer business questions.

Step one: create the database and tables. In MySQL Workbench, create a database named novamart_analytics. Create three tables: sales, customers, and stores. Define appropriate data types for every column. Use INT for IDs and quantities, DECIMAL(12,2) for all monetary values, DATE for dates, and VARCHAR with sensible lengths for text. Add PRIMARY KEY constraints on ID columns. Add a FOREIGN KEY on sales.Customer_ID referencing customers.Customer_ID, and another on sales.Store_Name referencing stores.Store_Name. These relationships enforce data integrity.

Step two: load the CSV data. Use MySQL's LOAD DATA INFILE command or the Table Data Import Wizard in MySQL Workbench to import your cleaned CSV files into the respective tables. Verify the import by running SELECT COUNT(*) on each table and comparing to your Excel row counts. If the numbers do not match, investigate before proceeding.

Step three: write the required analytical queries. You must produce the following queries, each saved as a separate script file or clearly commented in one master script.

Query one: Revenue and margin by category and region. Write a query that joins sales, customers, and stores to calculate total revenue, total net revenue, average discount, and return rate by product category and region. Return rate is calculated as the sum of negative quantities divided by the sum of absolute quantities, expressed as a percentage.

Query two: Monthly trend with growth. Write a query that groups sales by month and year, calculating total revenue, total transactions, and month-over-month revenue growth percentage. Handle the first month gracefully by returning NULL for growth rather than an error.

Query three: Store performance ranking. Write a query that ranks stores by total net revenue, average transaction value, and revenue per square meter. Include the store's region and whether it is above or below the regional average for net revenue.

Query four: Customer segment analysis. Write a query that analyzes average order value, total lifetime value, and repeat purchase rate by customer segment and payment method. Repeat purchase rate is the percentage of customers in each segment who made more than one purchase.

Save all queries in a file named novamart_queries.sql. Add comments above each query explaining what it does and which business question it answers. Well-commented SQL is professional SQL.

4. Phase Three: Power BI Dashboard

This is the presentation layer. Everything you have built in Excel and MySQL flows into Power BI, where you model the data, write DAX measures, and design an interactive dashboard that answers the four business questions visually.

Step one: connect Power BI to MySQL. Open Power BI Desktop. Click Get Data, choose MySQL Database, and enter your server credentials. Import the three tables: sales, customers, and stores. Power BI will load the data into its internal model. In the Transform Data window, verify that data types transferred correctly from MySQL. Fix any that did not, especially dates and decimals.

Step two: build the data model. Switch to Model View in Power BI. You should see three tables. Create relationships between them. Drag from customers.Customer_ID to sales.Customer_ID. Drag from stores.Store_Name to sales.Store_Name. Verify that Power BI detects one-to-many relationships with the one side on the dimension tables, customers and stores, and the many side on the fact table, sales. If Power BI suggests a bidirectional or many-to-many relationship, reject it. Force one-to-many with single-direction filtering from dimension to fact. This is the star schema pattern, and it is the standard for performant Power BI models.

Step three: create a custom date table. Do not rely on Power BI's auto-generated date hierarchy. Create a proper date table using DAX:

DateTable = CALENDAR(MIN(sales[Date]), MAX(sales[Date]))

Add calculated columns for Year, Quarter, Month, MonthName, and DayOfWeek. Mark this table as a date table in Power BI by right-clicking it and choosing Mark as Date Table. Relate DateTable[Date] to sales[Date]. This gives you full control over date filtering and prevents the performance issues associated with auto-generated date tables.

Step four: write DAX measures. Create the following measures in a dedicated measures table or directly in the sales table. Use clear names and add descriptions.

Total Revenue = SUM(sales[Revenue])

Total Net Revenue = SUM(sales[Net_Revenue])

Average Order Value = DIVIDE([Total Revenue], DISTINCTCOUNT(sales[Transaction_ID]), 0)

Return Rate = DIVIDE(SUMX(FILTER(sales, sales[Quantity] < 0), ABS(sales[Quantity])), SUMX(sales, ABS(sales[Quantity])), 0)

Transactions = DISTINCTCOUNT(sales[Transaction_ID])

MoM Growth % = VAR CurrentMonth = [Total Revenue] VAR PreviousMonth = CALCULATE([Total Revenue], DATEADD(DateTable[Date], -1, MONTH)) RETURN DIVIDE(CurrentMonth - PreviousMonth, PreviousMonth, 0)

Store Performance vs Regional Average = VAR StoreRevenue = [Total Net Revenue] VAR AvgRevenue = CALCULATE(AVERAGEX(VALUES(stores[Store_Name]), [Total Net Revenue]), ALLSELECTED(stores[Store_Name])) RETURN IF(StoreRevenue > AvgRevenue, "Above Average", "Below Average")

Step five: design the dashboard. Create a single report page named Executive Dashboard. Follow the information hierarchy rule: most important KPIs top-left, trends in the middle, detailed breakdowns bottom-right.

Top row: four card visuals showing Total Revenue, Total Net Revenue, Average Order Value, and Transactions. Use a professional color like deep navy for the cards.

Second row: a line chart showing monthly revenue trend with the MoM Growth % measure as a secondary axis or tooltip. A clustered column chart showing revenue by product category.

Third row: a bar chart ranking stores by net revenue, colored by whether they are above or below the regional average. A matrix or table showing customer segment analysis with average order value and repeat purchase rate.

Right side: slicers for Region, Product Category, Payment Method, and Year. These must filter every visual on the page. Test each slicer to confirm cross-filtering works.

Use a light gray background, white chart backgrounds for contrast, and no more than three colors in your palette. Remove unnecessary gridlines, legends where they do not add value, and decorative elements. The dashboard should answer all four business questions within ten seconds of looking at it.

Add a text box with the dashboard title, your name, and the date. This is a professional touch that separates student work from analyst work.

5. Submission Requirements and Grading

Submit the following files via the bootcamp portal before the deadline. Late submissions receive a 20 percent penalty.

1. cleaned_sales_data.csv, cleaned_customers.csv, cleaned_stores.csv. Your cleaned Excel exports. These should be ready to load into any database without further cleaning.

2. novamart_queries.sql. Your complete MySQL script with CREATE TABLE, INSERT or LOAD DATA statements, and all four analytical queries with comments.

3. NovaMart_Dashboard.pbix. Your Power BI file. The dashboard page must be the active page when the file opens. All slicers must function. All measures must calculate correctly.

4. Project_Summary.pdf. A two-page maximum written summary explaining your data cleaning decisions, your schema design choices, your DAX measure logic, and the key business insights you discovered. Include one screenshot of your final dashboard.

Grading rubric out of 100 points: Data cleaning quality and documentation, 20 points. MySQL schema design and query correctness, 25 points. Power BI data model and relationships, 15 points. DAX measures accuracy and efficiency, 15 points. Dashboard design and interactivity, 15 points. Written summary clarity and insight depth, 10 points.

Using AI to Move Faster in Your Final Project

This project is designed to be completed independently, but independence does not mean working without tools. AI can accelerate every phase of this project if you use it strategically. Here is exactly how.

1. Use Copilot to plan your cleaning strategy before touching Excel.
Paste a sample of your raw data and ask: "Identify all data quality issues in this dataset and suggest a step-by-step cleaning sequence for Excel." Copilot will flag the mixed dates, inconsistent store names, text-stored numbers, and invalid discounts. Use this as your checklist. Your written summary should explain which suggestions you followed and which you modified based on judgment. This shows you are using AI as a research assistant, not a replacement for thinking.

2. Generate complex MySQL queries with natural language, then audit them.
The month-over-month growth query and the store performance versus regional average query are genuinely tricky. Describe what you need to Copilot: "Write a MySQL query that calculates month-over-month revenue growth, handling the first month with NULL instead of an error." or "Write a query that ranks stores by net revenue and flags whether each store is above or below its regional average." Copy the query into MySQL Workbench, test it against manual calculations for a few rows, and only then trust it for the full dataset. If it fails, debug it yourself or ask Copilot to explain the error.

3. Let AI help with DAX measure writing.
DAX has a steep learning curve, especially for time intelligence and conditional logic. Describe your measure in plain language: "Write a DAX measure that calculates the return rate as the percentage of absolute returned quantity divided by total absolute quantity, with safe division to avoid divide-by-zero errors." Copilot will likely suggest something close to DIVIDE with SUMX and FILTER. Compare it to the measure logic taught in this bootcamp, adjust for your specific column names, and verify the result against known values before using it in your dashboard.

4. Get AI feedback on your dashboard design before finalizing.
Export a screenshot of your Power BI dashboard and ask an AI vision model: "Critique this dashboard for clarity, color accessibility, visual hierarchy, and whether it answers these four business questions: category revenue, monthly trends, store performance, and customer segments. What should I remove, rearrange, or add?" AI is trained on thousands of professional dashboards and can spot alignment issues, color contrast problems, and cluttered areas that you have stared at too long to see.

5. Draft your written summary with AI, then rewrite it in your own words.
After completing the project, ask Copilot: "Draft a two-page executive summary for a data analysis project covering data cleaning, SQL schema design, Power BI modeling, and key business insights. Include sections on methodology, findings, and recommendations." Use the draft as a structural starting point, but rewrite every sentence with your actual findings, your actual decisions, and your actual insights. Never submit AI-generated text verbatim. Instructors recognize generic language, and more importantly, the summary is your chance to prove you understand what the numbers mean.

6. Verify every AI-generated output before it becomes part of your submission.
AI can write syntactically correct SQL that references the wrong column. It can write valid DAX that calculates the wrong metric. It can suggest a beautiful color palette that fails accessibility standards for colorblind users. Your final grade depends on correctness, not on whether AI helped you get there. Treat AI output as a first draft from a competent junior colleague who understands syntax but not your specific data. Audit everything. Test everything. Own the result.

A habit worth building from this project onward: whenever you are stuck on any phase of a data pipeline, cleaning, querying, modeling, or visualization, describe the problem to AI in plain language before spending an hour banging your head against it. The answer usually comes in seconds. But the verification, the judgment, and the accountability always remain yours. That combination, fast generation plus rigorous verification, is what separates analysts who use AI from analysts who are replaced by it.

Good luck. Build something that makes you proud to call yourself a data analyst.