Download Free CE

Marketing Asset Management

Organize marketing assets across campaigns with versioning, approvals, and usage tracking

All recipes· enterprise-content· 12 minutesintermediate

Marketing Asset Management

Objective

Create a digital asset management system for marketing teams to organize, version, approve, and track usage of brand assets across campaigns and channels.

Step 1: Create Asset Categories Table

Organize assets hierarchically.

CREATE TABLE asset_categories (
    id INTEGER PRIMARY KEY,
    category_code VARCHAR(50) NOT NULL UNIQUE,
    category_name VARCHAR(200) NOT NULL,
    parent_category_id INTEGER,
    description TEXT,
    icon IMAGE(PNG),
    display_order INTEGER,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (parent_category_id) REFERENCES asset_categories(id)
);

Step 2: Create Marketing Assets Table

Store asset metadata.

CREATE TABLE marketing_assets (
    id INTEGER PRIMARY KEY,
    asset_code VARCHAR(50) NOT NULL UNIQUE,
    asset_name VARCHAR(200) NOT NULL,
    description TEXT,
    category_id INTEGER,
    asset_type VARCHAR(50),
    brand VARCHAR(100),
    product_line VARCHAR(100),
    target_audience TEXT,
    usage_rights TEXT,
    license_type VARCHAR(50),
    license_expiry DATE,
    thumbnail IMAGE(JPEG),
    created_by VARCHAR(100),
    owner_team VARCHAR(100),
    status VARCHAR(50) DEFAULT 'draft',
    approval_status VARCHAR(50) DEFAULT 'pending',
    is_template BOOLEAN DEFAULT FALSE,
    download_count INTEGER DEFAULT 0,
    view_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES asset_categories(id)
);

Step 3: Create Asset Files Table

Store multiple file versions.

CREATE TABLE asset_files (
    id INTEGER PRIMARY KEY,
    asset_id INTEGER NOT NULL,
    version_number INTEGER DEFAULT 1,
    file_name VARCHAR(255),
    file_type VARCHAR(50),
    image_file IMAGE(JPEG),
    video_file VIDEO(MP4),
    audio_file AUDIO(MP3),
    document_file PDF,
    file_size_mb DECIMAL(10, 2),
    dimensions VARCHAR(50),
    duration_seconds INTEGER,
    color_space VARCHAR(20),
    resolution_dpi INTEGER,
    format_type VARCHAR(50),
    is_current BOOLEAN DEFAULT TRUE,
    uploaded_by VARCHAR(100),
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (asset_id) REFERENCES marketing_assets(id)
);

Step 4: Create Asset Tags Table

Enable flexible tagging.

CREATE TABLE asset_tags (
    id INTEGER PRIMARY KEY,
    tag_name VARCHAR(100) NOT NULL UNIQUE,
    tag_type VARCHAR(50),
    usage_count INTEGER DEFAULT 0
);

CREATE TABLE asset_tag_mapping (
    id INTEGER PRIMARY KEY,
    asset_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    FOREIGN KEY (asset_id) REFERENCES marketing_assets(id),
    FOREIGN KEY (tag_id) REFERENCES asset_tags(id)
);

Step 5: Create Campaigns Table

Organize assets by campaign.

CREATE TABLE marketing_campaigns (
    id INTEGER PRIMARY KEY,
    campaign_code VARCHAR(50) NOT NULL UNIQUE,
    campaign_name VARCHAR(200) NOT NULL,
    description TEXT,
    campaign_type VARCHAR(50),
    brand VARCHAR(100),
    start_date DATE,
    end_date DATE,
    budget DECIMAL(12, 2),
    owner_id VARCHAR(50),
    owner_name VARCHAR(200),
    status VARCHAR(50) DEFAULT 'planning',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 6: Create Campaign Assets Table

Link assets to campaigns.

CREATE TABLE campaign_assets (
    id INTEGER PRIMARY KEY,
    campaign_id INTEGER NOT NULL,
    asset_id INTEGER NOT NULL,
    usage_type VARCHAR(50),
    channel VARCHAR(100),
    placement VARCHAR(200),
    start_date DATE,
    end_date DATE,
    added_by VARCHAR(100),
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (campaign_id) REFERENCES marketing_campaigns(id),
    FOREIGN KEY (asset_id) REFERENCES marketing_assets(id)
);

Step 7: Create Approval Workflow Table

Track asset approvals.

CREATE TABLE asset_approvals (
    id INTEGER PRIMARY KEY,
    asset_id INTEGER NOT NULL,
    version_number INTEGER,
    submitted_by VARCHAR(100),
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    reviewer_id VARCHAR(50),
    reviewer_name VARCHAR(200),
    review_status VARCHAR(50) DEFAULT 'pending',
    review_comments TEXT,
    reviewed_at TIMESTAMP,
    approval_level INTEGER DEFAULT 1,
    FOREIGN KEY (asset_id) REFERENCES marketing_assets(id)
);

Step 8: Create Asset Usage Log Table

Track asset downloads and usage.

CREATE TABLE asset_usage_log (
    id INTEGER PRIMARY KEY,
    asset_id INTEGER NOT NULL,
    file_id INTEGER,
    user_id VARCHAR(50),
    user_name VARCHAR(200),
    user_department VARCHAR(100),
    action_type VARCHAR(50),
    channel VARCHAR(100),
    purpose TEXT,
    action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (asset_id) REFERENCES marketing_assets(id),
    FOREIGN KEY (file_id) REFERENCES asset_files(id)
);

Step 9: Insert Sample Categories

Add asset categories.

INSERT INTO asset_categories (id, category_code, category_name, parent_category_id, description, display_order) VALUES
    (1, 'PHOTO', 'Photography', NULL, 'Product and lifestyle photography', 1),
    (2, 'PHOTO-PROD', 'Product Photos', 1, 'Product photography', 1),
    (3, 'PHOTO-LIFE', 'Lifestyle', 1, 'Lifestyle and contextual photos', 2),
    (4, 'VIDEO', 'Video', NULL, 'Video content', 2),
    (5, 'VIDEO-ADS', 'Advertisements', 4, 'TV and digital ads', 1),
    (6, 'VIDEO-SOCIAL', 'Social Media', 4, 'Social media video content', 2),
    (7, 'GRAPHICS', 'Graphics', NULL, 'Design assets', 3),
    (8, 'GRAPH-LOGO', 'Logos', 7, 'Brand logos and marks', 1),
    (9, 'GRAPH-BANNER', 'Banners', 7, 'Web and display banners', 2),
    (10, 'DOCS', 'Documents', NULL, 'Marketing collateral', 4),
    (11, 'DOCS-BROCH', 'Brochures', 10, 'Product brochures', 1),
    (12, 'DOCS-PRES', 'Presentations', 10, 'Sales presentations', 2);

Step 10: Insert Marketing Assets

Add asset content.

INSERT INTO marketing_assets (id, asset_code, asset_name, description, category_id, asset_type, brand, product_line, usage_rights, license_type, owner_team, status, approval_status, download_count, view_count, created_at) VALUES
    (1, 'PHO-PROD-001', 'Product Hero Shot - Model X', 'High-resolution product photography', 2, 'image', 'TechBrand', 'Model X', 'Internal use only', 'owned', 'Creative Team', 'published', 'approved', 245, 1250, '2023-06-01 10:00:00'),
    (2, 'PHO-PROD-002', 'Product Lineup - Summer Collection', 'Full product lineup shot', 2, 'image', 'TechBrand', 'Summer 2024', 'All channels', 'owned', 'Creative Team', 'published', 'approved', 189, 980, '2023-07-15 09:00:00'),
    (3, 'PHO-LIFE-001', 'Lifestyle - Urban Professional', 'Product in urban setting', 3, 'image', 'TechBrand', 'Model X', 'Digital only', 'owned', 'Creative Team', 'published', 'approved', 156, 720, '2023-08-01 11:00:00'),
    (4, 'VID-AD-001', 'TV Commercial - 30 Second Spot', 'Main advertising campaign video', 5, 'video', 'TechBrand', 'Model X', 'All channels', 'owned', 'Video Team', 'published', 'approved', 45, 380, '2023-09-01 14:00:00'),
    (5, 'VID-SOC-001', 'Instagram Story Template', 'Animated story template', 6, 'video', 'TechBrand', 'All', 'Social media', 'owned', 'Social Team', 'published', 'approved', 312, 1520, '2023-10-01 10:00:00'),
    (6, 'GRA-LOGO-001', 'Primary Logo - Full Color', 'Main brand logo', 8, 'image', 'TechBrand', NULL, 'All channels', 'owned', 'Brand Team', 'published', 'approved', 542, 2150, '2023-01-01 00:00:00'),
    (7, 'GRA-BAN-001', 'Web Banner - Product Launch', 'Homepage banner for launch', 9, 'image', 'TechBrand', 'Model X', 'Digital only', 'owned', 'Digital Team', 'published', 'approved', 128, 650, '2023-11-01 09:00:00'),
    (8, 'DOC-BRO-001', 'Product Brochure - Model X', 'Detailed product information', 11, 'document', 'TechBrand', 'Model X', 'Print and digital', 'owned', 'Product Marketing', 'published', 'approved', 89, 420, '2023-11-15 10:00:00');

Step 11: Insert Asset Files

Add file versions.

INSERT INTO asset_files (id, asset_id, version_number, file_name, file_type, file_size_mb, dimensions, resolution_dpi, format_type, is_current, uploaded_by, uploaded_at) VALUES
    (1, 1, 1, 'product_hero_v1.jpg', 'image', 15.2, '4000x3000', 300, 'JPEG', FALSE, 'design.team', '2023-06-01 10:00:00'),
    (2, 1, 2, 'product_hero_v2.jpg', 'image', 16.8, '4000x3000', 300, 'JPEG', TRUE, 'design.team', '2023-06-15 11:00:00'),
    (3, 2, 1, 'product_lineup_summer.jpg', 'image', 22.5, '6000x4000', 300, 'JPEG', TRUE, 'photo.team', '2023-07-15 09:00:00'),
    (4, 3, 1, 'lifestyle_urban.jpg', 'image', 18.4, '5000x3500', 300, 'JPEG', TRUE, 'photo.team', '2023-08-01 11:00:00'),
    (5, 6, 1, 'logo_primary_color.png', 'image', 0.5, '2000x800', 300, 'PNG', TRUE, 'brand.team', '2023-01-01 00:00:00'),
    (6, 6, 1, 'logo_primary_color.svg', 'image', 0.02, 'vector', NULL, 'SVG', TRUE, 'brand.team', '2023-01-01 00:00:00'),
    (7, 7, 1, 'web_banner_launch.jpg', 'image', 0.8, '1920x600', 72, 'JPEG', TRUE, 'digital.team', '2023-11-01 09:00:00');

INSERT INTO asset_files (id, asset_id, version_number, file_name, file_type, file_size_mb, dimensions, duration_seconds, format_type, is_current, uploaded_by, uploaded_at) VALUES
    (8, 4, 1, 'tv_spot_30s.mp4', 'video', 85.6, '1920x1080', 30, 'MP4', TRUE, 'video.team', '2023-09-01 14:00:00'),
    (9, 5, 1, 'insta_story_template.mp4', 'video', 12.4, '1080x1920', 15, 'MP4', TRUE, 'social.team', '2023-10-01 10:00:00');

INSERT INTO asset_files (id, asset_id, version_number, file_name, file_type, file_size_mb, format_type, is_current, uploaded_by, uploaded_at) VALUES
    (10, 8, 1, 'product_brochure_modelx.pdf', 'document', 5.8, 'PDF', TRUE, 'product.marketing', '2023-11-15 10:00:00');

Step 12: Insert Campaigns and Usage

Add campaign data.

INSERT INTO marketing_campaigns (id, campaign_code, campaign_name, description, campaign_type, brand, start_date, end_date, budget, owner_name, status) VALUES
    (1, 'CAMP-2024-Q1', 'Q1 Product Launch', 'Model X launch campaign', 'product_launch', 'TechBrand', '2024-01-15', '2024-03-31', 500000.00, 'Marketing Director', 'active'),
    (2, 'CAMP-2024-HOLIDAY', 'Holiday Season 2024', 'Holiday promotional campaign', 'seasonal', 'TechBrand', '2024-11-15', '2024-12-31', 250000.00, 'Campaign Manager', 'planning'),
    (3, 'CAMP-2024-BRAND', 'Brand Awareness', 'Ongoing brand awareness', 'branding', 'TechBrand', '2024-01-01', '2024-12-31', 1000000.00, 'Brand Director', 'active');

INSERT INTO campaign_assets (id, campaign_id, asset_id, usage_type, channel, placement, start_date, end_date, added_by) VALUES
    (1, 1, 1, 'hero_image', 'website', 'Homepage hero', '2024-01-15', '2024-03-31', 'campaign.manager'),
    (2, 1, 4, 'advertisement', 'tv', 'Prime time slots', '2024-01-20', '2024-02-28', 'media.buyer'),
    (3, 1, 7, 'display_ad', 'programmatic', 'Banner network', '2024-01-15', '2024-03-31', 'digital.manager'),
    (4, 1, 8, 'collateral', 'sales', 'Sales team distribution', '2024-01-15', NULL, 'sales.ops'),
    (5, 3, 6, 'brand_identity', 'all', 'All touchpoints', '2024-01-01', '2024-12-31', 'brand.manager'),
    (6, 3, 5, 'social_content', 'instagram', 'Stories and Reels', '2024-01-01', '2024-12-31', 'social.manager');

INSERT INTO asset_usage_log (id, asset_id, file_id, user_id, user_name, user_department, action_type, channel, purpose) VALUES
    (1, 1, 2, 'USR-001', 'John Designer', 'Creative', 'download', 'print', 'Magazine ad creation'),
    (2, 1, 2, 'USR-002', 'Jane Marketer', 'Digital', 'download', 'web', 'Landing page update'),
    (3, 6, 5, 'USR-003', 'Mike Sales', 'Sales', 'download', 'presentation', 'Client pitch deck'),
    (4, 4, 8, 'USR-004', 'Sarah Media', 'Media', 'view', 'planning', 'Media planning review'),
    (5, 5, 9, 'USR-005', 'Emily Social', 'Social', 'download', 'instagram', 'Story creation');

Step 13: Asset Search Results

Browse available assets.

SELECT
    ma.asset_code,
    ma.asset_name,
    ac.category_name,
    ma.asset_type,
    ma.brand,
    ma.approval_status,
    af.dimensions,
    af.file_size_mb,
    ma.view_count,
    ma.download_count
FROM marketing_assets ma
INNER JOIN asset_categories ac ON ma.category_id = ac.id
LEFT JOIN asset_files af ON ma.id = af.asset_id AND af.is_current = TRUE
WHERE ma.status = 'published'
  AND ma.approval_status = 'approved'
ORDER BY ma.download_count DESC;

Step 14: Campaign Asset Report

View campaign resources.

SELECT
    mc.campaign_name,
    mc.status as campaign_status,
    ma.asset_name,
    ma.asset_type,
    ca.channel,
    ca.placement,
    ca.start_date,
    ca.end_date
FROM marketing_campaigns mc
INNER JOIN campaign_assets ca ON mc.id = ca.campaign_id
INNER JOIN marketing_assets ma ON ca.asset_id = ma.id
WHERE mc.id = 1
ORDER BY ca.channel, ma.asset_type;

Step 15: Asset Usage Analytics

Analyze asset performance.

SELECT
    ma.asset_name,
    ma.asset_type,
    ma.view_count,
    ma.download_count,
    COUNT(aul.id) as usage_actions,
    COUNT(DISTINCT aul.user_id) as unique_users,
    COUNT(DISTINCT aul.channel) as channels_used,
    COUNT(DISTINCT ca.campaign_id) as campaigns_used
FROM marketing_assets ma
LEFT JOIN asset_usage_log aul ON ma.id = aul.asset_id
LEFT JOIN campaign_assets ca ON ma.id = ca.asset_id
WHERE ma.status = 'published'
GROUP BY ma.id, ma.asset_name, ma.asset_type, ma.view_count, ma.download_count
ORDER BY ma.download_count DESC
LIMIT 10;

Cleanup (Optional)

DROP TABLE IF EXISTS asset_usage_log;
DROP TABLE IF EXISTS asset_approvals;
DROP TABLE IF EXISTS campaign_assets;
DROP TABLE IF EXISTS marketing_campaigns;
DROP TABLE IF EXISTS asset_tag_mapping;
DROP TABLE IF EXISTS asset_tags;
DROP TABLE IF EXISTS asset_files;
DROP TABLE IF EXISTS marketing_assets;
DROP TABLE IF EXISTS asset_categories;

Expected Outcomes

  • Assets organized by category
  • Multiple file versions tracked
  • Campaigns link to assets
  • Usage monitored
  • Analytics available

Key Concepts Learned

  • Digital asset management
  • Version control
  • Campaign asset linking
  • Usage tracking
  • Approval workflows

Tags

sqlintermediateimagevideopdfenterprisemarketingdam

Run this on your own machine

Install SynapCores Community Edition free, paste the SQL or Cypher above into the bundled web UI, and watch it run.

Download Free CE