Reconcile intake form logs with downstream records

Detect dropped records and data mismatches between intake sources and downstream tools. Use this to audit pipelines before accounting discoveries occur.

Prompt

Prompt
Act as an enterprise workflow architect. Analyze the provided intake log export against the downstream database export to identify missing submissions, status mismatches, and pipeline breaks.

Rules:
- Identify every record that exists in the intake form log but is absent downstream.
- Flag partial records where unique identifiers match but key payload fields differ.
- List the most probable root cause for each discrepancy based on patterns (e.g. timeout, formatting drop, webhook failure).

Output format:
### 1. Reconciliation Summary (Total logs, Downstream count, Discrepancy rate)
### 2. Discrepancy Table (Intake ID, Downstream Status, Missing Fields, Probable Cause)
### 3. Immediate Recovery Actions (Step-by-step manual backfill plan)

My log data is below:

Example output

Example for: Jotform intake entries compared against Stripe and Salesforce account records

1. Reconciliation Summary
  • Total intake form submissions: 42
  • Downstream CRM records: 38
  • Discrepancy rate: 9.5% (4 dropped records, 2 payload mismatches)
2. Discrepancy Table
Intake IDDownstream StatusMissing FieldsProbable Cause
INT-1082MissingFull RecordWebhook timeout during payload delivery
INT-1094MissingFull RecordSpecial characters in business name broke JSON payload
INT-1105CreatedBilling Address, Tax IDDownstream validation rule rejected invalid postal code
INT-1118MissingFull RecordConnection token expired during sync run
3. Immediate Recovery Actions
  1. Export intake payloads for INT-1082, INT-1094, and INT-1118 into CSV format.
  2. Sanitize business name strings for INT-1094 to remove unescaped quotes.
  3. Manually import the CSV through the downstream admin console with standard upsert settings.
  4. Update INT-1105 manually with corrected postal code and trigger an account balance recalculation.
  5. Refresh API tokens and verify webhook status codes in the integration settings.
Written by AI from this prompt. Your results will vary.

Paste your raw CSV or tab-separated intake logs and downstream CRM or database exports right after this prompt into ChatGPT, Claude or Gemini. Ask the model to generate SQL query checks to automate this audit weekly.

Make it repeatable

Keep using this prompt without pasting it in every time.

Keep exploring

More in Automation & Workflows