runloopable / platform /harness /amazon_ads_schema.py
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
812ec21 verified
Raw History Blame Contribute Delete
13.3 kB
"""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"]