runloopable / platform /modules /amazon_operator_wasted.py
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
f462120 verified
Raw History Blame Contribute Delete
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}