loopable / platform /modules /amazon_operator_hourly.py
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
9e15897 verified
Raw History Blame
25.2 kB
"""Read SP Stream hours in the Ads profile's timezone and reconcile daily campaign cost."""
import datetime as dt
import math
from zoneinfo import ZoneInfo, ZoneInfoNotFoundError
from modules import amazon_operator as op
SOURCE = "amzn_ads_sp_hourly"
DAILY = "amzn_ads_sp_campaigns"
METRICS = ("spend", "clicks", "orders", "sales")
EMPTY = "Hourly data starts the hour after it is turned on"
UNSUPPORTED = "Hourly data is available for Sponsored Products only."
def _days(start, end):
try:
first, last = (dt.date.fromisoformat(str(value)) for value in (start, end))
except (TypeError, ValueError) as exc:
raise ValueError("Use YYYY-MM-DD for the hourly dates.") from exc
if first > last:
raise ValueError("The hourly start must be on or before its end.")
while True:
yield first.isoformat()
if first == last:
break
first += dt.timedelta(days=1)
def _scope(campaign_id, connection_id, profile_id):
clauses, args = ['CAST(campaign_id AS VARCHAR) = ?'], [str(campaign_id)]
for key, value in (("_connection_id", connection_id), ("profile_id", profile_id)):
if value is not None:
clauses.append(f'CAST("{key}" AS VARCHAR) = ?')
args.append(str(value))
return " AND ".join(clauses), args
def _scoped_identity(cur, campaign_id, connection_id, profile_id):
if not {"campaign_id", "profile_id", "_connection_id", "date", "cost"} <= op._columns(cur, DAILY):
return None, "Scoped daily campaign reports are unavailable. Sync Amazon Ads first."
where, args = _scope(campaign_id, connection_id, profile_id)
count = cur.execute(f"SELECT COUNT(*) FROM (SELECT DISTINCT _connection_id, profile_id FROM {DAILY} "
f"WHERE {where})", args).fetchone()[0]
if count != 1:
return None, "Choose one campaign connection and Ads profile with daily history."
connection, profile = cur.execute(f"SELECT DISTINCT _connection_id, profile_id FROM {DAILY} WHERE {where}",
args).fetchone()
if not connection or not profile:
return None, "The campaign report has no verified connection and Ads profile."
return (str(connection), str(profile)), None
def _occurrences(day, hour, timezone):
"""Count real local hours, including the spring gap and autumn repeat."""
wall = dt.datetime.combine(dt.date.fromisoformat(day), dt.time(hour))
instants = set()
for fold in (0, 1):
local = wall.replace(tzinfo=timezone, fold=fold)
utc = local.astimezone(dt.timezone.utc)
if utc.astimezone(timezone).replace(tzinfo=None) == wall:
instants.add(utc)
return len(instants)
def _base(campaign_id, days, ad_type, connection_id, profile_id, reason, state="unavailable"):
return {"campaign_id": str(campaign_id), "ad_type": ad_type, "connection_id": connection_id,
"profile_id": profile_id, "start": days[0], "end": days[-1], "timezone": None, "currency": None,
"attribution_window": "14d", "state": state, "reason": reason, "source_table": SOURCE,
"rows": [], "daily_rows": [{"date": day, "hour": hour, **dict.fromkeys((*METRICS, "acos")),
"observed": False, "occurrences": 0, "expected_occurrences": 1,
"metric_occurrences": dict.fromkeys(METRICS, 0), "reason": reason}
for day in days for hour in range(24)], "totals": dict.fromkeys((*METRICS, "acos"))}
def _publish_hours(report):
"""Publish one aggregate per hour, withholding each metric until every expected occurrence reports it."""
buckets = [[] for _ in range(24)]
for row in report["daily_rows"]:
buckets[row["hour"]].append(row)
report["rows"] = []
for hour, slots in enumerate(buckets):
active = [slot for slot in slots if slot["expected_occurrences"]]
row = {"hour": hour, "observed": any(slot["observed"] for slot in active),
"occurrences": sum(slot["occurrences"] for slot in active),
"expected_occurrences": sum(slot["expected_occurrences"] for slot in active),
"metric_occurrences": {metric: sum(slot["metric_occurrences"][metric] for slot in active)
for metric in METRICS},
"reason": "; ".join(dict.fromkeys(slot["reason"] for slot in active if slot["reason"])) or None}
if report["start"] == report["end"]:
row["date"] = report["start"]
for metric in METRICS:
complete = active and all(slot[metric] is not None and math.isfinite(slot[metric])
and slot["metric_occurrences"][metric] >= slot["expected_occurrences"] for slot in active)
value = sum(slot[metric] for slot in active) if complete else None
row[metric] = value if value is not None and math.isfinite(value) else None
row["acos"] = _acos(row["spend"], row["sales"])
if not active:
row["reason"] = "This hour does not occur in profile time"
report["rows"].append(row)
def _stream(cur, report):
required = {"campaign_id", "_connection_id", "profile_id", "currency", "timezone", "date", "hour",
"time_window_start", "attribution_window", *METRICS}
if not required <= op._columns(cur, SOURCE):
return "empty", EMPTY
where, args = _scope(report["campaign_id"], report["connection_id"], report["profile_id"])
metadata = cur.execute(f"SELECT DISTINCT timezone, currency FROM {SOURCE} WHERE {where}", args)
identities = []
while batch := metadata.fetchmany(256):
identities.extend(batch)
if not identities:
return "empty", EMPTY
if len(identities) != 1 or not all(identities[0]):
return "unavailable", "The hourly source has inconsistent profile timezone or currency."
timezone, currency = identities[0]
try:
ZoneInfo(str(timezone))
except (ZoneInfoNotFoundError, ValueError):
return "unavailable", "The Ads profile timezone is unavailable."
report.update(timezone=str(timezone), currency=str(currency))
if "currency" in op._columns(cur, DAILY):
mismatched = cur.execute(f"SELECT COUNT(*) FROM {DAILY} WHERE {where} "
'AND TRY_CAST("date" AS DATE) BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) '
'AND currency IS DISTINCT FROM ?', [*args, report["start"], report["end"], currency]).fetchone()[0]
if mismatched:
return "unavailable", "Hourly and daily campaign currencies do not match."
# Bucket aware timestamps in profile time, never the server's or UTC's calendar.
source = (f"SELECT *, timezone(?, TRY_CAST(time_window_start AS TIMESTAMPTZ)) AS local_time "
f"FROM {SOURCE} WHERE {where}")
valid = ("regexp_matches(time_window_start, '(Z|[+-][0-9]{2}:[0-9]{2})$') AND local_time IS NOT NULL "
'AND CAST(local_time AS DATE) = TRY_CAST("date" AS DATE) '
'AND hour(local_time) = TRY_CAST("hour" AS INTEGER) AND minute(local_time) = 0 '
'AND second(local_time) = 0')
period = 'TRY_CAST("date" AS DATE) BETWEEN CAST(? AS DATE) AND CAST(? AS DATE)'
params = [timezone, *args, report["start"], report["end"]]
invalid = cur.execute(f"WITH source AS ({source}) SELECT COUNT(*) FROM source WHERE ({period} "
"OR CAST(local_time AS DATE) BETWEEN CAST(? AS DATE) AND CAST(? AS DATE)) "
f"AND NOT COALESCE(({valid}), FALSE)",
[*params, report["start"], report["end"]]).fetchone()[0]
if invalid:
return "unavailable", "Hourly timestamps do not match the Ads profile's local date and hour."
incompatible = cur.execute(f"WITH source AS ({source}) SELECT COUNT(*) FROM source WHERE {period} "
"AND attribution_window IS DISTINCT FROM '14d'", params).fetchone()[0]
if incompatible:
return "unavailable", "Hourly sales and orders need a compatible 14d attribution window."
expressions = []
for metric in METRICS:
value = f'TRY_CAST("{metric}" AS DOUBLE)'
expressions.append(f'CASE WHEN COUNT(*) FILTER (WHERE "{metric}" IS NOT NULL '
f'AND ({value} IS NULL OR NOT isfinite({value}))) > 0 THEN NULL '
f'ELSE SUM({value}) END AS "{metric}"')
result = cur.execute(f"WITH source AS ({source}) SELECT CAST(local_time AS DATE), hour(local_time), "
+ ", ".join(expressions)
+ ", COUNT(DISTINCT TRY_CAST(time_window_start AS TIMESTAMPTZ)), "
+ ", ".join(f'COUNT(DISTINCT CASE WHEN TRY_CAST("{metric}" AS DOUBLE) IS NOT NULL '
f'AND isfinite(TRY_CAST("{metric}" AS DOUBLE)) '
'THEN TRY_CAST(time_window_start AS TIMESTAMPTZ) END)' for metric in METRICS)
+ " FROM source "
"WHERE CAST(local_time AS DATE) BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) "
"GROUP BY 1, 2 ORDER BY 1, 2", params)
observed = {}
while batch := result.fetchmany(256):
for day, hour, *values in batch:
observed[(str(day), hour)] = values
tz = ZoneInfo(str(timezone))
for row in report["daily_rows"]:
values = observed.get((row["date"], row["hour"]))
expected = _occurrences(row["date"], row["hour"], tz)
row["expected_occurrences"] = expected
if not values:
row["reason"] = "No hourly coverage" if expected else "This hour does not occur in profile time"
continue
row.update(zip(METRICS, values[:4]))
row.update(observed=True, occurrences=values[4], metric_occurrences=dict(zip(METRICS, values[5:])), reason=None)
missing = [metric for metric in METRICS if row[metric] is None or not math.isfinite(row[metric])]
if missing:
for metric in missing:
row[metric] = None
row["reason"] = "Unreported or invalid " + ", ".join(missing)
if row["occurrences"] < expected:
row["reason"] = "Incomplete coverage of the repeated profile hour"
elif any(count < expected for count in row["metric_occurrences"].values()):
row["reason"] = "Unreported or partial " + ", ".join(
metric for metric, count in row["metric_occurrences"].items() if count < expected)
row["acos"] = _acos(row["spend"], row["sales"])
active = [row for row in report["daily_rows"] if row["expected_occurrences"]]
for metric in METRICS:
if active and all(row[metric] is not None and row.get("metric_occurrences", {}).get(metric, 0)
>= row["expected_occurrences"] for row in active):
total = sum(row[metric] for row in active)
report["totals"][metric] = total if math.isfinite(total) else None
report["totals"]["acos"] = _acos(report["totals"]["spend"], report["totals"]["sales"])
return ("partial", "Some hourly metrics or hours are unavailable.") if any(
row["reason"] and row["expected_occurrences"] for row in report["daily_rows"]) else ("ready", None)
def _acos(spend, sales):
value = spend / sales * 100 if spend is not None and sales is not None and sales > 0 else None
return round(value, 2) if value is not None and math.isfinite(value) else None
def by_hour(tenant_key, campaign_id, start, end, *, st=None, ad_type="sp", connection_id=None, profile_id=None):
"""Return 24 hour-of-day aggregates and every daily slot for the requested profile-local window."""
days = list(_days(start, end))
if not str(campaign_id or "").strip():
raise ValueError("Choose a campaign for the hourly report.")
report = _base(campaign_id, days, ad_type, connection_id, profile_id, EMPTY)
if ad_type != "sp":
report.update(state="unsupported", reason=UNSUPPORTED)
_publish_hours(report)
report["validation"] = {"ok": False, "detail": UNSUPPORTED,
"days": [{"date": day, "ok": False, "hourly_spend": None, "daily_spend": None,
"difference_pct": None, "missing_hours": 24, "reasons": [UNSUPPORTED]}
for day in days]}
return report
st = op._rt(tenant_key, st)
with op._reader(tenant_key, st) as cur:
identity, reason = _scoped_identity(cur, campaign_id, connection_id, profile_id) if cur else (None, op.NOT_INSTALLED)
if identity:
report.update(connection_id=identity[0], profile_id=identity[1])
state, reason = _stream(cur, report)
report.update(state=state, reason=reason)
else:
report["reason"] = reason
if report["state"] == "unavailable":
for row in report["daily_rows"]:
row["reason"] = report["reason"]
_publish_hours(report)
report["validation"] = validate(tenant_key, campaign_id, start, end, st=st, report=report,
ad_type=ad_type, connection_id=report["connection_id"], profile_id=report["profile_id"])
if report["state"] == "ready" and not report["validation"]["ok"]:
report.update(state="partial", reason=report["validation"]["detail"])
return report
def validate(tenant_key, campaign_id, start, end, *, st=None, report=None, ad_type="sp", connection_id=None, profile_id=None):
"""Check published hour aggregates and daily slots against source metrics, coverage and daily campaign cost."""
days = list(_days(start, end))
st = op._rt(tenant_key, st)
if report is None:
return by_hour(tenant_key, campaign_id, start, end, st=st, ad_type=ad_type,
connection_id=connection_id, profile_id=profile_id)["validation"]
costs, independent, coverage = {}, {}, {}
timezone = None
source_identity = None
reason = None
with op._reader(tenant_key, st) as cur:
identity, reason = _scoped_identity(cur, campaign_id, connection_id, profile_id) if cur else (None, op.NOT_INSTALLED)
if identity and ad_type == "sp":
where, args = _scope(campaign_id, *identity)
result = cur.execute(f'SELECT TRY_CAST("date" AS DATE), SUM(TRY_CAST(cost AS DOUBLE)), '
'COUNT(*) FILTER (WHERE TRY_CAST(cost AS DOUBLE) IS NULL '
'OR NOT isfinite(TRY_CAST(cost AS DOUBLE))) '
f'FROM {DAILY} WHERE {where} AND TRY_CAST("date" AS DATE) '
'BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) GROUP BY 1', [*args, start, end])
while batch := result.fetchmany(256):
costs.update({str(day): value if not bad else None for day, value, bad in batch})
if {"date", "hour", "_connection_id", "profile_id", "campaign_id", "time_window_start",
"timezone", "currency", *METRICS} <= op._columns(cur, SOURCE):
metadata = cur.execute(f"SELECT DISTINCT timezone, currency FROM {SOURCE} WHERE {where}", args)
identities = []
while batch := metadata.fetchmany(256):
identities.extend(batch)
if len(identities) == 1 and all(identities[0]):
source_identity = tuple(map(str, identities[0]))
try:
timezone = ZoneInfo(str(identities[0][0]))
except (ZoneInfoNotFoundError, ValueError):
pass
metrics = []
for metric in METRICS:
value = f'TRY_CAST("{metric}" AS DOUBLE)'
metrics.append(f'CASE WHEN COUNT(*) FILTER (WHERE "{metric}" IS NOT NULL '
f'AND ({value} IS NULL OR NOT isfinite({value}))) > 0 THEN NULL ELSE SUM({value}) END')
result = cur.execute(f'SELECT TRY_CAST("date" AS DATE), TRY_CAST("hour" AS INTEGER), '
+ ', '.join(metrics)
+ ', COUNT(DISTINCT TRY_CAST(time_window_start AS TIMESTAMPTZ)), '
+ ', '.join(f'COUNT(DISTINCT CASE WHEN TRY_CAST("{metric}" AS DOUBLE) IS NOT NULL '
f'AND isfinite(TRY_CAST("{metric}" AS DOUBLE)) '
'THEN TRY_CAST(time_window_start AS TIMESTAMPTZ) END)' for metric in METRICS)
+ f' FROM {SOURCE} WHERE {where} '
'AND TRY_CAST("date" AS DATE) BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) '
'GROUP BY 1, 2', [*args, start, end])
while batch := result.fetchmany(256):
for day, hour, *values in batch:
independent[(str(day), hour)] = values[:4]
coverage[(str(day), hour)] = values[4:]
else:
reason = reason or UNSUPPORTED
scope_matches = (str(report.get("campaign_id")) == str(campaign_id) and report.get("ad_type") == ad_type == "sp"
and report.get("start") == days[0] and report.get("end") == days[-1]
and source_identity == (report.get("timezone"), report.get("currency"))
and report.get("attribution_window") == "14d" and report.get("source_table") == SOURCE
and identity == (report.get("connection_id"), report.get("profile_id")))
checks = []
expected_counts = {(day, hour): _occurrences(day, hour, timezone) if timezone else 1
for day in days for hour in range(24)}
for day in days:
rows = [row for row in report.get("daily_rows", []) if row.get("date") == day]
reasons = [reason] if reason else []
if not scope_matches:
reasons.append("The hourly report does not match the requested campaign scope.")
if report.get("state") in ("unavailable", "unsupported"):
reasons.append(report.get("reason") or "Hourly data unavailable")
if len(rows) != 24 or {row.get("hour") for row in rows} != set(range(24)) \
or len(report.get("daily_rows", [])) != len(days) * 24:
reasons.append("The report needs all 24 local-hour slots.")
relevant = [row for row in rows if expected_counts.get((day, row.get("hour")), 1)]
missing = [row for row in relevant if coverage.get((day, row.get("hour")), [0] * 5)[0]
< expected_counts.get((day, row.get("hour")), 1)]
if missing:
reasons.append(f"Missing coverage for {len(missing)} local hours.")
missing_metrics = [metric for metric in METRICS if any(row.get(metric) is None for row in relevant)]
if missing_metrics:
reasons.append("Unreported metrics: " + ", ".join(missing_metrics) + ".")
incomplete = [metric for index, metric in enumerate(METRICS) if any(
coverage.get((day, row.get("hour")), [0] * 5)[index + 1]
< expected_counts.get((day, row.get("hour")), 1) for row in relevant)]
if incomplete:
reasons.append("Incomplete metric coverage: " + ", ".join(incomplete) + ".")
for row in rows:
counts = coverage.get((day, row.get("hour")), [0] * 5)
if (row.get("expected_occurrences") != expected_counts.get((day, row.get("hour")))
or row.get("occurrences") != counts[0] or row.get("observed") != bool(counts[0])
or row.get("metric_occurrences") != dict(zip(METRICS, counts[1:]))):
reasons.append(f'Hour {row["hour"]}: coverage does not tie to reported source occurrences.')
values = independent.get((day, row.get("hour")), [None] * len(METRICS))
expected = {metric: value if value is not None and math.isfinite(value) else None
for metric, value in zip(METRICS, values)}
expected["acos"] = _acos(expected["spend"], expected["sales"])
if any((row.get(metric) is not None if value is None else
not isinstance(row.get(metric), (int, float)) or not math.isfinite(row[metric])
or not math.isclose(row[metric], value, rel_tol=1e-9, abs_tol=1e-9))
for metric, value in expected.items()):
reasons.append(f'Hour {row["hour"]}: figures do not tie to reported source metrics.')
spend = [row.get("spend") for row in rows if row.get("spend") is not None]
observed = sum(spend) if spend and all(isinstance(value, (int, float)) and math.isfinite(value) for value in spend) else None
daily = costs.get(day)
if daily is None or not math.isfinite(daily):
reasons.append("Daily campaign cost unavailable.")
discrepancy = abs(observed - daily) if observed is not None and daily is not None and math.isfinite(daily) else None
pct = discrepancy * 100 / abs(daily) if discrepancy is not None and daily != 0 else None
if discrepancy is not None and discrepancy > abs(daily) * .02 + 1e-9:
reasons.append("Hourly spend differs from daily campaign cost by more than 2%. Amazon may restate reports.")
if observed is None:
reasons.append("Hourly spend unavailable.")
checks.append({"date": day, "ok": not reasons, "hourly_spend": observed, "daily_spend": daily,
"difference_pct": pct, "missing_hours": len(missing), "reasons": reasons})
published = report.get("rows", [])
aggregate_errors = []
if len(published) != 24 or [row.get("hour") for row in published] != list(range(24)):
aggregate_errors.append("The published report needs exactly 24 hour-of-day aggregates.")
for row in published:
hour = row.get("hour")
slots = [(day, hour) for day in days if expected_counts.get((day, hour), 0)]
expected = {}
for index, metric in enumerate(METRICS):
values = [independent.get(slot, [None] * 4)[index] for slot in slots]
complete = slots and all(value is not None and math.isfinite(value) for value in values) and all(
coverage.get(slot, [0] * 5)[index + 1] >= expected_counts[slot] for slot in slots)
value = sum(values) if complete else None
expected[metric] = value if value is not None and math.isfinite(value) else None
expected["acos"] = _acos(expected["spend"], expected["sales"])
if any((row.get(metric) is not None if value is None else
not isinstance(row.get(metric), (int, float)) or not math.isfinite(row[metric])
or not math.isclose(row[metric], value, rel_tol=1e-9, abs_tol=1e-9))
for metric, value in expected.items()):
aggregate_errors.append(f"Hour {hour}: published figures do not tie to independently aggregated source metrics.")
counts = [sum(coverage.get(slot, [0] * 5)[index] for slot in slots) for index in range(5)]
if (row.get("expected_occurrences") != sum(expected_counts[slot] for slot in slots)
or row.get("occurrences") != counts[0] or row.get("observed") != bool(counts[0])
or row.get("metric_occurrences") != dict(zip(METRICS, counts[1:]))
or (row.get("date") != days[0] if len(days) == 1 else row.get("date") is not None)):
aggregate_errors.append(f"Hour {hour}: published scope or coverage does not match the requested window.")
if aggregate_errors:
checks[0]["ok"] = False
checks[0]["reasons"].extend(aggregate_errors)
totals = {}
active = [slot for slot, count in expected_counts.items() if count]
for index, metric in enumerate(METRICS):
values = [independent.get(slot, [None] * 4)[index] for slot in active]
complete = active and all(coverage.get(slot, [0] * 5)[index + 1] >= expected_counts[slot]
for slot in active) and all(value is not None and math.isfinite(value) for value in values)
total = sum(values) if complete else None
totals[metric] = total if total is not None and math.isfinite(total) else None
totals["acos"] = _acos(totals["spend"], totals["sales"])
actual = report.get("totals", {})
if any((actual.get(metric) is not None if value is None else
not isinstance(actual.get(metric), (int, float)) or not math.isfinite(actual[metric])
or not math.isclose(actual[metric], value, rel_tol=1e-9, abs_tol=1e-9)) for metric, value in totals.items()):
checks[0]["ok"] = False
checks[0]["reasons"].append("Hourly totals do not tie to independently aggregated source metrics.")
errors = [f'{check["date"]}: {" ".join(check["reasons"])}' for check in checks if not check["ok"]]
return {"ok": not errors, "detail": " ".join(errors) or "Each profile-local day ties to daily campaign cost within 2%.",
"days": checks}