Portfolio project · SQL, pandas · Intermediate · 12 to 16 hours
Do first-time buyers come back, and is that getting better or worse? You group customers by the month of their first order and follow each group over time. Doing it in SQL and again in pandas, with matching numbers, shows two skills and the habit of checking your own work.
What share of first-time customers order again within 90 days, by month of first order, and is it improving?
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.
UCI Machine Learning Repository
Two years of invoices with a customer ID and a timestamp on each line, so you can follow customers month by month. A common choice for cohort analysis.
Drop lines with no customer ID and treat C invoices as cancellations before you look for first orders.
Kaggle
A harder version: most customers order only once, so the story is how small the repeat base is and what sets the returning customers apart.
Use customer_unique_id. customer_id is created per order, so with it every customer looks like a one-time buyer.
Google BigQuery public datasets
A synthetic clothing store with users, orders and order items, at bigquery-public-data.thelook_ecommerce. Query it with SQL in the free BigQuery sandbox.
The data is generated, so say so in the write-up and keep the findings about method rather than about a real business.
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 the rules before any query. A customer is one person, not one account or one order. Their cohort is the month of their first completed order. They came back if a second completed order follows within 90 days.
Decide what to do with young cohorts: customers who first bought in the last 90 days of the data have not had the full window yet. Leave those cohorts out or mark them as incomplete. This is right-censoring, and interviewers ask about it.
Remove cancellations and rows without a customer, merge duplicate customers, and check the date range. Count customers and orders before and after, and keep those counts for the README.
Find each customer’s first order with MIN or ROW_NUMBER, give every later order a month offset from it, then count distinct customers per cohort and offset and divide by the cohort size. Save the queries as .sql files in the repo, not only in a notebook.
Repeat the calculation with groupby and pivot_table and confirm it matches SQL cohort by cohort. Then draw the retention grid as a heatmap: cohorts down the side, months since first order across, the rate in each cell and the cohort size in the label.
Add one line chart of the 90-day repeat rate per cohort. That is the chart that answers the question; the grid is the evidence.
Lead with the rate and the trend, then the cohorts that stand out and a reason you can check (season, first product category, channel). Say clearly what the data cannot tell you, such as whether a discount caused a return.
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]: customer cohort retention
## Executive summary
[The 90-day repeat rate, its trend, and the one action you recommend.]
## Objective
What share of first-time customers order again within 90 days, by month of first order, and is it improving? Audience: [marketing lead].
## Data and limits
Source: [dataset, link, license]. Period: [dates].
Customers after cleaning: [n]. Removed: [n] cancellations, [n] rows without a customer.
Cohorts after [month] excluded: fewer than 90 days of data (right-censoring).
Limits: [no marketing data, so returns cannot be tied to campaigns].
## Method
Customer = [..]. Cohort = month of first completed order. Came back = second completed order within 90 days. Built in SQL ([engine]); rebuilt in pandas and matched cohort by cohort.
## Findings
1. [Overall rate and trend, with numbers.]
2. [Strongest and weakest cohorts, with sizes.]
3. [What the strong cohorts have in common.]
## Recommendations
[One or two actions and how you would measure them.]
## Next steps
[e.g. revenue retention, not only customer retention; a test of the win-back offer.]About 12 to 16 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.
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.