"""amazon_ads_schema.py: the vendor schema artifact for the Amazon Ads tables (S79-T12, contract C6). One entry per ad type and report: the columns we store, the Reporting v1 field each one is read from, the row identity and the backfill window. The table keys, labels and backfill days come from `core.connector_spec`, so the two files cannot disagree on a name. Field ids come from Amazon's v1 field library, saved at `AutoWiki/research/amazon-ads-v1-schema/reporting_v1_fields.json`. v1 has no report types: one report per `REPORT_GROUPS` row covers several ad types, and its rows split by `adProduct.value`. """ import hashlib import sys from pathlib import Path from typing import NamedTuple _HERE = Path(__file__).resolve().parent if str(_HERE.parent) not in sys.path: sys.path.insert(0, str(_HERE.parent)) from core import connector_spec as _cs # noqa: E402 AD_TYPES = _cs.AMAZON_ADS_AD_TYPES REPORTS = _cs.AMAZON_ADS_REPORTS DATABASE_PREFIX = "ut_amzn_" MIRROR_PREFIX = "amzn_ads_" #: The C6 columns every Ads table carries, in this order. C6_COLUMNS = ("campaign_id", "ad_group_id", "target_id", "advertised_sku", "asin", "date", "impressions", "clicks", "cost", "sales", "orders") _INT = {"impressions", "clicks", "orders", "units"} _DBL = {"cost", "sales", "bid", "campaign_budget_amount"} #: Column labels, at most 40 characters, sentence case, no dash. LABELS = { "campaign_id": "Campaign ID", "ad_group_id": "Ad group ID", "target_id": "Target ID", "advertised_sku": "Advertised SKU", "asin": "ASIN", "date": "Date", "impressions": "Impressions", "clicks": "Clicks", "cost": "Spend", "sales": "Sales", "orders": "Orders", "units": "Units sold", "campaign_name": "Campaign", "campaign_status": "Campaign status", "campaign_budget_amount": "Daily budget", "ad_group_name": "Ad group", "ad_id": "Ad ID", "targeting": "Targeting", "match_type": "Match type", "bid": "Bid", "search_term": "Search term", "profile_id": "Profile ID", "currency": "Currency", } #: Per report, the columns beyond C6, in display order. _EXTRA = { "campaigns": ("campaign_name", "campaign_status", "campaign_budget_amount", "units"), "targeting": ("campaign_name", "ad_group_name", "targeting", "match_type", "bid", "units"), "advertised_products": ("campaign_name", "ad_group_name", "ad_id", "units"), "search_terms": ("campaign_name", "ad_group_name", "search_term", "targeting", "match_type", "units"), } _TAIL = ("profile_id", "currency") #: Our column -> its Reporting v1 field id, one map for every ad type. The store stamps profile_id. _VENDOR = { "date": "date.value", "campaign_id": "campaign.id", "campaign_name": "campaign.name", "campaign_status": "campaign.deliveryStatus", "campaign_budget_amount": "campaign.budgetAmount", "ad_group_id": "adGroup.id", "ad_group_name": "adGroup.name", "target_id": "adGroupTarget.id", "targeting": "target.value", "match_type": "target.matchType", "bid": "adGroupTarget.bid", "ad_id": "ad.id", "advertised_sku": "advertisedProductSku.value", "asin": "advertisedProduct.id", "search_term": "searchTerm.value", "impressions": "metric.impressions", "clicks": "metric.clicks", "cost": "metric.totalCost", "sales": "metric.sales", "orders": "metric.purchases", "units": "metric.unitsSold", "currency": "budgetCurrency.value", } #: The v1 field that names each row's ad type, and its value per ad type. AD_PRODUCT_FIELD = "adProduct.value" AD_PRODUCTS = {"sp": "SPONSORED_PRODUCTS", "sb": "SPONSORED_BRANDS", "sd": "SPONSORED_DISPLAY"} #: The name the v1 field library lists each ad type under. LIBRARY_AD_PRODUCTS = {"sp": "Sponsored Products", "sb": "Sponsored Brands", "sd": "Sponsored Display"} METRICS = ("metric.impressions", "metric.clicks", "metric.totalCost", "metric.sales", "metric.purchases", "metric.unitsSold") _HEAD = ("date.value", AD_PRODUCT_FIELD, "campaign.id", "campaign.name") _AD_GROUP = ("adGroup.id", "adGroup.name") _TAIL_FIELDS = ("budgetCurrency.value",) + METRICS #: ⛔ Not `targeting.value` or `targetingMatchType.value`: the library lists them for no ad product. #: The live probe of 2026-11-18 (`probe-2026-11-18.json`) proved these names, absent from the library. _TARGET_TEXT = ("target.value", "target.matchType") #: Fields a live Reporting v1 create accepted that the saved field library does not list. PROBE_PROVEN = frozenset(_TARGET_TEXT) class ReportGroup(NamedTuple): """One Reporting v1 report and the member tables its rows split into by `adProduct.value`. `optional` fields list no ad product in Amazon's library, or only a live probe proved them: a create that refuses one drops it. """ name: str members: tuple core: tuple optional: tuple = () @property def tables(self): """`adProduct.value` -> the member table its rows land in.""" return {AD_PRODUCTS[table.split("_", 1)[0]]: table for table in self.members} def fields(self, drop=()): """The fields one create asks for, without the optional fields Amazon refused.""" return tuple(f for f in self.core + self.optional if f not in drop) #: ⛔ Every requested dimension is a group-by and sets the row grain. A group asks only for its #: tables' identity dimensions, attributes of those, metrics, the ad product and required partners. #: Every core field lists every ad product of its group in the library: SB lacks the SKU field and #: the marketplace field `advertisedProduct.id` requires, so SB advertised products is its own report. REPORT_GROUPS = ( ReportGroup("campaigns", ("sp_campaigns", "sb_campaigns", "sd_campaigns"), _HEAD + ("campaign.deliveryStatus", "campaign.budgetAmount") + _TAIL_FIELDS), ReportGroup("targeting", ("sp_targeting", "sb_targeting", "sd_targeting"), _HEAD + _AD_GROUP + ("adGroupTarget.id",) + _TAIL_FIELDS, _TARGET_TEXT + ("adGroupTarget.bid",)), ReportGroup("advertised_products", ("sp_advertised_products", "sd_advertised_products"), _HEAD + _AD_GROUP + ("ad.id", "advertisedProduct.id", "advertisedProductMarketplace.value", "advertisedProductSku.value") + _TAIL_FIELDS), ReportGroup("advertised_products_sb", ("sb_advertised_products",), _HEAD + _AD_GROUP + ("ad.id",) + _TAIL_FIELDS), ReportGroup("search_terms", ("sp_search_terms", "sb_search_terms"), _HEAD + _AD_GROUP + ("adGroupTarget.id", "searchTerm.value") + _TAIL_FIELDS, _TARGET_TEXT), ) GROUPS = {group.name: group for group in REPORT_GROUPS} _GROUP_OF = {table: group for group in REPORT_GROUPS for table in group.members} def _short(ad_type, report): return f"{ad_type}_{report}" def _columns(report): out = list(C6_COLUMNS) for name in _EXTRA[report] + _TAIL: if name not in out: out.append(name) return tuple(out) def coltype(name): """The DuckDB type of one Ads column. Ids and dates are text, so a 17 digit id stays exact.""" return "BIGINT" if name in _INT else "DOUBLE" if name in _DBL else "VARCHAR" def vendor_name(column): """The v1 field one of our columns is read from, or None when the vendor has none.""" return _VENDOR.get(column) TABLE_SPECS = {} for _spec in _cs.AMAZON_ADS_TABLES: _ad_type, _report = _spec.key[len(DATABASE_PREFIX) + len("ads_"):].split("_", 1) _group = _GROUP_OF.get(_short(_ad_type, _report)) TABLE_SPECS[_short(_ad_type, _report)] = { "key": _spec.key, "source": _spec.source, "label": _spec.label, "ad_type": _ad_type, "report": _report, "group": _group.name if _group else None, "columns": _columns(_report), "row_identity": tuple(_spec.identity), "backfill_days": _cs.AMAZON_ADS_BACKFILL_DAYS[_spec.key], "offered": _spec.key not in _cs.AMAZON_ADS_NOT_OFFERED, "not_offered_reason": _cs.AMAZON_ADS_NOT_OFFERED.get(_spec.key), } del _spec, _ad_type, _report, _group def short_name(table): """`sp_campaigns`, `ads_sp_campaigns`, `amzn_ads_sp_campaigns` or the `ut_` key -> `sp_campaigns`.""" name = str(table or "") for prefix in (DATABASE_PREFIX + "ads_", MIRROR_PREFIX, "ads_"): if name.startswith(prefix): name = name[len(prefix):] break if name not in TABLE_SPECS: raise KeyError(f"unknown Amazon Ads table {table!r}") return name def columns_for(table): """Our stored columns for one Ads table, in order.""" return TABLE_SPECS[short_name(table)]["columns"] def group_of(table): """The report group that fills one Ads table, or None for a table v1 does not offer.""" return _GROUP_OF.get(short_name(table)) def group_for(fields, ad_products): """The report group a create request asks for, by its fields and ad product filter, or None.""" fields, ad_products = set(fields), set(ad_products) return next((g for g in REPORT_GROUPS if set(g.tables) == ad_products and set(g.core) <= fields <= set(g.core + g.optional)), None) def _cell(name, value): if value is None or value == "": return None kind = coltype(name) try: if kind == "BIGINT": return int(float(value)) if kind == "DOUBLE": return float(value) except (TypeError, ValueError): return None return str(value) def row_from_vendor(table, vendor_row, *, profile_id=None, report_id=None): """One flat v1 report row (dotted field ids) -> one stored row, with its `_row_key` and report origin.""" spec = TABLE_SPECS[short_name(table)] out = {} for column in spec["columns"]: source = vendor_name(column) value = vendor_row.get(source) if source else None if column == "profile_id": value = profile_id out[column] = _cell(column, value) out["_row_key"] = row_key(table, out) out["_report_id"] = None if report_id is None else str(report_id) return out def row_key(table, row): """The stable identity of one stored row: a re-sync of the same day updates, never appends.""" spec = TABLE_SPECS[short_name(table)] raw = "\x1f".join(str(row.get(c) if row.get(c) is not None else "") for c in spec["row_identity"]) return hashlib.sha1(f"{spec['source']}\x1f{raw}".encode("utf-8")).hexdigest() #: Columns the mirror adds to every Ads table, beside `columns_for`. ORIGIN_COLUMNS = ("_row_key", "_report_id") # Stream V1 has no historical report request and never enters TABLE_SPECS' daily loops. STREAM_WINDOWS = ("1d", "7d", "14d", "30d") STREAM_METRICS = ("cost", "impressions", "clicks") + tuple( f"attributed_{metric}_{window}{suffix}" for metric in ("sales", "conversions", "units_ordered") for window in STREAM_WINDOWS for suffix in ("", "_same_sku")) STREAM_GRAIN = ("profile_id", "advertiser_id", "marketplace_id", "currency", "timezone", "date", "hour", "time_window_start", "campaign_id", "ad_group_id", "ad_id", "keyword_id", "placement") STREAM_COLUMNS = STREAM_GRAIN + ("target_id", "keyword_text", "match_type", "_connection_id", "dataset_id", "idempotency_id", "received_at", "_row_key") + STREAM_METRICS STREAM_TABLE_SPECS = { "sp_hourly": {"key": "ut_amzn_ads_sp_hourly", "source": "amzn_ads_sp_hourly", "label": "SP hourly performance", "row_identity": ("_row_key",), "columns": STREAM_GRAIN + ("target_id", "keyword_text", "match_type", "_connection_id", "_row_key", "spend", "sales", "orders", "attribution_window", "event_count", "last_received_at") + STREAM_METRICS, "description": "Stream V1 deltas from activation only. Sales and orders use the explicitly named " "14d attribution window. All four windows remain available."}, } def stream_spec(table): """Resolve the separate stream catalogue without admitting it to daily report loops.""" name = str(table or "") for spec in STREAM_TABLE_SPECS.values(): if name in ("sp_hourly", "ads_sp_hourly", spec["key"], spec["source"]): return spec return None def stream_coltype(name): """Keep V1 integer counters and monetary deltas in distinct SQL types.""" if name in {"hour", "impressions", "clicks", "orders", "event_count"} or name.startswith(("attributed_conversions_", "attributed_units_ordered_")): return "BIGINT" if name in {"cost", "spend", "sales"} or name.startswith("attributed_sales_"): return "DOUBLE" return "VARCHAR" __all__ = ["AD_TYPES", "REPORTS", "C6_COLUMNS", "LABELS", "TABLE_SPECS", "AD_PRODUCT_FIELD", "AD_PRODUCTS", "LIBRARY_AD_PRODUCTS", "METRICS", "ReportGroup", "REPORT_GROUPS", "GROUPS", "ORIGIN_COLUMNS", "coltype", "vendor_name", "short_name", "columns_for", "group_of", "group_for", "row_from_vendor", "row_key", "STREAM_WINDOWS", "STREAM_METRICS", "STREAM_GRAIN", "STREAM_COLUMNS", "STREAM_TABLE_SPECS", "stream_spec", "stream_coltype"]