How I Built Bewertiq: Reading 10 Million Google Reviews as a Market Signal

TL;DR: Bewertiq is a build. Five generations of Playwright scrapers turned 24,981 business profiles and just under ten million reviews into a static, backend-free application. This is the pipeline, the database and the statistics — including how I estimate the reviews Google has quietly removed.

The scenario

Every local business runs on Google Maps, and every Maps profile is free market research that almost nobody reads. The star average and the review count are public. The shape underneath — how fast reviews accumulate, how many were removed, how one district differs from the next — is not.

I wanted to read the market, not the stars. That meant collecting a large, clean, geolocated corpus of German businesses and reviews, and then modelling it. No accounts, no API keys: only what Google exposes publicly to a logged-out browser.

The result sits at bewertiq.org — 24,981 business profiles across 30 cities and 29 categories, backed by 9,999,625 evaluated reviews, with every score computed in the visitor's browser.

Why the star rating is not enough

The first thing the data tells you is that the headline metric is compressed. In the full corpus of 47,032 scraped profiles, 91.9 percent of businesses sit between 4.0 and 5.0 stars. Trying to separate a 4.4 from a 4.8 on a five-point scale is trying to read a ruler with the markings rubbed off.

The second thing is that the star average and the removal notices are nearly independent. Pearson's correlation between rating and deletion ratio is r = 0.058 — no relationship. If anything, the better-rated businesses carry more removals, simply because more reviews means more removal potential. 89.7 percent of profiles never lost a review at all, and roughly 95 percent of the profiles that did receive a removal notice still sit in the 4-to-5-star band.

So the star rating is not a transparency indicator. To see the second dimension you need a number Google does not hand you: how many reviews are missing.

The scraper, five generations

The crawler was not designed once. It evolved, each generation fixing the failure of the last.

1. Sequential (Gen 1). One browser, one query at a time, clicking result cards and opening the side panel. 30 entries per run and a high CAPTCHA risk.
2. Card-based (Gen 2). Content extracted directly from the result cards, no click; the panel opened only for the deletion notice. CAPTCHA became so frequent it forced 120-second back-offs.
3. Parallel contexts (Gen 3). Three Playwright browser contexts driven by asyncio.gather(), city configuration in a dictionary, checkpoint and resume so a killed run continued where it stopped.
4. Maps route (Gen 4). Discovery moved from Google Search to Google Maps, which drops CAPTCHA almost entirely, then detail scraping in parallel. SQLite switched to WAL mode so writers and readers stop blocking each other.
5. Process-parallel (Gen 5). Three simultaneous OS processes: one scraper for ratings and deletion counts, one address scraper in a visible browser, and a cleaner that de-duplicates every five minutes.

The lesson across all five: the bottleneck is never parsing, it is getting the pages. Moving from search to Maps and from one context to several processes bought more throughput than any selector optimisation ever did.

Those five generations are also a timeline — and each step was a trade-off, not just an upgrade.

The pipeline itself, phase by phase:

From SQLite to a static site

The data layer is deliberately boring. Each run normalises into a single SQLite table with one row per business: name, category, rating, total reviews, the raw deletion notice, the parsed deletion range, address and coordinates. A cleaner strips non-business entries, collapses duplicates and repairs addresses (first via the Photon geocoder, then, for the stragglers, a small Maps pass that recovered close to 100 percent of the missing addresses).

The export writes the same CSV to three places — the project root, public/data/ and the frontend's src/data/. That single file is the contract between scraper and application.

The application itself is a static React bundle. There is no backend and no database at runtime: the browser downloads the CSV, enriches it in memory, and renders a Leaflet map and a set of Chart.js views. Individual routes are pre-rendered for search engines, but every statistic is computed live from the raw rows. That choice is what makes the modelling section below possible without a server.

The architecture, in diagrams

The project keeps its architecture as code. What follows are the same diagrams that live in the repository, cleaned up for the web — including the parts a build log usually leaves out.

The scraper pipeline

The full system

A request, end to end

The data model

Payload and traffic

The agent system

Estimating what Google hides

Google does not publish an exact deletion count. It publishes a notice in a range — "21 to 50 reviews removed", "more than 250 removed" — or nothing at all. Everything downstream depends on turning that text into a number.

The simplest layer is the Review Stability Score, which I expose as RSI on a 0 to 100 scale:

$$\text{RSI} = \max\left(0,\; 100\left(1 - \frac{D}{R}\right)\right)$$

Here R is the total number of reviews and D the estimated number of removed reviews, so the ratio D / R is the deletion rate and the score is simply how much of the history survives. A profile with no notice scores 100. A profile that lost a tenth of its reviews scores 90. RSI is a heuristic orientation value for how much removal activity a profile has attracted — it is not a verdict about intent, because the public notice never says who requested the removal.

For the ranges I use the midpoint when both bounds exist, and the lower bound when the range is open-ended. That leaves the worst case — the "more than 250" notices — as a censored variable. Rather than guess, I fit a regression on the profiles whose exact count is known and predict the censored ones from it:

$$\ln(E) = \beta_0 + \beta_1\,\frac{R_v}{\overline{R}^{\,(c)}_v} + \beta_2\,(S - \overline{S}^{\,(c)})$$

where R_v is the review volume, S the rating, and the bars denote the category means. The coefficients come from a plain three-column ordinary least squares solved with Gaussian elimination — small enough to run in the browser, and trained only on uncensored rows. The prediction is floored at the stated lower bound, so the model can never estimate fewer removals than Google admits to.

To put an uncertainty band around a single profile I use a Beta-Binomial model. Each review is a Bernoulli trial (removed or not), so the removal probability has a conjugate posterior:

$$D \mid R, \theta \sim \text{Binomial}(R, \theta), \qquad \theta \sim \text{Beta}(1,1)$$ $$\Rightarrow\; \theta \mid D \sim \text{Beta}(1 + D,\; 1 + R - D)$$

The posterior mean gives the expected count and the credible interval gives the range. A hierarchical variant shrinks each profile toward its category mean, capped at ten pseudo-observations so a category with few members cannot dominate. At the population level the same 0-to-100 scores are summarised with a Beta regression, where the dispersion is recovered from the mean and variance and the location is modelled on the logit scale:

$$\phi = \frac{\mu(100-\mu)}{\sigma^2} - 1, \qquad \operatorname{logit}(s) = \ln\frac{s}{100 - s}$$

The logit matters because RSI is bounded: a linear model on a 0-to-100 proportion will happily predict values outside the range, and a logit-normal cannot.

From Q to the Real score

RSI answers "how much survived". Three derived metrics answer "so what".

Q normalises the deletion ratio onto a five-star scale, so it can sit next to the rating:

$$Q = 5 - 4 \times \min\left(1,\; \frac{\text{deletedRate}}{0.10}\right)$$

Ten percent deletions is the cap: no notice at all maps to 5.0, a five-percent rate to 3.0, and anything at or beyond ten percent to 1.0. The cap is a known limitation — it is a traffic light for "noticed or not", not a measure of effect strength. Past ten percent you have to compare the raw ratio.

The Real score asks what the average would be if the removed reviews were still there:

$$\text{Real}(a) = \frac{S_v\,N_v + a\,D_e}{N_v + D_e}, \qquad a \in [1, 2]$$

with S_v the visible rating, N_v the visible count, and D_e the estimated removals. The stars of the removed reviews are not recoverable, so instead of inventing a fixed value I report a corridor: a = 1 assumes every removal was a one-star review (the largest possible effect), a = 2 assumes the mean of the one-to-two-star segment (a smaller one). The result is an interval, not a point — and because the count itself is uncertain, the interval is usually dominated by the unknown number of removals, not the unknown star value. For 93 percent of profiles, Google's range moves the result more than the entire a span.

The conservative special case is the worst-case clean score, where every removed review is assumed to be one star:

$$S_{\text{clean}} = \frac{S_v\,N_v + 1 \cdot D_e}{N_v + D_e}$$

The composite C is the average of the visible rating and Q, and is honest about inheriting Q's cap:

$$C = \frac{S_v + Q}{2}$$

The transparency scale

Q collapses everything above ten percent, which makes it useless for ranking the most affected profiles. The transparency scale fixes that by stretching both dimensions across their realistic ranges instead of their full spans.

The rating is remapped from its compressed 4.0-to-5.0 band onto a full star scale:

$$R^{*} = 1 + 4\,(S - 4), \qquad S \in [4.0,\, 5.0]$$

so a 4.0 becomes 1.0, a 4.5 becomes 3.0 and a 5.0 stays 5.0. Ratings below 4.0 are already discriminating, so they pass through unchanged.

The deletion ratio becomes transparency stars, using the same 0-to-10-percent stretch:

$$T_{\star} = 5 - 4 \times \min\left(1,\; \frac{\text{deletedRate}}{0.10}\right)$$

Plotted against each other, the two axes produce a 2x2 matrix with a clear elite quadrant — high R* and a perfect T★ — which holds 22.5 percent of the sample (2,934 businesses), a "transparent and solid" majority at 67.2 percent, and a small "deletion suspect" corner of 2.3 percent: well-rated but heavily cleaned. The single composite is:

$$C = \frac{R^{*} + T_{\star}}{2}$$

None of these transformations touch the stored data. The CSV keeps Google's originals; the remapping happens at render time. That keeps the structured data (the aggregateRating Google reads) truthful and the display legible.

The Enhanced Formula and the 19 variables

The headline model for estimating a censored count is a 19-feature regression. Eighteen predictors describe the business and its market; the nineteenth term is age, and it enters logarithmically because each additional year carries less information than the one before — a roughly five-year half-life:

$$\hat{E} = \exp\left(\beta_0 + \sum_{i=1}^{18} \beta_i x_i\right) + \log_2(\max(t,1))$$

The predictors fall into five groups:

Group	Variables
Core	Rating, log review volume, category factor
Location	Population density, geospatial cluster
Economy	Purchasing power, trade-tax multiplier, unemployment rate
Opening hours	Opening duration, Sunday opening, late-night opening
Profile and market	Competitor density, parking, pedestrian zone, public transport, tourism, founding season, social media

The category factor and the location cluster are the two that do most of the work in practice: a pediatrician and a nightclub in the same postal code are not comparable, and the model has to know that before it compares them.

What a static frontend can compute

The reason all of this runs in the browser is a single architectural decision: ship the raw corpus, not the conclusions. The bundle loads a CSV, and every regression, credible interval and star transform is a pure function over arrays. The only server is the static file host.

That is not just a cost choice. It means there is no scoring service to rate-limit, no API key to rotate, and no pipeline that can silently change a number between two page loads. The trade is payload: the corpus is large, so the app caches the enriched rows in IndexedDB and recomputes only what a given view needs.

Limitations

The dataset is a cross-section, not a panel. It captures one snapshot of each profile, with review history only as a proxy for time. Deletion counts are ranges, not integers, and the "more than 250" tail is modelled rather than observed. The removal notice never states who requested a removal, so a high removal count is a signal about visibility, not about misconduct — the profiles with the most removals are often the target of campaigns rather than their authors. And every score is a probability statement, not a guarantee: a data platform can tell you what a market looks like, not what a specific business will do tomorrow.

Takeaways

• The star rating is compressed and uncorrelated with removals; the missing reviews are the second axis.
• Estimating what a platform hides means fitting the known population and predicting the censored one, not guessing.
• Beta-Binomial posteriors turn a range into a credible interval, and the logit keeps a bounded score bounded.
• Report the effect as a corridor, and say plainly what the number cannot prove.
• A corpus plus pure functions is enough to run a full model stack with no backend at all.