Data Cleaning Workflows

Real-world standard operating procedures (SOPs) for cleaning and formatting messy data. These workflows combine practical business scenarios with pure browser-based tools to solve common CSV, ecommerce, analytics, and marketing data problems. 100% local, zero uploads.

How to Merge 50 Shopify Order CSVs Into One File Without Crashing Excel

Shopify caps order exports at ~10,000 rows per file via the Admin UI. The Admin API paginates at 250 orders per request (limit=250). A store pushing 500 orders/day accumulates 45,000 rows monthly—requiring 6+ separate exports that drift in schema as Shopify silently renames columns (e.g., 'Billing Country' → 'Billing Country Code' in 2024-Q3). Paste a few months of high-volume store data into Excel, cross 150K rows with 34+ columns, and watch it freeze due to 32-bit COM interop memory allocation. This workflow merges dozens of fragmented CSVs, deduplicates by Order ID using the 'Updated at' timestamp as tiebreaker, and realigns shifted columns. The output is a clean, flat table compliant with RFC 4180 quoting rules, ready for Triple Whale ingestion, Pivot Tables, or LTV/ROAS analysis.

BeginnerTransactional
Read workflow

Normalize Shopify Multi-Line Order Exports for Accurate Profit Calculation

When a customer buys three products, Shopify's CSV creates three rows—but only populates shipping, discounts, and tax on the first (parent) row. The second and third (child) rows leave these financial fields completely blank. If you sort this export in Excel by SKU, the child rows detach from their parent order's shipping cost, making order-level gross profit calculation impossible. This workflow groups rows by Order Name (e.g., #1042), forward-fills shipping and discount fields to all child rows, then pivots the data to preserve the single-row-per-order structure required by QuickBooks and Xero.

IntermediateTransactional
Read workflow

Merge Date-Range-Limited Shopify Exports Into One Master File

High-volume Shopify stores must pull data in monthly chunks due to the Admin UI's ~10,000 row export limit. But an order placed at 11:58 PM on March 31st that gets fulfilled after midnight will appear in both the March and April exports with different Fulfillment Status values ('unfulfilled' vs 'fulfilled'). Simply concatenating these files creates duplicate orders that inflate revenue. This workflow merges dozens of CSVs sequentially, identifies overlaps via the Name column, and keeps only the most recent version based on the 'Updated at' timestamp. It also flags schema drift if Shopify silently added or renamed fields between your January and June exports.

BeginnerTransactional
Read workflow

Extract and Clean Customer Lists from Shopify for Meta Lookalike Audiences

Shopify's order export is a transaction log, not a customer list. A loyal customer who bought six times appears as six separate rows—potentially with conflicting shipping addresses across orders. Meta's Conversions API (CAPI) requires identifiers hashed with SHA-256: 'em' (email, lowercase), 'ph' (phone, E.164 digits only), 'fn'/'ln' (first/last name, lowercase trimmed). Meta requires a >60% match rate for effective Lookalike Audiences. Uploading raw Shopify exports means hashing the same email six times with conflicting metadata, causing Meta to reject the record. This workflow deduplicates by Email, selects the most recent transaction's shipping details, strips apartment numbers, and formats phones to the strict E.164 international standard.

AdvancedTransactional
Read workflow

How to Convert Stripe Payout Reports into QuickBooks Import Format

Stripe's balance detail export dumps charges, refunds, and fees into a flat CSV with ISO 8601 timestamps (2024-03-15T08:30:00Z) and negative values for fees. None of this maps to QuickBooks Online's rigid 3-column schema (Date MM/DD/YYYY, Description, Amount). QBO rejects ISO timestamps, semicolon delimiters, and currency symbols in the Amount field. While Stripe admits IIF files only work for QBD (not QBO), SaaS connectors like A2X charge $49/mo and require OAuth access to your Stripe account—a security attack surface. This workflow splits transaction types into Chart of Accounts buckets (Revenue, Merchant Fees, Refunds), converts ISO 8601 to MM/DD/YYYY, and generates a CSV that passes QuickBooks import validation with zero reconciliation errors.

IntermediateTransactional
Read workflow

Isolate True Net Processing Fees from Stripe Balance Transactions

Stripe's pricing model is 'interchange ++': the 2.9% headline rate is a blended average. Actual per-transaction fees range from 1.3% (domestic debit) to 3.9% (international Amex corporate). The Payouts export shows the lump-sum deposit—zero visibility into per-transaction fees. The Balance_Transactions.csv dumps charges, refunds, adjustments, and dispute reversals into one flat table. The Fee column contains negative values for processing fees, but refund fee reversals appear as positive values interspersed without clear grouping—making a simple SUM() wildly inaccurate. This workflow filters the Type column, recalculates net fee by Source ID, and produces a clean summary of your true blended rate including hidden 1.5% cross-border fees and 1% currency conversion surcharges.

AdvancedTransactional
Read workflow

Convert Stripe UTC Export Timestamps to Local Tax Period

Stripe hardcodes all CSV export timestamps to UTC. For a US Pacific Time business, a purchase at 5:30 PM PST on March 31st exports as 2024-04-01T01:30:00Z. That single timestamp shift pushes the revenue into Q2, creating an audit-triggering discrepancy between Stripe and your QuickBooks sales ledger. The IANA Time Zone Database identifier for US Pacific is 'America/Los_Angeles'—PST (UTC-8) applies November–March, PDT (UTC-7) applies March–November. Excel's '=A2-8/24' formula hardcodes UTC-8, which is wrong for 34% of the year during DST. This SOP detects timestamp columns, applies a DST-aware timezone offset using IANA tz database rules, and rewrites dates in YYYY-MM-DD format aligned precisely to your tax jurisdiction.

IntermediateTransactional
Read workflow

Reconcile Stripe Disputes and Refunds Against Original Charges

Annual Stripe exports for mid-volume merchants routinely exceed 500,000 rows, mixing charges, refunds, and dispute creations in one monolithic CSV. Cryptic values like 'dispute_warning_under_review' (a pre-dispute inquiry that may or may not escalate) don't link back to the original charge amount in the same row. You must trace each event back via Source ID as a foreign key. Visa's Dispute Monitoring Program (VDMP) threshold is 0.9% dispute-to-transaction ratio; Mastercard's Excessive Chargeback Program (ECP) uses 1.5%. This workflow filters for adjustment events, joins them to original charge rows using Source ID, and computes net realized revenue—all via a blazing-fast local Wasm hash join.

AdvancedTransactional
Read workflow

Reconcile Facebook Ads Spend with Shopify Revenue

Attributing Facebook Ads spend to actual Shopify revenue fails at the join key. Shopify exports expose Order Name (#1042) but strip UTMs from top-level columns. Facebook groups spend by Ad Set ID. There is no shared key for a direct VLOOKUP. The bridge: Shopify stores UTM parameters in note_attributes as a stringified JSON blob: [{"name":"utm_campaign","value":"bf-2024"}]. Meta's Attribution Settings default to '7-day click, 1-day view'; Shopify uses last-click 30-day. These mismatched windows alone explain 20–30% of the discrepancy before data quality issues enter. This workflow extracts UTM tags from Note Attributes via regex, parses utm_campaign, and joins the two datasets using DuckDB-Wasm's hash join—entirely inside your browser tab.

AdvancedTransactional
Read workflow

Calculate True ROAS from Facebook Ads Manager Exports

Exporting six months of Meta Ads performance yields a CSV with inconsistent data types. Days with zero spend leave the 'Amount spent' column blank rather than showing 0.00—Excel's Pivot Table excludes blank cells from the denominator, artificially inflating ROAS. Other rows include currency symbols ($, €, £) depending on ad account locale settings. When you build a Pivot Table to calculate ROAS (Conversion value / Amount spent), these blanks and text characters trigger #DIV/0! and #VALUE! errors. A performance team managing $5M/month discovered 23% of daily rows had blank 'Amount spent' values, inflating ROAS from a true 2.1x to a reported 3.8x. This SOP replaces blanks with zeros, strips currency symbols, and aggregates daily spend to compute true campaign-level ROAS.

IntermediateTransactional
Read workflow

Generate Bulk UTM Links for Black Friday Without Excel Auto-Increment

Media buyers building Black Friday UTM grids standardly use Excel concatenation formulas (e.g., =A2&"?utm_source="&B2) and drag them down 500 rows. But Excel's auto-fill logic interprets trailing numbers as sequences: 'bfcm-1' becomes 'bfcm-2', 'bfcm-3' across rows. You only discover this weeks later when GA4 shows 47 phantom campaign sources instead of one unified campaign. GA4's session_source_medium dimension treats each unique UTM combination as a separate source, making ROAS aggregation impossible. This workflow generates bulk UTMs deterministically, locks static parameters against incrementation, and outputs a clean CSV of fully-formed tracking URLs.

BeginnerTransactional
Read workflow

How to Clean Apollo Exported Leads Before Importing into Cold Email Software

Apollo exports include 'guessed' emails (confidence < 90%, verified 12–18% hard bounce rate per Apollo's own documentation) and hidden spam traps. Importing raw Apollo CSVs into Instantly guarantees an automatic campaign pause at >3% bounce. This 4-step workflow filters by the 'Email Status' column (keeping only 'Verified'), purges role-based targets (info@, admin@, sales@), flags catch-all domains (domains that accept all addresses but never deliver), and formats the header row for zero-mapping imports into Smartlead or Lemlist. RFC 5322 defines valid mailbox syntax—common Apollo violations include trailing periods before @ ([email protected]) and consecutive dots (john..smith@).

BeginnerTransactional
Read workflow

Merge and Deduplicate Multiple SDR Lead Exports for Cold Email

When multiple SDRs search Apollo.io and ZoomInfo targeting the same accounts but different titles, their exported CSVs inevitably overlap. Merging these lists without deduplication causes internal collisions: two SDRs email the same prospect within 48 hours, triggering spam complaints that burn your sending domain. Furthermore, Apollo aggregates data from multiple sources, returning First Names in inconsistent casing (john, MARY, Mcdonald). This workflow deduplicates by Email address while preserving the most recent 'Added to Sequence' timestamp, applies intelligent title casing to names handling edge cases like prefixes (O'Brien), hyphenations (Jean-Pierre), and particles (van der Berg, de la Cruz), and flags cross-source duplicates.

IntermediateTransactional
Read workflow

Split Large B2B Lead Lists by Territory Assignment

RevOps managers regularly buy monolithic B2B databases (200,000+ rows) from ZoomInfo or Cognism, which must be split by State, Region, or Industry for SDR distribution via Salesforce Territory Management or round-robin assignment rules. The traditional Excel workflow—apply AutoFilter, copy visible rows, open new workbook, save as CSV, repeat 50 times—is agonizing. At 150,000 rows, Excel's AutoFilter lags for 10 seconds per click. This workflow reads your master CSV, identifies unique values in your chosen split column (e.g., State), and generates a zip file of separate CSVs for each segment instantly.

BeginnerTransactional
Read workflow

Validate Salesforce Picklist Fields Before CSV Import

Salesforce enforces strict validation on dependent picklists (State/Country). When importing trade show leads, source data contains wild variations: 'US', 'U.S.A.', 'United States', 'USA' all refer to the same country, but Salesforce rejects anything that doesn't exactly match its configured picklist. ISO 3166-1 alpha-2 codes (US, GB, DE) are the Salesforce standard. Crucially, Salesforce's Data Import Wizard (max 50K records) rejects the ENTIRE batch if any row fails picklist validation—it doesn't skip bad rows. Data Loader (max 5M records) offers 'skip invalid rows' but requires CLI setup. Neither provides a pre-flight validation report. This SOP cross-references your Country/State columns against standard picklists, applies regex-based normalization, and generates a clean file ready for a 100% successful upsert.

AdvancedTransactional
Read workflow

How to Query Nested JSON API Responses with SQL Directly in the Browser

Your Stripe API returns line_items nested 4 levels deep. Segment's tracking plan exports event_properties as objects inside arrays. Pasting this into Excel gives you a useless column of [object Object]. Writing a Python script with json_normalize() takes 20 minutes for a single ad-hoc query. DuckDB's read_json_auto() infers schema from the first 100 rows by default—if row 101 introduces a new nested key (common in Stripe webhooks where event types vary), the column silently becomes NULL. Use sample_size=-1 for full-file inference. This workflow loads raw JSON straight into DuckDB-Wasm, auto-detects the schema, and lets you run SQL with UNNEST() to flatten arrays and CTEs for multi-step logic—entirely inside your browser tab.

AdvancedTransactional
Read workflow

Convert Giant CSV Files to Parquet Without Python MemoryError

Loading a 3GB CSV into pandas using read_csv() triggers MemoryError on 16GB machines because the in-memory DataFrame consumes 5–10x the file's disk size. CSV's row-oriented structure wastes enormous space on repetitive strings. Parquet's columnar storage with configurable row_group_size (default 122,880 rows) and compression reduces footprint by 50–90%. Snappy offers ~2:1 ratio at 500MB/s throughput; ZSTD offers ~4:1 at 150MB/s. This workflow streams your giant CSV through DuckDB-Wasm in the browser, infers the schema automatically, and outputs a compressed .parquet file via COPY TO 'file.parquet' (FORMAT PARQUET, CODEC 'ZSTD'). No Python environment, no EC2 instances, zero cloud uploads.

BeginnerTransactional
Read workflow

Run Ad-Hoc SQL Queries on CSV Files Without Database Setup

When stakeholders ask 'how many California orders used a discount code?', the traditional workflow requires spinning up PostgreSQL, writing CREATE TABLE DDL, configuring COPY commands, and running pg_dump just to execute a simple SELECT. This 45-minute friction discourages exploratory analysis. DuckDB-Wasm registers your CSV as a virtual table in the browser, running entirely in a WebAssembly sandbox. Write standard SQL—SELECT, WHERE, JOIN, window functions (ROW_NUMBER, LAG, SUM OVER), CTEs—and get results in milliseconds. Schema inference handles type detection automatically. Browser Wasm modules use memory-mapped virtual files backed by IndexedDB, allowing datasets larger than available RAM.

IntermediateTransactional
Read workflow

Join Massive CSV Files Locally Without Silent Data Loss

Merging a 3M-row transactions.csv with a customers.csv crashes Excel VLOOKUP. Python pandas handles the scale, but introduces a deadly flaw: implicit schema inference. If one file stores customer_id as zero-padded strings ('00123') and the other has bare integers (123), an inner merge silently drops every mismatched row. No exception, no warning—just missing data. The fintech case: pandas inferred merchant_id '00458921' as Object and '458921' as Int64. The inner merge silently dropped 412,000 rows. This tool lets you write explicit SQL JOINs with CAST operations to enforce type consistency, running instantly in-browser via Wasm. Use LEFT JOIN to detect mismatches instead of silently dropping them.

AdvancedTransactional
Read workflow

How to Anonymize Customer Data Before Sharing with ChatGPT or Claude

Your raw customer CSV contains toxic PII. Pasting it into ChatGPT sends that data to OpenAI's servers—risking a Samsung-style corporate leak. OpenAI's Data Retention Policy stores API inputs for 30 days by default for abuse monitoring; ChatGPT web conversations are retained indefinitely unless manually deleted. Neither qualifies as a signed Data Processing Agreement under GDPR Article 28. This SOP local-masks names, emails, phones, and IPs using browser-based one-way SHA-256 hashing with per-session salt. It preserves column types and statistical structures for LLM analysis, rendering the output completely untraceable to real users. GDPR Recital 26 states anonymized data falls outside the regulation's scope—but only if re-identification is 'reasonably unlikely'.

IntermediateTransactional
Read workflow

Anonymization vs Pseudonymization: A Compliance Guide for AI Data Workflows

Uploading raw customer exports to ChatGPT or Claude creates an undocumented data transfer that compliance teams cannot audit. The legal distinction is critical: Anonymization irreversibly removes identifiers, placing data outside GDPR scope (Recital 26). Pseudonymization replaces identifiers with reversible tokens—data remains GDPR-regulated. During a SOC2 Type II audit, any uncontrolled third-party data transfer (including pasting CSVs into LLM interfaces) is flagged as a control gap. This guide maps the legal framework, provides implementation patterns for each anonymization level, and documents the audit trail required for SOC2 evidence collection.

AdvancedInformational
Read workflow

Redact SSN and Credit Card Numbers from Support Ticket Exports

Customer support tickets frequently contain accidentally disclosed PII—SSNs from identity verification, or credit cards from billing disputes. Simple find-and-replace misses the dozens of formatting variants: 123-45-6789, XXX-XX-6789, 123.45.6789, 123 45 6789, and partial disclosures. Regex patterns catch these reliably, but pasting production logs containing real SSNs into online regex testing tools (regex101.com, regexr.com) constitutes a data exposure incident. PCI DSS Requirement 3.3 mandates PANs must be rendered unreadable anywhere stored; Requirement 3.4 specifies acceptable methods (hash, truncate, tokenize). This workflow applies comprehensive PII detection patterns locally, replacing matches with [REDACTED] tokens.

IntermediateTransactional
Read workflow

Generate Synthetic Test Data Preserving Referential Integrity

Naive pseudonymization (replacing emails with random UUIDs) breaks referential integrity. If customer_id 847291 appears in both orders.csv and subscriptions.csv, random replacement breaks foreign key relationships—JOINs fail in staging. This workflow builds a deterministic mapping table via salted SHA-256 hashing, ensuring that customer 847291 always maps to the same synthetic email (e.g., [email protected]) across all related tables. The per-environment secret salt ensures staging data cannot be reverse-mapped without the salt. Safely clone production databases into staging without triggering GDPR Article 33 breach notifications.

AdvancedTransactional
Read workflow

Sanitize Email Lists Before Klaviyo Import

Importing a dirty contact list into Klaviyo is how brands torch their sending reputation in 48 hours. Suppression lists from legacy ESPs (Mailchimp, Omnisend) ship with invisible zero-width joiners (U+200D), malformed addresses (user@@domain), and role-based emails (info@, admin@) that act as spam traps. RFC 5321 Section 4.1.2 defines the mailbox grammar—zero-width characters fall outside the 'atext' production rule, making addresses technically invalid. Yet most ESP validators only check for '@' presence, not full RFC compliance. Klaviyo flags accounts with >2% hard bounces. Yahoo tightened to 0.3% in February 2024 with their new bulk sender requirements. This workflow cross-references your prospect list against Klaviyo's Suppression API export, strips non-printable Unicode, and validates RFC 5322 formatting.

IntermediateTransactional
Read workflow

Format Contact CSV for Klaviyo Profile Import

Klaviyo's import parser is case-sensitive and reserves properties prefixed with '$'. Columns named 'Email Address' will not auto-map to the $email primary identifier. Similarly, without $first_name, Klaviyo creates custom properties instead of populating the profile fields used for {{ person.first_name }} tags—causing emails to render with blank greetings ('Hi ,'). For SMS, Klaviyo strictly enforces E.164 formatting per ITU-T Recommendation E.164: +[country code][number] with no spaces, dashes, or parentheses. A value like '(415) 555-0123' triggers 'Invalid Phone Format' and row rejection. This workflow auto-maps your headers to Klaviyo's reserved property schema and strips phones to E.164 entirely in your browser.

BeginnerTransactional
Read workflow

Scrub Hard Bounces and Suppression Lists Before ESP Migration

When migrating from Mailchimp to Klaviyo, the new ESP assigns your domain a cold IP reputation. During IP warming, Gmail and Yahoo monitor you ruthlessly under the February 2024 bulk sender requirements (0.3% spam rate threshold for Yahoo, 0.1% hard bounce triggers temporary deferral on new domains per Google Postmaster Tools). A hard bounce rate >2% triggers automated sender penalties. The critical operational mistake: exporting only the 'Active' list from the old ESP, while forgetting the Suppression List (addresses that previously hard-bounced or unsubscribed). Without cross-referencing, dormant bad addresses silently poison your new sender reputation. This workflow performs a local anti-join between your master CSV and suppression CSV, purging dead emails before import.

IntermediateTransactional
Read workflow

Split Full Name Column for Email First Name Personalization

Event platforms (Eventbrite, Luma, Hopin) export a single 'Full Name' column. For ESP personalization, you need $first_name extracted perfectly. Excel's 'Text to Columns' breaks on common names: 'María de la Cruz' fragments into four misaligned columns, and 'Dr. James K. Patterson Jr.' splits across five cells. Python's nameparser library handles 95% of Western names but fails on CJK single-token names (王小明) and Arabic naming conventions (bin/binti patronymics). This workflow uses regex-based extraction: isolate the first token as $first_name, treat everything after as $last_name (preserving compound surnames), strip honorifics (Dr., Prof.) and suffixes (Jr., PhD, III) that corrupt the name field.

AdvancedTransactional
Read workflow

Calculate True SKU Net Profit from Amazon Settlement Reports

Amazon's Settlement Report (report type: _GET_V2_SETTLEMENT_REPORT_DATA_FLAT_FILE_) is a TSV where each row represents a single event (Order, Refund, FBA Fee, Commission) rather than order aggregates. The accounting trap: when pivoting this TSV to calculate net profit per SKU, direct summation of the 'amount' column double-counts refunds—the original sale AND the refund reversal both appear as separate rows. Worse, fee columns store values as negative numbers for charges, but refund rows store fee reversals as positive values (credits back to you). The transaction_type column contains values like 'Order', 'Refund', 'Service Fee', 'FBA Inventory Fee'. This workflow parses the TSV, groups transactions by amazon-order-id and sku, nets each transaction type correctly, and produces a clean per-SKU profit summary.

IntermediateTransactional
Read workflow

Reconcile Amazon FBA Refunds Against Inventory Reimbursements

When Amazon refunds a customer, they debit your account immediately. If the item is never returned or arrives damaged at the warehouse, Amazon owes you a reimbursement (cash or inventory). Statistically, 8–15% of these reimbursements are missed due to warehouse processing lags. This workflow joins your 'Refund' transactions from Settlement Reports with your 'Reimbursements' report via the Merchant Order ID. It flags every order where a refund was issued but no corresponding reimbursement or inventory restock has occurred after 45 days—allowing you to open bulk support cases.

AdvancedTransactional
Read workflow

Fix Broken Characters (Mojibake) in Excel CSV Exports

When you open a UTF-8 encoded CSV in Excel, international characters like 'Müller', 'José', or '北京' often render as 'Müller' or 'Josa©'. This is called Mojibake. Despite being a modern spreadsheet, Excel assumes the legacy Windows-1252 encoding unless it sees a 3-byte signature at the start of the file: the UTF-8 Byte Order Mark (BOM: 0xEF, 0xBB, 0xBF). Most Mac/Linux software exports raw UTF-8 without a BOM. This workflow reads your raw binary file, detects the encoding, and re-exports it with the correct BOM prefix—instantly making it 'Excel-safe' without needing to use the complex 'Data → From Text/CSV' import wizard.

BeginnerInformational
Read workflow

Remove Invisible Zero-Width Spaces and Control Characters from Huge CSVs

Data copied from websites or Salesforce often contains 'ghost' characters: the Zero-Width Space (U+200B), Zero-Width Joiner (U+200D), or Non-Breaking Space (U+00A0). These are literally invisible in Excel, but they change the string's byte-length. If you search for 'A101' in your CRM, and the record is stored as 'A101[U+200B]', the search returns 0 results. This leads to massive duplicate creation. This workflow perform a deep regex scan of every cell, identifies 15+ types of invisible Unicode pollutants, and purges them while preserving legitimate text and formatting.

BeginnerTransactional
Read workflow