Portfolio project · Excel, Power BI · Beginner · 10 to 13 hours
One page a head of sales can read in a minute: revenue, orders, average order value and where each one moved. Building and maintaining dashboards is a big part of many analyst jobs, so this project maps straight onto the work.
Which product categories and regions drove revenue in 2017, and where did average order value slip?
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.
Kaggle
About 100,000 orders from 2016 to 2018 in several linked CSV files: orders, order items, payments, reviews, customers, sellers and products.
Category names are in Portuguese; a translation file comes with the data. Orders have a status, so decide which ones count as sales before you total anything.
UCI Machine Learning Repository
About a million invoice lines from a UK online gift retailer, December 2009 to December 2011, in one Excel workbook.
Invoice numbers starting with C are cancellations, and many lines have no customer ID. Both change your totals.
Microsoft Learn
One small, clean table of sales and profit by segment, country and product, used in Microsoft’s own Power BI tutorials.
Good for learning the tool in an afternoon, too small and tidy to stand out on its own. Rebuild on Olist for the portfolio version. The terms ask for the attribution on the workbook’s Info sheet.
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.
Write down who reads the dashboard and the three to five numbers they need. A head of sales usually wants revenue, orders, average order value and one quality measure such as the cancellation rate.
Define each one in a sentence. Is revenue before or after shipping? Which order statuses count? Put the question, the SMART goal and the definitions at the top of your README before you open the data.
Load the files with Power Query (Get Data in Excel or Power BI) so every cleaning step is recorded and replays on refresh. Set column types, remove exact duplicates, keep the statuses your definition counts, and join items to orders so you have one row per order line.
Keep a list of every row you drop and why. It becomes the limits section of the write-up.
Total revenue and orders with a PivotTable or SUMIFS in Excel, or with measures in Power BI. Average order value is total revenue divided by the number of orders, not the average of the line values.
Compare each month with the same month a year earlier before you call anything growth: most retail data has a strong November and December.
KPI tiles across the top, a monthly revenue line, a sorted bar chart of categories, and one filter for state or month. Bars start at zero, units are on every axis, and anything a reader would not act on comes out.
Three to five findings, each one sentence with a number and what it means for the reader, then one recommendation. Put a screenshot of the dashboard at the top of the README: most reviewers will not download a .pbix or .xlsx file to look.
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]: e-commerce sales dashboard
## Executive summary
[Two or three sentences: the answer to the question, the biggest number, the recommendation.]
## Objective
Which categories and regions drove revenue in [year], and where did average order value slip?
Audience: [head of sales]. Decision it supports: [where to focus next quarter].
## Data and limits
Source: [dataset, link, license]. Period: [dates]. Rows after cleaning: [n].
Removed: [n] cancelled orders, [n] duplicate rows, [n] rows with no price.
Limits: [e.g. no marketing spend, so no return on spend].
## Method
KPI definitions: Revenue = [..]; Orders = [..]; AOV = Revenue / Orders.
Tools: [Power Query for cleaning, Power BI measures / Excel PivotTables].
## Findings
1. [Finding with a number and a comparison.]
2. [..]
3. [..]
## Recommendations
[One or two actions, each tied to a finding.]
## Next steps
[What you would add with more time or data.]About 10 to 13 hours for a first version, spread over one to three weeks of evenings.
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.
SQL, Power BI, Excel · 14 to 20 hours
The report most analysts own: SQL for the numbers, Power BI or Excel for the page, refreshed every month.