YouTube Ads was missing from the DAM-CX mappings CSV, causing empty
Platform > Rating fields for YouTube assets. Also adds a fallback that
derives the CreativeX platform from the filename social media code (e.g.
YTA -> YouTube) when the database has no mapped platforms.
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
CreativeX lookup now falls back to tracking ID search when filename match fails
(handles mismatched naming from CreativeX PDFs). strip_upload_components now
only removes job number and tracking ID, keeping social media codes (YTA, DV3,
etc.) in the clean filename. Updated SOCIAL_MEDIA_CODES from 4 to 39 codes
sourced from the Ferrero naming tool.
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
The notifier variable was referenced inside process_box_file but never passed
as a parameter, causing NameError for any file hitting the Master Tracking ID
check. Also changed the check from case-insensitive (.upper().startswith('M'))
to case-sensitive (.startswith('M')) to avoid false positives on random tracking
IDs like mviSv5.
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
A1→A2 now handles re-processing when campaign is reset to A1 after adding new
master assets. Existing assets reuse tracking IDs and skip Box upload, new assets
are processed normally. Also includes PPR domain registration for multiple master
asset IDs in a2_to_a3 and dam_client.
Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
Fixed issue where only 1 of 3 master asset IDs was being added to the
FERRERO.MASTERASSETIDS tabular field. The bug was caused by calling
_add_master_asset_id_field() before _add_master_asset_ids_field(),
which created the field with a single value and blocked the multi-value
method from adding all IDs.
Changes:
- metadata_extractor_mvp.py: Prioritize master_opentext_ids parameter
using if/elif logic to prevent single-ID method from blocking multi-ID
- a2_to_a3_upload_polling.py: Load multiple master assets in PPR mode
- filename_parser.py: Parse multiple tracking IDs (e.g., ID1+ID2+ID3)
- query_db.py: Fix .env loading path
- Added documentation and test files for multiple master asset IDs
Tested in PPR with 3 tracking IDs (BqB8vo+SfUQ7m+laRJo0) - all 3 master
asset IDs now correctly appear in the metadata structure.
Co-Authored-By: Claude Sonnet 4.5 <noreply@anthropic.com>
Reverted master_asset_id changes per user feedback:
- tracking_id already links derivatives to masters
- No need for additional master_asset_id foreign key
- Only storing dam_asset_id for DAM asset tracking
Changes:
- Reverted get_master_asset() to not return database 'id'
- Updated store_derivative_asset() to only INSERT dam_asset_id
- Updated a2_to_a3_upload_polling.py to pass None for master_asset_id
- Removed master_asset_id from INSERT statement
Note: Migration script still needed for dam_asset_id column only
Email Template Fix:
- Fixed subject line syntax error in a2_to_a3_batch_complete template
- Removed Jinja2 control flow ({% if %}) from subject line
- Changed to simple expression-only format
- Fixes 'Failed to send email' error
Database Logging Fix:
- Updated get_master_asset() to return database primary key 'id'
- Updated store_derivative_asset() to actually store master_asset_id and dam_asset_id
- Updated a2_to_a3_upload_polling.py to pass master_asset['id'] instead of None
- Added migration script to add dam_asset_id column to derivative_assets table
- Fixes issue where derivatives weren't being linked to masters in database
- Enables proper lookups and tracking of uploaded derivatives
Impact:
- Email notifications will now send successfully
- Derivatives will be properly logged and linked to master assets
- Other tools can now find uploaded derivatives in database
- Added ARTESIA.FIELD.ASSET_ID to MVP fields in field_mappings.yaml
- Updated metadata_extractor_mvp.py to accept master_opentext_id parameter
- Added _add_master_asset_id_field() and _get_field_id() helper methods
- Modified a2_to_a3_upload_polling.py to pass master asset's opentext_id
- Field is populated with original master asset's DAM ID for derivative tracking
- Field is omitted for new assets (tracking ID with -N suffix)
- Covers both A2→A3 standard derivatives and A5→A6 reworked assets
- Updated creativex_scoring_storing.py to map multiple placements to platforms
- Modified get_mapped_platform to get_mapped_platforms (returns list)
- Updated a2_to_a3_upload_polling.py to retrieve platforms list from DB
- Enhanced metadata_extractor_mvp.py to build multi-value CreativeX field
- Added DAM-CX mappings.csv for channel/placement to platform mapping
- Supports single channel with multiple placements generating multiple Platform^Score values
Changes database lookup strategy to match on full filename as it appears
in Box and in the CreativeX PDF report filename field.
Critical Design Change:
Old (incorrect):
- Strip job number and tracking ID from Box filename
- Lookup: NUT_PL_pl_TEST-E2E_EHI_1x1.png
- Database has: 6487512_NUT_PL_pl_TEST-E2E_EHI_1x1_7xXgKp.png
- RESULT: No match found, uses defaults
New (correct):
- Use original Box filename for lookup
- Lookup: 6487512_NUT_PL_pl_TEST-E2E_EHI_1x1_7xXgKp.png
- Database has: 6487512_NUT_PL_pl_TEST-E2E_EHI_1x1_7xXgKp.png
- RESULT: Match found, uses actual score
Rationale:
The CreativeX PDF report contains a "filename" field that stores the
actual asset filename including job number and tracking ID. This is
the name that gets extracted by LlamaExtract and stored in database.
The A2→A3 workflow receives files from Box with the SAME filename
structure (job_brand_country_lang_subject_trackingID.ext).
Therefore, we match on the complete original filename, not the stripped
version.
Database Storage Pattern:
- CreativeX PDF named: anything.pdf (name doesn't matter)
- PDF contains field: filename = "6487512_NUT_PL_pl_TEST-E2E_EHI_1x1_7xXgKp.png"
- Database stores: filename = "6487512_NUT_PL_pl_TEST-E2E_EHI_1x1_7xXgKp.png"
- A2→A3 receives: 6487512_NUT_PL_pl_TEST-E2E_EHI_1x1_7xXgKp.png from Box
- Lookup matches exactly
Clean filename still used for DAM upload, only the lookup is on original.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
Co-Authored-By: Claude <noreply@anthropic.com>
Adds debugging mode to A2→A3 workflow that builds full asset metadata
but doesn't upload to DAM, displaying complete JSON for field validation.
Changes to A2→A3 Script (a2_to_a3_upload_polling.py):
--dryrun Flag:
- New argument: --dryrun (build metadata but don't upload)
- Displays full asset representation as formatted JSON
- Shows field count
- Shows CreativeX lookup status
- Keeps file in Box (no deletion)
- Logs "DRYRUN MODE" clearly
- Returns success with 'DRYRUN_NO_UPLOAD' as asset_id
Dryrun Output Includes:
- Complete asset_representation JSON (all MVP fields)
- Field count (should be 27 fields)
- CreativeX status (found/missing)
- CreativeX score and URL values
- Clean separation with === lines
Usage:
python scripts/a2_to_a3_upload_polling.py --dryrun
Benefits:
- Debug metadata issues without DAM uploads
- Verify all fields present before going live
- Check CreativeX integration working
- Validate field values and formatting
- Safe testing with production data
Changes to Field Mappings (config/field_mappings.yaml):
Agency Name Fixed:
- Changed: FERRERO.MARKETING.FIELD.AGENCY NAME: "-"
- To: FERRERO.MARKETING.FIELD.AGENCY NAME: "Oliver"
- Exact case as required by DAM
- Comment updated to reflect this is final value
Impact:
- All A2→A3 uploads now have Agency Name = "Oliver"
- Not "Oliver Agency" (wrong)
- Not "-" placeholder (old)
- Exact case: "Oliver" (capital O, lowercase liver)
Use Case:
Run with --dryrun to see full JSON metadata, verify Agency name is
"Oliver", check all 27 MVP fields are present, then remove --dryrun
flag to perform actual uploads.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
Co-Authored-By: Claude <noreply@anthropic.com>
Replaces Box metadata template with database lookup for CreativeX scores,
adding automatic fallback to default values when scores are missing.
Changes to A2→A3 Script (a2_to_a3_upload_polling.py):
- Removed: box.get_file_metadata() for CreativeX data
- Added: db.get_creativex_score_by_filename() database lookup
- Uses clean filename (stripped of OMG Job + Tracking ID) for lookup
- Implements fallback when score not found:
- Default score: 0
- Default URL: https://app.creativex.com/preflight/pretests
- Tracks creativex_found flag for email notifications
- Logs warnings when defaults are used
Email Template Updates (notifier.py):
- Shows "✅ CreativeX Score Added: 85 (from database)" when found
- Shows "⚠️ CreativeX Score: Not found - used default (0)" when missing
- Adds orange warning box when defaults used:
- Lists default values (Score: 0, placeholder URL)
- Provides instructions to add score
- References Box folder 350605024645 and scoring script
Benefits:
- Automatic CreativeX lookup (no manual Box metadata entry)
- Graceful degradation (uploads succeed even without scores)
- Clear notification when scores are missing
- Preserves history (uses latest active version)
- No breaking changes (existing workflow continues to work)
Default Value Strategy:
- Score 0 indicates "not scored" but doesn't block upload
- Placeholder URL is valid CreativeX domain
- Email clearly shows when defaults are used
- Provides actionable instructions for adding scores
Workflow Integration:
1. CreativeX PDFs uploaded to folder 350605024645
2. creativex_scoring_storing.py extracts and stores scores
3. A2→A3 automatically looks up scores by filename
4. Uploads proceed with actual scores OR defaults
5. Email indicates which path was taken
Documentation: A2_A3_CREATIVEX_INTEGRATION.md
🤖 Generated with [Claude Code](https://claude.com/claude-code)
Co-Authored-By: Claude <noreply@anthropic.com>
Major changes:
1. Updated filename_parser.py for new V2 naming convention:
- Spot version now accepts only MST or REF (optional)
- Duration field is now optional
- Tracking ID supports -N suffix for folder-only mode
- Reduced minimum required parts from 9 to 7
- Improved asset type detection logic
2. Added recursive folder scanning to box_client.py:
- New list_folder_files_recursive() method
- Skips first-level job/batch folders
- Preserves folder structure from 2nd level onwards
- Skips hidden folders (starting with . or _)
3. Updated A2→A3 upload workflow:
- Uses recursive folder scanning
- Extracts and logs tracking mode (full vs folder_only)
- Handles subfolder paths for DAM uploads
- Shows folder distribution in logs
4. Added folder-only mode to metadata_extractor_mvp.py:
- New tracking_mode parameter (full/folder_only)
- folder_only mode builds metadata entirely from filename
- New _build_fields_from_filename() method
5. Added DAM subfolder creation to dam_client.py:
- New get_or_create_subfolder_path() method
- Creates matching folder structure in DAM
- Helper methods _find_subfolder_by_name() and _create_folder()
Folder structure behavior:
- Box: DAM-UPLOAD/1234567/Europe/Germany/file.mp4
- DAM: 01. Final Assets/Europe/Germany/file.mp4
- Job folder (1234567) is skipped, structure preserved from 2nd level
🤖 Generated with [Claude Code](https://claude.com/claude-code)
Co-Authored-By: Claude <noreply@anthropic.com>
Allows keeping uploaded files in Box for testing/debugging purposes.
NEW FEATURE: --keep-files Flag
- Optional flag for testing/debugging
- Prevents deletion of files from Box after upload
- Files remain in Box folder after successful DAM upload
USAGE:
Default (delete files after upload):
python scripts/a2_to_a3_upload_polling.py
Keep files in Box (testing):
python scripts/a2_to_a3_upload_polling.py --keep-files
Combined with A3update:
python scripts/a2_to_a3_upload_polling.py --keep-files --A3update
HOW IT WORKS:
1. Upload file to DAM (always happens)
2. Store in database (always happens)
3. If --keep-files flag:
- Skip Box file deletion
- Log: "--keep-files flag set - File kept in Box: filename.jpg"
4. If no flag (default):
- Delete file from Box
- Log: "Deleted file from Box: filename.jpg"
LOGGING:
```
With flag:
--keep-files flag set - File kept in Box: my_file.jpg
Without flag:
Deleted file from Box: my_file.jpg
```
USE CASES:
- Testing: Upload multiple times without re-uploading to Box
- Debugging: Keep files to inspect Box metadata
- Development: Test upload logic without losing files
- Backup: Maintain Box copies during initial testing
PRODUCTION NOTE:
For production, don't use this flag - files should be deleted
after successful upload to avoid duplicates on next run.
BOTH FLAGS TOGETHER:
python scripts/a2_to_a3_upload_polling.py --keep-files --A3update
- Uploads file to DAM
- Keeps file in Box
- Updates campaign A2→A3
- Perfect for end-to-end testing
Changes:
- scripts/a2_to_a3_upload_polling.py
- Added --keep-files flag
- Added keep_files parameter to process_box_file()
- Conditional Box file deletion
- Enhanced logging for both modes
🤖 Generated with [Claude Code](https://claude.com/claude-code)
Co-Authored-By: Claude <noreply@anthropic.com>
Allows manual campaign status update A2→A3 after file upload.
NEW FEATURE: --A3update Flag
- Optional flag for testing purposes
- Forces campaign status update A2→A3 after file upload
- Extracts campaign ID from uploaded asset's metadata
- Updates campaign status in DAM
USAGE:
Default (no status update):
python scripts/a2_to_a3_upload_polling.py
With status update (testing):
python scripts/a2_to_a3_upload_polling.py --A3update
HOW IT WORKS:
1. Upload file to DAM (always happens)
2. If --A3update flag set:
- Extract campaign ID from master asset metadata
- Look in inherited_metadata_collections for campaign container
- Update campaign status A2 → A3
- Log success/failure
LOGGING:
```
--A3update flag set - Attempting to update campaign status
Found campaign ID: abc123def456
Updating campaign status A2 → A3...
✓ Campaign status updated successfully: A2 → A3
```
USE CASES:
- Testing: Quickly update campaign status after single upload
- Manual workflow: Force status update without waiting for all assets
- Development: Test status update functionality
PRODUCTION NOTE:
For production, typically don't use this flag.
Campaign should stay at A2 until ALL localized assets uploaded.
Changes:
- scripts/a2_to_a3_upload_polling.py
- Added argparse import
- Added --A3update flag
- Added campaign status update logic
- Extracts campaign ID from full_metadata
🤖 Generated with [Claude Code](https://claude.com/claude-code)
Co-Authored-By: Claude <noreply@anthropic.com>
Major Feature: Box Metadata Integration
box_client.py:
✅ Added get_file_metadata() method
✅ Reads 'Ferrero-DAM-Metadata' template from Box files
✅ Extracts 'CreativeX Score' and 'CreativeX URL' fields
✅ Returns dict with score and url
a2_to_a3_upload_polling.py:
✅ Calls box.get_file_metadata() before download
✅ Logs Box metadata retrieved
✅ Passes box_metadata to build_mvp_asset_representation()
metadata_extractor_mvp.py:
✅ Added box_metadata parameter to build_mvp_asset_representation()
✅ Added _update_creativex_fields() method
✅ Updates FERRERO.FIELD.CREATIVEX LINK with URL from Box
✅ Logs CreativeX Score (tabular field - needs special handling)
Flow:
1. File uploaded to Box by agency
2. Agency adds metadata using Ferrero-DAM-Metadata template
3. Script reads CreativeX Score and URL from Box metadata
4. Updates MVP fields with Box metadata values
5. Uploads to DAM with CreativeX data
Field Mapping:
- Box: 'CreativeX URL' → DAM: FERRERO.FIELD.CREATIVEX LINK
- Box: 'CreativeX Score' → DAM: FERRERO.TAB.FIELD.CREATIVEX (logged, needs structure)
Next: Test with file that has Box metadata template applied
🤖 Generated with Claude Code
Co-Authored-By: Claude <noreply@anthropic.com>
A2→A3 Email Now Includes:
File Details:
✅ Original filename (from Box with Job# and Tracking ID)
✅ Clean filename (stripped for DAM)
✅ DAM Asset ID
✅ Tracking ID
Processing Details:
✅ Master Asset ID (source asset)
✅ Upload folder ID (where it went in DAM)
✅ Box folder ID (where it came from)
Complete Step-by-Step:
✅ Downloaded from Box (folder 348526703108)
✅ Loaded master metadata from database
✅ Built 27 MVP fields
✅ Updated Description from filename
✅ Updated Language from filename
✅ Set State to Local
✅ Stripped Job# and Tracking ID
✅ Uploaded to DAM
✅ Deleted from Box
Now both A1→A2 and A2→A3 emails are extremely verbose!
🤖 Generated with Claude Code
Major Features Added:
1. Delete Files from Box After Upload
- After successful DAM upload, delete file from Box
- Prevents reprocessing same files
- Keeps Box folder clean
- Only deletes on success (keeps on failure for retry)
2. Email Notification for Each Upload
- New template: a2_to_a3_file_uploaded
- Sends email immediately after each successful upload
- Includes: filename, clean filename, asset ID, tracking ID
- Don't wait for "all done" - notify per file
- Recipients: configured in .env (REPORT_EMAILS)
3. Log Rotation for Both Scripts
- Uses RotatingFileHandler
- Max file size: 10MB per log file
- Backup count: 28 files (approximately 1 month)
- Auto-rotates when log reaches 10MB
- Keeps logs/a1_to_a2.log (current)
- Backups: logs/a1_to_a2.log.1, .2, .3, etc.
- Automatically deletes logs older than 28 rotations
- Applied to both A1→A2 and A2→A3 scripts
Flow Changes:
A2→A3 now:
1. Poll Box folder
2. Find V2 files
3. Download from Box
4. Upload to DAM
5. Delete from Box ✅ NEW
6. Send email notification ✅ NEW
7. Store derivative record
8. Exit
Log Management:
- Active logs: ~10MB max
- Rotated backups: 28 files = ~280MB total
- Automatic cleanup (no manual intervention needed)
- 1 week of detailed logs + 3 weeks of backups
Database:
- Added dam_asset_id and upload_status columns to derivative_assets
- Fixed store_derivative_asset() to use existing schema columns
🤖 Generated with Claude Code
Co-Authored-By: Claude <noreply@anthropic.com>
Created a2_to_a3_upload_polling.py:
- Polls Box folder (348526703108) instead of webhook
- Works locally (no need for public URL)
- Single-run mode (process one file and exit)
- Can be run via cron every 5 minutes
Why Polling Instead of Webhook:
- Webhooks require public URL (doesn't work on localhost)
- Polling works everywhere (local and server)
- Same functionality, different trigger mechanism
Database Fix:
- Don't create new columns (dam_asset_id, upload_status)
- Use existing schema: tracking_id, derivative_filename, file_extension, status
- Simplified store_derivative_asset() to use existing columns only
- Database now compatible with existing schema
Test Results - A2→A3 Polling:
✅ Polls Box folder 348526703108
✅ Finds V2 files with tracking IDs
✅ Downloads from Box
✅ Loads master metadata from PostgreSQL
✅ Builds 27 MVP fields
✅ Updates Description, State, Language from filename
✅ Uploads to DAM successfully (Asset ID: 214924)
✅ Stores derivative record
✅ Processes one file and exits
Both Scripts Working:
✅ A1→A2: Downloads from DAM → Box (folder 348304357505)
✅ A2→A3: Uploads from Box → DAM (folder 348526703108)
Cron Setup:
*/5 * * * * python scripts/a1_to_a2_download.py
*/5 * * * * python scripts/a2_to_a3_upload_polling.py
Complete automation ready for production!
🤖 Generated with Claude Code
Co-Authored-By: Claude <noreply@anthropic.com>
