DJP 20a187a61d feat: Add Python project dependencies.

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

9.6 KiB

Raw Permalink Blame History

A2→A3 CreativeX Integration Guide

Overview

The A2→A3 upload workflow now automatically looks up CreativeX scores from the database instead of reading from Box metadata templates.

How It Works

Old Method (Box Metadata Template)

❌ Removed: box.get_file_metadata(file_id, template_name='Ferrero-DAM-Metadata')

Required manual metadata entry in Box
Error-prone, easy to miss
No history tracking

New Method (Database Lookup)

✅ Implemented: db.get_creativex_score_by_filename(clean_filename)

Automatic lookup by filename
Always gets latest active score
History preserved for audit
Fallback to defaults if not found

Workflow Sequence

Step-by-Step:

Agency uploads file to Box (folder 348526703108)
- Filename: Brand_Country_Language_123456_OMG7654321_AbCdEf.mp4
A2→A3 script polls Box folder
- Parses filename to get tracking ID: AbCdEf
- Strips upload components to get clean filename: Brand_Country_Language_123456.mp4

Database lookup for CreativeX score

creativex_data = db.get_creativex_score_by_filename("Brand_Country_Language_123456.mp4")

Two scenarios:

✅ Score Found in Database:

{
    'filename': 'Brand_Country_Language_123456.mp4',
    'creativex_id': '6864255',
    'creativex_url': 'https://app.creativex.com/audit/scorecards/32456797',
    'quality_score': '85',
    'box_file_id': '2043306479381',
    'full_extraction_data': {...},  # Complete JSON
    'extracted_at': '2025-11-11 21:29:45'
}

Uses score 85 and actual URL
Email shows: "✅ CreativeX Score Added: 85 (from database)"

⚠️ Score NOT Found:

None  # No matching filename in database

Uses default score: 0
Uses placeholder URL: https://app.creativex.com/preflight/pretests
Email shows orange warning box with instructions
Upload still succeeds (defaults allow it to pass)

Metadata applied to DAM upload
- Field: FERRERO.TAB.FIELD.CREATIVEX = score value
- Field: FERRERO.FIELD.CREATIVEX LINK = URL
- Plus all other MVP fields
File uploaded to DAM and deleted from Box

Code Changes

A2→A3 Script Changes

File: scripts/a2_to_a3_upload_polling.py

Lines 101-124: Replaced Box metadata lookup with database lookup

# Old code (removed):
# box_metadata = box.get_file_metadata(file_id, template_name='Ferrero-DAM-Metadata')

# New code:
clean_filename_for_lookup = parser.strip_upload_components(filename)
creativex_data = db.get_creativex_score_by_filename(clean_filename_for_lookup)

if creativex_data:
    box_metadata = {
        'score': creativex_data['quality_score'],
        'url': creativex_data['creativex_url']
    }
    creativex_found = True
else:
    box_metadata = {
        'score': '0',
        'url': 'https://app.creativex.com/preflight/pretests'
    }
    creativex_found = False

Lines 193-202: Pass CreativeX status to email

return {
    'success': True,
    'creativex_found': creativex_found,
    'creativex_score': box_metadata.get('score', '0'),
    'creativex_url': box_metadata.get('url', '...')
}

Email Template Changes

File: scripts/shared/notifier.py

Template: a2_to_a3_file_uploaded

Added to checklist:

{% if creativex_found %}
<li>✅ <strong>CreativeX Score Added:</strong> {{ creativex_score }} (from database)</li>
{% else %}
<li>⚠️ <strong>CreativeX Score:</strong> Not found - used default (0)</li>
{% endif %}

Added warning section:

{% if not creativex_found %}
<div style="background-color: #fff3e0; border-left: 4px solid #ff9800; padding: 15px;">
    <p><strong>⚠️ CreativeX Score Missing</strong></p>
    <p>No score found for: <code>{{ clean_filename }}</code></p>
    <p><strong>Default Values Used:</strong> Score: 0, URL: placeholder</p>
    <p><em>To add: Upload PDF to Box folder 350605024645 and run creativex_scoring_storing.py</em></p>
</div>
{% endif %}

Default Values

When no CreativeX score is found:

Field	Default Value	Purpose
Score	`0`	Indicates "not scored" but allows upload to proceed
URL	`https://app.creativex.com/preflight/pretests`	Valid CreativeX URL (preflight section)

These defaults ensure uploads never fail due to missing CreativeX data.

Database Query Details

Query Used:

SELECT filename, creativex_id, creativex_url, quality_score,
       box_file_id, full_extraction_data, extracted_at
FROM creativex_scores
WHERE filename = %s AND status = 'active'
ORDER BY extracted_at DESC
LIMIT 1

Key Points:

✅ Filters for status = 'active' → Only latest version
✅ Orders by extracted_at DESC → Most recent first
✅ LIMIT 1 → Only one result
✅ Returns None if no match found

Email Notification Examples

When CreativeX Score Found:

What Was Done:
✅ Downloaded from Box processing folder (348526703108)
✅ Loaded master metadata from database (AbCdEf)
✅ Built asset representation with 27 MVP fields
✅ Updated Description from filename
✅ Updated Language from filename
✅ Set State to "Local"
✅ CreativeX Score Added: 85 (from database)
✅ Stripped OMG Job Number and Tracking ID from filename
✅ Uploaded to DAM Final Assets folder
✅ Deleted file from Box

When CreativeX Score Missing:

What Was Done:
✅ Downloaded from Box processing folder (348526703108)
✅ Loaded master metadata from database (AbCdEf)
✅ Built asset representation with 27 MVP fields
✅ Updated Description from filename
✅ Updated Language from filename
✅ Set State to "Local"
⚠️ CreativeX Score: Not found - used default (0)
✅ Stripped OMG Job Number and Tracking ID from filename
✅ Uploaded to DAM Final Assets folder
✅ Deleted file from Box

[Orange Warning Box]
⚠️ CreativeX Score Missing
No CreativeX score found in database for: Brand_Country_Language_123456.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

Filename Matching Logic

Important: Clean Filename Used for Lookup

The A2→A3 script receives files with upload components:

Box filename: Brand_Country_Language_123_OMG7654321_AbCdEf.mp4
Clean filename: Brand_Country_Language_123.mp4 (stripped)

The database lookup uses the clean filename because:

CreativeX PDFs are named with clean filenames
Upload components (OMG Job, Tracking ID) are added later in workflow
Database stores what's in the PDF report (original clean name)

clean_filename_for_lookup = parser.strip_upload_components(filename)
# "Brand_Country_Language_123_OMG7654321_AbCdEf.mp4"
# → "Brand_Country_Language_123.mp4"

creativex_data = db.get_creativex_score_by_filename(clean_filename_for_lookup)

Testing Scenarios

Scenario 1: File with CreativeX Score

Upload PDF to Box folder 350605024645 with filename: Nutella_France_French_456.pdf
Run: python scripts/creativex_scoring_storing.py
Verify in database: Score stored for Nutella_France_French_456.mp4
Agency uploads: Nutella_France_French_456_OMG999_XyZ123.mp4 to A2→A3 folder
A2→A3 script:
- Strips to: Nutella_France_French_456.mp4
- Finds score in database
- Uploads with actual CreativeX data
- Email shows: "✅ CreativeX Score Added: 85"

Scenario 2: File WITHOUT CreativeX Score

Agency uploads: NewBrand_Italy_Italian_789_OMG888_Abc987.mp4 to A2→A3 folder
No PDF was ever uploaded to 350605024645 for this file
A2→A3 script:
- Strips to: NewBrand_Italy_Italian_789.mp4
- Database lookup returns None
- Uses defaults: Score=0, URL=placeholder
- Upload succeeds anyway
- Email shows orange warning: "⚠️ CreativeX Score Missing"

Advantages Over Box Metadata

Aspect	Old (Box Metadata)	New (Database)
Source	Manual entry in Box	Automatic from LlamaExtract
Accuracy	Human error prone	AI extraction
History	No history	Full version history
Lookup Speed	Box API call	Fast database query
Fallback	Would fail if missing	Graceful defaults
Audit Trail	None	Complete with timestamps
Scalability	Manual per file	Batch processing

Production Deployment Notes

Prerequisites:

CreativeX scoring system deployed (see CREATIVEX_DEPLOYMENT.md)
Database table creativex_scores created
At least some files scored (or expect warnings for missing scores)

No Breaking Changes:

A2→A3 workflow continues to work
Missing scores use defaults (Score: 0)
Uploads never fail due to missing CreativeX data
Email clearly indicates when defaults are used

Recommended Workflow:

Download master assets (A1→A2)
Upload CreativeX PDFs to folder 350605024645
Run creativex_scoring_storing.py to populate database
Agency creates localized versions
Agency uploads to A2→A3 folder
A2→A3 script automatically attaches CreativeX scores

Future Enhancements

Potential additions:

Add CreativeX brand/market validation (warn if mismatch with filename)
Display version number in A2→A3 email ("Score: 85 - Version 3")
Track which files are missing scores in daily report
Auto-populate score field in Box for agency reference

Support

Database queries: See CREATIVEX_DEPLOYMENT.md section "Checking Results"
Scoring new files: Run python scripts/creativex_scoring_storing.py
Logs: Check logs/a2_to_a3.log for CreativeX lookup results
Email: Orange warning box appears when scores are missing

9.6 KiB Raw Permalink Blame History