Virtual Try-On Images

Objective

Create a system for managing product images optimized for virtual try-on and augmented reality experiences. This enables customers to visualize products before purchase.

Step 1: Create AR-Enabled Products Table

Define products with virtual try-on support.

CREATE TABLE ar_products (
    id INTEGER PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    subcategory VARCHAR(100),
    ar_type VARCHAR(50) NOT NULL,
    has_ar_enabled BOOLEAN DEFAULT TRUE,
    try_on_count INTEGER DEFAULT 0,
    conversion_rate DECIMAL(5, 2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create AR Assets Table

Store AR-ready images and models.

CREATE TABLE ar_assets (
    id INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL,
    asset_type VARCHAR(50) NOT NULL,
    front_image IMAGE(PNG),
    side_image IMAGE(PNG),
    overlay_image IMAGE(PNG),
    mask_image IMAGE(PNG),
    thumbnail IMAGE(JPEG),
    width INTEGER,
    height INTEGER,
    anchor_points TEXT,
    scale_factor DECIMAL(4, 2) DEFAULT 1.0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES ar_products(id)
);

Step 3: Create Product Variants Table

Store color and style variants.

CREATE TABLE ar_product_variants (
    id INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL,
    variant_name VARCHAR(100),
    color_code VARCHAR(20),
    color_name VARCHAR(50),
    variant_image IMAGE(PNG),
    overlay_image IMAGE(PNG),
    is_default BOOLEAN DEFAULT FALSE,
    sort_order INTEGER,
    FOREIGN KEY (product_id) REFERENCES ar_products(id)
);

Step 4: Create Try-On Sessions Table

Track user try-on interactions.

CREATE TABLE try_on_sessions (
    id INTEGER PRIMARY KEY,
    session_id VARCHAR(100) NOT NULL,
    product_id INTEGER NOT NULL,
    variant_id INTEGER,
    user_photo IMAGE(JPEG),
    result_image IMAGE(JPEG),
    device_type VARCHAR(50),
    duration_seconds INTEGER,
    shared BOOLEAN DEFAULT FALSE,
    added_to_cart BOOLEAN DEFAULT FALSE,
    purchased BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES ar_products(id),
    FOREIGN KEY (variant_id) REFERENCES ar_product_variants(id)
);

Step 5: Create AR Configuration Table

Store category-specific AR settings.

CREATE TABLE ar_configurations (
    id INTEGER PRIMARY KEY,
    ar_type VARCHAR(50) NOT NULL UNIQUE,
    display_name VARCHAR(100),
    anchor_type VARCHAR(50),
    scaling_enabled BOOLEAN DEFAULT TRUE,
    rotation_enabled BOOLEAN DEFAULT TRUE,
    instructions TEXT,
    min_quality_score DECIMAL(3, 2) DEFAULT 0.7
);

Step 6: Insert AR Products

Add products with AR support.

INSERT INTO ar_products (id, sku, name, category, subcategory, ar_type, try_on_count, conversion_rate) VALUES
    -- Eyewear
    (1, 'GLASSES-001', 'Classic Aviator Sunglasses', 'Eyewear', 'Sunglasses', 'face_glasses', 15000, 12.5),
    (2, 'GLASSES-002', 'Round Frame Eyeglasses', 'Eyewear', 'Prescription', 'face_glasses', 12000, 15.2),
    (3, 'GLASSES-003', 'Sport Sunglasses', 'Eyewear', 'Sport', 'face_glasses', 8500, 10.8),
    -- Watches
    (4, 'WATCH-001', 'Luxury Chronograph Watch', 'Accessories', 'Watches', 'wrist_watch', 9000, 8.5),
    (5, 'WATCH-002', 'Smart Fitness Watch', 'Accessories', 'Watches', 'wrist_watch', 11000, 11.2),
    -- Jewelry
    (6, 'RING-001', 'Diamond Engagement Ring', 'Jewelry', 'Rings', 'finger_ring', 5000, 6.5),
    (7, 'NECKLACE-001', 'Pearl Necklace', 'Jewelry', 'Necklaces', 'neck_jewelry', 4500, 7.8),
    -- Makeup
    (8, 'LIPSTICK-001', 'Matte Lipstick Collection', 'Beauty', 'Lips', 'face_makeup', 25000, 18.5),
    (9, 'EYESHADOW-001', 'Smoky Eye Palette', 'Beauty', 'Eyes', 'face_makeup', 18000, 14.2),
    -- Hats
    (10, 'HAT-001', 'Baseball Cap', 'Accessories', 'Hats', 'head_hat', 7500, 9.5);

Step 7: Insert AR Assets

Add AR-ready images.

INSERT INTO ar_assets (id, product_id, asset_type, width, height, anchor_points, scale_factor) VALUES
    -- Glasses assets
    (1, 1, 'overlay', 800, 300, '{"left_eye": [200, 150], "right_eye": [600, 150], "nose_bridge": [400, 200]}', 1.0),
    (2, 2, 'overlay', 750, 280, '{"left_eye": [190, 140], "right_eye": [560, 140], "nose_bridge": [375, 180]}', 0.95),
    (3, 3, 'overlay', 850, 320, '{"left_eye": [210, 160], "right_eye": [640, 160], "nose_bridge": [425, 210]}', 1.05),
    -- Watch assets
    (4, 4, 'overlay', 400, 400, '{"wrist_center": [200, 200], "watch_face": [200, 180]}', 1.0),
    (5, 5, 'overlay', 380, 380, '{"wrist_center": [190, 190], "watch_face": [190, 170]}', 0.95),
    -- Ring assets
    (6, 6, 'overlay', 200, 200, '{"finger_base": [100, 150], "finger_tip": [100, 50]}', 1.0),
    -- Makeup assets
    (7, 8, 'overlay', 300, 150, '{"upper_lip": [150, 50], "lower_lip": [150, 100], "lip_corners": [[50, 75], [250, 75]]}', 1.0),
    (8, 9, 'overlay', 600, 200, '{"left_eyelid": [150, 100], "right_eyelid": [450, 100]}', 1.0),
    -- Hat assets
    (9, 10, 'overlay', 500, 300, '{"head_top": [250, 50], "forehead": [250, 200]}', 1.0);

Step 8: Insert Product Variants

Add color options.

INSERT INTO ar_product_variants (id, product_id, variant_name, color_code, color_name, is_default, sort_order) VALUES
    -- Aviator Sunglasses colors
    (1, 1, 'Gold Frame', '#FFD700', 'Gold', TRUE, 1),
    (2, 1, 'Silver Frame', '#C0C0C0', 'Silver', FALSE, 2),
    (3, 1, 'Black Frame', '#000000', 'Black', FALSE, 3),
    -- Lipstick colors
    (4, 8, 'Classic Red', '#FF0000', 'Red', TRUE, 1),
    (5, 8, 'Nude Pink', '#E8B4B8', 'Nude Pink', FALSE, 2),
    (6, 8, 'Berry', '#8E4585', 'Berry', FALSE, 3),
    (7, 8, 'Coral', '#FF7F50', 'Coral', FALSE, 4),
    -- Watch bands
    (8, 4, 'Leather Brown', '#8B4513', 'Brown Leather', TRUE, 1),
    (9, 4, 'Steel', '#71797E', 'Stainless Steel', FALSE, 2),
    (10, 5, 'Black Sport', '#1C1C1C', 'Black', TRUE, 1),
    (11, 5, 'Navy Sport', '#000080', 'Navy', FALSE, 2);

Step 9: Insert AR Configurations

Set up AR types.

INSERT INTO ar_configurations (id, ar_type, display_name, anchor_type, scaling_enabled, rotation_enabled, instructions, min_quality_score) VALUES
    (1, 'face_glasses', 'Eyewear Try-On', 'face_landmarks', TRUE, FALSE, 'Position your face in the center of the frame. Look straight ahead.', 0.75),
    (2, 'face_makeup', 'Makeup Try-On', 'face_landmarks', FALSE, FALSE, 'Ensure good lighting. Keep face centered and still.', 0.80),
    (3, 'wrist_watch', 'Watch Try-On', 'wrist_detection', TRUE, TRUE, 'Hold your wrist up to the camera with palm facing you.', 0.70),
    (4, 'finger_ring', 'Ring Try-On', 'hand_landmarks', TRUE, FALSE, 'Show your hand with fingers spread. Select which finger to try on.', 0.75),
    (5, 'neck_jewelry', 'Necklace Try-On', 'body_pose', TRUE, FALSE, 'Face the camera with your neck and shoulders visible.', 0.70),
    (6, 'head_hat', 'Hat Try-On', 'head_pose', TRUE, TRUE, 'Face forward with your full head visible in frame.', 0.70);

Step 10: Insert Try-On Sessions

Record user interactions.

INSERT INTO try_on_sessions (id, session_id, product_id, variant_id, device_type, duration_seconds, shared, added_to_cart, purchased, created_at) VALUES
    (1, 'sess_001', 1, 1, 'mobile_ios', 45, TRUE, TRUE, TRUE, '2024-07-01 10:30:00'),
    (2, 'sess_002', 1, 2, 'mobile_android', 30, FALSE, TRUE, FALSE, '2024-07-01 11:15:00'),
    (3, 'sess_003', 8, 4, 'mobile_ios', 120, TRUE, TRUE, TRUE, '2024-07-01 12:00:00'),
    (4, 'sess_004', 8, 5, 'desktop', 60, FALSE, FALSE, FALSE, '2024-07-01 14:30:00'),
    (5, 'sess_005', 4, 8, 'mobile_ios', 35, FALSE, TRUE, TRUE, '2024-07-02 09:00:00'),
    (6, 'sess_006', 2, NULL, 'mobile_android', 55, TRUE, TRUE, FALSE, '2024-07-02 10:45:00'),
    (7, 'sess_007', 8, 6, 'mobile_ios', 90, TRUE, TRUE, TRUE, '2024-07-02 13:20:00'),
    (8, 'sess_008', 10, NULL, 'desktop', 25, FALSE, FALSE, FALSE, '2024-07-02 15:00:00');

Step 11: Get AR Products with Assets

List products with try-on support.

SELECT
    ap.name,
    ap.category,
    ap.ar_type,
    ac.display_name as try_on_type,
    ac.instructions,
    COUNT(DISTINCT av.id) as color_options
FROM ar_products ap
INNER JOIN ar_configurations ac ON ap.ar_type = ac.ar_type
LEFT JOIN ar_product_variants av ON ap.id = av.product_id
WHERE ap.has_ar_enabled = TRUE
GROUP BY ap.id, ap.name, ap.category, ap.ar_type, ac.display_name, ac.instructions
ORDER BY ap.try_on_count DESC;

Step 12: Try-On Conversion Analysis

Analyze AR effectiveness.

SELECT
    ap.name,
    ap.category,
    ap.try_on_count,
    COUNT(ts.id) as sessions,
    COUNT(CASE WHEN ts.added_to_cart THEN 1 END) as added_to_cart,
    COUNT(CASE WHEN ts.purchased THEN 1 END) as purchases,
    COUNT(CASE WHEN ts.shared THEN 1 END) as shares,
    CAST(COUNT(CASE WHEN ts.purchased THEN 1 END) AS DECIMAL) / NULLIF(COUNT(ts.id), 0) * 100 as session_conversion_rate
FROM ar_products ap
LEFT JOIN try_on_sessions ts ON ap.id = ts.product_id
GROUP BY ap.id, ap.name, ap.category, ap.try_on_count
ORDER BY ap.try_on_count DESC;

Step 13: Variant Popularity Analysis

Track which variants are tried most.

SELECT
    ap.name as product,
    av.variant_name,
    av.color_name,
    COUNT(ts.id) as try_on_count,
    COUNT(CASE WHEN ts.purchased THEN 1 END) as purchases,
    AVG(ts.duration_seconds) as avg_session_seconds
FROM ar_product_variants av
INNER JOIN ar_products ap ON av.product_id = ap.id
LEFT JOIN try_on_sessions ts ON av.id = ts.variant_id
GROUP BY av.id, ap.name, av.variant_name, av.color_name
HAVING COUNT(ts.id) > 0
ORDER BY try_on_count DESC;

Step 14: Device Performance Report

Analyze by device type.

SELECT
    ts.device_type,
    COUNT(*) as sessions,
    AVG(ts.duration_seconds) as avg_duration,
    CAST(COUNT(CASE WHEN ts.added_to_cart THEN 1 END) AS DECIMAL) / COUNT(*) * 100 as cart_rate,
    CAST(COUNT(CASE WHEN ts.purchased THEN 1 END) AS DECIMAL) / COUNT(*) * 100 as purchase_rate,
    CAST(COUNT(CASE WHEN ts.shared THEN 1 END) AS DECIMAL) / COUNT(*) * 100 as share_rate
FROM try_on_sessions ts
GROUP BY ts.device_type
ORDER BY sessions DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS try_on_sessions;
DROP TABLE IF EXISTS ar_product_variants;
DROP TABLE IF EXISTS ar_assets;
DROP TABLE IF EXISTS ar_configurations;
DROP TABLE IF EXISTS ar_products;

Expected Outcomes

• AR assets organized
• Variants available
• Sessions tracked
• Conversion measured
• Device analytics captured

AR Types

Key Concepts Learned

• AR asset management
• Anchor point configuration
• Variant handling
• Session analytics
• Conversion tracking