Weight the pipeline, and check last month's call

The situation

You already have somewhere to put numbers. What you do not have is a number to point it at. Somebody asks for the forecast every month, and the answer has to survive being questioned: computed over every deal rather than a page of them, split the way the question was actually asked, and comparable with the answer you gave last time.

This recipe is deliberately literal. It shows the call, the output it returned, and the two refusals you will meet if you ask for more than the database will serve quickly.

What must be true when you are done

• One call returns the weighted pipeline, by the person who owns the deal and the month it is expected to close.
• You know which deals are not in that number, and why they are not.
• Last month's number is written down somewhere you control, so this month you can find out whether it was right.

The call

Weighted pipeline is the deal amount times the probability standing on that deal. Both are stored on the record, so the database multiplies and adds them and hands back the total. There is no export step and nothing to reconcile afterwards.

GET /v1/aggregate
  ?objectType=opportunity
  &function=sum
  &field=weighted_amount_minor
  &groupBy=owner_actor_id
  &groupBy=expected_close_date
  &bucket=month

Two groupings and a bucket. The bucket is what turns a close date into something worth grouping by: without it every date is its own group, which is a list rather than a report. Ask for quarter instead and the quarters follow your financial year if you have told the workspace when yours starts.

Same call without the two groupings and you get the one number: 414,250 across the same eight deals, against 793,000 unweighted. If you want it by stage instead, swap the groupings for stage_id and the labels come back as your stage names rather than identifiers.

Read the last row before you send the number anywhere

It says null, not zero, and that is the most useful thing on the page. One deal has no probability on it, so it has no weighted amount: reporting zero would state that somebody had priced it at nothing, which is a different claim and a wrong one. A group whose deals all lack a probability reports null for the whole group, which is why Tomas has a number for September and not for October.

Now look at matched 8 beside a total of 414,250. Matched counts the deals the question reached. The sum only adds the ones that have a number to add. Read the total as covering all eight and you are quietly short by however much that deal was worth, with nothing on the page to tell you. So ask the second question before you circulate the first answer.

GET /v1/aggregate
  ?objectType=opportunity
  &function=count
  &filter=weighted_amount_minor:isNull

One deal to go and ask somebody about. That is a five second call and it is the difference between a forecast and a forecast you can defend.

What this number is not

It is not a prediction, and nothing here is trying to be one. The probability is whatever a person put on the deal. Nothing copies a stage's suggested probability onto a deal on your behalf, so a deal nobody has priced stays out of the weighted total until somebody prices it.

That is the design, not an omission. A forecast that quietly fills in the blanks is the one that falls apart when somebody asks which deals it is made of. This one you can take apart in front of the person questioning it, because every number in it is a number a named person asserted.

Ask for too much and it refuses

The grouped answer is capped, and the cap is a refusal rather than a truncation.

Owner by month over a couple of quarters is comfortably inside it. Owner by day over a year is not, and neither is anything crossed with a field that has a different value on nearly every record. Narrow with a close date filter and ask again.

The other refusal you will meet is asking to group by the weighted amount, which is a measure rather than a dimension. The answer names the fields you can group by, so an agent fixes its own call in one turn instead of guessing.

Keep the number, or you can never check it

Forecast accuracy is not a harder query. It is the same query, asked on a schedule, with the answers kept. Nothing else can tell you whether the number you gave in September described what happened in October.

You do not need us to hold those snapshots, and you should not want us to: they belong in the same place as the rest of your reporting. What you need from here is a feed you can poll exactly once, which is what the change feed is.

GET /v1/changes?since=mut_01M0XX100Y51N1Q0DCWQCBR5F5

Ask for everything since an entry you have already seen and you get the entries after it, oldest first, and the id of the last one. Save that id. Next time, send it back. A poller that does this cannot miss an entry that arrived while it was working, and cannot process one twice, which is the whole reason to read the feed forwards rather than reading the top of it.

Every stage move records the stage before and the stage after, who moved it, and when. So the history of a deal is already there: what you are adding is the number you reported, on the day you reported it.

1. Nightly, run the grouped call above and write its rows into your own table with today's date against them.
2. Then run the change feed from the id you saved last night, write what it returns, and save the new last id.
3. At the end of the month, compare the row you wrote on the first against what actually closed. That difference is your forecast accuracy, per person, and it is computed from numbers nobody edited after the fact.

The mechanics of running that on a timer and keeping a table in step are a recipe of their own: see keep a spreadsheet or a warehouse in step.

One bound worth knowing before you build the job. How far back the feed reaches depends on your plan: the free workspace keeps ninety days of history, and the paid plans are not trimmed. Every answer states the oldest entry it still holds, so the job can check rather than assume.

Verify it

• Add the grouped rows up by hand once and compare them with the single ungrouped number. If they disagree, something is filtered that you did not mean to filter.
• Count the deals with no weighted amount before anything leaves the building. A total that silently omits your largest unpriced deal is the failure this recipe exists to prevent.
• Call the feed twice with the same id. You should get the same entries both times. A watermark that appears to move on its own is a job that will eventually skip something and never tell you.

Run it again

Nightly for the snapshot, monthly for the comparison. The first month of accuracy data tells you almost nothing. By the third you will know whose numbers to trust, which is a more useful thing to know than the forecast.