We sell foodservice sales software. One question our users ask constantly is
some version of "how many restaurants serve X?" A sales rep at a food brand
wants to know how many places put bacon, or mac and cheese, or cheesecake on
the menu, which chains carry it, and where.
That question is also something people type into Google. So we built a set of
public pages, one per menu item, under
restaurants that serve.
Each page answers it with real counts: total locations, independents versus
chains, top segments, top chains and top states.
The hard part was not generating pages. It was making sure a number on a
marketing page never disagrees with the number a customer sees in the
product for the same search. This post is how we did that, in about 1,000
lines of Python.
The trap: a second implementation of the same query
The obvious build is a script that queries the database directly: count
restaurants whose menus match "bacon", group by segment, group by state.
We tried a version of that first. It produced numbers that were close to
the app's. Close is the problem. The app applies filters, de-duplication and
chain rules that live in its view layer. Any re-implementation drifts from
it, and a prospect who checks a page against a trial account sees two
different answers.
So the rule became: the page generator does not compute counts. It asks the
app's own code.
Calling the API views in-process
Our backend is Django REST Framework. The product's report screens are
backed by a few API views: a summary view (totals, with filter parameters
for independents and chains), a list view (top chains), and a per-location
summary (counts per state).
Instead of calling those over HTTP, the pipeline runs inside a Django shell
and calls the view classes directly with DRF's APIRequestFactory, using the
same query parameters the frontend sends:
from django.db import transaction
from rest_framework.test import APIRequestFactory, force_authenticate
rf = APIRequestFactory()
def call(view, path, params):
"""One app API request, in-process, rolled back."""
req = rf.get(path, params)
force_authenticate(req, user=user)
with transaction.atomic():
try:
resp = view.as_view()(req)
finally:
transaction.set_rollback(True)
assert resp.status_code == 200, (path, params, resp.status_code)
return resp.data
Three things this buys:
- Same code path. Permissions, filters, serializers: all the app's own. The output matches the API field for field.
- No network hop, no web workers. Nothing goes through the public API, so a batch of page refreshes can't compete with customer traffic. We learned that one the hard way and now run the pipeline only against a non-production copy of the data.
-
Nothing persists. Some report queries write scratch join tables.
Every call runs inside
transaction.atomic()withset_rollback(True), so whatever the view writes is thrown away.
Each page is one search query built from a list of menu terms:
sq = "menu:" + " OR ".join(f'"{t}"' for t in items[slug]["terms"])
base = {"search_query": sq, "sort": "-count", "type": "all"}
def chain_sum(**extra):
s = call(SummaryView, "/reports/chains/summary/", {**base, **extra})
return {"businesses": s["count"], "locations": round(s["places_count"] or 0)}
total = chain_sum()
indep = chain_sum(count_less="1") # single-location operators
chains = chain_sum(count_greater="2") # multi-unit chains
Making ~95 calls per page fast
A page needs about 95 calls: the totals, one per segment (about 40), one per
state (51), plus top chains. Each call runs a full-text menu search first.
Two changes took a page from "go get lunch" to one to four minutes:
Memoize the search. Every call for a page runs the identical menu search
with different SQL filters on top. We wrap the search filterset's
apply_filters with a dict cache keyed on the inputs that affect the result:
_cache = {}
_orig = MenuSearchFilterset.apply_filters
def _cached_apply(self):
g = self.request.GET
key = (self.index_name, g.get("search_query"), g.get("sort"),
self.include_items, tuple(self.ids) if self.ids else None)
if key not in _cache:
_cache[key] = (_orig(self), self.sorting_applied)
res, self.sorting_applied = _cache[key]
return res
MenuSearchFilterset.apply_filters = _cached_apply
Warm, then fan out. We run the two distinct searches once, serially,
then fire the rest through a thread pool of four. Without the warm-up every
thread misses the cache at once and the search cluster rejects the burst
with 429s.
first_total, first_loc = chain_sum(), door_sum() # warm the cache
pool = ThreadPoolExecutor(4)
f_seg = {c: pool.submit(chain_sum, cuisine=c) for c in SEGMENTS}
f_st = {st: pool.submit(door_sum, state=st) for st in STATES}
Invariants, then a gate
Raw output goes to <slug>-app.json. Before anything becomes a page, two
checks run.
The splits must add up. Independents plus chains must equal the total,
for both businesses and locations. If they don't, the merge step refuses the
item:
out["splits_add_up"] = (
indep["businesses"] + chains["businesses"] == total["businesses"]
and indep["locations"] + chains["locations"] == total["locations"])
assert a["splits_add_up"], f"{slug}: app splits don't add up"
The page must have enough to say. A thin page is worse than no page, so
the generator has a gate:
GATE = {"min_locations": 200, "min_segments": 3, "min_chains": 3, "min_states": 3}
def gate(d):
reasons = []
if d["locations_total"] < GATE["min_locations"]:
reasons.append("too few locations")
if len(d["segments"]) < GATE["min_segments"]:
reasons.append("too few segments")
# ...chains, states, data date
return (not reasons), reasons
The payload builder will not include an item whose gate failed.
What the page is allowed to show
Public pages are also a scraping target, so the generator applies exposure
rules on the way out:
- Counts are rounded down and shown with a plus: 377,554 locations becomes "377,500+". Above 1,000 we round to the nearest hundred, above 100 to the nearest ten.
- Only multi-unit chains are named. No independents, no addresses, no per-location rows.
- No customer placements. A page must never reveal where one of our customers' products is on menus, so a brand list scrubs those names out of example dish text before it reaches the page.
def r(n):
n = int(n)
if n >= 1000:
return f"{(n // 100) * 100:,}+"
if n >= 100:
return f"{(n // 10) * 10:,}+"
return f"{n:,}"
The same rounded numbers go into both the visible copy and the page's
Dataset JSON-LD, generated from one dict, so the structured data can't
drift from the text either.
Shipping to the CMS without fear
The site runs on Webflow, and each page is a CMS item. Two habits make bulk
updates safe:
- Send only the number-bearing fields. The update payload is built from a fixed list (the answer capsule, counts, breakdown table, data date, meta description, structured data, FAQ answers). Headlines, images and anything a marketer edited by hand are never in the payload, so a refresh can't overwrite them.
- Snapshot first. Before a bulk change we save the current items to a dated JSON file. Rolling back is one update call with that file.
FIELDS = ["answer-capsule", "location-count", "chain-share", "top-segment",
"data-breakdown", "data-as-of", "seo-meta-description",
"structured-data", "faq-a1", "faq-a2", "faq-a3"]
for slug in slugs:
g = json.load(open(f"{GEN}/{slug}-gen-app.json"))
assert g["ok"], f"{slug}: gate failed {g['gate_reasons']}"
items.append({"id": ids[slug],
"fieldData": {k: g["fieldData"][k] for k in FIELDS if k in g["fieldData"]}})
The whole pipeline
items.json (slug -> menu terms)
│
▼
app_db.py app's report views, in-process, rolled back -> <slug>-app.json
│
▼
merge_app.py assert splits add up; keep only text from -> <slug>-page.json
the older menu pull
│
▼
pseo_gen.py gate, rounding, brand scrub, copy + JSON-LD -> <slug>-gen-app.json
│
▼
build_update.py number fields only, refuses failed gates -> update payload
│
▼
snapshot live items -> CMS update -> human OK -> publish
What we'd tell someone building the same thing
- If your product already answers the question, call the product's code, don't rewrite the query. Your pages and your app will agree by construction.
- Run batch jobs in-process against a non-production copy, never through the public API your customers use.
- Put invariants in the data step and a gate in the page step. Let the scripts refuse, not a reviewer.
- Make the CMS update narrow and reversible.
Today there are 16 live pages, and every count on them traces to a JSON file
written by the app's own code. If you sell into restaurants and want the
same numbers for your own territory, that's what
First Bite does.
Top comments (0)