One-off scripts to reconstruct missing daily coste/ingreso/margen/leads
from Google Ads and Leads Lake for days lost to the overwrite bug fixed
in run.py. backfill_metricas_mes.py generalizes the approach (mes/año
args) and was used to repair both mayo and junio 2026; the two junio-
specific scripts document the narrower fixes applied first.
On month change, run.py redirected the write to the previous month's
GACampaignMes record but built the JSON from the new month's near-empty
record, overwriting the previous month's full daily history with just
the redirected day. Now it merges with the previous month's real
MetricasDiarias (via get_metricas_diarias_prev_month) before writing.
Also add get_metrics_for_date() and get_daily_metrics_for_month() to
GoogleAdsClient for reconstructing historical daily cost/conversions,
needed by the backfill scripts.
Stacked/grouped bars made it hard to compare gasto vs ingreso when the
two values are close, and margen barely visible against that scale.
Extract a shared _eur_line_chart helper (single €-axis, fixed categorical
colors, tooltip) and reuse it for the per-campaign, portfolio, and
resumen daily views instead of duplicating Altair boilerplate per chart.
Streamlit 1.58 warns on every use_container_width call; switch to the
width="stretch" replacement to silence the noisy deprecation warnings.
Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
- run.py: skip metricas update when campaign has no prior-month
GACampaignMes record instead of silently writing into the new month
- slack_reporter.py: round before formatting so small negative margins
don't render as "-0€"
- Separate campaign-level daily metrics from the global portfolio view in
the Histórico tab using nested sub-tabs instead of a single stacked section.
- Shrink st.metric font sizes globally so large KPI values render correctly.
- Add a daily evolution chart in Resumen showing Inversión, Ingreso,
Margen (sumatorio) and Margen (PPL) aggregated across filtered campaigns.
Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
- analyzer.py: use abs() for tracking discrepancy so both directions alert
- slack_reporter.py: daily table adds L.AT/L.GA/Coste/€.AT/€.GA columns; margin uses Google Ads conversions × PPL; split monthly rankings into separate blocks; fix stale margen field
- run.py: leads_grupo always uses Google Ads conversions; tracking alert shows direction (over/under attribution)
- agent.py: increase decide() max_tokens 700→750 to fix JSON truncation for long campaign names
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
When multiple PMX campaigns share the same cursoid (e.g. PMX + Reset),
paths 4 (cursoid+utm_source) and 5 (cursoid+UserAgent) are disabled for
all of them to prevent double-counting Airtable leads. Only direct paths
(GACampaignID, GoogleCampaignID, attr_referer) are used in those cases.
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- airtable_client: restrict leadform cursoid path (path 5) to PMX-only
campaigns, preventing triple-attribution to search+pmx+leadform companion
- slack_reporter: derive margen from ingreso-coste instead of stale MetricasDiarias
field; split monthly rankings from fields to separate section blocks; add
_trunc() helper to clamp all text blocks under Slack's 3000-char limit
- agent: increase portfolio_daily_analysis max_tokens 500→800
- backfill: extend to June 11
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
Leadform leads in Airtable now counted for PMX campaigns via
attr_cursoid + UserAgent='Google-Ads-Notifications', both for
monthly totals and daily MetricasDiarias.
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
For PMX/Search campaigns, match leads by course number and UTM source
when direct campaign ID attribution is missing. Fixes campaigns like
fco_pmx_436 (2→48 leads) and fco_pmx_1525 (0→84 leads).
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- Add get_monthly_metrics_all() bulk method to GoogleAdsClient
- Use bulk fetch in first pass instead of N individual API calls
- Pre-compute leadform conversions per course number
- For PMX campaigns with leadform companion, leads_grupo = PMX conv + leadform conv
- fco_pmx_60 now correctly shows 334 leads at 61% margin (was negative)
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
Recalculated each day from fco_ campaign totals:
- CosteMes / ConvMes: from Google Ads monthly accumulated data
- PPLMedio: weighted average by Airtable leads
- CPAMedio: CosteMes / ConvMes
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
Both the monthly summary and the daily table now derive coste
from MetricasDiarias entries, so totals match exactly.
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- GAMes: new Airtable table aggregating daily fco_ metrics (coste, ingreso_sum, ingreso_lxp, leads, leads_lake)
- run.py: accumulate fco_ daily aggregate and write to GAMes each run
- slack_reporter.py: replace sparkline with daily margin % table (Sumatorio + LeadsxPPL per day)
- backfill_games_mayo.py: populated GAMes with all 17 existing May days
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- Add get_daily_conversions_for_month() to GoogleAdsClient
- Add backfill_leads_google_mayo.py: populates 'leads' (Google conv) for all May days
- 147 day-entries updated across 49 GACampaignMes records
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- Fetch leads from Airtable per campaign on yesterday's date before second pass
- Include 'leads' field in MetricasDiarias JSON alongside coste/ingreso/margen
- Add get_leads_by_campaign_on_date() bulk fetch in AirtableClient
- Add backfill_leads_mayo.py one-off script (already executed for days 01-03)
- Fix UnboundLocalError: remove duplicate local timedelta import inside run()
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
Leads submitted via Google Lead Form arrive with UserAgent
'Google-Ads-Notifications' and campaign name in attr_referer.
Added this as a fourth attribution clause in get_leads_this_month_gads()
to reduce discrepancy between Airtable and Google Ads conversion counts.
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- Use CosteMes and ConvMes from all previous month fco_ campaigns (not just
those active in current month) to avoid missing paused campaigns
- Show day-1 cierre block with Google Ads data labeled explicitly
- Show current month as 0 on day 1 (no data yet)
- Fetch Campaign Name in get_metricas_diarias_prev_month for fco_ filtering
- Fetch prev month data for days 1-5 (last 5 days can span two months)
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- New slack_reporter.py: posts daily summary to Slack after each run
- Shows monthly investment, revenue (sumatorio + leadsxPPL) and margins
- Rankings of best/worst fco_ campaigns for last 5 days and current month
- Alerts for campaigns with losses > 200€ or margin < -50%
- MetricasDiarias uses yesterday's data (Google Ads lag)
- SLACK_WEBHOOK_URL added to config
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
Each optimizer run writes yesterday's cost, revenue (conversions x PPL)
and margin per campaign as a JSON entry keyed by day number into the
MetricasDiarias field. Yesterday is used because Google Ads data for
the current day is not yet available.
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- Detect courses with _leadform companion campaigns before first pass
- Add ℹ️ LEADFORM note in Log for campaigns that use Google's native lead form (leads bypass the website and don't reach Airtable)
- Add ⚠️ LEADFORM COMPANION note for sibling campaigns of the same course (explains conversion count discrepancy)
- Also moves _course_num and courses_with_both detection to pre-pass (before first loop) so PMX analysis uses correct leads_grupo
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- Detect Search+PMX companion campaigns by course number
- Add PMX attribution warning to Log field for Search campaigns with active PMX
- Add ConvLeadsLakeMesGrupo: for PMX with Search companion uses Search leads count
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- Fix GoogleCampaignID matching with comma delimiters (exact word boundary)
- Remove attr_utm_medium filter (campaign ID filter is specific enough)
- Fix get_campaign_metrics: accumulate all daily rows for monthly totals
- Fix SPRINT urgency: only trigger when capping > 0
- Add Log field to GACampaignMes with Airtable vs Google Ads discrepancy info
- batch_update_gacampaignmes_advice now also updates Log field
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- Use GACampaignID field + FIND(gad_campaignid) fallback in Leads Lake filter
- Add ConvLeadsLakeMes to campaign data for tracking discrepancy comparison
- Update ConvLeadsLakeMesFinal daily as snapshot of Airtable lead count
- Agent: ignore capping when capping=0 in advice
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- batch_update_gacampaignmes_advice() replaces consejos method, also saves Criticidad
- Criticidad mapped from priority: Crítico (PAUSAR/SPRINT), Peligro, Mantener
- Updated in same batch as Consejo after second pass
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- get_active_gacampaignmes() now returns campaign_at_id (Google Ads Campaigns record ID)
- New batch_update_status() updates Status in GACampaignMes and Google Ads Campaigns
- run.py first pass collects status from get_campaign_metrics() and batch-updates
both tables at end of pass, ensuring fresh status after each run
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- analyzer.py: add status_google (ENABLED/PAUSED) to analysis dict
- agent.py: instruct agent never to recommend reactivating an ENABLED campaign;
mention reactivation in consejo only when campaign is PAUSED
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- New GACampaignMes table tracks monthly state of campaigns with activity
(ENABLED status or cost > 0 this month)
- sync_gacampaignmes(): creates/updates records with PPL, CPAMax, CosteMes,
ConvMes, CapTotalMes, Status for current month (numeric Mes: 1-12)
- get_active_gacampaignmes(): reads GACampaignMes as source for optimization loop
- get_leads_this_month_gads(): filters leads by GoogleCampaignID + utm_source
(pmx/google) + utm_medium (paid_search) instead of attr_cursoid
- update_gacampaignmes_leads_lake(): links leads to GACampaignMes Leads Lake field
- batch_update_gacampaignmes_consejos(): saves agent advice to Consejo field
- Google Ads Campaigns table simplified to catalog only (name, status, PPL)
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
- Campaigns now shown in three sections:
1. ACCIONES CRÍTICAS (urgencia PAUSAR/SPRINT)
2. ACCIONES NO CRÍTICAS (other budget changes)
3. SIN CAMBIOS (MANTENER)
- First pass collects all data, then sorts before printing
- Each campaign shows direct Google Ads link with account ID
- Summary table also grouped by section with links
Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
