Download platform/modules/amazon_sku_metrics.py from fsanyoto/loopable: direct link, hf CLI and curl.
- Browser
- Download file 37.8 kB
-
https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/platform/modules/amazon_sku_metrics.py
- Command line
-
hf download hf://spaces/fsanyoto/loopable/platform/modules/amazon_sku_metrics.py
-
curl -L -o amazon_sku_metrics.py https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/platform/modules/amazon_sku_metrics.py
37.8 kB
| """Per Amazon seller SKU ads, stock, returns and listing metrics, four health labels, a score and a priority. | |
| Every ratio is a fraction (0.15 means 15%), the scale `amazon_profit.compute_row` stores `margin` in. | |
| `rollup` builds the ASIN and Parent ASIN grains from SKU rows. It sums first, then recomputes every | |
| ratio from the sums. Ad spend and its trend come from the economics ad charges when economics carries | |
| them, else from the Ads tables. `validate_metrics` ties ad spend, stock and returns to independent | |
| mirror totals (rule 8). No source read carries a LIMIT (rule 1). | |
| """ | |
| from __future__ import annotations | |
| import datetime as dt | |
| import json | |
| import math | |
| # Data-layer readers of the Amazon mirror, shared on purpose so the stock and listing SQL is not forked. | |
| from modules import ranking_diagnostic, restock_planner | |
| #: Cut-offs and weights (owner ruling). Ratios are fractions: 0.005 is 0.5%. | |
| BENCHMARKS = { | |
| "ctr_strong": 0.005, # CTR at least 0.5% is Strong | |
| "ctr_weak": 0.003, # CTR under 0.3% is Weak | |
| "acos_strong": 0.25, # ACOS at most 25% is Strong | |
| "acos_weak": 0.40, # ACOS over 40% is Weak | |
| "tacos_strong": 0.10, # TACOS at most 10% is Strong | |
| "tacos_weak": 0.20, # TACOS over 20% is Weak | |
| "cvr_strong": 0.10, # CVR at least 10% is Strong | |
| "cvr_weak": 0.05, # CVR under 5% is Weak | |
| "margin_strong": 0.15, # margin at least 15% is Strong | |
| "margin_weak": 0.05, # margin under 5% is Weak | |
| "cover_strong_min": 30, # days of cover 30 to 90 is Strong | |
| "cover_strong_max": 90, | |
| "cover_weak_under": 14, # days of cover under 14 is Weak | |
| "cover_weak_over": 180, # days of cover over 180 is Weak | |
| "return_rate_strong": 0.03, # return rate at most 3% is Strong | |
| "return_rate_weak": 0.08, # return rate over 8% is Weak | |
| "listing_cvr_strong": 0.10, # unit session percentage at least 10%: mirrors the CVR ruling | |
| "listing_cvr_weak": 0.05, # unit session percentage under 5%: mirrors the CVR ruling | |
| "points_strong": 25, # each of the four dimensions is worth 25 points | |
| "points_fair": 12.5, # Fair scores half points | |
| "points_weak": 0, | |
| "points_no_data": 12.5, # No data scores neutral, half points, never zero | |
| "priority_high_under": 40, # score under 40 is High priority | |
| "priority_low_over": 70, # score over 70 is Low priority; 40 to 70 inclusive is Medium | |
| } | |
| GRAINS = ("sku", "asin", "parent_asin") | |
| #: The words a person reads and picks for each grain key. Stored rows may hold either form. | |
| GRAIN_LABELS = {"sku": "SKU", "asin": "ASIN", "parent_asin": "Parent ASIN"} | |
| _GRAIN_BY_WORD = {**{key: key for key in GRAINS}, | |
| **{label.lower(): key for key, label in GRAIN_LABELS.items()}} | |
| def grain_key(value): | |
| """The grain key for a key or its label, case-insensitive; "" for anything else.""" | |
| word = " ".join(str(value or "").split()).lower() | |
| return _GRAIN_BY_WORD.get(word, "") | |
| HEALTH_OPTIONS = ("Strong", "Fair", "Weak", "No data") | |
| PRIORITY_OPTIONS = ("High", "Medium", "Low") | |
| NO_ADS_NOTE = "Connect Amazon Ads on the Connectors page" | |
| NO_STOCK_NOTE = "No inventory planning data yet" | |
| NO_RETURNS_NOTE = "No returns data yet" | |
| #: Reason texts for a blank figure. The Grid cannot say why a cell is blank, so the reason is data. | |
| NOT_ADVERTISED_NOTE = "Not in the Ads reports for the period" | |
| NO_AD_SPEND_NOTE = "No ad spend" | |
| NO_NET_SALES_NOTE = "No net sales in the period" | |
| NOT_PLANNED_NOTE = "Not in the FBA planning report" | |
| NO_RECENT_SALES_NOTE = "No sales in the last 30 days" | |
| NO_UNITS_NOTE = "No units sold in the period" | |
| NO_TRAFFIC_NOTE = "Not in the traffic report" | |
| NO_SESSIONS_NOTE = "No sessions in the traffic report" | |
| NO_BUY_BOX_NOTE = "No Buy Box share in the traffic report" | |
| SERIES_DAYS = 30 | |
| #: Ad spend per advertised product may trail the campaign report by a few cents per day. | |
| ADS_TIE_TOLERANCE = 0.02 | |
| #: Economics ad spend ties to its own independent sum within one cent. | |
| SPEND_TIE_TOLERANCE = 0.01 | |
| COUNT_TIE_TOLERANCE = 0.01 | |
| ECONOMICS = "amzn_economics" | |
| PLANNING = "amzn_inventory_planning" | |
| FBA_RETURNS = "amzn_fba_customer_returns" | |
| MFN_RETURNS = "amzn_mfn_returns" | |
| AD_TYPES = ("sp", "sb", "sd") | |
| ADS_PRODUCTS = tuple(f"amzn_ads_{kind}_advertised_products" for kind in AD_TYPES) | |
| ADS_CAMPAIGNS = tuple(f"amzn_ads_{kind}_campaigns" for kind in AD_TYPES) | |
| _HEALTH = {"options": list(HEALTH_OPTIONS), "format": {"display": "dot"}} | |
| #: (key, label, type, description, extra) in display order. H1b wraps each with `amazon_profit._field`. | |
| METRIC_COLUMNS = ( | |
| ("parent_asin", "Parent ASIN", "text", "Variation family. An ASIN with no parent is its own.", {}), | |
| ("ad_impressions", "Ad impressions", "int", "Sponsored ad impressions in the period.", {}), | |
| ("ad_clicks", "Ad clicks", "int", "Sponsored ad clicks in the period.", {}), | |
| ("ad_spend", "Ad spend", "currency", "Sponsored ad cost in the period.", {}), | |
| ("ad_sales", "Ad sales", "currency", "Sales Amazon credits to the ads.", {}), | |
| ("ad_orders", "Ad orders", "int", "Orders Amazon credits to the ads.", {}), | |
| ("ctr", "CTR", "pct", "Ad clicks over ad impressions.", {}), | |
| ("cvr", "CVR", "pct", "Ad orders over ad clicks.", {}), | |
| ("cpc", "CPC", "currency", "Ad spend over ad clicks.", {}), | |
| ("acos", "ACOS", "pct", "Ad spend over ad sales.", {}), | |
| ("tacos", "TACOS", "pct", "Sponsored ad charges over net sales.", {}), | |
| ("tacos_note", "TACOS note", "text", "Why TACOS is blank.", {}), | |
| ("roas", "ROAS", "int", "Ad sales over ad spend.", {"format": {"decimals": 2}}), | |
| ("ads_note", "Ads note", "text", "Why ads figures are blank.", {}), | |
| ("fba_on_hand", "FBA on hand", "int", "Units available at Amazon in the newest snapshot.", {}), | |
| ("inbound_units", "Inbound units", "int", "Units on the way to Amazon.", {}), | |
| ("days_of_cover", "Days of cover", "int", "Days the FBA stock lasts at the 30 day sales pace.", {}), | |
| ("stock_note", "Stock note", "text", "Why stock figures are blank.", {}), | |
| ("return_units", "Returned units", "int", "Customer returns in the period.", {}), | |
| ("return_rate", "Return rate", "pct", "Returned units over units sold.", {}), | |
| ("returns_note", "Returns note", "text", "Why return figures are blank.", {}), | |
| ("sessions", "Sessions", "int", | |
| "Product page sessions of the ASIN from the newest traffic report. Not aligned to the period.", {}), | |
| ("unit_session_pct", "Unit session percentage", "pct", "Units sold over sessions.", {}), | |
| ("buy_box_pct", "Buy Box", "pct", "Share of sessions that showed your offer.", {}), | |
| ("listing_note", "Listing note", "text", "Why traffic figures are blank.", {}), | |
| ("stock_health", "Stock health", "select", "Days of cover against the benchmark.", _HEALTH), | |
| ("profit_health", "Profit health", "select", "Margin against the benchmark.", _HEALTH), | |
| ("ads_health", "Ads health", "select", "CTR, ACOS, TACOS and CVR against the benchmarks.", _HEALTH), | |
| ("listing_health", "Listing health", "select", "Unit session percentage and return rate.", _HEALTH), | |
| ("score", "Score", "int", "Health score from 0 to 100.", {"format": {"display": "bar"}}), | |
| ("priority", "Priority", "select", "High needs attention first.", | |
| {"options": list(PRIORITY_OPTIONS), "format": {"display": "dot"}}), | |
| ("units_30d", "Units trend", "chart", "Units sold per day, last 30 days.", {}), | |
| ("net_sales_30d", "Net sales trend", "chart", "Net sales per day, last 30 days.", {}), | |
| ("ad_spend_30d", "Ad spend trend", "chart", "Ad spend per day, last 30 days.", {}), | |
| ) | |
| ROLLUP_COLUMNS = ( | |
| ("sku_count", "SKUs", "int", "Seller SKUs in this row.", {}), | |
| ("skus", "SKU list", "text", "Seller SKUs in this row, comma separated.", {}), | |
| ) | |
| ADDITIVE_KEYS = ("units_sold", "net_sales", "fees", "sponsored_ads", "net_proceeds", "cogs_total", | |
| "net_profit", "ad_impressions", "ad_clicks", "ad_spend", "ad_sales", "ad_orders", | |
| "fba_on_hand", "inbound_units", "return_units", "velocity_30d") | |
| SERIES_KEYS = ("units_30d", "net_sales_30d", "ad_spend_30d") | |
| _INT_KEYS = frozenset(("units_sold", "ad_impressions", "ad_clicks", "ad_orders", "fba_on_hand", | |
| "inbound_units", "return_units")) | |
| #: A sum over a partly unlinked group is not a profit: one costless active child blanks it. | |
| _STRICT_KEYS = frozenset(("cogs_total", "net_profit")) | |
| _SCORED = ("tacos", "return_rate", "stock_health", "profit_health", "ads_health", "listing_health", | |
| "score", "priority") | |
| #: Each reason column and the figures it explains. A blank figure needs its note set. | |
| ADS_FIGURES = ("ad_impressions", "ad_clicks", "ad_spend", "ad_sales", "ad_orders", "ctr", "cvr", "cpc", "acos", | |
| "roas") | |
| NOTE_GROUPS = { | |
| "ads_note": ADS_FIGURES, | |
| "tacos_note": ("tacos",), | |
| "stock_note": ("fba_on_hand", "inbound_units", "days_of_cover"), | |
| "returns_note": ("return_units", "return_rate"), | |
| "listing_note": ("sessions", "unit_session_pct", "buy_box_pct"), | |
| } | |
| NOTE_KEYS = tuple(NOTE_GROUPS) | |
| #: What the last `rollup` call grouped and skipped. Updated in place. | |
| last_report: dict = {} | |
| # ---------------------------------------------------------------- pure arithmetic | |
| def _f(value): | |
| try: | |
| number = float(value) | |
| except (TypeError, ValueError): | |
| return None | |
| return number if math.isfinite(number) else None | |
| def _ratio(num, den): | |
| """num / den, or None when either is missing or the denominator is not positive.""" | |
| num, den = _f(num), _f(den) | |
| if num is None or den is None or den <= 0: | |
| return None | |
| return num / den | |
| def _half_up(value): | |
| return int(math.floor(value + 0.5)) | |
| def _text(value): | |
| return str(value).strip() if value is not None and str(value).strip() else None | |
| def band(metric, value): | |
| """Strong, Fair or Weak for one ratio against BENCHMARKS; None when the value is None.""" | |
| value = _f(value) | |
| if value is None: | |
| return None | |
| if metric == "days_of_cover": | |
| return cover_band(value) | |
| name = "listing_cvr" if metric == "unit_session_pct" else metric | |
| strong, weak = BENCHMARKS[f"{name}_strong"], BENCHMARKS[f"{name}_weak"] | |
| value = round(value, 9) | |
| if name in ("acos", "tacos", "return_rate"): | |
| return "Strong" if value <= strong else "Weak" if value > weak else "Fair" | |
| return "Strong" if value >= strong else "Weak" if value < weak else "Fair" | |
| def cover_band(days): | |
| """Strong for 30 to 90 days, Weak under 14 or over 180, else Fair; None when unknown.""" | |
| days = _f(days) | |
| if days is None: | |
| return None | |
| if BENCHMARKS["cover_strong_min"] <= days <= BENCHMARKS["cover_strong_max"]: | |
| return "Strong" | |
| if days < BENCHMARKS["cover_weak_under"] or days > BENCHMARKS["cover_weak_over"]: | |
| return "Weak" | |
| return "Fair" | |
| def combine(labels): | |
| """Weak if any is Weak, Strong if all available are Strong, else Fair; No data when none is available.""" | |
| found = [label for label in labels if label] | |
| if not found: | |
| return "No data" | |
| if "Weak" in found: | |
| return "Weak" | |
| return "Strong" if all(label == "Strong" for label in found) else "Fair" | |
| def points(label): | |
| return BENCHMARKS[{"Strong": "points_strong", "Fair": "points_fair", "Weak": "points_weak"} | |
| .get(label, "points_no_data")] | |
| def priority_for(score): | |
| if score < BENCHMARKS["priority_high_under"]: | |
| return "High" | |
| return "Low" if score > BENCHMARKS["priority_low_over"] else "Medium" | |
| def score_row(row: dict) -> dict: | |
| """Health labels, score and priority for one row that already carries the economics and metric keys.""" | |
| net_sales = _f(row.get("net_sales")) | |
| spend = _f(row.get("ad_spend")) | |
| if spend is None: | |
| spend = _f(row.get("sponsored_ads")) | |
| # R5: a SKU with no ad spend keeps TACOS blank. | |
| tacos = _ratio(spend, net_sales) if spend and spend > 0 else None | |
| return_rate = _ratio(row.get("return_units"), row.get("units_sold")) | |
| stock = combine([cover_band(row.get("days_of_cover"))]) | |
| profit = combine([band("margin", row.get("margin"))]) if net_sales else "No data" | |
| ads = combine([band("ctr", row.get("ctr")), band("acos", row.get("acos")), | |
| band("tacos", tacos), band("cvr", row.get("cvr"))]) | |
| listing = combine([band("unit_session_pct", row.get("unit_session_pct")), | |
| band("return_rate", return_rate)]) | |
| score = _half_up(sum(points(label) for label in (stock, profit, ads, listing))) | |
| return {"tacos": tacos, "return_rate": return_rate, "stock_health": stock, "profit_health": profit, | |
| "ads_health": ads, "listing_health": listing, "score": score, "priority": priority_for(score)} | |
| def _ads_reason(row): | |
| if row.get("ads_note") == NO_ADS_NOTE: | |
| return NO_ADS_NOTE | |
| if row.get("ad_impressions") is None: | |
| return NOT_ADVERTISED_NOTE | |
| names = [word for keys, word in ((("ctr",), "ad impressions"), (("cvr", "cpc"), "ad clicks"), | |
| (("acos",), "ad sales"), (("roas", "ad_spend"), "ad spend")) | |
| if any(row.get(k) is None for k in keys)] | |
| return f"No {' or '.join(names)} in the period" if names else NOT_ADVERTISED_NOTE | |
| def _tacos_reason(row): | |
| spend = _f(row.get("ad_spend")) | |
| if spend is None: | |
| spend = _f(row.get("sponsored_ads")) | |
| return NO_AD_SPEND_NOTE if not spend or spend <= 0 else NO_NET_SALES_NOTE | |
| def _stock_reason(row): | |
| if row.get("fba_on_hand") is None or row.get("inbound_units") is None: | |
| return NOT_PLANNED_NOTE | |
| return NO_RECENT_SALES_NOTE | |
| def _returns_reason(row): | |
| return NO_RETURNS_NOTE if row.get("return_units") is None else NO_UNITS_NOTE | |
| def _listing_reason(row): | |
| sessions = _f(row.get("sessions")) | |
| if sessions is None: | |
| return NO_TRAFFIC_NOTE | |
| if row.get("unit_session_pct") is None: | |
| return NO_SESSIONS_NOTE if sessions <= 0 else NO_UNITS_NOTE | |
| return NO_BUY_BOX_NOTE | |
| _REASONS = {"ads_note": _ads_reason, "tacos_note": _tacos_reason, "stock_note": _stock_reason, | |
| "returns_note": _returns_reason, "listing_note": _listing_reason} | |
| def blank_notes(row: dict) -> dict: | |
| """Each note in NOTE_GROUPS: "" when its figures are all set, else why one is blank (rule 1). | |
| `row` carries raw figures after `score_row`. An `ads_note` of NO_ADS_NOTE means no Ads table is mirrored.""" | |
| return {note: _REASONS[note](row) if any(row.get(k) is None for k in figures) else "" | |
| for note, figures in NOTE_GROUPS.items()} | |
| # ---------------------------------------------------------------- mirror reads | |
| def _columns(con, table): | |
| return {r[0] for r in con.execute( | |
| "SELECT column_name FROM information_schema.columns WHERE table_name = ?", [table]).fetchall()} | |
| def _q(name): | |
| return '"' + str(name).replace('"', '""') + '"' | |
| def _day(column): | |
| """A DATE from an ISO text or timestamp prefix, or from MM/DD/YYYY.""" | |
| text = f"CAST({_q(column)} AS VARCHAR)" | |
| return (f"COALESCE(TRY_CAST(substr({text}, 1, 10) AS DATE), " | |
| f"CAST(try_strptime({text}, '%m/%d/%Y') AS DATE))") | |
| def _pick(cols, *choices): | |
| return next((c for c in choices if c in cols), None) | |
| def _num_sql(cols, name): | |
| return f"TRY_CAST({_q(name)} AS DOUBLE)" if name in cols else "NULL" | |
| def _sku_sql(column): | |
| return f"TRIM(CAST({_q(column)} AS VARCHAR))" | |
| def _all_skus(con): | |
| if "msku" not in _columns(con, ECONOMICS): | |
| return [] | |
| return [r[0] for r in con.execute( | |
| f"SELECT DISTINCT {_sku_sql('msku')} FROM {ECONOMICS} WHERE NULLIF(TRIM(CAST(msku AS VARCHAR)), '') " | |
| "IS NOT NULL ORDER BY 1").fetchall()] | |
| def _economics(con, start, end, skus): | |
| """{sku: {asin, units}}: newest child ASIN and net units sold in [start, end].""" | |
| cols = _columns(con, ECONOMICS) | |
| if not {"msku", "start_date"} <= cols: | |
| return {} | |
| day = _day("start_date") | |
| asin = (f"arg_max(NULLIF(TRIM(CAST(child_asin AS VARCHAR)), ''), {day})" | |
| if "child_asin" in cols else "NULL") | |
| units = (f"SUM(TRY_CAST(sales_net_units_sold AS DOUBLE)) FILTER (WHERE {day} BETWEEN " | |
| "CAST(? AS DATE) AND CAST(? AS DATE))" if "sales_net_units_sold" in cols else "NULL") | |
| args = [start, end] if "sales_net_units_sold" in cols else [] | |
| rows = con.execute(f"SELECT {_sku_sql('msku')}, {asin}, {units} FROM {ECONOMICS} " | |
| f"WHERE {_sku_sql('msku')} IN (SELECT unnest(?)) GROUP BY 1", | |
| [*args, skus]).fetchall() | |
| return {r[0]: {"asin": r[1], "units": r[2]} for r in rows} | |
| def _econ_ads_columns(con): | |
| """The economics ad charge totals, `_charge_` detail skipped; empty when economics has none.""" | |
| from modules import amazon_profit # amazon_profit imports this module at load, so import late | |
| cols = _columns(con, ECONOMICS) | |
| if not {"msku", "start_date"} <= cols: | |
| return [] | |
| return amazon_profit.ads_columns(cols) | |
| def _econ_ads_sql(columns): | |
| return " + ".join(f"COALESCE(TRY_CAST({_q(c)} AS DOUBLE), 0)" for c in columns) | |
| def _econ_spend(con, start, end, skus): | |
| """{sku: economics ad charges} in [start, end]; None when economics carries no ad amounts.""" | |
| columns = _econ_ads_columns(con) | |
| if not columns: | |
| return None | |
| rows = con.execute(f"SELECT {_sku_sql('msku')}, SUM({_econ_ads_sql(columns)}) FROM {ECONOMICS} " | |
| f"WHERE {_day('start_date')} BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) " | |
| f"AND {_sku_sql('msku')} IN (SELECT unnest(?)) GROUP BY 1", [start, end, skus]).fetchall() | |
| return {r[0]: r[1] for r in rows} | |
| def _ads_parts(con): | |
| """SELECT sku, day, impressions, clicks, cost, sales, orders over every present ads product table.""" | |
| parts = [] | |
| for table in ADS_PRODUCTS: | |
| cols = _columns(con, table) | |
| if not {"advertised_sku", "date"} <= cols: | |
| continue | |
| metrics = ", ".join(f"{_num_sql(cols, c)} AS {c}" | |
| for c in ("impressions", "clicks", "cost", "sales", "orders")) | |
| parts.append(f"SELECT {_sku_sql('advertised_sku')} AS sku, {_day('date')} AS day, {metrics} " | |
| f"FROM {table}") | |
| return " UNION ALL ".join(parts) or None | |
| def _ads(con, start, end, skus): | |
| """{sku: summed ad figures} in [start, end]; None when no ads table is mirrored.""" | |
| union = _ads_parts(con) | |
| if union is None: | |
| return None | |
| rows = con.execute( | |
| f"SELECT sku, SUM(impressions), SUM(clicks), SUM(cost), SUM(sales), SUM(orders), COUNT(*) " | |
| f"FROM ({union}) a WHERE day BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) " | |
| "AND sku IN (SELECT unnest(?)) GROUP BY 1", [start, end, skus]).fetchall() | |
| return {r[0]: {"impressions": r[1], "clicks": r[2], "cost": r[3], "sales": r[4], "orders": r[5]} | |
| for r in rows if r[6]} | |
| def _returns_parts(con): | |
| """SELECT sku, day, units over the FBA and MFN customer returns tables that are present.""" | |
| parts = [] | |
| for table, skus, days, qty in ((FBA_RETURNS, ("sku",), ("return_date",), ("quantity",)), | |
| (MFN_RETURNS, ("merchant_sku", "sku"), | |
| ("return_request_date", "return_date"), ("return_quantity", "quantity"))): | |
| cols = _columns(con, table) | |
| sku, day, units = _pick(cols, *skus), _pick(cols, *days), _pick(cols, *qty) | |
| if sku is None or day is None: | |
| continue | |
| # A row without a quantity is one returned unit. | |
| amount = f"COALESCE(TRY_CAST({_q(units)} AS DOUBLE), 1)" if units else "1" | |
| parts.append(f"SELECT {_sku_sql(sku)} AS sku, {_day(day)} AS day, {amount} AS units FROM {table}") | |
| return " UNION ALL ".join(parts) or None | |
| def _returns(con, start, end, skus): | |
| """{sku: returned units} in [start, end]; None when no returns table is mirrored.""" | |
| union = _returns_parts(con) | |
| if union is None: | |
| return None | |
| rows = con.execute(f"SELECT sku, SUM(units) FROM ({union}) r WHERE day BETWEEN CAST(? AS DATE) " | |
| "AND CAST(? AS DATE) AND sku IN (SELECT unnest(?)) GROUP BY 1", | |
| [start, end, skus]).fetchall() | |
| return {r[0]: r[1] for r in rows} | |
| def _daily(con, first, last, skus): | |
| """{sku: {day: [units, net_sales, ad_spend]}} for the series window.""" | |
| out = {} | |
| def add(rows, index): | |
| for sku, day, value in rows: | |
| if day is not None and value is not None: | |
| out.setdefault(sku, {}).setdefault(day.isoformat(), [0.0, 0.0, 0.0])[index] += value | |
| cols = _columns(con, ECONOMICS) | |
| spend = _econ_ads_columns(con) | |
| if {"msku", "start_date"} <= cols: | |
| sums = [(index, f"TRY_CAST({column} AS DOUBLE)") for index, column in | |
| ((0, "sales_net_units_sold"), (1, "sales_net_product_sales_amount")) if column in cols] | |
| if spend: | |
| sums.append((2, _econ_ads_sql(spend))) | |
| for index, value in sums: | |
| add(con.execute( | |
| f"SELECT {_sku_sql('msku')}, {_day('start_date')} AS d, SUM({value}) " | |
| f"FROM {ECONOMICS} WHERE {_day('start_date')} BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) " | |
| f"AND {_sku_sql('msku')} IN (SELECT unnest(?)) GROUP BY 1, 2", | |
| [first, last, skus]).fetchall(), index) | |
| union = None if spend else _ads_parts(con) | |
| if union is not None: | |
| add(con.execute(f"SELECT sku, day, SUM(cost) FROM ({union}) a WHERE day BETWEEN CAST(? AS DATE) " | |
| "AND CAST(? AS DATE) AND sku IN (SELECT unnest(?)) GROUP BY 1, 2", | |
| [first, last, skus]).fetchall(), 2) | |
| return out | |
| def _lineage_read(con, catalog, column, keys, projection): | |
| """Read only requested lineage keys in bounded SQL pages.""" | |
| keys = sorted({str(k).strip() for k in keys if k}) | |
| if catalog is None: | |
| return | |
| for start in range(0, len(keys), 300): | |
| part = keys[start:start + 300] | |
| source = "c" if column == "sku" else "p" | |
| sql = catalog + f"SELECT {projection} FROM {source} WHERE {column} IN (" + ",".join("?" for _ in part) + ")" | |
| offset = 0 | |
| while True: | |
| rows = con.execute(sql + f" ORDER BY 1 LIMIT 500 OFFSET {offset}", part).fetchall() | |
| yield from rows | |
| if len(rows) < 500: | |
| break | |
| offset += len(rows) | |
| def _parent_map(con, asins): | |
| """Use the project popup's pricing lineage for the requested ASINs.""" | |
| from core import amazon_projects as AP | |
| return dict(_lineage_read(con, AP._catalog(con), "child", asins, "child, parent")) | |
| def parent_children(con, parent): | |
| """Read the same pricing family used by persisted competitor cells.""" | |
| from core import amazon_projects as AP | |
| return [child for child, _ in _lineage_read(con, AP._catalog(con), "parent", [parent], "child, parent") | |
| if child and child != parent] | |
| def lineage_rows(con, tables, *, children=False): | |
| """Project stored row identities onto the popup's current listings and pricing lineage.""" | |
| from core import amazon_projects as AP | |
| catalog = AP._catalog(con) | |
| out = {g: {rid: dict(row) for rid, row in rows.items()} for g, rows in tables.items()} | |
| listed = {sku: (asin, parent) for sku, asin, parent in _lineage_read( | |
| con, catalog, "sku", [r.get("sku") for r in out.get("sku", {}).values()], "sku, asin, parent")} | |
| for row in out.get("sku", {}).values(): | |
| row["asin"], row["parent_asin"] = listed.get(str(row.get("sku") or "").strip(), (None, None)) | |
| asins = [str(r.get("asin") or "").strip().upper() for r in out.get("asin", {}).values()] | |
| parents = dict(_lineage_read(con, catalog, "child", asins, "child, parent")) | |
| for row in out.get("asin", {}).values(): | |
| row["asin"] = str(row.get("asin") or "").strip().upper() or None | |
| row["parent_asin"] = parents.get(row["asin"]) | |
| if children: | |
| wanted = [str(r.get("parent_asin") or "").strip().upper() | |
| for r in out.get("parent_asin", {}).values()] | |
| families = {parent: [] for parent in wanted} | |
| for child, parent in _lineage_read(con, catalog, "parent", wanted, "child, parent"): | |
| if child and child != parent: | |
| families[parent].append(child) | |
| # Rival popups use the parent itself when pricing names no children. | |
| out["asin"] = {rid: dict(row, parent_asin=None) for rid, row in out.get("asin", {}).items()} | |
| for parent, kids in families.items(): | |
| for child in kids or [parent]: | |
| out["asin"][("lineage", parent, child)] = {"asin": child, "parent_asin": parent} | |
| return out | |
| def _out(value): | |
| value = round(float(value), 2) | |
| return int(value) if value == int(value) else value | |
| def series_cell(values): | |
| """The stored chart value: a JSON list of numbers, oldest first.""" | |
| return json.dumps([_out(v) for v in values]) | |
| def _series(value): | |
| if isinstance(value, str): | |
| try: | |
| value = json.loads(value) | |
| except ValueError: | |
| return None | |
| if not isinstance(value, (list, tuple)): | |
| return None | |
| return [_f(v) or 0.0 for v in value] | |
| def _int(value): | |
| value = _f(value) | |
| return None if value is None else _half_up(value) | |
| def sku_metrics(con, *, start, end, skus, today) -> dict[str, dict]: | |
| """Per SKU metric cells for the period [start, end] (ISO dates), plus internal keys. | |
| `skus` None reads every SKU in economics. Series cover the 30 days before `today`. | |
| """ | |
| wanted = [s for s in dict.fromkeys(_text(s) for s in (_all_skus(con) if skus is None else skus)) if s] | |
| if not wanted: | |
| return {} | |
| if isinstance(today, str): | |
| today = dt.date.fromisoformat(today[:10]) | |
| days = [(today - dt.timedelta(days=SERIES_DAYS - i)).isoformat() for i in range(SERIES_DAYS)] | |
| econ = _economics(con, start, end, wanted) | |
| planning = restock_planner._planning(con, wanted) or {} | |
| pricing = ranking_diagnostic._pricing(con) or {} | |
| parents = _parent_map(con, [r.get("asin") for r in econ.values()] | |
| + [r.get("asin") for r in planning.values()]) | |
| ads = _ads(con, start, end, wanted) | |
| econ_spend = _econ_spend(con, start, end, wanted) | |
| returns = _returns(con, start, end, wanted) | |
| daily = _daily(con, days[0], days[-1], wanted) | |
| out = {} | |
| for sku in wanted: | |
| e, p = econ.get(sku, {}), planning.get(sku) | |
| asin = _text(e.get("asin")) or _text((p or {}).get("asin")) | |
| row = {"asin": asin, "parent_asin": (parents.get(asin) or asin) if asin else None} | |
| a = (ads or {}).get(sku) | |
| impressions, clicks = (_f(a["impressions"]), _f(a["clicks"])) if a else (None, None) | |
| spend, sales, orders = (_f(a["cost"]), _f(a["sales"]), _f(a["orders"])) if a else (None, None, None) | |
| if econ_spend is not None: | |
| spend = _f(econ_spend.get(sku)) or 0.0 | |
| row.update(ad_impressions=_int(impressions), ad_clicks=_int(clicks), ad_spend=spend, ad_sales=sales, | |
| ad_orders=_int(orders), ctr=_ratio(clicks, impressions), cvr=_ratio(orders, clicks), | |
| cpc=_ratio(spend, clicks), acos=_ratio(spend, sales), roas=_ratio(sales, spend), | |
| ads_note="" if a else NO_ADS_NOTE if ads is None else NOT_ADVERTISED_NOTE) | |
| available = _f(p.get("available")) if p else None | |
| velocity = _f(p.get("units")) if p else None | |
| cover = _f(p.get("days")) if p else None | |
| if cover is None and available is not None: | |
| cover = _ratio(available * 30, velocity) | |
| row["fnsku"] = _text(p.get("fnsku")) if p else None | |
| row.update(fba_on_hand=_int(available), inbound_units=_int(p.get("inbound")) if p else None, | |
| velocity_30d=velocity, days_of_cover=_int(cover)) | |
| row["return_units"] = None if returns is None else _int(returns.get(sku) or 0) | |
| traffic = pricing.get(asin) if asin else None | |
| sessions = _int(traffic.get("sessions")) if traffic else None | |
| box = _f(traffic.get("buyBox")) if traffic else None | |
| row.update(sessions=sessions, buy_box_pct=None if box is None else box / 100.0, | |
| unit_session_pct=_ratio(e.get("units"), sessions)) | |
| per_day = daily.get(sku, {}) | |
| for index, key in enumerate(SERIES_KEYS): | |
| row[key] = series_cell(per_day.get(day, (0.0, 0.0, 0.0))[index] for day in days) | |
| out[sku] = row | |
| return out | |
| # ---------------------------------------------------------------- grain rollups | |
| def _sum(values): | |
| found = [v for v in (_f(x) for x in values) if v is not None] | |
| return sum(found) if found else None | |
| def _active(row): | |
| return bool(_f(row.get("net_sales")) or _f(row.get("units_sold"))) | |
| def rollup(rows: list[dict], *, key: str) -> list[dict]: | |
| """One row per distinct rows[i][key] (asin or parent_asin): sums, recomputed ratios, then score_row keys.""" | |
| if key not in ("asin", "parent_asin"): | |
| raise ValueError("Roll up by asin or parent_asin.") | |
| groups, skipped = {}, [] | |
| for row in rows: | |
| value = _text(row.get(key)) | |
| if value is None: | |
| skipped.append(row.get("sku")) | |
| continue | |
| groups.setdefault(value, []).append(row) | |
| last_report.clear() | |
| last_report.update(key=key, groups=len(groups), skipped=len(skipped), | |
| skipped_skus=sorted(str(s) for s in skipped if s is not None)) | |
| out = [] | |
| for value in sorted(groups): | |
| children = sorted(groups[value], key=lambda r: str(r.get("sku") or "")) | |
| skus = [str(c.get("sku")) for c in children if c.get("sku") is not None] | |
| row = {key: value, "title": next((c.get("title") for c in children if c.get("title")), None), | |
| "sku_count": len(children), "skus": ", ".join(skus)} | |
| row["parent_asin"] = value if key == "parent_asin" else next( | |
| (_text(c.get("parent_asin")) for c in children if _text(c.get("parent_asin"))), value) | |
| for k in ADDITIVE_KEYS: | |
| if k in _STRICT_KEYS and any(c.get(k) is None and _active(c) for c in children): | |
| row[k] = None | |
| continue | |
| total = _sum(c.get(k) for c in children) | |
| row[k] = _half_up(total) if total is not None and k in _INT_KEYS else total | |
| for k in SERIES_KEYS: | |
| total = [0.0] * SERIES_DAYS | |
| for c in children: | |
| for i, v in enumerate((_series(c.get(k)) or [])[:SERIES_DAYS]): | |
| total[i] += v | |
| row[k] = series_cell(total) | |
| row.update(ctr=_ratio(row["ad_clicks"], row["ad_impressions"]), | |
| cvr=_ratio(row["ad_orders"], row["ad_clicks"]), cpc=_ratio(row["ad_spend"], row["ad_clicks"]), | |
| acos=_ratio(row["ad_spend"], row["ad_sales"]), roas=_ratio(row["ad_sales"], row["ad_spend"]), | |
| margin=_ratio(row["net_profit"], row["net_sales"]) if row["net_profit"] is not None else None, | |
| ads_note=NO_ADS_NOTE if all(c.get("ads_note") == NO_ADS_NOTE for c in children) else "") | |
| # Two seller SKUs on one FNSKU report the same inventory pool, so stock counts once per FNSKU. | |
| pools = {} | |
| for c in children: | |
| pool = _text(c.get("fnsku")) or _text(c.get("sku")) | |
| if pool and pool not in pools: | |
| pools[pool] = (_f(c.get("fba_on_hand")), _f(c.get("inbound_units"))) | |
| on_hand = _sum(h for h, _i in pools.values()) | |
| inbound = _sum(i for _h, i in pools.values()) | |
| row["fba_on_hand"] = _int(on_hand) if on_hand is not None else None | |
| row["inbound_units"] = _int(inbound) if inbound is not None else None | |
| velocity = row["velocity_30d"] | |
| cover = _ratio(row["fba_on_hand"], velocity / 30) if row["fba_on_hand"] is not None and velocity else None | |
| row["days_of_cover"] = _int(cover) | |
| # A SKU's sessions are its ASIN's, so they are summed once per distinct ASIN. | |
| traffic = {} | |
| for c in children: | |
| asin = _text(c.get("asin")) | |
| if asin and asin not in traffic: | |
| traffic[asin] = (_f(c.get("sessions")), _f(c.get("buy_box_pct"))) | |
| sessions = _sum(s for s, _b in traffic.values()) | |
| boxes = [(b, s or 0.0) for s, b in traffic.values() if b is not None] | |
| weight = sum(w for _b, w in boxes) | |
| row["sessions"] = _int(sessions) | |
| row["buy_box_pct"] = (sum(b * w for b, w in boxes) / weight if weight | |
| else (sum(b for b, _w in boxes) / len(boxes) if boxes else None)) | |
| row["unit_session_pct"] = _ratio(row["units_sold"], row["sessions"]) | |
| if key == "asin": | |
| row["asin"] = value | |
| row.update(score_row(row)) | |
| row.update(blank_notes(row)) | |
| out.append(row) | |
| return out | |
| # ---------------------------------------------------------------- validate | |
| def _check(name, ok, a, b, gap, note=""): | |
| return {"check": name, "ok": bool(ok), "a": None if a is None else round(a, 2), | |
| "b": None if b is None else round(b, 2), "gap": None if gap is None else round(gap, 4), | |
| "note": note} | |
| def validate_metrics(con, *, start, end, computed: dict[str, dict]) -> list[dict]: | |
| """Independent ties: [{check, ok, a, b, gap, note}] in amazon_profit.validate's shape.""" | |
| skus = [s for s in (_text(k) for k in computed) if s] | |
| checks = [] | |
| spend = sum(_f(r.get("ad_spend")) or 0.0 for r in computed.values()) | |
| econ_columns = _econ_ads_columns(con) | |
| if econ_columns: | |
| # Summed one column at a time in Python, a different path from the per SKU row sum. | |
| total = 0.0 | |
| for column in econ_columns: | |
| total += float(con.execute( | |
| f"SELECT COALESCE(SUM(TRY_CAST({_q(column)} AS DOUBLE)), 0) FROM {ECONOMICS} WHERE " | |
| f"left(CAST(start_date AS VARCHAR), 10) BETWEEN ? AND ? AND " | |
| f"TRIM(CAST(msku AS VARCHAR)) IN (SELECT unnest(?))", [start, end, skus or [""]]).fetchone()[0]) | |
| gap = abs(spend - total) | |
| checks.append(_check("ads_spend_ties_economics", gap <= SPEND_TIE_TOLERANCE, spend, total, gap, | |
| "Ad spend against the economics ad charges in the period, within one cent.")) | |
| else: | |
| checks.append(_check("ads_spend_ties_economics", True, 0, 0, 0, "Economics carries no ad charges.")) | |
| campaigns = [t for t in ADS_CAMPAIGNS if {"date", "cost"} <= _columns(con, t)] | |
| if econ_columns: | |
| checks.append(_check("ads_spend_ties_campaigns", True, None, None, None, | |
| "Ad spend comes from economics, so the campaign reports do not apply.")) | |
| elif not campaigns and _ads_parts(con) is None: | |
| checks.append(_check("ads_spend_ties_campaigns", True, 0, 0, 0, NO_ADS_NOTE)) | |
| elif not campaigns: | |
| checks.append(_check("ads_spend_ties_campaigns", False, spend, None, None, | |
| "Campaign reports are missing, so ad spend cannot be tied.")) | |
| else: | |
| union = " UNION ALL ".join(f"SELECT {_day('date')} AS day, TRY_CAST(cost AS DOUBLE) AS cost FROM {t}" | |
| for t in campaigns) | |
| total = float(con.execute(f"SELECT COALESCE(SUM(cost), 0) FROM ({union}) c WHERE day BETWEEN " | |
| "CAST(? AS DATE) AND CAST(? AS DATE)", [start, end]).fetchone()[0]) | |
| gap = abs(spend - total) / abs(total) if total else abs(spend - total) | |
| checks.append(_check("ads_spend_ties_campaigns", gap <= ADS_TIE_TOLERANCE, spend, total, gap, | |
| "Advertised product spend against campaign spend, within 2%.")) | |
| on_hand = sum(_f(r.get("fba_on_hand")) or 0.0 for r in computed.values()) | |
| cols = _columns(con, PLANNING) | |
| if not {"sku", "available"} <= cols: | |
| checks.append(_check("stock_ties_planning", True, 0, 0, 0, NO_STOCK_NOTE)) | |
| else: | |
| newest = ('CAST("_snapshot_date" AS VARCHAR) = (SELECT MAX(CAST("_snapshot_date" AS VARCHAR)) ' | |
| f"FROM {PLANNING})" if "_snapshot_date" in cols else "TRUE") | |
| total = float(con.execute(f"SELECT COALESCE(SUM(TRY_CAST(available AS DOUBLE)), 0) FROM {PLANNING} " | |
| f"WHERE {newest} AND {_sku_sql('sku')} IN (SELECT unnest(?))", | |
| [skus or [""]]).fetchone()[0]) | |
| gap = abs(on_hand - total) | |
| checks.append(_check("stock_ties_planning", gap <= COUNT_TIE_TOLERANCE, on_hand, total, gap, | |
| "FBA on hand against the newest planning snapshot.")) | |
| returned = sum(_f(r.get("return_units")) or 0.0 for r in computed.values()) | |
| union = _returns_parts(con) | |
| if union is None: | |
| checks.append(_check("returns_tie", True, 0, 0, 0, NO_RETURNS_NOTE)) | |
| else: | |
| total = float(con.execute(f"SELECT COALESCE(SUM(units), 0) FROM ({union}) r WHERE day BETWEEN " | |
| "CAST(? AS DATE) AND CAST(? AS DATE) AND sku IN (SELECT unnest(?))", | |
| [start, end, skus or [""]]).fetchone()[0]) | |
| gap = abs(returned - total) | |
| checks.append(_check("returns_tie", gap <= COUNT_TIE_TOLERANCE, returned, total, gap, | |
| "Returned units against the returns tables in the period.")) | |
| short = [sku for sku, r in computed.items() for k in SERIES_KEYS | |
| if len(_series(r.get(k)) or []) != SERIES_DAYS] | |
| checks.append(_check("trend_length", not short, len(short), 0, len(short), | |
| f"Every trend has {SERIES_DAYS} days." if not short | |
| else f"Short trends: {', '.join(sorted(set(map(str, short))))}.")) | |
| return checks | |