"""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