WooCommerce analytics exports may contain long order IDs, SKUs with leading zeros, dates near timezone boundaries, currency decimals, percentages, formula-like text, Unicode names, and empty values. Spreadsheet applications can coerce these into scientific notation, dates, formulas, rounded numbers, or dropped zeros. That makes a correct raw export look broken and can also create a dangerous file when formula-leading text is opened.
Use this for WooCommerce analysts, finance teams, store managers, agencies, BI pipelines, and support teams validating analytics CSV exports.
Quick answer
Save the original bytes before opening the file. Record content hash, encoding, delimiter, line endings, header order, quoting, row count, and expected totals. Inspect representative rows as raw text and import columns with explicit types. Compare raw, parser, and spreadsheet values for IDs, SKUs, timestamps, currency, refunds, formulas, and empty fields. Escape formula-leading user content and keep the untouched export as evidence.
Test scenarios to run
Run the same controlled fixture across these branches. Write down the expected result before testing so a surprising response is easy to identify.
| Scenario | Fixture | Expected result |
|---|
| SKU 000184 | Identifier imported as text | Leading zeros preserved |
| Long order ID | More than 15 digits | No scientific notation or rounding |
| Formula prefix | Equals, plus, minus, or at sign | Safely escaped as text |
| Refund timestamp | UTC near local midnight | Correct business date and total |
Diagnostic table
Use this table to connect the observed behavior to evidence and a verification step.
| Action | Evidence to collect | How to verify |
|---|
| Preserve and hash the raw export before opening it. | Hash and archive the untouched CSV with export type, filters, timezone, WooCommerce version, role, and report arguments. | Raw hash, encoding, row count, headers, and totals are recorded. |
| Define explicit column types, timezone, decimal, and null rules. | Inspect encoding, byte-order mark, delimiter, quoting, line endings, headers, raw row count, and malformed records. | IDs, SKUs, dates, decimals, refunds, and Unicode survive typed import. |
| Escape formula-leading text at export or safe-import boundaries. | Create fixtures for leading zeros, long integers, ISO timestamps, localized dates, decimal currency, refunds, Unicode, commas, quotes, newlines, and formula prefixes. | Formula-leading values open as text rather than executable formulas. |
| Import through a controlled parser or typed spreadsheet workflow. | Import with explicit text, date, decimal, and identifier types instead of double-clicking the file. | Spreadsheet and BI totals reconcile to the untouched export. |
What to check first
- Hash and archive the untouched CSV with export type, filters, timezone, WooCommerce version, role, and report arguments.
- Inspect encoding, byte-order mark, delimiter, quoting, line endings, headers, raw row count, and malformed records.
- Create fixtures for leading zeros, long integers, ISO timestamps, localized dates, decimal currency, refunds, Unicode, commas, quotes, newlines, and formula prefixes.
- Import with explicit text, date, decimal, and identifier types instead of double-clicking the file.
- Reconcile API or UI totals, raw CSV values, parser output, spreadsheet cells, and downstream BI records.
Field notes
- Never overwrite the original export with the spreadsheet version.
- Use a structured parser for automation and publish its schema with the report.
- Treat formula-leading customer or product text as a security issue, not a formatting preference.
Useful command or data shape
Adapt paths, IDs, and privacy handling to the site before running commands or storing data on production.
field,raw,spreadsheet_default,typed_import,expected,result
sku,000184,184,000184,000184,pass_typed
order_id,93482017465012987,9.3482E+16,93482017465012987,exact,pass_typed
created_at,2026-08-16T00:15:00Z,8/15/26,2026-08-16T00:15:00Z,utc,inspect
name,=SUM(A1:A2),formula,'=SUM(A1:A2),text,safe
Why this usually happens
- CSV has no native column types, so applications guess.
- Spreadsheet numeric precision can be lower than identifier length.
- Locale and timezone settings can reinterpret dates, decimals, and delimiters.
Decision rule
Approve an analytics file when its raw bytes and typed parser reconcile to the source report, dangerous text is neutralized, and the documented spreadsheet import preserves every identifier, date, and monetary value.
Production verification checklist
- Raw hash, encoding, row count, headers, and totals are recorded.
- IDs, SKUs, dates, decimals, refunds, and Unicode survive typed import.
- Formula-leading values open as text rather than executable formulas.
- Spreadsheet and BI totals reconcile to the untouched export.
Safe fix order
Use a sequence that makes each result easy to prove. Stop when new evidence changes the scope or owner of the problem.
- Preserve and hash the raw export before opening it.
- Define explicit column types, timezone, decimal, and null rules.
- Escape formula-leading text at export or safe-import boundaries.
- Import through a controlled parser or typed spreadsheet workflow.
- Reconcile raw values, business totals, and downstream records before distribution.
Mistakes to avoid
- Changing production before preserving a reproducible fixture, timestamps, and the current result.
- Treating one successful screen, request, or export as proof that every downstream system agrees.
- Removing logs, identifiers, or rollback evidence before the owner and failure boundary are known.
- Testing only an administrator session instead of the roles, devices, consent states, and failure paths users actually have.
Questions teams ask during testing
Can this be tested on production?
Use production for read-only confirmation and a narrow synthetic fixture. Perform destructive, version, cache-policy, queue, or schema changes on staging first, then promote the smallest proven change.
What evidence should be kept?
Keep versions, fixture IDs, UTC timestamps, request or export evidence, expected and actual results, the decision owner, rollback point, and the final clean verification. Redact personal data.
When is the work finished?
Finish when the canonical user path passes, downstream records reconcile, failure cases are understood, monitoring is in place, and an established page links to the new guide with useful context.
What to tell the client or owner
Give the owner the affected versions, exact fixture, stable IDs, UTC timeline, before and after evidence, decision, rollback point, unresolved risks, and next review date.
When HandL WP should help
Bring in help when this affects leads, checkout, search visibility, security, paid media reporting, or a client production site. HandL WP can trace the issue through WordPress, hosting, cache, tracking, and Search Console, then verify the workflow after the technical fix.
If this is active on a production site, audit a WooCommerce analytics export.
Related HandL WP guides
Use these related guides when the same issue touches tracking, security, checkout, or crawler visibility.
Add the formula-injection fixture
Protect the raw-text workflow with the WooCommerce CSV formula-injection escaping test, covering dangerous prefixes, safe spreadsheet imports, identifiers, refunds, and reconciled totals.
Roundtrip product and variation CSV data
Use the WooCommerce 11.1 product CSV roundtrip test to preserve raw encoding, SKUs, variation parents, attributes, tax, stock, images, and idempotent second-import behavior.
Helpful references