Interview Questions — E-commerce Analytics¶
Questions an interviewer might ask about this project.
[Beginner] This dataset has 8 separate tables. How did you approach combining them?
Show answer
I started by understanding the schema — which table is the "fact" table (orders/order_items) and which are dimensions (customers, products, sellers). Then I mapped the keys: orders link to customers via customer_id, to items via order_id, items link to products via product_id, and so on.
The critical discipline was joining in the right order and at the right grain:
1. Aggregate order_items to one row per order first (to avoid multiplying order-level fields)
2. Then join orders → customers → reviews
3. Assert one row per order after joining to catch any accidental row multiplication
I always verify row counts before and after each join — if the count jumps unexpectedly, a join multiplied rows and every downstream metric would be wrong.
[Beginner] What's the difference between customer_id and customer_unique_id in this dataset, and why does it matter?
Show answer
This is the dataset's biggest trap. customer_id is unique per order — a returning customer gets a brand-new customer_id for each purchase. customer_unique_id identifies the actual person across all their orders.
If you analyse repeat purchases or customer LTV using customer_id, every customer looks like a one-time buyer (0% repeat rate, which is obviously wrong). You must use customer_unique_id for any customer-level analysis.
I learned this by checking: customers['customer_unique_id'].nunique() was lower than customers['customer_id'].nunique() — that gap is exactly the repeat customers, and it only appears if you use the right key.
[Mid-level] You found delivery speed correlates with review score. How confident are you this is causal?
Show answer
Reasonably confident, but I'd be careful with the language. The relationship is strong and monotonic — review score drops steadily from 4.32 (fast) to 2.28 (very slow) — and there's an obvious causal mechanism: customers who wait a month are frustrated and rate lower.
But I'd note confounders: - Geography: remote regions have both slow delivery AND possibly other service issues - Product type: large/heavy items ship slower AND might have more quality issues - Seller quality: bad sellers might be slow AND sell worse products
To strengthen the causal claim, I'd: (1) control for category and region in the analysis, (2) look at within-seller variation — does the same seller get worse reviews when they deliver slowly? If yes, that isolates the delivery effect.
My framing to stakeholders: "Delivery speed is strongly associated with review scores, with a clear causal mechanism. Improving delivery is very likely to improve reviews — and it's worth testing directly by setting delivery SLAs and measuring the review impact."
[Mid-level] Why did you aggregate order_items before joining to orders?
Show answer
order_items has one row per item — an order with 3 products has 3 rows. If I joined order_items directly to orders and then summed an order-level field (like a flag or a customer attribute), I'd count it 3 times.
By aggregating items to one row per order first — SUM(price), COUNT(items) per order_id — I get a clean order-level frame. Then joining to orders is a clean 1:1 relationship that doesn't multiply rows.
This "aggregate the many-side before joining" pattern is fundamental to working with relational data correctly.
[Mid-level] What is RFM segmentation and how did you implement it?
Show answer
RFM scores customers on three dimensions: - Recency: how recently did they buy? (lower = better) - Frequency: how often do they buy? - Monetary: how much do they spend?
Implementation: for each customer (using customer_unique_id), I computed recency (days since last order), frequency (order count), and monetary (total spend). Then NTILE(5) to score each dimension 1-5. Combining the scores produces segments:
- High R + High F = "Champions" (protect them)
- Low R + High F = "At Risk" (win them back)
- High R + Low F = "New" (nurture to second purchase)
RFM is powerful because it's interpretable and directly maps to marketing actions — unlike a black-box clustering model.
[Senior] Olist's repeat purchase rate is only 3%. How would you investigate and what would you recommend?
Show answer
Investigation:
1. Confirm it's real, not a data artefact — verify I'm using customer_unique_id and the full time window (a short window understates repeat rate because customers haven't had time to return).
2. Segment the repeat behaviour — is 3% uniform, or do certain categories/regions/sellers have higher repeat rates I can learn from?
3. Link to satisfaction — what's the repeat rate for customers who left 5-star reviews vs 1-star? I'd expect the gap to be large, confirming satisfaction drives retention.
4. Time-to-second-purchase — for the few who do return, how long does it take? This informs when to trigger win-back campaigns.
Recommendations: - Address the root cause (delivery speed → satisfaction → retention) - Build post-purchase engagement (the first 30 days after delivery are critical) - Target the "New" RFM segment specifically — converting a first-time buyer to a second purchase is the highest-leverage retention moment
Caveat I'd raise: as a marketplace (not a direct retailer), Olist may have structurally lower repeat rates than a single-brand store — customers may not perceive "Olist" as the brand they're buying from. That's a strategic question about brand visibility, not just a tactical retention fix.
[Senior] How would you turn this analysis into a production data pipeline?
Show answer
- Move from CSVs to a warehouse: load the source tables into BigQuery/Snowflake/Postgres on a schedule.
- Build transformation models (dbt): the joins, aggregations, and RFM logic become version-controlled, tested SQL models. The "master frame" and "customer summary" become materialised tables refreshed nightly.
- Data quality tests: assert one row per order after joins, no null order totals, review scores in 1-5 — as automated dbt tests that fail the pipeline if violated.
- Incremental processing: only process new/changed orders rather than reprocessing 100k orders nightly.
- Serve to BI: Tableau/Power BI connects to the warehouse tables (not CSVs), with scheduled extract refresh.
- Monitoring: alert if metrics drift (e.g., repeat rate suddenly changes — likely a data issue, not a real shift).
- Documentation: every metric (LTV, RFM segment definitions) documented as a single source of truth so the business trusts the numbers.