AnalyticsTracking, UTMs and data qualityIntermediateBuilds a checklist

Lead-data cleaning and lead-quality audit

Plans how to clean a lead export (duplicates, formats, junk) and how to check lead quality by source, using only fields you have.

For: Performance marketers, Marketing analysts, Growth marketers · Works with: Google Sheets / Excel, Any capable chat model (ChatGPT, Claude, Gemini, others)

Public beta. This prompt was drafted with AI assistance and checked by automated rules, but it has not been reviewed or tested by a person yet. Treat it as a starting point and check the output. It is hidden from search engines while in beta. Use the “Was this prompt useful?” box to tell us what works.

Your prompt

Act as a marketing-operations analyst. I have a lead export with these columns: created_at, email, company, job_title, source, medium, campaign, country, status. Sample rows (anonymised): 2026-09-02 | a***@example.com | Acme Ltd | Marketing Manager | google | cpc | brand | UK | MQL 2026-09-02 | test@test.com | Test | tester | direct | none | none | US | New Goal: reduce junk leads and rank sources by lead quality. A "qualified lead" means: status reaches 'SQL' within 30 days. Privacy constraints: no real personal data may be shared with external tools. Deliver: 1. Assumptions about the data and any column whose meaning is unclear. 2. A cleaning plan: duplicate detection rules (state which fields and how fuzzy matching would work), email and phone format checks, test or junk lead detection, inconsistent values, missing required fields. For each step, give a spreadsheet or SQL approach and the risk of false positives. 3. A lead-quality audit plan by source using fields I have: how to compute qualified-rate and downstream conversion by source, how to handle small samples, and what additional fields (CRM stage, deal outcome) I need to judge quality properly. 4. A short list of data-governance cautions (do not paste real personal data into AI tools unless your policy allows it; use anonymised samples). 5. What cannot be concluded from the data. Rules: do not invent leads, rates or benchmarks. Missing data must be flagged. Verify that every proposed check uses only the columns I listed. Treat source-quality differences as correlation unless a controlled comparison exists.

Customize

The fields start with example values so you can see how the prompt works. Everything stays in your browser.

Your export columns.

Use fake or anonymised rows only.

What you want.

Your definition.

Your policy.

Example of what to expect

Illustrative only. It describes the kind of result this prompt aims for; real output varies by tool, model and run.

Assumptions, a step-by-step cleaning plan with false-positive risks, a source-quality audit plan, data-governance cautions and a 'cannot conclude' list. (Illustrative.)

Expected format: Assumptions, cleaning steps, audit plan, governance notes.

How to use it

  1. Use anonymised samples only.
  2. Pilot cleaning rules on a copy of the data.
  3. Review dedup rules with sales ops.

Limitations

  • Aggressive deduplication can merge distinct people.
  • Lead quality needs downstream outcome data from your CRM.

Platform notes

Google Sheets / Excel

Not verified: third-party guidance only · checked 2026-10-11

Formula syntax is shared across Google Sheets and Excel for the functions used here, but argument separators and function availability depend on your locale and version; test on a small range first.

General notes on Google Sheets / Excel
  • Formulas are written for common functions in both products. Locale settings (decimal and argument separators) can change what you must type.

Any capable chat model (ChatGPT, Claude, Gemini, others)

Official docs read · checked 2026-10-11

Works in any capable chat model. Paste a small anonymised sample and recompute the key numbers yourself: chat models can misread columns or miscalculate.

General notes on Any capable chat model (ChatGPT, Claude, Gemini, others)
  • These prompts are plain text and work in any chat assistant. For analytics prompts, paste a small, anonymised export; a chat model can misread columns or miscalculate, so recompute key numbers yourself.

Source, license and attribution

Origin
Original by MarketerTools
Publisher
MarketerTools
License
Original work by MarketerTools, free to copy and use. Informed by the linked documentation; no third-party text is reproduced.
Platform assumptions checked
2026-10-11

Documentation and references behind this prompt

These informed the structure and the platform notes. A reference is not a license, and no third-party prompt text is copied here.

Spotted an attribution error, or are you a source owner with a request? Use the chat button at the bottom right and quote prompt ID ana-025. We will correct or remove it.

Related prompts

  • AnalyticsIntermediateBuilds a checklist

    UTM governance audit from a list of campaign URLs

    Audits a list of tagged URLs for naming drift, case issues and missing parameters, and produces a naming standard you can enforce.

    Any capable chat model (ChatGPT, Claude, Gemini, others) · Google Analytics 4 · Google Sheets / Excel

  • AnalyticsIntermediateWrites formulas

    Spreadsheet formulas for marketing KPIs (Sheets and Excel)

    Gets working formulas for KPIs from your actual column layout, with error handling, a test case and explanations you can audit.

    Google Sheets / Excel · Any capable chat model (ChatGPT, Claude, Gemini, others)

  • AnalyticsIntermediateAnalyses your data

    GA4 traffic-quality check: engagement vs conversion by source

    Separates sources that bring visits from sources that bring valuable visits, ranking by engagement and conversion with sample-size warnings.

    Google Analytics 4 · Any capable chat model (ChatGPT, Claude, Gemini, others)

  • AnalyticsIntermediateBuilds a checklist

    GA4 event and conversion tracking audit from an event list

    Reviews your event list and key-event setup for naming, duplication, missing parameters and gaps against a measurement plan.

    Google Analytics 4 · Any capable chat model (ChatGPT, Claude, Gemini, others)