AI-Driven Marketing & Customer Insights

Problem

Create intelligent marketing campaigns by analyzing customer behavior, segmenting audiences, predicting churn, and generating personalized content using AI-powered insights.

What You'll Learn

• Segment customers using AI-powered clustering
• Analyze campaign performance and customer sentiment
• Predict customer lifetime value and churn risk
• Generate personalized marketing content automatically

Setup: Create Marketing Database

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    age INTEGER,
    gender TEXT,
    location TEXT,
    signup_date DATE,
    total_spent DECIMAL(10,2),
    last_purchase_date DATE,
    purchase_frequency INTEGER,
    preferred_channel TEXT,
    customer_profile VECTOR(384)
);

CREATE TABLE campaigns (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    type TEXT,
    channel TEXT,
    start_date DATE,
    end_date DATE,
    budget DECIMAL(10,2),
    target_audience TEXT,
    content TEXT,
    campaign_embedding VECTOR(384)
);

CREATE TABLE campaign_interactions (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    campaign_id INTEGER REFERENCES campaigns(id),
    interaction_type TEXT,
    interaction_date TIMESTAMP,
    conversion BOOLEAN DEFAULT FALSE,
    revenue DECIMAL(10,2) DEFAULT 0
);

CREATE TABLE customer_feedback (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    feedback_type TEXT,
    rating INTEGER CHECK (rating >= 1 AND rating <= 5),
    comment TEXT,
    feedback_date DATE,
    sentiment_score REAL
);

CREATE TABLE product_purchases (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    product_name TEXT,
    category TEXT,
    price DECIMAL(10,2),
    purchase_date DATE,
    quantity INTEGER
);

Seed Data: Customer Base

INSERT INTO customers (id, name, email, age, gender, location, signup_date, total_spent, last_purchase_date, purchase_frequency, preferred_channel) VALUES
(1, 'Emma Wilson', 'emma.w@email.com', 28, 'Female', 'New York, NY', '2024-01-15', 1250.50, '2025-01-10', 8, 'Email'),
(2, 'Michael Chen', 'michael.c@email.com', 35, 'Male', 'San Francisco, CA', '2023-11-20', 2100.75, '2025-01-08', 12, 'Social Media'),
(3, 'Sarah Johnson', 'sarah.j@email.com', 42, 'Female', 'Chicago, IL', '2024-03-10', 890.25, '2024-12-15', 4, 'Email'),
(4, 'David Rodriguez', 'david.r@email.com', 31, 'Male', 'Austin, TX', '2024-06-05', 1650.00, '2025-01-12', 10, 'Mobile App'),
(5, 'Lisa Park', 'lisa.p@email.com', 26, 'Female', 'Seattle, WA', '2024-08-20', 750.80, '2025-01-14', 6, 'Social Media'),
(6, 'James Martinez', 'james.m@email.com', 38, 'Male', 'Miami, FL', '2023-09-12', 3200.40, '2025-01-11', 15, 'Email'),
(7, 'Anna Kim', 'anna.k@email.com', 29, 'Female', 'Los Angeles, CA', '2024-02-28', 1100.60, '2024-11-20', 3, 'Mobile App'),
(8, 'Robert Taylor', 'robert.t@email.com', 45, 'Male', 'Boston, MA', '2023-12-03', 2800.90, '2025-01-09', 14, 'Email');

INSERT INTO campaigns (id, name, type, channel, start_date, end_date, budget, target_audience, content) VALUES
(1, 'Spring Fashion Sale', 'Promotional', 'Email', '2025-01-01', '2025-01-31', 5000.00, 'Women 25-40', 'Discover our new spring collection with 30% off on all items!'),
(2, 'Tech Gadgets Launch', 'Product Launch', 'Social Media', '2025-01-10', '2025-01-25', 8000.00, 'Men 25-45', 'Revolutionary tech gadgets that will change your daily routine. Pre-order now!'),
(3, 'Loyalty Rewards Program', 'Retention', 'Mobile App', '2025-01-05', '2025-01-20', 3000.00, 'High-value customers', 'Exclusive rewards for our most valued customers. Unlock special benefits today!'),
(4, 'Winter Clearance', 'Clearance', 'Email', '2024-12-01', '2024-12-31', 2000.00, 'Price-sensitive shoppers', 'Last chance! Up to 70% off on winter items. Limited time only!'),
(5, 'Valentine\'s Day Special', 'Seasonal', 'Social Media', '2025-02-01', '2025-02-14', 4500.00, 'Couples 25-50', 'Show your love with our Valentine\'s Day special collection. Perfect gifts await!');

INSERT INTO product_purchases (id, customer_id, product_name, category, price, purchase_date, quantity) VALUES
(1, 1, 'Wireless Headphones', 'Electronics', 199.99, '2025-01-10', 1),
(2, 1, 'Summer Dress', 'Fashion', 89.50, '2024-12-20', 2),
(3, 2, 'Smart Watch', 'Electronics', 299.99, '2025-01-08', 1),
(4, 2, 'Laptop Bag', 'Accessories', 79.99, '2024-12-15', 1),
(5, 3, 'Coffee Maker', 'Home & Kitchen', 149.99, '2024-12-15', 1),
(6, 4, 'Running Shoes', 'Sports', 129.99, '2025-01-12', 1),
(7, 4, 'Fitness Tracker', 'Electronics', 199.99, '2025-01-05', 1),
(8, 5, 'Skincare Set', 'Beauty', 89.99, '2025-01-14', 1),
(9, 6, 'Premium Suit', 'Fashion', 599.99, '2025-01-11', 1),
(10, 6, 'Leather Shoes', 'Fashion', 249.99, '2024-12-20', 1);

INSERT INTO campaign_interactions (id, customer_id, campaign_id, interaction_type, interaction_date, conversion, revenue) VALUES
(1, 1, 1, 'OPEN', '2025-01-15 09:30:00', TRUE, 89.50),
(2, 2, 2, 'CLICK', '2025-01-12 14:20:00', FALSE, 0),
(3, 3, 4, 'OPEN', '2024-12-10 10:15:00', TRUE, 149.99),
(4, 4, 3, 'CLICK', '2025-01-07 16:45:00', TRUE, 199.99),
(5, 5, 1, 'OPEN', '2025-01-16 11:20:00', FALSE, 0),
(6, 6, 2, 'CLICK', '2025-01-13 19:30:00', TRUE, 299.99),
(7, 7, 4, 'OPEN', '2024-12-12 08:45:00', FALSE, 0),
(8, 8, 3, 'CLICK', '2025-01-08 12:10:00', TRUE, 599.99);

INSERT INTO customer_feedback (id, customer_id, feedback_type, rating, comment, feedback_date) VALUES
(1, 1, 'Product Review', 5, 'Amazing headphones! Great sound quality and comfortable fit.', '2025-01-12'),
(2, 2, 'Service Review', 4, 'Fast delivery but packaging could be better.', '2025-01-10'),
(3, 3, 'Product Review', 3, 'Coffee maker works fine but instructions were unclear.', '2024-12-18'),
(4, 4, 'Service Review', 5, 'Excellent customer service and quick response to my questions.', '2025-01-14'),
(5, 6, 'Product Review', 5, 'Perfect suit! Great quality and perfect fit.', '2025-01-13');

Generate Customer and Campaign Embeddings

UPDATE customers 
SET customer_profile = EMBED(
    name || ' ' || age || ' ' || gender || ' ' || location || ' ' || 
    preferred_channel || ' ' || total_spent || ' ' || purchase_frequency
);

UPDATE campaigns 
SET campaign_embedding = EMBED(name || ' ' || type || ' ' || target_audience || ' ' || content);

Analyze Customer Sentiment

UPDATE customer_feedback 
SET sentiment_score = SENTIMENT(comment);

Customer Segmentation Analysis

WITH customer_segments AS (
    SELECT 
        c.*,
        CASE 
            WHEN c.total_spent > 2000 AND c.purchase_frequency > 10 THEN 'VIP'
            WHEN c.total_spent > 1000 AND c.purchase_frequency > 6 THEN 'Loyal'
            WHEN c.total_spent > 500 AND c.purchase_frequency > 3 THEN 'Regular'
            WHEN c.last_purchase_date < DATE('2024-12-01') THEN 'At Risk'
            ELSE 'New'
        END as segment,
        CASE 
            WHEN age < 30 THEN 'Gen Z/Young Millennial'
            WHEN age < 40 THEN 'Millennial'
            WHEN age < 50 THEN 'Gen X'
            ELSE 'Boomer'
        END as age_group
    FROM customers c
)
SELECT 
    segment,
    age_group,
    COUNT(*) as customer_count,
    AVG(total_spent) as avg_spending,
    AVG(purchase_frequency) as avg_frequency,
    GROUP_CONCAT(DISTINCT preferred_channel) as preferred_channels
FROM customer_segments
GROUP BY segment, age_group
ORDER BY avg_spending DESC;

Campaign Performance Analysis

SELECT 
    c.name as campaign_name,
    c.type,
    c.channel,
    c.budget,
    COUNT(ci.id) as total_interactions,
    COUNT(CASE WHEN ci.conversion = TRUE THEN 1 END) as conversions,
    ROUND(COUNT(CASE WHEN ci.conversion = TRUE THEN 1 END) * 100.0 / COUNT(ci.id), 2) as conversion_rate,
    SUM(ci.revenue) as total_revenue,
    ROUND(SUM(ci.revenue) / c.budget, 2) as roi
FROM campaigns c
LEFT JOIN campaign_interactions ci ON c.id = ci.campaign_id
GROUP BY c.id, c.name, c.type, c.channel, c.budget
ORDER BY roi DESC;

Predict Customer Churn Risk

WITH churn_analysis AS (
    SELECT 
        c.*,
        COALESCE(AVG(cf.sentiment_score), 0) as avg_sentiment,
        COUNT(cf.id) as feedback_count,
        JULIANDAY('2025-01-20') - JULIANDAY(c.last_purchase_date) as days_since_purchase,
        CASE 
            WHEN JULIANDAY('2025-01-20') - JULIANDAY(c.last_purchase_date) > 60 THEN 0.8
            WHEN JULIANDAY('2025-01-20') - JULIANDAY(c.last_purchase_date) > 30 THEN 0.5
            WHEN purchase_frequency < 3 THEN 0.4
            WHEN COALESCE(AVG(cf.sentiment_score), 0) < 0 THEN 0.6
            ELSE 0.1
        END as churn_risk_score
    FROM customers c
    LEFT JOIN customer_feedback cf ON c.id = cf.customer_id
    GROUP BY c.id
)
SELECT 
    name,
    total_spent,
    days_since_purchase,
    purchase_frequency,
    ROUND(avg_sentiment, 2) as avg_sentiment,
    ROUND(churn_risk_score, 2) as churn_risk,
    CASE 
        WHEN churn_risk_score > 0.6 THEN 'High Risk'
        WHEN churn_risk_score > 0.3 THEN 'Medium Risk'
        ELSE 'Low Risk'
    END as risk_category
FROM churn_analysis
ORDER BY churn_risk_score DESC;

Find Similar Customers for Targeting

WITH target_customer AS (
    SELECT customer_profile FROM customers WHERE id = 6  -- James Martinez (VIP customer)
)
SELECT 
    c.name,
    c.total_spent,
    c.purchase_frequency,
    c.preferred_channel,
    COSINE_SIMILARITY(tc.customer_profile, c.customer_profile) as similarity_score
FROM customers c
CROSS JOIN target_customer tc
WHERE c.id != 6
ORDER BY similarity_score DESC
LIMIT 5;

Generate Personalized Campaign Content

SELECT 
    c.name as customer_name,
    c.preferred_channel,
    c.total_spent,
    GENERATE(
        'Create a personalized marketing message for ' || c.name || 
        ' (age ' || c.age || ', ' || c.gender || ' from ' || c.location || ') ' ||
        'who prefers ' || c.preferred_channel || ' and has spent $' || c.total_spent || 
        '. Focus on fashion and electronics based on purchase history.',
        '{"max_length": 150, "tone": "friendly", "focus": "personalization"}'
    ) as personalized_message
FROM customers c
WHERE c.total_spent > 1000
ORDER BY c.total_spent DESC
LIMIT 4;

Customer Lifetime Value Prediction

WITH clv_calculation AS (
    SELECT 
        c.id,
        c.name,
        c.total_spent,
        c.purchase_frequency,
        JULIANDAY('2025-01-20') - JULIANDAY(c.signup_date) as customer_lifetime_days,
        ROUND(c.total_spent / (JULIANDAY('2025-01-20') - JULIANDAY(c.signup_date)) * 365, 2) as annual_value,
        ROUND(c.total_spent / c.purchase_frequency, 2) as avg_order_value
    FROM customers c
    WHERE c.purchase_frequency > 0
)
SELECT 
    name,
    total_spent,
    purchase_frequency,
    avg_order_value,
    annual_value,
    ROUND(annual_value * 3, 2) as predicted_3year_clv,  -- Simple 3-year projection
    CASE 
        WHEN annual_value > 1000 THEN 'High Value'
        WHEN annual_value > 500 THEN 'Medium Value'
        ELSE 'Low Value'
    END as value_segment
FROM clv_calculation
ORDER BY predicted_3year_clv DESC;

Analyze Product Category Performance

SELECT 
    pp.category,
    COUNT(pp.id) as total_purchases,
    SUM(pp.price * pp.quantity) as total_revenue,
    AVG(pp.price) as avg_price,
    COUNT(DISTINCT pp.customer_id) as unique_customers,
    ROUND(AVG(cf.sentiment_score), 2) as avg_sentiment
FROM product_purchases pp
LEFT JOIN customer_feedback cf ON pp.customer_id = cf.customer_id
GROUP BY pp.category
ORDER BY total_revenue DESC;

Campaign Audience Matching

SELECT 
    ca.name as campaign_name,
    ca.target_audience,
    c.name as customer_name,
    c.age,
    c.gender,
    c.total_spent,
    COSINE_SIMILARITY(ca.campaign_embedding, c.customer_profile) as audience_match_score
FROM campaigns ca
CROSS JOIN customers c
WHERE COSINE_SIMILARITY(ca.campaign_embedding, c.customer_profile) > 0.3
ORDER BY ca.id, audience_match_score DESC;

Try These Additional Queries

-- Extract insights from customer feedback
SELECT 
    customer_id,
    comment,
    EXTRACT_KEYWORDS(comment, 3) as key_themes,
    sentiment_score
FROM customer_feedback
WHERE comment IS NOT NULL;

-- Generate campaign ideas based on customer segments
SELECT 
    'VIP Customers' as segment,
    GENERATE(
        'Create a luxury campaign idea for VIP customers who spend over $2000 annually and prefer email communication. Focus on exclusive products and premium service.',
        '{"max_length": 200, "focus": "luxury_marketing"}'
    ) as campaign_idea
UNION ALL
SELECT 
    'At-Risk Customers' as segment,
    GENERATE(
        'Create a win-back campaign for customers who haven\'t purchased in 60+ days. Focus on special offers and reconnection.',
        '{"max_length": 200, "focus": "retention_marketing"}'
    ) as campaign_idea;

-- Analyze seasonal purchase patterns
SELECT 
    CASE 
        WHEN SUBSTR(purchase_date, 6, 2) IN ('12', '01', '02') THEN 'Winter'
        WHEN SUBSTR(purchase_date, 6, 2) IN ('03', '04', '05') THEN 'Spring'
        WHEN SUBSTR(purchase_date, 6, 2) IN ('06', '07', '08') THEN 'Summer'
        ELSE 'Fall'
    END as season,
    category,
    COUNT(*) as purchase_count,
    SUM(price * quantity) as revenue
FROM product_purchases
GROUP BY season, category
ORDER BY season, revenue DESC;

-- Find cross-selling opportunities
SELECT 
    c.name,
    GROUP_CONCAT(DISTINCT pp.category) as purchased_categories,
    GENERATE(
        'Suggest cross-selling products for customer who bought: ' || GROUP_CONCAT(DISTINCT pp.product_name, ', '),
        '{"max_length": 100, "focus": "cross_selling"}'
    ) as cross_sell_suggestions
FROM customers c
JOIN product_purchases pp ON c.id = pp.customer_id
GROUP BY c.id, c.name
HAVING COUNT(DISTINCT pp.category) >= 2;

Key Takeaways

✅ Smart Segmentation: AI-powered customer clustering based on behavior and preferences
✅ Predictive Analytics: Churn prediction and lifetime value calculation for proactive marketing
✅ Personalization: Generate targeted content and campaigns using customer insights
✅ Performance Optimization: Data-driven campaign analysis and ROI measurement

Next Steps

• Implement real-time recommendation engines
• Add A/B testing capabilities for campaign optimization
• Create automated email triggers based on customer behavior
• Integrate social media sentiment analysis

Note: This tutorial demonstrates marketing analytics concepts using simulated customer data for educational purposes.