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

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.

The connected Explore path is:

Product -> Variant -> Listing -> Offer observations
                         |-> Review observations
Store -------------------+

A Product family can have several exact Variants. One Variant can resolve to several Store Listings. One Listing can have many Offer and Review observations over time. Joining several one-to-many paths can multiply rows, so counts and aggregates must first return to the grain named in the question.

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

Reviews are append-only aggregate observations, not one mutable row. Current Reviews select the latest known aggregate for each active Listing. Each observation contains a review count and a score normalized to 0-100.

Review counts and scores are evidence attached to a Listing. They do not provide review text, sentiment, unique reviewers, sales, or reliable additive counts across syndicated Products or sibling Variants.

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

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:

1. the state is available when any qualifying current row is available;
2. otherwise it is unavailable when any row explicitly reports out of stock;
3. 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 versioned 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. It is a dashboard feature, not unrestricted public SQL access.

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.