A Data-Driven Case Study on SaaS Sales Conversion

It’s been a while since I document an interview case study breakdown, so here we go. This is a fresh RevOps case study I recently worked on in a SaaS company interview — I’ll keep the name anonymous.

Intro

This case study was way more scoped-down comparing to the DoorDash Case I posted about last year. The problem was more defined and not much wide-open strategy space to be “creative”.

The ask was pretty clear and typical: extracting insights using SQL and presenting them in a clean, digestible format. Clear decks and reports go a long way — especially in remote-first teams.

More efficient async work = less meetings, who wouldn’t love that?

Even though the case wasn’t super complex, it’s worth sharing — mainly because it touches on a classic SaaS Sales/RevOps challenge. Plus it got me through to the next rounds, so yeah, did its job.

Case Prompts &Data

Table 1: Opportunity_Monthly_Snapshot

Table 2: Account_Monthly_Snapshot

Table 3: Opportunity

Table 4: Account

My Approach

With the prompt and datasets, let me walk through how I approached it — starting with a few things I needed to do:

1. Run a variance analysis on conversion rate
2. Show insights
3. Propose next steps/other considerations (This wasn’t directly asked in the prompt, but it felt like the natural next step — otherwise the analysis would’ve felt incomplete.)

Step 1: define and extract conversion rates

The conversion rate wasn’t directly available in the dataset, so I first defined it, then ran the analysis for the overall pipeline and new opportunities respectively.

SQL query I used to extract four additional columns:

WITH ranked_opp AS (
SELECTo.*,(
SELECT COUNT(*)
FROM Opportunity o2
WHERE o2.account_id = o.account_id
AND o2.created_date < o.created_date
) AS opp_rank
FROM Opportunity o
)SELECT
o.opportunity_id AS opportunity_id,
o.account_id AS account_id,
o.stage_1_date AS stage_1_date,
CASE WHEN o.opp_rank = 0 THEN 1 ELSE 0 END AS new_opportunity,
CASE WHEN o.stage_2_date IS NOT NULL THEN 1 ELSE 0 END AS stage12_conversion,

-- stage1_quarter
strftime('%Y', o.created_date) || ' Q' ||
((CAST(strftime('%m', o.created_date) AS INTEGER) - 1) / 3 + 1) AS stage1_quarter,

-- stage12_conversion_quarter
CASE
WHEN o.stage_2_date IS NOT NULL THEN
strftime('%Y', o.stage_2_date) || ' Q' ||
((CAST(strftime('%m', o.stage_2_date) AS INTEGER) - 1) / 3 + 1)
ELSE NULL
END AS stage12_conversion_quarter,

o.amount AS opportunity_amount,
o.owner AS opportunity_owner,
o.type AS opportunity_type,
o.source AS opportunity_source,
o.product_line AS opportunity_product_line,
a.company_name AS account_company_name,
a.segment AS account_segment,
a.region AS account_region,
a.industry AS account_industry,
a.owner AS account_owner

FROM ranked_opp o
JOIN Account a ON o.account_id = a.account_id;

Step 2: answer the business question

Now with the cleaned table in hand, I broke it down into three parts:

1. Show the data — kept it simple, intuitive, and visual (think tables with key metrics highlighted).
2. Answer the main question — has the conversion improved for new opps and for the overall pipeline?
3. Propose next steps + flag risks — tied my recommendations back to broader business goals and called out realistic levers the team could pull.

Final Output

Outro

If you’re prepping for RevOps or similar data-driven challenges, you’ll probably see some variation of this case — SQL + conversion logic + clear slides. Don’t overcomplicate it. Focus on clarity, business relevance, and show that you can go from numbers to next steps.

And of course, thanks for reading — As always, happy to chat if you’re working through something similar.