SellerBot — Overview & Data Model

SellerBot is the acquisition / enrichment engine. It takes raw Amazon seller listings, groups them into real-world business entities, and then tries to discover two things about each business: its website and a contact email. Those enriched emails are what eventually get emailed through the Smartlead outreach platform.

This page is the map. It explains what each table is, how the tables connect, how to read a seller's progress through the funnel, and — most usefully — how to answer common questions with SQL. It's written for analysts and business users (and the agents they point at the data), not just engineers.

All seb_*, in orange_schema

Every SellerBot table is prefixed seb_ and lives in the orange_schema Postgres schema. The flat table list is in Database Tables; this page is the relationship deep-dive.

The funnel at a glance

Read it top to bottom:

1. Ingest — a CSV import (seb_ingest_batch) lands one seb_amazon_seller row per Amazon listing, plus a daily seb_seller_snapshot of its metrics.
2. Group — an async grouping job stamps seb_amazon_seller.seller_group_id, collapsing the many listings of one real business into a single seb_seller_group. The seller group, not the listing, is the unit of enrichment.
3. Enrich — each group pursues up to two goals (website, email). For each goal there is exactly one seb_state row, and strategies are tried in priority order. Every strategy run is logged as a seb_attempt.
4. Confirm — a website candidate is confirmed by a review (approve → the domain lands on the group). An email candidate is confirmed by verification (a verdict; no human step).
5. Hand off — verified, sendable emails are pushed to Smartlead for outreach. (See the outreach bridge.)

The two goals: website and email

goal is always one of website or email. The two are independent state machines for the same group — a group can end up with a website but no email, an email but no website, both, or neither.

Email does not always require a website

A common misconception. There is no hard rule that email needs a website first. The website prerequisite is a per-strategy property — seb_strategy.prerequisite_goal (nullable). A strategy with prerequisite_goal = 'website' only runs once the group has an enriched website; a strategy with prerequisite_goal = NULL runs off the Amazon seller ID alone.

• seb_amz_sf_email_harvest_p1 — no prerequisite (harvests from the Amazon storefront). Currently inactive, but proves email can exist without a website.
• anymail_finder_v1 — the only active email strategy today, and it does set prerequisite_goal = 'website'. So in current production email follows website in practice — but that's a config choice, not a structural law.

(The prereq_goal='website' you may see on the email goal in goals.py is documentary metadata only — it is never consulted for gating.)

Core entities at a glance

Grain and join keys for the tables you'll touch most. "One row per" is the single most important column when writing SQL — it tells you when a join will fan out.

Table	One row per	Unique key / PK	Key foreign keys
seb_ingest_batch	one CSV import	id	—
seb_amazon_seller	one Amazon listing	(amazon_seller_id, marketplace)	→ seb_seller_group (nullable)
seb_seller_snapshot	listing per day	(amazon_seller_id, marketplace, snapshot_date)	→ seb_ingest_batch
seb_seller_group	one business entity	domain (unique, when set)	self merged_into_id
seb_state	one (group, goal) pair	(seller_group_id, goal)	→ seb_seller_group
seb_strategy	one catalog entry	(goal, name)	self prerequisite_strategy_id
seb_attempt	one strategy run	append-only	→ seb_state, seb_strategy
seb_review_event	one decision on an attempt	append-only (round 1 or 2)	→ seb_attempt, users
seb_email	one approved email	(seller_group_id, email_address)	→ seb_seller_group
seb_email_attribution	one attempt↔email link	(email_id, attempt_id)	→ seb_email, seb_attempt
seb_verification_pass	one verifier verdict	(email_id, verifier_name)	→ seb_email
seb_smartscout_category	one category	(marketplace, smartscout_id)	—
seb_dataforseo_task	one in-flight API call	(strategy_id, state_id)	(transient)
seb_bbb_scrape_task	one in-flight BBB scrape	(transient)	(transient)

How the tables relate

The relational spine — follow the lines to know what joins to what.

Walking the funnel stages:

• Ingest. seb_ingest_batch is one CSV import. It writes seb_amazon_seller (the listings) and seb_seller_snapshot (a daily metrics row, kept for time-series). Row-level import failures go to seb_ingest_batch_error.
• Identity. A listing attaches to a business via seb_amazon_seller.seller_group_id. This is set asynchronously by the grouping job, so freshly-ingested listings can briefly have a NULL group. seb_seller_group.merged_into_id marks a group that was merged into another.
• Work tracking. seb_state holds the current status of one (group, goal) pair. seb_attempt is the append-only history — one row every time a strategy runs against that state, win or lose. seb_strategy is the catalog the attempts reference.
• Website output. When a website attempt is approved, the domain is written to seb_seller_group.domain (the group, not the state).
• Email output. Email candidates become seb_email rows. seb_email_attribution records which attempt(s) produced each email (a many-to-many — a small share of emails are found by more than one strategy). seb_verification_pass logs each verifier's verdict; the latest is cached on seb_email.verification_verdict.

The state machine

seb_state.status puts every (group, goal) pair into exactly one bucket:

Status	Meaning	Dispatcher treats as
pending	No strategy attempted yet	Dispatchable (work it)
in_progress	Tried ≥1 strategy, no live candidate, more eligible	Dispatchable
review	A website candidate is parked awaiting a verdict	Blocked (skip until resolved)
enriched	Goal satisfied — domain landed, or a verified email landed	Terminal
exhausted	All eligible strategies tried, nothing found	Terminal

Transitions:

• pending → in_progress — an attempt returns no candidate but more strategies remain.
• in_progress → review — a website attempt produces a candidate (website goal only; email has no human review).
• review → enriched — the candidate is approved.
• review → in_progress / exhausted — the candidate is rejected (back to work, or terminal if nothing's left).
• → enriched (email) — a candidate is verified; email auto-transitions with no review step.
• → exhausted — eligible strategies run out with no candidate.

Strategies and attempts

A strategy is one enrichment method (a SERP scrape, an API lookup, a harvester). The catalog (seb_strategy) is code-owned — rows are created and toggled only by Alembic seeder migrations, never via API. Each strategy has:

• a goal and a priority (1 = highest; tried first),
• an is_active flag and an is_shadow flag (shadow = runs for measurement, not for shipping),
• a config JSONB eligibility predicate matched against the group's seb_amazon_seller attributes (e.g. min_rev, adr_countries, no_sendable_email),
• optional prerequisite_goal and prerequisite_strategy_id gates.

Each run produces a seb_attempt with outcome = candidate or no_candidate (the latter carries a no_candidate_reason). The enriched payload sits in seb_attempt.result (JSONB) — result->>'domain' for website strategies, result->>'email' for email strategies.

Currently active strategies (snapshot — June 2026)

Strategies drift as seeders toggle them. Query seb_strategy for live truth. As of this writing:

Goal	Active strategy	Priority	Notes
website	dataforseo_gmaps_gmb_v1	0	Google Maps / GMB via DataForSEO; rev + country gated
website	bbb_google_serp_dataforseo_v1	1	shadow (measurement only)
email	anymail_finder_v1	0	prerequisite_goal='website'; only fires on groups with no sendable email

Everything else (seb_domain_email_harvest_p1, seb_apollo_person_step1/2_p1, the legacy harvesters, manual_invalidation_v1, …) is inactive.

Transient task tables. seb_dataforseo_task and seb_bbb_scrape_task track in-flight third-party API calls between dispatch and result. They are scratch space — the durable record is always the seb_attempt they reconcile into.

Where a result came from (lineage / provenance)

The two goals record provenance differently. This matters a lot when you ask "which strategy found this?"

Website — reconstructed from the approve event

There is no attribution table for websites. The domain is written to seb_seller_group.domain, but the producing attempt is not stored as a column. You recover it by finding the approved attempt:

seb_seller_group.domain
  → seb_state (seller_group_id, goal='website')
    → seb_attempt (state_id)
      → seb_review_event (action='approve', round=1)   ← the winning attempt
      → seb_strategy (attempt.strategy_id → name)        ← the strategy

-- "What strategy produced the website for this seller?"
SELECT g.domain                AS website,
       st.name                 AS strategy_name,
       a.result ->> 'domain'   AS attempt_domain,   -- cross-check: == g.domain
       re.created_at           AS approved_at,
       re.reviewer_user_id                          -- AUTO_APPROVE sentinel if bot
FROM   orange_schema.seb_amazon_seller s
JOIN   orange_schema.seb_seller_group  g   ON g.id  = s.seller_group_id
JOIN   orange_schema.seb_state         ste ON ste.seller_group_id = g.id AND ste.goal = 'website'
JOIN   orange_schema.seb_attempt       a   ON a.state_id   = ste.id
JOIN   orange_schema.seb_review_event  re  ON re.attempt_id = a.id
                                          AND re.action = 'approve' AND re.round = 1
JOIN   orange_schema.seb_strategy      st  ON st.id = a.strategy_id
WHERE  s.amazon_seller_id = :amazon_seller_id
  AND  s.marketplace      = :marketplace
ORDER  BY re.created_at ASC;   -- earliest approve = original producer (see merge caveat)

Two caveats

• Merge. If two groups collided on the same domain they were merged; the surviving group can show more than one approve. The earliest is the real producer.
• Auto-approve. reviewer_user_id is a sentinel (AUTO_APPROVE) when a bot approved it; policy context lives in seb_review_event.metadata.

Email — explicit via the attribution table

Email lineage is first-class. seb_email_attribution links each email to the attempt(s) that produced it:

-- "What strategy(ies) produced a given email?"
SELECT e.email_address,
       e.verification_verdict,
       st.name AS strategy_name,
       att.discovered_at
FROM   orange_schema.seb_email             e
JOIN   orange_schema.seb_email_attribution att ON att.email_id = e.id
JOIN   orange_schema.seb_attempt           a   ON a.id = att.attempt_id
JOIN   orange_schema.seb_strategy          st  ON st.id = a.strategy_id
WHERE  e.email_address = :email_address;

Query cookbook

Common business questions, ready to run. All seb_* tables are in orange_schema (qualified below); or SET search_path TO orange_schema; first and drop the prefix.

1. How far has this seller gotten — both goals at once?

SELECT ste.goal, ste.status, ste.current_result, ste.reviewed_at, ste.first_verified_at
FROM   orange_schema.seb_amazon_seller s
JOIN   orange_schema.seb_state ste ON ste.seller_group_id = s.seller_group_id
WHERE  s.amazon_seller_id = :amazon_seller_id AND s.marketplace = :marketplace;

2. All sendable emails we have for a seller. "Sendable" = verdict success or inconclusive (the outreach denominator).

SELECT e.email_address, e.verification_verdict, e.person_name, e.job_title,
       e.address_type, e.domain_type
FROM   orange_schema.seb_amazon_seller s
JOIN   orange_schema.seb_email e ON e.seller_group_id = s.seller_group_id
WHERE  s.amazon_seller_id = :amazon_seller_id AND s.marketplace = :marketplace
  AND  e.verification_verdict IN ('success', 'inconclusive');

3. Find the seller group from a domain (then everything else hangs off it).

SELECT id AS seller_group_id, domain
FROM   orange_schema.seb_seller_group
WHERE  domain = :domain;

4. Funnel counts by status, for a goal.

SELECT status, COUNT(*) AS groups
FROM   orange_schema.seb_state
WHERE  goal = 'website'           -- or 'email'
GROUP  BY status ORDER BY groups DESC;

5. Strategy scoreboard — candidate rate per strategy.

SELECT st.goal, st.name,
       COUNT(*) FILTER (WHERE a.outcome = 'candidate')    AS candidates,
       COUNT(*)                                           AS attempts,
       ROUND(100.0 * COUNT(*) FILTER (WHERE a.outcome='candidate') / COUNT(*), 1) AS candidate_pct
FROM   orange_schema.seb_attempt a
JOIN   orange_schema.seb_strategy st ON st.id = a.strategy_id
GROUP  BY st.goal, st.name ORDER BY st.goal, candidate_pct DESC;

6. Websites found but no email yet (a re-targeting list):

SELECT g.id AS seller_group_id, g.domain
FROM   orange_schema.seb_seller_group g
JOIN   orange_schema.seb_state w ON w.seller_group_id = g.id AND w.goal='website' AND w.status='enriched'
LEFT   JOIN orange_schema.seb_email e
       ON e.seller_group_id = g.id AND e.verification_verdict IN ('success','inconclusive')
WHERE  e.id IS NULL;

7. Auto-approved vs human-approved websites (volume by reviewer kind):

SELECT CASE WHEN re.metadata ? 'run_id' THEN 'auto' ELSE 'human' END AS approved_by,
       COUNT(*) AS approvals
FROM   orange_schema.seb_review_event re
WHERE  re.action = 'approve' AND re.round = 1
GROUP  BY 1;

8. Did we actually email this seller, and did they reply? (the outreach bridge — join by email string)

SELECT e.email_address,
       lcm.status                                   AS campaign_status,
       COUNT(*) FILTER (WHERE m.type = 'SENT')      AS sent,
       COUNT(*) FILTER (WHERE m.type = 'REPLY')     AS replies
FROM   orange_schema.seb_email e
JOIN   orange_schema.jeff_sl_leads l            ON LOWER(l.email) = LOWER(e.email_address)
LEFT   JOIN orange_schema.jeff_sl_lead_campaign_map lcm ON lcm.lead_id = l.id
LEFT   JOIN orange_schema.jeff_sl_lead_messages     m   ON m.lead_id   = l.id
WHERE  e.seller_group_id = :seller_group_id
GROUP  BY e.email_address, lcm.status;

Downstream: the outreach bridge

SellerBot finds emails; Smartlead (jeff_sl_*) sends them. The seam between them has no foreign key — the two systems are joined by the email-address string.

The handoff rule: an email is pushed to a Smartlead campaign when it is sendable (verification_verdict IN ('success','inconclusive')), its group has a US/UK primary listing, and the group is not merged away. Once in Smartlead, send / reply / bounce events accrue in jeff_sl_lead_messages.

Because there's no FK, analytics re-joins the two sides on the email string (LOWER(seb_email.email_address) = LOWER(jeff_sl_leads.email)), which is exactly what the mv_outreach_* materialized views pre-compute. Full Smartlead schema is in Database Tables.

Materialized views (the Metabase surface)

Most dashboards read these, not the raw tables. Each is refreshed on a schedule.

View	Grain	What it's for
mv_sellerbot_seller_enriched	one seb_amazon_seller listing	Per-listing dims + coverage flags (has_website, has_sendable_email, revenue bucket, country segment, is_primary_for_group). The workhorse for funnel cross-tabs.
mv_sellerbot_opening_snapshot	(amazon_seller_id, marketplace)	Each seller's earliest revenue snapshot, classed by gap from launch (true_opening ≤120d, first_observation >120d, unknown, no_data) — "what shape were they in when we first saw them?"
mv_sellerbot_cohort_snapshot_dist	(cohort_month, marketplace, country_segment, snapshot_date, revenue_bucket)	Revenue-bucket distribution of each launch cohort over time, with cohort_n denominator and pct
mv_outreach_event	(group_id, lead_id, campaign_id, message_id, event_type)	One row per outreach event (SENT/REPLY/BOUNCED/FORWARD) over the hygienic pool, fully denormalized with seller/campaign/series/Jeff-selection dims
mv_outreach_lead_base	(group_id, email)	Every sendable target including the never-contacted — the outreach denominator (untouched-pool, new-vs-touched). LEFT JOIN to mv_outreach_event to find untouched
mv_outreach_contact_dim	one normalized email (lower(btrim(...)))	Attribute-only dimension lookup; the Smartlead funnel LEFT JOINs it on lower(btrim(email)) to slice by TOFU dims. Deduped so the join can't fan out

Provenance is not in the MVs

None of these carry the strategy that produced a website/email. For lineage you must query the raw tables (see lineage).

Glossary

• Listing (seb_amazon_seller) — one Amazon seller account on one marketplace. The raw input.
• Seller group (seb_seller_group) — one real-world business; the unit of enrichment. Many listings → one group.
• Primary listing — the canonical listing of a group (lowest id); used so group-grain queries don't fan out over every listing.
• Goal — website or email; what we're trying to discover.
• Strategy — one enrichment method for a goal, tried in priority order.
• Attempt — one logged run of a strategy against a state (append-only).
• Candidate — an attempt that produced something (vs no_candidate).
• State — the current status of one (group, goal) pair.
• Enriched — the goal is satisfied (domain landed, or verified email landed).
• Exhausted — all eligible strategies tried, nothing found.
• Sendable — an email with verdict success or inconclusive; the denominator the outreach funnel treats as usable.
• Verdict — verification result: success, fail, inconclusive, or NULL.
• Shadow strategy — runs for measurement only; its candidates never ship.
• AUTO_APPROVE — sentinel user id stamped on bot-written review events.