runloopable / platform /modules /amazon_search_signals.py
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
95fb175 verified
Raw History Blame Contribute Delete
15.2 kB
"""Child ASIN search signals over complete monthly reports and their reported history."""
from __future__ import annotations
import datetime as dt
import importlib
import math
TABLE = "amzn_search_query_performance"
TERM = "search_query_data_search_query"
# Arrays use the public pdp_search_model wire order, with unknown values kept as None.
FIELDS = ("vol", "tc", "mc", "tp", "mp", "mkt", "our", "ti", "mi", "tca", "mca", "pm", "py")
COLUMNS = {
"vol": "search_query_data_search_query_volume",
"tc": "click_data_total_click_count", "mc": "click_data_asin_click_count",
"tp": "purchase_data_total_purchase_count", "mp": "purchase_data_asin_purchase_count",
"ti": "impression_data_total_query_impression_count", "mi": "impression_data_asin_impression_count",
"tca": "cart_add_data_total_cart_add_count", "mca": "cart_add_data_asin_cart_add_count",
"pm": "purchase_data_total_median_purchase_price_amount",
"py": "purchase_data_asin_median_purchase_price_amount",
}
MODEL_MISSING = "Search model unavailable: modules.pdp_search_model"
NO_REPORT = "No search report"
# SQP value columns: your sales and all sales on the searches (market share = SALES_COL / MARKET_SALES_COL).
SALES_COL = 6
MARKET_SALES_COL = 5
def _model():
try:
return importlib.import_module("modules.pdp_search_model")
except ModuleNotFoundError as exc:
if exc.name != "modules.pdp_search_model":
raise
return None
def trend_score(src, context):
"""Return the canonical score for {m: {YYYY-MM: FIELDS array}} and {months, history}."""
model = _model()
values = list(src.get("m", {}).values())
if model is None or any(v[0] is None for v in values):
return None
return model.trendScore75(src, context)
def funnel_score(rows):
"""Return the canonical searches-weighted score from rate55 rows containing vol and total."""
model = _model()
if model is None or any(row.get("vol") is None for row in rows):
return None
return model.listScore57(rows)["score"]
def _columns(con):
return {r[0] for r in con.execute(
"SELECT column_name FROM information_schema.columns WHERE table_name=?", [TABLE]).fetchall()}
def _period_sql(columns):
# A legacy report is monthly only when both dates span one complete calendar month.
start = "TRY_CAST(substr(CAST(start_date AS VARCHAR),1,10) AS DATE)"
end = "TRY_CAST(substr(CAST(end_date AS VARCHAR),1,10) AS DATE)"
declared = " AND upper(trim(CAST(_report_period AS VARCHAR)))='MONTH'" if "_report_period" in columns else ""
return (f"{start}=date_trunc('month',{start}) AND "
f"{end}=last_day({start}) AND {end} < ?" + declared)
def _number(value):
if value is None:
return None
number = float(value)
return number if math.isfinite(number) and number >= 0 else None
def _sum(values):
return None if any(v is None for v in values) else sum(values)
def _aggregate(arrays):
totals = [_sum([row[i] for row in arrays]) for i in range(11)]
for index, weight in ((11, 3), (12, 4)):
prices, weights = [row[index] for row in arrays], [row[weight] for row in arrays]
if any(v is None for v in prices + weights):
totals.append(None)
else:
total = sum(weights)
totals.append(sum(p * w for p, w in zip(prices, weights)) / total if total else prices[0])
return totals
def _months(last):
year, month = map(int, last.split("-"))
ordinal = year * 12 + month - 1
return [f"{i // 12:04d}-{i % 12 + 1:02d}" for i in range(ordinal - 11, ordinal + 1)]
def _load(con, columns, cutoff):
selected = [f'TRY_CAST("{col}" AS DOUBLE)' if col in columns else "NULL" for col in COLUMNS.values()]
sql = (f"SELECT CAST(asin AS VARCHAR), CAST({TERM} AS VARCHAR), "
f"substr(CAST(start_date AS VARCHAR),1,7), {','.join(selected)} FROM {TABLE} "
f"WHERE {_period_sql(columns)} ORDER BY asin, {TERM}, start_date")
cursor = con.execute(sql, [cutoff])
grouped, history = {}, set()
for batch in iter(lambda: cursor.fetchmany(2048), []):
for asin, query, month, *raw in batch:
values = dict(zip(COLUMNS, map(_number, raw)))
for key, count, price in (("mkt", "tp", "pm"), ("our", "mp", "py")):
values[key] = (_number(values[count] * values[price])
if values[count] is not None and values[price] is not None else None)
search = grouped.setdefault(asin, {}).setdefault(query, {"q": query, "m": {}})
array, previous = [values[k] for k in FIELDS], search["m"].get(month)
search["m"][month] = _aggregate([previous, array]) if previous is not None else array
history.add(month)
sources = {asin: list(queries.values()) for asin, queries in grouped.items()}
return sources, sorted(history)
def _total(src, months):
arrays = [src["m"][m] for m in months if m in src["m"]]
return dict(zip(FIELDS, _aggregate(arrays))) if arrays else None
def _ratio(numerator, denominator):
if numerator is None or denominator is None:
return None, "Search report values missing"
if denominator <= 0:
return None, "Search report denominator is zero"
return numerator / denominator, None
def _value(values, kind):
if values is None:
return None, "Month not reported"
if kind == "search":
value = values[0]
if value is None:
return None, "Search report values missing: vol"
return (value, None) if value > 0 else (None, "No positive searches reported")
numerator, denominator = (SALES_COL, MARKET_SALES_COL) if kind == "market_share" else (2, 1)
return _ratio(values[numerator], values[denominator])
def _year_change(src, months, kind):
pairs = [(src["m"][month], src["m"][str(int(month[:4]) - 1) + month[4:]])
for month in months if month in src["m"] and str(int(month[:4]) - 1) + month[4:] in src["m"]]
count = len(pairs)
if count < 3:
return None, "Fewer than 3 months reported in both years", count
current, prior = (_aggregate([p[i] for p in pairs]) for i in (0, 1))
if kind == "search":
ratio, reason = _ratio(current[0], prior[0])
else:
now, now_reason = _value(current, kind)
before, before_reason = _value(prior, kind)
ratio, reason = _ratio(now, before)
reason = now_reason or before_reason or reason
return (ratio - 1 if ratio is not None else None), reason, count
def _trend(src, context, kind):
change, reason, matched = _year_change(src, context["months"], kind)
series = []
for month in sorted(set(context["history"]) | set(context["months"])):
value, cause = _value(src["m"].get(month), kind)
series.append({"month": month, "value": value, "reason": cause})
return {"series": series, "change": change, "reason": reason, "matched_months": matched}
def _funnel(searches, context, model):
scored = []
reported = False
for search in searches:
values = _total(search, context["months"])
if values is None:
continue
reported = True
missing = [k for k in ("vol", "ti", "mi", "tc", "mc", "tca", "mca", "tp", "mp") if values[k] is None]
if missing:
return None, "Search report values missing: " + ", ".join(missing), []
if model is not None:
scored.append({"q": search["q"], **model.rate55(values)})
if not reported:
return None, "Not reported in the last 12 months", []
score = funnel_score(scored) if model is not None else None
reason = MODEL_MISSING if model is None else "Too few own observations or zero market rates" if score is None else None
return score, reason, [{"q": r["q"], "vol": r["vol"], "steps": r["score"], "total": r["total"]} for r in scored]
def _exposure(values, model):
if values is None:
return None, "Not reported in the last 12 months"
for key in ("ti", "mi", "mp", "mc"):
if values[key] is None:
return None, "Search report values missing: " + key
if values["ti"] <= 0:
return None, "Search report denominator is zero"
base = "tp" if values["mp"] >= 2 else "tc" if values["mc"] >= 10 else None
if values["mi"] > 0 and base and (values[base] is None or values[base] <= 0):
return None, "Search report exposure denominator missing or zero"
if model is None:
return None, MODEL_MISSING
result = model.expo59(values)
return {"kind": result["k"], "multiple": result["x"], "base": result.get("by")}, None
def _asin_signals(searches, context):
monthly = {}
for search in searches:
for month, values in search["m"].items():
monthly.setdefault(month, []).append(values)
src = {"m": {month: _aggregate(xs) for month, xs in monthly.items()}}
model = _model()
totals = _total(src, context["months"])
trends = {kind: _trend(src, context, kind) for kind in ("search", "market_share", "click_share")}
score = trend_score(src, context)
score_reason = (MODEL_MISSING if model is None else "Too few positive reported months") if score is None else None
if any(values[0] is None for values in src["m"].values()):
score_reason = "Search report values missing: vol"
funnel, funnel_reason, scored = _funnel(searches, context, model)
exposure, exposure_reason = _exposure(totals, model)
market, market_reason = _ratio(totals["our"], totals["mkt"]) if totals else (None, "Not reported in the last 12 months")
clicks, clicks_reason = _ratio(totals["mc"], totals["tc"]) if totals else (None, "Not reported in the last 12 months")
reasons = {"trend_score": score_reason, "funnel_score": funnel_reason, "exposure": exposure_reason,
"market_share": market_reason, "click_share": clicks_reason}
if not searches:
reasons = dict.fromkeys(reasons, NO_REPORT)
for trend in trends.values():
trend.update(change=None, reason=NO_REPORT)
return {"trend_score": score, "funnel_score": funnel, "market_share": market, "click_share": clicks,
"exposure": exposure, "trends": trends, "reasons": reasons, "note": None if searches else NO_REPORT,
"monthly": {m: dict(zip(FIELDS[:11], xs[:11])) for m, xs in src["m"].items()}, "searches": scored}
def signals(con, asins=None, *, today=None):
"""Read all complete MONTH facts from the supplied tenant cursor and return {months, history, asins}."""
cutoff = (today or dt.date.today()).replace(day=1)
columns = _columns(con)
required = {"asin", TERM, "start_date", "end_date"}
sources, history = _load(con, columns, cutoff) if required <= columns else ({}, [])
context = {"months": _months(history[-1]) if history else [], "history": history}
wanted = sorted(sources) if asins is None else list(dict.fromkeys(asins))
result = {**context, "asins": {asin: _asin_signals(sources.get(asin, []), context) for asin in wanted}}
if columns and not required <= columns:
cause = "Search report columns missing: " + ", ".join(sorted(required - columns))
for row in result["asins"].values():
row.update(note=cause, reasons=dict.fromkeys(row["reasons"], cause))
for trend in row["trends"].values():
trend["reason"] = cause
return result
def _sql_number(columns, key):
if COLUMNS[key] not in columns:
return "NULL::DOUBLE"
value = f'TRY_CAST("{COLUMNS[key]}" AS DOUBLE)'
return f"CASE WHEN isfinite({value}) AND {value}>=0 THEN {value} END"
def _sql_sums(columns):
expressions = {key: _sql_number(columns, key) for key in COLUMNS}
expressions.update(mkt=f"({expressions['tp']})*({expressions['pm']})",
our=f"({expressions['mp']})*({expressions['py']})")
return ",".join(f"CASE WHEN COUNT(*)=COUNT({expressions[key]}) THEN SUM({expressions[key]}) END"
for key in FIELDS[:11])
def validate(con, result=None, *, asins=None, today=None):
"""Tie every ASIN month and derived signal to independent uncapped raw-source SQL aggregates."""
result = signals(con, asins, today=today) if result is None else result
columns, cutoff = _columns(con), (today or dt.date.today()).replace(day=1)
if not {"asin", TERM, "start_date", "end_date"} <= columns:
return {"ok": False, "checks": {"signals tie to the search report": False}, "reason": "Search report unavailable"}
query = (f"SELECT CAST(asin AS VARCHAR), CAST({TERM} AS VARCHAR), "
f"substr(CAST(start_date AS VARCHAR),1,7), {_sql_sums(columns)} FROM {TABLE} "
f"WHERE {_period_sql(columns)} GROUP BY 1,2,3 ORDER BY 1,2,3")
sources, monthly, history = {}, {}, set()
cursor = con.execute(query, [cutoff])
for batch in iter(lambda: cursor.fetchmany(2048), []):
for asin, term, month, *values in batch:
values = [float(v) if v is not None else None for v in values]
sources.setdefault(asin, {}).setdefault(term, {})[month] = values + [None, None]
history.add(month)
cursor = con.execute(
f"SELECT CAST(asin AS VARCHAR), substr(CAST(start_date AS VARCHAR),1,7), {_sql_sums(columns)} "
f"FROM {TABLE} WHERE {_period_sql(columns)} GROUP BY 1,2 ORDER BY 1,2", [cutoff])
for batch in iter(lambda: cursor.fetchmany(2048), []):
for asin, month, *values in batch:
monthly.setdefault(asin, {})[month] = dict(zip(FIELDS[:11], values))
context = {"months": _months(max(history)) if history else [], "history": sorted(history)}
checks = {"store complete-month window": result["months"] == context["months"],
"reported history": result["history"] == context["history"]}
ties, metrics = True, True
for asin, actual in result["asins"].items():
ties = ties and _same(actual["monthly"], monthly.get(asin, {}))
searches = [{"q": q, "m": months} for q, months in sources.get(asin, {}).items()]
expected = _asin_signals(searches, context)
keys = ("trend_score", "funnel_score", "market_share", "click_share", "exposure", "trends", "reasons", "note", "searches")
metrics = metrics and all(_same(actual[key], expected[key]) for key in keys)
checks.update({"signals tie to the search report": ties, "derived signals tie to raw SQL": metrics})
ok = all(checks.values())
return {"ok": ok, "checks": checks, "reason": None if ok else "Search signals differ from raw SQL"}
def _same(actual, expected):
if isinstance(actual, dict) and isinstance(expected, dict):
return actual.keys() == expected.keys() and all(_same(actual[k], expected[k]) for k in actual)
if isinstance(actual, list) and isinstance(expected, list):
return len(actual) == len(expected) and all(_same(a, b) for a, b in zip(actual, expected))
if isinstance(actual, (int, float)) and isinstance(expected, (int, float)):
return math.isclose(actual, expected, rel_tol=1e-10, abs_tol=1e-9)
return actual == expected