The previous article in this series made the case that reorder leakage is invisible week to week and showed a one-afternoon spreadsheet audit to measure it. This one is the build guide: how to turn that audit into a standing early-warning system using the CRM you already pay for — HubSpot or Pipedrive — without buying anything new.
This is the unglamorous version, deliberately. It won’t model seasonality or auto-draft outreach. What it will do is guarantee that no account slips past 1.5× its order cycle without a human seeing its name on a list. For many converters, that alone closes most of the gap.
The design in one paragraph
Each account (company in HubSpot, organization in Pipedrive) carries three numbers: its typical reorder interval, the date of its last order, and a computed “overdue ratio” — weeks since last order divided by typical interval. One saved list/filter shows every account whose ratio crossed your threshold, sorted by last year’s revenue. One person owns a 30-minute weekly pass over that list. That’s the whole system. Every piece below is in service of those four sentences.
Step 1: Get order dates into the CRM
The system lives or dies on one field: last order date, kept current. Three ways converters typically feed it, in ascending order of effort:
- Manual on order entry. Whoever enters the order in the ERP updates the CRM field. Workable below ~20 orders a week; fragile above.
- Weekly CSV import. A weekly export of “orders since last week” from the ERP or invoicing system, imported against the company record. Thirty minutes a week, no integration project, surprisingly durable.
- A small automation. A middleware connection (or your IT person’s script) that pushes order date and value on each invoice. Worth it once the weekly import becomes a chore someone skips.
Start with the weekly import. The mistake to avoid is starting with the integration project — converters have lost a year waiting for the “proper” ERP connection while the spreadsheet version would have caught accounts every month.
Step 2: Three custom fields
Create these on the company/organization object:
| Field | Type | Source |
|---|---|---|
Typical reorder interval (weeks) | number | From your order-gap audit: the account’s median gap. Set once, revisit quarterly. |
Last order date | date | Updated by the import (Step 1). |
Reorder status | dropdown: On cycle / Watch / Overdue / Dormant / Seasonal | Set by the weekly review (Step 4). |
The interval field is the one that makes this your system instead of a generic “no activity in 90 days” alert. An account that orders every 5 weeks is in trouble at week 8 — long before a 90-day rule notices. An account that orders twice a year would generate four false alarms a year under the same rule. Per-account intervals kill both failure modes.
If your CRM tier supports calculated fields, add Weeks since last order computed from the date field; if not, the saved-list logic below handles it with date filters.
Step 3: The overdue list
Build one saved list (HubSpot: active list; Pipedrive: filter) named something blunt like “Overdue to reorder.” Logic: last order date is older than the account’s typical interval × 1.5. Without calculated fields, approximate with interval bands — three lists (“interval ≤6w and no order in 9w”, “7–12w and none in 18w”, “13w+ and none in 30w”) merged into one view. Cruder, equally effective.
Sort by trailing-12-month revenue, descending. The sort matters: when the list is long (the first week, it will be), the CSRs must start where the money is.
Two exclusions keep the list honest: accounts marked Seasonal (their rhythm is the calendar, not a cycle — handle them with a simple “expected back in March” task instead), and accounts marked Dormant (consciously written off; review that pile quarterly, not weekly).
Step 4: The weekly pass
The system’s heartbeat is a 30-minute standing slot — same day, same owner, protected. The pass:
- Open the overdue list. For each new arrival, decide: call, email, or reclassify (seasonal/dormant/known reason).
- Assign each call to the CSR who owns the relationship, as a CRM task with a due date this week.
- Move anything the team learned last week into
Reorder status— the dropdown is your attrition log. - Note the count. The trend of “accounts entering overdue per week” is your retention dashboard, version one.
The check-in itself stays service-shaped: “You usually run these about now — want us to get ahead of it and hold press time?” It reads as attentiveness, because it is.
What this version can’t do
Honesty about the manual build’s limits, since they’re the reasons it eventually gets replaced:
- Static intervals. The median you set in January drifts as the account’s business changes; nobody recalculates 300 medians quarterly by hand.
- No drafting. CSRs still compose every message. At ten overdue accounts a week that’s fine; at forty it’s the bottleneck.
- Discipline-dependent. The weekly pass survives exactly as long as its owner does. Vacation, busy season, turnover — each is a hole in the watch.
- No revenue-at-risk math. Sorting by last year’s revenue approximates risk; it doesn’t compute it.
Those four limits are, not coincidentally, the spec of the full reorder-capture system: live cycle models instead of static medians, drafted outreach a human approves, alerts that don’t depend on anyone’s calendar, and a board sorted by modeled revenue at risk. The manual build is not a lesser version of that system — it’s the proof of concept that tells you whether your account base has enough drift to justify the automated one. Run it for two months and you’ll know.
The Monday checklist
To compress the whole build:
- Export orders; compute each account’s median gap (one afternoon).
- Create the three fields; load intervals and last-order dates.
- Stand up the weekly CSV import from the ERP.
- Build the “Overdue to reorder” list, sorted by revenue.
- Book the 30-minute weekly pass and name its owner.
- After two months: count the saves, then decide whether the automated layer earns its keep.
Nothing on that list requires a budget approval. The only real cost is admitting that “our CSRs would notice” was never a system — and for a converter whose revenue is mostly repeat business, the watching deserves one.