Prepare an Order Export for Customer Analysis: A CSV and Excel Checklist

A Scenario to Set the Stakes

Imagine a fictional Thai online accessories shop — call it Saifah Studio — that has been running for three years and has accumulated roughly 18,000 order records across two sales channels. The operations lead wants to produce an RFM (Recency, Frequency, Monetary) segmentation of customers so the team can prioritise who to re-engage. She exports a CSV from her shop platform, double-clicks it in Excel, and notices immediately that the date column looks odd: some entries are left-aligned text, others are right-aligned numbers, and a spot-check reveals that an order she placed on 5 April is showing as 4 May. Without a systematic pre-import checklist, she would have fed corrupted dates into every Recency calculation downstream.

Start by keeping an untouched copy of the original export. This checklist helps a shop prepare a reviewable file before choosing an analysis tool: declare the row structure, preserve identifiers, verify dates, and reconcile totals. It is a preparation method, not a universal import contract. Confirm the receiving tool’s documented field requirements and test a small sample before loading a full export. Do not replace a working source file with an edited spreadsheet whose changes cannot be explained.

Worked-File Checklist: Four Core Fields and Two Recommended Fields

The fictional one-row-per-order file in this guide requires four core fields for a complete RFM example. The table separates these from two recommended optional fields and states their role and basic checks. These are the teaching file’s rules, not a universal import schema. Before using a real export, confirm the documented requirements of the chosen analysis tool.

The four core fields are order_id, customer_id, order_date and order_value. The two recommended optional fields are order_status and product_category: keep them when you need to identify cancelled or test orders, or analyse categories. Channel is another optional field for files combining platforms; it preserves provenance and lets the team check identifiers that may overlap between sources.

The worked file in this guide has one row per order. If your export has one row per product line, an order_id may correctly appear several times. Keep that distinction in your handoff notes: count distinct orders separately from file records, and check whether an order total is repeated on every line before summing revenue. Repeated IDs are not automatically duplicates to delete. Match a small order against the platform before deciding how to aggregate it.

Valid vs. Invalid Rows — A Worked Teaching Example

The table below contains four entirely fictional order rows. They are chosen to illustrate distinct failure modes, not to represent any real shop's data. Every field is declared so the example is fully reproducible.

Row 10001 is fully valid: all required fields are present and correctly typed, and the date is in unambiguous ISO 8601 format. This row contributes to Recency, Frequency, and Monetary calculations without reservation.

Row 10002 has a blank order_value. This is not a catastrophic error; the row still carries a valid customer_id and order_date, so it contributes to Recency (most recent purchase date) and Frequency (distinct order count) without restriction. What it cannot support is any revenue-based Monetary calculation. The correct label for this row is 'Limited — unsuitable for revenue-based Monetary analysis'. Dropping this row entirely to avoid the blank would silently reduce Frequency counts for the affected customer, which is the worse error.

Row 10003 has a missing customer_id. Without that identifier, the row cannot be linked to any buyer, so it cannot contribute to customer-level RFM segments or cohort membership. It can still contribute to aggregate order-level revenue totals if you need an order-level revenue total for the shop as a whole. The correct label here is 'Limited — unusable for customer-level RFM or cohort linkage; usable only for order-level revenue totals'.

Row 10004 has the invalid date 2024-02-30. February does not have a thirtieth day, including in a leap year, so the value cannot identify a real purchase date. Check the order in the source platform and correct the export before using it in a date-based calculation. An English month-name value such as April 5 2024 is a different problem: some parsers accept it, but interpretation depends on language and settings. Do not label a parseable English date inherently invalid.

Ambiguous Day and Month Values

The text 04/05/2024 can mean 4 May or 5 April. In the fictional shop example, the export convention is day/month/year, so the intended value is 4 May. A month/day/year interpretation produces a different valid date. The value may look plausible even when the business meaning has changed. Inspect at least one value where both numbers are twelve or below and compare it with the source order.

Power Query uses locale information when converting text to dates. In its editor, Change Type → Using Locale lets you select the destination type and an appropriate locale. For example, a documented day/month/year Gregorian export can be tested with a matching locale such as English (United Kingdom). Do not assume that choosing Thai is correct for every file from a Thai shop: check the year convention, source format and actual converted values.

A text check can help find values that were left as strings, but it cannot prove the date is correct. A wrongly converted date can already be numeric; a deliberately preserved source column can correctly remain text. Keep the raw column beside the converted one, test a sample against the order platform, and investigate conversion errors before removing the raw values.

Treat these as two separate acceptance checks: the conversion must succeed, and the converted calendar day must match the source. A file that passes only the first check can still produce a misleading recency ranking. Record the declared date convention in the file handoff so another teammate can repeat the same check.

Sources: Microsoft: Set a locale or region for data (Power Query)

Choosing a Safe Date Format for the Export

Use a four-digit year and document the exact date convention. A declared YYYY-MM-DD value such as 2024-05-04 is easier to check than an unexplained slash-separated date, but still verify how the receiving tool parses it. State whether years are Gregorian or Buddhist Era; changing a cell display format does not itself convert the calendar convention.

If the platform exports day/month/year and you cannot change it, keep that format in the handoff notes. Use a matching locale during Power Query conversion, or the appropriate date order if your installed Text Import Wizard exposes that choice. Keep a small sample containing both an ambiguous date and a day greater than twelve, then compare the converted values with the original orders.

If timestamps include time zones, agree which calendar day the analysis uses before removing the time. For an original teaching example, 2024-05-05 00:30 at UTC+7 is 2024-05-04 17:30 UTC. A late-evening UTC+7 timestamp remains on the same UTC calendar day; it does not move forward into tomorrow. Preserve the original timestamp and offset until your team has chosen a consistent convention.

Controlled Import Methods — Never Double-Click a CSV

Use a controlled import when a file contains dates, long identifiers or Thai text. Excel can open CSV files directly, but the import workflow gives you a chance to inspect separators, encoding and column types. Keep the original export unchanged and create a separate checking workbook. The exact buttons depend on the installed Excel version and platform.

In a supported desktop version, start with Data → From Text/CSV, inspect the preview, and open the transformation editor before loading. Review any automatically added type-conversion step. Set identifier columns to Text at the earliest conversion stage, then convert the raw date column using a locale that matches the documented file convention. Preview a few known orders before accepting the result.

The legacy Text Import Wizard is another option where it is available. It supports explicit column formats, including Text for identifiers and a date-order choice for dates. Follow the controls in the version you actually have; do not assume desktop instructions apply unchanged to Excel for the web. If the necessary control is absent, preserve the file and choose a supported import method rather than improvising an irreversible conversion.

Changing a cell display format does not recover a lost identifier or prove that a converted date has the right meaning. If the first import changed the data, return to the untouched export and repeat the import with corrected settings. Record those settings and a few before/after checks so the next file can be prepared consistently.

Sources: Microsoft: Set a locale or region for data (Power Query) · Microsoft: Import or export text (.txt or .csv) files

The 1904 Date System Trap

If dates copied between workbooks are consistently about four years apart, check their date systems as well as their import settings. Excel supports 1900 and 1904 date systems. Microsoft documents a 1,462-day difference between the serial numbers for the same date. This is a possible explanation to investigate, not proof that every CSV date error comes from that option.

For example, Microsoft gives July 5, 2011 as serial 40729 under the 1900 system and 39267 under the 1904 system. This issue concerns date serial values and workbook interpretation; do not assume that a plain text value such as 2024-05-04 is automatically shifted merely because it came from a CSV.

In Windows Excel, inspect File → Options → Advanced → When calculating this workbook → Use 1904 date system. Mac controls differ. Before changing the option, save a copy and compare a known source date with the current workbook. Test the change on that copy and check all date columns, especially if the workbook already combines data from different sources.

Do not use switching the date-system option as a general repair for swapped day/month values. Those are separate problems. If serial dates cross workbook boundaries, document both systems and choose a conversion based on known source dates. If you cannot explain the discrepancy, return to the original export and rebuild the checking workbook rather than altering dates by a guessed offset.

Sources: Microsoft: Date systems in Excel

Long Numeric Identifier Handling

Excel's floating-point numeric type has approximately 15 significant decimal digits of precision. Any integer with more than 15 digits — which is common in platform-generated order IDs and some customer IDs — will have its trailing digits replaced with zeros when stored as a number. An order ID like 1234567890123456 arrives in the cell as 1234567890123450, and the change is invisible unless you compare against the source. Two distinct orders can end up with identical stored IDs, making the distinct-order count for Frequency completely unreliable.

Set ID columns to Text before a conversion to numbers can discard digits. In Power Query, inspect the automatically inserted Changed Type step: adding a Text conversion after a damaging numeric conversion does not reconstruct the original identifier. Replace or remove that earlier numeric conversion for the ID column and verify its raw value. The Text Import Wizard can also declare the column as Text before import.

As a practical spot-check, compare five or six long identifiers against the untouched export, character by character. Include one with a leading zero. This is a suggested checking method, not a claim about results from real customers. If digits were already lost, re-import from the original file with text handling applied before numeric conversion.

Sources: Microsoft: Keeping leading zeros and large numbers

Pre-Import Reconciliation: Record Count, Not Line Count

Before you import, you need a trusted source-record count to compare against what Excel receives. The natural instinct is to count lines — using wc -l on a Unix-like system or Get-Content in PowerShell on Windows — and subtract one for the header. This is not the same as a record count.

A CSV field can contain a quoted line break, so one record may occupy more than one physical line. Microsoft documents that Power Query Csv.Document with QuoteStyle.Csv treats quoted line breaks as part of the field. For an original example, an export with three order records and one two-line note has more than three data lines but still only three records. A raw line count is therefore the wrong reconciliation baseline.

Obtain the full record count with a CSV-aware import or parser that handles the file’s delimiter and quoting correctly. A preview may show only a sample: do not treat its displayed rows as the full-file total without checking. Record the full count before applying filters, grouping or deduplication. Compare it with the loaded data rows, then separately reconcile distinct orders and purchase value using the declared row structure.

The reconciliation equation is simple: source record count (excluding header) must equal post-import data row count. Any discrepancy — even a single row — is a signal to stop and investigate before analysis proceeds.

Sources: Microsoft: Csv.Document — quoted line breaks

Post-Import Row and Completeness Checks

Once data is in Excel, confirm the total row count using a method that does not depend on a specific column's completeness. If you converted your imported range to an Excel Table (Insert → Table), the function =ROWS(Table1) returns the exact number of data rows regardless of what any individual cell contains. This is the cleanest approach and is not affected by blanks.

If you did not create a Table, use COUNTA on a column that is guaranteed non-null by your own schema — order_id is the strongest candidate, since every row must have an order identifier for any analysis to be valid. Avoid using COUNTA or ROWS minus COUNTBLANK on columns that may legitimately be blank (order_value, product_category). Those formulas count non-blank cells in the chosen column, not total rows, and will silently undercount whenever blanks are present.

For the date column, a helper such as =ISTEXT(C2) reports whether the checked cell contains text. Adjust C2 to your actual date column. A TRUE result needs interpretation: the value may be deliberately preserved raw text, or conversion may not have happened. A FALSE result alone does not confirm the correct date. Check known orders and conversion errors rather than treating this helper as a complete date-validation test.

For order_value, count blanks with =COUNTBLANK(D2:D18001) and note the result. Blanks here are not errors per se, but they must be consciously excluded from any Monetary calculation rather than treated as zero — a blank order_value is different in meaning from a zero-value order. Document the blank count in your working notes so that any colleague reviewing results later understands why Monetary row counts may be smaller than Frequency row counts.

Encoding, Regional Settings, and What Not to Change

Microsoft documents that a UTF-8 CSV saved with a byte order mark can be opened normally in Excel; its guidance gives Power Query and Get Data From Text as alternatives for other cases. For a Thai shop, verify a few product names and Thai characters against the original export after loading. If they are garbled, check the file’s encoding in the import settings and repeat from the untouched source instead of manually retyping corrupted names.

For date disagreements, use a locale at the conversion step rather than assuming every colleague’s computer uses the same regional settings. This is a workflow recommendation: it keeps the chosen interpretation visible in the checking process. Record the chosen locale, calendar convention, separator and encoding with the file so a colleague can reproduce the preparation.

Retain a compact handoff note containing the export period, channel, one-row-per-order or line-item structure, full record count, distinct-order count, missing-value totals and unresolved exceptions. Someone who receives only an edited workbook cannot reliably infer those decisions. Keep the original export, checking workbook and handoff note together with access limited to the people who need them.

Sources: Microsoft: Opening CSV UTF-8 files correctly in Excel · Microsoft: Set a locale or region for data (Power Query)

Pre-Import Checklist Summary and Data Handoff

Work through each item in order. Do not proceed to import until every item is checked. Items that reveal problems should be corrected at the CSV level — in a plain-text editor or by re-exporting from the platform — rather than patched post-import whenever possible, because post-import corrections compound complexity and are harder to audit.

Item (a): Confirm the fields and row structure. In the complete RFM teaching file, check order_id, customer_id, order_date and order_value, and agree what each row represents. The receiving tool may require a different field list, naming convention or format. Read its documented requirements and test a small sample before loading a full export; this checklist does not guarantee that an arbitrary platform file is import-ready.

Item (b): Document the date and calendar convention. Test a sample of ambiguous values. In Power Query, choose a locale whose convention matches the file when converting to Date. Use DMY, MDY or YMD wording only where the installed Text Import Wizard offers date-order controls. Record both the setting and the source-to-result spot-check.

Item (c): Import ID columns as Text. Before clicking Load or Finish in any import wizard, set order_id and customer_id to Text type. Verify a sample of long IDs after import against the original CSV in a plain-text editor.

Item (d): Reconcile record counts. Obtain the CSV-aware record count before import. After import, use =ROWS(Table1) or COUNTA on order_id to confirm the post-import data row count. The two numbers must match.

Item (e): Check raw and converted dates against known orders. A text-type helper is useful for inspection but cannot certify correct parsing. Resolve impossible dates such as 2024-02-30 from the source platform, retain unresolved rows separately, and document which records are excluded from date-based analysis.

Item (f): When serial dates are copied between workbooks, check whether their date systems agree. Investigate a consistent 1,462-day discrepancy on a copy before making changes. This does not replace the day/month or timezone checks.

Item (g): Record missing purchase values and decide the analysis policy explicitly. In this teaching example, a blank value is unknown rather than zero: the verified purchase can support Recency and Frequency, but its missing amount cannot support a complete Monetary total. This describes the example’s rule, not a promise about how every tool imports or scores an incomplete row. Correct the missing value where possible before scoring.

After these checks, the file is better prepared for an import review, rather than guaranteed ready for every tool. Confirm the destination’s required fields, supported formats and handling of incomplete rows before loading it. CasperBrain uses four RFM quartiles, with 1 best and 4 weakest, and composite text scores such as 114. Treat these conventions as product-specific: a score from another system may use a different scale or direction and should not be copied across unchanged. Keep the scoring convention with any report so teammates interpret the same data consistently.

Sources and editorial method

Examples are fictional and contain no customer records. Prepared by the CasperBrain team with AI drafting assistance; facts and language are checked before publication.

• Microsoft: Set a locale or region for data (Power Query)
• Microsoft: Import or export text (.txt or .csv) files
• Microsoft: Date systems in Excel
• Microsoft: Opening CSV UTF-8 files correctly in Excel
• Microsoft: Keeping leading zeros and large numbers
• Microsoft: Csv.Document — quoted line breaks