runloopable / platform /modules /amazon_operator_overview.py
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
f462120 verified
Raw History Blame Contribute Delete
13.4 kB
"""Read-only Operator dashboard figures from campaign reports and Amazon economics."""
import datetime as dt
import hashlib
import math
from modules import amazon_operator as op
AD_TYPES = ("sp", "sb", "sd")
ECONOMICS = "amzn_economics"
METRICS = ("spend", "ad_sales", "orders", "total_sales", "organic_sales", "acos", "tacos")
SORTS = {"spend", "sales", "orders", "acos", "campaign"}
def _period(start, end):
try:
first, last = (dt.date.fromisoformat(str(value)) for value in (start, end))
except (TypeError, ValueError) as exc:
raise ValueError("Use YYYY-MM-DD for the overview dates.") from exc
if first > last:
raise ValueError("The overview start must be on or before its end.")
return first.isoformat(), last.isoformat()
def _ratio(spend, sales):
return round(100 * spend / sales, 2) if spend is not None and sales is not None and sales > 0 else None
def _figures(spend, sales, orders, total):
return {"spend": round(spend, 2), "ad_sales": round(sales, 2), "orders": orders,
"total_sales": round(total, 2) if total is not None else None,
"organic_sales": round(total - sales, 2) if total is not None else None,
"acos": _ratio(spend, sales), "tacos": _ratio(spend, total)}
def _campaign_source(cur):
"""Union campaign reports without reading product reports or materializing source rows."""
parts = []
for ad_type in AD_TYPES:
table = op._ads_table(ad_type, "campaigns")
cols = op._columns(cur, table)
if not cols:
continue
missing = {"date", "campaign_id", "cost", "sales", "orders"} - cols
if missing:
return None, f"{table} lacks {', '.join(sorted(missing))}. Sync Amazon Ads first."
text = lambda name: f'COALESCE(CAST("{name}" AS VARCHAR), \'\')' if name in cols else "''"
parts.append(
f"SELECT '{ad_type}' AS ad_type, {text('_connection_id')} AS connection_id, "
f"{text('profile_id')} AS profile_id, {text('campaign_id')} AS campaign_id, "
f"{text('campaign_name')} AS campaign, TRY_CAST(\"date\" AS DATE) AS day, "
'TRY_CAST(cost AS DOUBLE) AS spend, TRY_CAST(sales AS DOUBLE) AS sales, '
f'TRY_CAST(orders AS DOUBLE) AS orders FROM {table}')
return (" UNION ALL ".join(parts), None) if parts else (None, op.NOT_INSTALLED)
def _empty(start, end, reason, *, no_prior=False, first=None, last=None):
return {"start": start, "end": end, "state": "unavailable", "reason": reason,
"noPrior": no_prior, "firstDataDate": first, "dataDate": last,
"totals": dict.fromkeys(METRICS), "daily": [],
"campaigns": {"items": [], "total": 0, "cursor": None}}
def _page(cur, source, start, end, cursor, limit, sort, descending):
"""Page grouped campaigns in SQL, with a stable identity across types, profiles and connections."""
if isinstance(limit, bool) or not isinstance(limit, int) or limit < 1:
raise ValueError("The campaign page size must be a positive integer.")
if sort not in SORTS:
raise ValueError("Choose a campaign sort: spend, sales, orders, acos or campaign.")
scope = hashlib.sha256(f"{start}|{end}|{sort}|{descending}".encode()).hexdigest()[:16]
offset = 0
if cursor:
decoded = op._decode(cursor)
if len(decoded) != 2 or decoded[0] != scope or not str(decoded[1]).isdigit():
raise ValueError("The campaign cursor does not match this overview.")
offset = int(decoded[1])
grouped = (
f"WITH reports AS ({source}) SELECT ad_type, connection_id, profile_id, campaign_id, "
"arg_max(campaign, day) AS campaign, SUM(spend) AS spend, SUM(sales) AS sales, SUM(orders) AS orders, "
"CASE WHEN SUM(sales) > 0 THEN SUM(spend) / SUM(sales) * 100 END AS acos "
"FROM reports WHERE day BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) GROUP BY 1, 2, 3, 4")
total = cur.execute(f"SELECT COUNT(*) FROM ({grouped})", [start, end]).fetchone()[0]
direction = "DESC" if descending else "ASC"
rows = cur.execute(
f"SELECT * FROM ({grouped}) ORDER BY {sort} {direction} NULLS LAST, "
"ad_type, connection_id, profile_id, campaign_id LIMIT ? OFFSET ?",
[start, end, limit, offset]).fetchall()
fields = ("ad_type", "connection_id", "profile_id", "campaign_id", "campaign", "spend", "sales", "orders", "acos")
items = [{k: round(v, 2) if k in ("spend", "sales", "acos") and v is not None else v
for k, v in zip(fields, row)} for row in rows]
next_offset = offset + len(rows)
return {"items": items, "total": total,
"cursor": op._encode([scope, next_offset]) if next_offset < total else None}
def overview(tenant_key, start, end, *, st=None, cursor=None, limit=50, sort="spend", descending=True):
"""Return daily percentages, sales and spend plus one server-paged row per campaign."""
start, end = _period(start, end)
st = op._rt(tenant_key, st)
with op._reader(tenant_key, st) as cur:
if cur is None:
return _empty(start, end, op.NOT_INSTALLED)
source, reason = _campaign_source(cur)
if source is None:
return _empty(start, end, reason)
first, last = cur.execute(f"SELECT MIN(day), MAX(day) FROM ({source})").fetchone()
if first is None:
return _empty(start, end, "No campaign history has arrived from Amazon Ads.")
first, last = str(first), str(last)
if end < first:
return _empty(start, end, f"No complete Ads history exists before {first}.",
no_prior=True, first=first, last=last)
bad = " OR ".join(f"{k} IS NULL OR NOT isfinite({k})" for k in ("spend", "sales", "orders"))
if cur.execute(f"SELECT COUNT(*) FROM ({source}) WHERE day BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) "
f"AND ({bad})", [start, end]).fetchone()[0]:
return _empty(start, end, "Campaign reports contain missing or invalid metrics.", first=first, last=last)
daily_ads = {}
result = cur.execute(f"SELECT day, SUM(spend), SUM(sales), SUM(orders) FROM ({source}) "
"WHERE day BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) GROUP BY day ORDER BY day",
[start, end])
while batch := result.fetchmany(256):
daily_ads.update({str(row[0]): row[1:] for row in batch})
economics = {}
econ_cols = op._columns(cur, ECONOMICS)
reason = f"Ads history starts on {first}. Earlier figures are unavailable." if start < first else None
if {"start_date", "sales_net_product_sales_amount"} <= econ_cols:
result = cur.execute(
'SELECT TRY_CAST(left(CAST(start_date AS VARCHAR), 10) AS DATE) AS day, '
'SUM(TRY_CAST(sales_net_product_sales_amount AS DOUBLE)), '
'COUNT(*) FILTER (WHERE TRY_CAST(sales_net_product_sales_amount AS DOUBLE) IS NULL '
'OR NOT isfinite(TRY_CAST(sales_net_product_sales_amount AS DOUBLE))) '
f'FROM {ECONOMICS} WHERE left(CAST(start_date AS VARCHAR), 10) BETWEEN ? AND ? '
'GROUP BY day ORDER BY day', [start, end])
while batch := result.fetchmany(256):
economics.update({str(day): value if not invalid else None for day, value, invalid in batch})
else:
reason = "Amazon economics sales are unavailable. Sync Amazon economics first."
daily = []
day = dt.date.fromisoformat(start)
while day.isoformat() <= end:
key = day.isoformat()
if key < first or key > last:
daily.append({"date": key, **dict.fromkeys(METRICS)})
else:
spend, sales, orders = daily_ads.get(key, (0.0, 0.0, 0.0))
daily.append({"date": key, **_figures(spend, sales, orders, economics.get(key))})
if key == end:
break
day += dt.timedelta(days=1)
spend, sales, orders = (sum(row[i] for row in daily_ads.values()) for i in range(3))
total = sum(economics.values()) if economics and all(v is not None for v in economics.values()) else None
if any(row["total_sales"] is None for row in daily):
reason = reason or "Some days have no economics sales or campaign history. Missing figures are unavailable."
return {"start": start, "end": end, "state": "partial" if reason else "ready", "reason": reason,
"noPrior": start < first, "firstDataDate": first, "dataDate": last,
"totals": _figures(spend, sales, orders, total), "daily": daily,
"campaigns": _page(cur, source, start, end, cursor, limit, sort, descending)}
def validate(tenant_key, start, end, *, st=None, report=None):
"""Reconcile each figure against separate campaign and economics aggregations, independent of overview's union."""
start, end = _period(start, end)
st = op._rt(tenant_key, st)
report = overview(tenant_key, start, end, st=st) if report is None else report
independent = {"spend": 0.0, "ad_sales": 0.0, "orders": 0.0, "total_sales": None}
days, errors = {}, []
with op._reader(tenant_key, st) as cur:
if cur is None:
return {"ok": False, "detail": op.NOT_INSTALLED, "independent": independent}
for ad_type in ("sp", "sb", "sd"):
table = op._ads_table(ad_type, "campaigns")
cols = op._columns(cur, table)
if not cols:
continue
if not {"date", "cost", "sales", "orders"} <= cols:
errors.append(f"{table} has missing metrics.")
continue
result = cur.execute(
'SELECT TRY_CAST("date" AS DATE), SUM(TRY_CAST(cost AS DOUBLE)), '
'SUM(TRY_CAST(sales AS DOUBLE)), SUM(TRY_CAST(orders AS DOUBLE)) '
f'FROM {table} WHERE TRY_CAST("date" AS DATE) BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) '
'GROUP BY 1', [start, end])
while batch := result.fetchmany(256):
for day, spend, sales, orders in batch:
values = (spend, sales, orders)
if any(v is None or not math.isfinite(v) for v in values):
errors.append(f"{table} has invalid metrics.")
continue
rec = days.setdefault(str(day), dict(spend=0.0, ad_sales=0.0, orders=0.0, total_sales=None))
for key, value in zip(("spend", "ad_sales", "orders"), values):
rec[key] += value
independent[key] += value
if {"start_date", "sales_net_product_sales_amount"} <= op._columns(cur, ECONOMICS):
result = cur.execute(
'SELECT left(CAST(start_date AS VARCHAR), 10), SUM(TRY_CAST(sales_net_product_sales_amount AS DOUBLE)) '
f'FROM {ECONOMICS} WHERE left(CAST(start_date AS VARCHAR), 10) BETWEEN ? AND ? GROUP BY 1',
[start, end])
while batch := result.fetchmany(256):
for day, sales in batch:
if sales is None or not math.isfinite(sales):
errors.append("Economics has invalid sales.")
continue
days.setdefault(day, dict(spend=0.0, ad_sales=0.0, orders=0.0, total_sales=None))["total_sales"] = sales
independent["total_sales"] = (independent["total_sales"] or 0.0) + sales
else:
errors.append("Economics sales are unavailable.")
def expected_figures(row):
spend, sales, total = row["spend"], row["ad_sales"], row["total_sales"]
return {"spend": round(spend, 2), "ad_sales": round(sales, 2), "orders": row["orders"],
"total_sales": round(total, 2) if total is not None else None,
"organic_sales": round(total - sales, 2) if total is not None else None,
"acos": round(spend * 100 / sales, 2) if sales > 0 else None,
"tacos": round(spend * 100 / total, 2) if total is not None and total > 0 else None}
expected = expected_figures(independent)
def ties(actual, wanted):
return all((actual.get(k) is None if v is None else
isinstance(actual.get(k), (int, float)) and math.isfinite(actual[k]) and abs(actual[k] - v) <= 0.01)
for k, v in wanted.items())
if report.get("start") != start or report.get("end") != end or report.get("state") == "unavailable":
errors.append("The requested overview window is unavailable or mismatched.")
if not ties(report.get("totals", {}), expected):
errors.append("Overview totals do not tie to campaign cost, campaign sales and economics sales.")
observed = {row.get("date"): row for row in report.get("daily", [])}
for day, row in days.items():
wanted = expected_figures(row)
if not ties(observed.get(day, {}), wanted):
errors.append(f"Daily figures do not tie on {day}.")
return {"ok": not errors, "detail": " ".join(errors) or "Campaign cost, ad sales and economics sales tie.",
"app_total": report.get("totals"), "independent": expected}