Data Analyst Portfolio Projects That Get You Hired

Download the public orders dataset (a common one is the 'Online Retail' dataset, or generate your own with a tool like Mockaroo). Expect a table named orders with columns order_id, customer_id, order_date, product_id, quantity, unit_price, and discount. A realistic version has 1.2 million rows and ~20,000 unique customers. Load it into SQLite or Postgres before you write any queries.

Write SELECT SUM(quantity * unit_price) AS total_revenue FROM orders to get the headline number. Then compute average order value as revenue divided by COUNT(DISTINCT order_id), and revenue per user as revenue divided by COUNT(DISTINCT customer_id). A worked example: if total revenue is $8,200,000 across 60,000 orders and 20,000 customers, ARPU is $410 and AOV is $136.67. Screenshot these three numbers — they are the story you will tell in interviews.

Group revenue by month with DATE_TRUNC('month', order_date) and chart the line. Then answer one business question like 'which top 10 products drive revenue by month?' using a window function. In my experience, interviewers ask follow-ups that start with 'so what?' — be ready to say which month dipped and why, even if your answer is a guess grounded in the data. The point is to show you can connect a number to a decision.

A clean setup is two tables: customers with columns customer_id, signup_date, plan (Free, Plus, Pro), and country; and subscriptions with customer_id, plan, start_date, end_date (NULL if still active). Define a churned customer explicitly — for example, one whose subscription ended more than 30 days ago and has not renewed. Write the definition in a sentence at the top of your write-up, because that sentence is what interviewers grade.

Compute churn rate per monthly signup cohort: churned_in_cohort / total_in_cohort, then plot it over 12 months. If the Plus plan shows a 9% monthly churn and the Pro plan shows 4%, that is a finding worth writing up. The common mistake here is averaging churn across all customers and losing the cohort signal — a fix is to always GROUP BY signup month before you aggregate.

Pick the cohort with the worst churn and state one concrete action, like 'customers who downgrade within the first 60 days churn 3x faster, so the fix is to target onboarding check-ins in the first month.' You do not need to be right — you need to show you can turn a number into an experiment. I recommend writing this recommendation as a single sentence with the number in it, so it is easy to quote in an interview.

Set up a table experiment_events with columns user_id, variant (control or treatment), event_name (page_view, signup, purchase), and timestamp. Simulate two weeks of data where the treatment group converts at 3.8% versus 3.2% for control. A worked example on 50,000 users per group gives you the numbers to defend.

Calculate conversion rate per variant, then lift = (treatment_rate - control_rate) / control_rate. With 3.8% vs 3.2%, lift is 18.75%. Then run a simple significance check (a two-proportion z-test in Python or a chi-square in a spreadsheet) so you can say whether the difference is likely real. The decision — 'ship it, but watch retention' — is the part that separates a good answer from a memorized one.

Use a sessions table (session_id, user_id, source, start_ts) and an events table (session_id, event_name, event_ts). Define stages as landing → product_page → add_to_cart → checkout_start → purchase. Count unique sessions reaching each stage, then compute the drop-off percentage between consecutive stages. A worked example: if 10,000 sessions land but only 1,200 reach checkout and 800 purchase, the biggest drop is between landing and product page at 88%.

Slice the same funnel by the source column (organic, paid, email, referral). You will almost always find that paid traffic converts worse than organic, and that finding is your write-up. State it plainly: 'Paid sessions drop 91% before product page while organic drops 74%, so we should test the paid landing page before scaling spend.' A conclusion like that, with the numbers attached, is what recruiters remember.

Use Tableau, Power BI, or Google Looker Studio — whatever you want to be hired for. Limit the dashboard to four panels: a headline KPI, a trend line, one comparison chart, and one table with filters. For the revenue project, that is total revenue, monthly revenue trend, revenue by product, and a monthly table you can filter by customer segment. Four panels is enough; more and it stops being a story.

Put a single sentence at the top of the dashboard, like 'Revenue grew 14% from Q1 to Q2, driven by the Pro plan, with AOV flat.' Then record a two-minute screen walkthrough where you narrate the dashboard. I usually upload this to a private Vimeo link or YouTube unlisted and paste it in the portfolio. A recorded walkthrough beats a wall of text because it shows you can communicate, which is half the job.

If every project uses the same famous public dataset that every tutorial uses, recruiters recognize it and assume you copied the walkthrough. The fix is to change the business question or the metric so the analysis is yours, or mix in your own generated data. Even renaming the columns and adding a year of new rows makes a real difference.

A chart without a sentence about what to do next is decoration. Every project needs a conclusion you can say out loud, with a number in it. If you cannot say what you would change in the business based on the analysis, go back and add that line. This is the single most common gap I see, and the cheapest one to fix.

If a recruiter asks 'how did you clean this?' and you cannot point to the exact steps, the project loses credibility. The fix is to include a short methodology note per project — the tables you used, the joins, and the one or two decisions you made. You do not need to publish raw customer data, but you should be able to reproduce the analysis from the note. This will break the 'is this real?' question every time.

Put the project that matches the job first. If the posting emphasizes SQL, lead with the revenue analysis. If it emphasizes dashboards, lead with the dashboard. Do not keep the same order for every application — reorder for the specific job. This takes two minutes and it is one of the highest-leverage changes you can make.

Your resume and portfolio must tell one story. The resume bullet 'Reduced reported churn analysis from days to hours with a SQL pipeline' should point to the churn project. Update your resume to reference the portfolio projects by name. If you are switching careers, the realistic path to getting hired is covered in how to become a data analyst — use it to phrase your past experience so it lines up with the projects you built. This is the second required link and it closes the loop for a recruiter.