José Manuel Gómez
769d86c896
- Broaden Airtable lead counting to attr_utm_source IN ('Lead ads','landingpage')
— the 'landingpage' leads (100% fbclid, 0% gclid) were being missed entirely,
undercounting real leads for '_web' suffixed campaigns and skewing
capping/pacing decisions since yesterday's first production run.
- Add airtable_client.get_meta_leads_bulk() for day/curso-level aggregation.
- Drop per-familia Slack sectioning in favor of a single Formación block,
chunked by campaign batches instead.
- Add daily AT-vs-Meta table, per-curso PPL/CPL contrast table (leadform vs
landing breakdown), and a Claude-generated portfolio strategic diagnosis
(ported from leads-optimizer's portfolio_daily_analysis).
- Persist daily aggregate totals to a new Baserow table (daily_metrics) so
the dashboard and future reports don't depend on Meta's historical API
access remaining available indefinitely.
- Surface adset/ad-level recommendations in the campaign cards instead of
only numeric tables.
96 lines
3.3 KiB
Python
96 lines
3.3 KiB
Python
"""
|
|
One-time script: adds the 'daily_metrics' table to the EXISTING
|
|
meta_optimizer_formacion database in Baserow (created by setup_baserow.py).
|
|
|
|
Un registro por día con los totales agregados de todo el bloque Formación
|
|
(spend, leads_meta, leads_at, ing_meta, ing_at, margin, margin_pct). Se
|
|
persiste cada día al ejecutar run.py para no depender de poder volver a
|
|
pedirle a Meta el histórico diario más adelante (la API no garantiza
|
|
retención ilimitada), y para que el dashboard pueda leerlo sin llamar a
|
|
Meta/Airtable cada vez.
|
|
|
|
Usage:
|
|
python add_daily_metrics_table.py
|
|
"""
|
|
import os
|
|
import sys
|
|
import requests
|
|
from dotenv import load_dotenv
|
|
|
|
sys.stdout.reconfigure(encoding="utf-8", errors="replace")
|
|
|
|
load_dotenv()
|
|
|
|
BASE_URL = os.environ.get("BASEROW_URL", "").rstrip("/")
|
|
EMAIL = os.environ.get("BASEROW_EMAIL", "")
|
|
PASSWORD = os.environ.get("BASEROW_PASSWORD", "")
|
|
DB_NAME = "meta_optimizer_formacion"
|
|
|
|
if not BASE_URL or not EMAIL or not PASSWORD:
|
|
print("Error: BASEROW_URL, BASEROW_EMAIL and BASEROW_PASSWORD must be set in .env")
|
|
sys.exit(1)
|
|
|
|
auth = requests.post(f"{BASE_URL}/api/user/token-auth/",
|
|
json={"email": EMAIL, "password": PASSWORD}, timeout=10)
|
|
if not auth.ok:
|
|
print(f"Auth error: {auth.text}")
|
|
sys.exit(1)
|
|
JWT = auth.json()["access_token"]
|
|
HEADERS = {"Authorization": f"JWT {JWT}", "Content-Type": "application/json"}
|
|
|
|
|
|
def api(method, path, **kwargs):
|
|
resp = requests.request(method, f"{BASE_URL}/api{path}", headers=HEADERS, **kwargs)
|
|
if not resp.ok:
|
|
print(f" API error {resp.status_code} {method} {path}: {resp.text[:300]}")
|
|
resp.raise_for_status()
|
|
return resp.json()
|
|
|
|
|
|
db_id = None
|
|
for ws in api("GET", "/workspaces/"):
|
|
for app in api("GET", f"/applications/workspace/{ws['id']}/"):
|
|
if app.get("name") == DB_NAME:
|
|
db_id = app["id"]
|
|
break
|
|
if db_id:
|
|
break
|
|
|
|
if not db_id:
|
|
print(f"Error: no se encontró la base '{DB_NAME}'. Ejecuta setup_baserow.py primero.")
|
|
sys.exit(1)
|
|
|
|
print(f"Database: {DB_NAME} (id={db_id})")
|
|
|
|
existing_tables = api("GET", f"/database/tables/database/{db_id}/")
|
|
if any(t["name"] == "daily_metrics" for t in existing_tables):
|
|
print("La tabla 'daily_metrics' ya existe. Nada que hacer.")
|
|
sys.exit(0)
|
|
|
|
t = api("POST", f"/database/tables/database/{db_id}/", json={"name": "daily_metrics"})
|
|
table_id = t["id"]
|
|
print(f"Table: daily_metrics (id={table_id})")
|
|
|
|
primary_id = api("GET", f"/database/fields/table/{table_id}/")[0]["id"]
|
|
api("PATCH", f"/database/fields/{primary_id}/", json={"name": "date", "type": "text"})
|
|
print(" ~ primary field: date")
|
|
|
|
for f in [
|
|
{"name": "spend", "type": "number", "number_decimal_places": 2},
|
|
{"name": "leads_meta", "type": "number"},
|
|
{"name": "leads_at", "type": "number"},
|
|
{"name": "ing_meta", "type": "number", "number_decimal_places": 2},
|
|
{"name": "ing_at", "type": "number", "number_decimal_places": 2},
|
|
{"name": "margin", "type": "number", "number_decimal_places": 2, "number_negative": True},
|
|
{"name": "margin_pct", "type": "number", "number_decimal_places": 1, "number_negative": True},
|
|
]:
|
|
api("POST", f"/database/fields/table/{table_id}/", json=f)
|
|
print(f" + {f['name']}")
|
|
|
|
print(f"""
|
|
{'='*50}
|
|
Añade esto a tu .env:
|
|
BASEROW_TABLE_DAILY_METRICS={table_id}
|
|
{'='*50}
|
|
""")
|