Download platform/harness/amazon_ads_schema.py from fsanyoto/loopable: direct link, hf CLI and curl.
- Browser
- Download file 13.3 kB
-
https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/platform/harness/amazon_ads_schema.py
- Command line
-
hf download hf://spaces/fsanyoto/loopable/platform/harness/amazon_ads_schema.py
-
curl -L -o amazon_ads_schema.py https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/platform/harness/amazon_ads_schema.py
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 = () | |
| 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"] | |