Email Import

Gmail → Coda AI → Items + Kanban Loop Sizing (Workflow Design)

This document describes a Coda-native workflow that:

Connects to the Gmail Pack

Scans the last 365 days of email

Uses Coda AI to identify orders

Extracts line items + quantities

Suggests which products should become Items

Computes typical purchase quantities and uses them to size Kanban Loops (NPK + Planned)

It is designed to fit the data model in blueprint/blueprint.json:

Items (sync table)

Kanban Loops (local table)

New local tables under Settings & Templates → Email Import

⁠

High-level architecture

Inputs

Gmail Pack messages from the last year (query-driven)

Optional: known supplier list from Suppliers (local table)

Processing stages

Ingest: pull candidate emails (last 365d) into Email Order Inbox

Classify: AI decides which are purchase orders / order confirmations

Extract: AI returns structured JSON (supplier, order number/date, line items)

Normalize: convert JSON → Email Order Lines

Match: map raw line items → Items (existing or new suggestions)

Analyze: compute per-item purchase patterns → recommend NPK + Planned

Apply: update/create Kanban Loops rows (and optionally update Item vendor/order method fields)

Outputs

Items populated (new items suggested; import is human-approved)

Kanban Loops populated with NPK and Planned sized from email purchase history

⁠

Tables (what to create in Coda)

Create these local tables on a page: Settings & Templates → Email Import. They are also defined in blueprint/blueprint.json for the blueprint spec.

1) Email Import Runs

One row per scan / parsing run.

Goal: make the pipeline repeatable and incremental (“process only what’s new”).

Key columns:

Run ID (text; e.g. 2025-12-31-001)

Lookback Days (default 365)

Gmail Query (text; see below)

Status (SelectList: Draft / Running / Completed / Failed)

Counts: messages found, orders detected, lines parsed, new items suggested

2) Email Order Inbox

Stores ingested emails + AI results.

Key columns:

Gmail identifiers: Message ID, Thread ID

Metadata: Date, From, Subject, Snippet

Body: Body (Plain Text) (truncate long bodies if needed)

AI outputs:

Is Order? (AI) (true/false)

Order Type (AI) (SelectList: Order Confirmation / Purchase Order / Invoice / Shipping / Not an Order / Unknown)

Extracted Order JSON (AI) (text)

Pipeline: Parse Status (New / Parsed / Skipped / Error), Error

3) Email Order Lines

One row per extracted line item.

Key columns:

Email (lookup to Email Order Inbox)

Supplier Name, Order Number, Order Date

Raw: Item Name (Raw), SKU (Raw), Quantity, Unit

Matching:

Matched Item (lookup to Items sync table)

Match Confidence (0–1 or Low/Med/High)

Needs Review (checkbox), Approved (checkbox)

4) Email Purchase Patterns

Aggregated per-item statistics + recommendations.

Key columns:

Item (lookup to Items)

Orders (Count), Total Qty (Last 365d)

Typical Qty (Mode/Median), P75 Qty

Avg Days Between Orders

Recommended NPK, Recommended Planned

Apply to Kanban Loop (Button)

⁠

Gmail ingestion (how to get the last year of emails)

Suggested Gmail queries

Start conservative; widen later.

Base lookback: newer_than:365d

Typical order signals (choose a few based on your vendors):

subject:(order OR "purchase order" OR "PO #" OR "order confirmation" OR "order #")

("thank you for your order" OR "order total" OR "items ordered")

If you maintain Suppliers: from:(supplier-domain.com OR other-vendor.com)

Example combined query:

newer_than:365d (subject:(order OR "order confirmation" OR "purchase order") OR "thank you for your order" OR "order total")

Implementation pattern inside Coda

Use the Gmail Pack to pull message metadata + body into a table, then copy/transform into Email Order Inbox for AI processing (so you control what gets fed into AI).

If the Gmail Pack supports a sync table for messages, prefer that. Otherwise use a formula/action-driven table and a “Run Import” button that appends rows.

⁠

Coda AI: classification + structured extraction

A) Is Order? (AI) (binary classification)

AI input should be a compact “email summary” to reduce cost and improve accuracy:

From

Date

Subject

Snippet

First N characters of body (e.g., 5–10k max)

Prompt template:

You are classifying whether an email represents a purchase event that should influence inventory planning. Return only one of: ORDER, NOT_ORDER, UNCERTAIN. Treat these as ORDER: purchase order, order confirmation, invoice/receipt for purchased goods. Treat these as NOT_ORDER: shipping updates, marketing, password resets, newsletters.

Map results:

ORDER → true

NOT_ORDER → false

UNCERTAIN → false but set Needs Review

B) Extracted Order JSON (AI) (structured extraction)

Prompt template (return JSON only):

Extract a purchasing record from this email. Return valid JSON only (no markdown). If this is not an order, return: { "isOrder": false }. If it is an order, return:

{

"isOrder": true,

"orderNumber": "string|null",

"orderDate": "YYYY-MM-DD|null",

"supplierName": "string|null",

"supplierEmail": "string|null",

"currency": "string|null",

"lines": [

{

"name": "string|null",

"sku": "string|null",

"quantity": "number|null",

"unit": "string|null",

"unitPrice": "number|null"

}

]

}

Notes:

Quantity must be numeric (no words).

If multiple quantities exist (case + units), prefer the purchasable unit (case/carton) and keep unit text.

⁠

Normalization: JSON → Email Order Lines

Coda implementation options (pick one):

Option 1 (simplest): Human-assisted “Expand Lines” button

Filter Email Order Inbox to Is Order? (AI) = true and Parse Status = New.

Button “Expand Lines”:

Parses the JSON

Adds one row per line into Email Order Lines

Marks the email row as Parsed

Option 2 (more automated): Automation rule

Rule: “When Parse Status changes to New and Is Order? (AI) is true → run action expand”.

⁠

Matching extracted line items to Items

Use a deterministic-first strategy:

Exact SKU match (best)

Exact name match

Fuzzy match (contains/startsWith; optionally AI-assisted)

Otherwise create a Suggested Item record for human approval

Suggested columns on Email Order Lines:

Matched Item:

If SKU (Raw) is present: match on Items.internalSku

Else try name match

Match Confidence:

High if SKU match

Medium if exact name match

Low otherwise

Needs Review:

true if confidence is not High

Once approved:

Create a new Item row (if missing)

Set Item fields like:

PrimaryVendor / PrimaryOrderMethod = "Email" (if applicable)

PrimaryOrderQuantityAmount/Unit (optional: from typical quantity)

⁠

Sizing Kanban Loops from email purchase patterns

You asked for:

Which items should be in Items

Typical purchased quantities

Size Kanban loops accordingly

This section focuses on (2) and (3).

Purchase quantity statistics per item (last 365d)

Compute on Email Purchase Patterns using Email Order Lines filtered to approved + matched.

Recommended “typical purchase quantity”:

Prefer Mode if there is a clear most-common quantity.

Else use Median (robust to outliers).

Also compute P75 to see a “common upper size”.

Recommended NPK

Set NPK = typical purchase quantity, unless:

The typical purchase quantity is 1 but P75 is larger (suggests case buying) → use P75

Units vary (EA vs Case) → force review

Recommended Planned

To convert purchase history into a loop size without needing perfect demand modeling:

Let ordersPerYear = count of distinct orders containing the item

Let avgDaysBetweenOrders = 365 / ordersPerYear

Start with a 2-bin default:

Planned = 2

Increase to 3+ for highly frequent purchases:

If avgDaysBetweenOrders < 14 → Planned = 3

If < 7 → Planned = 4

This yields a usable starting point, and you can later replace it with PFEP demand + lead time formulas (KLT/RE/LO/WI/SA) if you capture usage rates.

⁠

Applying recommendations to Kanban Loops

On Email Purchase Patterns, add a button Apply to Kanban Loop that:

Finds/creates a Kanban Loops row for the Item

Writes:

NPK = Recommended NPK

Planned = Recommended Planned

Optionally sets Supplier/Route fields if you can infer them from the supplier

Guardrails:

Never overwrite an existing loop’s values without an “overwrite” checkbox.

Always log the change on Email Import Runs or in a change log.

⁠

Suggested rollout (pragmatic)

Phase 1: Get signal working (1–2 hours)

Gmail Pack connected

Ingest messages last 365d with a conservative query

AI classify + extract JSON

Normalize a handful of orders

Phase 2: Scale parsing (half day)

Add incremental runs

Improve query + supplier filtering

Add manual review queues (uncertain classification, low-confidence matches)

Phase 3: Close the loop (half day)

Create Email Purchase Patterns

Apply NPK/Planned recommendations into Kanban Loops

Generate order cards from loops as usual

Want to print your doc?
This is not the way.

Try clicking the ··· in the right corner or using a keyboard shortcut (

CtrlP

) instead.