Spaces:
Running on CPU Upgrade
Running on CPU Upgrade
File size: 25,241 Bytes
f462120 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 | """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}
|