Production 3.0 → Genius forecast
How the Ship-Month Revenue Forecast works
A one-off report that turns the Production 3.0 spreadsheet into a forward-looking dollar forecast, bucketed by the month each order is expected to ship. It uses Genius Sales # as the join key into Genius ERP, and Genius Expected Ready to Ship as the date that decides which month an order's dollars land in.
Genius Sales # → SalesOrderHeaderEntity.Code
Genius Expected Ready to Ship
Where the data comes from
Production 3.0 is Nathan's shorthand name for the shop's order/planning/production tracking spreadsheet. This report reads four of its tabs — the ones that represent live production work:
| Tab | What it tracks |
|---|---|
ATT/ AST | Attachment / accessibility structure systems |
CVR-AV | Cargo van ramps |
SDR | Sliding door ramps |
MISC | Miscellaneous production items |
Access is read-only end to end. The report authenticates as a dedicated
[email protected] Google service account whose OAuth scope
is spreadsheets.readonly — write attempts fail at the credential level, not just by convention.
The core idea: Genius Sales # is the index
Why the dollar amount is pre-tax only, with no shipping split
What we tested
We checked whether shipping/freight dollars could be broken out separately from product dollars for a
forward-looking (still in production) order. Freight charges live on
CustomerInvoiceHeaderEntity, joined to the sales order via CustomerOrder = SO.Code.
Tested a real in-production order (Consigli, SO 10535759, expected ship 4/22/26): the invoice
query returned zero records. Tested a real already-shipped order (SO 10538619):
the invoice existed with a full freight line item.
Conclusion
Invoice and freight data only exists once an order actually ships and gets invoiced. For a forward-looking forecast, that data is structurally unavailable for most rows — it isn't a bug, it's how the business process works.
The report shows pre-tax total only (SalesOrderHeaderEntity.TotalBeforeTaxes),
with no product-vs-shipping split. This is documented as an explicit non-goal, not an oversight.
How multi-SKU orders are grouped into one line
A sliding door ramp order is never just one item — it's typically the ramp, a crossover section, a lifting stick, and sometimes a handrail system, all under one customer and one Genius Sales #. The sheet lays these out as visually merged blocks: only the first physical row carries Customer, Zoho #, Genius Sales #, Sales Rep, and dates. Every following row in that block has only the Product # column filled in — all other cells come back genuinely blank from the sheet's API, not carried-forward merge values.
"; ". A fully blank row closes out the block.
Real example — Lifegrip Solutions, SDR tab
| Sheet row | Customer | Product # | Genius Sales # |
|---|---|---|---|
| 5 | Lifegrip Solutions | 1-FG-SDR083060SL-V4 | 10540133 |
| 6 | (blank) | 1-FG-SDC0529SL | (blank) |
| 7 | (blank) | 1-FG-TRLS30 | (blank) |
| 8 | (blank) | 10-FG-HRDBA-SYS | (blank) |
Becomes one forecast line item:
Customer: Lifegrip Solutions
Genius Sales #: 10540133
Product: 1-FG-SDR083060SL-V4; 1-FG-SDC0529SL; 1-FG-TRLS30; 10-FG-HRDBA-SYS
Pre-Tax Total: $2,046
Ship Month: 2026-08Not every look-alike case is a grouping bug
Some customer names repeat on the sheet without being one multi-SKU order. Example: "Monterey Peninsula Unified
School District" appears three times on the ATT/AST tab, each with its own Zoho # (81624,
81753, 81754) and no Genius Sales # yet (placeholder rows, still at quote stage). These
are three genuinely separate orders under the same customer name — correctly kept as three separate rows,
not merged into one.
Why an order lands in Exceptions
Every row that can't be forecasted is kept, not silently dropped, so the total dollar universe stays reconcilable. There are four reasons a line item ends up in the Exceptions tab instead of the forecast:
9/11/69 instead of 9/11/26.
9/11/69 would otherwise silently parse to the year
1969 and land in a garbage bucket instead of getting flagged — this exact case was caught on a live run.
What the output workbook looks like
| Tab | What's in it |
|---|---|
| Detail | One row per forecasted order, grouped by Source Tab, then by Ship Month within each tab. Subtotal after each tab+month group, a total after each tab, and a grand total at the bottom. All dollars: whole-dollar, comma-separated, right-justified. |
| Summary | Ship Month × Source Tab matrix of dollar subtotals, with a month total column, a grand total row, and an exception-count breakdown by reason. |
| By Product Line | Same Tab → Month grouping as Detail, but a leaner column set (Product, Customer, Genius Sales #, Pre-Tax Total, Ready to Ship) for scanning individual production items per tab per month. |
| Exceptions | Full detail on every row that couldn't be forecasted, with the specific reason spelled out so it can be fixed on the sheet or in Genius. |
First live run (2026-08-10)
136 raw sheet rows across the 4 tabs correctly collapsed into 70 real orders after grouping:
| Source tab | Real orders |
|---|---|
| ATT/ AST | 20 |
| CVR-AV | 26 |
| SDR | 21 |
| MISC | 3 |
Of those, 59 line items forecasted cleanly (valid Genius Sales #, valid Genius match, parseable ready-to-ship date), for a total of $795,262 pre-tax across Ship Months 2026-04 through 2026-09. 11 rows landed in Exceptions across the four reasons above. Zero Genius API lookup failures.
Re-running the report
This is a one-off report right now — not scheduled or cron'd. To re-run it with current sheet data:
cd /Users/nathanshiba/.openclaw/workspace-forge
python3 scripts/production3_ship_forecast.py
# optional custom output path
python3 scripts/production3_ship_forecast.py --out /path/to/output.xlsxRelated pages
This report reuses the same Genius API access pattern as Order Lookup. See
How Order Lookup Accesses Genius API for the underlying
authentication and entity-fetch mechanics, and
PO Number Field Overload Map for why PoNumber is never
used as a production join key — only SalesOrderHeaderEntity.Code is safe for that.