How to Convert Order Emails to JSON

Overview

Order confirmation emails from e-commerce platforms contain all the data you need to update your internal systems: order number, customer details, line items, totals, shipping address, and tracking information. Converting these emails from prose to structured JSON enables:

• Automatic order record creation in your fulfillment system or ERP
• Real-time inventory updates when orders arrive
• Automatic customer notification workflows
• Sales analytics and reporting from email data
• Supplier order notifications routed to your purchasing system

JsonHook receives these order emails, parses the MIME structure, and delivers the full content as JSON to your webhook. Your handler applies extraction logic to pull the structured order data out of the email body and insert it into your systems.

Prerequisites

What you need to build an order email parser:

• A JsonHook inbound address that receives forwarded order confirmation emails
• Sample order emails from each e-commerce platform you receive from (at least 10 real examples per platform)
• A database or ERP API to store extracted order data
• Familiarity with your order email format — examine samples carefully to identify consistent extraction targets

Step-by-Step Instructions

Build an order email-to-JSON conversion pipeline:

1. Collect and analyze sample order emails. Look for consistent patterns: does the order number always appear as "Order #12345"? Are line items in a table or as bullet points? Is the total on a line by itself?
2. Create a JsonHook address for order emails and configure forwarding from your order notification mailbox.
3. Write extraction functions for each platform/format. Test against your samples before connecting to live email.
4. Store the structured order data in your database with appropriate schema.
5. Trigger downstream actions: update inventory, create fulfillment records, notify the warehouse team.
6. Monitor extraction accuracy — alert when the order number or total cannot be extracted (likely a format change in the sender's template).

Code Example

Order extraction function handling multiple common e-commerce email formats:

interface ParsedOrder {
  orderNumber:  string | null;
  total:        string | null;
  customerEmail: string | null;
  shippingAddress: string | null;
  trackingNumber: string | null;
  platform:     string;
}

function detectPlatform(from: string, subject: string): string {
  if (from.includes("shopify")) return "shopify";
  if (from.includes("woocommerce") || from.includes("wordpress")) return "woocommerce";
  if (from.includes("amazon")) return "amazon";
  if (from.includes("etsy")) return "etsy";
  return "unknown";
}

function extractOrder(email: any): ParsedOrder {
  const text = email.textBody ?? "";
  const platform = detectPlatform(email.from.toLowerCase(), email.subject);

  // Multi-platform order number patterns
  const orderPatterns = [
    /Orders+#?s*([A-Z0-9-]{4,20})/i,     // Generic
    /#([0-9]{4,10})/,                         // Short numeric
    /Orders+number[:s]+([A-Z0-9-]+)/i,    // "Order number: ..."
    /Confirmation[:s#]+([A-Z0-9-]{4,20})/i // "Confirmation: ..."
  ];

  let orderNumber: string | null = null;
  for (const pattern of orderPatterns) {
    const m = text.match(pattern);
    if (m) { orderNumber = m[1]; break; }
  }

  const totalMatch = text.match(/(?:Orders+)?Total[:s]+$?([d,]+.d{2})/i);
  const emailMatch = text.match(/(?:to|for|email)[:s]+([a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+)/i);
  const trackingMatch = text.match(/[Tt]racking[:s#]+([A-Z0-9]{10,30})/);

  return {
    orderNumber,
    total:          totalMatch?.[1] ?? null,
    customerEmail:  emailMatch?.[1] ?? null,
    shippingAddress: null, // Multi-line — complex to extract from text
    trackingNumber: trackingMatch?.[1] ?? null,
    platform,
  };
}

// Webhook handler
app.post("/webhooks/orders", async (req, res) => {
  // ... verify HMAC ...
  const { email, deliveryId } = JSON.parse(req.body.toString());
  const order = extractOrder(email);

  if (!order.orderNumber) {
    console.warn(`Failed to extract order number from ${deliveryId}`);
    await notifyOps(`Order extraction failed: ${email.subject}`);
    return res.sendStatus(200);
  }

  await db.orders.upsert({
    deliveryId,
    orderNumber:   order.orderNumber,
    total:         order.total,
    platform:      order.platform,
    customerEmail: order.customerEmail,
    rawSubject:    email.subject,
    rawFrom:       email.from,
    receivedAt:    new Date(),
  });

  console.log(`Order ${order.orderNumber} (${order.platform}) stored`);
  res.sendStatus(200);
});

Common Pitfalls

Order email conversion pitfalls:

• E-commerce platforms update their email templates. Shopify, WooCommerce, and others periodically redesign their confirmation emails. Set up monitoring for extraction failures so you are alerted when a template change breaks your parser before it causes data loss.
• Order numbers with special formats. Some platforms use formats like US-1234-5678, ORD/2025/0042, or UUID-like strings. Test your order number regex against real samples from each platform and consider platform-specific patterns.
• Multi-currency and locale variations. Totals may appear as $123.45, €123,45, £123.45, or 123.45 USD. Make your total extraction pattern flexible enough to handle multiple currency formats if you receive international orders.
• Processing the same order multiple times. Some platforms send multiple emails per order (confirmation, shipped, delivered). Use the order number as an idempotency key — upsert rather than insert, and track which events have been processed for each order.
• Not storing the raw email alongside extracted data. Store the full JSON payload (or at minimum the textBody and htmlBody) alongside the extracted fields. This lets you re-run extraction with improved logic against historical emails when the parser is updated.