Comm-Log Send Reconciliation

Reproducing Finance's target_base for merchant 501, October 2026, all Diwali campaigns from the raw communication_log, and explaining the gap to a straightforward query.

Naive COUNT(*) of sends	30
Reproduced target_base	22 — matches Finance
Gap	8 sends: 4 from an unapproved campaign, 4 retry attempts of customers already counted

python reconcile.py                               # bridge + checks + final number, exits non-zero on mismatch
sqlite3 data/comm_log.db < sql/target_base.sql    # final number only
python -m unittest discover tests                 # edge cases the provided data doesn't cover

Everything uses only the Python standard library (3.9+). data/ holds the provided dataset, unchanged.

---

1. Reconciliation bridge

Each step is a standalone query in sql/bridge/ (step 4 is the final query, sql/target_base.sql), listed in the order I found I needed it.

Step	Description	Result	Δ	Reason
0	Naive COUNT(*) of sends: merchant 501, Oct 2026, type '2', Diwali campaigns	30		Starting point: one communication_log row per send attempt.
1	Keep only campaigns cleared for reporting (creation_status finalized and processing_status = 'processed')	26	−4	Campaign 9004 is approval_awaiting; its 4 sends ran ahead of sign-off and don't count.
2	Collapse retries: COUNT(DISTINCT customer_id) per COALESCE(parent_id, id)	22	−4	A retry re-attempts the same communication, so C2 and C3 (9002) and D1 (9202) shouldn't count again. This matched Finance, but a per-group breakdown showed two errors cancelling out — see steps 3 and 4.
3	Resolve every campaign to the root of its retry chain at any depth (recursive CTE)	21	−1	9003's parent is 9002, which is itself a retry of 9001. One level of parent_id left C3's third attempt in a phantom communication "9002".
4	Standalone campaigns (no parent, no retries) count every send, not distinct customers	22	+1	C20 got two 9101 sends, on Oct 10 and Oct 20, both delivered. That's a re-target, not a retry, and step 2 had wrongly merged them.
	Final target_base	22

Where the 8 sends went

Sends removed	Campaign → customers	Why it's not a qualifying send
4	9004 → C11, C12, C13, C14	Campaign not cleared for reporting (approval_awaiting)
2	9002 → C2, C3	Retry of 9001; both customers already counted
1	9003 → C3	Second retry (9003 → 9002 → 9001); C3 already counted
1	9202 → D1	Retry of 9201; D1 already counted
8		30 − 8 = 22

By underlying communication

flowchart LR
    %% Mermaid stacks unconnected groups bottom-up, so they are declared in reverse
    subgraph D["Wave 2: 5"]
        direction LR
        c9201["9201 Wave 2<br/>D1–D5"] --> c9202["9202 Retry<br/>D1"]
    end
    subgraph B["Flash Sale: 7"]
        direction LR
        c9101["9101 Standalone<br/>C20 ×2, C21–C25"]
    end
    subgraph A["Cart Recovery: 10"]
        direction LR
        c9001["9001 Wave 1<br/>C1–C10"] --> c9002["9002 Retry A<br/>C2, C3"] --> c9003["9003 Retry B<br/>C3"]
        c9001 -.-> c9004["9004 Retry C<br/>approval_awaiting<br/>C11–C14 · excluded"]
    end

Loading

Communication (root)	Campaigns counted	Kind	Cleared sends	target_base
9001 Diwali Cart Recovery	9001, 9002, 9003 (9004 excluded)	retry chain → distinct customers	13	10
9101 Diwali Flash Sale	9101	standalone → every send	7	7
9201 Diwali Wave 2	9201, 9202	retry chain → distinct customers	6	5
Total			26	22

---

2. Final query

sql/target_base.sql, runnable as-is against data/comm_log.db:

WITH RECURSIVE
chain (campaign_id, root_id) AS (
    SELECT id, id FROM campaign WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, ch.root_id
    FROM campaign AS c
    JOIN chain AS ch ON c.parent_id = ch.campaign_id
),
communication AS (
    SELECT ch.root_id,
           root.name AS root_name,
           COUNT(*) > 1 AS has_retries
    FROM chain AS ch
    JOIN campaign AS root ON root.id = ch.root_id
    GROUP BY ch.root_id, root.name
),
qualifying_send AS (
    SELECT l.id, l.customer_id, cm.root_id, cm.has_retries
    FROM communication_log AS l
    JOIN campaign AS c ON c.id = l.communication_id AND c.merchant_id = l.merchant_id
    JOIN chain AS ch ON ch.campaign_id = c.id
    JOIN communication AS cm ON cm.root_id = ch.root_id
    WHERE l.merchant_id = 501
      AND l.communication_type = '2'
      AND l.sent_time >= '2026-10-01' AND l.sent_time < '2026-11-01'
      AND cm.root_name LIKE '%Diwali%'
      AND c.creation_status IN ('approved', 'aborted', 'resumed', 'stopped')
      AND c.processing_status = 'processed'
)
SELECT SUM(units) AS target_base
FROM (
    SELECT CASE WHEN has_retries THEN COUNT(DISTINCT customer_id) ELSE COUNT(*) END AS units
    FROM qualifying_send
    GROUP BY root_id, has_retries
);
-- target_base = 22

How it works:

1. chain walks parent_id down from every root, so every campaign, however deep, maps to the original communication it retries.
2. communication marks a root as a retry chain if any campaign points at it. Otherwise it's standalone.
3. qualifying_send applies the scope (merchant, month, type, Diwali) and the reporting gate. The gate is applied per campaign after chains are resolved, so an unapproved retry in the middle of a chain can't split its children off into a separate communication.
4. The final SELECT counts distinct customers per retry chain and every send for standalone campaigns.

---

3. How I investigated

1. Profiled before aggregating (checks/01, checks/02, tests/). First I ruled out plumbing problems: the DB and the CSVs hold identical rows (7 campaigns, 30 sends), there are no orphan campaign ids, no merchant mismatches, and nothing outside the stated scope. That meant the gap had to come from business logic, not bad joins.
2. Naive count → 30. Then I applied the README's reporting gate, which removes 9004 → 26.
3. Collapsed retries with the obvious one-level query → 22. It matched Finance, but a one-level parent_id lookup can't be right for chains the README says can be deeper than two levels. So I broke the result down by group. It showed a group keyed on 9002 (itself a retry) holding C3, and campaign 9101 counted as 6 even though it has 7 sends. That's two errors, +1 and −1, cancelling out.
4. Fixed chain depth with a recursive CTE → 21. C3 now counts once, under 9001.
5. Applied the standalone rule → 22. 9101 has no parent and no retries, and C20's two sends are 10 days apart and both delivered, which is a genuine re-target. Every send counts.
6. Verified independently (checks/03). A row-level audit labels all 30 sends as counted or excluded, with a reason for each, and sums to 22. reconcile.py fails unless the bridge, the final query and the audit all agree.
7. Stress-tested the definition (checks/04). I checked which other plausible queries also return 22, and whether the answer depends on how "reached" is read.
8. Tested cases the data doesn't contain (tests/). These include an unapproved middle retry, a new customer reached only by a deep retry, a standalone re-send, out-of-month and non-Diwali sends, and each finalized or unprocessed status. Each test checks the final query moves exactly as the definition says it should.

Checks that did not change the number

Check	Finding
comm_log.db vs the CSVs	Identical: 7 campaign rows, 30 send rows
Scope filters (merchant 501, type '2', October 2026, "Diwali" in name)	Every row is already in scope, so the filters remove nothing
Referential integrity	No sends without a campaign, no merchant mismatch between a send and its campaign
sent_time vs scheduled_time	Equal on every row, so the choice of time column doesn't matter
Chain integrity	Every campaign resolves to a root (no cycles or dangling parents), and every retry was sent after its parent
Duplicates	No exact duplicate rows. The only repeated (campaign, customer) pair is C20 in 9101, 10 days apart
Retry targeting	No retry went to a customer the parent had already delivered to
Delivery status	Counting only customers who were eventually delivered still gives 22
Diwali filter on the campaign's own name vs its root's name	Same result: all 7 campaign names contain "Diwali"

---

4. What surprised me

22 is easy to reach for the wrong reason. Two plausible queries both return exactly 22. One counts only delivered sends from approved campaigns. The other deduplicates customers one level up parent_id, and that one is two errors cancelling out: it double-counts C3's third attempt and merges C20's legitimate repeat send. I only trusted the number once a row-level audit of all 30 sends agreed with it. Campaign 9004 is labelled a retry of 9001 but doesn't behave like one. None of its four customers (C11–C14) were ever in 9001's audience, and it actually sent, using 4 billing credits, while still approval_awaiting. So billing (30 credits in total, 26 on approved campaigns) and reporting (22) differ for more than one reason. The data never tests the ambiguous cases in the definition: a customer who is never delivered in a chain (does "reached" require delivery?), a chain that crosses a month boundary, or a repeat send to the same customer inside a retry chain rather than in a standalone campaign.

5. Assumptions and open questions for Finance

• Chain structure comes before the reporting gate. A root counts as a retry chain if any campaign points at it, including an unapproved one, and an unapproved middle link doesn't break the chain. This doesn't change the result here, because 9004 is a leaf and 9001 also has the approved retry 9002.
• The month window applies per send (sent_time). If a chain spans two months, each month counts the customers with an in-window send. This case isn't in the data.
• Delivery isn't required. I read target_base as customers targeted per communication, since failed attempts are what create retries. In this data the delivered-only reading gives the same 22, but Finance should confirm it for other periods.
• Diwali scope is matched on the chain root's name, so a renamed retry still belongs to its Diwali communication.

---

Repository layout

.
├── reconcile.py                  runs everything below; exits 1 if the numbers disagree
├── data/                         provided dataset (comm_log.db + equivalent CSVs)
├── tests/test_target_base.py     CSV/DB parity and edge cases for the final query
└── sql/
    ├── target_base.sql           final query (bridge step 4) -> 22
    ├── bridge/
    │   ├── step_0_naive_count.sql
    │   ├── step_1_cleared_campaigns.sql
    │   ├── step_2_collapse_retries_one_level.sql
    │   └── step_3_resolve_full_chain.sql
    └── checks/
        ├── 01_campaign_profile.sql        per-campaign sends, customers, delivery, credits
        ├── 02_data_checks.sql             scope, integrity and chain-structure checks
        ├── 03_row_level_audit.sql         all 30 sends labelled counted/excluded with reason
        └── 04_alternative_definitions.sql coincidental 22s and sensitivity of the definition