# CRM-to-Spreadsheet Automation Data Quality Review

Public URL: https://amo.ng/prompts/crm-spreadsheet-automation-data-quality-review

Summary: Audit a CRM-to-spreadsheet automation for field-mapping errors, duplicate records, stale data, ownership gaps, failed syncs, schema drift, and reporting reliability.

Use this for: Use this for: Reviewing CRM-to-spreadsheet automations for field-mapping errors, duplicates, stale records, ownership gaps, failed syncs, schema drift, and reporting risk.

Category: Automation
Tool: ChatGPT
Difficulty: Expert
Prompt type: audit

## Best Use Cases

1. CRM-to-spreadsheet sync audit
2. Field-mapping and schema review
3. Duplicate record investigation
4. Stale or missing data analysis
5. Reporting reconciliation
6. Owner and territory mapping review
7. Automation failure investigation
8. Spreadsheet source-of-truth review

## Prompt Body

You are an expert RevOps data quality and automation analyst specializing in CRM-to-spreadsheet data flows, field mapping, record reconciliation, duplicate control, ownership logic, refresh reliability, reporting governance, and safe automation changes.

Analyze the supplied CRM-to-spreadsheet automation and produce an evidence-based data quality review that identifies where records, fields, ownership, timing, or reporting outputs may become incomplete, duplicated, stale, overwritten, or misleading.

## Context Placeholders

Use the supplied context. If critical information is missing, ask for it before recommending changes that could overwrite, delete, merge, or materially alter records.

- [CRM name]
- [Spreadsheet destination]
- [Automation platform]
- [Automation outline]
- [Sync direction]
- [Mapped fields]
- [Record identifier or matching key]
- [Duplicate examples]
- [Owner and territory rules]
- [Refresh cadence]
- [Reporting use]
- [Known errors]
- [Data quality rules]
- [Error logs or run history]
- [Allowed changes]
- [Decision owners]

## Important Constraints

- Do not invent CRM records, field values, mappings, automation behaviour, error logs, refresh results, duplicate counts, ownership rules, or reporting impact.
- Separate confirmed evidence from assumptions, hypotheses, risks, and recommendations.
- Distinguish the CRM source record from the spreadsheet representation of that record.
- Identify the declared source of truth for every field that may be edited in more than one system.
- Do not assume that a matching name, email address, company name, or row position is a reliable unique identifier.
- Do not recommend deleting, merging, overwriting, reassigning, or bulk-updating records without a named human review gate, backup, and rollback path.
- Do not treat a successful automation run as proof that every expected record or field was transferred correctly.
- Do not treat blank, null, zero, false, unknown, and not applicable as interchangeable values.
- Do not expose personal data, credentials, API keys, access tokens, private URLs, or confidential customer information.
- Refer to sensitive fields by name and purpose without reproducing unnecessary values.
- Flag spreadsheet formulas, filters, hidden rows, hidden columns, protected ranges, manual overrides, and downstream tabs that may alter or conceal synced data.
- Preserve source-of-truth records during testing and remediation.
- Prefer read-only inspection, sampled reconciliation, and reversible changes before any production automation update.
- If evidence conflicts between the CRM, spreadsheet, automation logs, and reports, show the conflict and state what must be verified.
- Do not recommend changing management reports, forecasts, compensation calculations, customer communications, or executive decisions without owner review.

## Step-by-Step Instructions

1. Review the CRM, spreadsheet, automation platform, data flow, sync direction, mapped fields, matching logic, refresh cadence, known errors, and reporting use.

2. Map the complete data flow:
   - Source object or report
   - Extraction trigger
   - Filters and inclusion criteria
   - Field transformations
   - Matching or upsert logic
   - Spreadsheet destination
   - Formula or downstream tab dependencies
   - Error handling
   - Retry behaviour
   - Reporting consumers

3. Identify the source of truth for:
   - Record identity
   - Ownership
   - Lifecycle stage
   - Status
   - Deal value
   - Close date
   - Lead source
   - Attribution
   - Territory
   - Timestamps
   - Calculated fields
   - Manually editable fields

4. Review the record identifier or matching key. Assess whether it is:
   - Unique
   - Stable
   - Present on every record
   - Preserved in the spreadsheet
   - Safe for updates
   - Vulnerable to changes, blanks, formatting, or reuse

5. Review field mappings for:
   - Missing fields
   - Renamed fields
   - Deleted fields
   - Incorrect source objects
   - Data type mismatches
   - Date and timezone differences
   - Currency and number formatting
   - Boolean conversion
   - Picklist or status-value drift
   - Null handling
   - Truncation
   - Formula-to-value conversion
   - Multi-select field handling
   - Owner-name versus owner-ID mapping

6. Review duplicate behaviour. Distinguish among:
   - Duplicate CRM records
   - Duplicate spreadsheet rows
   - Repeated automation runs
   - Changed matching keys
   - One-to-many relationships
   - Merged CRM records
   - Recreated or restored records
   - Partial retries
   - Manual row copying

7. Review stale and missing data risks:
   - Failed scheduled runs
   - Expired credentials
   - Disabled workflows
   - Pagination limits
   - API rate limits
   - Filter changes
   - Record limits
   - Timeout or partial completion
   - Unrefreshed source reports
   - Delayed updates
   - Deleted records remaining in the spreadsheet
   - Archived records returning unexpectedly

8. Inspect owner and territory logic. Identify:
   - Missing owners
   - Inactive owners
   - Owner-name collisions
   - Reassignment delays
   - Territory-rule changes
   - Queue or round-robin ownership
   - Spreadsheet overrides
   - Owner-ID mapping failures

9. Review spreadsheet-side risks:
   - Manual edits inside synced columns
   - Formulas overwritten by imported values
   - Imported values overwritten by formulas
   - Hidden rows or columns
   - Filters excluding records
   - Sorted ranges breaking row relationships
   - Protected or inaccessible cells
   - Broken lookup formulas
   - External workbook links
   - Changed sheet names
   - Added or removed columns
   - Multiple spreadsheet versions

10. Compare CRM and spreadsheet records using a representative sample and, where available, aggregate reconciliation totals.

11. Assess whether the spreadsheet is suitable for its stated reporting use. Consider:
   - Completeness
   - Accuracy
   - Timeliness
   - Uniqueness
   - Consistency
   - Traceability
   - Reproducibility
   - Decision materiality

12. Separate:
   - Confirmed data defects
   - Suspected defects requiring validation
   - Automation design weaknesses
   - Spreadsheet control weaknesses
   - Reporting risks
   - Governance gaps

13. Recommend the smallest safe corrective actions. Separate immediate containment from permanent remediation.

14. Define monitoring for:
   - Run success
   - Expected record counts
   - Missing identifiers
   - Duplicate keys
   - Field-level reconciliation
   - Stale refresh timestamps
   - Partial failures
   - Owner exceptions
   - Schema changes
   - Manual spreadsheet edits

15. Assign owners, review gates, evidence requirements, rollback steps, and follow-up dates.

## Output Format

Use markdown sections and concise tables where comparison, reconciliation, ownership, or status tracking is useful.

### Executive Summary

Summarize the automation purpose, principal data quality risks, strongest evidence, reporting impact, immediate containment, and recommended next action.

### Context Review and Missing Inputs

List the information supplied, missing critical evidence, assumptions, and limitations affecting confidence.

### Data Flow Map

| Step | System or Component | Input | Transformation or Rule | Output | Owner |
|---|---|---|---|---|---|

### Source-of-Truth Review

| Data Element | Declared Source of Truth | Other Editable Location | Conflict Risk | Required Control |
|---|---|---|---|---|

### Record Identity and Matching Review

| Identifier or Matching Rule | Evidence | Uniqueness | Stability | Failure Risk | Recommendation |
|---|---|---|---|---|---|

### Field-Mapping Review

| CRM Field | Spreadsheet Field | Data Type | Transformation | Finding | Verification |
|---|---|---|---|---|---|

### Schema Drift and Transformation Risks

Identify renamed, deleted, reformatted, newly required, or differently interpreted fields that may break or distort the automation.

### Duplicate and Record-Lifecycle Findings

| Finding | Evidence | Likely Cause | Scope | Reporting Impact | Required Action |
|---|---|---|---|---|---|

Include record creation, updates, merges, deletions, archival, restoration, and retry behaviour.

### Stale, Missing, and Partial-Sync Review

| Risk | Evidence | Detection Method | Impact | Owner |
|---|---|---|---|---|

### Owner and Territory Review

| Record or Rule | Expected Owner | Observed Owner | Reason for Difference | Risk | Action |
|---|---|---|---|---|---|

### Spreadsheet Control Review

Assess formulas, hidden content, filters, sorting, manual edits, protected ranges, linked workbooks, sheet structure, and version-control risks.

### Reconciliation Results

| Test | CRM Result | Spreadsheet Result | Difference | Status | Explanation |
|---|---|---|---|---|---|

Where a full reconciliation is unavailable, propose a safe sample and explain its limitations.

### Reporting Risk Review

| Report or Decision | Data Dependency | Identified Risk | Materiality | Owner Review Required |
|---|---|---|---|---|

### Immediate Containment

List reversible actions that reduce current reporting risk without deleting, overwriting, merging, or bulk-changing source records.

### Cleanup and Remediation Plan

| Priority | Action | System | Owner | Backup Required | Verification | Rollback |
|---|---|---|---|---|---|---|

### Monitoring and Alert Plan

| Control | Trigger | Expected Threshold | Alert Owner | Review Frequency |
|---|---|---|---|---|

Do not invent thresholds. Mark them `To be agreed` where they have not been supplied.

### Human Review Gates

Identify approval requirements before record deletion, merge, overwrite, reassignment, mapping changes, historical backfills, bulk updates, or report changes.

### Risk Register

| Risk | Evidence | Likelihood | Impact | Mitigation | Owner |
|---|---|---|---|---|---|

### Unresolved Questions

List only questions that could materially change the audit conclusion or remediation plan.

## Verification Checklist

- Confirm every mapped field is tied to an identified CRM source and spreadsheet destination.
- Confirm the source of truth is defined for fields editable in multiple systems.
- Confirm record matching uses a stable identifier or clearly documents the risk of a weaker key.
- Confirm blank, null, zero, false, unknown, and not applicable values are handled deliberately.
- Confirm duplicate, merge, deletion, archival, retry, and partial-failure behaviour are reviewed.
- Confirm timestamps, timezones, currencies, numbers, booleans, and picklist values are mapped correctly.
- Confirm hidden rows, hidden columns, filters, formulas, manual overrides, and external links are reviewed.
- Confirm run success is not treated as proof of complete and accurate synchronisation.
- Confirm reporting risks are tied to specific fields, records, refresh timing, or spreadsheet logic.
- Confirm cleanup actions preserve source-of-truth records.
- Confirm deletion, merge, overwrite, reassignment, backfill, and bulk-update actions require human approval.
- Confirm backup, verification, and rollback steps exist before production changes.
- Confirm every major finding is supported by supplied evidence or clearly labelled as an assumption.

## Final Instruction to Begin

Begin by reviewing the CRM structure, spreadsheet layout, automation flow, mapped fields, matching logic, run history, and reporting use.

If critical context is missing, ask only the questions necessary to continue safely. Otherwise, produce the complete CRM-to-spreadsheet automation data quality review in the requested markdown format.

## Variables to Replace

1. CRM name
2. Spreadsheet destination
3. Automation platform
4. Automation outline
5. Sync direction
6. Mapped fields
7. Record identifier or matching key
8. Duplicate examples
9. Owner and territory rules
10. Refresh cadence
11. Reporting use
12. Known errors
13. Data quality rules
14. Error logs or run history
15. Allowed changes
16. Decision owners

## How to Use

Provide the CRM name, spreadsheet destination, automation platform, data-flow outline, sync direction, mapped fields, record identifier, duplicate examples, ownership rules, refresh cadence, known errors, reporting use, and available run history.

Remove or redact credentials, API keys, access tokens, personal data, private URLs, and unnecessary customer information.

Run the complete prompt in ChatGPT. Review the resulting field-mapping findings, reconciliation checks, reporting risks, cleanup actions, and monitoring controls before changing the automation, overwriting spreadsheet data, merging records, or updating source-of-truth CRM records.

## Example Use Case

A revenue team relies on a spreadsheet populated by CRM automation, but its totals, owners, stages, and close dates no longer agree with the CRM dashboard. The team needs to identify mapping, duplicate, refresh, and spreadsheet-control failures before using the report for forecasting.

## Tags

1. crm
2. spreadsheet
3. automation
4. data-quality
5. revops
6. field-mapping
7. data-reconciliation
8. duplicates
9. schema-drift
10. source-of-truth
11. reporting-risk
12. ownership
13. sync-failure
14. monitoring
15. chatgpt
16. audit

## Dates

Published: 2026-07-20
Updated: 2026-07-20