# Data Dictionary
PLOTT DATA · Wayfair US Rank Intelligence · SONGMICS HOME sample · 2026-09-01

Types: `str`, `int`, `float`, `bool`, `date` (ISO-8601 `YYYY-MM-DD`).
"Measured" = read directly off the live Wayfair page. "Derived" = computed by PLOTT
from measured fields.

---

## `observed_snapshot_real.csv` — the real day (1,560 rows)
Also the column set of `fact_daily_rank.csv`.

| Column | Type | Source | Definition |
|---|---|---|---|
| `collection_date` | date | measured | UTC date of the collection run |
| `is_observed` | bool | meta | `True` = really collected. `False` = illustrative back-simulated row |
| `category_slug` | str | meta | Stable PLOTT key for the sub-category |
| `category_name` | str | measured | Wayfair's display name for the sub-category |
| `department` | str | measured | Wayfair top-level department |
| `sku` | str | measured | Wayfair SKU (e.g. `W005401895`, `VSLE2912`). Primary product key |
| `product_name` | str | measured | Listing title as displayed |
| `brand` | str | measured | Brand shown on the listing card, ® / ™ stripped |
| `brand_type` | str | derived | `wayfair_house` / `third_party` / `client` — classified against Wayfair's ~180 private-label brands and the SONGMICS brand family |
| `organic_rank` | int | derived | Position among **non-sponsored** results, 1 = best. `null` for sponsored slots |
| `slot_position` | int | measured | Position as displayed to the shopper, ads included |
| `rank_band` | str | derived | `01-12`, `13-24`, `25-48`, `49-72`, `73+` |
| `is_sponsored` | bool | measured | Paid placement (detected via `auctionId`/`adType` params or the Sponsored label) |
| `price` | float | measured | Current price, USD |
| `original_price` | float | measured | Strike-through list price, USD. `null` if not on promotion |
| `discount_pct` | float | derived | `(original - price) / original × 100` |
| `price_type` | str | measured | `sale` / `regular` / `clearance` |
| `listing_flag` | str | measured | Merchandising badge, e.g. `Labor Day Deal`, `Sale` |
| `rating` | float | measured | Mean star rating, 0–5 |
| `review_count` | int | measured | Cumulative lifetime review count. `0` where the card shows no reviews |
| `shipping_text` | str | measured | Delivery promise, e.g. `FREE Delivery` |
| `product_url` | str | measured | Canonical PDP URL |

## `client_catalog.csv` — SONGMICS HOME + VASAGLE on Wayfair (36 rows, all measured)

| Column | Type | Definition |
|---|---|---|
| `client_brand` | str | `SONGMICS HOME` or `VASAGLE` |
| `sku` | str | Wayfair SKU. Prefix `NNGM` = SONGMICS HOME, `VSLE` = VASAGLE |
| `product_name`, `price_usd`, `original_price_usd`, `discount_pct` | | As above |
| `rating`, `review_count` | float, int | As above |
| `has_reviews` | bool | `review_count > 0` |
| `ranks_in_tracked_categories` | bool | Whether this SKU appeared anywhere in the 1,560 tracked slots |
| `listing_flag`, `shipping_text`, `available_colors` | str | As above |
| `product_url`, `brand_page_url` | str | Canonical URLs |

## `analysis_rank_band.csv` — cross-section by rank band (5 rows, all measured)

| Column | Type | Definition |
|---|---|---|
| `rank_band` | str | Organic rank band |
| `listings` | int | Listings observed in the band |
| `median_review_count` / `mean_review_count` | float | Central tendency of cumulative reviews |
| `median_rating` | float | Median star rating |
| `median_price_usd` | float | Median current price |
| `house_brand_share_pct` | float | % of the band held by Wayfair private-label brands |
| `third_party_share_pct` | float | % held by independent brands |
| `listings_with_zero_reviews_pct` | float | % of the band with no reviews yet |
| `median_review_count_index` | float | Band median ÷ the `49-72` band median. The empirical visibility gradient |

## `analysis_share_of_shelf.csv` — per category (12 rows, all measured)

| Column | Type | Definition |
|---|---|---|
| `slots_tracked` | int | Total slots collected (~144 = 3 pages) |
| `organic_slots` / `sponsored_slots` | int | Split of those slots |
| `sponsored_density_pct` | float | Share of all slots that are paid |
| `house_brand_share_organic_pct` | float | Wayfair private-label share of organic slots |
| `third_party_share_organic_pct` | float | Independent-brand share of organic slots |
| `client_share_organic_pct` | float | SONGMICS-family share of organic slots |
| `house_brand_share_top12_pct` | float | Private-label share of the top 12 organic slots |
| `distinct_brands_organic` | int | Distinct brands competing organically |
| `median_price_usd`, `median_review_count` | float | Category central tendency |
| `review_entry_bar_top12` | float | Median review count of the top-12 organic listings — the practical review threshold for reaching page-1 top |

## `keyword_rankings_raw.json` — head-keyword ranks (all measured)
`rows[]` carries `keyword`, `result_page`, `slot_position`, `organic_rank`,
`is_sponsored`, plus the same product fields as above. `brand_focus` tags which
SONGMICS brand the keyword is relevant to.

## `fact_weekly_snapshot.csv` — weekly roll-up (illustrative)

| Column | Type | Definition |
|---|---|---|
| `week_start` / `week_end` / `week_index` | date, int | Week boundaries; index 1–4 |
| `avg_organic_rank` | float | Mean organic rank across the week |
| `best_organic_rank` / `worst_organic_rank` | int | Range within the week |
| `rank_volatility_sd` | float | Population SD of daily rank — the stability measure |
| `days_on_page_1_pct` | int | % of days ranked ≤ 48 |
| `avg_price_usd` / `min_price_usd` | float | Weekly price behaviour |
| `review_count_start` / `review_count_end` | int | Cumulative reviews at each end of the week |
| `new_reviews` | int | `review_count_end − review_count_start`. **The velocity signal** |
| `est_units_sold_low` | int | `new_reviews / 0.04` |
| `est_units_sold_mid` | int | `new_reviews / 0.03` |
| `est_units_sold_high` | int | `new_reviews / 0.02` |
| `is_observed_week` | bool | Whether the week ends on the real snapshot day |

## `dim_category.csv`
`category_slug`, `category_name`, `department`, `brand_focus`,
`wayfair_category_id` (the `c…` id in the URL), `category_url`, `listings_tracked`.

## `dim_product.csv`
`sku`, `category_slug`, `product_name`, `brand`, `brand_type`, `department`,
`available_colors`, `available_sizes_text`, `product_url`, `image_url`,
`first_seen`, `last_seen`.

## `manifest.json`
Run metadata: `snapshot_date`, `run_id`, `postal_code`, `pages_per_category`,
`observed_listings`, `panel_days`, `review_rate_assumption`, `seed`, and
`rank_visibility_weights` (the calibration curve derived from the observed data).

---

## Join keys

```
dim_product.sku            1─n  fact_daily_rank.sku
dim_category.category_slug 1─n  fact_daily_rank.category_slug
fact_daily_rank            n─1  fact_weekly_snapshot  on (sku, category_slug, week)
client_catalog.sku         1─1  dim_product.sku       where the SKU ranks
```

`sku` is unique per product across all of Wayfair. `(collection_date, category_slug,
sku)` is the grain of `fact_daily_rank.csv`; a SKU can legitimately appear in more
than one category on the same day.

## Known coverage notes

- **Sponsored slots carry `organic_rank = null` by design.** Filter on
  `is_sponsored = False` for merchandising analysis; use `slot_position` for
  true share-of-voice including ads.
- **`Cat Trees, Perches & Scratchers` (c1874142)** renders its result grid
  client-side after hydration, so HTML collection yields ~17 of ~144 slots.
  The keyword surface (`cat tree`, `cat tower`, `cat condo`, `cat scratching post`)
  covers the same demand fully and is included at full depth. In production this
  category is collected via the rendered-browser path.
- **Review counts are cumulative and can decrease slightly** when Wayfair removes
  reviews. Treat negative `new_reviews` as zero for unit estimation.