runloopable / platform /modules /amazon_profit_account.py
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
95fb175 verified
Raw History Blame Contribute Delete
46 kB
"""Read-only Amazon account figures from mirror aggregates and period-matched receipt costs."""
from __future__ import annotations
import calendar
import datetime as dt
import math
from decimal import Decimal, InvalidOperation, ROUND_HALF_UP
from modules import amazon_profit as profit
ECONOMICS = profit.ECONOMICS
ADS_TABLES = tuple(f"amzn_ads_{kind}_advertised_products" for kind in ("sp", "sb", "sd"))
ACCOUNT_FIELDS = frozenset(("sku", "asin", "period", "units_sold", "net_sales", "fees",
"sponsored_ads", "net_proceeds", "cogs_total", "net_profit", "cost_source"))
WHAT_IF_FIELDS = ACCOUNT_FIELDS | {"cogs_per_unit", "margin", "odoo_product", "units_per_sku"}
ORDERED = "sales_ordered_product_sales_amount"
REFUNDS = "sales_refunded_product_sales_amount"
def _cents(value):
if value is None or value == "":
return None
try:
number = Decimal(str(value))
return int((number * 100).quantize(Decimal("1"), rounding=ROUND_HALF_UP)) if number.is_finite() else None
except (InvalidOperation, ValueError, TypeError):
return None
def _metric(cents, sources, *, reason=None, no_prior=False, validated=True):
return {"value": None if cents is None else cents / 100, "validated": validated and cents is not None,
"reason": reason, "noPrior": bool(no_prior), "sourceTables": list(sources)}
def _bounds(start, end, key):
return {"key": key, "start": start.isoformat(), "end": end.isoformat(),
"days": max(0, (end - start).days + 1)}
def _year_before(day):
return day.replace(year=day.year - 1, day=min(day.day, calendar.monthrange(day.year - 1, day.month)[1]))
def _comparisons(bounds):
start, end = (dt.date.fromisoformat(bounds[k]) for k in ("start", "end"))
prior_end = start - dt.timedelta(days=1)
return {"prior": _bounds(start - dt.timedelta(days=bounds["days"]), prior_end, "prior"),
"yearAgo": _bounds(_year_before(start), _year_before(end), "yearAgo")}
def stored_skus(runtime, *, fields=ACCOUNT_FIELDS):
"""Stream only this tenant's SKU table through the store's paged object reader."""
import core.user_tables as ut
backend = runtime._document_store() if callable(getattr(runtime, "_document_store", None)) else runtime
reader = getattr(backend, "iter_json_items", None)
if not callable(reader):
raise profit.SourceUnavailable("Amazon SKU cost lookup requires the paged store reader.")
name = runtime.store_key(ut.STORE_KEY) if callable(getattr(runtime, "store_key", None)) else ut.STORE_KEY
result = {}
for batch in reader(name, (profit.TABLE_KEY, "rows"), batch_size=512):
for _rid, row in batch:
sku = str(row.get("sku") or "").strip()
if not sku:
continue
if sku in result:
raise profit.SourceUnavailable("Amazon SKU cost lookup has duplicate seller SKUs. Refresh the table.")
result[sku] = {key: row.get(key) for key in fields}
return result
def _model_number(value, name, *, fraction=False, whole=False):
try:
number = Decimal(str(value))
except (InvalidOperation, ValueError, TypeError):
raise ValueError(f"{name} must be a finite nonnegative number.") from None
if not number.is_finite() or number < 0 or not math.isfinite(float(number)):
raise ValueError(f"{name} must be a finite nonnegative number.")
if fraction and number > 1:
raise ValueError("TACoS must be a fraction from 0 to 1. For 10%, use 0.10.")
if whole and number != number.to_integral_value():
raise ValueError("Monthly units must be a whole nonnegative number.")
return number
def what_if(sku, price=None, landed_cost=None, tacos=None, units=None, *, con, stored,
settings, period=None, today=None):
"""Model a source-backed SKU with observed signed fees and no stored-state changes."""
today = today or profit._today()
configured = profit.period_bounds(settings.get("period"), today)
recorded = settings.get("lastPeriod")
snapshot_missing = not isinstance(recorded, dict) or not all(recorded.get(key) for key in ("key", "start", "end", "label"))
saved = configured if snapshot_missing else {key: recorded[key] for key in ("key", "start", "end", "label")}
selected = profit.period_bounds(period or configured["key"], today)
supplied = {name: _model_number(value, name, fraction=name == "tacos", whole=name == "units")
for name, value in (("price", price), ("landed_cost", landed_cost), ("tacos", tacos), ("units", units))
if value is not None}
sku = str(sku or "").strip()
out = {"sku": sku, "label": "Model", "available": False,
"baselinePeriod": saved, "selectedPeriod": selected}
def unavailable(reason):
return {**out, "reason": reason}
row = stored.get(sku)
if not row:
return unavailable("This SKU is not in Command Center. Refresh the table first.")
if not profit.links({"baseline": row}).get(sku, {}).get("code"):
return unavailable("Link this SKU to an Odoo product first.")
if snapshot_missing and configured["key"] in profit.PERIOD_DAYS:
return unavailable("Receipt cost snapshot dates unavailable. Refresh Command Center for this period.")
if (saved["start"], saved["end"]) != (selected["start"], selected["end"]):
return unavailable("Receipt cost unavailable for selected window. Refresh Command Center for this period.")
problem = _ready(con, ECONOMICS)
if problem:
return unavailable(problem)
cols = profit._require(con, ECONOMICS, profit._ECONOMICS_REQUIRED)
ads_cols = profit.ads_columns(cols)
if not ads_cols:
return unavailable("Economics ad spend totals unavailable. Resync Amazon economics.")
required = ("sales_net_units_sold", "sales_net_product_sales_amount", "fees_total_amount")
invalid = con.execute(f'SELECT count(*) FROM {ECONOMICS} WHERE msku = ? AND '
'left(CAST(start_date AS VARCHAR),10) BETWEEN ? AND ? AND ('
+ " OR ".join(f'"{c}" IS NULL OR NOT isfinite("{c}")' for c in required)
+ "".join(f' OR ("{c}" IS NOT NULL AND NOT isfinite("{c}"))' for c in ads_cols)
+ " OR (" + " AND ".join(f'"{c}" IS NULL' for c in ads_cols) + ")"
+ ")", [sku, selected["start"], selected["end"]]).fetchone()[0]
if invalid:
return unavailable("Economics amounts unavailable. Resync Amazon economics.")
item = next((item for item in profit.economics_by_sku(con, selected["start"], selected["end"])
if item["sku"] == sku), None)
if item is None:
return unavailable("No economics source rows for this SKU and period.")
currency = profit.economics_currency(con)
if not isinstance(currency, str) or len(currency) != 3 or not currency.isalpha():
return unavailable("Source currency unavailable. Resync Amazon economics.")
cost, reason = _cost(item, row, selected, settings, today)
if reason:
return unavailable(reason)
try:
sold = _model_number(item["units"], "Source units", whole=True)
sales = _model_number(item["net_sales"], "Net sales")
spend = _model_number(item["ads"], "Ad spend")
displayed_cost = _model_number(row.get("cogs_per_unit"), "Stored unit cost")
total_cost = _model_number(row.get("cogs_total"), "Stored total cost")
fees = Decimal(str(item["fees"]))
stored_margin = Decimal(str(row.get("margin")))
if not sold or not sales or not fees.is_finite() or not stored_margin.is_finite() or spend > sales:
return unavailable("Current unit economics cannot be represented. Positive net sales and units with TACoS at most 100% are required.")
unit_cost = total_cost / sold
if _cents(unit_cost) != _cents(displayed_cost) or cost != _cents(total_cost):
return unavailable("Stored unit cost does not match receipt COGS. Refresh Command Center.")
actual = profit.compute_row(item, float(unit_cost), 1)
if (_cents(actual["net_profit"]) != _cents(row.get("net_profit"))
or _cents(Decimal(str(actual["margin"])) * 100) != _cents(stored_margin)):
return unavailable("Current margin does not reconcile. Refresh Command Center.")
except (ValueError, InvalidOperation, TypeError, OverflowError):
return unavailable("Current unit economics unavailable. Refresh Command Center.")
baseline = {"price": sales / sold, "amazon_fees_per_unit": fees / sold,
"landed_cost": unit_cost, "tacos": spend / sales}
modeled_price = supplied.get("price", baseline["price"])
modeled_cost = supplied.get("landed_cost", baseline["landed_cost"])
modeled_tacos = supplied.get("tacos", baseline["tacos"])
monthly_units = supplied.get("units")
ads = modeled_price * modeled_tacos
fee_per_unit = baseline["amazon_fees_per_unit"]
unit_profit = modeled_price - fee_per_unit - ads - modeled_cost
result = {"price": modeled_price, "amazon_fees_per_unit": fee_per_unit, "ad_cost_per_unit": ads,
"landed_cost": modeled_cost, "tacos": modeled_tacos, "profit_per_unit": unit_profit,
"margin": unit_profit / modeled_price if modeled_price else None,
"monthly_profit": unit_profit * monthly_units if monthly_units is not None else None,
"units": monthly_units}
if any(value is not None and not math.isfinite(float(value)) for value in result.values()):
raise ValueError("Model inputs produce amounts outside the supported range.")
return {**out, "available": True, "reason": None, "currency": currency,
"basis": "Net product sales after source refunds and promotions. Observed Amazon fees stay fixed per unit.",
"tacosScale": "Fraction from 0 to 1. For 10%, use 0.10.",
"monthlyUnitsBasis": "Supplied assumption, separate from units sold in the baseline period.",
"baseline": {**{key: float(value) for key, value in baseline.items()},
"units_sold": int(sold), "net_sales": float(sales), "net_profit": actual["net_profit"],
"margin": actual["margin"], "stored_margin_percent": float(stored_margin)},
**{key: None if value is None else float(value) for key, value in result.items()}}
def _ready(con, table):
"""Physical tables and completed sync markers are separate from a window's row count."""
from harness import amazon_store
if not profit._columns(con, table):
return f"Missing source: {table}"
if not {"entity", "phase", "cursor_wd", "updated_at", "rows"} <= profit._columns(con, "_sync_state"):
return f"Source readiness unavailable: {table}"
state = amazon_store._read_state(con, table)
if not state or state["state"] not in ("done", "live"):
return f"Source still loading: {table}"
return None
def _cost(item, row, bounds, settings, today):
"""Use T31's FIFO snapshot only for its exact window and unchanged economics inputs."""
reason = "Receipt cost unavailable for selected window. Refresh Command Center for this period."
if not row or settings.get("refusal"):
return None, reason
saved = profit.period_bounds(settings.get("period"), today)
if (saved["start"], saved["end"]) != (bounds["start"], bounds["end"]) or row.get("period") != saved["label"]:
return None, reason
expected = {"net_sales": item["net_sales"], "fees": item["fees"], "sponsored_ads": item["ads"],
"net_proceeds": item["net_sales"] - item["fees"] - item["ads"]}
if any(_cents(value) is None for value in expected.values()):
return None, "Economics amounts unavailable"
if any(_cents(row.get(key)) != _cents(value) for key, value in expected.items()):
return None, "Receipt cost snapshot no longer matches the mirror. Refresh Command Center."
try:
units_match = Decimal(str(row.get("units_sold"))) == Decimal(str(item["units"]))
except (InvalidOperation, ValueError, TypeError):
units_match = False
cost = _cents(row.get("cogs_total"))
source = str(row.get("cost_source") or "")
# R43: a typed Amazon COGS stands only where the refresh found no Odoo receipt.
valid_source = source in ("No units sold", profit.TYPED_COST_SOURCE) or all(
part in ("Landed cost", "Purchase price (no landed costs)") for part in source.split(", "))
if not units_match or cost is None or not valid_source:
return None, source or reason
if _cents(row.get("net_profit")) != _cents(expected["net_proceeds"]) - cost:
return None, "Receipt cost snapshot profit does not reconcile. Refresh Command Center."
return cost, None
def typed_cost_line(count):
"""How many SKUs in a window use a typed Amazon COGS, in plain words; None when none do."""
if not count:
return None
return f"{count} SKU uses a typed cost." if count == 1 else f"{count} SKUs use a typed cost."
def _ads_coverage(con, bounds):
"""Read existing per-connection coverage without stamping connection or continuation state."""
from harness import amazon_ads_store as ads
from harness import amazon_store as store
sources = ["_sync_state", *ADS_TABLES]
cols = profit._columns(con, "_sync_state")
if not {"entity", "phase", "cursor_wd"} <= cols:
return "Connect Amazon Ads", sources
markers = {key: (phase, detail) for key, phase, detail in con.execute(
"SELECT entity, phase, cursor_wd FROM _sync_state WHERE starts_with(entity, ?)",
[store.entity_key("ads_")]).fetchall()}
# Continuation keys start with sync_mark_/sync_floor_, outside the ads_ prefix.
for key, phase, detail in con.execute(
"SELECT entity, phase, cursor_wd FROM _sync_state WHERE starts_with(entity, ?) "
"OR starts_with(entity, ?)", [store.entity_key(store.WATERMARK_PREFIX + "ads_"),
store.entity_key(store.BACKFILL_FLOOR_PREFIX + "ads_")]).fetchall():
markers[key] = phase, detail
prefix = ads.CONNECT_DAY_KEY + "::connection::"
connections = {key[len(prefix):]: detail for key, (phase, detail) in markers.items()
if key.startswith(prefix) and phase == "done"}
if not connections and markers.get(ads.CONNECT_DAY_KEY, (None, None))[0] == "done":
connections = {None: markers[ads.CONNECT_DAY_KEY][1]}
if not connections:
have = any(profit._columns(con, table) for table in ADS_TABLES)
return ("Amazon Ads connect day unavailable" if have else "Connect Amazon Ads"), sources
for table in ADS_TABLES:
have = profit._columns(con, table)
if not {"date", "sales"} <= have:
return f"Missing source: {table}.sales", sources
if "_connection_id" in have:
ids = {row[0] or None for row in con.execute(
f'SELECT DISTINCT _connection_id FROM "{table}"').fetchall()}
if not ids <= connections.keys():
return "Amazon Ads connection coverage unavailable", sources
elif any(cid is not None for cid in connections):
return "Amazon Ads connection attribution unavailable", sources
for cid, day in connections.items():
try:
connected = dt.date.fromisoformat(str(day)[:10]).isoformat()
except ValueError:
return "Amazon Ads connect day unavailable", sources
if bounds["start"] < connected:
return "Amazon Ads history starts at the connect day", sources
mark = table[len(store.PREFIX):]
phase = markers.get(ads._state_key(store.entity_key(mark), cid), (None, None))[0]
floor = markers.get(ads._state_key(store.entity_key(store.BACKFILL_FLOOR_PREFIX + mark), cid), (None, None))[1]
last = markers.get(ads._state_key(store.entity_key(store.WATERMARK_PREFIX + mark), cid), (None, None))[1]
try:
floor = dt.date.fromisoformat(str(floor)[:10]).isoformat()
last = dt.date.fromisoformat(str(last)[:10]).isoformat()
except ValueError:
return f"Amazon Ads coverage unavailable: {table}", sources
if phase != "done" or floor > bounds["start"] or last < bounds["end"]:
return f"Amazon Ads coverage incomplete: {table}", sources
return None, sources
def _attribution(con, bounds, daily):
reason, sources = _ads_coverage(con, bounds)
ads_by_day = {}
if reason is None:
union = " UNION ALL ".join(f'SELECT left(CAST(date AS VARCHAR),10) AS day, sales FROM "{t}"'
for t in ADS_TABLES)
rows = con.execute(f"SELECT day, SUM(sales), count(*), count(sales) FROM ({union}) "
"WHERE day BETWEEN ? AND ? GROUP BY day ORDER BY day",
[bounds["start"], bounds["end"]]).fetchall()
if not rows:
reason = "No Amazon Ads source rows for window"
elif any(n != present or _cents(sales) is None for _day, sales, n, present in rows):
reason = "Amazon Ads sales amounts unavailable"
else:
ads_by_day = {day: _cents(sales) for day, sales, _n, _present in rows}
days = set(daily) | set(ads_by_day)
series = []
for day in sorted(days):
sales = daily.get(day)
ad_sales = ads_by_day.get(day, 0) if reason is None else None
missing = reason or ("No economics source rows for day" if sales is None else None)
series.append({"day": day, "netSales": _metric(sales, [ECONOMICS], no_prior=sales is None,
reason="No economics source rows for day" if sales is None else None),
"adSales": _metric(ad_sales, sources, reason=reason, no_prior=reason is not None),
"organicSales": _metric(None if missing else sales - ad_sales, [ECONOMICS, *sources],
reason=missing, no_prior=missing is not None)})
total = sum(ads_by_day.values()) if reason is None else None
product_sales = sum(daily.values()) if daily else None
organic_reason = reason or ("No economics source rows for window" if product_sales is None else None)
out = {"daily": series, "adSales": _metric(total, sources, reason=reason, no_prior=reason is not None),
"organicSales": _metric(None if total is None or product_sales is None else product_sales - total,
[ECONOMICS, *sources], reason=organic_reason, no_prior=organic_reason is not None),
"reason": reason, "sourceTables": [ECONOMICS, *sources]}
out["validate"] = validate_attribution(con, bounds, out)
if not out["validate"]["ok"]:
for key in ("adSales", "organicSales"):
out[key]["validated"] = False
out[key]["reason"] = out[key]["reason"] or out["validate"]["reason"]
return out
def validate_attribution(con, bounds, view):
"""Reconcile chart totals to independent scalar SQL and enforce the connection coverage wall."""
why, sources = _ads_coverage(con, bounds)
why = why or view["reason"]
checks = []
if why:
return {"ok": False, "state": "unavailable", "reason": why, "sourceTables": sources, "checks": []}
params = [bounds["start"], bounds["end"]]
union = " UNION ALL ".join(f'SELECT date, sales FROM "{t}"' for t in ADS_TABLES)
ad_total = _cents(con.execute(f"SELECT SUM(sales) FROM ({union}) WHERE "
"left(CAST(date AS VARCHAR),10) BETWEEN ? AND ?", params).fetchone()[0])
net_total = _cents(con.execute(f"SELECT SUM(sales_net_product_sales_amount) FROM {ECONOMICS} WHERE "
"left(CAST(start_date AS VARCHAR),10) BETWEEN ? AND ?", params).fetchone()[0])
def check(name, observed, expected):
checks.append({"check": name, "ok": observed is not None and expected is not None and observed == expected,
"a": None if observed is None else observed / 100,
"b": None if expected is None else expected / 100})
ad_values = [_cents(day["adSales"]["value"]) for day in view["daily"]]
organic_values = [_cents(day["organicSales"]["value"]) for day in view["daily"]]
check("Daily ad sales equal advertised-product source sales", sum(ad_values) if None not in ad_values else None, ad_total)
check("Attributed total equals advertised-product source sales", _cents(view["adSales"]["value"]), ad_total)
expected_organic = None if ad_total is None or net_total is None else net_total - ad_total
check("Organic total equals source net sales minus attributed sales", _cents(view["organicSales"]["value"]), expected_organic)
check("Daily organic sales equal source net sales minus attributed sales",
sum(organic_values) if None not in organic_values else None, expected_organic)
ok = all(c["ok"] for c in checks)
return {"ok": ok, "state": "tied" if ok else "failed", "reason": None if ok else "Attribution does not reconcile",
"checks": checks, "sourceTables": [ECONOMICS, *sources]}
def _window(con, bounds, stored, settings, today):
sources = [ECONOMICS]
cols = profit._require(con, ECONOMICS, profit._ECONOMICS_REQUIRED)
ads_cols = profit.ads_columns(cols)
optional = [column for column in (ORDERED, REFUNDS) if column in cols]
sums = ", ".join(f'SUM("{c}"), count("{c}")' for c in optional)
where = "left(CAST(start_date AS VARCHAR),10) BETWEEN ? AND ?"
count, *breakdown = con.execute(f"SELECT count(*){', ' + sums if sums else ''} FROM {ECONOMICS} WHERE {where}",
[bounds["start"], bounds["end"]]).fetchone()
no_prior = count == 0
reason = "No economics source rows for window" if no_prior else None
amounts = {column: _cents(breakdown[i * 2]) if breakdown[i * 2 + 1] == count and count else None
for i, column in enumerate(optional)}
invalid = con.execute(
f"SELECT count(*) FROM {ECONOMICS} WHERE {where} AND ("
+ " OR ".join(f'"{c}" IS NULL OR NOT isfinite("{c}")' for c in
("sales_net_product_sales_amount", "fees_total_amount", "sales_net_units_sold"))
+ "".join(f' OR ("{c}" IS NOT NULL AND NOT isfinite("{c}"))' for c in ads_cols)
+ " OR NULLIF(trim(msku),'') IS NULL)", [bounds["start"], bounds["end"]]).fetchone()[0]
if invalid:
raise profit.SourceUnavailable("Economics amounts or SKU unavailable. Resync Amazon economics.")
reason = reason or ("Missing source: economics ad spend totals" if not ads_cols else None)
items = profit.economics_by_sku(con, bounds["start"], bounds["end"]) if not no_prior else []
activity = {sku for (sku,) in con.execute(
f"SELECT msku FROM {ECONOMICS} WHERE {where} GROUP BY msku",
[bounds["start"], bounds["end"]]).fetchall()} if count else set()
skus, costs, typed = [], [], 0
for item in items:
amounts_sku = [_cents(item[key]) for key in ("net_sales", "fees", "ads")]
proceeds = amounts_sku[0] - amounts_sku[1] - amounts_sku[2] if None not in amounts_sku else None
cost, cost_reason = _cost(item, stored.get(item["sku"]), bounds, settings, today)
# A historical SKU with no selected-window activity contributes zero cost.
if item["sku"] not in activity:
cost, cost_reason = 0, None
elif cost is not None and str((stored.get(item["sku"]) or {}).get("cost_source") or "") == profit.TYPED_COST_SOURCE:
typed += 1
costs.append(cost)
skus.append({"sku": item["sku"], "asin": item.get("asin"), "units": item["units"],
"netSales": _metric(_cents(item["net_sales"]), sources, reason=reason, validated=reason is None),
"netProceeds": _metric(proceeds if reason is None else None, sources, reason=reason),
"cogs": _metric(cost, [profit.TABLE_KEY], reason=cost_reason),
"netProfit": _metric(None if cost is None or reason or proceeds is None else proceeds - cost,
[ECONOMICS, profit.TABLE_KEY], reason=reason or cost_reason)})
total = lambda field: sum(_cents(item[field]) for item in items) if reason is None else None
net_sales, fees, spend = total("net_sales"), total("fees"), total("ads")
proceeds = None if net_sales is None else net_sales - fees - spend
ordered, refunds = amounts.get(ORDERED), amounts.get(REFUNDS)
gross_bridge = net_sales is not None and ordered is not None and refunds is not None and ordered - refunds == net_sales
cogs = sum(costs) if costs and all(c is not None for c in costs) else None
cost_reason = next((s["cogs"]["reason"] for s in skus if s["cogs"]["value"] is None), reason)
if cogs is None and not cost_reason:
cost_reason = "Receipt cost unavailable for selected window"
typed_line = typed_cost_line(typed)
# The typed count rides on the COGS figure only: net profit keeps its own reason and checks.
cogs_reason = cost_reason if cost_reason or cogs is None else typed_line
components = []
def component(key, label, cents, tables, *, included=True, why=None):
components.append({"key": key, "label": label, "included": included,
**_metric(cents, tables, reason=why, no_prior=no_prior)})
component("sales", "Ordered product sales" if gross_bridge else "Net product sales", ordered if gross_bridge else net_sales,
sources, why=reason)
component("adSpend", "Ad spend", None if spend is None else -spend, sources, why=reason)
component("amazonFees", "Amazon fees", None if fees is None else -fees, sources, why=reason)
component("promotions", "Customer promotions", None, sources, included=False,
why="Missing source: customer promotion breakdown. Fee-charge promotions are fee rebates.")
component("refunds", "Refunds" if gross_bridge else "Refunds already included in net sales",
None if refunds is None else -refunds, sources, included=gross_bridge,
why=reason or ("Missing source: " + REFUNDS if refunds is None else None))
component("cogs", "COGS", None if cogs is None else -cogs, [profit.TABLE_KEY], why=cogs_reason)
net = None if cogs is None or proceeds is None else proceeds - cogs
metrics = {"netSales": _metric(net_sales, sources, reason=reason, no_prior=no_prior),
"netProceeds": _metric(proceeds, sources, reason=reason, no_prior=no_prior),
"cogs": _metric(cogs, [profit.TABLE_KEY], reason=cogs_reason, no_prior=no_prior),
"netProfit": _metric(net, [ECONOMICS, profit.TABLE_KEY], reason=reason or cost_reason, no_prior=no_prior)}
ranked = sorted(skus, key=lambda s: (-(s["netSales"]["value"] or 0), s["sku"]))
mix = [{"sku": s["sku"], "sales": s["netSales"]} for s in ranked[:10]]
if len(ranked) > 10:
remainder = [_cents(s["netSales"]["value"]) for s in ranked[10:]]
mix.append({"sku": "Other", "isOther": True,
"sales": _metric(sum(remainder) if None not in remainder else None, sources,
reason=reason, validated=reason is None)})
days = con.execute(f"SELECT left(CAST(start_date AS VARCHAR),10), SUM(sales_net_product_sales_amount) "
f"FROM {ECONOMICS} WHERE {where} GROUP BY 1 ORDER BY 1",
[bounds["start"], bounds["end"]]).fetchall()
daily = {day: _cents(sales) for day, sales in days if _cents(sales) is not None}
out = {"period": bounds, "noPrior": no_prior, "sourceRows": count, "reason": reason,
"sourceTables": [ECONOMICS, profit.SETTLEMENTS, profit.TABLE_KEY], "metrics": metrics,
"waterfall": {"components": components, "netProfit": metrics["netProfit"],
"salesBasis": "orderedLessRefunds" if gross_bridge else "netProductSales",
"reason": None if gross_bridge else "Refunds and promotions remain embedded in net product sales."},
"skus": skus, "salesMix": mix, "attribution": _attribution(con, bounds, daily),
"typedCost": {"skus": typed, "line": typed_line}}
out["validate"] = validate(con, out)
out["waterfall"]["validate"] = {"ok": all(c["ok"] for c in out["validate"]["checks"] if "Waterfall" in c["check"]),
"checks": [c for c in out["validate"]["checks"] if "Waterfall" in c["check"]]}
out["salesMixValidate"] = next(c for c in out["validate"]["checks"] if c["check"].startswith("Top ten"))
out["validated"] = out["validate"]["ok"]
if not out["validated"]:
metrics["netProfit"]["validated"] = False
metrics["netProfit"]["reason"] = metrics["netProfit"]["reason"] or "Account P&L does not reconcile"
return out
def validate(con, view):
"""Independently reconcile the waterfall, SKU sums and settlement source aggregates."""
bounds, metrics = view["period"], view["metrics"]
start, end = bounds["start"], bounds["end"]
checks = []
def check(name, a, b, *, why=None, tolerance=0):
ok = a is not None and b is not None and abs(a - b) <= tolerance and not why
checks.append({"check": name, "ok": ok, "a": a, "b": b, "reason": why,
"sourceTables": view["sourceTables"]})
observed = profit.net_proceeds_total(con, start, end) if view["sourceRows"] else None
amount = metrics["netProceeds"]["value"]
check("SKU net proceeds equal independent economics total", amount, observed,
tolerance=0.01, why=view["reason"])
before = [c for c in view["waterfall"]["components"] if c["included"] and c["key"] != "cogs"]
cents = [_cents(c["value"]) for c in before]
bridge = sum(cents) / 100 if cents and all(c is not None for c in cents) else None
check("Waterfall before COGS equals SKU net proceeds", bridge, amount, tolerance=0.001,
why=view["reason"])
why = _ready(con, profit.SETTLEMENTS)
settled, span, proceeds = None, None, bridge
if why is None:
try:
currency = profit.economics_currency(con)
span = profit.settlement_coverage(con, start, end, currency)
# The tie reads only the days issued settlements cover, on both sides.
lo, hi = span["start"] or start, span["end"] or end
settled = profit.settlement_components(con, lo, hi, currency)
why = settled["reason"]
if span["start"] is None:
settled = dict(settled, rows=0)
why = f"Not verified: settlement components in {dt.date.fromisoformat(start):%B %Y}: no settled days."
elif (lo, hi) != (start, end) and bridge is not None:
proceeds = profit.net_proceeds_total(con, lo, hi)
except profit.SourceUnavailable as exc:
why = str(exc)
target = settled["total"] if settled and settled["rows"] else None
if target is not None and not math.isfinite(target):
target, why = None, "Settlement amounts unavailable"
tied, gap = profit._tie(proceeds, target) if proceeds is not None and target is not None else (False, None)
gap = gap if gap is None or math.isfinite(gap) else None
covered = {k: span[k] for k in ("start", "end", "label", "partial", "clear", "sentence")} if span else None
checks.append({"check": "Net proceeds tie to settlements within 2%",
"ok": tied and not why,
"a": proceeds, "b": target, "gap": gap, "tolerance": profit.SETTLEMENT_TOLERANCE,
"reason": why or ("No settlement source rows for window" if target is None else None),
"components": [{**part, "gap": part["gap"] if math.isfinite(part["gap"]) else None}
for part in settled["parts"]] if settled else [],
"excluded": settled.get("excluded", {}) if settled else {},
"covered": covered, "note": covered and covered["sentence"],
"sourceTables": [ECONOMICS, profit.SETTLEMENTS, "amzn_orders", *ADS_TABLES]})
values = [_cents(s["netProfit"]["value"]) for s in view["skus"]]
sku_net = sum(values) / 100 if values and all(c is not None for c in values) else None
check("Account net profit equals sum of SKU profit after COGS", metrics["netProfit"]["value"], sku_net,
why=metrics["netProfit"]["reason"], tolerance=0.001)
sku_before = [_cents(s["netProceeds"]["value"]) for s in view["skus"]]
sku_costs = [_cents(s["cogs"]["value"]) for s in view["skus"]]
after_costs = ((sum(sku_before) - sum(sku_costs)) / 100
if sku_before and None not in sku_before + sku_costs else None)
check("Account net profit equals SKU net proceeds minus SKU COGS", metrics["netProfit"]["value"], after_costs,
why=metrics["netProfit"]["reason"], tolerance=0.001)
full = [_cents(c["value"]) for c in view["waterfall"]["components"] if c["included"]]
check("Waterfall components sum to account net profit", sum(full) / 100 if full and None not in full else None,
metrics["netProfit"]["value"], why=metrics["netProfit"]["reason"], tolerance=0.001)
mix = [_cents(s["sales"]["value"]) for s in view["salesMix"]]
check("Top ten plus Other preserves net product sales", sum(mix) / 100 if None not in mix else None,
metrics["netSales"]["value"], why=view["reason"], tolerance=0.001)
return {"ok": all(c["ok"] for c in checks), "state": "tied" if all(c["ok"] for c in checks) else
"unavailable" if view["reason"] or metrics["netProfit"]["value"] is None or target is None else "failed",
"checks": checks, "sourceTables": view["sourceTables"]}
def validate_pace(con, bounds, pace, elapsed, month_days, reason):
"""Check the forecast arithmetic against independent source sums without certifying an outcome."""
if reason or not elapsed:
return {"ok": False, "state": "unavailable", "reason": reason or "No complete days in current month",
"sourceTables": [ECONOMICS, "_sync_state"], "checks": []}
cols = profit._columns(con, ECONOMICS)
ad_expr = " + ".join(f'COALESCE("{c}",0)' for c in profit.ads_columns(cols)) or "0"
sales, proceeds = con.execute(
f"SELECT SUM(sales_net_product_sales_amount), SUM(sales_net_product_sales_amount - fees_total_amount - ({ad_expr})) "
f"FROM {ECONOMICS} WHERE left(CAST(start_date AS VARCHAR),10) BETWEEN ? AND ?",
[bounds["start"], bounds["end"]]).fetchone()
checks = []
for key, source in (("netSales", sales), ("netProceeds", proceeds)):
expected = None if source is None else _cents(Decimal(str(source)) * month_days / elapsed)
observed = _cents(pace[key]["value"])
checks.append({"check": f"{key} pace equals source daily rate times calendar days",
"ok": expected is not None and observed is not None and abs(expected - observed) <= 1,
"a": pace[key]["value"], "b": None if expected is None else expected / 100,
"reason": pace[key]["reason"]})
return {"ok": all(c["ok"] for c in checks), "state": "estimate", "reason": None,
"checks": checks, "sourceTables": [ECONOMICS, "_sync_state"]}
class _SourceRows:
def __init__(self, rows):
self.rows = rows
def fetchone(self):
return self.rows[0] if self.rows else None
def fetchall(self):
return list(self.rows)
def _settlement_source(sql):
"""Parse distinct settlement labels and referenced order dates without losing source lines or null keys."""
expressions = {"posted": ("posted_date", f"CAST({profit._moment_sql('s.posted_date')} AS DATE)")}
for name, field in (("description", "amount_description"), ("kind", "amount_type"),
("transaction", "transaction_type")):
expressions[name] = field, f"lower(regexp_replace(COALESCE({field}, ''), '[^a-zA-Z0-9]', '', 'g'))"
fields = [field for field, _expression in expressions.values()]
projection = ", ".join(expression.replace("s.posted_date", "posted_date") + " AS " + name
for name, (_field, expression) in expressions.items())
keys = ", ".join(f"{field} AS source_{field}" for field in fields)
labels = (f"labels AS MATERIALIZED (SELECT {keys}, {projection} "
f"FROM (SELECT DISTINCT {', '.join(fields)} FROM {profit.SETTLEMENTS})), ")
for name, (_field, expression) in expressions.items():
sql = sql.replace(expression + " AS " + name, "label." + name + " AS " + name)
join = " AND ".join(f"s.{field} IS NOT DISTINCT FROM label.source_{field}" for field in fields)
sql = sql.replace(f"FROM {profit.SETTLEMENTS} s JOIN streams",
f"FROM {profit.SETTLEMENTS} s JOIN labels label ON {join} JOIN streams")
sql = sql.replace("FROM amzn_orders GROUP BY amazon_order_id",
f"FROM amzn_orders WHERE amazon_order_id IN (SELECT trim(order_id) "
f"FROM {profit.SETTLEMENTS}) GROUP BY amazon_order_id")
return sql.replace("WITH orders AS (", "WITH " + labels + "orders AS (", 1)
class _ValidationSources:
"""Share settlement source preparation across this request's independent window sums."""
def __init__(self, con, bounds):
self.con, self.results, self.spans = con, {}, {}
self.bounds = [span for bound in bounds for span in (bound, *_comparisons(bound).values())]
def _spans(self, currency=None):
if currency in self.spans:
return self.spans[currency]
spans = {(bound["start"], bound["end"]) for bound in self.bounds}
if currency is not None:
for start, end in tuple(spans):
covered = profit.settlement_coverage(self, start, end, currency)
spans.add((covered["start"] or start, covered["end"] or end))
self.spans[currency] = sorted(spans)
return self.spans[currency]
def _components(self, sql, params):
marker = "SELECT component, COALESCE(SUM(currency_amount), 0)"
prefix, select = sql.split(marker, 1)
aggregates, select = (marker + select).split("FROM dated WHERE", 1)
aggregates = aggregates.removeprefix("SELECT component, ")
# The grouping keys retain every input to the original window predicate and its bad-row counts.
prefix += (" , prepared(component, basis, posted, dates, bad_dates, amount, source_rows, "
"bad_amounts, unmatched_orders) AS (SELECT component, basis, posted, dates, bad_dates, "
+ aggregates + " FROM dated GROUP BY component, basis, posted, dates, bad_dates) ")
spans = self._spans(params[0])
for bound in ("lo", "hi", "lo", "hi"):
select = select.replace("CAST(? AS DATE)", "span." + bound, 1)
ranges = ", ".join(f"({index}, CAST(? AS DATE), CAST(? AS DATE))" for index in range(len(spans)))
select = f"FROM prepared JOIN (VALUES {ranges}) AS span(id, lo, hi) ON" + select
select = select.replace("GROUP BY component ORDER BY component",
"GROUP BY span.id, component ORDER BY span.id, component")
bindings = list(params[:2]) + [value for span in spans for value in span]
rows = self.con.execute(_settlement_source(prefix) + "SELECT span.id, component, "
"COALESCE(SUM(amount), 0), SUM(source_rows), SUM(bad_amounts), SUM(unmatched_orders) "
+ select, bindings).fetchall()
for index, span in enumerate(spans):
self.results[(sql, tuple(params[:2]) + span * 2)] = [row[1:] for row in rows if row[0] == index]
def _unscoped(self, sql):
marker = "AND (CAST("
source, _date_filter = sql.split(marker, 1)
source = source.replace("SELECT count(*)", f"SELECT CAST({profit._moment_sql('posted_date')} AS DATE) AS posted", 1)
spans = self._spans(profit.economics_currency(self.con))
sums = ["count(*) FILTER (WHERE posted BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) OR posted IS NULL)"
for _span in spans]
counts = self.con.execute("SELECT " + ", ".join(sums) + " FROM (" + source + ")",
[value for span in spans for value in span]).fetchone()
for index, count in enumerate(counts):
self.results[(sql, spans[index])] = [(count,)]
def execute(self, sql, params=()):
params = tuple(params or ())
key = sql, params
if key in self.results:
return _SourceRows(self.results[key])
if sql == "SELECT column_name FROM information_schema.columns WHERE table_name = ?":
self.results[key] = self.con.execute(sql, params).fetchall()
return _SourceRows(self.results[key])
components = (sql.startswith("WITH orders AS (") and len(params) == 6
and sql.endswith(" GROUP BY component ORDER BY component"))
unscoped = (sql.startswith(f"SELECT count(*) FROM {profit.SETTLEMENTS} s\n")
and "WHERE h.settlement_id IS NULL" in sql and len(params) == 2)
if components:
self._components(sql, params)
elif unscoped:
self._unscoped(sql)
if key in self.results:
return _SourceRows(self.results[key])
return self.con.execute(sql, params)
def _account_bounds(chosen, today):
bounds = [chosen, _bounds(today.replace(day=1), today - dt.timedelta(days=1), "mtd")]
current = today.replace(day=1)
for _ in range(2):
last = current - dt.timedelta(days=1)
current = last.replace(day=1)
bounds.append(_bounds(current, last, current.strftime("%Y-%m")))
return bounds
def account_view(con, period=None, *, stored=None, settings=None, today=None):
"""Return selected, comparison and monthly account windows without refreshing stored inputs."""
today = today or profit._today()
selected = profit.period_bounds(period or profit.last_complete_month(today), today)
end = min(dt.date.fromisoformat(selected["end"]), today - dt.timedelta(days=1))
chosen = _bounds(dt.date.fromisoformat(selected["start"]), end, selected["key"])
if dt.date.fromisoformat(chosen["start"]).year < 2:
raise ValueError("Choose a period with an available year-ago calendar window.")
if not profit._mirror_has_economics(con):
return {"empty": True, "reason": "Sync Amazon economics first", "period": chosen,
"sourceTables": [ECONOMICS], "validated": False}
problem = _ready(con, ECONOMICS)
if problem:
raise profit.SourceUnavailable(problem)
profit._require(con, ECONOMICS, profit._ECONOMICS_REQUIRED)
if con.execute(f"SELECT count(*) FROM {ECONOMICS} WHERE "
"TRY_CAST(left(CAST(start_date AS VARCHAR),10) AS DATE) IS NULL").fetchone()[0]:
raise profit.SourceUnavailable("Economics dates unavailable. Resync Amazon economics.")
stored, settings = stored or {}, settings or {}
periods = _account_bounds(chosen, today)
con = _ValidationSources(con, periods)
cache = {}
def window(bounds):
key = bounds["start"], bounds["end"]
if key not in cache:
cache[key] = _window(con, bounds, stored, settings, today)
return {**cache[key], "period": bounds}
def compared(bounds):
return {**window(bounds), "comparisons": {key: window(span) for key, span in _comparisons(bounds).items()}}
mtd = compared(periods[1])
months = [compared(bounds) for bounds in periods[2:]]
pace = {}
elapsed = mtd["period"]["days"]
month_days = calendar.monthrange(today.year, today.month)[1]
from harness import amazon_store
coverage = [amazon_store._read_state(con, amazon_store.entity_key(prefix + "economics"))
for prefix in (amazon_store.BACKFILL_FLOOR_PREFIX, amazon_store.WATERMARK_PREFIX)]
floor, last = [str((state or {}).get("detail") or "")[:10] for state in coverage]
pace_reason = None if all((state or {}).get("state") == "done" for state in coverage) and \
floor and last and floor <= mtd["period"]["start"] and last >= mtd["period"]["end"] else \
"Economics coverage unavailable for pace forecast"
for key, metric in mtd["metrics"].items():
value = _cents(metric["value"])
forecast = None if value is None or not elapsed or pace_reason else int(
(Decimal(value) * month_days / elapsed).quantize(Decimal("1"), rounding=ROUND_HALF_UP))
pace[key] = {**_metric(forecast, [*metric["sourceTables"], "_sync_state"], reason=metric["reason"] or pace_reason or
("No complete days in current month" if not elapsed else None),
no_prior=metric["noPrior"] or bool(pace_reason)), "projected": True}
return {**compared(chosen), "empty": False, "currency": profit.economics_currency(con),
"monthToDate": mtd, "previousMonths": months,
"paceForecast": {"metrics": pace, "elapsedDays": elapsed, "monthDays": month_days,
"sourceTables": [ECONOMICS, profit.TABLE_KEY, "_sync_state"],
"reason": pace_reason,
"validate": validate_pace(con, mtd["period"], pace, elapsed, month_days, pace_reason),
"basis": "Complete days through yesterday at a constant daily pace. This is an estimate."}}