Weekly reviews crawl → digest → social posts

For a month, our review digest told me nothing had happened: three Sunday emails in a row said zero new reviews, average rating unchanged, see you next week. In those weeks customers wrote 46 reviews. The crawl had stopped running on August 23, and the digest dutifully reported the silence as calm.

That is the most useful thing I can tell you about automating review collection, so I am starting with it. The rest of this post is the system itself: what it crawls, how it stores reviews so re-crawls never double-count, the gate that decides which reviews may become social posts, and three bugs that each taught me one rule. All the numbers below are from our own tables.

What it does, in one paragraph

Every Sunday at noon a job crawls the review pages of every product listing we have linked to a catalog SKU (official API for Mercado Libre, a headless browser for Amazon), stores each review once, downloads any customer photos and videos, writes a weekly snapshot row, and emails a digest. At 12:45 a second job takes the new five-star reviews, runs them through a positivity check, renders testimonial plates in three aspect ratios, writes captions, and queues them for my approval. Nothing is posted without a click.

The table that matters

Today the store holds 931 product reviews (805 from Mercado Libre, 126 from Amazon) and 84 Google business reviews. The ratings are lopsided, as they are for most sellers: 792 five-star, 75 four-star, and 62 at three stars or below. Only 412 of the five-star reviews have a body longer than 40 characters, which is the real pool for testimonials. A crawl of 931 reviews gives you about 400 usable quotes.

The schema that made re-crawling safe:

product_review_crawls     -- one row per crawl of one listing
  listing_url, source_platform, aggregate_rating, aggregate_review_count, crawled_at

product_reviews           -- one row per review, ever
  master_product_id, source_platform, source_review_id,   -- unique together
  rating, title, body, review_date, has_photo, has_video, media_urls,
  first_seen_at, last_seen_at

product_review_weekly_snapshots
  week_start, total_reviews, avg_rating, new_reviews_week, photo_count

Two details carry most of the weight. source_review_id is the marketplace’s own review id when we can read it, and otherwise a SHA-256 fingerprint of author, date, body and rating. If a later crawl finds the native id for a row we fingerprinted, the row is re-keyed rather than duplicated. And every review attaches to the catalog SKU, never to the marketplace listing id, because one product has several listings and listings get replaced.

The aggregate count on the crawl row matters too. Marketplaces show “4.7 from 300 ratings”, but most of those are star-only ratings with no text. If you display the count of stored text reviews you understate your social proof by a factor of three or more.

Bug one: “new this week” meant “stored this week”

The first digest counted a review as new when first_seen_at fell in the last seven days. Then I ran a catch-up crawl, and a complaint from March showed up in that week’s “new negative reviews” table as if it were urgent. The fix was to use the customer’s review_date for everything the reader sees, and keep first_seen_at only for a small ops line (“N rows stored today”). Two timestamps, two questions: when did the customer say it, and when did we notice.

Bug two: 89 “customer photos” were profile selfies

In the week of August 9 the photo count dropped from 303 to 214. That was the fix, not a loss. The browser crawler had selected every Amazon image inside a review card, which included each reviewer’s profile picture. We had been about to put strangers’ selfies into a testimonial collage. The fix: select only the review image tiles, reject known avatar URLs and default-avatar file hashes, reject Amazon’s exact 460×460 profile squares and other small squares, and purge the matching files from disk. If you scrape user-generated media, assume some of it is a person’s face that they did not post as a review.

Bug three: silence looked like calm

Here are the weekly snapshots around the gap:

The crawl table shows no crawl at all between August 23 and September 30. The irony is that I caused the cheerful emails myself. In early September a database error in the crawl step was also killing the snapshot and the email, so I separated them: a crawl failure could no longer block the Sunday digest. That was the right fix for the wrong problem. From then on, a failing crawl produced a tidy email saying zero new reviews, instead of no email, which at least would have made me look. When the crawl came back, 53 reviews arrived in one day.

The real fix was to make the digest tell “nothing new” apart from “did not look”. This now runs before every send:

def crawl_health(conn, now):
    row = conn.execute(text("""
        SELECT max(crawled_at) AS last_at,
               count(DISTINCT listing_url) FILTER (WHERE crawled_at > :week)    AS crawled,
               count(DISTINCT listing_url) FILTER (WHERE crawled_at > :tracked) AS tracked
        FROM product_review_crawls WHERE crawled_at <= :now"""),
        {"now": now, "week": now - timedelta(days=7), "tracked": now - timedelta(days=60)}
    ).mappings().first()
    if row["last_at"] is None or now - row["last_at"] > timedelta(days=8):
        return "STALE"      # numbers below are not this week's
    if row["crawled"] < 0.8 * row["tracked"]:
        return "PARTIAL"    # some listings silently dropped out
    return "OK"

Anything other than OK goes into the email subject line as [Crawl STALE] and into a red banner at the top. Run against history, it says STALE on September 20, which is exactly the email that should have alarmed me.

It also says PARTIAL for every week from mid-August, which brings me to a confession. The headless Amazon crawl has not stored a single crawl since July 11. Every Amazon review we have came in through a browser extension I run by hand, and Amazon’s review pages challenge headless browsers often enough that the weekly batch skips them. Roughly half the listings we track had quietly stopped being crawled, and the old digest gave no sign of it. I have not fixed the Amazon side yet. At least now the subject line says so every week.

The gate: why seven five-star reviews were rejected

A five-star rating does not make a review safe to post. The social job sends each new five-star text to a small, cheap model with one question: is this an unambiguous positive statement about our product? Since the gate went live it has checked 69 reviews and rejected seven. The reasons it gave:

• Two were not clearly about our product at all.
• One contained irony.
• Four contained a complaint despite the five stars: unclear instructions, a fiddly control, a broken part, and a safety concern.

That last group is the interesting one. Customers give five stars to the seller and complain about the product in the same sentence. Those reviews belong with the support team, not on Instagram. Today the gate only excludes them; routing them to a support queue is the next change. If the model is unavailable, a keyword heuristic decides instead. Plates that existed before the gate were grandfathered and marked as such, so the record shows which decisions a gate actually made.

From review to post

An approved review becomes a white-and-blue testimonial plate in three sizes (4:5 for the feed, 1:1 for Facebook, 9:16 for Stories), with the first name and last initial only, the star rating and the product name. The plates are rendered from a template, so text never overlaps the product photo, and a publish check refuses any image with letterbox bands. The caption is drafted separately, and the whole pack lands in a queue where I approve or reject it. Rebuilding packs renumbers them, so the queue checks what was already posted before anything goes out twice.

Key takeaways

1. Key reviews by the marketplace id, fall back to a content fingerprint, and re-key when the real id appears. That is what makes weekly re-crawls idempotent across channels and listing changes.
2. Keep two timestamps and use the right one. review_date for anything a person reads, first_seen_at for crawl operations. Mixing them turns a catch-up crawl into a false alarm.
3. Put crawl health in the subject line. Last crawl time and listings covered this week, with STALE and PARTIAL states. Without this, a broken crawler reports good news.
4. Gate on sentiment, not stars. Ask whether the text is an unambiguous positive about your product, and send the rejected five-star reviews to support, because that is where the useful complaints are.
5. Treat scraped media as possibly personal. Select only review-attached images, reject avatars and profile-sized squares, and purge what slipped through.

If you want to copy it this week

Start smaller than I did. Pick your one biggest channel, export its reviews to a sheet with three columns you never overwrite (review id, review date, first seen), and add a cell at the top with the date of the last export, which turns red after eight days. Each Monday, filter five-star reviews longer than 40 characters, read them, and move any that contain a complaint to a support tab. You will have the digest, the dedupe and the gate before writing any code, and the red cell will catch the failure that cost me a month.

The recipe pattern is in the open Agentic Company playbook.

— Alex