Creating a Personalized Product Recommendation Agent: From Customer Behavior to Recommendations

E-commerce sites show generic product recommendations. Customers see irrelevant products, conversion rates are low, and personalization is manual. Teams can't scale personalization across thousands of products and millions of customers. The result: low recommendation click-through rates, missed sales opportunities, and poor customer experience.

The problem: A customer browses running shoes, adds them to cart, but doesn't purchase. The site shows them kitchen appliances. The customer leaves. A week later, they return and buy running shoes from a competitor. The site lost a sale because it showed irrelevant recommendations.

The solution: A personalized recommendation agent that analyzes customer behavior, purchase history, browsing patterns, and product attributes—generating relevant recommendations in real-time. This post walks through building a repeatable architecture that transforms generic recommendations into personalized intelligence.

The Problem with Generic Product Recommendations

Generic recommendations fail to convert. Here's what e-commerce teams face:

The Relevance Problem

Recommendations aren't personalized:

• All customers see the same "bestsellers"
• No consideration of individual preferences
• Browsing history isn't factored in
• Purchase history is ignored

The math: If 1% of customers click on generic recommendations and personalized recommendations achieve 5% click-through, that's a 5x improvement. For 1M monthly visitors, that's 50,000 additional clicks vs 10,000.

The Scale Problem

Personalization doesn't scale:

• Manual curation works for 100 products, not 10,000
• Rule-based systems break with product catalog changes
• A/B testing every recommendation combination is impossible
• Teams can't personalize for millions of customers manually

The impact: A team manually curates recommendations for 50 products. The catalog grows to 5,000 products. Manual curation becomes impossible. Recommendations become generic again.

The Real-Time Problem

Recommendations are stale:

• Updated weekly or monthly, not in real-time
• Customer behavior changes aren't reflected immediately
• New products aren't included in recommendations
• Seasonal changes aren't accounted for

The cost: A customer browses winter coats in December, but recommendations still show summer items from the last update in August. The customer leaves without purchasing.

The Data Fragmentation Problem

Customer data is scattered:

• Browsing data in analytics
• Purchase data in orders
• Cart data in e-commerce platform
• Product data in catalog

The missed opportunity: Teams can't combine browsing behavior with purchase history to understand customer preferences. Recommendations are based on incomplete data.

A Unified Customer Behavior Architecture

The solution is a unified view architecture that tracks customer interactions, analyzes behavior patterns, and generates personalized recommendations.

Architecture Components

1. Behavior Tracking Layer

• Captures customer interactions (views, clicks, cart adds)
• Tracks browsing sessions and paths
• Records purchase history
• Monitors cart abandonment

2. Customer Behavior View

• Unified view of all customer interactions
• Pre-computed behavior scores
• Product affinity calculations
• Category preferences

3. Product Similarity Engine

• Analyzes product attributes
• Calculates similarity scores
• Identifies complementary products
• Maps product relationships

4. Collaborative Filtering

• Finds similar customers
• Identifies products liked by similar customers
• Generates "customers who bought X also bought Y" recommendations

5. Recommendation Generation

• Combines multiple recommendation strategies
• Scores and ranks recommendations
• Filters by availability and relevance
• Generates personalized lists

6. Real-Time Integration

• API for real-time recommendation requests
• Caching for performance
• A/B testing support
• Performance tracking

Tracking Customer Interactions Across Touchpoints

The first stage is tracking all customer interactions—browsing, cart activity, purchases—into a unified behavior view.

Building the Customer Behavior View

Create a comprehensive view that joins interaction data across touchpoints:

CREATE MATERIALIZED VIEW customer_behavior_comprehensive AS
SELECT 
    -- Customer Info
    c.customer_id,
    c.email,
    c.signup_date,
    c.customer_segment, -- 'new', 'returning', 'vip', 'at_risk'
    
    -- Product Interaction
    pi.product_id,
    pi.product_name,
    pi.category,
    pi.brand,
    pi.price,
    
    -- Interaction Types
    pi.interaction_type, -- 'view', 'click', 'cart_add', 'cart_remove', 'purchase'
    pi.interaction_timestamp,
    pi.session_id,
    
    -- Purchase Context
    o.order_id,
    o.order_date,
    o.order_value,
    o.quantity,
    
    -- Cart Context
    ca.cart_id,
    ca.cart_added_at,
    ca.cart_abandoned,
    
    -- Time Dimensions
    DATE(pi.interaction_timestamp) as interaction_date,
    DATE_PART('hour', pi.interaction_timestamp) as interaction_hour,
    DATE_PART('dow', pi.interaction_timestamp) as day_of_week,
    
    -- Calculated Fields
    CASE 
        WHEN pi.interaction_type = 'purchase' THEN 10
        WHEN pi.interaction_type = 'cart_add' THEN 5
        WHEN pi.interaction_type = 'click' THEN 3
        WHEN pi.interaction_type = 'view' THEN 1
        ELSE 0
    END as interaction_score,
    
    -- Recency Score (more recent = higher score)
    CASE 
        WHEN pi.interaction_timestamp > CURRENT_TIMESTAMP - INTERVAL '1 day' THEN 1.0
        WHEN pi.interaction_timestamp > CURRENT_TIMESTAMP - INTERVAL '7 days' THEN 0.8
        WHEN pi.interaction_timestamp > CURRENT_TIMESTAMP - INTERVAL '30 days' THEN 0.5
        ELSE 0.2
    END as recency_score
    
FROM customer_interactions pi
LEFT JOIN customers c ON pi.customer_id = c.customer_id
LEFT JOIN products p ON pi.product_id = p.product_id
LEFT JOIN orders o ON pi.customer_id = o.customer_id AND pi.product_id = o.product_id
LEFT JOIN cart_activity ca ON pi.customer_id = ca.customer_id AND pi.product_id = ca.product_id;

Customer Product Affinity

Calculate how much each customer likes each product:

CREATE MATERIALIZED VIEW customer_product_affinity AS
SELECT 
    customer_id,
    product_id,
    product_name,
    category,
    brand,
    
    -- Interaction Metrics
    COUNT(*) FILTER (WHERE interaction_type = 'view') as view_count,
    COUNT(*) FILTER (WHERE interaction_type = 'click') as click_count,
    COUNT(*) FILTER (WHERE interaction_type = 'cart_add') as cart_add_count,
    COUNT(*) FILTER (WHERE interaction_type = 'purchase') as purchase_count,
    
    -- Total Interaction Score
    SUM(interaction_score) as total_interaction_score,
    
    -- Weighted Score (interaction score * recency)
    SUM(interaction_score * recency_score) as weighted_affinity_score,
    
    -- Last Interaction
    MAX(interaction_timestamp) as last_interaction_date,
    
    -- Days Since Last Interaction
    EXTRACT(DAY FROM (CURRENT_TIMESTAMP - MAX(interaction_timestamp))) as days_since_interaction,
    
    -- Affinity Category
    CASE 
        WHEN SUM(interaction_score * recency_score) > 50 THEN 'high_affinity'
        WHEN SUM(interaction_score * recency_score) > 20 THEN 'medium_affinity'
        WHEN SUM(interaction_score * recency_score) > 5 THEN 'low_affinity'
        ELSE 'no_affinity'
    END as affinity_category
    
FROM customer_behavior_comprehensive
GROUP BY customer_id, product_id, product_name, category, brand;

Customer Category Preferences

Identify which categories each customer prefers:

CREATE MATERIALIZED VIEW customer_category_preferences AS
SELECT 
    customer_id,
    category,
    
    -- Category Interaction Metrics
    COUNT(DISTINCT product_id) as products_viewed,
    SUM(interaction_score) as total_category_score,
    SUM(interaction_score * recency_score) as weighted_category_score,
    
    -- Category Purchase Metrics
    COUNT(*) FILTER (WHERE interaction_type = 'purchase') as category_purchases,
    SUM(order_value) FILTER (WHERE interaction_type = 'purchase') as category_revenue,
    
    -- Preference Rank
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY SUM(interaction_score * recency_score) DESC) as category_rank
    
FROM customer_behavior_comprehensive
GROUP BY customer_id, category;

Product Similarity and Clustering

Calculate product similarity based on attributes, customer behavior, and relationships.

Product Attribute Similarity

CREATE MATERIALIZED VIEW product_similarity AS
SELECT 
    p1.product_id as product_id_1,
    p1.product_name as product_name_1,
    p2.product_id as product_id_2,
    p2.product_name as product_name_2,
    
    -- Attribute Similarity
    CASE WHEN p1.category = p2.category THEN 1.0 ELSE 0.0 END as category_match,
    CASE WHEN p1.brand = p2.brand THEN 0.8 ELSE 0.0 END as brand_match,
    CASE WHEN ABS(p1.price - p2.price) / NULLIF(GREATEST(p1.price, p2.price), 0) < 0.2 THEN 0.6 ELSE 0.0 END as price_similarity,
    
    -- Combined Similarity Score
    (
        CASE WHEN p1.category = p2.category THEN 1.0 ELSE 0.0 END * 0.4 +
        CASE WHEN p1.brand = p2.brand THEN 0.8 ELSE 0.0 END * 0.3 +
        CASE WHEN ABS(p1.price - p2.price) / NULLIF(GREATEST(p1.price, p2.price), 0) < 0.2 THEN 0.6 ELSE 0.0 END * 0.3
    ) as attribute_similarity_score
    
FROM products p1
CROSS JOIN products p2
WHERE p1.product_id != p2.product_id
  AND p1.product_id < p2.product_id; -- Avoid duplicates

Collaborative Filtering: Products Bought Together

CREATE MATERIALIZED VIEW products_bought_together AS
SELECT 
    o1.product_id as product_id_1,
    o2.product_id as product_id_2,
    COUNT(DISTINCT o1.order_id) as co_purchase_count,
    
    -- Jaccard Similarity (customers who bought both / customers who bought either)
    COUNT(DISTINCT o1.order_id)::float / 
    NULLIF(
        COUNT(DISTINCT o1.customer_id) + COUNT(DISTINCT o2.customer_id) - COUNT(DISTINCT o1.order_id),
        0
    ) as co_purchase_similarity
    
FROM orders o1
INNER JOIN orders o2 ON o1.order_id = o2.order_id AND o1.product_id != o2.product_id
WHERE o1.product_id < o2.product_id
GROUP BY o1.product_id, o2.product_id
HAVING COUNT(DISTINCT o1.order_id) >= 5; -- Minimum co-purchases for relevance

Complementary Products

Identify products that complement each other:

CREATE MATERIALIZED VIEW complementary_products AS
SELECT 
    pbt.product_id_1,
    pbt.product_id_2,
    pbt.co_purchase_count,
    pbt.co_purchase_similarity,
    
    -- Sequential Purchase (bought product_1, then product_2)
    COUNT(*) FILTER (
        WHERE o1.order_date < o2.order_date 
        AND o1.customer_id = o2.customer_id
    ) as sequential_purchase_count,
    
    -- Complementary Score
    (pbt.co_purchase_similarity * 0.6 + 
     (COUNT(*) FILTER (WHERE o1.order_date < o2.order_date AND o1.customer_id = o2.customer_id)::float / 
      NULLIF(pbt.co_purchase_count, 0)) * 0.4) as complementary_score
    
FROM products_bought_together pbt
LEFT JOIN orders o1 ON pbt.product_id_1 = o1.product_id
LEFT JOIN orders o2 ON pbt.product_id_2 = o2.product_id AND o1.customer_id = o2.customer_id
GROUP BY pbt.product_id_1, pbt.product_id_2, pbt.co_purchase_count, pbt.co_purchase_similarity
ORDER BY complementary_score DESC;

Recommendation Algorithm Implementation

Combine multiple recommendation strategies to generate personalized recommendations.

Recommendation Generation

CREATE MATERIALIZED VIEW product_recommendations AS
WITH customer_affinity_recommendations AS (
    -- Recommendations based on customer's product affinity
    SELECT 
        cpa.customer_id,
        ps.product_id_2 as recommended_product_id,
        ps.product_name_2 as recommended_product_name,
        'affinity_similar' as recommendation_type,
        ps.attribute_similarity_score * cpa.weighted_affinity_score as recommendation_score
    FROM customer_product_affinity cpa
    JOIN product_similarity ps ON cpa.product_id = ps.product_id_1
    WHERE cpa.affinity_category IN ('high_affinity', 'medium_affinity')
      AND ps.attribute_similarity_score > 0.5
),
collaborative_recommendations AS (
    -- Recommendations based on similar customers
    SELECT 
        cpa1.customer_id,
        cpa2.product_id as recommended_product_id,
        cpa2.product_name as recommended_product_name,
        'collaborative' as recommendation_type,
        cpa2.weighted_affinity_score * 0.8 as recommendation_score
    FROM customer_product_affinity cpa1
    JOIN customer_product_affinity cpa2 
        ON cpa1.product_id = cpa2.product_id 
        AND cpa1.customer_id != cpa2.customer_id
    WHERE cpa1.affinity_category = 'high_affinity'
      AND cpa2.affinity_category IN ('high_affinity', 'medium_affinity')
      AND cpa2.customer_id NOT IN (
          SELECT customer_id FROM customer_product_affinity 
          WHERE customer_id = cpa1.customer_id AND product_id = cpa2.product_id
      )
),
complementary_recommendations AS (
    -- Recommendations based on complementary products
    SELECT 
        cpa.customer_id,
        cp.product_id_2 as recommended_product_id,
        p.product_name as recommended_product_name,
        'complementary' as recommendation_type,
        cp.complementary_score * cpa.weighted_affinity_score as recommendation_score
    FROM customer_product_affinity cpa
    JOIN complementary_products cp ON cpa.product_id = cp.product_id_1
    JOIN products p ON cp.product_id_2 = p.product_id
    WHERE cpa.affinity_category IN ('high_affinity', 'medium_affinity')
      AND cp.complementary_score > 0.3
      AND cpa.customer_id NOT IN (
          SELECT customer_id FROM customer_product_affinity 
          WHERE customer_id = cpa.customer_id AND product_id = cp.product_id_2
      )
),
category_preference_recommendations AS (
    -- Recommendations based on category preferences
    SELECT 
        ccp.customer_id,
        p.product_id as recommended_product_id,
        p.product_name as recommended_product_name,
        'category_preference' as recommendation_type,
        ccp.weighted_category_score * 0.6 as recommendation_score
    FROM customer_category_preferences ccp
    JOIN products p ON ccp.category = p.category
    WHERE ccp.category_rank <= 3 -- Top 3 categories
      AND ccp.customer_id NOT IN (
          SELECT customer_id FROM customer_product_affinity 
          WHERE customer_id = ccp.customer_id AND product_id = p.product_id
      )
)
SELECT 
    customer_id,
    recommended_product_id,
    recommended_product_name,
    recommendation_type,
    recommendation_score,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY recommendation_score DESC) as recommendation_rank
FROM (
    SELECT * FROM customer_affinity_recommendations
    UNION ALL
    SELECT * FROM collaborative_recommendations
    UNION ALL
    SELECT * FROM complementary_recommendations
    UNION ALL
    SELECT * FROM category_preference_recommendations
) all_recommendations
WHERE recommendation_score > 0.1 -- Minimum score threshold
ORDER BY customer_id, recommendation_score DESC;

Top Recommendations per Customer

CREATE MATERIALIZED VIEW customer_top_recommendations AS
SELECT 
    customer_id,
    recommended_product_id,
    recommended_product_name,
    recommendation_type,
    recommendation_score,
    recommendation_rank
FROM product_recommendations
WHERE recommendation_rank <= 20 -- Top 20 recommendations
ORDER BY customer_id, recommendation_rank;

Real-Time Recommendation Generation

Generate recommendations in real-time as customers browse, using cached data for performance.

MCP Tool for Recommendations

{
  "name": "get_product_recommendations",
  "description": "Retrieves personalized product recommendations for a customer based on their browsing history, purchase history, and product similarity. Use when customers are browsing products, viewing product pages, or have items in their cart.",
  "inputSchema": {
    "type": "object",
    "properties": {
      "customer_id": {
        "type": "string",
        "description": "Customer ID to generate recommendations for"
      },
      "product_id": {
        "type": "string",
        "description": "Optional: Current product being viewed (for complementary recommendations)"
      },
      "limit": {
        "type": "integer",
        "description": "Maximum number of recommendations to return",
        "minimum": 1,
        "maximum": 50,
        "default": 10
      },
      "recommendation_type": {
        "type": "string",
        "enum": ["all", "affinity_similar", "collaborative", "complementary", "category_preference"],
        "description": "Filter by recommendation type. Defaults to 'all'.",
        "default": "all"
      }
    },
    "required": ["customer_id"]
  },
  "query": "SELECT * FROM customer_top_recommendations WHERE customer_id = $1 AND (recommendation_type = $4 OR $4 = 'all') ORDER BY recommendation_rank LIMIT $3",
  "policies": ["authenticated"]
}

Real-Time Recommendation Flow

Schema Overview

The recommendation architecture uses a hierarchical schema that traces from customer interactions to personalized recommendations.

Key relationships:

• customer_interactions → customer_behavior_comprehensive (unified behavior tracking)
• customer_behavior_comprehensive → customer_product_affinity (affinity calculation)
• products → product_similarity (similarity analysis)
• orders → products_bought_together → complementary_products (relationship mapping)
• Multiple sources → product_recommendations (recommendation generation)

Traceability: Every recommendation can be traced back to customer interactions, product similarity, and collaborative filtering logic.

Closing Thoughts

Building a personalized product recommendation agent transforms e-commerce from generic to personalized. The key is treating recommendations as a data problem—not a manual curation task.

Key takeaways:

1. Behavior tracking is foundational: Capture all customer interactions. Comprehensive behavior data makes personalization possible.

2. Multiple strategies work better: Combine affinity-based, collaborative, and complementary recommendations. No single strategy works for all customers.

3. Real-time matters: Generate recommendations as customers browse. Cached recommendations enable fast response times.

4. Scoring enables ranking: Score recommendations from multiple sources. Rank by score to show the most relevant products first.

5. Performance requires optimization: Pre-compute affinities and similarities. Materialized views enable fast real-time queries.

The architecture described here is repeatable. Start with basic behavior tracking, add affinity calculation, then implement similarity analysis and recommendation generation. Each stage builds on the previous one.

Most importantly: this isn't about replacing human curation—it's about scaling it. Merchandisers still define product relationships and strategies. The recommendation agent provides automated personalization, enabling teams to scale personalization across millions of customers and thousands of products.

This architecture pattern is implemented in Pylar, but the concepts apply to any system generating product recommendations. The key is the behavior tracking → affinity calculation → similarity analysis → recommendation generation pipeline.