A YouTube-first, operator-triggered data and AI workflow that collects public competitor signals, structures and validates them, and produces traceable competitive-intelligence reports.
Freelance project delivered through HEMLE Consulting France for Les Préparations de l'Apothicaire.
01 · Overview
Digital competitor activity is dispersed across platforms and expensive to monitor consistently. The engineering challenge was to reduce repetitive collection and consolidation work without hiding missing data, failed collection cycles or the limits of AI interpretation.
A multi-channel intelligence system built from isolated source collectors, a shared normalization model, persistent structured storage, a controlled AI analysis layer and repeatable reporting. Meta/Instagram and TikTok remain evaluated or planned channels, not delivered capabilities.
A YouTube-focused vertical slice covering collection, structured storage, validation, read-only AI analysis and local reporting. It is automated on demand rather than fully autonomous.
02 · Architecture
Collection and analysis are deliberately decoupled: Google handles data ingestion and storage; the local analysis workflow reads approved data without writing back.
YouTube Data API v3
Public channel and recent-video metadata.
↓Apps Script collector
API calls, retries, normalization and orchestration.
↓Google Sheets
Raw records, canonical records and execution logs.
↓Read-only analysis
Claude reads approved tabs without writing to Google.
↓JSON snapshots
Dated factual state for traceability and comparison.
↓Markdown report
Rule-constrained, traceable intelligence output.
03 · Data Pipeline
Ingest
Normalize
Deduplicate
Validate cycle
Snapshot
AI analysis
Report & log
Apps Script owns collection and Google writes; Claude reads in read-only mode and writes local outputs. Each side remains independently diagnosable.
Raw API objects are written before normalized records so processing can be investigated and replayed.
Missing metrics stay null and are excluded from calculations instead of becoming invented zeroes.
Snapshots, content identifiers, run logs, report indexes and append-only journals connect claims to observed records.
Versioned rules separate facts, interpretations and recommendations, prevent unsupported causality and require human review for compliance.
04 · Quality & Reliability
10 / 10
Executed on fictitious data to validate specific analysis safeguards. One truncation case failed first, was corrected, then passed. This is not a claim of total system reliability, and sensitive compliance cases still need independent review.
Automation maturity
Automated on demand
YouTube collection and local report generation work after explicit operator triggers.
Operational validation
Formalize API, Sheets and trigger tests; exercise the complete panel and a second comparison cycle.
Multi-channel orchestration
Coordinate qualified source collectors, unified data and recurring cross-channel reporting.
05 · Current Limitations
Stating what the system cannot yet do protects decision quality and makes the next engineering work explicit.
06 · Target Architecture
This is a documented design direction, not a claim that the multi-channel platform is already operational.
Not claimed: Docker, Kubernetes, Terraform, queues, CI/CD or a specific cloud deployment. The source design does not commit to them.
| Capability | V1 | Target |
|---|---|---|
| YouTube collection | Implemented | Retained |
| Meta / Instagram | Not implemented | Evaluated / planned |
| TikTok | Not implemented | V2 candidate |
| Operator trigger | Current | Reduced through orchestration |
| AI report generation | Implemented on demand | Extended across sources |
| Cross-channel analysis | Not available | Target capability |
| Real cycle comparison | Not yet exercised | Recurring snapshot analysis |
07 · My Contribution