Breww Sync v5: User Guide

Quick Start

What this spreadsheet does

Breww Sync pulls your sales orders, product catalogue, customer records, and batch/racking data from the Breww API, then computes 8 analytics tabs covering revenue, production, customer retention, stock forecasting, and product volumes, broken down by week with year-on-year comparisons.

I've verified all claims to the best of my ability, but as before, only you can properly scrutinise these figures to make sure nothing is off.

Notes

You asked for two customer-maintenance tabs and one forecast tab. Instead I'm giving you one combined tab for "Lapsed & Lost" customers, and I've separated the forecasting into two tabs: Customers and Products. Hopefully the logic of these decisions will feel clear on review.
Expandable Rows revealing product proclivities for each customer, and relative customer-thirst for each beer, is totally doable with the data we're already working with, but it will require an interactive dashboard web-app. Which in many ways is easier than a Google Sheet.
I decided to combine certain products that I assume to be the same, "Deep" and "Nitro Deep", for example, even when they have different product codes in Breww. I could be wrong about this, easily unpicked if you want to subdivide more than I have.
Acme and your taprooms are currently excluded from all forecasting. Perhaps in fact you want to include them in some calculations, let's discuss.
Container tracking is not yet available via the Breww API. People are asking in their forums for this, but currently the API doesn't expose this data. Stock Weeks is my attempt at a workaround, calculated from current stock ÷ projected weekly demand from our forecast engine. If you have container tracking data stored elsewhere, we should incorporate that.
The "Retain" checkboxes on the Lapsed & Lost tab control whether a customer stays in the forecast. Customers who haven't ordered in under 6 weeks are pre-checked (retained by default); 6+ weeks are unchecked (excluded by default). You can override either direction: uncheck a recent customer to exclude them early, or check a long-absent customer to keep them in the forecast. Your manual choices persist across refreshes, but won't be reflected in the projections until you next run the analytics commands.
Once we're happy with all this, it will be set to update automatically every morning. But for now it is synced in three manual steps from the Breww Sync menu at the top of the sheet, more on that below.

Tab Guide

Spreadsheet Tabs

Tabs appear in this order along the bottom of the spreadsheet:

Summary On Trade Sales Product Detail Lapsed & Lost Forecast (Product) Forecast (Customer) Batch Tracking Profiles Sync Log _Orders _Products _Customers _Batches _BatchActions

The underscore-prefixed tabs are raw data caches, you can ignore these.

Summary

A week-by-week overview of the business for the current year. Each row is one business week, with sales revenue, production volume, and customer counts, compared against the same week last year. A "Stock Weeks" section on the right shows how many weeks of stock remain for each beer. Where a beer has both kegs and cans, each SKU gets its own row.

On Trade Sales

Year-on-year revenue comparison for every external customer, broken down by week. Each customer gets two rows: the top row shows 2026 weekly revenue, and the bottom row (italicised) shows 2025. Customers are sorted by total 2026 revenue so far.

Product Detail

Every beer product ranked by year-to-date volume sold, with comparisons across three time windows: last week, last 4 weeks, and year-to-date. Products with zero current-year volume are excluded.

Lapsed & Lost

Customers who haven't ordered in 2 or more weeks, sorted first by fewest weeks without an order, then by all-time order count. A "Retain" checkbox controls whether each customer stays in the forecast. Customers under 6 weeks are pre-checked; 6+ weeks are unchecked. You can override either way, and after the next sync, your choices persist across refreshes.

Forecast (Product)

Projected weekly volume (in litres) for each beer product across 52 weeks of 2026. These projections are based on 2025 trading patterns from retained customers, adjusted using the profile curve for newer accounts. Products are sorted by total projected volume. Values are rounded to whole litres.

Forecast (Customer)

Projected weekly revenue (in £) for each retained customer across 52 weeks of 2026. Customers are sorted by total projected revenue. Each customer's projection uses their 2025 ordering pattern, with one of two methods applied depending on when they started trading.

Normal text on white: projected from actual 2025 trading history ("History" method)
Italic text on grey: estimated via the profile curve ("Profile" method, customer started trading after Week 13 of 2025)

Batch Tracking

Production data in two sections: a summary box at the top showing per-beer averages from recent brews, and a detailed log of every racking event below.

FYI: it seems that a lot of brews are missing start dates and/or final gravity measurements, which I've confirmed to be genuinely missing from the data in Breww. This means some cells appear blank, hopefully it's not a total mystery why those figures aren't in the record.

Profiles

Section 1, Profile Curve: A static reference table showing the typical distribution of when new customers place orders across the year (based on 293 new customer orders from 2024). Highlighted rows indicate weeks with >3% of annual volume.

Section 2, New Customers: All customers with their first orders in 2026. Each has an editable field for projected revenue. The figures entered here will be reflected in projections after the next analytics refresh.

Section 3, Calculator: Type an estimated annual volume for a new customer and see the projected weekly volume based on the profile curve.

How to Sync

3-Step Refresh Process

The spreadsheet refreshes in three manual steps, each triggered from the Breww Sync menu in the menu bar:

Refresh Sequence

1

Sync Raw Data (API)

Fetches all data from Breww API into 5 hidden raw tabs

~4–5 minutes

2

Analytics 1: Core

Builds Batch Tracking, Lapsed & Lost, On Trade Sales, Product Detail

~30–60 seconds

3

Analytics 2: Summary & Forecasts

Builds Summary, Profiles, Forecast Product, Forecast Customer

~30–60 seconds

Each step will show a confirmation dialog before running and a summary dialog when complete.

If you see an "execution time exceeded" message, that's just because you didn't click "OK" soon enough after the code finished running, dismiss and ignore.

Full Menu Reference

Menu Item	Purpose
Set API Key	Store your Breww API key (one-time)
Initial Setup (Create Tabs)	Create or reset all 14 tabs with headers
Sync Raw Data (API)	Fetch fresh data from Breww
Analytics 1: Core	Compute core analytics tabs
Analytics 2: Summary & Forecasts	Compute summary and forecast tabs
Diagnose (Check Raw Data)	Debug tool: shows row counts and data samples
Export All Tabs (CSV)	Export every tab as a CSV file to Google Drive

Technical Reference

Methodology

This section documents the exact calculation logic for each metric, a comprehensive spec to inform further development.

Data Flow

Breww API

/orders/ /products/ /customers-suppliers/ /drink-batches/ /drink-batch-actions/

▾

Step 1: Sync Raw Data

_Orders _Products _Customers _Batches _BatchActions

DataLayer.gs reads raw tabs → JS objects

▾

Step 2: Analytics 1

Batch Tracking Lapsed & Lost On Trade Sales Product Detail

Step 3: Analytics 2

Summary Profiles Forecast (Product) Forecast (Customer)

Analytics 2 reads "Retain" checkbox state from Lapsed & Lost (built in Analytics 1)

Key architectural point: Analytics 1 and 2 never call the Breww API. They read exclusively from the raw tabs via the DataLayer. This means you can re-run analytics without consuming API rate limit.

Revenue field mapping: All revenue figures use the Breww API field order_lines[].value, the ex-VAT net line amount. This is the correct metric for B2B trade sales reporting.

Per-Tab Calculation Specs

Summary, Main Table

Row scope: One row per business week from Week 1 to the current week of the current year.

Metric	Formula	Source
Sales (£)	`SUM(order.lineTotal)` for all order lines in this business week/year, excluding internal accounts	_Orders
Sales LY (£)	Same formula for the same business week of the previous year	_Orders
Sales YoY (%)	`(Sales - Sales_LY) / Sales_LY`, "New" if LY=0 and CY>0; blank if both zero	Derived
Production (HL)	`SUM(batch.totalVolL / 100)` for batches with completion date in this business week/year	_Batches
Production LY (HL)	Same formula for the previous year	_Batches
Customers Ordering	Count of unique `customerId` values with `lineTotal > 0`, excluding internal accounts	_Orders
Customers LY	Same count for the previous year	_Orders

YoY colour logic

Green (#dcfce7) if this year > last year
Red (#fecaca) if this year < last year
No colour if equal or both zero

Summary, Stock Weeks

Scope: One row per active (non-obsolete) beer product with either stock or projected demand.

Metric	Formula
Stock (litres)	`stockQty × product.volumeLitres`
Projected weekly demand	`SUM(projection weeks[currentWeek..52]) / (52 - currentWeek)`
Weeks of stock	`stockLitres / weeklyDemand`, rounded to 1dp

Colour grading

0 weeks: #fca5a5 strong red
<2 weeks: #fecaca red
<4 weeks: #fed7aa orange
<8 weeks: #fef3c7 amber
8+ weeks: #dcfce7 green (healthy)

Sort order: Fewest weeks of stock first (most urgent at top).

Deduplication: By normalised full product name. Beers with a normalisation rule merge their variants; beers without keep separate rows per packaging format.

On Trade Sales

Row scope: Two rows per external customer (2026 row + 2025 row). Only customers with any orders in 2025 or 2026 are included.

Sort order: Total 2026 revenue descending.

Cell	Formula
Week N (2026 row)	`SUM(order.lineTotal)` for this customer in business week N of 2026. Blank if zero.
Week N (2025 row)	Same for 2025. If total 2025 revenue = 0, all cells show a dash (`-`).

YoY colour logic

Green if 2026 value > 2025 value (both must be > 0)
Red if 2026 value < 2025 value
No colour if 2026 cell is blank/zero or 2025 cell is a dash

Formatting: 2025 rows are italic with font colour #9ca3af. £#,##0 format. Frozen: 1 header row, 2 columns.

Product Detail

Row scope: One row per normalised product name with non-zero current-year YTD volume. Only beer products included.

Sort order: YTD volume descending.

Time windows

Last Week, the most recently completed week (currentWeek - 1)
Last 4 Weeks, the 4 most recently completed weeks
YTD, Weeks 1 through most recently completed week
"LY" variants use identical week numbers from the previous year

Metric	Formula
Volume (L)	`SUM(order.quantity × product.volumeLitres)` for matching orders
YoY (%)	`ROUND(((YTD - YTD_LY) / YTD_LY) × 100)`

YoY special values

"New": product has YTD > 0 but zero sales across ALL of last year
"N/A": product existed last year but had no sales in the YTD period

Batch Tracking

Summary

Row scope: One row per active beer (normalised name), computed from the last 4 gyles per beer.

Metric	Formula
Avg Residency (days)	Mean of per-gyle max residencies. `Residency = (datetimeDone - batch.datetimeStarted)` in days.
Avg Yield (%)	`totalVol / (totalVol + totalLost)` across all 4 gyles
Avg ABV	`(startingGravity - finalGravity) × 131.25` per gyle. No fallback to recipe ABV.
Total Vol (L)	`SUM(volSuccessfulL)` across all racking actions in the 4 gyles
Total Lost (L)	`SUM(volLostL)` across all racking actions in the 4 gyles
Brews Analysed	Count of gyles used (1-4)

Batch data scope: Only batches with planned_start_date ≥ 2024-01-01. All batch statuses included (Planned, In Progress, Completed, Cancelled).

Brews (Raw Data)

Every batch action (racking event) from _BatchActions, sorted by gyle number descending then datetimeDone ascending.

Lapsed & Lost

Row scope: All customers with 2+ weeks since their last order, excluding internal accounts.

Sort order: Weeks Without Order ascending, then All-Time Orders descending.

Metric	Formula
Weeks Without Order	`FLOOR((now - lastOrderDate) / (7 × 86400000))`
All-Time Orders	Count of unique `orderNumber` values
Avg Spend/Order	`totalSpend / orderCount`
Avg Spend/Week	`totalSpend / activeWeekCount`
Avg Spend/Month	`totalSpend / MAX(activeWeekCount / 4.33, 1)`
Total Spend (12m)	`SUM(lineTotal)` for orders in last 12 months
Retain	Boolean checkbox, controls forecast inclusion

Retain checkbox defaults

Customer State	Previously on List?	Default Value
New, < 6 weeks gap	No	Checked (included)
New, ≥ 6 weeks gap	No	Unchecked (excluded)
Returning, any gap	Yes	Preserved (your last choice)

Weeks colour gradient

2–3 weeks: #fefce8 light yellow
4–7 weeks: #fef3c7 amber
8–11 weeks: #fed7aa orange
12–15 weeks: #fecaca red
16+ weeks: #fca5a5 strong red

Persistence

Before clearing: readRetainOverrides_() reads col A + col H for ALL rows. After rebuild: hasOwnProperty check restores manual state; new customers get week-based default. If a customer orders again and drops below the 2-week threshold, they leave the tab entirely.

Profiles

The Profiles tab has a horizontal three-section layout.

Section 1, Profile Curve (cols B–E)

52 rows showing expected distribution of when new customers place orders across the year, based on 293 new customer orders from 2024. Weeks where percentage > 3% get an amber background.

Section 2, New 2026 Customers (cols G–I)

Dynamic list of customers who have ordered in 2026 but had no orders in 2025. Includes an editable Est. Annual £ field (amber border), manual overrides feed into the forecast engine. Default estimate: actual 2026 revenue ÷ SUM(profile percentages for active weeks).

Section 3, New Customer Calculator (cols K+)

Interactive calculator: type an estimated annual volume and see projected weekly volume based on the profile curve.

Forecast (Product)

Row scope: One row per normalised beer product name. Multiple product codes for the same beer are merged.

Sort order: Total projected volume descending.

Cell	Formula
Week N	`SUM(productProjection.weeks[N].vol)` across all product codes for this beer, rounded to integer
2026 Total	Sum of all 52 weeks

Volume unit: Litres (integer, format #,##0 "L").

Forecast (Customer)

Row scope: One row per retained customer.

Sort order: Total projected revenue descending.

Cell	Formula
Week N	`customerProjection.weeks[N]`, rounded to 2dp
2026 Total	Sum of all 52 weeks

Visual differentiation

History method: white background, normal font
Profile method: grey background (#f5f5f5), italic font (pre-onboarding weeks)

Exclusions

The following accounts are excluded from all customer-facing analytics:

Acme
40FT Taproom
40FT Taprooms
40ft Brewery
40FT Brewery

Matching logic: Case-insensitive substring match via isExcludedAccount().

Non-beer products (no drinkName) are excluded from Product Detail, Forecast (Product), Stock Weeks, and Batch Tracking Summary.

Forecast Methodology

The forecast engine (buildForecastProjection_()) projects 2025 trading data into a 52-week 2026 estimate.

Step 1: Input Data

All order lines from 2025 (business week year = 2025)
All order lines from 2026 for new-in-2026 customers
Per-customer weekly revenue and per-product weekly volume
Each customer's first order week in the relevant year

Step 1b: New 2026 Customers

Customers who have ordered in 2026 but have no 2025 orders are treated as new customers: always retained, always use Profile method, and support manual annual revenue overrides from the Profiles tab.

Step 2: Determine Retained Customers

The Retain checkbox on the Lapsed & Lost tab is authoritative, the engine is a pure function of checkbox state.

Customer State	On L&L Tab?	Retain	Forecast
Active (< 2 weeks gap)	No	N/A	Included
Lapsed (2–5 weeks)	Yes	Pre-checked ✓	Included
Lapsed (2–5 weeks)	Yes	Manually unchecked ✗	Excluded
Lost (6+ weeks)	Yes	Pre-unchecked ✗	Excluded
Lost (6+ weeks)	Yes	Manually checked ✓	Included
New 2026 customer	Maybe	Ignored	Always included

Step 3: Choose Projection Method

Condition	Method	Description
New 2026 customer (no 2025 orders)	Profile	Extrapolate from 2026 orders using profile curve
First 2025 order Weeks 1–13	History	Full year of trading data: use actual weekly values directly
First 2025 order after Week 13	Profile	Actual data for active weeks, profile curve for pre-onboarding weeks

Cutoff: CONFIG.FORECAST_FULL_YEAR_CUTOFF_WEEK = 13

Step 4: History Method

projectedWeek[w] = actual2025Revenue[w] || 0 (for w = 1..52)

Step 5: Profile Method

Calculate sum of profile percentages for customer's active period: activePctSum = SUM(profilePct[w-1]) for w = firstWeek..52
Estimate annual revenue: annualRevenue = actualTotalRevenue / activePctSum
Override for new 2026 customers: if a manual annual revenue value exists in Profiles tab, that value is used instead.
For each week:
Pre-onboarding (w < firstWeek): projectedWeek[w] = annualRevenue × profilePct[w-1]
Active period (w ≥ firstWeek): projectedWeek[w] = actualRevenue[w] || 0

The same logic applies to product volumes (litres instead of £). For new 2026 customers with a manual revenue override, product volumes are scaled proportionally.

YoY Comparison Logic

Business week alignment: All year-on-year comparisons use business week numbers, not calendar dates. Week 7 of 2026 is compared to Week 7 of 2025.

Value	Meaning	Condition
`+N%`	Revenue increased by N%	LY > 0, CY > LY
`-N%`	Revenue decreased by N%	LY > 0, CY < LY
`0%`	Flat	LY > 0, CY = LY
`New`	New this year	LY = 0 across entire year, CY > 0
`N/A`	Not applicable	Product existed LY but no YTD sales
Blank	No data	CY = 0
`-`	No history	On Trade Sales: no 2025 orders at all

Product Name Normalisation

The normaliseProductName() function merges variant product names using case-insensitive substring matching:

If name contains	Normalised to
`"deep"`	Deep, merges "Nitro Deep", "Deep Lager", "Deep - 30L Keg", etc.
`"dalston sunrise"`	Dalston Sunrise, merges all variants
Neither pattern	Returned unchanged

Applied in: Product Detail, Batch Tracking summary, Forecast (Product), and Stock Weeks.

Business Week Numbering

All date-to-week conversions use Matthew's "first full week" convention (not ISO 8601):

Week 1 starts on the first Monday on or after 1 January (for 2026: 5 January)
Weeks run Monday to Sunday
Always exactly 52 weeks (no week 53)
Orphan days before the first Monday are absorbed into Week 1
Overflow days after Week 52's Sunday are absorbed into Week 52
A date always belongs to its calendar year (no cross-year assignment)

Known Constraints

Limitations & Notes

Product renames in Breww Products are matched by their current drink name. If a product is renamed in Breww, historical orders under the old name won't be associated with the new name. Known affected: JAM Raspberry Sour (formerly "Raspberry Gose") and Main Squeeze Hazy IPA (formerly "Dalston Sunset"), LY volumes are understated.
Container tracking not available via API Breww has this in the app but it's not exposed in the public API. If/when Breww adds this endpoint, it would enable stock-in-trade monitoring.
Bar Analytics pending Breww's per-product bar-level analytics are pending API availability.
Manual 3-step process Deliberately split into 3 steps to avoid Google Apps Script's 6-minute execution limit, save API budget (re-run analytics without hitting Breww), and give user control via confirmation dialogs.
Analytics ordering dependency Analytics 1 must complete before Analytics 2. The forecast engine reads the Retain checkbox state from the Lapsed & Lost tab, which is built in Analytics 1.
Checkbox persistence Your manual Retain overrides survive every refresh via a read-before-clear pattern. If a customer's name changes in Breww, their override will be lost (match is by name string).
Rate limiting The Breww API allows 60 requests per minute. The sync applies a 1.1-second sleep between page fetches. If rate-limited (HTTP 429), it waits 60 seconds and retries up to 3 times.
Data window All order and batch data fetched from 1 January 2024 onwards (CONFIG.FETCH_START_DATE = '2024-01-01').
Forecast accuracy considerations Forecasts assume 2026 mirrors 2025 trading patterns. Profile-method projections are less accurate than History-method. The Retain checkbox is authoritative, your manual overrides always win. Only beer products are included.
Timezone The Apps Script project timezone is set to Europe/London. The default (America/New_York) causes date-only ISO strings to be parsed incorrectly, shifting dates by a day.