File size: 13,362 Bytes
9e15897 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 | """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}
|