Sales executive to sales analyst: rebuild your pipeline funnel

The plan below assumes you are still in your job and can give it roughly an hour on weekdays and a longer block at weekends. Adjust the pace; keep the order.

Days 1–30: get your own pipeline into a clean table

You know your CRM from the user side: lead source, stage, expected close date, the notes you add after every call, the target-versus-achievement sheet your manager sends each Monday. The first month is about seeing the same information as data.

Week 1. Design the table. Write down the columns a pipeline analysis needs: opportunity ID, owner, lead source, created date, current stage, the date each stage was reached, deal value, won or lost, lost reason. Notice which of these your CRM actually captures reliably. Stage dates are often missing because reps update stages in bulk on Friday evening; you know this from experience, and it matters later.

Week 2. Build a synthetic dataset. Create 60 to 100 invented opportunities in a spreadsheet that behave the way your territory does. Never export real customer or deal data from your employer's CRM.

Weeks 3–4. Compute the funnel in Excel. With stages in columns B to F (1 if reached, 0 if not), the core formulas are:

Opportunities reaching each stage:   =COUNTIFS(C:C,1)
Stage-to-stage conversion:           =COUNTIFS(D:D,1)/COUNTIFS(C:C,1)
Win rate by lead source (source in H, won flag in F):
                                     =COUNTIFS(H:H,"Referral",F:F,1)/COUNTIFS(H:H,"Referral")
Median days from demo to proposal (helper column K holds the day difference):
                                     =MEDIAN(K:K)

By day 30 you should be able to answer: at which stage does the pipeline lose the most opportunities, and does that differ by lead source?

Days 31–60: move the same analysis into SQL

Excel breaks once data has one row per stage change rather than one row per deal. Real CRMs usually store history that way, so SQL is the next step. Here is a stage-history version on synthetic data:

CREATE TABLE stage_log (opp_id INTEGER, source TEXT, stage TEXT, reached_on TEXT);

INSERT INTO stage_log VALUES
 (1,'Referral','Lead','2026-07-01'),(1,'Referral','Demo','2026-07-04'),
 (1,'Referral','Proposal','2026-07-10'),(1,'Referral','Won','2026-07-20'),
 (2,'Website','Lead','2026-07-02'),(2,'Website','Demo','2026-07-12'),
 (3,'Website','Lead','2026-07-03'),
 (4,'Referral','Lead','2026-07-05'),(4,'Referral','Demo','2026-07-07'),
 (4,'Referral','Proposal','2026-07-15'),
 (5,'Website','Lead','2026-07-06'),(5,'Website','Demo','2026-07-09'),
 (5,'Website','Proposal','2026-07-20'),(5,'Website','Won','2026-08-05'),
 (6,'Website','Lead','2026-07-08'),
 (7,'Referral','Lead','2026-07-09'),(7,'Referral','Demo','2026-07-11');

-- How many opportunities reached each stage, by source
SELECT source,
       SUM(stage = 'Lead')     AS leads,
       SUM(stage = 'Demo')     AS demos,
       SUM(stage = 'Proposal') AS proposals,
       SUM(stage = 'Won')      AS won
FROM stage_log
GROUP BY source;

-- Days from demo to proposal, per opportunity that reached both
SELECT d.opp_id, d.source,
       julianday(p.reached_on) - julianday(d.reached_on) AS demo_to_proposal_days
FROM stage_log d
JOIN stage_log p ON p.opp_id = d.opp_id AND p.stage = 'Proposal'
WHERE d.stage = 'Demo'
ORDER BY demo_to_proposal_days;

On this invented data, referral leads all reach a demo (3 of 3) while website leads reach a demo 2 times out of 4. Both sources convert one opportunity to a win. The self-join finds three opportunities that reached both demo and proposal, taking 6, 8 and 11 days. Those counts are far too small to conclude anything; the point is that you can compute them from stage history.

During this month, also learn LEFT JOIN (to find opportunities stuck at a stage) and one window function, LAG(), to get time between consecutive stages without a self-join.

Days 61–90: build the dashboard and the story

Weeks 9–10. One dashboard page. In Power BI or a similar tool: funnel by stage, conversion by lead source, median days in each stage, and a table of opportunities that have sat in one stage longer than your cut-off.

Week 11. Write the finding for a sales manager. Three sentences: where the funnel leaks, what probably explains it (from your field experience), and what you would test. For example: website leads drop before the demo, possibly because the first call happens days after the enquiry; test calling website leads within the same working day for a month.

Week 12. Prepare your case. Put the SQL, the synthetic data and a one-page note in a public repository. Make clear in the README that the data is invented and why.

The specific gap for someone from the field

Sales executives usually bring target pressure, customer understanding and a sharp sense of when CRM data is fiction. The gaps are consistent:

• Neutrality. As a rep, you argue for your deals. As an analyst, you report the pipeline as it is, including when the story is uncomfortable for the team.
• Data hygiene. Duplicate accounts, stages skipped, close dates pushed monthly. Learn to detect and document them instead of silently fixing them.
• Forecasting basics. Managers will ask what the quarter will close at. Learn the simple weighted-pipeline method first and understand its weaknesses.

Sales operations analyst as the first door

Sales analyst, sales operations analyst and sales MIS analyst are the closest fits, and an internal move within your current company can be the easiest route, because you already know its products and process. Revenue operations analyst is a related title in software companies.

A sample interview answer

"I have been a field sales executive for three years selling to small businesses, so I know how the pipeline really moves, including that stage dates are often updated in bulk. I got interested in why our website leads converted worse than referrals, and I could not answer it from the Monday target sheet. So I learned Excel funnel formulas and then SQL, and built a pipeline analysis on synthetic stage-history data that measures conversion and time between stages by lead source. I also documented the data problems I would expect in a real CRM. I want a sales analyst role because I can speak to reps in their language and still report the numbers neutrally."

Useful next steps

Check your current resume with the free skill check. If you want guided practice in SQL, Power BI and verifying AI-assisted analysis, the Data Analyst programme fits this path; ask a counsellor for current batch dates. If you work on the marketing side of the funnel instead, see the digital marketing executive guide.