Download platform/modules/amazon_search_signals.py from fsanyoto/loopable: direct link, hf CLI and curl.
- Browser
- Download file 15.2 kB
-
https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/platform/modules/amazon_search_signals.py
- Command line
-
hf download hf://spaces/fsanyoto/loopable/platform/modules/amazon_search_signals.py
-
curl -L -o amazon_search_signals.py https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/platform/modules/amazon_search_signals.py
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 | |