Download platform/modules/amazon_operator_wasted.py from fsanyoto/loopable: direct link, hf CLI and curl.
- Browser
- Download file 22.9 kB
-
https://huggingface.co/spaces/fsanyoto/loopable/resolve/9291c0100f4e7b8da2efcbbf1ebdea72ee1663e7/platform/modules/amazon_operator_wasted.py
- Command line
-
hf download hf://spaces/fsanyoto/loopable@9291c0100f4e7b8da2efcbbf1ebdea72ee1663e7/platform/modules/amazon_operator_wasted.py
-
curl -L -o amazon_operator_wasted.py https://huggingface.co/spaces/fsanyoto/loopable/resolve/9291c0100f4e7b8da2efcbbf1ebdea72ee1663e7/platform/modules/amazon_operator_wasted.py
22.9 kB
| """Store wasted-term groups as one JSON value under the store's 128 MB document ceiling. | |
| No rows are capped. Split this document by digest when its size approaches the ceiling. | |
| """ | |
| import base64 | |
| import copy | |
| import datetime as dt | |
| import hashlib | |
| import json | |
| from decimal import Decimal | |
| from modules import amazon_operator as op | |
| KEY = "amazon_operator_wasted" | |
| DEFAULT_WINDOW_DAYS = 14 | |
| PAGE_SIZE = 256 | |
| def clean_window(value): | |
| """Require a positive whole number of days within the supported calendar.""" | |
| number = op._num(value) | |
| if isinstance(value, bool) or number is None or not number.is_integer() or number < 1: | |
| raise ValueError("The wasted spend window must be a positive whole number of days.") | |
| try: | |
| (op._now().date() - dt.timedelta(days=int(number))).isoformat() | |
| except (OverflowError, ValueError) as exc: | |
| raise ValueError("The wasted spend window exceeds the supported calendar.") from exc | |
| return int(number) | |
| def setting_window_days(tenant_key, *, st=None): | |
| """Read the wasted_window_days setting, defaulting to fourteen days.""" | |
| st = op._rt(tenant_key, st) | |
| return clean_window(op._doc(st, op.KEY_SETTINGS).get("wasted_window_days", DEFAULT_WINDOW_DAYS)) | |
| def set_window(tenant_key, value, *, st=None): | |
| """Save the typed window in the existing Operator settings document.""" | |
| st, value = op._rt(tenant_key, st), clean_window(value) | |
| st.update(op.KEY_SETTINGS, lambda doc: {**(doc or {}), "wasted_window_days": value}) | |
| return value | |
| class _SearchPage: | |
| """Lend the existing search-row parser a SQL-bounded aggregate page.""" | |
| def __init__(self, cur, offset): | |
| self.cur, self.offset, self.count = cur, offset, 0 | |
| def execute(self, sql, args): | |
| if sql.rstrip().endswith("GROUP BY ALL"): | |
| self.result = self.cur.execute(sql + " ORDER BY ALL LIMIT ? OFFSET ?", | |
| [*args, PAGE_SIZE, self.offset]) | |
| return self | |
| if "information_schema.columns" not in sql: | |
| raise RuntimeError("The search-term reader changed. Update its paged adapter.") | |
| return self.cur.execute(sql, args) | |
| def fetchall(self): | |
| rows = self.result.fetchall() | |
| self.count = len(rows) | |
| return rows | |
| def wasted_terms(tenant_key, window_days=None, *, st=None, now=None): | |
| """List scoped search terms with spend and no orders over the last complete days.""" | |
| st = op._rt(tenant_key, st) | |
| days = clean_window(window_days) if window_days is not None else setting_window_days(tenant_key, st=st) | |
| last = (now or op._now()).date() - dt.timedelta(days=1) | |
| first = last - dt.timedelta(days=days - 1) | |
| terms, reports = {}, 0 | |
| with op._reader(tenant_key, st) as cur: | |
| if cur is None: | |
| raise RuntimeError("The tenant Ads mirror is unavailable.") | |
| for ad_type in op.ALL_AD_TYPES: | |
| table = op._ads_table(ad_type, "search_terms") | |
| cols = op._columns(cur, table) | |
| if not cols: | |
| continue | |
| if not {"date", "campaign_id", "cost", "orders", "clicks"} <= cols or not cols.intersection( | |
| {"search_term", "query", "keyword"}): | |
| raise ValueError(f"{table} lacks the search-term metrics. Sync Amazon Ads first.") | |
| invalid = " OR ".join( | |
| f'(TRY_CAST("{field}" AS DOUBLE) IS NULL OR NOT isfinite(TRY_CAST("{field}" AS DOUBLE)))' | |
| for field in ("cost", "orders", "clicks")) | |
| bad = cur.execute(f"SELECT COUNT(*) FROM {table} WHERE {op._DATE} BETWEEN ? AND ? AND ({invalid})", | |
| [first.isoformat(), last.isoformat()]).fetchone()[0] | |
| if bad: | |
| raise ValueError(f"{table} contains missing or invalid search-term metrics. Sync Amazon Ads first.") | |
| reports += 1 | |
| offset = 0 | |
| while True: | |
| page = _SearchPage(cur, offset) | |
| rows = op._search_rows(page, ad_type, first.isoformat(), last.isoformat()) | |
| for row in rows: | |
| key = term_scope(row) | |
| item = terms.setdefault(key, {**row, "spend": Decimal(0), "orders": 0, "clicks": 0}) | |
| if any(op._num(row[field]) is None for field in ("cost", "orders", "clicks")): | |
| raise ValueError("Search-term metrics contain invalid numbers.") | |
| item["spend"] += Decimal(str(row["cost"])) | |
| item["orders"] += row["orders"] | |
| item["clicks"] += row["clicks"] | |
| if page.count < PAGE_SIZE: | |
| break | |
| offset += page.count | |
| if not reports: | |
| raise RuntimeError(op.NOT_INSTALLED) | |
| out = [] | |
| for identity, item in sorted(terms.items()): | |
| if item["orders"] != 0 or item["spend"] <= 0: | |
| continue | |
| item.pop("cost", None) | |
| item["id"] = "wt_" + hashlib.sha256(json.dumps(identity).encode()).hexdigest()[:24] | |
| item["spend"] = float(item["spend"].quantize(Decimal("0.01"))) | |
| out.append(item) | |
| return out | |
| def digest(terms): | |
| """Hash the complete canonical term set, independent of source ordering.""" | |
| raw = json.dumps(sorted(terms, key=lambda row: row["id"]), sort_keys=True, ensure_ascii=False, | |
| separators=(",", ":"), allow_nan=False) | |
| return hashlib.sha256(raw.encode("utf-8")).hexdigest() | |
| def saved(tenant_key, *, st=None): | |
| """Read the saved digest without generating groups.""" | |
| return op._doc(op._rt(tenant_key, st), KEY) | |
| def save(tenant_key, terms, grouped, days, *, st=None, now=None): | |
| """Project the latest source window separately from immutable claimed batches.""" | |
| st = op._rt(tenant_key, st) | |
| source = {term_scope(t): copy.deepcopy(t) for t in terms} | |
| if len(source) != len(terms) or len({t["id"] for t in terms}) != len(terms): | |
| raise ValueError("The source repeats a search-term scope or id.") | |
| record = {**copy.deepcopy(grouped), "digest": digest(terms), "window_days": days, | |
| "built_at": op._stamp(now or op._now())} | |
| def merge(doc): | |
| retained = {gid: g for gid, g in (doc or {}).get("groups", {}).items() | |
| if g.get("status", "proposed") != "proposed"} | |
| groups, current, covered = dict(retained), {}, set() | |
| def projection(gid, row, members, outcome_ids=None): | |
| return {"id": gid, "label": row["label"], "reason": row.get("reason"), "terms": members, | |
| "term_count": len(members), "cost": float(sum(Decimal(str(t["spend"])) for t in members)), | |
| "outcome_ids": outcome_ids or {}} | |
| for gid, row in sorted(retained.items()): | |
| members, outcome_ids = [], {} | |
| for old in row["terms"]: | |
| scope = term_scope(old) | |
| if scope in source and scope not in covered: | |
| term = source[scope] | |
| members.append(term) | |
| outcome_ids[term["id"]] = gid + ":" + old["id"] | |
| covered.add(scope) | |
| if members: | |
| current[gid] = projection(gid, row, members, outcome_ids) | |
| for row in copy.deepcopy(grouped["groups"]): | |
| remaining = [] | |
| for term in row["terms"]: | |
| scope = term_scope(term) | |
| if scope in source and scope not in covered: | |
| remaining.append(source[scope]) | |
| covered.add(scope) | |
| if not remaining: | |
| continue | |
| gid = row["id"] | |
| if gid in groups or len(remaining) != len(row["terms"]): | |
| identity = [record["digest"], sorted(t["id"] for t in remaining)] | |
| gid = "wg_" + hashlib.sha256(json.dumps(identity).encode()).hexdigest()[:24] | |
| if gid in groups: | |
| raise ValueError("The current group conflicts with a saved batch.") | |
| row.update(terms=remaining, term_count=len(remaining), digest=record["digest"], status="proposed", | |
| id=gid, cost=float(sum(Decimal(str(t["spend"])) for t in remaining))) | |
| groups[gid] = row | |
| current[gid] = projection(gid, row, remaining) | |
| if covered != source.keys(): | |
| raise ValueError("The groups do not cover every current source term.") | |
| return {**record, "groups": groups, "current_groups": current, "group_count": len(current), | |
| "term_count": len(source), | |
| "total_spend": float(sum((Decimal(str(t["spend"])) for t in terms), Decimal(0)))} | |
| result = st.update(KEY, merge) | |
| suppress_singles(tenant_key, st=st) | |
| return result | |
| def term_scope(term): | |
| """Identify one exact Ads destination and normalized term without crossing campaign boundaries.""" | |
| return tuple(str(term.get(k) or "").strip() for k in | |
| ("ad_type", "connection_id", "profile_id", "campaign_id", "ad_group_id")) + ( | |
| " ".join(str(term.get("term") or term.get("keyword_text") or "").split()).lower(),) | |
| def in_grouped_scope(term, scopes): | |
| """Retire legacy unscoped singles conservatively within their exact campaign and ad group.""" | |
| scope = term_scope(term) | |
| if scope in scopes: | |
| return True | |
| return any(scope[0] == group[0] and scope[3:] == group[3:] | |
| and all(not value or not target or value == target | |
| for value, target in zip(scope[1:3], group[1:3])) for group in scopes) | |
| def grouped_scopes(tenant_key, *, st=None): | |
| """Cover every saved group, including sent history, so a daily build cannot repeat its terms.""" | |
| return {term_scope(t) for g in saved(tenant_key, st=st).get("groups", {}).values() for t in g.get("terms", [])} | |
| def suppress_singles(tenant_key, *, st=None): | |
| """Retire only still-open single-term proposals already owned by a saved group.""" | |
| st = op._rt(tenant_key, st) | |
| scopes = grouped_scopes(tenant_key, st=st) | |
| if not scopes: | |
| return | |
| def apply(doc): | |
| for day in (doc or {}).get("days", {}).values(): | |
| for row in day.get("items", {}).values(): | |
| if (row.get("kind") == "negative_keyword" and row.get("status") in op.OPEN | |
| and in_grouped_scope(row.get("payload") or {}, scopes)): | |
| row.update(status="dismissed", note="Included in a wasted spend group.", grouped=True) | |
| return doc or {} | |
| st.update(op.KEY_DAYS, apply) | |
| def _writes(group): | |
| """Freeze each term's exact destination and match type into the existing C3 write shape.""" | |
| out, scopes = [], set() | |
| for term in group["terms"]: | |
| scope = term_scope(term) | |
| if scope in scopes: | |
| raise ValueError("The group repeats an Ads term scope.") | |
| scopes.add(scope) | |
| if any(not term.get(k) for k in ("id", "ad_type", "profile_id", "campaign_id", "ad_group_id", "term")): | |
| raise ValueError("Each term needs its saved profile, campaign and ad group. Refresh Amazon Ads.") | |
| pid = group["id"] + ":" + term["id"] | |
| payload = {k: str(term[k]) for k in ("ad_type", "profile_id", "campaign_id", "ad_group_id")} | |
| if term.get("connection_id"): | |
| payload["connection_id"] = str(term["connection_id"]) | |
| payload.update(operation="add_negative_keyword", keyword_text=term["term"], | |
| match_type="negativeExact", item_id=pid) | |
| out.append({"item_id": pid, "kind": "negative_keyword", "payload": payload}) | |
| if not out: | |
| raise ValueError("The group has no saved terms.") | |
| if len({w["item_id"] for w in out}) != len(out): | |
| raise ValueError("The group repeats a saved term id.") | |
| return out | |
| def _refusal(result): | |
| """Read an explicit per-term refusal from an otherwise successful Ads response.""" | |
| import re | |
| def plain(value): | |
| return op._plain(value).strip() if isinstance(value, str) and not any(c in value for c in "{}[]") else None | |
| def cause(errors): | |
| rows = errors if isinstance(errors, list) else [errors] | |
| for row in rows: | |
| message = plain(row.get("message")) if isinstance(row, dict) else plain(row) | |
| if message: | |
| return message | |
| for row in rows: | |
| code = row.get("code") if isinstance(row, dict) else None | |
| if isinstance(code, str) and re.fullmatch(r"[A-Za-z0-9_ -]+", code): | |
| cleaned = " ".join(code.lower().replace("_", " ").split()) | |
| if cleaned: | |
| return cleaned | |
| return "The provider did not give a plain reason." | |
| body = result.get("response") if isinstance(result, dict) else None | |
| collection = body.get("negativeKeywords", body) if isinstance(body, dict) else body | |
| if isinstance(collection, dict): | |
| errors = collection.get("error") or collection.get("errors") | |
| if errors: | |
| return "Amazon Ads refused this term: " + cause(errors) | |
| if isinstance(collection, list): | |
| errors = [row for row in collection if isinstance(row, dict) and row.get("code") | |
| and str(row["code"]).upper() not in ("SUCCESS", "200", "201")] | |
| if errors: | |
| return "Amazon Ads refused this term: " + cause(errors) | |
| return None | |
| def approve(tenant_key, user, group_id, digest, *, st=None, now=None): | |
| """Claim one immutable group and save one approval before sending through the named Ads door.""" | |
| import uuid | |
| st = op._rt(tenant_key, st) | |
| if str(getattr(st, "key", tenant_key)).strip().lower() != str(tenant_key).strip().lower(): | |
| raise PermissionError("The wasted spend store belongs to another tenant.") | |
| who = op._editor(st, user, tenant_key) | |
| at, approval_id = op._stamp(now or op._now()), "apv_" + uuid.uuid4().hex[:16] | |
| claimed = [] | |
| def claim(doc): | |
| claimed.clear() | |
| group = (doc or {}).get("groups", {}).get(group_id) | |
| if not isinstance(group, dict): | |
| raise KeyError(group_id) | |
| if group.get("status", "proposed") != "proposed": | |
| return doc | |
| if not digest or digest != doc.get("digest") or digest != group.get("digest", doc.get("digest")): | |
| raise ValueError("These groups changed. Reload wasted spend before approving.") | |
| writes = _writes(group) | |
| group.update(status="unknown", decision_id=approval_id, decided_by=who, decided_at=at, | |
| digest=digest, writes=writes, | |
| outcomes={w["item_id"]: {"status": "unknown", "cause": "Send outcome has not been recorded."} | |
| for w in writes}) | |
| claimed.append(copy.deepcopy(group)) | |
| return doc | |
| st.update(KEY, claim) | |
| if not claimed: | |
| return {"group_id": group_id, "status": "skipped", "cause": "This group already has a decision."} | |
| group = claimed[0] | |
| record = {"id": approval_id, "decision": "approved", "by": who, "at": at, "note": None, | |
| "source": "wasted_spend", "tenant": tenant_key, "group_id": group_id, "digest": digest, | |
| "item_ids": [w["item_id"] for w in group["writes"]], "writes": group["writes"]} | |
| op._put_decision(st, record) | |
| client = op._ads_client() | |
| for write in group["writes"]: | |
| result = None | |
| if client is None: | |
| status, cause = "refused", op.NOT_INSTALLED | |
| else: | |
| try: | |
| result = op._json_safe(client.send_named_write(tenant_key, write["kind"], copy.deepcopy(write["payload"]), | |
| approval_id=approval_id, rt=st)) | |
| except Exception as exc: | |
| status = "refused" if type(exc).__name__ == "AdsWriteRefused" else "unknown" | |
| cause = op._cause(exc) if status == "refused" else "The send outcome is unknown. Check Amazon Ads before taking further action." | |
| else: | |
| cause = _refusal(result) | |
| status = "refused" if cause else "sent" | |
| outcome = {"status": status, "cause": cause, "result": result, | |
| "sent_payload": write["payload"] if status == "sent" else None, "at": at} | |
| def finish(doc): | |
| row = (doc or {}).get("groups", {}).get(group_id) | |
| if not row or row.get("decision_id") != approval_id: | |
| raise RuntimeError("The approved group is no longer stored.") | |
| row["outcomes"][write["item_id"]] = outcome | |
| return doc | |
| st.update(KEY, finish) | |
| def log(doc): | |
| doc = doc or {} | |
| doc.setdefault("items", {})[write["item_id"]] = { | |
| **outcome, "kind": write["kind"], "decision_id": approval_id, | |
| "decided_by": who, "group_id": group_id} | |
| return doc | |
| st.update(op.KEY_OUTCOMES, log) | |
| def settle(doc): | |
| row = doc["groups"][group_id] | |
| states = [v["status"] for v in row["outcomes"].values()] | |
| if all(s == "sent" for s in states): | |
| row["status"] = "sent" | |
| elif "unknown" in states: | |
| row["status"] = "unknown" | |
| else: | |
| row["status"] = "partial" if "sent" in states else "refused" | |
| return doc | |
| st.update(KEY, settle) | |
| suppress_singles(tenant_key, st=st) | |
| return {"group_id": group_id, "approval_id": approval_id, "status": saved(tenant_key, st=st)["groups"][group_id]["status"]} | |
| def read(tenant_key, *, st=None, cursor=None, limit=50, group_id=None, terms_cursor=None, history=False): | |
| """Read bounded group and term pages from the saved document with no model or store writes.""" | |
| st = op._rt(tenant_key, st) | |
| if isinstance(limit, bool) or not isinstance(limit, int) or limit < 1: | |
| raise ValueError("The wasted spend page size must be a positive whole number.") | |
| with op._screen_store(tenant_key, st, documents=(KEY,)) as (execute, pg, _names): | |
| p, each, array = ("%s", "jsonb_each", "jsonb_array_elements") if pg else ("?", "json_each", "json_each") | |
| source = f"SELECT value FROM {{tbl}} WHERE key = {p}" if pg else f"SELECT value FROM docs WHERE name = {p}" | |
| projection = "value -> 'groups'" if history else "value -> 'current_groups'" | |
| sql = (f"WITH doc AS ({source}), group_doc AS (SELECT value -> 'groups' AS decisions, " | |
| f"{projection} AS projection FROM doc), group_rows AS (SELECT g.key, g.value AS r, " | |
| f"decisions -> g.key AS decision FROM group_doc, LATERAL {each}(projection) g " | |
| + ("WHERE g.value ->> 'status' <> 'proposed' " if history else "") + ") ") | |
| meta = execute(sql + "SELECT value ->> 'digest', value ->> 'window_days', value ->> 'built_at', " | |
| "value ->> 'state', value ->> 'reason', value ->> 'total_spend', " | |
| "value ->> 'term_count', value ->> 'group_count', " | |
| "(value -> 'current_groups') IS NOT NULL FROM doc LIMIT 1").fetchone() | |
| if not meta or not meta[0] or (not history and not meta[8]): | |
| return {"state": "pending", "reason": "Wasted spend has not been grouped yet.", "digest": None, | |
| "groups": [], "total_spend": None, "term_count": 0, "group_count": 0, "cursor": None} | |
| dg, days, at, state, reason, total, term_count, group_count, _projected = meta | |
| if history: | |
| total, term_count, group_count = execute(sql + "SELECT COALESCE(SUM(CAST(r ->> 'cost' AS DOUBLE PRECISION)), 0), " | |
| "COALESCE(SUM(CAST(r ->> 'term_count' AS BIGINT)), 0), COUNT(*) FROM group_rows").fetchone() | |
| scope_prefix = "history:" if history else "" | |
| def offset(token, scope): | |
| if not token: | |
| return 0 | |
| try: | |
| value = json.loads(base64.urlsafe_b64decode(str(token).encode("ascii")).decode("utf-8")) | |
| except (ValueError, UnicodeError) as exc: | |
| raise ValueError("The wasted spend cursor could not be read.") from exc | |
| if not isinstance(value, list) or len(value) != 3 or value[:2] != [dg, scope_prefix + scope] or not str(value[2]).isdigit(): | |
| raise ValueError("The wasted spend cursor no longer matches these groups.") | |
| return int(value[2]) | |
| start = offset(cursor, "groups") | |
| term_start = offset(terms_cursor, str(group_id)) | |
| if terms_cursor and not group_id: | |
| raise ValueError("A term cursor must name its group.") | |
| groups_sql = sql + "SELECT g.key, g.r ->> 'label', g.r ->> 'cost', " + ( | |
| "g.r ->> 'term_count', g.r ->> 'reason', g.decision ->> 'status', " | |
| "g.decision ->> 'decided_by', g.decision ->> 'decided_at', g.decision ->> 'digest', " | |
| "g.decision ->> 'decision_id' FROM group_rows g ") | |
| rows = execute(groups_sql + (f"WHERE g.key = {p} " if group_id else "") + f"ORDER BY g.key LIMIT {p} OFFSET {p}", | |
| ([group_id] if group_id else []) + [limit, start]).fetchall() | |
| groups = [] | |
| for gid, label, cost, count, why, status, who, decided_at, group_digest, decision_id in rows: | |
| count = int(count) | |
| terms = execute(sql + "SELECT t.value, g.decision -> 'outcomes' -> " | |
| "COALESCE(g.r -> 'outcome_ids' ->> (t.value ->> 'id'), (g.key || ':' || (t.value ->> 'id'))) " | |
| f"FROM group_rows g, LATERAL {array}(g.r -> 'terms') t WHERE g.key = {p} " | |
| f"ORDER BY t.value ->> 'id' LIMIT {p} OFFSET {p}", | |
| [gid, limit, term_start]).fetchall() | |
| def parsed(value): | |
| return value if isinstance(value, dict) else json.loads(value) if value else None | |
| groups.append({"id": gid, "label": label, "cost": float(cost), "term_count": count, "reason": why, | |
| "status": status or "proposed", "decided_by": who, "decided_at": decided_at, | |
| "digest": group_digest or dg, "decision_id": decision_id, "match_type": "negativeExact", | |
| "terms": [{**parsed(t), "match_type": "negativeExact", "outcome": parsed(outcome)} | |
| for t, outcome in terms], | |
| "terms_cursor": op._encode([dg, scope_prefix + gid, term_start + len(terms)]) | |
| if term_start + len(terms) < count else None}) | |
| if group_id and not groups: | |
| raise KeyError(group_id) | |
| end = start + len(groups) | |
| return {"digest": dg, "window_days": None if history else int(days), "built_at": at, "state": state, "reason": reason, | |
| "total_spend": float(total), "term_count": int(term_count), "group_count": int(group_count), | |
| "groups": groups, "cursor": op._encode([dg, scope_prefix + "groups", end]) | |
| if not group_id and end < int(group_count) else None} | |