Turn Messy WhatsApp Orders into a Clean Order Sheet with Gemini and Python

If you sell through WhatsApp, you know what an order looks like. It’s never a form. It’s something like this:

kak mau order hoodie hitam L 2, sama tote bag 1 ya. kirim ke Jl. Melati No. 12 RT 03/RW 05, Sukajadi, Bandung 40162. a.n. Rina, 0812-3456-7890. JNE aja kak

Then someone retypes all of that into a spreadsheet: name, phone, address, items, sizes, quantities, courier. Then they work out the total and type a confirmation back. At 30 orders a day, that’s an hour or more of copying, and every typo in an address is a returned package.

This guide builds a small Python script that does the boring part:

1. You paste a batch of order chats into a text file.
2. Gemini 3.5 Flash-Lite pulls out the name, phone, address, courier and items as structured JSON, matched against your product catalog.
3. Python, not the AI, checks the result: does the SKU exist, does the phone number actually appear in the chat, is the address too short to ship to?
4. Python works out the subtotal from your price list, so the AI never does the maths.
5. You get orders.csv for packing and shipping, and confirmations.txt with a reply for each customer, ready to paste.

Anything that looks wrong is marked REVIEW instead of being guessed. AI cost: roughly $0.002 per order. Build time: about 40 minutes.

This is a quality-of-life tool first. At the end there’s an honest section on selling it as a setup service to other sellers.

What you need

Pricing as of October 2026 (check ai.google.dev/pricing before you rely on it, because Google changes models and prices often):

• Gemini 3.5 Flash-Lite (gemini-3.5-flash-lite, paid tier): $0.30 per 1M input tokens, $2.50 per 1M output tokens. This is the default in the script.
• Free tier: rate-limited, fine for testing with made-up orders. Don’t put real customer data through it. Google’s terms allow free-tier content to be used to improve its products. Paid-tier content isn’t used that way.

Older tutorials use gemini-2.5-flash. Google is now limiting the 2.5 models to accounts that have used them before and recommends 3.5 Flash-Lite for new projects, so use a 3.x model.

How the cost works out

For each chat, the script sends your catalog, the instructions and the chat itself:

• Catalog of 40 products with nicknames: about 1,200 tokens
• Instructions: about 350 tokens
• A typical order chat (a few messages): about 150–300 tokens

That comes to about 1,800 input tokens per order. The JSON answer plus Flash-Lite’s minimal “thinking” is roughly 400 output tokens.

• Input: 1,800 × $0.30/1M = $0.00054
• Output: 400 × $2.50/1M = $0.0010
• Total: about $0.0016 per order, so roughly $1.60 per 1,000 orders

A big catalog costs more because it’s sent with every order. With 500 products you’d be at about $0.01 per order, which is still small, but see the Reality check for a better approach. The script prints real token counts after each run, so you can check these numbers on your own data.

Step 1: Set up the project

mkdir wa-orders && cd wa-orders
python3 -m venv .venv
source .venv/bin/activate          # Windows: .venv\Scripts\activate
pip install google-genai pydantic

export GEMINI_API_KEY="your-key-here"   # Windows PowerShell: $env:GEMINI_API_KEY="your-key-here"

To use paid-tier pricing and data terms, turn on billing for the Google Cloud project behind your key in AI Studio. Set a budget alert in Google Cloud Billing too. At these prices you won’t need it, but a bug in a loop is cheaper to catch at $5 than at $500.

Step 2: Write your catalog

The catalog is what makes this work. Without it the model invents product names. With it, the model has to pick from a fixed list of SKUs, and Python can check that pick.

Save as catalog.csv:

sku,name,price,aliases
HD-BLK-M,Hoodie Black M,185000,hoodie hitam M; hodie item M
HD-BLK-L,Hoodie Black L,185000,hoodie hitam L; hodie item L
HD-BLK-XL,Hoodie Black XL,195000,hoodie hitam XL; hodie item XL
HD-NVY-L,Hoodie Navy L,185000,hoodie navy L; hoodie biru dongker L
TS-WHT-L,T-Shirt White L,89000,kaos putih L; tshirt putih L
TOTE-01,Canvas Tote Bag,65000,tote; totebag; tas kanvas

Spend ten minutes on the aliases column. Scroll back through a few weeks of chats and write down what customers actually call things: misspellings, local words, “yang item”, “yang dongker”. This column decides how many orders end up in REVIEW, far more than the choice of model does.

One row per variant (size, colour) is deliberate. “Hoodie black, size L” becomes one SKU, so stock, packing lists and totals stay simple.

Step 3: Get the chats out of WhatsApp

Keep this manual and boring:

• WhatsApp Web or Desktop: select the customer’s messages, copy, paste into chats.txt.
• Phone: long-press a message → Copy (select several first to copy them together), then send it to yourself or paste into a notes app synced to your computer.

Put a line with just --- between customers:

kak mau order hoodie hitam L 2, sama tote bag 1 ya. kirim ke Jl. Melati No. 12 RT 03/RW 05,
Sukajadi, Bandung 40162. a.n. Rina, 0812-3456-7890. JNE aja kak
---
Halo min, kaos putih L satu. Budi Santoso, Perum Griya Asri Blok C7, Sidoarjo 61252.
wa 085712345678, sicepat
---
min yang biru kemarin masih ada? kalau ada aku ambil 1 ya, alamat sama kayak dulu

The third one is there on purpose. It has no address, no name, and “the blue one from yesterday” isn’t a product. A good tool should refuse to turn that into an order, and this one does.

Why not export the whole chat with WhatsApp’s Export chat feature? You can, but an export contains every message ever sent in that chat, including old orders and your own replies. Splitting that reliably is harder than copying the five messages that matter.

Step 4: The script

Save as orders.py:

import argparse, csv, os, re, sys, time
from pathlib import Path

from google import genai
from google.genai import errors, types
from pydantic import BaseModel

MODEL = os.environ.get("ORDER_MODEL", "gemini-3.5-flash-lite")
PRICE_IN, PRICE_OUT = 0.30, 2.50   # USD per 1M tokens, Gemini 3.5 Flash-Lite paid tier
MAX_QTY = 20                       # anything above this goes to review

class Item(BaseModel):
    sku: str
    qty: int
    note: str

class Order(BaseModel):
    customer_name: str
    phone: str
    address: str
    courier: str
    items: list[Item]
    unclear: list[str]

client = genai.Client()            # reads GEMINI_API_KEY

def load_catalog(path):
    with open(path, newline="", encoding="utf-8") as f:
        return {r["sku"].strip().upper(): r for r in csv.DictReader(f)}

def split_chats(path):
    text = Path(path).read_text(encoding="utf-8")
    return [b.strip() for b in re.split(r"^\s*---\s*$", text, flags=re.M) if b.strip()]

def build_prompt(catalog, chat):
    products = "\n".join(f'{sku} | {r["name"]} | {r.get("aliases", "")}'
                         for sku, r in catalog.items())
    return f"""You extract online shop orders from Indonesian WhatsApp chats.
Rules:
- Only use SKUs from the catalog below. Match by name and aliases.
- If an item cannot be matched to exactly one SKU, do not guess. Put the customer's words in "unclear".
- Copy the customer name, phone and address exactly as written. If one is missing, leave it empty
  and add "missing name", "missing phone" or "missing address" to "unclear".
- Never invent or complete a phone number or address.
- qty is the number of pieces. If no number is given for an item, use 1.
- courier: the shipping service mentioned (JNE, J&T, SiCepat, etc.), or empty.
- note: size or colour remarks that the SKU does not cover, otherwise empty.

Catalog (SKU | name | aliases):
{products}

Chat:
{chat}"""

def extract(prompt):
    for attempt in range(3):
        try:
            resp = client.models.generate_content(
                model=MODEL,
                contents=prompt,
                config=types.GenerateContentConfig(
                    response_mime_type="application/json",
                    response_schema=Order,
                    thinking_config=types.ThinkingConfig(thinking_level="minimal"),
                ),
            )
        except errors.APIError as e:
            print(f"  API error {e.code} (attempt {attempt + 1}), retrying", file=sys.stderr)
            time.sleep(5 * (attempt + 1))
            continue
        if resp.parsed:
            return resp.parsed, resp.usage_metadata
    return None, None

def digits(s):
    return re.sub(r"\D", "", s)

def check(order, chat, catalog):
    problems = list(order.unclear)
    squeezed = re.sub(r"[\s\-.()+]", "", chat)
    phone = digits(order.phone)
    if len(phone) < 9:
        problems.append("phone missing or too short")
    elif phone[-9:] not in squeezed:
        problems.append("phone not found in chat")
    if len(order.address) < 20 or not re.search(r"\d", order.address):
        problems.append("address looks incomplete")
    if not order.items:
        problems.append("no items")
    subtotal, lines = 0, []
    for it in order.items:
        sku = it.sku.strip().upper()
        if sku not in catalog:
            problems.append(f"unknown SKU {it.sku}")
            continue
        if not 1 <= it.qty <= MAX_QTY:
            problems.append(f"check qty {it.qty} for {sku}")
        price = int(catalog[sku]["price"])
        subtotal += price * it.qty
        lines.append((sku, catalog[sku]["name"], it.qty, price, it.note))
    return problems, subtotal, lines

def rupiah(n):
    return "Rp" + f"{n:,}".replace(",", ".")

def confirmation(order, lines, subtotal):
    items = "\n".join(f"- {name} x{qty} = {rupiah(price * qty)}" + (f" ({note})" if note else "")
                      for _, name, qty, price, note in lines)
    courier = f" {order.courier}" if order.courier else ""
    return (f"Halo kak {order.customer_name}, pesanan kakak:\n{items}\n"
            f"Subtotal: {rupiah(subtotal)} (belum termasuk ongkir{courier})\n"
            f"Dikirim ke: {order.address}\nMohon dicek ya kak, kalau sudah sesuai kami kirim totalnya.")

def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("chats")
    ap.add_argument("--catalog", default="catalog.csv")
    ap.add_argument("--out", default="orders.csv")
    args = ap.parse_args()

    catalog = load_catalog(args.catalog)
    chats = split_chats(args.chats)
    tokens_in = tokens_out = 0
    rows, replies = [], []

    for n, chat in enumerate(chats, 1):
        order, usage = extract(build_prompt(catalog, chat))
        if order is None:
            rows.append({"no": n, "status": "REVIEW", "problems": "API failed", "chat": chat})
            continue
        tokens_in += usage.prompt_token_count or 0
        tokens_out += (usage.candidates_token_count or 0) + (usage.thoughts_token_count or 0)
        problems, subtotal, lines = check(order, chat, catalog)
        status = "REVIEW" if problems else "OK"
        rows.append({
            "no": n, "status": status, "name": order.customer_name,
            "phone": digits(order.phone), "address": order.address, "courier": order.courier,
            "items": "; ".join(f"{sku} x{qty}" for sku, _, qty, _, _ in lines),
            "subtotal": subtotal, "problems": "; ".join(problems), "chat": chat,
        })
        if status == "OK":
            replies.append(f"#{n} {order.customer_name}\n{confirmation(order, lines, subtotal)}\n")
        print(f"#{n}: {status} {'; '.join(problems)}")

    fields = ["no", "status", "name", "phone", "address", "courier",
              "items", "subtotal", "problems", "chat"]
    with open(args.out, "w", newline="", encoding="utf-8-sig") as f:
        w = csv.DictWriter(f, fieldnames=fields)
        w.writeheader()
        w.writerows(rows)
    Path("confirmations.txt").write_text("\n".join(replies), encoding="utf-8")

    cost = tokens_in / 1e6 * PRICE_IN + tokens_out / 1e6 * PRICE_OUT
    ok = sum(r["status"] == "OK" for r in rows)
    print(f"\n{ok}/{len(rows)} OK, {len(rows) - ok} need review")
    print(f"Tokens: {tokens_in} in, {tokens_out} out, about ${cost:.4f}")

if __name__ == "__main__":
    main()

The file is written as utf-8-sig so Excel opens it with the right encoding, and the original chat is kept in the last column so you can check any row without going back to WhatsApp.

Step 5: Run it

python orders.py chats.txt --catalog catalog.csv

With the three sample chats, you should see something like:

#1: OK
#2: OK
#3: REVIEW missing name; missing phone; missing address; yang biru kemarin; phone missing or too short; address looks incomplete; no items

2/3 OK, 1 need review
Tokens: 1850 in, 1104 out, about $0.0033

The input count is low because the sample catalog has only 6 products; a 40-product catalog pushes it to roughly 1,800 tokens per order. Your token counts and the exact wording of the unclear notes will be a bit different. Open orders.csv in Excel or Google Sheets and sort by status. Then open confirmations.txt. Order #1 reads:

Halo kak Rina, pesanan kakak:
- Hoodie Black L x2 = Rp370.000
- Canvas Tote Bag x1 = Rp65.000
Subtotal: Rp435.000 (belum termasuk ongkir JNE)
...

Order #3 gets no confirmation. You reply to that customer yourself and ask which product they mean and where to send it. That’s the correct result.

Step 6: Why the checks matter

Flash-Lite is good at this job, but “good” means occasional mistakes, and in shipping, one mistake costs money. Each check in check() catches a specific failure:

• Phone not found in chat. Models sometimes “fix” a number, dropping or adding a digit. The script compares the last 9 digits with the chat text (spaces, dashes and dots removed). If they aren’t there, the number came from the model, not the customer.
• Unknown SKU. Even with a schema, a model can return a SKU that looks right but isn’t in your list, like HD-BLACK-L. Python only accepts exact matches.
• Quantity limits. In “hoodie hitam L 2”, it’s easy to confuse which number is the size and which is the quantity. Anything over MAX_QTY is flagged. Set it to fit your shop.
• Totals in Python. The model never sees a price, so it can’t get a sum wrong. Prices come only from your CSV.

When you first start, check every OK row against the chat for a week anyway. Count how many were wrong. If it’s zero out of a few hundred, start trusting the OK rows and only read the REVIEW ones. If it isn’t zero, look at the pattern. It’s nearly always a missing alias.

Honest cost breakdown

Time saved: typing one order into a sheet by hand, then writing a confirmation, takes most people 1–3 minutes. Copying a chat into the file and checking an OK row takes about 15–30 seconds. Those are estimates, so time yourself on 20 orders before and after. At 30 orders a day, a realistic saving is around 30–60 minutes a day, and fewer wrong addresses.

Turning it into income

The script is useful to you. It’s also something other small sellers will pay to have set up, because the hard part isn’t the code, it’s building the catalog and alias list from their own chats.

A realistic offer:

• Setup: you build their catalog.csv from their price list and a few weeks of chats, install the script on their laptop, and show the admin how to use it. Two to four hours of work.
• Pricing: no market rate exists for this, so test it. A one-off setup fee somewhere around Rp500,000–1,500,000 is a sensible starting range for an Indonesian small online shop, plus a small monthly fee if you maintain the catalog when products change. Their API bill stays theirs, on their own key.
• Where to find clients: sellers you already buy from, local UMKM groups, and Facebook groups for online sellers. Show it working on their own messy chats, with their permission. That sells it far better than a demo with clean sample data.

Don’t promise “fully automatic orders”. Promise “admin spends minutes instead of an hour, and nothing ships without a check”. You can deliver that.

Reality check: what does NOT work

• Fully automatic WhatsApp bots built on unofficial libraries. Tools that drive WhatsApp Web (whatsapp-web.js, Baileys and similar) break WhatsApp’s terms, and numbers using them get banned. Losing the shop’s main number is far worse than an hour of typing. If you need real automation, the official WhatsApp Business Platform exists, with per-message pricing and a Meta business verification process. That’s a bigger project than this guide.
• Voice notes and screenshots. The script reads text only. Customers who send a voice note or a photo of a handwritten list still need a human, or a separate transcription step (see our Whisper guide).
• Proof of payment. Don’t let AI decide whether a transfer arrived. Check your bank or e-wallet app. Edited transfer screenshots are a common scam.
• “Same address as last time.” The model can’t see your history and won’t guess. That’s correct behaviour, so don’t “fix” it with a prompt that invites guessing.
• Huge catalogs. Past a few hundred SKUs, sending the full list every time gets slower and less accurate. Filter first: send only the products whose name or alias shares a word with the chat.
• Shipping costs. The script deliberately leaves them out. Ongkir depends on weight, origin and courier service. Use the courier’s or marketplace’s own calculator.
• Customer data. Names, phone numbers and addresses are personal data under Indonesia’s PDP Law (UU No. 27/2022). Use the paid tier, don’t paste chats into random free web tools, and delete old chats.txt files once orders ship.
• Skipping the alias work. Most bad results come from a thin catalog, not a weak model. A more expensive model won’t fix a missing “hodie item”.

Final checklist

• catalog.csv has one row per variant, real prices, and aliases taken from real chats
• API key on a billed project, budget alert set, no real data on the free tier
• Tested on 20+ real chats, with every OK row checked against the chat for the first week
• MAX_QTY set to fit your shop
• Payment checked in your bank app, never by the AI
• Old chat files deleted once orders ship

Start with tomorrow’s orders. Paste them in, run the script, and time it.