When new orders arrive from your storefront or intake forms, the raw data is rarely perfectly formatted for your downstream systems. This guide walks you through the essential steps to clean, convert, and normalize order fields so they look great in your CRM, emails, and databases.

flowchart TD
    Raw["Raw Order Data"] --> Lines["Clean Line Items"]
    Raw --> Finance["Format Currency"]
    Raw --> Dates["Convert Dates"]
    Raw --> SqFt["Normalize SqFt"]
    
    Lines --> Output["Ready for CRM, Email, & Billing"]
    Finance --> Output
    Dates --> Output
    SqFt --> Output

Processing Line Items

Orders often contain multiple products or services bundled together in a single "line items" array. To make this data readable in emails or text messages, you need to flatten and format it.

1. 1

   Strip HTML tags

   Raw product descriptions often include hidden HTML. Use a text formatting step to remove all HTML tags from the original items list so you are left with plain text.

2. 2

   Encode special characters (Optional)

   If you plan to pass the line items through a URL (like a webhook or a pre-filled form link), replace ampersands (&) with their URL-encoded equivalent (%26).

3. 3

   Convert to readable text

   Convert the array of items into a single text block. Depending on where the data is going, choose the right separator:

   • For Emails: Separate items using `` so they appear on new lines in HTML emails.

   • For Plain Text: Separate items using a comma , or standard [:newline:] characters.

   • For URLs: Separate items using %0A (the URL-encoded newline character).

Formatting Currency

To ensure your financial data is consistent, standard order fields like Subtotal, Sales Tax, and Total should be explicitly formatted.

Pass these fields through a number formatter with the following configuration:

Converting Dates and Timezones

System-generated order dates are usually captured in UTC (Coordinated Universal Time). To make these dates useful for your local team, they need to be converted to your operational timezone.

1. Timezone Conversion: Convert the raw order date from UTC to US/Eastern (or your local timezone). Format the output to a clean YYYY-MM-DD standard.

2. Scheduling Checks: If you need to calculate a follow-up or scheduling window, use a date manipulation step to add time (e.g., +1 day) to the current date.

Normalizing Square Footage (SqFt)

For property or service-based orders, Square Footage (SqFt) is a critical metric. However, users might type "2000 sq ft", leave it blank, or enter "0".

To clean this up, use a two-part process:

1. Extract the number

Use a number extraction tool to pull only the digits out of the raw input. This turns "2,500 sq ft" into simply 2500.

2. Apply a fallback formula

To prevent division-by-zero errors in your pricing calculators, use a spreadsheet-style formula to default empty or zero values to 1.

IF(OR(SqFt="", SqFt="0"), 1, SqFt)

Handling Custom Questions

If your order form includes custom questions (like "How did you hear about us?"), you can use a Lookup Table to format the output.

Set up a fallback value so that even if the exact match isn't found, the raw answer is still passed through clearly:

Referral Source: {{Question_Answer}}