Skip to main content

Vertical Collections

Parent: Store OVERVIEW
Status: Active
Integration: Publisher ↔ Store

Overview

Vertical Collections connect Publisher content to Store products. Each content vertical (golf, wellness, tech, etc.) has a matching product collection, enabling contextual commerce throughout the content experience.
┌─────────────────────────────────────────────────────────────────────────┐
│                    VERTICAL COLLECTIONS ARCHITECTURE                     │
├─────────────────────────────────────────────────────────────────────────┤
│                                                                          │
│   PUBLISHER VERTICAL                         STORE COLLECTION            │
│   ┌─────────────────┐                       ┌─────────────────┐         │
│   │  golf/          │ ──────────────────▶   │  Golf Gear      │         │
│   │  • Best Drivers │                       │  • Supplements  │         │
│   │  • Swing Tips   │                       │  • Equipment    │         │
│   │  • Course Guide │                       │  • Apparel      │         │
│   └─────────────────┘                       └─────────────────┘         │
│                                                                          │
│   CONTENT INTELLIGENCE                                                   │
│   ┌─────────────────────────────────────────────────────────────────┐   │
│   │  Post Analysis → Product Matching → Dynamic Recommendations      │   │
│   │                                                                  │   │
│   │  "Best golf drivers for seniors"                                │   │
│   │       ↓                                                          │   │
│   │  Keywords: [golf, drivers, seniors, distance, forgiveness]      │   │
│   │       ↓                                                          │   │
│   │  Products: Joint supplements, lightweight drivers, swing aids   │   │
│   └─────────────────────────────────────────────────────────────────┘   │
│                                                                          │
└─────────────────────────────────────────────────────────────────────────┘

Collection Hierarchy

Primary Structure

Store (trendingsociety.com)
├── All Products
├── Verticals (auto-synced from Publisher)
│   ├── Golf
│   ├── Wellness
│   ├── Tech
│   ├── Travel
│   ├── Fitness
│   ├── Beauty
│   └── [23 total verticals]
├── Product Type
│   ├── Supplements
│   ├── Digital Products
│   ├── Equipment
│   └── Apparel
├── Source
│   ├── Supliful (White-label)
│   ├── Collective (Curated)
│   └── Digital
└── Featured
    ├── Best Sellers
    ├── New Arrivals
    └── Staff Picks

Collection Types

TypePurposeUpdate Frequency
VerticalMatch content verticalsReal-time (webhook)
SmartAuto-populate by rulesHourly
ManualCurated by teamAs needed
DynamicAI-powered recommendationsPer request

Schema

Vertical-Collection Mapping

CREATE TABLE vertical_collections (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Links
  vertical_id uuid REFERENCES publisher_verticals(id) UNIQUE NOT NULL,
  shopify_collection_id bigint UNIQUE,
  shopify_collection_handle text,
  
  -- Collection info
  collection_title text NOT NULL,
  collection_description text,
  
  -- Display
  featured_image_url text,
  sort_order text DEFAULT 'best-selling',  -- 'best-selling', 'price-asc', 'price-desc', 'created-desc', 'manual'
  
  -- Rules for smart collection
  is_smart_collection boolean DEFAULT true,
  smart_rules jsonb DEFAULT '[]',           -- Shopify smart collection rules
  
  -- Content integration
  related_post_count integer DEFAULT 0,
  featured_posts uuid[],                    -- Manually featured posts
  
  -- Performance
  product_count integer DEFAULT 0,
  total_revenue numeric(10,2) DEFAULT 0,
  conversion_rate numeric,
  
  -- SEO
  seo_title text,
  seo_description text,
  
  -- Status
  status text DEFAULT 'active',
  published_at timestamptz,
  
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE INDEX idx_vertical_collections_vertical ON vertical_collections(vertical_id);
CREATE INDEX idx_vertical_collections_shopify ON vertical_collections(shopify_collection_id);
CREATE TABLE product_collections (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  
  product_id uuid REFERENCES shopify_products(id),
  collection_id uuid REFERENCES vertical_collections(id),
  
  -- Position in collection
  position integer,
  
  -- Assignment
  assignment_type text DEFAULT 'auto',      -- 'auto', 'manual', 'ai'
  assignment_reason text,                   -- Why this product is in this collection
  
  -- Performance in collection
  impressions integer DEFAULT 0,
  clicks integer DEFAULT 0,
  conversions integer DEFAULT 0,
  revenue numeric(10,2) DEFAULT 0,
  
  created_at timestamptz DEFAULT now(),
  
  UNIQUE(product_id, collection_id)
);

CREATE INDEX idx_product_collections_product ON product_collections(product_id);
CREATE INDEX idx_product_collections_collection ON product_collections(collection_id);

Post-Product Recommendations

CREATE TABLE post_product_recommendations (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Links
  post_id uuid REFERENCES publisher_posts(id) NOT NULL,
  product_id uuid REFERENCES shopify_products(id) NOT NULL,
  
  -- Recommendation details
  recommendation_type text,                 -- 'keyword', 'vertical', 'ai', 'manual', 'affiliate'
  relevance_score numeric,                  -- 0-1 confidence score
  position integer,                         -- Display order
  
  -- Context
  context_keywords text[],                  -- Keywords that triggered match
  placement text,                           -- 'inline', 'sidebar', 'bottom', 'popup'
  
  -- Performance
  impressions integer DEFAULT 0,
  clicks integer DEFAULT 0,
  conversions integer DEFAULT 0,
  revenue numeric(10,2) DEFAULT 0,
  
  -- A/B testing
  variant text,
  
  -- Status
  status text DEFAULT 'active',
  
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now(),
  
  UNIQUE(post_id, product_id, placement)
);

CREATE INDEX idx_post_recommendations_post ON post_product_recommendations(post_id);
CREATE INDEX idx_post_recommendations_product ON post_product_recommendations(product_id);
CREATE INDEX idx_post_recommendations_score ON post_product_recommendations(relevance_score DESC);

Collection Sync

Create Collection from Vertical

// When new vertical created, create matching collection
async function createCollectionForVertical(vertical) {
  const client = new Shopify.Clients.Graphql(
    Deno.env.get('SHOPIFY_SHOP_DOMAIN'),
    Deno.env.get('SHOPIFY_ACCESS_TOKEN')
  )
  
  // Create smart collection in Shopify
  const { body } = await client.query({
    data: `
      mutation createCollection($input: CollectionInput!) {
        collectionCreate(input: $input) {
          collection {
            id
            legacyResourceId
            handle
          }
          userErrors { field message }
        }
      }
    `,
    variables: {
      input: {
        title: vertical.name,
        descriptionHtml: `<p>Products curated for ${vertical.name.toLowerCase()} enthusiasts.</p>`,
        handle: vertical.slug,
        seo: {
          title: `${vertical.name} Products | Trending Society`,
          description: `Shop the best ${vertical.name.toLowerCase()} products, handpicked by our editorial team.`
        },
        ruleSet: {
          appliedDisjunctively: false,
          rules: [
            {
              column: 'TAG',
              relation: 'EQUALS',
              condition: vertical.slug
            }
          ]
        }
      }
    }
  })
  
  const collection = body.data.collectionCreate.collection
  
  // Save mapping
  await supabase
    .from('vertical_collections')
    .insert({
      vertical_id: vertical.id,
      shopify_collection_id: parseInt(collection.legacyResourceId),
      shopify_collection_handle: collection.handle,
      collection_title: vertical.name,
      is_smart_collection: true,
      smart_rules: [{ column: 'TAG', relation: 'EQUALS', condition: vertical.slug }],
      published_at: new Date().toISOString()
    })
  
  return collection
}

Sync Products to Collections

// Ensure product is in correct vertical collection
async function syncProductToCollections(product) {
  // Get product's vertical
  const verticalId = product.vertical_id
  if (!verticalId) return
  
  // Get collection for this vertical
  const { data: collection } = await supabase
    .from('vertical_collections')
    .select('id, shopify_collection_id')
    .eq('vertical_id', verticalId)
    .single()
  
  if (!collection) return
  
  // Check if product already in collection
  const { data: existing } = await supabase
    .from('product_collections')
    .select('id')
    .eq('product_id', product.id)
    .eq('collection_id', collection.id)
    .single()
  
  if (!existing) {
    // Add to collection
    await supabase
      .from('product_collections')
      .insert({
        product_id: product.id,
        collection_id: collection.id,
        assignment_type: 'auto',
        assignment_reason: `Vertical match: ${product.vertical_id}`
      })
    
    // Ensure product has vertical tag in Shopify
    await addProductTag(product.shopify_id, product.vertical_slug)
  }
}

// Add tag to product (triggers smart collection inclusion)
async function addProductTag(shopifyProductId, tag) {
  const client = new Shopify.Clients.Graphql(
    Deno.env.get('SHOPIFY_SHOP_DOMAIN'),
    Deno.env.get('SHOPIFY_ACCESS_TOKEN')
  )
  
  await client.query({
    data: `
      mutation addTags($id: ID!, $tags: [String!]!) {
        tagsAdd(id: $id, tags: $tags) {
          node { ... on Product { id tags } }
          userErrors { field message }
        }
      }
    `,
    variables: {
      id: `gid://shopify/Product/${shopifyProductId}`,
      tags: [tag]
    }
  })
}

Product-Content Matching

Keyword-Based Matching

// Match products to post based on content analysis
async function matchProductsToPost(post) {
  // Get post's content analysis
  const { data: analysis } = await supabase
    .from('content_analysis')
    .select('keywords, entities, topics, sentiment')
    .eq('content_id', post.id)
    .single()
  
  if (!analysis) return []
  
  // Extract relevant keywords
  const keywords = [
    ...analysis.keywords,
    ...analysis.entities.map(e => e.name),
    ...analysis.topics
  ].map(k => k.toLowerCase())
  
  // Find products matching keywords
  const { data: products } = await supabase
    .from('shopify_products')
    .select(`
      id,
      shopify_id,
      title,
      tags,
      price,
      featured_image_url,
      vertical_id
    `)
    .eq('status', 'active')
    .eq('vertical_id', post.vertical_id)  // Same vertical
    .order('total_orders', { ascending: false })
    .limit(20)
  
  // Score products by keyword match
  const scored = products.map(product => {
    const productKeywords = [
      ...product.tags,
      product.title.toLowerCase().split(' ')
    ].flat()
    
    const matchCount = keywords.filter(k => 
      productKeywords.some(pk => pk.includes(k) || k.includes(pk))
    ).length
    
    const relevanceScore = matchCount / Math.max(keywords.length, 1)
    
    return {
      ...product,
      relevance_score: relevanceScore,
      matched_keywords: keywords.filter(k => 
        productKeywords.some(pk => pk.includes(k) || k.includes(pk))
      )
    }
  })
  
  // Filter and sort by relevance
  return scored
    .filter(p => p.relevance_score > 0.1)
    .sort((a, b) => b.relevance_score - a.relevance_score)
    .slice(0, 6)
}

// Save recommendations to database
async function savePostRecommendations(postId, recommendations) {
  const records = recommendations.map((rec, index) => ({
    post_id: postId,
    product_id: rec.id,
    recommendation_type: 'keyword',
    relevance_score: rec.relevance_score,
    position: index + 1,
    context_keywords: rec.matched_keywords,
    placement: 'sidebar',
    status: 'active'
  }))
  
  // Upsert recommendations
  await supabase
    .from('post_product_recommendations')
    .upsert(records, {
      onConflict: 'post_id,product_id,placement'
    })
}

AI-Powered Matching

// Use embeddings for semantic product matching
async function semanticProductMatch(postContent, verticalId) {
  // Generate embedding for post content
  const postEmbedding = await generateEmbedding(postContent)
  
  // Find products with similar embeddings
  const { data: products } = await supabase.rpc('match_products_by_embedding', {
    query_embedding: postEmbedding,
    vertical_filter: verticalId,
    match_threshold: 0.7,
    match_count: 10
  })
  
  return products.map(p => ({
    ...p,
    recommendation_type: 'ai',
    relevance_score: p.similarity
  }))
}

// Supabase function for vector similarity search
/*
CREATE OR REPLACE FUNCTION match_products_by_embedding(
  query_embedding vector(1536),
  vertical_filter uuid,
  match_threshold float DEFAULT 0.7,
  match_count int DEFAULT 10
)
RETURNS TABLE (
  id uuid,
  shopify_id bigint,
  title text,
  price numeric,
  featured_image_url text,
  similarity float
)
LANGUAGE sql STABLE
AS $$
  SELECT 
    sp.id,
    sp.shopify_id,
    sp.title,
    sp.price,
    sp.featured_image_url,
    1 - (sp.embedding <=> query_embedding) as similarity
  FROM shopify_products sp
  WHERE sp.status = 'active'
    AND sp.vertical_id = vertical_filter
    AND sp.embedding IS NOT NULL
    AND 1 - (sp.embedding <=> query_embedding) > match_threshold
  ORDER BY sp.embedding <=> query_embedding
  LIMIT match_count;
$$;
*/

Dynamic Collections

Real-Time Personalization

// Get personalized product recommendations for user
async function getPersonalizedProducts(userId, verticalId, limit = 6) {
  // Get user's audience profile
  const { data: profile } = await supabase
    .from('audience_profiles')
    .select(`
      id,
      interests,
      purchase_history,
      browsing_history,
      wtp_score
    `)
    .eq('id', userId)
    .single()
  
  if (!profile) {
    // Fall back to best sellers
    return getBestSellers(verticalId, limit)
  }
  
  // Score products based on user profile
  const { data: products } = await supabase
    .from('shopify_products')
    .select('*')
    .eq('status', 'active')
    .eq('vertical_id', verticalId)
  
  const scored = products.map(product => {
    let score = 0
    
    // Interest match
    const interestMatch = profile.interests?.filter(i => 
      product.tags.includes(i)
    ).length || 0
    score += interestMatch * 0.3
    
    // Price match (based on WTP)
    const wtpPrice = profile.wtp_score * 2  // WTP 50 = $100 comfort zone
    const priceDiff = Math.abs(product.price - wtpPrice) / wtpPrice
    score += Math.max(0, 1 - priceDiff) * 0.2
    
    // Not already purchased
    const purchased = profile.purchase_history?.includes(product.shopify_id)
    if (purchased) score -= 0.5
    
    // Browsing recency boost
    const browsed = profile.browsing_history?.find(b => 
      b.product_id === product.shopify_id
    )
    if (browsed) {
      const daysSinceBrowse = (Date.now() - new Date(browsed.timestamp)) / 86400000
      if (daysSinceBrowse < 7) score += 0.2
    }
    
    // Popularity boost
    score += Math.min(product.total_orders / 100, 0.3)
    
    return { ...product, personalization_score: score }
  })
  
  return scored
    .sort((a, b) => b.personalization_score - a.personalization_score)
    .slice(0, limit)
}
// Generate trending products based on recent performance
async function getTrendingProducts(verticalId, days = 7, limit = 12) {
  const { data: trending } = await supabase.rpc('get_trending_products', {
    p_vertical_id: verticalId,
    p_days: days,
    p_limit: limit
  })
  
  return trending
}

// Supabase function
/*
CREATE OR REPLACE FUNCTION get_trending_products(
  p_vertical_id uuid,
  p_days int DEFAULT 7,
  p_limit int DEFAULT 12
)
RETURNS TABLE (
  id uuid,
  shopify_id bigint,
  title text,
  price numeric,
  featured_image_url text,
  recent_orders int,
  trend_score float
)
LANGUAGE sql STABLE
AS $$
  WITH recent_sales AS (
    SELECT 
      (li->>'product_id')::bigint as product_id,
      COUNT(*) as order_count,
      SUM((li->>'price')::numeric * (li->>'quantity')::int) as revenue
    FROM shopify_orders o,
    LATERAL jsonb_array_elements(o.line_items) as li
    WHERE o.processed_at > now() - (p_days || ' days')::interval
      AND o.financial_status = 'paid'
    GROUP BY 1
  ),
  previous_sales AS (
    SELECT 
      (li->>'product_id')::bigint as product_id,
      COUNT(*) as order_count
    FROM shopify_orders o,
    LATERAL jsonb_array_elements(o.line_items) as li
    WHERE o.processed_at BETWEEN 
      now() - ((p_days * 2) || ' days')::interval 
      AND now() - (p_days || ' days')::interval
      AND o.financial_status = 'paid'
    GROUP BY 1
  )
  SELECT 
    sp.id,
    sp.shopify_id,
    sp.title,
    sp.price,
    sp.featured_image_url,
    COALESCE(rs.order_count, 0)::int as recent_orders,
    CASE 
      WHEN COALESCE(ps.order_count, 0) = 0 THEN rs.order_count::float
      ELSE (rs.order_count - ps.order_count)::float / ps.order_count
    END as trend_score
  FROM shopify_products sp
  LEFT JOIN recent_sales rs ON sp.shopify_id = rs.product_id
  LEFT JOIN previous_sales ps ON sp.shopify_id = ps.product_id
  WHERE sp.status = 'active'
    AND sp.vertical_id = p_vertical_id
    AND COALESCE(rs.order_count, 0) > 0
  ORDER BY trend_score DESC, recent_orders DESC
  LIMIT p_limit;
$$;
*/

Collection Display

Subdomain Theming

Each vertical subdomain shows its collection:
// golf.trendingsociety.com/shop
// wellness.trendingsociety.com/shop

async function getVerticalShopData(subdomain) {
  // Get vertical by subdomain
  const { data: vertical } = await supabase
    .from('publisher_verticals')
    .select('id, name, slug, theme_config')
    .eq('slug', subdomain)
    .single()
  
  if (!vertical) return null
  
  // Get collection
  const { data: collection } = await supabase
    .from('vertical_collections')
    .select(`
      *,
      products:product_collections(
        product:shopify_products(*)
      )
    `)
    .eq('vertical_id', vertical.id)
    .single()
  
  // Get featured posts for cross-promotion
  const { data: posts } = await supabase
    .from('publisher_posts')
    .select('id, title, slug, featured_image')
    .eq('vertical_id', vertical.id)
    .eq('status', 'published')
    .order('published_at', { ascending: false })
    .limit(3)
  
  return {
    vertical,
    collection,
    products: collection?.products?.map(p => p.product) || [],
    relatedPosts: posts
  }
}

In-Content Product Cards

// Component for displaying product recommendations in posts
interface ProductCardProps {
  product: ShopifyProduct
  placement: 'inline' | 'sidebar' | 'bottom'
  postId: string
}

async function ProductCard({ product, placement, postId }: ProductCardProps) {
  // Track impression
  await trackImpression(postId, product.id, placement)
  
  return {
    id: product.id,
    title: product.title,
    price: formatPrice(product.price),
    compareAtPrice: product.compare_at_price 
      ? formatPrice(product.compare_at_price) 
      : null,
    image: product.featured_image_url,
    url: `https://trendingsociety.com/products/${product.handle}?ref=${postId}`,
    cta: product.price > 50 ? 'Shop Now' : 'Add to Cart'
  }
}

// Track product card impression
async function trackImpression(postId, productId, placement) {
  await supabase.rpc('increment_recommendation_impressions', {
    p_post_id: postId,
    p_product_id: productId,
    p_placement: placement
  })
}

Performance Analytics

Collection Performance

SELECT 
  vc.collection_title,
  v.slug as vertical,
  vc.product_count,
  vc.total_revenue,
  vc.conversion_rate,
  COUNT(DISTINCT ppr.post_id) as posts_featuring_products
FROM vertical_collections vc
JOIN publisher_verticals v ON vc.vertical_id = v.id
LEFT JOIN post_product_recommendations ppr ON ppr.product_id IN (
  SELECT product_id FROM product_collections WHERE collection_id = vc.id
)
WHERE vc.status = 'active'
GROUP BY vc.id, v.slug
ORDER BY vc.total_revenue DESC;

Recommendation Performance

SELECT 
  recommendation_type,
  placement,
  SUM(impressions) as total_impressions,
  SUM(clicks) as total_clicks,
  SUM(conversions) as total_conversions,
  SUM(revenue) as total_revenue,
  ROUND(SUM(clicks)::numeric / NULLIF(SUM(impressions), 0) * 100, 2) as ctr,
  ROUND(SUM(conversions)::numeric / NULLIF(SUM(clicks), 0) * 100, 2) as conversion_rate
FROM post_product_recommendations
WHERE created_at > now() - interval '30 days'
GROUP BY recommendation_type, placement
ORDER BY total_revenue DESC;

Top Converting Post-Product Pairs

SELECT 
  pp.title as post_title,
  sp.title as product_title,
  ppr.impressions,
  ppr.clicks,
  ppr.conversions,
  ppr.revenue,
  ppr.relevance_score,
  ROUND(ppr.conversions::numeric / NULLIF(ppr.clicks, 0) * 100, 1) as conversion_rate
FROM post_product_recommendations ppr
JOIN publisher_posts pp ON ppr.post_id = pp.id
JOIN shopify_products sp ON ppr.product_id = sp.id
WHERE ppr.conversions > 0
ORDER BY ppr.revenue DESC
LIMIT 50;

Vertical Commerce Summary

SELECT 
  v.name as vertical,
  COUNT(DISTINCT sp.id) as products,
  COUNT(DISTINCT pp.id) as posts,
  COUNT(DISTINCT ppr.id) as recommendations,
  SUM(ppr.impressions) as total_impressions,
  SUM(ppr.revenue) as recommendation_revenue,
  SUM(sp.total_revenue) as total_product_revenue
FROM publisher_verticals v
LEFT JOIN vertical_collections vc ON v.id = vc.vertical_id
LEFT JOIN product_collections pc ON vc.id = pc.collection_id
LEFT JOIN shopify_products sp ON pc.product_id = sp.id
LEFT JOIN publisher_posts pp ON v.id = pp.vertical_id
LEFT JOIN post_product_recommendations ppr ON pp.id = ppr.post_id
GROUP BY v.id, v.name
ORDER BY total_product_revenue DESC NULLS LAST;

Automation

Auto-Assign Products on Import

// Trigger: New product synced from Shopify
async function onProductCreated(product) {
  // 1. Determine vertical from tags/type
  const verticalId = await determineProductVertical(product)
  
  if (verticalId) {
    // Update product with vertical
    await supabase
      .from('shopify_products')
      .update({ vertical_id: verticalId })
      .eq('id', product.id)
    
    // Add to collection
    await syncProductToCollections({ ...product, vertical_id: verticalId })
  }
  
  // 2. Generate product embedding for semantic search
  const embedding = await generateProductEmbedding(product)
  await supabase
    .from('shopify_products')
    .update({ embedding })
    .eq('id', product.id)
  
  // 3. Find relevant posts to recommend product
  await generateProductRecommendations(product)
}

async function determineProductVertical(product) {
  // Check product type mapping
  const typeMap = {
    'Supplements': 'wellness',
    'Golf Equipment': 'golf',
    'Tech Accessories': 'tech',
    'Travel Gear': 'travel',
    'Fitness Equipment': 'fitness',
    'Beauty Products': 'beauty'
  }
  
  if (typeMap[product.product_type]) {
    const { data: vertical } = await supabase
      .from('publisher_verticals')
      .select('id')
      .eq('slug', typeMap[product.product_type])
      .single()
    return vertical?.id
  }
  
  // Check tags
  for (const tag of product.tags) {
    const { data: vertical } = await supabase
      .from('publisher_verticals')
      .select('id')
      .eq('slug', tag.toLowerCase())
      .single()
    
    if (vertical) return vertical.id
  }
  
  return null
}

Auto-Update Recommendations

// Cron: Daily at 2 AM
async function refreshAllRecommendations() {
  // Get all published posts
  const { data: posts } = await supabase
    .from('publisher_posts')
    .select('id, vertical_id, content')
    .eq('status', 'published')
  
  for (const post of posts) {
    // Get fresh recommendations
    const recommendations = await matchProductsToPost(post)
    
    // Update recommendations
    await savePostRecommendations(post.id, recommendations)
    
    // Rate limit
    await new Promise(r => setTimeout(r, 100))
  }
  
  console.log(`Refreshed recommendations for ${posts.length} posts`)
}

DocumentPurpose
shopify-sync.mdProduct synchronization
dropship-network.mdFulfillment sources
fulfillment.mdOrder processing
../publisher/affiliate-system.mdAffiliate revenue