Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / internal/modules/recruiting/analytics_desk.go |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 720d47fae476b0d38477ae4401697d372b1ea2bc |
| Branch | feature/opportunity-to-outcome |
| Path or archive member | internal/modules/recruiting/analytics_desk.go |
| Original SHA-256 | 2c91e671a886bea446c420bfa1d0f7aae76228cdb67ff4472e6de411a449d7d7 |
| Git blob SHA | adc1aa66bbcd0fa3bea50eba763586082a252b43 |
| Reading-copy SHA-256 | 2c91e671a886bea446c420bfa1d0f7aae76228cdb67ff4472e6de411a449d7d7 |
Recovered source transcript
Download complete source text ↓162 lines · 7.0 KB
package recruiting
import (
"context"
"fmt"
"github.com/jackc/pgx/v5"
"github.com/shopspring/decimal"
)
// DESK PERFORMANCE: what a recruiter PRODUCED, as distinct from what they DID.
//
// The analytics report already carries time to offer, time to start, worked time to start, fill rate, aging,
// the funnel, stage to stage conversion, source effectiveness, and per recruiter effort. Effort is calls
// logged and connect rate: it measures activity, which is honest and is not the same question as outcome. A
// desk that logs two hundred calls and places nobody and a desk that logs forty and places four look the same
// in an effort report.
//
// Three things were genuinely missing from the industry standard set, and all three are here:
// placements per recruiter, the outcome half of the effort report;
// the REDEPLOYMENT RATE, which nothing could compute until the redeployment arm existed;
// and MARGIN, which nothing could compute honestly until the wrap rate landed in migration 0098.
// DeskOutcome is one recruiter's production over the window. Submissions and placements are counted from the
// stage event history rather than the current stage, so a candidate who was submitted and later rejected
// still counts as a submission: the work happened.
type DeskOutcome struct {
UserID string `json:"user_id,omitempty"`
Name string `json:"name"`
Submissions int `json:"submissions"`
Placements int `json:"placements"`
// PlacementRate is placements as a share of submissions, absent rather than zero when nothing was
// submitted, because a zero rate on no attempts claims a failure that did not happen. This is the same
// rule RecruiterEffort already applies to its connect rate.
PlacementRate string `json:"placement_rate,omitempty"`
}
// RedeploymentRate is the share of contractors finishing an assignment in the window who were placed again.
//
// IT IS THE ONE STAFFING METRIC THAT MEASURES WHETHER A FIRM KEEPS WHAT IT ALREADY WON. A cleared contractor
// rolling off is the cheapest placement available: no sourcing, no clearance risk, a client who already knows
// them. Ended is the denominator and Redeployed the numerator.
type RedeploymentRate struct {
Ended int `json:"ended"`
Redeployed int `json:"redeployed"`
Rate string `json:"rate,omitempty"`
// Window names the days allowed between one assignment ending and the next starting, because a rate with
// no window is not comparable to anything, including itself last quarter.
WindowDays int `json:"window_days"`
}
// redeployGraceDays is how long after an assignment ends a new one still counts as a redeployment. Sixty days
// is a contract staffing quarter: long enough that a gap over a holiday or a slow contract action still counts
// as keeping the person, short enough that it is not simply "they came back eventually".
const redeployGraceDays = 60
const deskOutcomesSQL = `
WITH subs AS (
SELECT a.owner_user_id AS uid, count(DISTINCT a.id) AS n
FROM applications a
JOIN application_stage_events e
ON e.organization_id = a.organization_id AND e.application_id = a.id
WHERE e.to_stage = 'submitted' AND e.occurred_at >= $1
GROUP BY a.owner_user_id
), plcs AS (
SELECT a.owner_user_id AS uid, count(DISTINCT p.id) AS n
FROM placements p
JOIN applications a ON a.organization_id = p.organization_id AND a.id = p.application_id
WHERE p.created_at >= $1
GROUP BY a.owner_user_id
)
-- THE KEY SET IS BUILT WITH A UNION, NOT A FULL OUTER JOIN.
--
-- The first version joined the two sides with FULL OUTER JOIN ... ON plcs.uid IS NOT DISTINCT FROM subs.uid,
-- which Postgres refuses outright: "FULL JOIN is only supported with merge-joinable or hash-joinable join
-- conditions". IS NOT DISTINCT FROM is neither. The whole analytics endpoint answered 500, and every Go test
-- stayed green because none of them touches a database. Only running the screen found it.
--
-- IS NOT DISTINCT FROM is still needed, because a NULL owner is a real group: work nobody owns is reported as
-- Unassigned rather than dropped. It is legal on a LEFT JOIN, which plans as a nested loop, and UNION already
-- treats two NULLs as the same key, so the key set collapses them correctly.
, keys AS (
SELECT uid FROM subs
UNION
SELECT uid FROM plcs
)
SELECT
coalesce(u.id::text, '') AS user_id,
coalesce(u.full_name, 'Unassigned') AS name,
coalesce(subs.n, 0)::int AS submissions,
coalesce(plcs.n, 0)::int AS placements
FROM keys
LEFT JOIN subs ON subs.uid IS NOT DISTINCT FROM keys.uid
LEFT JOIN plcs ON plcs.uid IS NOT DISTINCT FROM keys.uid
LEFT JOIN users u ON u.id = keys.uid
ORDER BY placements DESC, submissions DESC, name ASC`
// redeploymentRateSQL counts contractors whose assignment ENDED inside the window, and how many of them
// started another one within the grace period.
//
// A person is counted ONCE however many assignments they ended, because the question is about people kept,
// not rows closed. $1 is the window start, $2 the grace period in days.
const redeploymentRateSQL = `
WITH ended AS (
SELECT DISTINCT a.candidate_id AS cid, max(p.end_date) AS last_end
FROM placements p
JOIN applications a ON a.organization_id = p.organization_id AND a.id = p.application_id
WHERE p.status IN ('completed', 'terminated')
AND p.end_date IS NOT NULL
AND p.end_date >= $1
GROUP BY a.candidate_id
)
SELECT
count(*)::int AS ended,
count(*) FILTER (WHERE EXISTS (
SELECT 1
FROM placements nxt
JOIN applications na ON na.organization_id = nxt.organization_id AND na.id = nxt.application_id
WHERE na.candidate_id = ended.cid
AND nxt.start_date >= ended.last_end
AND nxt.start_date <= ended.last_end + make_interval(days => $2)
))::int AS redeployed
FROM ended`
func (s *Service) readDeskOutcomes(ctx context.Context, tx pgx.Tx, since any) ([]DeskOutcome, error) {
rows, err := tx.Query(ctx, deskOutcomesSQL, since)
if err != nil {
return nil, fmt.Errorf("recruiting: desk outcomes: %w", err)
}
defer rows.Close()
out := make([]DeskOutcome, 0, 16)
for rows.Next() {
var d DeskOutcome
if e := rows.Scan(&d.UserID, &d.Name, &d.Submissions, &d.Placements); e != nil {
return nil, fmt.Errorf("recruiting: scan desk outcome: %w", e)
}
if d.Submissions > 0 {
r := decimal.NewFromInt(int64(d.Placements)).
Div(decimal.NewFromInt(int64(d.Submissions))).
Mul(decimal.NewFromInt(100)).Round(2)
d.PlacementRate = r.String()
}
out = append(out, d)
}
return out, rows.Err()
}
func (s *Service) readRedeploymentRate(ctx context.Context, tx pgx.Tx, since any) (RedeploymentRate, error) {
var r RedeploymentRate
r.WindowDays = redeployGraceDays
if err := tx.QueryRow(ctx, redeploymentRateSQL, since, redeployGraceDays).
Scan(&r.Ended, &r.Redeployed); err != nil {
return r, fmt.Errorf("recruiting: redeployment rate: %w", err)
}
if r.Ended > 0 {
r.Rate = decimal.NewFromInt(int64(r.Redeployed)).
Div(decimal.NewFromInt(int64(r.Ended))).
Mul(decimal.NewFromInt(100)).Round(2).String()
}
return r, nil
}