DJP 20a187a61d feat: Add Python project dependencies.

2025-11-20 22:45:04 -05:00

107 KiB

Raw Permalink Blame History

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)

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<br/>pOiJ9s, a7K9mP, bvSGyd]
        A1_TRACKING --> A1_BOX[Upload to Box with tracking IDs<br/>nutella_hero_pOiJ9s.mp4]
        A1_BOX --> A1_DB[Store in database with full metadata]
        A1_DB --> A1_CX{CreativeX in<br/>master metadata?}
        A1_CX -->|Yes| A1_CX_STORE[Store master score<br/>status='master-cx-score']
        A1_CX -->|No| A1_CX_SKIP[Log: No CreativeX normal]
        A1_CX_STORE --> A1_CHECK{All assets<br/>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<br/>DE, FR, IT versions + aspect ratios]
        AG_CREATE --> AG_CREATIVEX[🔴 CRITICAL: Submit EVERY derivative to CreativeX<br/>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<br/>status='active']
        AG_SCORE_DB --> AG_VERIFY[Verify: derivative count = score count ✅]
        AG_VERIFY --> AG_NAMING[Use Naming Tool for EACH derivative<br/>Copy exact filename]
        AG_NAMING --> AG_UPLOAD[Upload to Box A2→A3 folder<br/>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<br/>Email warning sent]
        A2_CX_USE --> A2_BUILD[Build asset representation]
        A2_CX_DEFAULT --> A2_BUILD
        A2_BUILD --> A2_OVERRIDE[Override from filename:<br/>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:<br/>ECOMMERCE STATUS = NOT APPROVED]
        A5_MARK --> A5_LEGAL[Legal reviewer adds comments:<br/>Missing copyright, compliance issues]
        A5_LEGAL --> A5_IACC[IA&CC reviewer adds comments:<br/>Brand guideline violations]
        A5_IACC --> A5_APPROVER[General approver adds comments:<br/>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<br/>NOT APPROVED assets]
        A5_FILTER --> A5_APPROVED[✅ APPROVED assets: Skipped<br/>Not re-downloaded]
        A5_FILTER --> A5_TRACKING[Lookup EXISTING tracking ID<br/>REUSE: pOiJ9s]
        A5_TRACKING --> A5_DOWNLOAD[Download rejected assets from DAM]
        A5_DOWNLOAD --> A5_BOX_UP[Upload to Box A5→A6 folder<br/>Separate folder from A1→A2]
        A5_BOX_UP --> A5_EMAIL_OUT[Email with ALL rejection comments:<br/>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<br/>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<br/>Old score marked 'superseded']
        RW_DB --> RW_NAMING[Use Naming Tool:<br/>NEW job number + SAME tracking ID]
        RW_NAMING --> RW_EXAMPLE[Example: 7777_NUT_SUMMER_OLV_30S_16x9_DE_de_pOiJ9s.mp4<br/>↑ 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<br/>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:

CreativeX Scoring (Purple): Every derivative MUST be scored individually
Tracking IDs (Blue): Critical link between master and derivatives, reused in rework
Rejection Comments (Red): Legal, IA&CC, and approver feedback guide fixes
Rework Loop (Orange): Can repeat until approved - same tracking ID maintained

Executive Overview
System Architecture
The Complete Asset Lifecycle
The Naming Convention Tool
Python Automation Scripts
Detailed Workflow Walkthroughs
CreativeX Integration
The Database
Common Mistakes & Pitfalls
Monitoring & Health Checks
Troubleshooting Guide
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

graph TB
    subgraph Frontend["User-Facing Tools"]
        NT[Naming Convention Tool<br/>public-v2/]
        BOX[Box.com Folders<br/>4 separate folders]
        EMAIL[Email Notifications<br/>Success/Error/Reports]
    end

    subgraph Backend["Automation Layer"]
        A1A2[a1_to_a2_download.py<br/>Master Downloads]
        A2A3[a2_to_a3_upload_polling.py<br/>Derivative Uploads]
        A5A6[a5_to_a6_download.py<br/>Rejections/Rework]
        B1B2[b1_to_b2_download.py<br/>Global Masters]
        CX[creativex_scoring_storing.py<br/>Score Extraction]
        DR[daily_report.py<br/>Reporting]
    end

    subgraph Storage["Data Layer"]
        DAM[(OpenText DAM<br/>Asset Repository)]
        DB[(PostgreSQL<br/>Tracking Database)]
        BACKUP[(Daily Backups<br/>7-day retention)]
    end

    subgraph External["External Services"]
        LLAMA[LlamaExtract AI<br/>CreativeX PDF parsing]
        WEBHOOK[Make.com Webhooks<br/>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:

graph TB
    subgraph L1["Layer 1: User Interface"]
        UI1[DAM Web Interface<br/>Campaign & Asset Management]
        UI2[Naming Convention Tool<br/>Filename Generation]
        UI3[Box.com Interface<br/>File Upload/Download]
    end

    subgraph L2["Layer 2: Automation Scripts (Python)"]
        S1[Workflow Scripts<br/>A1→A2, A2→A3, A5→A6, B1→B2]
        S2[Support Scripts<br/>CreativeX, Daily Reports]
        S3[Shared Modules<br/>DAM Client, Box Client, Database]
    end

    subgraph L3["Layer 3: Data Storage"]
        D1[(PostgreSQL Database<br/>Tracking & Metadata)]
        D2[(Box Storage<br/>File Transfer)]
        D3[(DAM Storage<br/>Asset Repository)]
    end

    subgraph L4["Layer 4: External Services"]
        E1[LlamaExtract AI<br/>PDF Parsing]
        E2[Mailgun SMTP<br/>Email Delivery]
        E3[Make.com<br/>Webhook Automation]
    end

    subgraph L5["Layer 5: Infrastructure"]
        I1[Cron Scheduler<br/>Every 5 minutes]
        I2[Backup System<br/>Daily + Weekly]
        I3[Log Management<br/>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

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:

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
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.)
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

sequenceDiagram
    autonumber
    participant DAM as OpenText DAM
    participant Script as a1_to_a2_download.py<br/>(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<br/>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

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

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):

{
    '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

SELECT * FROM master_assets
WHERE tracking_id = 'pOiJ9s'

Returns: Complete master metadata, upload folder ID, campaign info

Lookup 2: CreativeX Score

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:

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:

Assets reach A3 status after upload
Designated approvers review in DAM
Check quality, compliance, brand guidelines
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

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:<br/>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:

# 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:

# 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:

# 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

flowchart TD
    A[Global Campaign Created] --> B[Type = Global comm]
    B --> C[Assets in Final Assets folder<br/>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<br/>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:

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

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:

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:

# 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

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<br/>DAM API + OAuth2/mTLS]
        BOX[box_client.py<br/>Box JWT + Upload/Download]
        DB[database.py<br/>PostgreSQL Pool]
        NOT[notifier.py<br/>Email + Webhooks]
        PARSER[filename_parser.py<br/>V2.1 Parsing]
        MVP[metadata_extractor_mvp.py<br/>Field Mapping]
        CONFIG[config_loader.py<br/>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:

# 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:

# 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:

# 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:

# 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:

# 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:

# 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

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:<br/>- Analytics<br/>- Comparison<br/>- 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:

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
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)
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
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:

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:

Client expectations:
- Contract may require all assets scored
- Quality standards apply to ALL deliverables
- Score=0 looks unprofessional
Reporting incomplete:
- Can't analyze which markets perform best (only have 10% of data)
- Can't identify improvement opportunities
- ROI tracking impossible
Selective scoring = inconsistent quality:
- Some assets optimized, others not
- Performance disparity
- Brand inconsistency
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:

Internal reference assets (not client-facing)
Template files (not final deliverables)
Work-in-progress (will be scored before final delivery)
Non-creative assets (legal documents, spreadsheets)

But for client deliverables: ALL must be scored.

How to Verify All Assets Are Scored

Before Mass Upload:

# 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:

# 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

# Manual execution (or can be added to cron)
python scripts/creativex_scoring_storing.py

Script Process:

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:

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

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:

-- 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:

-- 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:

# 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):

# 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):

# 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):

# Checks backup age, warns if > 25 hours old
./database/check_backups.sh

Restore Process:

# 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:

Run script manually after PDF upload
Add to cron for automatic processing
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:

# 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?

# Verify folder ID in email
A2→A3 folder: 348526703108
CreativeX folder: 350605024645

Check 2: Does the filename parse correctly?

# 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?

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

grep "YOUR_FILENAME" logs/a2_to_a3.log

"CreativeX score shows 0 instead of actual score"

Check 1: Is score in database?

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'?

# 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?

# 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

# 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:

Stop all workflows immediately

crontab -e  # Comment out all cron jobs

Create emergency backup

./database/backup.sh --daily

Restore from last known good backup

./database/restore.sh backups/dumps/ferrero_tracking_2025-11-13_02-00.sql.gz

Verify restoration

PGPASSWORD=xxx psql -h localhost -p 5437 -U ferrero_user -d ferrero_tracking -c "
SELECT COUNT(*) FROM master_assets;
SELECT COUNT(*) FROM creativex_scores;
"

Resume workflows

crontab -e  # Uncomment cron jobs

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:

Test connections

python scripts/test_connection.py
python scripts/test_connection.py --auth-pfx

Check credentials in .env

grep "DAM_" .env | grep -v PASSWORD
grep "BOX_" .env | grep -v PASSWORD | grep -v KEY

Check external services

DAM: Is it accessible? Network issues?
Box: API status page check
Database: Container running?

Check disk space

df -h /opt/ferrero-automation

Review recent changes

git log --oneline | head -10

Rollback if recent deployment caused issues

git revert <commit-hash>

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

# 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

# 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

✅ Set campaign to A1 when ready
✅ Wait for automation (don't rush)
✅ Forward Box URLs to agency promptly
✅ Monitor email notifications
✅ Review daily reports
❌ Don't manually move files between DAM folders
❌ Don't change status codes manually

For Agencies

✅ ALWAYS use the Naming Convention Tool
✅ Copy filename EXACTLY from tool (don't type)
✅ Wait for A1→A2 email before starting
✅ Get tracking IDs from email, not elsewhere
✅ Upload CreativeX PDFs before derivatives
✅ Upload to correct Box folder (check email URL)
✅ Use SAME tracking ID for rework
❌ Don't manually type filenames
❌ Don't reuse tracking IDs across campaigns
❌ Don't upload directly to DAM

For Approvers

✅ Always add detailed rejection comments
✅ Be specific about what needs fixing
✅ Check compliance before approving
❌ Don't reject without explaining why

For Operations

✅ Monitor daily reports
✅ Check backup health weekly
✅ Review error patterns
✅ Test restore monthly
✅ 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

107 KiB Raw Permalink Blame History Unescape Escape

Ferrero DAM Asset Production Workflow

Complete System Guide

Complete Workflow Overview: A1 → A4 (with A5-A6 Rejection Cycle)

Table of Contents

Executive Overview

What is This System?

The Problem It Solves

Key Benefits

System Components

Stakeholders & Their Roles

Success Metrics

System Architecture

High-Level Architecture

Physical Deployment

Data Flow Overview

The Complete Asset Lifecycle

Overview: From Brief to Live Asset

Phase 1: Campaign Creation & Master Asset Development

Phase 2: Master Asset Download (A1→A2) - AUTOMATED

Phase 3: Agency Localization

Phase 4: Derivative Asset Upload (A2→A3) - AUTOMATED

Phase 5: Approval & Distribution (A3→A4)

Phase 6: Rejection & Rework Cycle (A5→A6→A2→A3)

Workflow 4: Global Master Campaigns (B1→B2)

The Naming Convention Tool

Location & Purpose

Why Naming is CRITICAL

V2.1 Filename Structure

1. Job Number (OMG Job)

2. Brand Code

3. Subject Title

4. Asset Type

5. Duration (Optional)

6. Aspect Ratio

7. Spot Version (Optional)

8. Country Code

9. Language Code

10. Social Media Version (Optional)

11. Tracking ID

Using the Naming Tool: Step-by-Step

Tool Validation Rules

What Happens When Naming is Wrong

Python Automation Scripts

Execution Schedule (Cron)

Script Dependencies

Shared Modules Explained

dam_client.py - DAM API Client

box_client.py - Box.com Integration

database.py - PostgreSQL Operations

filename_parser.py - V2.1 Parser

metadata_extractor_mvp.py - Field Mapping

CreativeX Integration

What is CreativeX?

Two Sources of CreativeX Scores

🔴 CRITICAL REQUIREMENT: Every Derivative Needs Its Own CreativeX Score

The Rule: One Asset = One Score

The Math

What Happens If You Don't Score All Assets

The Workflow: Scoring BEFORE Upload

Common Mistake: "I'll Score the Important Ones Only"

Batch Processing CreativeX

Exceptions to the Rule

How to Verify All Assets Are Scored

CreativeX PDF Extraction Workflow

Master vs Derivative Scores

The Database

Database Architecture

Table Purposes

master_assets (35 columns)

derivative_assets

creativex_scores

campaign_status

Backup Strategy

Common Mistakes & Pitfalls

Category 1: Filename Errors

Mistake 1.1: Using Old V1 Field Order

Mistake 1.2: Manual Typing Instead of Copy/Paste

Mistake 1.3: Wrong Tracking ID (Copy/Paste Error)

Mistake 1.4: Reusing Tracking ID from Different Campaign

107 KiB

Raw Permalink Blame History