Reconcile pay-per-call attribution and contract eligibility

01 / 10

Start with a reconciled population, then apply the contract

Finish this exercise with an explained row for every call and a candidate export that uses the same rules as the summary. The downloadable records are fictional; they do not establish that a real buyer owes a payment.

Keep three questions separate: which source owns the call, which measured facts exist, and which contract predicates those facts satisfy. A termination category such as CALLER or AGENT does not prove that a person answered. A stored disposition is an input to review, not a universal definition of a billable call.

1. Download and extract the complete exercise ZIP into one working directory, or save the individual files below together. Do not import these files into VICIdial.
2. Write down the exact source population and time window before looking at the expected answer.

You should seeYou have nine fictional call rows and nine attribution rows to reconcile; one call is outside the selected window.

Download the complete reconciliation exercise (ZIP) →

Download the complete exercise notes →

Download fictional calls →

Download fictional attribution rows →

Download the local classifier →

02 / 10

Define one call identity and inspect the join

The fixture uses a guaranteed unique call_id. Your installation needs its own reviewed call-leg identity and source mapping; lead_id alone can join several calls by the same lead. Inspect one-to-many and missing matches before summing duration or money.

C004 has two source matches and must be held for review. C005 has none. Neither is silently assigned to the most convenient source. The example keeps both anomalies visible while evaluating the six unambiguous in-window calls.

03 / 10

Step 1 — Record source ownership at the call time

For real inbound work, record the DID/source mapping and its effective period. A dedicated DID per source is one possible attribution design; an agent-selected label or today’s DID assignment is not proof of the historical source.

For this exercise, open attribution-fictional.csv. Confirm C001 maps once to SOURCE_A, C004 maps twice, and C005 is absent. Preserve these defects so the reconciliation can detect them.

1. Check the raw call key is unique. Stop on duplicate base rows before joining.
2. Count attribution matches per call: zero, one or more than one.
3. Keep raw and mapped counts side by side; do not use an inner join that hides unmatched calls.

You should see8 = 6 uniquely attributed + 1 ambiguous + 1 unmatched. Investigate any failure of that equality.

04 / 10

Step 2 — Keep routing evidence separate from attribution arithmetic

If the real task includes configuring inbound service, complete Route a test DID to an in-group before collecting call evidence. Reopen the saved DID destination, confirm the intended in-group and agent membership, then perform the approved inbound functional test in that guide.

A DID configuration row does not prove that a caller reached an agent or that the contract was satisfied. This local worksheet starts from fictional call evidence and performs no carrier or routing change.

You should seeThe routing task has its own observed pass/blocked result; it is not inferred from this spreadsheet.

Route a test DID to an in-group →

05 / 10

Step 3 — Give every qualification input a source

The teaching contract requires at least 60 connected seconds, a reviewed qualification, no duplicate flag, and a completed review flag. SOURCE_A and SOURCE_B have invented USD rates of 12.50 and 18.00. These values teach the calculation; they are not market prices or a standard contract.

connected_seconds is an explicitly supplied fictional measurement. Do not rename VICIdial length_in_sec as connected time without proving how it is measured on your build. The consent_reviewed input is not a legal conclusion. Apply the actual signed contract and relevant review requirements to real records.

06 / 10

Step 4 — Run the same predicate for the summary and export

Run the downloaded classifier from its directory. It reads only the two local CSVs and creates a new output directory. It never connects to VICIdial or creates an invoice.

Open every decision row before accepting the total. C002 is short, C003 is unqualified, C006 is duplicate and C007 lacks the required review; C004 and C005 remain attribution exceptions.

python3 -B reconcile.py

You should seecandidate-billable.csv contains C001 and C008 only. Their amounts are 12.50 and 18.00, totaling 30.50; the summary and export are derived from the same decisions.

07 / 10

Step 5 — Reconcile by identity, not just by grand total

Compare your output with the expected files. A different row set with the same total is still a discrepancy. In real work, exchange only the agreed minimal evidence: stable call reference, source, window, measured duration basis, qualification decision, contract version and dispute reason.

Keep an unfiltered call-log export labelled raw evidence. The optional query below is a read-only inventory; it deliberately does not label its rows billable or its duration connected. Confirm the database timezone and installed schema before using it.

SELECT closecallid, uniqueid, lead_id, campaign_id, status, call_date,       length_in_sec AS logged_length_seconds, term_reasonFROM vicidial_closer_logWHERE call_date >= '2026-11-06 00:00:00'  AND call_date < '2026-11-07 00:00:00'ORDER BY closecallid;

Expected decision for every row →

Expected candidate export →

Expected summary →

08 / 10

Explain different, missing and unknown results

If raw rows do not split into inside/outside-window rows, fix the boundary or timezone. If in-window rows do not split into zero/one/many attribution matches, inspect the join. If the candidate export differs from the summary, stop billing and make both use one decision result.

09 / 10

Stop changes without destroying the evidence

A data discrepancy is not itself authorization to change a live DID or campaign. Have the routing owner use the relevant guide if a source must be paused; record the old destination and the exact authorized change.

For this offline exercise, no server rollback is needed. Keep the inputs, output and contract version together. A corrected mapping is a new reviewed run; retain the old result so a reviewer can explain the change.

You should seeYou can explain all nine input rows and identify exactly which two meet the fictional contract. Real contract acceptance remains a separate review.

10 / 10

Use the same discipline for reports and integrations

Continue with Scope and check a VICIdial User Stats report for metric scope, or VICIdial Call URLs: a durable local receipt lab for a bounded event-receipt exercise. Do not let a successful event delivery or a large call count stand in for the qualification evidence your contract requires.