Portfolio project · SQL, Power BI, Excel · Advanced · 14 to 20 hours
Most analyst jobs include a recurring report. This project shows the whole chain: SQL that pulls the numbers at the right grain, a Power BI or Excel report that refreshes for a new month, totals that reconcile, and three lines of commentary that say what changed and why.
How did revenue, orders, average order value and return rate move this month, against last month and the same month last year, and why?
Turned into a SMART goal, so you know when you are done and a reviewer can see you scoped it like an analyst:
Public data, free to download. Pick one, read its license on the source page, and link that page in your README rather than uploading the files.
Microsoft Learn
Microsoft’s sample database for a fictional bicycle company: sales orders, products, customers and territories, as SQL Server backups and install scripts.
The data warehouse version (AdventureWorksDW) is already a star schema, which suits Power BI. The regular (OLTP) version gives you more SQL to write.
Google BigQuery public datasets
Orders, order items, products and users at bigquery-public-data.thelook_ecommerce. Power BI has a BigQuery connector, or you can export the monthly table to Excel.
It is synthetic data. Use it to show the pipeline and the checks rather than claims about a real business.
Kaggle
Load the CSVs into DuckDB, SQLite or PostgreSQL, write the monthly queries there, and report from the result.
The last months of the data are thin. Pick a reporting month with full data and say why.
Each step links to AnalystQuest levels that teach the skill on the Harbor & Pine store data, so you can practise it before you do it on the real dataset.
One line per KPI: the formula, the filters (which statuses, with or without shipping), and the grain it is counted at. The report month is the last complete month, never the current partial one. Every later step points back to this sheet.
Write one query per grain: an order-level table and an order-line table, each aggregated before you join them, so shipping fees or order counts are not repeated on every line. Save the result as a view or a summary table the report reads from.
Use LAG over a monthly series for month-over-month change, and compare with the same month a year earlier to take out the season. Check that both months you compare are complete.
Connect Power BI or Excel to the view through Power Query, so a new month is a refresh, not a rebuild. Lay out a KPI block (value, against last month, against last year), a 13-month trend, and a channel and category table. No hard-coded months anywhere.
Before you publish, check the report totals against a direct query on the database and note the result. Then write three lines of commentary: what moved most, the likely reason with a number behind it, and what you would look at next.
The same project, packaged three ways. Numbers in brackets are placeholders: replace them with your own results.
GitHub
CV
Start with a verb, include a number, and name the tool. Every number must be one you can explain in an interview.
Copy it into your README or a document and replace each bracket. Keep the executive summary to what a busy manager would read.
# [Project title]: monthly KPI report
## Executive summary
[Month]: revenue [value] ([+x]% vs last month, [+y]% vs [month last year]).
[The one change leadership should know about, and its likely cause.]
## Objective
How did revenue, orders, AOV and return rate move in [month], against last month and the same month last year, and why? Audience: [leadership]. Refreshed monthly.
## Data and limits
Source: [database, link]. Tables: [..]. Report month: [last complete month].
Reconciliation: report revenue [value] vs database [value] (difference [x]%).
Limits: [e.g. returns recorded late, so last month's return rate will rise].
## Method
KPI definitions in docs/kpi-definitions.md. SQL views at order and line grain, aggregated before joining. MoM with LAG; YoY against the same month last year.
## Findings
1. [Biggest mover, with numbers.]
2. [Channel or category behind it.]
3. [Anything unusual and whether it is real or a data issue.]
## Recommendations
[Where to look next, or one action.]
## Next steps
[e.g. automate the refresh, add a target line, add a drill-through page.]About 14 to 20 hours for a first version, spread over one to three weeks of evenings.
Excel, Power BI · 10 to 13 hours
One page a head of sales can read in a minute: revenue, orders, average order value and where they moved.
SQL, pandas · 12 to 16 hours
Do first-time buyers come back? Monthly cohorts and a retention grid, built in SQL and checked in pandas.
Python, Statistics · 8 to 12 hours
Check the split, estimate the lift with a confidence interval, and write a decision a product manager can act on.