Download platform/modules/amazon_operator_overview.py from fsanyoto/loopable: direct link, hf CLI and curl.
- Browser
- Download file 13.4 kB
-
https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/platform/modules/amazon_operator_overview.py
- Command line
-
hf download hf://spaces/fsanyoto/loopable/platform/modules/amazon_operator_overview.py
-
curl -L -o amazon_operator_overview.py https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/platform/modules/amazon_operator_overview.py
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} | |