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.
Shopify limits order exports to specific date ranges, so sellers with years of data end up with dozens of separate CSV files. This guide walks through merging them into a single master file while handling duplicate order IDs and inconsistent column orders across exports.
A step-by-step SOP for stripping PII (names, emails, phone numbers, physical addresses) from production datasets while preserving the statistical shape of the data. Includes guidance on which columns to mask, which to drop, and how to verify the output is truly de-identified.
Apollo.io exports contain duplicate contacts, generic role-based emails (info@, support@), invalid domains, and inconsistent company name formatting. This workflow shows how to clean all of these issues in one pass to keep your sender reputation above 95% deliverability.
Stripe's payout CSV has 15+ columns that don't map to QuickBooks' expected schema. This guide shows how to separate gross revenue from processing fees, reformat dates to MM/DD/YYYY, and produce a clean CSV that QuickBooks accepts without manual reconciliation.
Modern APIs return deeply nested JSON that's painful to parse in spreadsheets. This developer-focused guide demonstrates converting API response dumps into queryable tables and running SQL with JOINs, aggregations, and window functions — all without spinning up a local database.
Attributing Facebook Ads spend to actual Shopify revenue is a nightmare because Shopify exports Name (e.g., #1042) and Order ID, but completely strips UTM parameters or Campaign IDs from the standard order CSV. Meanwhile, your Facebook Ads export groups spend by Campaign ID and Ad Set Name. You cannot directly VLOOKUP these two datasets. This workflow walks you through extracting UTM tags from Shopify's Note or Tags fields, parsing them with regex, and executing a memory-safe local VLOOKUP to bridge ad spend and realized revenue without touching a cloud server.
WooCommerce's native CSV export is notoriously hostile to accounting workflows. Product descriptions in the post_content column arrive wrapped in raw HTML tags and shortcodes like [woocommerce_reviews]. Custom meta fields (e.g., _regular_price, _sku) frequently shift columns when plugins inject hidden postmeta, and the post_date column alternates between Y-m-d H:i:s and Unix timestamps depending on your WordPress version. This SOP strips HTML remnants via regex, realigns displaced meta columns, and standardizes all date fields to ISO 8601 so QuickBooks and Xero accept the import without throwing schema validation errors.
Importing a dirty contact list into Klaviyo is the fastest way to torch your sending domain's reputation. Exported suppression lists from legacy ESPs are riddled with invisible zero-width spaces, malformed addresses like user@@domain.com, and role-based emails (info@, admin@) that trigger spam traps. If your hard bounce rate exceeds 2% on a single campaign, Klaviyo will throttle your account and your transactional emails start landing in Gmail's Promotions tab. This workflow cross-references your prospect list against historical bounce logs, strips non-printable Unicode characters, and validates RFC 5322 email formatting entirely in your browser.
Stripe's Payouts export only shows the lump-sum deposit hitting your bank account — it tells you nothing about the per-transaction fee composition. To calculate your real effective processing rate, you need the Balance_Transactions.csv file, but this export dumps charges, refunds, adjustments, dispute reversals, and Stripe billing fees into a single flat table with no hierarchy. The Fee column contains negative values for processing fees, but refund-related fee reversals and dispute charges are interspersed without clear grouping, making a simple SUM() wildly inaccurate. This workflow filters the Type column to isolate charge, payment, refund, and dispute rows separately, recalculates the net fee per original transaction by grouping on Source ID, and produces a clean summary showing your true blended rate.
Stripe hardcodes all CSV export timestamps to UTC with no option to select your local timezone. For a business operating in US Pacific Time (UTC-8), a customer purchase completed at 5:30 PM PST on March 31st appears as 2024-04-01 01:30:00 in the Created (UTC) column of your Stripe export. This single timestamp shift pushes that revenue into Q2 for tax purposes, creating a discrepancy between your Stripe revenue total and your QuickBooks sales ledger. This SOP detects all timestamp columns (Created (UTC), Available On (UTC)), applies a configurable timezone offset, and rewrites the date in YYYY-MM-DD format aligned to your state's tax jurisdiction.
Annual Stripe exports for mid-volume merchants routinely exceed 500,000 rows, mixing successful charges, partial refunds, full refunds, dispute creations, dispute wins, and dispute losses in one monolithic CSV. The Type column uses cryptic values like dispute, dispute_reversal, and refund without linking back to the original charge amount in the same row. You must trace each dispute or refund back to its Source ID to find the original charge row and calculate net revenue impact. This workflow filters the export to only dispute and refund event types, joins them back to original charge rows using Source ID as the foreign key, and computes the net realized revenue per order after all adjustments.
When a customer buys three different products in one order, Shopify's CSV export creates three separate rows — one per line item — but only populates shipping cost, shipping method, and discount codes on the first row. The second and third rows have these columns completely blank. If you sort or filter this export in Excel by Lineitem quantity or Lineitem name, the child rows detach from their parent order's shipping and discount data, making it impossible to calculate true order-level gross profit. This workflow groups rows by Name (the order number like #1042), forward-fills shipping and discount fields from the first row to all child rows, then optionally pivots the data so each unique line item becomes its own column while preserving the single-row-per-order structure that accounting software expects.
Shopify's admin export interface caps each download to a specific date range, and for stores with high order volume, you may need to pull data in monthly or quarterly chunks. The problem: if you export March 1-31 and then April 1-30, any order placed at 11:58 PM on March 31st that gets fulfilled or updated after midnight appears in both exports with different Fulfillment Status values. Simply concatenating the files creates duplicate Name entries that inflate revenue reports. This workflow merges multiple Shopify order CSVs sequentially, identifies overlapping orders by the Name column, and keeps only the most recent version of each order based on the Updated at timestamp. It also validates that column headers match across all files before merging, flagging any schema drift caused by Shopify silently adding new fields like Payment References between your January and June exports.
Shopify's order export is not a customer list — it's a transaction log. A loyal customer who bought six times appears as six separate rows with potentially different Shipping Name, Shipping Street, and Shipping City values if they sent gifts to different addresses. Meta's Custom Audience matching algorithm requires clean, deduplicated PII to achieve acceptable match rates. Uploading raw Shopify order exports to Meta Ads Manager means the same customer email hashes six times while their name and address fields conflict, causing Meta to reject or low-confidence-match most records. This workflow deduplicates rows by Email, selects the most recent transaction's shipping details per customer, reconciles Billing First Name and Shipping First Name conflicts by preferring the shipping address (which Meta's address matching weights higher), strips apartment numbers into a separate Address Line 2 field, and formats phone numbers to E.164 international standard (+1XXXXXXXXXX) that Meta requires for phone-based matching.
Exporting half a year of daily campaign performance from Meta Ads Manager yields a CSV riddled with data type inconsistencies. Days with zero spend often leave the Amount spent (USD) column completely blank rather than showing a zero, while other rows include currency symbols or thousands separators depending on your account's locale settings. When you attempt to build a Pivot Table in Excel to calculate Return on Ad Spend by dividing Purchases conversion value by Amount spent (USD), these blank cells and text characters trigger #DIV/0! and #VALUE! errors that corrupt your entire analysis. This workflow standardizes the Amount spent (USD) column by replacing blanks with zeros and stripping currency symbols, then aggregates daily spend and conversion value by Campaign name to compute true ROAS at the campaign level.
Media buyers preparing for Black Friday often need to generate hundreds of UTM-tagged URLs across multiple ad sets, creatives, and placements. The standard approach involves building a concatenation formula in Excel like =A2&"?utm_source=facebook&utm_medium=cpc&utm_campaign="&B2 and dragging it down 500 rows. But Excel's auto-fill logic sometimes interprets bfcm-1 in the campaign parameter as a sequence and increments it to bfcm-2, bfcm-3, bfcm-4 without warning. You only discover this weeks later when GA4 shows 47 phantom campaign sources instead of one unified bfcm-1. This workflow accepts your base URLs and UTM parameter mappings, validates that campaign names remain static across all generated rows, and outputs a clean CSV of fully-formed tracking URLs.
Uploading offline conversion events to Facebook's Conversions API requires customer identifiers like email addresses and phone numbers to be hashed with SHA-256 before transmission. But Meta's hashing algorithm is case-sensitive and whitespace-sensitive — an email like " [email protected] " with leading spaces and mixed casing produces a completely different hash than "[email protected]", causing the match to fail silently. Worse, CRM exports from Salesforce or HubSpot often contain invisible zero-width joiner characters (U+200D) copied from web forms, which pass visual inspection but break the hash entirely. This workflow normalizes all email fields to lowercase, strips leading/trailing whitespace and non-printable Unicode characters, validates RFC 5322 email syntax, and reformats phone numbers to E.164 international standard (+1XXXXXXXXXX for US numbers) with country codes prepended.
When multiple SDRs on your team search Apollo.io or ZoomInfo targeting the same companies but different job titles, the exported CSVs inevitably overlap. SDR #1 pulls VP-level contacts at Acme Corp while SDR #2 targets Directors at the same accounts — both exports contain [email protected] because that person matches both search criteria. Merging these files without deduplication causes internal collisions: two SDRs email the same prospect from different sending domains within 48 hours, triggering spam complaints that damage your entire sending infrastructure's reputation. Beyond duplicates, Apollo exports frequently return First Name values in inconsistent casing — john, MARY, De instead of De — because the platform aggregates data from multiple providers without normalizing capitalization. This workflow deduplicates by Email address while preserving the most recent Added to Sequences timestamp, then applies intelligent title casing to First Name and Last Name fields, handling edge cases like hyphenated names (Jean-Pierre).
Marketing teams purchasing B2B contact databases from providers like ZoomInfo, Cognism, or Clay often receive a single monolithic CSV containing 200,000+ rows covering all target accounts nationwide. RevOps managers then need to split this master list by State, Region, or Industry to distribute territory-specific assignments to SDR teams. The traditional Excel workflow — applying AutoFilter on the State column, selecting visible rows, copying to a new workbook, saving as CSV, and repeating for each of the 50 states — becomes unusable at scale. At around 150,000 rows, Excel's AutoFilter response time degrades to 8-12 seconds per operation. The copy-paste cycle for each territory takes 20-30 seconds including the save dialog. This workflow reads the master CSV, identifies the unique values in your chosen split column, and instantly generates separate CSV files for each segment — all processed locally.
Salesforce administrators configure Country and State/Province fields as dependent picklists with strict value restrictions — meaning the system only accepts exact matches from a predefined list. When importing leads from Apollo, ZoomInfo, or event badge scans, the source data invariably contains variations: United States, US, U.S.A., USA, and United States of America all refer to the same country, but Salesforce will reject any value that doesn't exactly match the configured picklist value. A single mismatched picklist value causes the entire import batch to fail — Salesforce doesn't skip invalid rows and continue; it rejects the entire file with an error log listing each problematic row. This workflow cross-references your Country and State columns against Salesforce's standard picklist values, applies regex-based normalization to map common variations, and generates a validation report.
Loading a 3GB server log CSV into pandas with read_csv() routinely triggers MemoryError on machines with 16GB RAM because pandas creates an in-memory DataFrame that can consume 5-10x the file's disk size during parsing. The row-oriented structure of CSV also means storing repetitive string values (like status codes or IP prefixes) wastes enormous amounts of space. Parquet's columnar storage with dictionary encoding and Snappy compression commonly reduces storage footprint by 50–90% depending on data cardinality. This workflow streams your CSV through DuckDB-Wasm in the browser, infers schema automatically, and outputs a .parquet file. The trade-off: Parquet is a binary format that you cannot inspect with a text editor or cat — you need a reader like DuckDB, PyArrow, or this tool to query it.
When a stakeholder asks 'how many orders from California had a discount code in Q3?', the traditional workflow demands spinning up PostgreSQL, writing CREATE TABLE DDL statements, configuring COPY commands to import the CSV, and only then writing your SELECT query. For a one-off validation question, this environment setup takes 30-45 minutes of friction that discourages exploratory analysis. This tool registers your CSV file as a virtual table in DuckDB-Wasm running entirely in your browser. You write standard SQL — SELECT, WHERE, GROUP BY, JOIN, window functions — and get results in seconds. Schema inference handles type detection automatically, though you can override with explicit CAST operations when needed. The data never leaves your machine.
Merging two CSV files with millions of rows — like joining a transactions.csv against a customers.csv on customer_id — seems straightforward until you hit Excel's row limit or VLOOKUP's performance cliff. Excel becomes unresponsive around 500K rows, eventually showing 'Process not responding' before crashing entirely. Python pandas handles the scale but introduces a subtler failure: automatic schema inference. If one file has customer_id values like '00123', '00456' (parsed as strings preserving leading zeros) and the other has 123, 456 (parsed as integers), a LEFT JOIN produces Unexpected NULL for every row where the types don't match. You lose data silently with no error message. This tool lets you write explicit SQL JOINs with CAST operations to enforce type consistency, running entirely in-browser via DuckDB-Wasm.
Data Governance policies and Data Processing Agreements (DPAs) typically prohibit sharing personally identifiable information with third-party services — regardless of whether an AI vendor claims not to train on your data. When marketing teams upload raw customer exports containing email addresses, phone numbers, and physical addresses to ChatGPT or Claude for segmentation analysis, they create an undocumented data transfer that compliance teams cannot audit or control. The distinction matters: Anonymization irreversibly removes the ability to re-identify individuals (falling outside GDPR scope), while Pseudonymization replaces identifiers with reversible tokens (still regulated). This workflow applies one-way hashing to email and phone columns, redacts address fields entirely, and generalizes dates of birth into age ranges — producing a dataset that retains statistical patterns for AI analysis while eliminating direct identifiers. All processing occurs in your browser; the original file never transits through any server.
Customer support tickets frequently contain accidentally disclosed sensitive information — customers paste full Social Security Numbers when verifying identity, type complete credit card numbers when disputing charges, or share bank account details when setting up payment plans. Zendesk and Intercom exports preserve these in plaintext within the Description and Comment columns. Before sharing support logs with QA teams, training vendors, or analytics platforms, this PII must be systematically redacted. The challenge: SSN formats vary wildly — 123-45-6789, 123 45 6789, 123456789 — and credit card numbers appear with or without spaces, dashes, or grouping. Simple find-and-replace misses most variants. Regex patterns can catch these reliably, but pasting production support logs containing real SSNs and card numbers into online regex testing tools constitutes a data exposure incident by itself. This workflow applies comprehensive PII detection patterns locally, replacing matches with [REDACTED-SSN] or [REDACTED-PAN] tokens.
Development teams cloning production databases into staging environments create a predictable failure mode: test workflows trigger real emails, SMS messages, or webhook callbacks to actual customers. A QA engineer running a batch notification test sends 12,000 promotional emails to real users because the staging database contained production email addresses. The standard mitigation — pseudonymizing the export before import — introduces a subtler problem when done naively. Referential integrity requires that the same customer ID always maps to the same synthetic email across all tables. If customer_id: 847291 appears in both orders.csv and subscriptions.csv, both must receive the identical pseudonymized email. Random replacement breaks foreign key relationships and causes import failures. This workflow builds a deterministic mapping table during the first pass — hashing each real identifier to a consistent synthetic value — then applies that mapping across all related tables.
Zero-width joiners (U+200D), zero-width non-joiners (U+200C), and zero-width spaces (U+200B) are invisible Unicode characters that SaaS platforms inadvertently inject into exported data — often originating from rich-text copy-paste in web forms, WYSIWYG editors, or middleware that uses ZWJ as an internal delimiter. These characters render identically to clean strings in every visual context, making them undetectable through manual inspection. The observable failure symptoms are maddening: two cells both display CUST-847291, yet VLOOKUP returns #N/A, XLOOKUP fails to match, and SELECT * FROM table WHERE id = 'CUST-847291' returns 0 rows. The root cause is that one value contains a zero-width space (CUST-847291 = 12 characters) while the other is clean (11 characters). Python's strip() method only removes standard whitespace (U+0020, U+0009, U+000A) and does not catch these Unicode ghosts. This workflow strips all zero-width characters, non-breaking spaces (U+00A0), and other invisible Unicode from every cell, then trims standard leading/trailing whitespace — all processed locally in your browser.
When a CSV file is encoded in UTF-8 but lacks a Byte Order Mark (BOM — the three-byte sequence \xEF\xBB\xBF), Windows Excel historically falls back to the system's ANSI code page (e.g., Windows-1252 for Western European locales) during file opening. This is a legacy behavior: Excel treats BOM-less UTF-8 as an ambiguous encoding and guesses based on the OS locale. macOS Excel and Google Sheets default to UTF-8 regardless of BOM presence, which is why the same file opens correctly on a Mac but displays mojibake on Windows. The observable symptom is distinctive: UTF-8 multibyte sequences get misinterpreted as single-byte ANSI characters. The German ü (UTF-8: \xC3\xBC) renders as ü, ä becomes ä, Chinese characters like 用户 become ç"¨æˆ·, and French é displays as é. The fix is not re-encoding the file — the content is already valid UTF-8 — but prepending the 3-byte BOM signature so Excel's encoding detection recognizes it as UTF-8.
REST API responses from platforms like Shopify, Stripe, and HubSpot return deeply nested JSON structures where critical data lives inside arrays — order.line_items[], charge.refunds[], deal.products[]. Naive JSON-to-CSV converters handle nested objects via dot-notation flattening (address.city, address.zip), but they fail catastrophically on arrays. When encountering line_items: [{"sku": "A", "qty": 2}, {"sku": "B", "qty": 1}], these tools either drop the array entirely or serialize the entire array into a single cell as the literal string [object Object], rendering the data useless for downstream analysis. The correct approach requires two strategies: dot-notation flattening for nested objects and array explosion for nested arrays (one order with 3 line items becomes 3 CSV rows, each carrying the parent order's metadata). This workflow parses your JSON, detects array vs. object structures at every depth level, applies dot-notation to objects, and explodes arrays into separate rows while duplicating parent-level fields.
Klaviyo's profile import parser is case-sensitive and reserves all properties prefixed with '$' as built-in profile attributes. Columns named 'Email Address', 'email', or 'E-mail' will not auto-map to the $email reserved property — Klaviyo requires the exact column header '$email' to recognize and populate the primary identifier. Similarly, $first_name and $last_name must use the dollar-sign prefix; without it, Klaviyo creates custom properties instead of populating the profile fields that personalization tags like {% person 'first_name' %} reference, causing emails to render with blank greetings. For SMS marketing imports, Klaviyo enforces strict E.164 international phone number formatting — the number must include a leading '+', country code, and subscriber number with no spaces, dashes, or parentheses. A value like '(415) 555-0123' triggers 'Invalid Phone Format' and the row is rejected. This workflow renames your columns to match Klaviyo's reserved property schema exactly, strips and reformats phone numbers to E.164 (e.g., +14155550123), and validates email syntax before upload — all processed locally in your browser so subscriber PII never leaves your machine.
When migrating from one Email Service Provider to another — say from Mailchimp to Klaviyo or from SendGrid to Customer.io — the new ESP assigns your sending domain a neutral or cold IP reputation. During the IP warming phase, mailbox providers (Gmail, Microsoft, Yahoo) monitor your bounce rate with heightened scrutiny. The industry threshold for triggering automated sender reputation penalties is a hard bounce rate exceeding 2% on any single campaign. If your imported list contains addresses that already hard-bounced on the old platform, those same dead mailboxes will bounce again on day one of warming. The critical operational mistake is exporting only the 'Active' subscriber list from the old ESP and importing it directly, while forgetting to also export the Suppression List — the record of every address that previously hard-bounced, unsubscribed, or was marked as spam. Without cross-referencing these two lists, dormant addresses silently poison your new sender reputation. This workflow performs a local anti-join between your master subscriber CSV and your suppression list CSV, removing every suppressed email address before import.
Event platforms like Eventbrite, Luma, and Cvent export attendee data with a single Full Name column rather than separate first and last name fields. For email personalization, you need $first_name extracted correctly — but naive space-based splitting breaks on common name structures. 'Text to Columns' with a space delimiter turns 'John Paul Smith' into first: John, last: Paul (losing the surname entirely), splits 'María de la Cruz' into four fragmented columns, and reduces 'Dr. James K. Patterson Jr.' to chaos across five cells. The correct approach uses pattern-based extraction: isolate the first token as first_name, treat everything after the first token as last_name (preserving compound surnames, middle names, and suffixes intact), and strip honorifics (Dr., Mr., Prof.) and generational suffixes (Jr., III, PhD) that would corrupt the last name field. This workflow applies regex-based name parsing that handles middle names, multi-part surnames (van der Berg, de la Cruz, bin Ibrahim), and common prefixes/suffixes — outputting clean $first_name and $last_name columns ready for Klaviyo, HubSpot, or any ESP.
Amazon's Settlement Report (GET_V2_SETTLEMENT_REPORT_DATA_FLAT_FILE) is delivered as a Tab-Separated Values (TSV) file, not CSV — which causes immediate parsing failures when tools assume comma delimiters. More critically, the report uses an extremely flat transaction model where each row represents a single event type (Order, Refund, Adjustment, FBA Fee, Commission) rather than aggregating per order. A single order generates 4-6 rows: one Order row with product_sales revenue, separate rows for fba_fees, commission, and shipping_credits, and if refunded, additional rows with negative values in product_sales and positive reversals in fee columns. The accounting trap: when building a Pivot Table to calculate net profit per SKU, naive summation of the amount column double-counts refunds because the original sale and the refund reversal both appear as separate line items. Additionally, fee columns like selling_fees and fba_fees store values as negative numbers (costs), but refund rows store fee reversals as positive numbers. This workflow parses the TSV, groups transactions by amazon-order-id and sku, netting each transaction type correctly before outputting a clean per-SKU profit summary.
Amazon's FBA Inventory reports (GET_FBA_MYI_UNSUPPRESSED_INVENTORY_DATA) are point-in-time snapshots — each export captures quantity_available, quantity_inbound_working, and quantity_reserved at the exact moment of generation. Sellers who download weekly snapshots to track inventory velocity and forecast replenishment needs accumulate multiple files containing the same SKUs at different time points. Merging these files for trend analysis requires careful deduplication logic. The common failure mode: appending 4 weekly snapshots into one workbook and building a Pivot Table on FNSKU produces Sum of quantity_available that is 4x the actual inventory level. If SKU ABC-001 had 1,200 units available in each of the 4 snapshots, the Pivot shows 4,800 — triggering the replenishment system to under-order by 3,600 units. This workflow merges multiple inventory TSV files and deduplicates by FNSKU, preserving the most recent snapshot for accurate stock-level reporting.
When Amazon's fulfillment network damages, loses, or fails to return customer-returned inventory, the seller is entitled to reimbursement — but Amazon does not automatically reimburse every qualifying event. Sellers must cross-reference three separate reports to identify gaps: the Orders Report, the Refunds Report, and the Reimbursements Report. The join challenge: these reports use inconsistent key columns. The Orders Report uses order-id, the Settlement Report uses amazon-order-id, and the Reimbursements Report uses amazon_order_id (underscore instead of hyphen). Excel VLOOKUP on 200,000+ row datasets with mismatched column names routinely freezes with 'Not Responding' and produces #N/A errors. Worse, if Excel interprets numeric order IDs as numbers, it drops leading zeros — an order ID 001-2345678-9012345 becomes 1-2345678-9012345, breaking every join. This workflow performs SQL-based LEFT JOINs locally via DuckDB-Wasm, normalizing all order ID columns to consistent string format before reconciliation.