Forecasting At The Wrong Grain

Original Spike - 6 min read (Edited for clarity and minor redactions. Core structure remains faithful to the original.)

Executive Summary

The Forecast Report can be changed to include candidates once only who currently appear more than once because they are involved in multiple shortlists.

However to do this requires significant structural changes to both the data model and the various forecast calculations.

The effort is justified if forecast accuracy is a priority and will mean the report aligns with customer expectations but it is not a quick fix.

1. Objective

1.1 Background

We currently have a report which displays the forecasted gross profit expected from our vacancies and placements, based on their start date.

The main page has a stacked column chart with month on the x axis. With each stack representing a different type of vacancy or placement.

We also have the flexibility in the report to set the weighting for each stack i.e. the probability that the vacancy / placement will happen based on the stage that the candidate has been shortlisted at. The report user can apply a percentage from -100% to 100% based on the effective likelihood of receiving this forecasted gross profit.

1.2 The Ask

An external customer has mentioned that the same candidate could be shortlisted for multiple vacancies but can only ever accept one. And the Forecast report doesn't make allowances for this "candidate-vacancy" duplication.

This spike is to explore if it is possible for the Forecast report to be enhanced to only include one vacancy / placement per candidate. And to address the following specifically:

• What would be involved?
• Any further impacts/considerations?

2. Investigation

The main page of the Forecast report consists of a stacked column chart, a donut and 6 headline cards.

2.1 Stacked Column Chart

This stacked chart is made up of up to 8 different stacks, showing the gross profit (GP) associated to vacancies and placements with each column representing a different month. The 8 stacks represent:

1. GP (likely to be) earned from:
   1. Permanent vacancies.
   2. Contract / temporary vacancies.
   3. Placement extensions.
   4. Existing contract / temporary placements.
   5. Existing permanent placements.
2. GP (likely to be) lost from:
   1. Placement extensions unlikely to extend.
   2. Contract / temporary vacancies ending.
   3. Permanent vacancies ending.

2.2 Headline Cards

The headline cards display various aggregations:

• Above the line total (i.e. GP earned)
• Placement above the line total
• Placement below the line total
• Vacancy above the line total
• Vacancy below the line total
• Below the line total (i.e. GP lost)

2.3 Current Problem

This current approach over-forecasts because it counts the same candidate multiple times: once for every vacancy they're linked to. A candidate can only ever accept one vacancy but our report makes no allowances for this real-world behaviour.

We are currently aggregating over vacancy and placement records and we don't take account of the identity of the candidates involved. Indeed the only fact table which includes a reference to candidate is the OpenPlacements table.

In basic terms the fix requires adding CandidateContactKey to all relevant fact tables, however this has other repercussions which makes this unfeasible. The main problem being that there is not a one to one mapping between candidate and vacancy / placement.

So every vacancy / placement could have multiple candidates associated with it. Adding in candidates to the fact tables will increase the number of rows in the tables (since every record relates to multiple candidates i.e. one row in the existing table becomes multiple rows once the CandidateContactKey is added) and will therefore impact measures which aggregate over the table.

For example, a vacancy which appeared once might now appear twice in the table and therefore its gross profit will be double-counted. Meaning any measures aggregating profit from this table will return different values than previously.

An obvious alternative would be to use FctShortlist since this already has the relationship between candidate and vacancy. However it has missing data such as the gross profit value and number of positions which are both stored in FctVacancy.

2.4 The Grain Problem

The fundamental issue is that the forecast is currently built at vacancy grain. But the customer's requirement is at candidate grain. These are incompatible without a significant remodelling effort.

A single vacancy can have multiple candidates and a single candidate can be linked to multiple vacancies. This creates a many-to-many forecasting problem which cannot be resolved simply.

3. Feasibility

Candidate-level de-duplication is feasible. But it requires shifting from vacancy-level to candidate-level aggregation. The work required includes:

• Grouping vacancies by candidate
• Ranking these vacancies per candidate by shortlist stage
• Using tie-breaking logic where one candidate is at the same shortlist stage for multiple vacancies
• Exclude the non-selected vacancies from aggregation ensuring each candidate is linked to at most one vacancy

4. Options And Their Trade-offs

Option	Description	Advantages	Disadvantages
A: Enrich Existing Tables	Add CandidateKey to FctVacancy, FctPlacement etc.	Uses existing structure.	Changes grain. Will impact existing measures potentially across whole report suite.
B: Build New Candidate-Level Fact Tables	Create new tables at candidate-vacancy grain in the warehouse.	Clean design, no impact on existing reports.	More ETL work, new objects in warehouse and semantic model to maintain. Increases size of database and semantic model.
C: Create new candidate level measures	Write new DAX measures in Power BI to replicate the existing measures but at candidate-vacancy grain.	No upstream changes required.	Complex as aggregating across fact tables. Invisible, error-prone and carries performance risk on large datasets.

5. Outstanding Questions

If a candidate has multiple vacancies when duplicating what is the deciding factor:

• The vacancy where the candidate has reached the furthest shortlist stage?
• The vacancy with the highest GP?
• The vacancy with the earliest start date?
• The vacancy with the most-recent associated activity?
• Another factor?

If furthest shortlist stage is the deciding factor, what happens if one candidate has two or more vacancies at this latest stage?

What is the priority of this work against other initiatives? More than likely this will take multiple sprints.

How much desire across the customer base is there for such a candidate forecast report?

6. Recommendation

I would recommend option B.While it requires most up-front work, it avoids the risks to the whole reporting suite of changing grain described in option A.

Option B is more visible and more robust than option C and also doesn't carry the performance risks of that option.

Depending on the direction of travel, option B also has a potential strategic upside of creating a clean foundation on which to build data infrastructure for future candidate-centric reporting. This makes it an infrastructure investment rather than just a fix.

I would also recommend that it should be preceded by a 1-2 week design spike to agree how these changes can be implemented in the data warehouse.

As part of option B, it may be possible to re-purpose or enhance the existing FctShortlist table however this also carries the risk of altering existing reports and measures that are dependent upon the table in its existing state.

7. Conclusion

In conclusion, candidate-level de-duplication is achievable but it's a significant change. It requires:

• Clarification for basis of de-duplication of vacancies per candidate.
• Business rule for a tie-break for candidates with multiple vacancies / placement extensions at the same stage (if stage is used as basis for de-duplication).
• Creation of new candidate fact tables in the data warehouse.
• A full rewrite of all the forecast measures (minimum 18 measures).
• Full or partial rebuild of Forecast report taking a candidate-centric approach rather than a vacancy-centric approach.

This is not a small change but it is achievable over time. And the investment will lead to a forecast report which is both more accurate and also meets the customer's expectation.