In partnership with
It’s 11:00 AM. Your manager walks over to your desk (or pings you on Slack, same energy).
"Revenue is down 15% this week compared to last week. Can you figure out what happened? I need an answer before the 3 PM leadership sync."
You have 4 hours. No one is going to tell you where to look. No one is going to hand you a clean dataset. You are on your own.
This is Root Cause Analysis (RCA). And it is probably 40% of what you will do as a data analyst. Every week. For the rest of your career.
No bootcamp teaches this. No YouTube tutorial covers it. So let’s walk through it together.
The Wrong Way (What most juniors do)
You open the database. You write a query. Revenue this week vs. last week. Yep, it’s down 15%. You make a bar chart showing the drop. You send it to your manager.
Manager’s response: "I already know it’s down 15%. I need to know WHY."
A chart showing the problem is not an analysis. Your manager can read numbers. What they can't do (because they don't have time) is drill into the data layer by layer until the real cause shows up.
That is your job.
The Right Way: The Drill-Down Tree
Think of revenue as a tree with branches. Your job is to figure out which branch is broken.
Level 1: The Equation Revenue = Number of Orders × Average Order Value (AOV) ( Excluding Traffic/Users for simplicity )
That is the first split. Before you touch SQL, ask yourself: Is revenue down because we got fewer orders? Or because people spent less per order?
SELECT
CASE
WHEN order_date BETWEEN '2026-02-02' AND '2026-02-08' THEN 'This Week'
WHEN order_date BETWEEN '2026-01-26' AND '2026-02-01' THEN 'Last Week'
END AS week_label,
COUNT(DISTINCT order_id) AS total_orders,
ROUND(SUM(revenue) / COUNT(DISTINCT order_id), 2) AS avg_order_value,
SUM(revenue) AS total_revenue
FROM orders
WHERE order_date BETWEEN '2026-01-26' AND '2026-02-08'
GROUP BY week_label;
The Result:
Last Week: 10,000 Orders | ₹850 AOV
This Week: 8,200 Orders | ₹880 AOV
The Insight: Orders dropped significantly. AOV actually went up slightly. So the problem isn't pricing or product mix—it is volume. We have narrowed the tree.
Level 2: Where did the orders drop?
"Fewer orders" is still too broad. We need to split further.
By what? Whatever makes sense for the business. Usually:
Channel (App vs. Website vs. Offline)
Region (Delhi vs. Mumbai vs. Bangalore)
User Type (New vs. Returning)
Let’s try Channel first:
SELECT
channel,
COUNT(DISTINCT CASE WHEN week = 'Last Week' THEN order_id END) AS last_week,
COUNT(DISTINCT CASE WHEN week = 'This Week' THEN order_id END) AS this_week,
((this_week - last_week) / last_week::float) * 100 as pct_change
FROM orders
GROUP BY channel;
The Result:
📱 App: -3% (Normal fluctuation)
🏪 Offline: -2% (Normal fluctuation)
💻 Website: -50% (🚨 THE BLEEDING IS HERE 🚨)
Website orders got cut in half. Everything else is fine. Now you know exactly where to look.
Level 3: Why did website orders crash?
This is where you stop being just a "data person" and start being an analyst. You need to form hypotheses.
Technical: Did the site go down? Did the checkout button break?
Marketing: Did we turn off ads? Did a landing page change?
External: Did a competitor run a massive sale?
You check the web traffic data:
SELECT
DATE(visit_date) as day,
COUNT(*) as total_visits,
ROUND(100.0 * purchases / total_visits, 2) as conversion_rate
FROM website_sessions
WHERE visit_date >= '2026-02-02'
ORDER BY day;
The Find:
Conversion Rate: Stable at 2.5% all week. (So the website isn't broken).
Traffic: Dropped 45% starting specifically on Tuesday.
The Conclusion: The site works fine. The people just stopped coming.
You Ping the Marketing Manager: "Hey, did we change anything with website traffic acquisition on Tuesday?" Reply: "Oh yeah, we paused the Google Ads 'Brand Campaign' due to a budget reallocation."
What you tell your manager at 3 PM
Do not send a raw data dump. Do not send 4 different charts. Send this:
Subject: Update on Revenue Drop (-15%)
Summary: Revenue is down 15% WoW. This is entirely driven by a 50% drop in Website Orders. App and Offline channels are stable.
Root Cause: Website traffic dropped 45% starting Tuesday. Conversion rates remain healthy, indicating no technical issues.
The Driver: Marketing paused the 'Google Ads Brand Campaign' on Tuesday.
Impact: This campaign pause cost us approximately ~1,500 orders this week.
Recommendation: If the budget cut is permanent, we need to forecast this lower revenue baseline for next week. If it was a mistake, we need to restart the campaign immediately to recover volume.
That took you 4 hours and 4 queries. But it tells a complete story: What happened, Where, Why, and What to do about it.
Before reading on. A quick shoutout to our sponsor who keeps this newsletter free. It helps us keep the lights on if you check them out. (Continue Reading)
AI in HR? It’s happening now.
Deel's free 2026 trends report cuts through all the hype and lays out what HR teams can really expect in 2026. You’ll learn about the shifts happening now, the skill gaps you can't ignore, and resilience strategies that aren't just buzzwords. Plus you’ll get a practical toolkit that helps you implement it all without another costly and time-consuming transformation project.
The Framework to Remember
Every RCA (Root Cause Analysis) follows the same pattern:
Decompose: Break the metric down (Revenue → Orders × AOV).
Isolate: Find the broken branch. Ignore the healthy ones.
Explain: Find the "event" that caused the change (The Ad Pause).
Recommend: Tell them what to do next.
Practice this right now
Take any dataset you have. Pick two weeks. If there is a difference, drill down. If there isn't, artificially delete 40% of the data from one "Category" and see if you can trace it back using SQL.
The SQL isn't hard. The thinking is.
Next newsletter: The Analyst's Automation Playbook; because if you're running the same report every Monday morning, you're doing it wrong.
— RAJ (Real Analyst Jobs)
P.S. Added new 20+ fresh remote jobs: realanalystjobs.com
Added new free projects : https://realanalystjobs.com/projects
Document your 2026 Journey: https://realanalystjobs.com/journey
Talk to me : https://realanalystjobs.com/raj