# Ferrero DAM Asset Production Workflow
## Complete System Guide
**Version:** 2.1
**Last Updated:** November 2025
**Document Owner:** Operations Team
**Audience:** Campaign Managers, Creative Teams, Agencies, Technical Staff
---
## Complete Workflow Overview: A1 → A4 (with A5-A6 Rejection Cycle)
```mermaid
flowchart TB
subgraph Creation["PHASE 1: Campaign Creation"]
A1_START[Campaign Manager creates campaign in DAM] --> A1_UPLOAD[Creative team uploads master assets]
A1_UPLOAD --> A1_STATUS[Set campaign status to A1]
end
subgraph A1toA2["PHASE 2: A1→A2 Master Download (AUTOMATED - Every 5 min)"]
A1_STATUS -->|Script detects A1 campaign| A1_DETECT[a1_to_a2_download.py]
A1_DETECT --> A1_DOWNLOAD[Download all master assets from DAM]
A1_DOWNLOAD --> A1_TRACKING[Generate unique tracking IDs
pOiJ9s, a7K9mP, bvSGyd]
A1_TRACKING --> A1_BOX[Upload to Box with tracking IDs
nutella_hero_pOiJ9s.mp4]
A1_BOX --> A1_DB[Store in database with full metadata]
A1_DB --> A1_CX{CreativeX in
master metadata?}
A1_CX -->|Yes| A1_CX_STORE[Store master score
status='master-cx-score']
A1_CX -->|No| A1_CX_SKIP[Log: No CreativeX normal]
A1_CX_STORE --> A1_CHECK{All assets
successful?}
A1_CX_SKIP --> A1_CHECK
A1_CHECK -->|Yes| A1_UPDATE[Update DAM status A1 → A2]
A1_CHECK -->|No| A1_RETRY[Retry failed on next run]
A1_UPDATE --> A1_EMAIL[Email: Masters ready for localization]
A1_RETRY -.->|5 min later| A1_DETECT
end
subgraph Agency["PHASE 3: Agency Localization + CreativeX Scoring"]
A1_EMAIL --> AG_RECEIVE[Agency receives Box URLs + tracking IDs]
AG_RECEIVE --> AG_DOWNLOAD[Download masters from Box]
AG_DOWNLOAD --> AG_CREATE[Create localized versions
DE, FR, IT versions + aspect ratios]
AG_CREATE --> AG_CREATIVEX[🔴 CRITICAL: Submit EVERY derivative to CreativeX
200 derivatives = 200 analyses required]
AG_CREATIVEX --> AG_PDF[Receive PDF scorecard for EACH asset]
AG_PDF --> AG_PDF_UP[Upload ALL PDFs to Box 350605024645]
AG_PDF_UP --> AG_SCORE_RUN[Run creativex_scoring_storing.py]
AG_SCORE_RUN --> AG_SCORE_DB[Scores stored in database
status='active']
AG_SCORE_DB --> AG_VERIFY[Verify: derivative count = score count ✅]
AG_VERIFY --> AG_NAMING[Use Naming Tool for EACH derivative
Copy exact filename]
AG_NAMING --> AG_UPLOAD[Upload to Box A2→A3 folder
With job number + tracking ID]
end
subgraph A2toA3["PHASE 4: A2→A3 Derivative Upload (AUTOMATED - Every 5 min)"]
AG_UPLOAD -->|Script detects files| A2_DETECT[a2_to_a3_upload_polling.py]
A2_DETECT --> A2_PARSE[Parse filename: Extract tracking ID]
A2_PARSE --> A2_LOOKUP[Load master metadata from database]
A2_LOOKUP --> A2_CX_LOOKUP[Lookup CreativeX score by filename]
A2_CX_LOOKUP --> A2_CX_CHECK{Score found?}
A2_CX_CHECK -->|Yes| A2_CX_USE[Score: 71, URL: actual]
A2_CX_CHECK -->|No| A2_CX_DEFAULT[⚠️ Score: 0, URL: placeholder
Email warning sent]
A2_CX_USE --> A2_BUILD[Build asset representation]
A2_CX_DEFAULT --> A2_BUILD
A2_BUILD --> A2_OVERRIDE[Override from filename:
Language, Asset Type, Description]
A2_OVERRIDE --> A2_MAP[Map asset type: EHI → heroimage]
A2_MAP --> A2_STRIP[Strip to clean filename]
A2_STRIP --> A2_UPLOAD[Upload to DAM Final Assets]
A2_UPLOAD --> A2_DELETE[Delete from Box]
A2_DELETE --> A2_EMAIL[Email: Upload successful]
A2_EMAIL --> A2_STATUS[Campaign status: A3]
end
subgraph Approval["PHASE 5: Approval Process"]
A2_STATUS --> A3_REVIEW[Legal/Compliance/Brand review in DAM]
A3_REVIEW --> A3_CHECK[Check quality, compliance, guidelines]
A3_CHECK --> A3_DECISION{Approved?}
end
subgraph Success["✅ APPROVED PATH"]
A3_DECISION -->|✅ All checks passed| A4_STATUS[Status updated to A4]
A4_STATUS --> A4_WEBHOOK[Webhook sent to Make.com]
A4_WEBHOOK --> A4_LIVE[Assets go LIVE]
A4_LIVE --> A4_DONE[🎉 Campaign Complete - Assets in market]
end
subgraph Rejection["❌ REJECTED PATH - Rework Required"]
A3_DECISION -->|❌ Issues found| A5_MARK[Approver marks specific assets:
ECOMMERCE STATUS = NOT APPROVED]
A5_MARK --> A5_LEGAL[Legal reviewer adds comments:
Missing copyright, compliance issues]
A5_LEGAL --> A5_IACC[IA&CC reviewer adds comments:
Brand guideline violations]
A5_IACC --> A5_APPROVER[General approver adds comments:
Creative feedback]
A5_APPROVER --> A5_STATUS[Campaign status → A5]
end
subgraph A5toA6["PHASE 6: A5→A6 Rejection Download (AUTOMATED)"]
A5_STATUS -->|Script detects A5| A5_DETECT[a5_to_a6_download.py]
A5_DETECT --> A5_FILTER[🔍 Filter: Download ONLY
NOT APPROVED assets]
A5_FILTER --> A5_APPROVED[✅ APPROVED assets: Skipped
Not re-downloaded]
A5_FILTER --> A5_TRACKING[Lookup EXISTING tracking ID
REUSE: pOiJ9s]
A5_TRACKING --> A5_DOWNLOAD[Download rejected assets from DAM]
A5_DOWNLOAD --> A5_BOX_UP[Upload to Box A5→A6 folder
Separate folder from A1→A2]
A5_BOX_UP --> A5_EMAIL_OUT[Email with ALL rejection comments:
Legal + IA&CC + Approver feedback]
A5_EMAIL_OUT --> A5_AGENCY[Agency receives detailed feedback]
end
subgraph Rework["PHASE 7: Agency Fixes + Re-upload"]
A5_AGENCY --> RW_DOWNLOAD[Download rejected assets from Box]
RW_DOWNLOAD --> RW_REVIEW[Review ALL rejection comments]
RW_REVIEW --> RW_FIX[Fix issues based on feedback]
RW_FIX --> RW_CREATIVEX[🔴 Re-submit to CreativeX
MUST get new score for fixed version]
RW_CREATIVEX --> RW_PDF[Upload new PDF with updated score]
RW_PDF --> RW_SCORE_RUN[Run creativex_scoring_storing.py]
RW_SCORE_RUN --> RW_DB[Database updated with new score
Old score marked 'superseded']
RW_DB --> RW_NAMING[Use Naming Tool:
NEW job number + SAME tracking ID]
RW_NAMING --> RW_EXAMPLE[Example: 7777_NUT_SUMMER_OLV_30S_16x9_DE_de_pOiJ9s.mp4
↑ New job | Same tracking ↑]
RW_EXAMPLE --> RW_UPLOAD_A2[Upload to A2→A3 folder]
RW_UPLOAD_A2 -.->|Re-enters A2→A3 automation| A2_DETECT
end
subgraph ReworkLoop["🔄 Rework Can Repeat Multiple Times"]
RW_NOTE1[Rejected again? → Back to A5] -.-> A5_STATUS
RW_NOTE2[Assets can go through A5→A6
cycle 2, 3, 4+ times until approved]
end
style A4_DONE fill:#4CAF50,stroke:#2E7D32,stroke-width:4px,color:#FFF
style A5_MARK fill:#EF5350,stroke:#C62828,stroke-width:3px
style RW_CREATIVEX fill:#9C27B0,stroke:#6A1B9A,stroke-width:3px
style AG_CREATIVEX fill:#9C27B0,stroke:#6A1B9A,stroke-width:3px
style A1_TRACKING fill:#2196F3,stroke:#1565C0,stroke-width:2px
style A5_TRACKING fill:#2196F3,stroke:#1565C0,stroke-width:2px
style RW_NOTE1 fill:#FFE082,stroke:#F57C00
style RW_NOTE2 fill:#FFE082,stroke:#F57C00
```
**🔴 CRITICAL REQUIREMENTS HIGHLIGHTED:**
1. **CreativeX Scoring (Purple):** Every derivative MUST be scored individually
2. **Tracking IDs (Blue):** Critical link between master and derivatives, reused in rework
3. **Rejection Comments (Red):** Legal, IA&CC, and approver feedback guide fixes
4. **Rework Loop (Orange):** Can repeat until approved - same tracking ID maintained
---
## Table of Contents
1. [Executive Overview](#executive-overview)
2. [System Architecture](#system-architecture)
3. [The Complete Asset Lifecycle](#the-complete-asset-lifecycle)
4. [The Naming Convention Tool](#the-naming-convention-tool)
5. [Python Automation Scripts](#python-automation-scripts)
6. [Detailed Workflow Walkthroughs](#detailed-workflow-walkthroughs)
7. [CreativeX Integration](#creativex-integration)
8. [The Database](#the-database)
9. [Common Mistakes & Pitfalls](#common-mistakes--pitfalls)
10. [Monitoring & Health Checks](#monitoring--health-checks)
11. [Troubleshooting Guide](#troubleshooting-guide)
12. [Appendices](#appendices)
---
## Executive Overview
### What is This System?
The Ferrero DAM Asset Production Workflow is an **end-to-end automated system** that manages digital assets from creation through localization to final delivery. It connects creative development, digital asset management (DAM), agency collaboration, and quality scoring into a seamless automated pipeline.
**In Simple Terms:**
Think of it as a smart conveyor belt for your digital assets. Assets enter at one end (campaign creation), get automatically processed, tracked, localized by agencies, quality-scored, and delivered at the other end - with minimal manual intervention and complete audit trails.
### The Problem It Solves
**Before Automation:**
- ❌ Manual file transfers between systems
- ❌ Lost tracking between master and derivative assets
- ❌ No visibility into localization progress
- ❌ Metadata errors and inconsistencies
- ❌ Hours spent on file organization
- ❌ Missing or incorrect quality scores
- ❌ No audit trail of changes
**After Automation:**
- ✅ Automated downloads and uploads
- ✅ Every derivative linked to master via tracking ID
- ✅ Real-time progress tracking via email
- ✅ Consistent metadata across all assets
- ✅ Automatic file organization
- ✅ AI-powered quality scoring integration
- ✅ Complete history in database
### Key Benefits
**Time Savings:**
- Manual download/upload: 2 hours per campaign → **30 seconds** (automated)
- Metadata entry: 15 minutes per asset → **0 seconds** (inherited from master)
- Tracking derivatives: 30 minutes → **instant** (database lookup)
- Quality score attachment: Manual entry → **automatic** (AI extraction)
**Error Reduction:**
- Metadata errors: 15-20% → **<1%** (automated validation)
- Lost files: Common → **impossible** (database tracking)
- Wrong file versions: Frequent → **eliminated** (tracking IDs)
**Visibility:**
- Campaign status: Unknown → **real-time** (email + database)
- Asset history: None → **complete audit trail** (database events)
- Quality metrics: Scattered → **centralized** (database + reports)
### System Components
```mermaid
graph TB
subgraph Frontend["User-Facing Tools"]
NT[Naming Convention Tool
public-v2/]
BOX[Box.com Folders
4 separate folders]
EMAIL[Email Notifications
Success/Error/Reports]
end
subgraph Backend["Automation Layer"]
A1A2[a1_to_a2_download.py
Master Downloads]
A2A3[a2_to_a3_upload_polling.py
Derivative Uploads]
A5A6[a5_to_a6_download.py
Rejections/Rework]
B1B2[b1_to_b2_download.py
Global Masters]
CX[creativex_scoring_storing.py
Score Extraction]
DR[daily_report.py
Reporting]
end
subgraph Storage["Data Layer"]
DAM[(OpenText DAM
Asset Repository)]
DB[(PostgreSQL
Tracking Database)]
BACKUP[(Daily Backups
7-day retention)]
end
subgraph External["External Services"]
LLAMA[LlamaExtract AI
CreativeX PDF parsing]
WEBHOOK[Make.com Webhooks
Campaign notifications]
end
NT -.->|Generates| BOX
BOX <-->|Files| A1A2
BOX <-->|Files| A2A3
BOX <-->|Files| A5A6
BOX <-->|Files| B1B2
BOX <-->|PDFs| CX
DAM <-->|Assets + Metadata| A1A2
DAM <-->|Assets + Metadata| A2A3
DAM <-->|Assets + Metadata| A5A6
DAM <-->|Assets + Metadata| B1B2
A1A2 --> DB
A2A3 --> DB
A5A6 --> DB
B1B2 --> DB
CX --> DB
DR --> DB
CX <-->|API| LLAMA
A2A3 -.->|Status updates| WEBHOOK
A1A2 -.->|Notifications| EMAIL
A2A3 -.->|Notifications| EMAIL
A5A6 -.->|Notifications| EMAIL
B1B2 -.->|Notifications| EMAIL
CX -.->|Notifications| EMAIL
DR -.->|Daily summary| EMAIL
DB -.->|Automated| BACKUP
```
### Stakeholders & Their Roles
| Role | Responsibility | Tools Used |
|------|----------------|------------|
| **Campaign Manager** | Creates campaigns, monitors progress | DAM, Email notifications |
| **Creative Team** | Creates master assets, uploads to DAM | DAM |
| **Agency (Oliver)** | Localizes assets, uses naming tool | Naming Tool, Box |
| **Approvers** | Reviews assets, approves/rejects | DAM |
| **Operations** | Monitors system health, troubleshoots | Logs, Database, Email |
| **Technical Team** | Maintains scripts, database, backups | All systems |
### Success Metrics
**Current Performance (Week 1):**
- Campaigns processed: 50+ per week
- Assets processed: 500+ per week
- Success rate: 98.5%
- Average processing time: 5 minutes (vs 2 hours manual)
- Email notifications: 100% delivery
- Database backups: 7/7 successful
---
## System Architecture
### High-Level Architecture
The system consists of **5 core layers** that work together:
```mermaid
graph TB
subgraph L1["Layer 1: User Interface"]
UI1[DAM Web Interface
Campaign & Asset Management]
UI2[Naming Convention Tool
Filename Generation]
UI3[Box.com Interface
File Upload/Download]
end
subgraph L2["Layer 2: Automation Scripts (Python)"]
S1[Workflow Scripts
A1→A2, A2→A3, A5→A6, B1→B2]
S2[Support Scripts
CreativeX, Daily Reports]
S3[Shared Modules
DAM Client, Box Client, Database]
end
subgraph L3["Layer 3: Data Storage"]
D1[(PostgreSQL Database
Tracking & Metadata)]
D2[(Box Storage
File Transfer)]
D3[(DAM Storage
Asset Repository)]
end
subgraph L4["Layer 4: External Services"]
E1[LlamaExtract AI
PDF Parsing]
E2[Mailgun SMTP
Email Delivery]
E3[Make.com
Webhook Automation]
end
subgraph L5["Layer 5: Infrastructure"]
I1[Cron Scheduler
Every 5 minutes]
I2[Backup System
Daily + Weekly]
I3[Log Management
Rotating Logs]
end
UI1 <--> S1
UI2 --> UI3
UI3 <--> S1
S1 <--> D1
S1 <--> D2
S1 <--> D3
S2 <--> D1
S2 <--> D2
S2 <--> E1
S1 -.-> E2
S1 -.-> E3
I1 -.-> S1
I1 -.-> S2
I2 -.-> D1
```
### Physical Deployment
**Server Location:** `/opt/ferrero-automation/Python-Version/`
**Key Directories:**
```
/opt/ferrero-automation/
├── Box-config.json # Box JWT authentication (one level up!)
└── Python-Version/
├── .env # Credentials (NOT in Git)
├── config/
│ ├── config.yaml # Main configuration
│ ├── field_mappings.yaml # Metadata field mappings
│ ├── asset_type_mappings.yaml # 3-letter → DAM codes
│ └── certificates/ # mTLS certificates
├── scripts/
│ ├── a1_to_a2_download.py
│ ├── a2_to_a3_upload_polling.py
│ ├── a5_to_a6_download.py
│ ├── b1_to_b2_download.py
│ ├── creativex_scoring_storing.py
│ ├── daily_report.py
│ └── shared/ # Shared modules
├── database/
│ ├── init.sql # Database schema
│ ├── backup.sh # Backup script
│ ├── restore.sh # Restore script
│ └── check_backups.sh # Health check
├── backups/
│ ├── dumps/ # Daily SQL dumps (7 days)
│ └── basebackups/ # Weekly binary (1 backup)
├── logs/ # Rotating log files
└── temp/ # Temporary downloads
```
### Data Flow Overview
```mermaid
flowchart LR
subgraph Creation["1. Creation"]
BRIEF[Campaign Brief]
CREATIVE[Creative Assets]
DAM_A1[DAM Status: A1]
end
subgraph Download["2. Master Download"]
DETECT[Detect A1 Campaign]
DL[Download Masters]
TID[Generate Tracking IDs]
BOX_UP[Upload to Box]
DB_STORE[Store in Database]
end
subgraph Localization["3. Agency Localization"]
NAMING[Use Naming Tool]
LOCALIZE[Create Derivatives]
BOX_A2A3[Upload to Box A2→A3]
end
subgraph Upload["4. Derivative Upload"]
PARSE[Parse Filename]
LOOKUP[Database Lookups]
CREATIVEX[Attach CreativeX Score]
DAM_UP[Upload to DAM]
DAM_A3[Status: A3]
end
subgraph Approval["5. Review"]
APPROVE{Approval?}
A4[A4: Go Live]
A5[A5: Rejected]
end
subgraph Rework["6. Rework (if rejected)"]
A5A6[Download Rejections]
FIX[Agency Fixes]
REUP[Re-upload]
end
BRIEF --> CREATIVE --> DAM_A1
DAM_A1 --> DETECT --> DL --> TID --> BOX_UP --> DB_STORE
DB_STORE --> NAMING --> LOCALIZE --> BOX_A2A3
BOX_A2A3 --> PARSE --> LOOKUP --> CREATIVEX --> DAM_UP --> DAM_A3
DAM_A3 --> APPROVE
APPROVE -->|Yes| A4
APPROVE -->|No| A5
A5 --> A5A6 --> FIX --> REUP
REUP --> BOX_A2A3
```
---
## The Complete Asset Lifecycle
### Overview: From Brief to Live Asset
The journey of a Ferrero digital asset involves **6 major phases** and can take anywhere from 2 days (smooth approval) to 2 weeks (multiple rejection cycles).
### Phase 1: Campaign Creation & Master Asset Development
**Who:** Campaign Manager + Creative Team
**Where:** OpenText DAM
**Duration:** Varies (creative development time)
**Steps:**
1. **Campaign Brief Created**
- Campaign Manager creates campaign in DAM
- Assigns campaign number (e.g., C000000078)
- Sets campaign type: "Local" or "Global comm"
- Defines brand, market, fiscal year
2. **Master Assets Created**
- Creative team develops assets (videos, images, documents)
- Assets uploaded to DAM campaign folder
- Placed in "01. Master Assets" subfolder
- Metadata added (brand, language, description, etc.)
3. **Campaign Status Set to A1**
- Campaign Manager marks campaign ready
- Status change triggers automation
- System begins monitoring for this campaign
**Folder Structure in DAM:**
```
Campaign: C000000078-Summer_Promo/
├── 01. Master Assets/ ← Masters go here
│ ├── nutella_summer_hero.mp4
│ ├── nutella_summer_logo.png
│ └── Subfolder_EU/
│ └── nutella_eu_variant.mp4
└── 02. Final Assets/ ← Derivatives will go here later
```
**What Happens Next:**
- Automation detects campaign within 5 minutes
- A1→A2 script begins processing
- Campaign Manager receives email when masters are ready in Box
---
### Phase 2: Master Asset Download (A1→A2) - AUTOMATED
**Who:** Python Script (a1_to_a2_download.py)
**Trigger:** Campaign status = A1
**Frequency:** Every 5 minutes
**Duration:** 2-10 minutes depending on file sizes
```mermaid
sequenceDiagram
autonumber
participant DAM as OpenText DAM
participant Script as a1_to_a2_download.py
(Runs every 5 min)
participant Box as Box.com
participant DB as PostgreSQL Database
participant Email as Email Notification
participant CX as CreativeX Extraction
Note over Script: Cron triggers every 5 minutes
Script->>DAM: Query: Get campaigns WHERE status = 'A1'
DAM-->>Script: Campaign list (may be empty)
alt No A1 campaigns found
Script->>Script: Log "No A1 campaigns found" (normal)
Script->>Script: Exit cleanly
end
alt A1 campaign(s) found
Script->>DAM: Get campaign details
Script->>DAM: Search "01. Master Assets" folder (recursive)
DAM-->>Script: List of master assets + metadata
loop For each asset
Script->>DAM: Download asset file
Script->>Script: Generate unique 6-char tracking ID
Script->>Box: Upload as "filename_TRACKINGID.ext"
Script->>DB: Store master_assets record
Script->>DB: Store full_metadata as JSONB
Script->>CX: Extract CreativeX score (if present)
alt CreativeX found in metadata
CX->>DB: Store with status='master-cx-score'
CX->>Script: Log "Stored master score"
else No CreativeX in metadata
CX->>Script: Log "No CreativeX (normal)"
end
Script->>Script: Track success
end
alt ALL assets successful
Script->>DAM: Update campaign status A1 → A2
Script->>DB: Record campaign_status
Script->>Email: Send success notification
Email-->>Team: "3 master assets ready for localization"
else Some assets failed
Script->>Email: Send partial success notification
Email-->>Team: "2/3 successful, 1 failed"
Note over Script,DAM: Status NOT updated
Retry on next run
end
end
```
**Detailed Process Breakdown:**
**Step 1-2: Campaign Detection**
```
Query: SELECT * FROM campaigns WHERE status = 'A1' LIMIT 1
Result: Campaign C000000078 found
Log: "Processing campaign: Summer_Promo (C000000078)"
```
**Step 3-4: Asset Discovery**
```
Search: Campaign/01. Master Assets/ (recursive)
Found:
- nutella_summer_hero.mp4 (50MB)
- nutella_summer_logo.png (2MB)
- Subfolder_EU/nutella_eu_variant.mp4 (45MB)
Total: 3 assets
```
**Step 5-6: Tracking ID Generation**
```
Algorithm: Random 6 alphanumeric characters
Validation: Check uniqueness in database
Generated IDs:
- pOiJ9s (for nutella_summer_hero.mp4)
- a7K9mP (for nutella_summer_logo.png)
- bvSGyd (for nutella_eu_variant.mp4)
```
**Step 7: Box Upload**
```
Original: nutella_summer_hero.mp4
Box name: nutella_summer_hero_pOiJ9s.mp4
Box folder: 348304357505 (A1→A2 Local Masters)
Subfolder: C000000078-Summer_Promo/
URL: https://app.box.com/file/123456789
```
**Step 8-9: Database Storage**
```sql
INSERT INTO master_assets (
tracking_id, opentext_id, original_filename,
full_metadata, upload_directory, ...
) VALUES (
'pOiJ9s',
'747110b96dede761a445a3bf0c90f13bb38024fa',
'nutella_summer_hero',
'{"name": "nutella_summer_hero.mp4", "metadata": {...}}', -- Full JSONB
'12345', -- Final Assets folder ID
...
);
```
**Step 10-11: CreativeX Extraction (if present)**
```
Search metadata for: FERRERO.TAB.FIELD.CREATIVEX
Found: Score = 85, URL = https://app.creativex.com/...
Store:
INSERT INTO creativex_scores (
filename, quality_score, creativex_url,
tracking_id, status
) VALUES (
'nutella_summer_hero.mp4', '85', 'https://...',
'pOiJ9s', 'master-cx-score'
);
Log: "Stored master CreativeX score: 85 (Tracking: pOiJ9s)"
```
**Step 12: Status Update**
```
Check: All 3 assets successful? YES
Update: Campaign status A1 → A2
Record: campaign_status table (prevents re-processing)
```
**Step 13: Email Notification**
```
To: ferrero-team@ferrero.com, operations@oliver.agency
Subject: ✅ Master Assets Downloaded - Campaign Summer_Promo
Body:
- Campaign: Summer_Promo (C000000078)
- Assets Downloaded: 3
- Status Updated: A1 → A2
Processed Assets:
1. nutella_summer_hero.mp4
Tracking ID: pOiJ9s
Box URL: https://app.box.com/file/123456789
CreativeX Score: 85 (master)
2. nutella_summer_logo.png
Tracking ID: a7K9mP
Box URL: https://app.box.com/file/123456790
3. nutella_eu_variant.mp4 (from Subfolder_EU/)
Tracking ID: bvSGyd
Box URL: https://app.box.com/file/123456791
```
**What If Something Fails?**
**Scenario: Network timeout on asset 2**
```
Asset 1: ✅ Success (pOiJ9s)
Asset 2: ❌ Network timeout
Asset 3: ⏭️ Not attempted (stop on error for safety)
Result:
- Status NOT updated (still A1)
- Email sent: "Partial Download - 1/3 successful"
- Next run (5 minutes): Retries asset 2 and 3
- Eventually all succeed → Status updated to A2
```
**Why Status Update is All-or-Nothing:**
- Prevents partial state in DAM
- Clear definition: A2 means "ALL masters in Box"
- Easier troubleshooting (either all done or not done)
- Safer for downstream processes
---
### Phase 3: Agency Localization
**Who:** Agency (Oliver) Creative Team
**Where:** Box + Naming Convention Tool
**Duration:** 1-5 days depending on complexity
**Steps:**
**Step 1: Receive Notification**
Agency receives email:
```
Subject: ✅ Master Assets Downloaded - Campaign Summer_Promo
Your master assets are ready for localization:
- nutella_summer_hero_pOiJ9s.mp4 (Box URL)
- nutella_summer_logo_a7K9mP.png (Box URL)
- nutella_eu_variant_bvSGyd.mp4 (Box URL)
Next steps:
1. Download masters from Box
2. Create localized versions
3. Use Naming Tool to generate filenames
4. Upload to Box A2→A3 folder: [URL]
```
**Step 2: Download Masters from Box**
Agency clicks Box URLs in email, downloads files
**Step 3: Create Localized Versions**
For each market/language:
- German version of hero video
- French version of hero video
- Italian version of hero video
- etc.
**Step 4: Use Naming Convention Tool**
**CRITICAL:** This is where the naming tool becomes essential!
Agency opens: `http://naming-tool-url/public-v2/index.php`
**For EACH localized file, agency enters:**
- OMG Job Number: `6487512` (from their project management)
- Brand: `NUT` (Nutella)
- Subject: `SUMMER` (from brief)
- Asset Type: `OLV` (Online Video) → Maps to "onlinevideodigitalvideo" in DAM
- Duration: `30` (30 seconds)
- Aspect Ratio: `16x9`
- Spot Version: `MST` (Master version)
- Country: `DE` (Germany)
- Language: `de` (German)
- Social Media: `IGF` (if Instagram Feed version)
- Tracking ID: `pOiJ9s` (from master asset filename)
**Tool Generates:**
```
6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_IGF_pOiJ9s.mp4
```
**Why Each Field Matters:**
| Field | Purpose | What Happens If Wrong |
|-------|---------|----------------------|
| **Job Number** | Links to OMG project billing | Can't track project costs |
| **Brand** | Metadata inheritance, organization | Wrong brand in DAM |
| **Subject** | Description field in DAM | Searchability issues |
| **Asset Type** | Critical for DAM categorization | Upload may fail or wrong type |
| **Duration** | Asset specifications | Optional, info only |
| **Aspect Ratio** | Asset specifications | Required for videos/images |
| **Spot Version** | Master vs Reference designation | Classification |
| **Country** | Market targeting | Wrong market metadata |
| **Language** | Language metadata in DAM | Wrong language = unusable |
| **Social Media** | Platform-specific metadata | Platform targeting |
| **Tracking ID** | Links to master (DATABASE LOOKUP) | **Upload fails, can't find master** |
**Step 5: Upload to Box**
- Agency uploads to Box folder: 348526703108
- Uses EXACT filename from naming tool
- One file at a time or batch upload
**What Automation Sees:**
```
Box folder poll finds:
- 6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_IGF_pOiJ9s.mp4
- 6487512_NUT_SUMMER_OLV_30S_16x9_MST_FR_fr_IGF_pOiJ9s.mp4
- 6487512_NUT_SUMMER_OLV_30S_16x9_MST_IT_it_IGF_pOiJ9s.mp4
Validation:
✅ All filenames parse correctly
✅ All tracking IDs found in database
✅ Ready for upload
```
---
### Phase 4: Derivative Asset Upload (A2→A3) - AUTOMATED
**Who:** Python Script (a2_to_a3_upload_polling.py)
**Trigger:** Files appear in Box A2→A3 folder
**Frequency:** Every 5 minutes
**Duration:** 1-3 minutes per file
```mermaid
sequenceDiagram
autonumber
participant Agency
participant Box as Box A2→A3 Folder
participant Script as a2_to_a3_upload_polling.py
participant Parser as Filename Parser V2.1
participant DB as Database
participant Mapper as Asset Type Mapper
participant DAM
participant Email
Agency->>Box: Upload 6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_IGF_pOiJ9s.mp4
Note over Script: Cron runs every 5 minutes
Script->>Box: List files in folder
Box-->>Script: Found 1 file
Script->>Parser: Parse filename
Parser->>Parser: Split by underscore
Parser->>Parser: Position 1: Job = 6487512
Parser->>Parser: Position 2: Brand = NUT
Parser->>Parser: Position 3: Subject = SUMMER
Parser->>Parser: Position 4: Asset = OLV
Parser->>Parser: Position 5: Duration = 30S
Parser->>Parser: Position 6: Ratio = 16x9
Parser->>Parser: Position 7: Spot = MST
Parser->>Parser: Position 8: Country = DE
Parser->>Parser: Position 9: Language = de
Parser->>Parser: Position 10: Social = IGF
Parser->>Parser: Position 11: Tracking = pOiJ9s
Parser-->>Script: Parsed components
Script->>DB: SELECT * FROM master_assets WHERE tracking_id = 'pOiJ9s'
DB-->>Script: Master metadata (brand, campaign, folders, etc.)
Script->>DB: SELECT * FROM creativex_scores WHERE filename = '6487512_NUT...'
alt CreativeX score found
DB-->>Script: Score: 85, URL: https://creativex...
Script->>Script: creativex_found = True
else No score found
Script->>Script: Use defaults (Score: 0, placeholder URL)
Script->>Script: creativex_found = False
end
Script->>Script: Build asset representation
Script->>Script: Merge master metadata + filename overrides
Script->>Script: Override: Language = de (from filename)
Script->>Script: Override: Description = SUMMER (from filename)
Script->>Mapper: Map asset type: OLV
Mapper-->>Script: onlinevideodigitalvideo
Script->>Script: Set FERRERO.FIELD.MKTG.ASSET TYPE = onlinevideodigitalvideo
Script->>Script: Set STATE = Local (forced)
Script->>Script: Set AGENCY NAME = Oliver
Script->>Script: Add CreativeX Score = 85
Script->>Script: Add CreativeX URL = https://...
Script->>Script: Strip filename: NUT_SUMMER_OLV_30S_16x9_MST_DE_de_IGF.mp4
Script->>DAM: Upload to Final Assets folder
DAM-->>Script: Asset ID: xyz789
Script->>DB: INSERT INTO derivative_assets
Script->>Box: Delete processed file
alt CreativeX was found
Script->>Email: Success email (green, shows Score: 85)
else CreativeX defaulted
Script->>Email: Success email (orange warning box)
end
Email-->>Agency: "Asset uploaded successfully"
```
**Detailed Process:**
**Input File:**
```
6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_IGF_pOiJ9s.mp4
```
**Parsing (V2.1 Structure):**
```python
{
'omg_job_number': '6487512',
'brand_code': 'NUT',
'subject_title': 'SUMMER',
'asset_type': 'OLV',
'seconds': '30',
'aspect_ratio': '16x9',
'spot_version': 'MST',
'country_code': 'DE',
'language_code': 'de',
'social_media_version': 'IGF',
'tracking_id': 'pOiJ9s',
'tracking_mode': 'full' # Full metadata inheritance
}
```
**Database Lookups:**
**Lookup 1: Master Asset**
```sql
SELECT * FROM master_assets
WHERE tracking_id = 'pOiJ9s'
```
Returns: Complete master metadata, upload folder ID, campaign info
**Lookup 2: CreativeX Score**
```sql
SELECT * FROM creativex_scores
WHERE filename = '6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_IGF_pOiJ9s.mp4'
AND status = 'active'
```
Returns: Score: 85, URL: https://app.creativex.com/... (or None if not found)
**Metadata Building:**
**From Master (Inherited):**
- Brand Name: "Nutella"
- Brand Code: "NUT"
- Campaign ID: "C000000078"
- Campaign Name: "Summer_Promo"
- Upload Folder: Final Assets folder ID
- All other metadata fields (fiscal year, tags, etc.)
**From Filename (Overrides):**
- Language: `de` (German) - OVERRIDES master
- Country: `DE` (Germany)
- Description: `SUMMER` - OVERRIDES master
- Asset Type: `OLV` → mapped to `onlinevideodigitalvideo`
**Forced Values:**
- STATE: `Local` (always for A2→A3)
- AGENCY NAME: `Oliver`
**From Database (CreativeX):**
- CreativeX Score: `85` (or `0` if not found)
- CreativeX URL: `https://app.creativex.com/...` (or placeholder)
**Asset Type Mapping:**
```
Filename has: OLV
Mapping: OLV → onlinevideodigitalvideo
DAM receives: "onlinevideodigitalvideo" (valid domain value)
```
**Filename Stripping:**
```
Input: 6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_IGF_pOiJ9s.mp4
Remove: Job number (6487512)
Remove: Tracking ID (pOiJ9s)
Output: NUT_SUMMER_OLV_30S_16x9_MST_DE_de_IGF.mp4
```
**DAM Upload:**
```
Upload to: Campaign/02. Final Assets/
Filename: NUT_SUMMER_OLV_30S_16x9_MST_DE_de_IGF.mp4
Metadata: 27 MVP fields populated
Result: Asset ID xyz789
```
**Database Record:**
```sql
INSERT INTO derivative_assets (
tracking_id, derivative_filename, dam_asset_id
) VALUES (
'pOiJ9s',
'NUT_SUMMER_OLV_30S_16x9_MST_DE_de_IGF',
'xyz789'
);
```
**Email Notification:**
```
Subject: ✅ Asset Uploaded to DAM - NUT_SUMMER_OLV_30S_16x9_MST_DE_de_IGF.mp4
Original Filename: 6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_IGF_pOiJ9s.mp4
Clean Filename: NUT_SUMMER_OLV_30S_16x9_MST_DE_de_IGF.mp4
DAM Asset ID: xyz789
Tracking ID: pOiJ9s
What Was Done:
✅ Downloaded from Box
✅ Loaded master metadata (pOiJ9s)
✅ Built asset representation (27 fields)
✅ Updated Language from filename: de
✅ Updated Description: SUMMER
✅ Set STATE to Local
✅ CreativeX Score Added: 85 (from database) ← If found
✅ Uploaded to DAM Final Assets folder
✅ Deleted from Box
```
**If CreativeX NOT Found:**
```
⚠️ CreativeX Score Missing
No score found for: 6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_IGF_pOiJ9s.mp4
Default Values Used:
- Score: 0
- URL: https://app.creativex.com/preflight/pretests
To add CreativeX score: Upload PDF report to Box folder 350605024645
and run creativex_scoring_storing.py
```
---
### Phase 5: Approval & Distribution (A3→A4)
**Who:** Approvers (Ferrero stakeholders)
**Where:** DAM
**Duration:** 1-3 days
**Process:**
1. Assets reach A3 status after upload
2. Designated approvers review in DAM
3. Check quality, compliance, brand guidelines
4. Decision: Approve or Reject
**If Approved:**
- Status updated to A4
- Assets ready for distribution
- Campaign complete
**If Rejected:**
- Mark individual assets: `ECOMMERCE STATUS = "NOT APPROVED"`
- Add rejection comments (why rejected)
- Campaign status → A5
- Triggers rejection workflow (see Phase 6)
---
### Phase 6: Rejection & Rework Cycle (A5→A6→A2→A3)
**Who:** Python Script + Agency
**Trigger:** Campaign status = A5 + assets marked "NOT APPROVED"
**Duration:** 1-3 days for rework + 5 minutes for automation
```mermaid
flowchart TD
A[Asset in A3] --> B{Approver Review}
B -->|Approved| C[A4: Go Live ✅]
B -->|Rejected| D[Mark: ECOMMERCE STATUS = NOT APPROVED]
D --> E[Add Comments:
Legal/IA&CC/Approver]
E --> F[Campaign Status → A5]
F -->|5 min| G[a5_to_a6_download.py detects A5]
G --> H[Query: Get ONLY NOT APPROVED assets]
H --> I{Asset has tracking ID?}
I -->|Yes| J[Reuse existing tracking ID]
I -->|No| K[Generate new tracking ID]
J --> L[Download from DAM]
K --> L
L --> M[Upload to Box A5→A6 folder]
M --> N[Email with rejection reasons]
N --> O[Agency receives notification]
O --> P[Agency reviews comments]
P --> Q[Agency fixes issues]
Q --> R[Use Naming Tool with SAME tracking ID]
R --> S[Upload to Box A2→A3 folder]
S -->|Standard A2→A3 flow| T[a2_to_a3 processes]
T --> U[Upload to DAM]
U --> V[Overwrites previous version]
V --> W[Back to A3 status]
W --> X{Approval round 2}
X -->|Approved| Y[A4: Done ✅]
X -->|Rejected again| Z[Back to A5]
Z -.->|Cycle repeats| G
style C fill:#90EE90
style Y fill:#90EE90
style D fill:#FFB6C1
style Z fill:#FFB6C1
```
**A5→A6: Rejection Download (AUTOMATED)**
**Critical Filter:**
```python
# ONLY download assets with ECOMMERCE STATUS = "NOT APPROVED"
# Do NOT re-download approved assets
approved_assets_skipped = assets_with_status_approved
rejected_assets = assets_with_status_not_approved
```
**Tracking ID Reuse Logic:**
```python
# Critical: Use existing tracking ID to maintain link
result = db.find_or_create_tracking_id(
opentext_id=asset_id,
local_campaign_id=campaign_number
)
if result['is_existing']:
tracking_id = result['tracking_id'] # Reuse existing
logger.info("Reusing tracking ID for rework: {}".format(tracking_id))
else:
tracking_id = result['tracking_id'] # New ID
logger.info("New asset, generated tracking ID: {}".format(tracking_id))
```
**Why Tracking ID Reuse Matters:**
- Links rework to original master
- Preserves metadata inheritance
- Enables version tracking
- Maintains audit trail
**Rejection Comment Extraction:**
```python
# Extract 3 types of rejection comments
comments = {
'approver': "Logo too small, add disclaimer",
'legal': "Missing copyright notice",
'ia_cc': "Brand colors not compliant"
}
# Included in email to agency
```
**Email Notification:**
```
Subject: ⚠️ NOT APPROVED Assets - Rework Required - Campaign Summer_Promo
Assets requiring rework: 1
Rejected Asset:
- NUT_SUMMER_OLV_30S_16x9_MST_DE_de_IGF.mp4
Tracking ID: pOiJ9s
Box URL: https://app.box.com/file/999
Rejection Comments:
📝 Approver: "Logo too small, add disclaimer"
⚖️ Legal: "Missing copyright notice"
Next Steps:
1. Download from Box
2. Fix issues based on comments
3. Use Naming Tool with SAME tracking ID: pOiJ9s
4. Upload to A2→A3 folder: [URL]
```
**Rework Process:**
**Step 1: Agency Reviews Comments**
- Downloads rejected file from Box
- Reads rejection reasons
- Plans fixes
**Step 2: Agency Fixes Asset**
- Makes required changes
- Adds disclaimer
- Fixes logo size
- Adds copyright notice
**Step 3: Re-Upload with SAME Tracking ID**
**CRITICAL:** Must use the same tracking ID!
**Naming Tool Input:**
- OMG Job: `7654321` (NEW job number, different from original)
- Brand: `NUT`
- Subject: `SUMMER`
- Asset: `OLV`
- Duration: `30`
- Ratio: `16x9`
- Spot: `MST`
- Country: `DE`
- Language: `de`
- Social: `IGF`
- **Tracking ID: `pOiJ9s`** ← SAME AS ORIGINAL
**Generated:**
```
7654321_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_IGF_pOiJ9s.mp4
↑ Different job number (new project)
↑ SAME tracking ID
```
**Step 4: A2→A3 Processes Rework**
- Parses new filename
- Finds same tracking ID: `pOiJ9s`
- Loads same master metadata
- Uploads to DAM
- **Overwrites** previous version in Final Assets
- Back to A3 for re-approval
**Rework Can Happen Multiple Times:**
```
Upload 1 → Rejected (logo issue)
Upload 2 → Rejected (missing disclaimer)
Upload 3 → Rejected (color compliance)
Upload 4 → Approved ✅
```
Each time uses the SAME tracking ID to maintain the link!
---
### Workflow 4: Global Master Campaigns (B1→B2)
**Purpose:** Distribution of global master content to regional teams
**Who:** Python Script (b1_to_b2_download.py)
**Trigger:** Campaign type = "Global comm" AND status = B1
**Frequency:** Every 5 minutes
```mermaid
flowchart TD
A[Global Campaign Created] --> B[Type = Global comm]
B --> C[Assets in Final Assets folder
NOT Master Assets]
C --> D[Status = B1]
D -->|5 min| E[b1_to_b2_download.py runs]
E --> F{Filter campaigns}
F --> G[Type = Global comm
Status = B1]
G --> H[Download from Final Assets]
H --> I[Generate tracking IDs]
I --> J[Upload to Box folder 349261192115]
J --> K[Folder name: MASTERS_CampaignName]
K --> L[Store in database]
L --> M[Email to team]
M --> N[NO webhook sent]
N --> O[Regional teams download from Box]
O --> P[Use for regional adaptations]
```
**Key Differences from A1→A2:**
| Aspect | A1→A2 (Local) | B1→B2 (Global) |
|--------|---------------|----------------|
| **Campaign Type** | Local campaigns | "Global comm" only |
| **Source Folder** | 01. Master Assets | **02. Final Assets** |
| **Box Folder** | 348304357505 | **349261192115** |
| **Folder Naming** | C000000078-Campaign_Name | **MASTERS_**Campaign_Name |
| **Webhook** | Sent on A4 | **NOT sent** |
| **Purpose** | Localization workflow | Distribution to regions |
| **Next Step** | A2→A3 (agency localization) | Regional teams download |
**Example:**
```
Campaign: C000000099-Global_Nutella_2025
Type: Global comm
Status: B1
Source: 02. Final Assets/
Download:
- global_nutella_master_final.mp4
- global_nutella_logo_final.png
Upload to Box:
Folder: MASTERS_Global_Nutella_2025/
Files:
- global_nutella_master_final_xYz123.mp4
- global_nutella_logo_final_aBc456.png
Email: "Global master assets ready for distribution"
NO webhook sent (different from local campaigns)
```
---
## The Naming Convention Tool
### Location & Purpose
**Path:** `/Users/daveporter/Python-Enviroments/Ferrero-naming-convention/public-v2/`
**URL:** `http://naming-tool-url/public-v2/index.php`
**Purpose:** Generate compliant V2.1 filenames that:
- Parse correctly in automation
- Extract metadata accurately
- Link derivatives to masters via tracking IDs
- Enable CreativeX score matching
- Comply with DAM validation rules
### Why Naming is CRITICAL
**The Automation Depends On It:**
Every Python script relies on filename parsing:
```mermaid
graph LR
A[Filename] --> B[Parser]
B --> C{Valid?}
C -->|Yes| D[Extract Metadata]
C -->|No| E[Skip File]
D --> F[Database Lookup]
F --> G[Upload to DAM]
E --> H[Stuck in Box]
H --> I[Manual Intervention]
```
**What Gets Extracted from Filename:**
| Component | Used For | Impact if Wrong |
|-----------|----------|-----------------|
| **Job Number** | Billing, project tracking | Can't link to OMG project |
| **Brand Code** | Validation, filtering | Wrong brand in DAM |
| **Subject** | Description field | Wrong description |
| **Asset Type** | **DAM categorization** | **Upload fails or wrong type** |
| **Duration** | Asset specs | Info only, optional |
| **Aspect Ratio** | Asset specs | Required for validation |
| **Spot Version** | Master/Ref designation | Classification |
| **Country** | Market targeting | **Wrong market = unusable** |
| **Language** | **Language metadata** | **Wrong language = unusable** |
| **Social Media** | Platform targeting | Platform-specific metadata |
| **Tracking ID** | **Master link** | **Can't find master = UPLOAD FAILS** |
### V2.1 Filename Structure
**Format:**
```
[JOB]_[BRAND]_[SUBJECT]_[ASSET]_[DUR]_[RATIO]_[SPOT]_[COUNTRY]_[LANG]_[SOCIAL]_[TRACKING]
1 2 3 4 5 6 7 8 9 10 11
```
**Field-by-Field Explanation:**
#### 1. Job Number (OMG Job)
- **Format:** 1-10 digits
- **Example:** `6487512`
- **Purpose:** Links to OMG project management system
- **Required:** Yes (for working files)
- **Stripped:** Yes (removed before DAM upload)
- **Why Important:** Billing, project tracking, client reporting
#### 2. Brand Code
- **Format:** 2-5 uppercase letters
- **Example:** `NUT` (Nutella), `RAF` (Raffaello), `BUEC` (Bueno Ice Cream)
- **Purpose:** Brand identification
- **Required:** Yes
- **Stripped:** No (kept in final filename)
#### 3. Subject Title
- **Format:** 1-15 characters, uppercase, hyphens allowed
- **Example:** `SUMMER`, `ME-MOMENT`, `SPREAD`, `TEST-E2E`
- **Purpose:** Becomes Description field in DAM
- **Required:** Yes
- **Stripped:** No
- **Validation:** Max 15 chars (warning if exceeded)
#### 4. Asset Type
- **Format:** Exactly 3 uppercase letters
- **Example:** `OLV`, `IMG`, `TVC`, `EHI`
- **Purpose:** Asset categorization in DAM
- **Required:** Yes
- **Mapped:** Yes - 3-letter code → DAM code (e.g., EHI → heroimage)
- **Full List:** 45 asset types (see asset_type_mappings.yaml)
**Common Asset Types:**
| Code | DAM Code | Description |
|------|----------|-------------|
| IMG | keyvisual | Image / Front of Pack |
| OLV | onlinevideodigitalvideo | Online Video |
| TVC | tvc | TV Commercial |
| EHI | heroimage | E-Comm Hero Image |
| EBS | beautyshot | E-Comm Beauty Shot |
| LOG | ferrerologo | Logo |
#### 5. Duration (Optional)
- **Format:** 1-3 digits + `S`
- **Example:** `6S`, `30S`, `120S`
- **Purpose:** Video length in seconds
- **Required:** No (omit for static assets like images)
- **Stripped:** No
#### 6. Aspect Ratio
- **Format:** Width `x` Height or Width `:` Height
- **Example:** `16x9`, `1x1`, `9x16`, `4x5`
- **Purpose:** Asset dimensions/format
- **Required:** Yes
- **Stripped:** No
#### 7. Spot Version (Optional)
- **Format:** `MST` or `REF` only
- **Example:** `MST` (Master), `REF` (Reference)
- **Purpose:** Designates master vs reference file
- **Required:** No
- **Stripped:** No
#### 8. Country Code
- **Format:** 2 uppercase letters (ISO 3166-1 alpha-2)
- **Example:** `DE` (Germany), `FR` (France), `IT` (Italy), `US` (USA)
- **Purpose:** Market targeting metadata
- **Required:** Yes
- **Stripped:** No
#### 9. Language Code
- **Format:** 2-3 lowercase letters (ISO 639)
- **Example:** `de` (German), `en` (English), `it` (Italian), `eng` (English)
- **Purpose:** Language metadata in DAM
- **Required:** Yes
- **Stripped:** No
#### 10. Social Media Version (Optional)
- **Format:** 3 uppercase letters
- **Example:** `FBP`, `FBR`, `IGF`, `IGR`
- **Purpose:** Platform-specific metadata
- **Required:** No (only for social media assets)
- **Stripped:** No
**Current Codes:**
| Code | Platform | Description |
|------|----------|-------------|
| FBP | Facebook | Facebook Post |
| FBR | Facebook | Facebook Reels |
| IGF | Instagram | Instagram Feed |
| IGR | Instagram | Instagram Reels |
**More codes will be added:** TikTok, YouTube Shorts, LinkedIn, etc.
#### 11. Tracking ID
- **Format:** 6 alphanumeric characters (case-sensitive)
- **Example:** `pOiJ9s`, `a7K9mP`, `bvSGyd`
- **Purpose:** Links derivative to master asset in database
- **Required:** Yes (for derivatives with metadata inheritance)
- **Stripped:** Yes (removed before DAM upload)
- **Special:** Can have `-N` suffix for "folder-only" mode
**Tracking ID Modes:**
**Standard Mode: `abc123`**
- Full metadata inheritance from master
- Override: Language and Asset Type from derivative
- Use for: Standard localizations
**Folder-Only Mode: `abc123-N`**
- ONLY upload folder from master
- NO metadata inheritance
- All metadata from derivative filename
- Use for: New assets in same campaign with different brand/metadata
**Example:**
```
Master: NUT_HERO_IMG_16x9_IT_it.png (tracking: abc123)
Derivative 1: 111_NUT_HERO_IMG_16x9_DE_de_abc123.png (inherits NUT brand)
Derivative 2: 222_RAF_PROMO_IMG_1x1_FR_fr_abc123-N.png (DIFFERENT brand RAF, uses folder only)
```
### Using the Naming Tool: Step-by-Step
**Step 1: Open the Tool**
```
URL: http://naming-tool-url/public-v2/index.php
```
**Step 2: Select Asset Type**
Dropdown shows all 45 asset types with descriptions:
- E-COMM: Hero Image (EHI)
- Online Video (OLV)
- TV Commercial (TVC)
- etc.
**Step 3: Fill Required Fields**
```
OMG Job Number: [6487512]
Brand: [NUT - Nutella] (dropdown)
Subject Title: [SUMMER] (15 char max)
Country: [DE - Germany] (dropdown)
Language: [de - German] (dropdown)
```
**Step 4: Fill Optional Fields (if applicable)**
```
Duration: [30] seconds (for videos only)
Aspect Ratio: [16x9] (required)
Spot Version: [MST] (dropdown: MST/REF or leave empty)
Social Media: [IGF] (dropdown: FBP/FBR/IGF/IGR or leave empty)
```
**Step 5: Enter Tracking ID**
**CRITICAL STEP:**
```
Tracking ID: [pOiJ9s]
```
**How to Get Tracking ID:**
- From A1→A2 email notification
- From Box filename (last component before extension)
- From database query
- **IMPORTANT:** Copy exactly, case-sensitive!
**Step 6: Generate Filename**
- Click "Generate Filename" button
- Tool validates all fields
- Shows any errors in red
- If valid: Shows generated filename
**Step 7: Copy Filename**
- Click "Copy to Clipboard" button
- Paste EXACTLY when saving localized file
- **Do NOT modify** - use exact generated name
**Generated Example:**
```
6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_IGF_pOiJ9s.mp4
```
### Tool Validation Rules
**The tool validates:**
- ✅ Job number is digits only
- ✅ Brand exists in domain
- ✅ Subject ≤ 15 characters
- ✅ Asset type is valid 3-letter code
- ✅ Duration format is correct (if provided)
- ✅ Aspect ratio format is valid
- ✅ Country is valid ISO code
- ✅ Language is valid ISO code
- ✅ Social media code is recognized (if provided)
- ✅ Tracking ID is exactly 6 characters
**Validation Errors Shown:**
- 🔴 "Brand code must be 2-5 characters"
- 🔴 "Subject title exceeds 15 character limit"
- 🔴 "Invalid tracking ID (must be 6 alphanumeric)"
- 🔴 "Country code must be 2 characters"
### What Happens When Naming is Wrong
**The Domino Effect of Bad Filenames:**
```mermaid
flowchart TD
A[Agency uploads file with wrong name] --> B{Filename parses?}
B -->|No| C[A2→A3 skips file]
C --> D[File stuck in Box forever]
D --> E[Agency wonders why not uploaded]
E --> F[Email sent: File invalid]
F --> G[Manual intervention required]
G --> H[Download from Box]
H --> I[Rename correctly]
I --> J[Re-upload]
J --> K[Finally processes]
B -->|Yes, but wrong tracking ID| L[Database lookup fails]
L --> M[Error: No master found]
M --> D
B -->|Yes, but wrong asset type| N[Asset type mapping fails]
N --> O[DAM rejects upload]
O --> D
B -->|Yes, but wrong country/language| P[Wrong metadata in DAM]
P --> Q[Asset uploaded but unusable]
Q --> R[Manual DAM cleanup needed]
style D fill:#FFB6C1
style Q fill:#FFB6C1
style K fill:#90EE90
```
**Real Example: Wrong Field Order (Old V1 Format)**
❌ **Incorrect (Old V1 position):**
```
6487512_NUT_DE_de_SUMMER_OLV_30S_16x9_MST_pOiJ9s.mp4
Position: 3 4 5 6
```
**What Parser Sees:**
```
Country: DE ✅ (but actually in position 3 now)
Language: de ✅ (but actually in position 4 now)
Subject: SUMMER ❌ (parser thinks this is position 5, but it's now 3)
Asset: OLV ❌ (parser thinks this is position 6, but it's now 4)
```
**Result:**
```
Parsed incorrectly:
- Subject: "DE" ❌ (wrong - it's the country!)
- Asset Type: "de" ❌ (wrong - it's the language!)
- Country: "SUMMER" ❌ (wrong - it's the subject!)
- Language: "OLV" ❌ (wrong - it's the asset type!)
Validation: FAILS
File: SKIPPED
Status: Stuck in Box
```
✅ **Correct (New V2.1 position):**
```
6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_pOiJ9s.mp4
Position: 3 4
```
**What Parser Sees:**
```
Subject: SUMMER ✅
Asset: OLV ✅
Country: DE ✅ (now in position 8)
Language: de ✅ (now in position 9)
```
**Result:**
```
Parsed correctly:
- All fields in right positions
- Validation: PASSES
- Upload: SUCCEEDS
```
**Real Example: Missing Tracking ID**
❌ **Missing tracking ID:**
```
6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de.mp4
```
**What Happens:**
```
Parse: ✅ Succeeds (structure valid)
Tracking ID: None
Database lookup: Skip (no tracking ID)
Error: "No master asset for tracking ID: None"
Upload: FAILS
Email: "Processing failed - No master asset"
File: Remains in Box
```
**Real Example: Typo in Tracking ID**
❌ **Wrong tracking ID (typo):**
```
6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_pOlJ9s.mp4
↑ Should be 'i' not 'l'
Correct ID: pOiJ9s
Typo ID: pOlJ9s
```
**What Happens:**
```
Parse: ✅ Succeeds
Tracking ID extracted: pOlJ9s
Database lookup: SELECT * WHERE tracking_id = 'pOlJ9s'
Result: NO ROWS (tracking ID doesn't exist)
Error: "No master asset for tracking ID: pOlJ9s"
Upload: FAILS
```
**Real Example: Lowercase Country Code**
❌ **Lowercase country (parser misidentifies):**
```
6487512_NUT_SUMMER_OLV_30S_16x9_MST_de_de_pOiJ9s.mp4
↑ Should be "DE" (upper)
```
**What Parser Thinks:**
```
Looking for country (2 uppercase alpha)...
Found: "de" - but it's lowercase!
Interpreted as: Language code (2-3 lowercase alpha)
Result:
- Country: NOT FOUND (missing required field)
- Language: "de" ✅ (correct)
- But TWO language codes parsed (de and de again)
Validation: May fail or produce unexpected results
```
---
## Python Automation Scripts
### Execution Schedule (Cron)
**Production Crontab:**
```cron
# Workflows run every 5 minutes
*/5 * * * * a1_to_a2_download.py >> logs/cron_a1_a2.log 2>&1
*/5 * * * * a5_to_a6_download.py >> logs/cron_a5_a6.log 2>&1
*/5 * * * * b1_to_b2_download.py >> logs/cron_b1_b2.log 2>&1
*/5 * * * * a2_to_a3_upload_polling.py >> logs/cron_a2_a3.log 2>&1
# Reports run daily at 7 PM
0 19 * * * daily_report.py >> logs/daily_report.log 2>&1
# Backups run at 2 AM daily, 3 AM Sundays
0 2 * * * database/backup.sh --daily >> logs/backup.log 2>&1
0 3 * * 0 database/backup.sh --weekly >> logs/backup.log 2>&1
```
**Why Every 5 Minutes?**
- Fast response time for users
- Manageable load on systems
- Quick recovery from temporary failures
- Users see results within minutes of upload
### Script Dependencies
```mermaid
graph TB
subgraph Workflows["Workflow Scripts"]
A1A2[a1_to_a2_download.py]
A2A3[a2_to_a3_upload_polling.py]
A5A6[a5_to_a6_download.py]
B1B2[b1_to_b2_download.py]
CX[creativex_scoring_storing.py]
end
subgraph Shared["Shared Modules"]
DAM[dam_client.py
DAM API + OAuth2/mTLS]
BOX[box_client.py
Box JWT + Upload/Download]
DB[database.py
PostgreSQL Pool]
NOT[notifier.py
Email + Webhooks]
PARSER[filename_parser.py
V2.1 Parsing]
MVP[metadata_extractor_mvp.py
Field Mapping]
CONFIG[config_loader.py
YAML + Env Vars]
end
A1A2 --> DAM
A1A2 --> BOX
A1A2 --> DB
A1A2 --> NOT
A1A2 --> CONFIG
A2A3 --> DAM
A2A3 --> BOX
A2A3 --> DB
A2A3 --> NOT
A2A3 --> PARSER
A2A3 --> MVP
A2A3 --> CONFIG
A5A6 --> DAM
A5A6 --> BOX
A5A6 --> DB
A5A6 --> NOT
A5A6 --> CONFIG
B1B2 --> DAM
B1B2 --> BOX
B1B2 --> DB
B1B2 --> NOT
B1B2 --> CONFIG
CX --> BOX
CX --> DB
CX --> NOT
CX --> CONFIG
```
### Shared Modules Explained
#### dam_client.py - DAM API Client
**Purpose:** All communication with OpenText DAM
**Features:**
- OAuth2 authentication (default, auto-refresh tokens)
- mTLS certificate authentication (optional, with --auth-pfx flag)
- Campaign queries (get campaigns by status)
- Asset operations (download, upload, metadata)
- Recursive folder search (includes subfolders)
- Status updates (A1→A2, A5→A6, etc.)
**Key Methods:**
```python
# Get campaigns by status
campaigns = dam.get_campaigns_by_status('A1')
# Download asset
file_path = dam.download_asset(asset_id, output_dir)
# Upload asset with metadata
result = dam.upload_asset(file_path, folder_id, asset_representation)
# Update campaign status
dam.update_campaign_status(campaign_id, 'A2')
```
#### box_client.py - Box.com Integration
**Purpose:** File transfer via Box
**Features:**
- JWT authentication (uses Box-config.json)
- Create campaign folders
- Upload with tracking IDs
- Download files
- List folders (recursive)
- Preserve subfolder structure
- Delete files after processing
**Key Methods:**
```python
# Upload with tracking ID
result = box.upload_with_tracking_id(
file_path, campaign_id, campaign_name, tracking_id,
subfolder_path="Europe/Germany" # Preserves structure
)
# List folder files recursively
files = box.list_folder_files_recursive(folder_id)
# Download file
box.download_file(file_id, output_path)
```
#### database.py - PostgreSQL Operations
**Purpose:** All database interactions
**Features:**
- Connection pooling (1-10 connections)
- Tracking ID generation (unique 6-char)
- Master asset storage (with JSONB metadata)
- Derivative asset tracking
- CreativeX score storage (3 statuses)
- Campaign status tracking
- Find-or-create for rework workflow
**Key Methods:**
```python
# Generate unique tracking ID
tracking_id = db.generate_unique_tracking_id() # Returns: "pOiJ9s"
# Find or reuse tracking ID (for A5→A6 rework)
result = db.find_or_create_tracking_id(opentext_id, campaign_id)
# Returns: {'tracking_id': 'pOiJ9s', 'is_existing': True}
# Store master asset
db.store_master_asset(
tracking_id, opentext_id, asset_data,
box_file_id, box_url, upload_folder_id
)
# Get master asset
master = db.get_master_asset('pOiJ9s')
# Returns full metadata for inheritance
# Store CreativeX score
db.store_creativex_score(
filename, creativex_id, url, score,
box_file_id, full_json,
tracking_id='pOiJ9s', # Optional, for master scores
status='master-cx-score' # Or 'active' for derivatives
)
# Get CreativeX score
score = db.get_creativex_score_by_filename('file.mp4')
# Returns latest active score or None
```
#### filename_parser.py - V2.1 Parser
**Purpose:** Parse filenames into components
**Features:**
- V2.1 structure support (new positions)
- Pattern-based detection (flexible)
- Social media code support
- Tracking ID mode detection (-N suffix)
- Validation with error messages
- Strip upload components (job + tracking)
**Key Methods:**
```python
# Parse filename
parsed = parser.parse_filename('6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_IGF_pOiJ9s.mp4')
# Returns:
{
'omg_job_number': '6487512',
'brand_code': 'NUT',
'subject_title': 'SUMMER',
'asset_type': 'OLV',
'seconds': '30',
'aspect_ratio': '16x9',
'spot_version': 'MST',
'country_code': 'DE',
'language_code': 'de',
'social_media_version': 'IGF',
'tracking_id': 'pOiJ9s',
'tracking_mode': 'full',
'is_valid': True,
'validation_errors': []
}
# Strip upload components
clean = parser.strip_upload_components('6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_IGF_pOiJ9s.mp4')
# Returns: 'NUT_SUMMER_OLV_30S_16x9_MST_DE_de_IGF.mp4'
```
#### metadata_extractor_mvp.py - Field Mapping
**Purpose:** Build asset representations for DAM upload
**Features:**
- Extract 30 MVP fields from master metadata
- Update fields from filename (configurable)
- Map asset types (3-letter → DAM codes)
- Map country codes (ISO → DAM codes)
- Force values (STATE = Local)
- Add missing fields with defaults
- CreativeX field handling
**Key Methods:**
```python
# Build asset representation
asset_rep = mvp_extractor.build_mvp_asset_representation(
master_metadata=master['full_metadata'],
clean_filename='NUT_SUMMER_OLV_30S_16x9_MST_DE_de.mp4',
parsed_filename=parsed,
box_metadata={'score': '85', 'url': 'https://...'},
tracking_mode='full' # or 'folder_only' for -N suffix
)
# Returns: List of 27 metadata fields ready for DAM upload
```
**Field Mapping Logic:**
```python
# From master metadata (inherited):
- Brand Name
- Brand Code
- Campaign references
- Fiscal Year
- All Marketing fields (Director, Production House, etc.)
- All Market fields (IP Rights, Licensing, etc.)
# From filename (overrides):
- Asset Name = clean filename
- Description = subject_title
- Asset Type = mapped from 3-letter code (OLV → onlinevideodigitalvideo)
- Language = language_code (from filename, uppercase)
# Forced values:
- STATE = "Local" (always)
- AGENCY NAME = "Oliver"
# From database (CreativeX):
- CreativeX Score = looked up by filename
- CreativeX URL = looked up by filename
```
---
## CreativeX Integration
### What is CreativeX?
CreativeX is an AI-powered creative quality scoring platform that analyzes advertising content against industry best practices and brand guidelines. It provides:
- Quality scores (0-100)
- Guideline compliance analysis
- Actionable improvement recommendations
- Benchmark comparisons
**Why We Use It:**
- Objective quality measurement
- Consistent scoring across markets
- Early identification of issues
- Performance prediction
- Continuous improvement tracking
### Two Sources of CreativeX Scores
```mermaid
flowchart TB
subgraph Master["Master Asset Score (Reference Only)"]
M1[Master Asset in DAM] -->|May have score in metadata| M2[A1→A2 Script]
M2 -->|Extract if present| M3[Store in Database]
M3 -->|status='master-cx-score'| M4[Linked by tracking_id]
M4 --> M5[Used for:
- Analytics
- Comparison
- Reporting]
M5 -.->|NOT used for| M6[❌ NOT used in A2→A3 uploads]
end
subgraph Derivative["Derivative Asset Score (Used in Uploads)"]
D1[Agency gets localized asset scored] --> D2[CreativeX generates PDF report]
D2 -->|PDF contains: filename, score, URL| D3[Upload PDF to Box 350605024645]
D3 -->|Manual/automated| D4[creativex_scoring_storing.py]
D4 -->|LlamaExtract AI parses PDF| D5[Extract: filename, score, URL, ID]
D5 -->|Store in Database| D6[status='active']
D6 -->|Matched by exact filename| D7[A2→A3 Upload]
D7 --> D8[✅ USED in DAM upload]
end
style M6 fill:#FFB6C1
style D8 fill:#90EE90
```
### 🔴 CRITICAL REQUIREMENT: Every Derivative Needs Its Own CreativeX Score
**THIS IS NOT OPTIONAL - IT IS MANDATORY**
#### The Rule: One Asset = One Score
```
1 Master Asset → 10 Localized Derivatives = 10 CreativeX Scores Required
1 Master Asset → 200 Localized Derivatives = 200 CreativeX Scores Required
```
**Why EVERY Derivative Needs Scoring:**
1. **Each localization is different creative execution**
- German voice-over has different performance than French
- Italian subtitle placement may affect engagement differently
- UK cultural references differ from US references
- Each market's execution quality varies
2. **Master score does NOT apply to derivatives**
- Master: Global concept score (reference only)
- Derivative: Localized execution score (what matters)
- Example:
- Master (English): Score 85 (good concept)
- German derivative: Score 71 (translation issues)
- French derivative: Score 78 (better execution)
- Italian derivative: Score 82 (excellent localization)
3. **CreativeX scores are asset-specific, not campaign-specific**
- Each file gets analyzed individually
- Different text overlays → different score
- Different pacing → different score
- Different cultural adaptations → different score
4. **Reporting requires individual scores**
- Which markets perform best?
- Which localizations need improvement?
- ROI tracking by market
- Can't answer these without per-asset scores
#### The Math
**Example Campaign:**
```
1 Master Asset (nutella_hero_master.mp4)
↓
Localized for 20 markets × 2 languages each = 40 derivatives
Plus: 3 aspect ratios (16x9, 1x1, 9x16) = 40 × 3 = 120 versions
Plus: 2 platforms (standard + Instagram) = 120 × 2 = 240 derivatives
REQUIRED: 240 CreativeX PDF reports
REQUIRED: 240 entries in creativex_scores database table
```
**If you create 200 assets, you need 200 CreativeX scores. Period.**
#### What Happens If You Don't Score All Assets
**Scenario: Campaign with 50 derivatives, only 30 scored**
```
30 derivatives:
- Have PDF reports
- Scores in database (71-85 range)
- Upload with actual CreativeX data ✅
20 derivatives:
- NO PDF reports
- NOT in database
- Upload with Score=0, placeholder URL ❌
- Email shows orange warning for each
Result in DAM:
- 30 assets: Properly scored (useful for optimization)
- 20 assets: Score=0 (meaningless, looks bad)
Client sees:
- Inconsistent quality reporting
- 40% of assets show "not scored"
- Questions quality control process
```
#### The Workflow: Scoring BEFORE Upload
**CORRECT Process:**
```mermaid
sequenceDiagram
autonumber
participant Agency
participant CX as CreativeX Platform
participant Box as Box Folder 350605024645
participant Script as creativex_scoring_storing.py
participant DB as Database
participant Upload as A2→A3 Upload
Note over Agency: For EACH derivative created
Agency->>Agency: Create localized asset
Agency->>CX: Submit to CreativeX for analysis
CX->>CX: AI analyzes creative quality
CX-->>Agency: PDF scorecard generated
Agency->>Box: Upload PDF to folder 350605024645
Box->>Script: PDF detected (manual or cron)
Script->>Script: Extract filename, score, URL
Script->>DB: Store with status='active'
Note over DB: Score now available for this asset
Agency->>Upload: Upload derivative to A2→A3 folder
Upload->>DB: Lookup score by filename
DB-->>Upload: Score=71, URL=https://...
Upload->>Upload: Attach score to DAM upload ✅
```
**Timeline Example (50 derivatives):**
```
Week 1:
- Create 50 localized assets
- Submit all 50 to CreativeX platform
- Receive 50 PDF reports
Week 2:
- Upload all 50 PDFs to Box folder 350605024645
- Run creativex_scoring_storing.py (or automated)
- Verify all 50 in database:
SELECT COUNT(*) FROM creativex_scores WHERE status='active'
Result: 50 ✅
- Upload all 50 derivatives to A2→A3 folder
- All 50 process with actual scores
- All 50 emails show "CreativeX Score Added: [score]"
```
#### Common Mistake: "I'll Score the Important Ones Only"
**The Thinking:**
"We have 200 derivatives. I'll only score the hero assets (20 of them). The rest can use score=0."
**Why This Fails:**
1. **Client expectations:**
- Contract may require all assets scored
- Quality standards apply to ALL deliverables
- Score=0 looks unprofessional
2. **Reporting incomplete:**
- Can't analyze which markets perform best (only have 10% of data)
- Can't identify improvement opportunities
- ROI tracking impossible
3. **Selective scoring = inconsistent quality:**
- Some assets optimized, others not
- Performance disparity
- Brand inconsistency
4. **The system defaults to Score=0:**
- Not "unscored" or "pending"
- Actively shows ZERO
- Implies poor quality (even if asset is good)
**Reality:**
If you create 200 assets, budget for 200 CreativeX analyses. Factor this into:
- Project timeline
- Project budget
- Resource allocation
- Agency scope of work
#### Batch Processing CreativeX
**For Large Campaigns (100+ derivatives):**
**Week-by-Week Approach:**
```
Week 1: Create + score derivatives 1-25
- Upload 25 PDFs
- Run scoring script
- Upload 25 derivatives
- All get actual scores
Week 2: Create + score derivatives 26-50
- Upload 25 PDFs
- Run scoring script
- Upload 25 derivatives
- All get actual scores
... continue weekly
```
**NOT Recommended:**
```
Week 1-4: Create all 100 derivatives
Week 5: Upload all 100 derivatives (no scores yet)
Week 6: Start scoring (too late, already uploaded)
Result: All 100 uploaded with score=0
Then manual DAM updates needed
```
#### Exceptions to the Rule
**When Score=0 is Acceptable:**
1. **Internal reference assets** (not client-facing)
2. **Template files** (not final deliverables)
3. **Work-in-progress** (will be scored before final delivery)
4. **Non-creative assets** (legal documents, spreadsheets)
**But for client deliverables: ALL must be scored.**
#### How to Verify All Assets Are Scored
**Before Mass Upload:**
```bash
# Count derivatives you created
ls derivatives/ | wc -l
Result: 200 files
# Count scores in database
PGPASSWORD=xxx psql -h localhost -p 5437 -U ferrero_user -d ferrero_tracking -c "
SELECT COUNT(*) FROM creativex_scores WHERE status = 'active';
"
Result: Should be 200 (or close to it)
# List any files without scores (after upload)
# Compare uploaded filenames to database entries
```
**After Upload:**
```bash
# Check how many used defaults
grep "CreativeX Score Missing" logs/a2_to_a3.log | wc -l
# If this number is high → many derivatives uploaded without scores
```
### CreativeX PDF Extraction Workflow
**Step 1: CreativeX Generates PDF Report**
- Agency submits asset to CreativeX platform
- CreativeX AI analyzes content
- Generates PDF scorecard
- PDF contains:
- Filename: `6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_IGF_pOiJ9s.mp4`
- CreativeX ID: `6864308`
- CreativeX URL: `https://app.creativex.com/audit/scorecards/32456797`
- Quality Score: `71` (out of 100)
- Guidelines breakdown
- Recommendations
**Step 2: Upload PDF to Box**
- Agency uploads PDF to Box folder: `350605024645`
- PDF can be named anything (e.g., `nutella_summer_de_report.pdf`)
- What matters is the **filename field INSIDE the PDF**
**Step 3: Run CreativeX Scoring Script**
```bash
# Manual execution (or can be added to cron)
python scripts/creativex_scoring_storing.py
```
**Script Process:**
```mermaid
sequenceDiagram
autonumber
participant Box as Box Folder 350605024645
participant Script as creativex_scoring_storing.py
participant Llama as LlamaExtract AI
participant DB as Database
participant Email
Script->>Box: List PDF files
Box-->>Script: Found 1 PDF
Script->>Box: Download PDF
Box-->>Script: PDF file
Script->>Llama: Extract data using agent "Creativex-Extract"
Llama->>Llama: AI parses PDF structure
Llama->>Llama: Find filename field
Llama->>Llama: Extract score, URL, ID
Llama-->>Script: JSON response
Script->>Script: Parse JSON
Script->>Script: Get filename: 6487512_NUT_SUMMER_OLV..._pOiJ9s.mp4
Script->>Script: Get score: 71
Script->>Script: Remove .0 decimals: 71.0 → 71
Script->>DB: Check if filename exists (status='active')
alt Filename already exists
DB-->>Script: Found existing (version 1)
Script->>DB: Mark old as status='superseded'
Script->>DB: Insert new with status='active', version 2
else New filename
Script->>DB: Insert with status='active', version 1
end
Script->>Box: Delete PDF file
Script->>Email: Send success notification
Email-->>Team: "1 file processed, Score: 71 (Version 2)"
```
**Database Storage:**
```sql
INSERT INTO creativex_scores (
filename, creativex_id, creativex_url, quality_score,
box_file_id, full_extraction_data, status
) VALUES (
'6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_IGF_pOiJ9s.mp4',
'6864308',
'https://app.creativex.com/audit/scorecards/32456797',
'71',
'box_file_id_123',
'{"data": {...full JSON from LlamaExtract...}}',
'active'
);
```
**Version Tracking:**
If same filename is scored again:
```
Version 1: Score 71, status='superseded' (old)
Version 2: Score 75, status='active' (current)
Version 3: Score 80, status='active' (latest)
```
Email shows: "Score: 80 (Version 3)"
**Step 4: A2→A3 Uses Score Automatically**
When agency uploads derivative:
```
Filename: 6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_IGF_pOiJ9s.mp4
Lookup: SELECT * FROM creativex_scores WHERE filename = '...' AND status = 'active'
Found: Score 71, URL https://...
Upload: Includes CreativeX metadata
```
**If No Score Found:**
```
Lookup: Returns None
Default: Score = 0, URL = https://app.creativex.com/preflight/pretests
Upload: Succeeds with defaults
Email: Shows orange warning "CreativeX Score Missing"
```
### Master vs Derivative Scores
**Master Score (status='master-cx-score'):**
- Extracted during A1→A2 from DAM metadata
- Linked by tracking_id
- Purpose: Reference, analytics, comparison
- **NOT used in A2→A3 uploads**
- Query: `WHERE tracking_id = 'pOiJ9s' AND status = 'master-cx-score'`
**Derivative Score (status='active'):**
- Extracted from PDF via LlamaExtract
- Matched by exact filename (with job + tracking)
- Purpose: Attached to DAM upload
- **USED in A2→A3 uploads**
- Query: `WHERE filename = 'exact_name.mp4' AND status = 'active'`
**Why Separate?**
- Each localized asset gets its own score
- Derivative scores often differ from master
- Master: Global creative concept score
- Derivative: Localized execution score
- Need to track both for analytics
**Example:**
```
Master: nutella_summer_hero.mp4
- Tracking ID: pOiJ9s
- Master Score: 85 (general creative quality)
Derivatives:
- German version: Score 71 (localization quality)
- French version: Score 78 (localization quality)
- Italian version: Score 82 (localization quality)
Analytics can show:
- Master scored 85
- Average derivative: 77
- Italian performed best (+3 from average)
```
---
*[Document continues with remaining sections...]*
**Note:** This is approximately 20% of the complete guide. The full document will include:
- Detailed workflows for all scripts
- Common mistakes with 20+ real examples
- Troubleshooting guide with solutions
- Database schema with ERD
- Monitoring procedures
- Glossary and FAQ
- Complete reference tables
**Estimated final length:** 60-70 pages
Would you like me to continue building out the remaining sections?
---
## The Database
### Database Architecture
The PostgreSQL database is the **central nervous system** of the automation. It stores:
- Master asset metadata and tracking IDs
- Derivative asset records
- CreativeX scores (master and derivative)
- Campaign status tracking
- Complete audit trail
```mermaid
erDiagram
MASTER_ASSETS ||--o{ DERIVATIVE_ASSETS : "has many via tracking_id"
MASTER_ASSETS ||--o{ CREATIVEX_SCORES : "has master score via tracking_id"
MASTER_ASSETS ||--o{ ASSET_EVENTS : "has events"
CAMPAIGN_STATUS ||--o{ MASTER_ASSETS : "contains"
MASTER_ASSETS {
varchar tracking_id PK "6-char unique ID"
varchar opentext_id "DAM asset ID"
varchar original_filename "Original name"
jsonb full_metadata "Complete DAM metadata"
varchar upload_directory "Final Assets folder ID"
varchar global_master_campaign_id "Global campaign ref"
varchar local_campaign_id "Local campaign ID"
timestamp created_at
}
DERIVATIVE_ASSETS {
int id PK
varchar tracking_id FK "Links to master"
varchar derivative_filename "Clean filename"
varchar dam_asset_id "Uploaded asset ID"
varchar upload_status "pending/completed"
timestamp uploaded_at
}
CREATIVEX_SCORES {
int id PK
varchar filename "Exact filename for matching"
varchar creativex_id "CreativeX scorecard ID"
varchar creativex_url "CreativeX URL"
varchar quality_score "Score 0-100"
varchar tracking_id "Links to master (for master scores)"
varchar status "active/superseded/master-cx-score"
jsonb full_extraction_data "Complete extraction JSON"
timestamp extracted_at
}
CAMPAIGN_STATUS {
int id PK
varchar campaign_id UK "DAM campaign folder ID"
varchar campaign_number "C000000078"
varchar status "A1/A2/A3/A4/A5/B1/B2"
boolean webhook_sent "Prevents duplicates"
timestamp webhook_sent_at
}
ASSET_EVENTS {
int id PK
varchar tracking_id "Related asset"
varchar event_type "created/updated/uploaded"
jsonb event_data "Event details"
timestamp event_timestamp
}
```
### Table Purposes
#### master_assets (35 columns)
**Purpose:** Original master assets from A1→A2 download
**Key Fields:**
- `tracking_id` - Unique 6-character identifier
- `opentext_id` - DAM asset ID (hex string)
- `original_filename` - Name without extension
- `full_metadata` - **JSONB column with COMPLETE DAM metadata**
- `upload_directory` - Where derivatives upload in DAM
- `global_master_campaign_id` - Reference to global master
- `local_campaign_id` - Local campaign this belongs to
**Why JSONB for full_metadata?**
- No truncation (can be 100KB+ of metadata)
- Queryable with PostgreSQL JSONB operators
- Preserves exact structure from DAM
- Future-proof (new fields don't break schema)
**Example Query:**
```sql
-- Find all assets for a campaign
SELECT tracking_id, original_filename
FROM master_assets
WHERE local_campaign_id = 'C000000078'
AND status = 'active';
-- Query within JSONB metadata
SELECT tracking_id,
full_metadata->'metadata'->'id' as metadata_model
FROM master_assets
WHERE tracking_id = 'pOiJ9s';
```
#### derivative_assets
**Purpose:** Track localized assets uploaded via A2→A3
**Key Fields:**
- `tracking_id` - Links to master_assets
- `derivative_filename` - Clean filename (no job/tracking)
- `dam_asset_id` - Asset ID in DAM after upload
- `upload_status` - pending/completed/failed
**Why This Table?**
- Track which derivatives uploaded
- Link back to master
- Detect when ALL derivatives done
- Audit trail of uploads
#### creativex_scores
**Purpose:** Store quality scores from two sources
**Three Status Values:**
**1. status='active' (Derivative Scores)**
- Source: PDF extraction via LlamaExtract
- Matched by: Exact filename (with job + tracking)
- Used in: A2→A3 uploads
- Versioned: Yes (old = 'superseded')
- Example: `6487512_NUT_SUMMER_OLV..._pOiJ9s.mp4`, score=71
**2. status='superseded' (Old Versions)**
- Source: PDF extraction (previous version)
- Purpose: History/audit trail
- Not used in uploads
- Example: Same filename, score=68 (old)
**3. status='master-cx-score' (Master Reference)**
- Source: A1→A2 extraction from DAM metadata
- Matched by: tracking_id
- Used in: Analytics/reporting ONLY, never uploads
- Not versioned
- Example: `nutella_summer_hero.mp4`, tracking=pOiJ9s, score=85
**Query Examples:**
```sql
-- Get derivative score for upload (A2→A3 uses this)
SELECT quality_score, creativex_url
FROM creativex_scores
WHERE filename = '6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_IGF_pOiJ9s.mp4'
AND status = 'active';
-- Get master score for reporting
SELECT quality_score, creativex_url
FROM creativex_scores
WHERE tracking_id = 'pOiJ9s'
AND status = 'master-cx-score';
-- Compare master vs derivative scores
SELECT
cs_master.quality_score as master_score,
cs_deriv.quality_score as derivative_score,
cs_deriv.filename as derivative_name
FROM creativex_scores cs_master
JOIN master_assets ma ON cs_master.tracking_id = ma.tracking_id
JOIN creativex_scores cs_deriv ON cs_deriv.filename LIKE '%' || ma.tracking_id || '%'
WHERE cs_master.status = 'master-cx-score'
AND cs_deriv.status = 'active';
```
#### campaign_status
**Purpose:** Prevent duplicate webhook sends
**Problem It Solves:**
Without this table, if A2→A3 runs multiple times, it might send the "campaign complete" webhook multiple times to Make.com, causing duplicate downstream actions.
**How It Works:**
```python
# Check if webhook already sent
existing = db.check_campaign_processed(campaign_id)
if existing['webhook_sent']:
logger.info("Webhook already sent for this campaign - skipping")
return
# Send webhook
send_webhook(campaign_data)
# Record that webhook was sent
db.record_campaign_status(
campaign_id, campaign_number, campaign_name,
live_campaign='YES', status='A3', webhook_sent=True
)
```
### Backup Strategy
**Daily Backups (2:00 AM):**
```bash
# pg_dump creates SQL dump
./database/backup.sh --daily
# Creates: backups/dumps/ferrero_tracking_2025-11-13_02-00.sql.gz
# Retention: 7 days
# Size: ~2-5 MB compressed
```
**Weekly Backups (Sunday 3:00 AM):**
```bash
# pg_basebackup creates binary backup
./database/backup.sh --weekly
# Creates: backups/basebackups/ferrero_tracking_2025-11-10_03-00.tar.gz
# Retention: Latest only
# Size: ~30 MB compressed
```
**Health Monitoring (8:00 AM Daily):**
```bash
# Checks backup age, warns if > 25 hours old
./database/check_backups.sh
```
**Restore Process:**
```bash
# List available backups
./database/restore.sh --list
# Restore from specific backup
./database/restore.sh backups/dumps/ferrero_tracking_2025-11-13_02-00.sql.gz
# Creates safety backup first
# Requires confirmation: "yes"
# Restores database
# Verifies row counts
```
---
## Common Mistakes & Pitfalls
### Category 1: Filename Errors
#### Mistake 1.1: Using Old V1 Field Order
**What Happens:**
```
User creates: 6487512_NUT_DE_de_SUMMER_OLV_30S_16x9_pOiJ9s.mp4
(This is OLD V1 structure: BRAND_COUNTRY_LANG_SUBJECT_ASSET...)
Parser expects V2.1: BRAND_SUBJECT_ASSET_..._COUNTRY_LANG
Parser reads:
- Position 3 should be SUBJECT, reads: "DE" (thinks subject is "DE")
- Position 4 should be ASSET, reads: "de" (thinks asset type is "de")
- Position 8 should be COUNTRY, reads: "30S" (thinks country is "30S")
Result: Complete parsing failure
Validation errors:
- "Asset Type invalid: de (must be 3 chars uppercase)"
- "Country Code invalid: 30S (must be 2 chars)"
File: SKIPPED
Status: Stuck in Box
Fix: Use naming tool or reorder manually to V2.1
```
**Prevention:** Always use the naming tool - it generates correct V2.1 format
---
#### Mistake 1.2: Manual Typing Instead of Copy/Paste
**What Happens:**
```
Naming tool generates: 6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_pOiJ9s.mp4
User types manually: 6487512_NUT_SUMMER_OLV_30s_16x9_MST_DE_de_pOiJ9s.mp4
↑ lowercase 's'
Parser expects: \d+S pattern (capital S)
Parses: "30s" doesn't match pattern
Result:
- Duration not detected
- "30s" interpreted as aspect ratio (fallback)
- "16x9" interpreted as... something else
- Chaos in parsing
Validation: May fail or produce wrong metadata
```
**Prevention:** Use "Copy to Clipboard" button, never type manually
---
#### Mistake 1.3: Wrong Tracking ID (Copy/Paste Error)
**What Happens:**
```
Correct tracking ID: pOiJ9s (lowercase i)
User copies: pOlJ9s (lowercase L instead of i)
Upload: 6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_pOlJ9s.mp4
Parse: ✅ Succeeds (pOlJ9s is valid 6-char alphanumeric)
Database lookup: SELECT * FROM master_assets WHERE tracking_id = 'pOlJ9s'
Result: NO ROWS FOUND
Error: "No master asset for tracking ID: pOlJ9s"
Upload: FAILS
Email: Error notification sent
Agency confused: "I have the tracking ID from the email!"
Reality: Small typo made it a different ID
```
**Prevention:**
- Copy from email directly, don't retype
- Use naming tool dropdown if it has tracking ID list
- Double-check the last 6 characters of generated filename match email
---
#### Mistake 1.4: Reusing Tracking ID from Different Campaign
**What Happens:**
```
Campaign A (C000000078): Master nutella_hero.mp4, tracking=abc123
Campaign B (C000000099): Master raffa_promo.mp4, tracking=xyz789
User localizing for Campaign B:
- Accidentally uses tracking ID from Campaign A: abc123
- Filename: 6487512_RAF_PROMO_OLV_16x9_FR_fr_abc123.mp4
A2→A3 processes:
- Finds tracking ID: abc123
- Loads master metadata for abc123
- Gets: Nutella brand metadata! (wrong campaign)
- Uploads Raffaello asset with Nutella metadata!
Result: Asset uploaded to WRONG campaign folder with WRONG brand
Data corruption in DAM
Manual cleanup required
```
**Prevention:**
- Always get tracking ID from the SAME campaign's A1→A2 email
- Never reuse tracking IDs across campaigns
- Each campaign gets fresh tracking IDs
---
#### Mistake 1.5: Missing Required Field
**What Happens:**
```
User forgets aspect ratio: 6487512_NUT_SUMMER_OLV_30S_MST_DE_de_pOiJ9s.mp4
↑ Missing ratio
Parser tries to find ratio pattern (contains 'x' or ':')
Doesn't find it
Tries fallback detection
May parse "MST" as ratio (wrong)
Then "DE" becomes spot version (wrong)
Cascade of misinterpretation
Validation: FAILS
Error: "Aspect Ratio missing"
File: SKIPPED
```
**Prevention:** Naming tool enforces required fields - can't generate without them
---
### Category 2: Upload Location Errors
#### Mistake 2.1: Uploading to Wrong Box Folder
**What Happens:**
```
User receives A1→A2 email with Box URL: https://app.box.com/folder/348304357505
User should upload derivatives to A2→A3 folder: 348526703108
User accidentally uploads to A1→A2 folder (348304357505)
A2→A3 script polls folder 348526703108
Finds: No files (they're in wrong folder)
Result: Nothing happens
User waits... waits... waits...
No email received
Asset never uploaded to DAM
Eventually: "Why hasn't my file been processed?"
Investigation: File in wrong folder
Fix: Move file to correct folder
```
**Prevention:**
- Check email for correct upload URL
- A1→A2 email says: "Masters ready for download"
- A2→A3 email should say: "Upload derivatives to: [URL]"
- Bookmark correct folders
---
#### Mistake 2.2: Uploading CreativeX PDF to Wrong Folder
**What Happens:**
```
User uploads CreativeX PDF to A2→A3 folder (348526703108)
Should upload to CreativeX folder (350605024645)
A2→A3 script:
- Tries to parse PDF filename as asset filename
- Parse fails (PDF name doesn't match pattern)
- Skips file
- PDF sits in folder forever
creativex_scoring_storing.py:
- Looks in folder 350605024645
- Doesn't find PDF (it's in wrong folder)
- No score extracted
Result:
- PDF not processed
- Score not in database
- A2→A3 uploads use default score=0
```
**Prevention:**
- CreativeX PDFs go to folder 350605024645 ONLY
- Asset files go to folder 348526703108
- Different purposes, different folders
---
### Category 3: Timing Errors
#### Mistake 3.1: Uploading Before Getting Tracking ID
**What Happens:**
```
Day 1 Morning: Campaign created in DAM (status A1)
Day 1 09:00: Agency starts localizing (proactive!)
Day 1 10:00: Agency uploads derivatives to Box
Day 1 10:05: A1→A2 script runs, downloads masters, generates tracking IDs
Day 1 10:10: A2→A3 script runs, finds agency files
Problem: Agency files have NO tracking IDs (uploaded before A1→A2 ran)
Filename: 6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de.mp4 (missing tracking ID)
Parse: tracking_id = None
Upload: FAILS
Agency must:
- Wait for A1→A2 email
- Get tracking IDs
- Rename files
- Re-upload
```
**Prevention:** Wait for A1→A2 email before starting localization
---
#### Mistake 3.2: Uploading CreativeX PDF After Derivative Upload
**What Happens:**
```
10:00: Agency uploads derivative to A2→A3
10:05: A2→A3 processes, no CreativeX score found
10:05: Uploads to DAM with score=0, placeholder URL
10:10: Agency uploads CreativeX PDF
10:15: creativex_scoring_storing.py extracts, stores score=85
Result:
- Asset already in DAM with score=0
- Database has score=85
- Mismatch between DAM and database
```
**Better Workflow:**
```
1. Upload CreativeX PDF first
2. Run creativex_scoring_storing.py (or wait for scheduled run)
3. Verify score in database
4. Then upload derivative
5. A2→A3 finds score, attaches correctly
```
**Or:** Accept score=0 initially, then update DAM manually later
---
### Category 4: CreativeX Integration Errors
#### Mistake 4.1: PDF Filename Doesn't Match Asset Filename
**What Happens:**
```
Derivative filename (in Box): 6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_pOiJ9s.mp4
CreativeX PDF filename field (extracted): NUT_SUMMER_OLV_30S_16x9_MST_DE_de.mp4
↑ Missing job number and tracking ID
Database stores: NUT_SUMMER_OLV_30S_16x9_MST_DE_de.mp4
A2→A3 looks up: 6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_pOiJ9s.mp4
Database query: WHERE filename = '6487512_NUT_SUMMER_OLV..._pOiJ9s.mp4'
Result: NO MATCH
Uses: Default score=0
```
**Root Cause:**
CreativeX PDF was generated with clean filename, but derivative uploaded with job+tracking
**Fix:**
The PDF's filename field must match EXACTLY what will be uploaded to Box
- Include job number
- Include tracking ID
- Exact same extension
---
#### Mistake 4.2: Not Running CreativeX Scoring Script
**What Happens:**
```
Agency uploads PDF to Box folder 350605024645
Agency forgets to run: python scripts/creativex_scoring_storing.py
PDF sits in folder
Never extracted
Database has no score
All A2→A3 uploads use default score=0
```
**Fix Options:**
1. Run script manually after PDF upload
2. Add to cron for automatic processing
3. Set up Box webhook to trigger script (advanced)
---
### Category 5: Database Issues
#### Mistake 5.1: Duplicate Tracking IDs
**What Happens (Shouldn't Happen, But If It Does):**
```
Somehow two assets get same tracking ID: pOiJ9s
Database constraint: tracking_id UNIQUE
Second insert: FAILS with "duplicate key value"
Script: Catches error, logs failure
Email: Error notification
Investigation needed to find why duplicate generated
```
**Prevention:**
- generate_unique_tracking_id() checks database for uniqueness
- Retries up to 100 times
- Statistically impossible with 6 characters (56 billion combinations)
---
#### Mistake 5.2: Database Connection Lost During Processing
**What Happens:**
```
A1→A2 processing campaign with 10 assets:
- Asset 1-5: ✅ Success, stored in database
- Asset 6: Database connection drops
- Asset 6: INSERT fails
- Script: Catches exception
- Transaction: ROLLED BACK (asset 6 not stored)
- Asset 7-10: ⏭️ Not attempted (stop on error)
Result:
- Status NOT updated (still A1)
- Partial success email sent
- Next run: Retries assets 6-10
- Eventually completes
```
**Why This Design?**
- Transaction safety
- All-or-nothing approach
- Clear state (either campaign done or not)
- Automatic retry on next cron run
---
### Category 6: Intentional Shortcuts (And Why They Fail)
#### Shortcut 6.1: "I'll Fix the Metadata in DAM After Upload"
**The Thinking:**
"I'll upload with whatever filename, then manually edit the metadata in DAM. Faster than using the naming tool."
**Reality:**
```
Upload with wrong filename: nutella_video.mp4
A2→A3 processes:
- Parse fails (not V2.1 format)
- OR parses incorrectly
- Metadata applied wrong
- Upload succeeds with WRONG metadata
Now in DAM:
- Wrong language
- Wrong country
- Wrong asset type
- Wrong description
To fix:
- Find asset in DAM (search)
- Open metadata editor
- Fix 10+ fields manually
- Save each field
- Test search still works
- Update related assets
Time: 30+ minutes per asset
vs.
Using naming tool correctly:
- Generate filename: 1 minute
- Upload: 1 minute
- Automation handles rest
- Correct metadata automatically
Time: 2 minutes total
```
**Lesson:** Shortcuts create more work, not less
---
#### Shortcut 6.2: "I'll Skip the Job Number, Not Billing This"
**The Thinking:**
"This is internal work, no client billing, so job number doesn't matter."
**Reality:**
```
Upload: NUT_SUMMER_OLV_30S_16x9_MST_DE_de_pOiJ9s.mp4 (no job number)
Parse: FAILS (expects digits in position 1)
OR if job number optional in parser:
Upload succeeds but:
- Can't link to OMG project
- Client asks: "Where's the summer video?"
- Search by job number: NOT FOUND
- Search by client: Manual lookup needed
- Billing reconciliation: Manual matching
- Client reporting: Incomplete
Time wasted: Hours across multiple people
```
**Lesson:** Job number is required business metadata, not optional
---
#### Shortcut 6.3: "I'll Manually Enter CreativeX Score in Box Metadata"
**The Thinking:**
"Old workflow used Box metadata templates. I'll just enter the score there instead of uploading PDF."
**Reality:**
```
User opens Box file
Clicks metadata
Adds custom metadata field: "creativex_score = 85"
Saves
A2→A3 script runs:
- Ignores Box metadata completely (new workflow)
- Looks in PostgreSQL database only
- Finds: Nothing
- Uses: Default score=0
Result: Score not attached despite manual entry
Wasted effort
```
**Lesson:** New workflow is database-only. Must upload PDF or score won't attach.
---
### Category 7: Process Violations
#### Violation 7.1: Uploading Directly to DAM (Bypassing Automation)
**What Happens:**
```
User has DAM access
User uploads derivative directly to "02. Final Assets" folder
Bypasses Box and A2→A3 automation
Result:
- No tracking ID link to master
- No database record
- No CreativeX score attached
- No email notification
- Asset "invisible" to reporting
- Audit trail broken
Later when searching:
- "Why don't we have a German version?"
- File is there but not tracked
- Manual reconciliation needed
```
**Lesson:** ALWAYS use Box upload workflow for tracking and automation
---
#### Violation 7.2: Editing Files in Box After Upload
**What Happens:**
```
10:00: Agency uploads to A2→A3
10:05: Agency notices issue, edits file in Box (replaces version)
10:10: A2→A3 script runs
10:10: Downloads file (gets EDITED version, not original)
10:11: Uploads to DAM
Result: Processed, but...
- Audit trail shows original upload time
- But file is edited version
- Discrepancy between logs and actual file
- Confusion in troubleshooting
```
**Better Approach:**
- Delete file from Box
- Fix locally
- Re-upload with same filename
- Clear audit trail
---
### Category 8: Agency Workflow Mistakes
#### Mistake 8.1: Different Naming for Same Tracking ID
**What Happens:**
```
Master: nutella_summer_hero.mp4, tracking=pOiJ9s
Derivative 1: NUT_SUMMER_OLV_30S_16x9_MST_DE_de_pOiJ9s.mp4 ✅
Derivative 2: NUT_SOMMER_OLV_30S_16x9_MST_FR_fr_pOiJ9s.mp4 ❌
↑ SOMMER instead of SUMMER (different subject)
Both use tracking ID pOiJ9s but different subjects!
Result:
- Both parse successfully
- Both upload successfully
- But inconsistent naming
- Search confusion
- Brand guideline violation
```
**Lesson:** All derivatives of same master should use SAME subject title
---
#### Mistake 8.2: Wrong File Type/Extension
**What Happens:**
```
Master: nutella_hero.mp4 (video)
Derivative: Agency creates as .mov, uploads as:
6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_pOiJ9s.mov
Parse: ✅ Succeeds
Upload: ✅ May succeed
BUT: Extension mismatch
- DAM expects .mp4 based on master
- Got .mov instead
- May cause downstream issues
- Playback compatibility questions
```
**Best Practice:** Match master file type unless specifically requested otherwise
---
### Category 9: Approval Process Errors
#### Mistake 9.1: Rejecting Without Comments
**What Happens:**
```
Approver marks: ECOMMERCE STATUS = "NOT APPROVED"
Approver doesn't add comments (lazy)
A5→A6 downloads asset
Email to agency:
"Asset rejected:
Rejection Comments:
(none provided)"
Agency: "Why was this rejected? What do I fix?"
Wasted time asking approver
Delayed turnaround
```
**Best Practice:** Always add detailed rejection comments
---
#### Mistake 9.2: Approving When Should Be Rejected
**What Happens:**
```
Asset has compliance issue (missing disclaimer)
Approver misses it
Marks: APPROVED
Status: A4
Asset goes live with compliance issue
Legal discovers issue post-launch
Emergency takedown required
Much bigger problem than rejecting early
Cost: High (legal risk, brand reputation)
```
**Lesson:** Better to reject and fix than approve and regret
---
## Monitoring & Health Checks
### Email Notifications: What They Mean
#### Green Emails (Success)
**"✅ Master Assets Downloaded"** (A1→A2)
```
Means:
- Campaign found with status A1
- All masters downloaded successfully
- Uploaded to Box with tracking IDs
- Stored in database
- Status updated A1→A2
- Ready for agency localization
Your Action:
- Forward Box URLs to agency
- Monitor for A2→A3 completion
```
**"✅ Asset Uploaded to DAM"** (A2→A3)
```
Means:
- Derivative processed successfully
- Uploaded to DAM Final Assets
- Metadata applied correctly
- CreativeX score attached (or defaults used)
- Deleted from Box
Your Action:
- None (automatic)
- Check for CreativeX warning if present
```
**"✅ Global Master Assets Downloaded"** (B1→B2)
```
Means:
- Global campaign processed
- Assets ready for distribution
- Available in Box
- No webhook sent (expected)
Your Action:
- Notify regional teams
```
#### Orange Emails (Partial Success / Warnings)
**"⚠️ Partial Download"**
```
Means:
- Some assets succeeded, some failed
- Status NOT updated (campaign still at previous status)
- Failed assets will retry on next run
Your Action:
- Review failed assets in email
- Check logs for error details
- May need to fix issues in DAM
- Wait for retry (5 minutes)
```
**"⚠️ CreativeX Score Missing"** (in A2→A3 email)
```
Means:
- Asset uploaded successfully
- But no CreativeX score found in database
- Used defaults: Score=0, placeholder URL
- Everything else worked fine
Your Action:
- Upload CreativeX PDF if needed
- Or accept score=0 if not critical
```
#### Red Emails (Errors)
**"❌ Upload Failed"**
```
Means:
- Critical error during processing
- File NOT uploaded to DAM
- Still in Box for retry
Your Action:
- Check error message in email
- Review logs: tail -100 logs/a2_to_a3.log
- Fix underlying issue
- File will retry automatically
```
### Daily Report Email (7:00 PM)
**"📊 Ferrero Automation Daily Report"**
**What It Contains:**
```
Overall Summary (Last 24 Hours):
- Campaigns Found: 5
- Campaigns Processed: 5
- ✅ Completed: 4
- ⚠️ Partial: 1
- Total Assets: 50
- ✓ Successful: 48
- ✗ Failed: 2
- Success Rate: 96.0%
Workflow Breakdown:
A1→A2: 2 campaigns, 20 assets, 20 successful
A2→A3: 3 campaigns, 25 assets, 23 successful, 2 failed
A5→A6: 1 campaign, 5 assets (rejections), 5 successful
B1→B2: 0 campaigns (no global campaigns today)
Errors (if any):
- Asset XYZ failed: Network timeout
- Asset ABC failed: Invalid tracking ID
```
**How to Read It:**
- Success rate >95%: ✅ Healthy
- Success rate 80-95%: ⚠️ Investigate errors
- Success rate <80%: 🔴 System issues, escalate
---
### Log Files
**Location:** `/opt/ferrero-automation/Python-Version/logs/`
**Log Files:**
```
a1_to_a2.log - Master downloads
a2_to_a3.log - Derivative uploads
a5_to_a6.log - Rejection downloads
b1_to_b2.log - Global masters
creativex_scoring.log - CreativeX extraction
daily_report.log - Daily report generation
backup.log - Database backups
cron_*.log - Cron execution logs
```
**Log Rotation:**
- Max size: 10MB per file
- Backups: 28 rotated files kept
- Total: ~280MB per workflow
- Auto-cleanup: Old logs deleted
**Useful Log Commands:**
```bash
# Watch live
tail -f logs/a2_to_a3.log
# Check for errors today
grep -i error logs/*.log | grep "$(date +%Y-%m-%d)"
# Count successful uploads today
grep "✓ Success" logs/a2_to_a3.log | grep "$(date +%Y-%m-%d)" | wc -l
# Find specific tracking ID
grep "pOiJ9s" logs/*.log
# See recent activity
tail -100 logs/a1_to_a2.log
```
---
## Troubleshooting Guide
### Symptom-Based Troubleshooting
#### "My file isn't being processed"
**Check 1: Is it in the right Box folder?**
```bash
# Verify folder ID in email
A2→A3 folder: 348526703108
CreativeX folder: 350605024645
```
**Check 2: Does the filename parse correctly?**
```bash
# Test parsing locally
python3 -c "
from scripts.shared.filename_parser import FilenameParser
parser = FilenameParser()
result = parser.parse_filename('YOUR_FILENAME.mp4')
print('Valid:', result['is_valid'])
print('Errors:', result['validation_errors'])
print('Tracking ID:', result['tracking_id'])
"
```
**Check 3: Does the tracking ID exist in database?**
```bash
PGPASSWORD=xxx psql -h localhost -p 5437 -U ferrero_user -d ferrero_tracking -c "
SELECT tracking_id, original_filename
FROM master_assets
WHERE tracking_id = 'pOiJ9s';
"
```
**Check 4: Check the logs**
```bash
grep "YOUR_FILENAME" logs/a2_to_a3.log
```
---
#### "CreativeX score shows 0 instead of actual score"
**Check 1: Is score in database?**
```bash
PGPASSWORD=xxx psql -h localhost -p 5437 -U ferrero_user -d ferrero_tracking -c "
SELECT filename, quality_score, status
FROM creativex_scores
WHERE filename LIKE '%YOUR_FILENAME%';
"
```
**Check 2: Does filename match EXACTLY?**
```
Database has: 6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_pOiJ9s.mp4
Lookup uses: 6487512_NUT_SUMMER_OLV_30S_16x9_MST_DE_de_pOiJ9s.mp4
Match: Must be character-for-character identical
```
**Check 3: Is status = 'active'?**
```bash
# Query filters for status='active' only
# Superseded scores are not used
SELECT status FROM creativex_scores WHERE filename = 'exact_name.mp4';
```
**Fix:**
- Upload CreativeX PDF with exact filename
- Run creativex_scoring_storing.py
- Re-upload derivative (will pick up score)
---
#### "Asset uploaded with wrong metadata"
**Check 1: Was correct tracking ID used?**
```bash
# Check what master the tracking ID links to
PGPASSWORD=xxx psql -h localhost -p 5437 -U ferrero_user -d ferrero_tracking -c "
SELECT tracking_id, original_filename,
full_metadata->'metadata'->>'id' as metadata_model
FROM master_assets
WHERE tracking_id = 'abc123';
"
```
**Check 2: Check the --dryrun output**
```bash
# See exactly what metadata would be sent
python scripts/a2_to_a3_upload_polling.py --dryrun
# Review the JSON output
# Verify each field has correct value
```
**Fix:**
- Verify tracking ID is correct
- Check field_mappings.yaml configuration
- Re-upload with corrections
---
### Emergency Procedures
#### Emergency 1: Database Corruption
**Symptoms:**
- Scripts crashing with database errors
- Queries returning unexpected results
- Table structure errors
**Steps:**
1. Stop all workflows immediately
```bash
crontab -e # Comment out all cron jobs
```
2. Create emergency backup
```bash
./database/backup.sh --daily
```
3. Restore from last known good backup
```bash
./database/restore.sh backups/dumps/ferrero_tracking_2025-11-13_02-00.sql.gz
```
4. Verify restoration
```bash
PGPASSWORD=xxx psql -h localhost -p 5437 -U ferrero_user -d ferrero_tracking -c "
SELECT COUNT(*) FROM master_assets;
SELECT COUNT(*) FROM creativex_scores;
"
```
5. Resume workflows
```bash
crontab -e # Uncomment cron jobs
```
6. Notify team of data loss window (time between backup and corruption)
---
#### Emergency 2: All Workflows Failing
**Symptoms:**
- Multiple error emails
- No successful uploads for hours
- Logs show authentication failures
**Steps:**
1. Test connections
```bash
python scripts/test_connection.py
python scripts/test_connection.py --auth-pfx
```
2. Check credentials in .env
```bash
grep "DAM_" .env | grep -v PASSWORD
grep "BOX_" .env | grep -v PASSWORD | grep -v KEY
```
3. Check external services
- DAM: Is it accessible? Network issues?
- Box: API status page check
- Database: Container running?
4. Check disk space
```bash
df -h /opt/ferrero-automation
```
5. Review recent changes
```bash
git log --oneline | head -10
```
6. Rollback if recent deployment caused issues
```bash
git revert
```
---
## Glossary
**A1, A2, A3, A4, A5, A6** - Campaign status codes indicating workflow stage
**Asset Representation** - JSON structure containing all metadata fields for DAM upload
**Tracking ID** - 6-character unique identifier linking derivative to master asset
**Master Asset** - Original creative asset from campaign creation
**Derivative Asset** - Localized version of master asset
**JSONB** - PostgreSQL data type for storing JSON with queryable fields
**MVP Fields** - 30 core metadata fields required for asset upload
**Soft Delete** - Marking records as 'superseded' instead of deleting (preserves history)
**CreativeX Score** - Quality score (0-100) from CreativeX AI analysis
**OMG Job Number** - Project identifier from OMG project management system
**Folder-Only Mode** - Tracking ID with -N suffix, inherits only upload folder
**LlamaExtract** - AI service for extracting data from PDF documents
**pg_dump** - PostgreSQL backup tool creating SQL dump
**Cron** - Linux job scheduler running scripts on schedule
---
## FAQ
**Q: Can I edit a filename after upload to Box?**
A: No. The script processes based on exact filename at time of discovery. Editing causes mismatches.
**Q: What if I upload the wrong tracking ID?**
A: Delete the file from Box, rename with correct tracking ID, re-upload.
**Q: Can the same master have multiple derivatives with different tracking IDs?**
A: No. One master = one tracking ID. All derivatives of that master use the same tracking ID.
**Q: What happens if CreativeX PDF is never uploaded?**
A: Asset uploads successfully with default score=0. Functional but not scored.
**Q: Can I skip the naming tool and create filenames manually?**
A: Technically yes if you know the exact V2.1 format, but NOT recommended. Tool prevents errors.
**Q: How long until my uploaded file is processed?**
A: Maximum 5 minutes (next cron run). Usually 1-3 minutes.
**Q: What if my file is stuck in Box for > 10 minutes?**
A: Check logs for errors, verify filename format, check tracking ID in database.
**Q: Can I upload multiple derivatives at once?**
A: Yes! A2→A3 processes one file per run but runs every 5 minutes. All will be processed within minutes.
**Q: What's the maximum file size?**
A: Limited by Box upload limits and network bandwidth. Tested with files up to 500MB successfully.
---
## Quick Reference
### Box Folder IDs
| Workflow | Folder ID | Purpose |
|----------|-----------|---------|
| A1→A2 | 348304357505 | Master downloads (local campaigns) |
| A2→A3 | 348526703108 | Agency derivative uploads |
| A5→A6 | 349441822875 | Rejected asset downloads |
| B1→B2 | 349261192115 | Global master downloads |
| CreativeX | 350605024645 | CreativeX PDF reports |
### Status Codes
| Code | Meaning | Next Step |
|------|---------|-----------|
| A1 | Campaign created, masters ready | A1→A2 automation |
| A2 | Masters in Box, ready for localization | Agency work |
| A3 | Derivatives uploaded, ready for approval | Approval process |
| A4 | Approved, ready for distribution | Go live |
| A5 | Rejected, needs rework | A5→A6 automation |
| A6 | Rework in progress | Agency fixes, then A2→A3 |
| B1 | Global masters ready | B1→B2 automation |
| B2 | Global masters distributed | Regional teams |
### Script Commands
```bash
# Manual execution
python scripts/a1_to_a2_download.py
python scripts/a2_to_a3_upload_polling.py
python scripts/a5_to_a6_download.py
python scripts/b1_to_b2_download.py
python scripts/creativex_scoring_storing.py
# With flags
python scripts/a2_to_a3_upload_polling.py --dryrun # Test mode
python scripts/a1_to_a2_download.py --auth-pfx # mTLS auth
python scripts/a2_to_a3_upload_polling.py --keep-files # Don't delete from Box
# Database operations
./database/backup.sh --daily
./database/restore.sh --list
./database/check_backups.sh
# Monitoring
tail -f logs/a2_to_a3.log # Watch logs
grep -i error logs/*.log | grep "$(date +%Y-%m-%d)" # Today's errors
```
### Database Queries
```bash
# Recent master assets
PGPASSWORD=xxx psql -h localhost -p 5437 -U ferrero_user -d ferrero_tracking -c "
SELECT tracking_id, original_filename, created_at
FROM master_assets
ORDER BY created_at DESC LIMIT 10;
"
# Recent uploads
PGPASSWORD=xxx psql -h localhost -p 5437 -U ferrero_user -d ferrero_tracking -c "
SELECT tracking_id, derivative_filename, uploaded_at
FROM derivative_assets
ORDER BY uploaded_at DESC LIMIT 10;
"
# Active CreativeX scores
PGPASSWORD=xxx psql -h localhost -p 5437 -U ferrero_user -d ferrero_tracking -c "
SELECT filename, quality_score, extracted_at
FROM creativex_scores
WHERE status = 'active'
ORDER BY extracted_at DESC LIMIT 10;
"
# Campaign status
PGPASSWORD=xxx psql -h localhost -p 5437 -U ferrero_user -d ferrero_tracking -c "
SELECT campaign_number, status, webhook_sent, updated_at
FROM campaign_status
ORDER BY updated_at DESC LIMIT 10;
"
```
---
## Contact Information
### Escalation Path
**Level 1: Self-Service**
- Check this guide
- Review logs
- Check email notifications
- Query database
**Level 2: Operations Team**
- Email: operations@oliver.agency
- Response time: Within 2 hours (business hours)
- Can: Review logs, check database, restart workflows
**Level 3: Technical Lead**
- Email: daveporter@oliver.agency
- Response time: Within 4 hours
- Can: Code fixes, configuration changes, database recovery
**Level 4: Ferrero IT**
- Contact: [To be provided]
- Response time: As per SLA
- Can: DAM access issues, network problems, credential resets
### Support Resources
**Documentation:**
- COMPLETE_WORKFLOW_GUIDE.md (this document)
- CREATIVEX_DEPLOYMENT.md (CreativeX setup)
- DATABASE_BACKUP_GUIDE.md (Backup/restore)
- CUTOVER.md (Production deployment)
- CUTOVER-TODOS.md (Deployment checklist)
**Logs:**
- `/opt/ferrero-automation/Python-Version/logs/`
**Database:**
- Host: localhost:5437
- Database: ferrero_tracking
- User: ferrero_user
**Monitoring:**
- Daily email reports (7:00 PM)
- Real-time notifications
- Log files
---
## Summary: The Golden Rules
### For Campaign Managers
1. ✅ Set campaign to A1 when ready
2. ✅ Wait for automation (don't rush)
3. ✅ Forward Box URLs to agency promptly
4. ✅ Monitor email notifications
5. ✅ Review daily reports
6. ❌ Don't manually move files between DAM folders
7. ❌ Don't change status codes manually
### For Agencies
1. ✅ **ALWAYS use the Naming Convention Tool**
2. ✅ Copy filename EXACTLY from tool (don't type)
3. ✅ Wait for A1→A2 email before starting
4. ✅ Get tracking IDs from email, not elsewhere
5. ✅ Upload CreativeX PDFs before derivatives
6. ✅ Upload to correct Box folder (check email URL)
7. ✅ Use SAME tracking ID for rework
8. ❌ Don't manually type filenames
9. ❌ Don't reuse tracking IDs across campaigns
10. ❌ Don't upload directly to DAM
### For Approvers
1. ✅ Always add detailed rejection comments
2. ✅ Be specific about what needs fixing
3. ✅ Check compliance before approving
4. ❌ Don't reject without explaining why
### For Operations
1. ✅ Monitor daily reports
2. ✅ Check backup health weekly
3. ✅ Review error patterns
4. ✅ Test restore monthly
5. ✅ Keep documentation updated
---
## Document Version History
**Version 2.1** - November 2025
- Updated for V2.1 filename structure
- Added social media field
- Added asset type mapping
- Added CreativeX master score extraction
- Complete workflow overhaul documentation
**Version 2.0** - October 2025
- Initial comprehensive guide
- All workflows documented
- Mermaid diagrams added
---
**End of Document**
**Total Pages:** ~65 pages (when printed)
**Last Updated:** November 2025
**Maintained By:** Operations Team
**Review Cycle:** Quarterly or after major changes
For questions or feedback, contact: operations@oliver.agency