Query semantics
Correct ecommerce analysis depends on what one record represents, how entities relate, and when the evidence was observed. These definitions apply to Explore evidence views, packaged Analyses, Explore AI, and Agent answers.
Choose the strongest contract
| Question | Preferred surface |
|---|---|
| Stable competitive price, price-movement, availability-change, or exact-assortment metric | Packaged Analysis |
| One-off grouping, count, exception list, or joined evidence question | Explore AI |
| Exact current market or Variant evidence | Purpose-built Explore view or public API |
| Raw extracted or normalized page data | Capture or Item view, API, export, or Query |
Packaged Analyses own their formulas, eligibility, denominators, and evidence. Explore AI may inspect the same data but must not silently reinterpret those contracts.
Organization scope and grain
The authenticated organization determines the customer dataset. Identifiers only have meaning inside that dataset unless a response explicitly says otherwise.
A Product family can have several exact Variants. A Variant can appear in several Listings, each belonging to one Store. Offer observations belong to individual Listings; review-count and score observations can be shared by Listings from the same source page or imported record within a Store.
See Relationships for the entity connections. Joining Listings to both Offer and Review histories can multiply rows, so counts and aggregates must first return to the grain named in the question. Deduplicate shared review sources before counting or weighting them.
Active Listings
An active Listing is the latest known Listing state without explicit removal evidence. A Listing does not become inactive merely because a later Offer is missing. Removed Listings remain available when a history or removal question explicitly asks for them.
Current Offers
Offers are append-only observations. The current Offer set is every Offer from each active Listing's latest observed Extract run.
Latest-run selection happens before filtering by country, currency, condition, seller, price, or availability. Selecting the latest row independently for each seller or currency can incorrectly retain Offers that disappeared from a later Listing observation.
At the natural current-Offer grain, one row represents a Listing, seller, condition, and currency. Store-level price or availability metrics deliberately reduce marketplace sellers so a Store with many sellers does not receive extra weight.
Current Reviews
Each Review records one source aggregate with a count and score normalized to
0-100. Current Reviews select the latest (observed_at, capture_id) per Store
and review_scope_key, associated with active Listings. Join on organization,
Store and scope; deduplicate those scopes before counting or weighting.
A page aggregate shared by several SKUs contributes once to a comparison. Variant details show its scope, observation time and how many Listings/Variants share it. Zero is an observed count; null means unknown. An observation with neither count nor score does not erase earlier known evidence.
These aggregates do not provide review text, sentiment, sales, or unique reviewer counts across Stores. Product matching does not prove that two pages share the same review source.
Observed assortment, newness, and removal
Observed assortment is the set of distinct exact Variants connected to active Listings in the selected Store and country scope. Multiple Listings and sellers must not multiply one Variant.
"New" means first observed in this organization's Extralt dataset:
- Variant newness uses its first-known time;
- Listing newness uses its first-seen time;
- Product-level launch time is not available and must not be invented.
"Removed" requires explicit Listing removal evidence. A missing seller or Offer is no longer observed, not automatically removed or out of stock.
Time meanings
| Time | Meaning |
|---|---|
| Observation or Run start | When Extralt observed the source page |
| Published time | A date reported by the source when available |
| First seen or first known | First valid evidence in this organization dataset |
| Last seen | Most recent valid Listing evidence |
| Removed time | Explicit Listing removal evidence |
| Change observation | When a semantic before/after change was recorded |
Stores are often observed at different times. Describe the actual evidence range rather than presenting asynchronous observations as one synchronized market snapshot.
Country, currency, and condition
Store and Listing context is country-specific. Partition market comparisons by country unless the question explicitly requires several markets.
Never compare or aggregate price amounts across currencies without an explicit conversion source and timestamp. Extralt does not currently provide currency conversion. Include currency in every price result and keep Offer condition explicit when it affects comparability.
Availability and stock
Availability is an observed state derived from current Offers. At Listing, seller, and condition grain:
- the state is available when any qualifying current row is available;
- otherwise it is unavailable when any row explicitly reports out of stock;
- otherwise it is unknown.
Currency is not part of availability grain unless the question explicitly asks for currency-specific availability. At Store and Variant grain, a Store is available when any qualifying current Offer is available.
Keep absent, unknown, explicitly unavailable, and removed distinct. Categorical stock does not reveal units on hand, future inventory, demand, lost sales, or continuous outage duration.
Missing values
An empty value or schema default is not necessarily a measured zero. A website may not expose a field at all. Use the source URL, observation time, result coverage, and relevant exclusions before interpreting absence as zero, false, or unavailable.
Coverage and freshness
A trustworthy result states the selected Stores and countries, currencies and conditions, evidence-time range, and included or excluded populations relevant to its denominator. When cross-store identity is required, matched and unmatched coverage must remain visible.
For an Analysis, summary, coverage, and freshness describe the selected scope;
the evidence rows can span several API pages. For Explore AI, the persisted
rows are a bounded result and truncated means the result should be narrowed.
Query execution boundary
Explore AI loads a model of the relevant entities, fields, relationships, and definitions before generating SQL. The service returns at most 100 rows and stores the effective SQL with the result. Your own SQL runs through the same execution with SQL queries, which publishes the same model and rules.
ClickHouse's reader role and row policies enforce read-only, organization- scoped access and resource ceilings. The API adds transport, execution, row, and response-size limits. Query-level settings cannot raise the role ceilings.
Continue with the semantic model for entity grains and Exploring data for Analysis behavior.
Taxonomy attributes
Items, Products and Variants expose arrays of English taxonomy value names under stable attribute keys. For example, fabric: ["Cotton", "Polyester"] preserves a blend. An empty array means unknown. SKU and Variant attributes contain configuration-specific overrides; an absent key inherits the Product value. Selected options remain the exact configuration and do not expand into unobserved combinations.
For an exact Variant, join Products and Variants on both organization and Product ID, apply FINAL to each table, and use mapUpdate(p.attributes, v.attributes). Filter membership with has(effective_attributes['fabric'], 'Cotton'). API and dashboard Variant details apply the same override rule; raw Item exports include both Product-level and SKU-specific arrays.