Vertical Collections
Parent: Store OVERVIEWStatus: 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.Copy
┌─────────────────────────────────────────────────────────────────────────┐
│ 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
Copy
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
| Type | Purpose | Update Frequency |
|---|---|---|
| Vertical | Match content verticals | Real-time (webhook) |
| Smart | Auto-populate by rules | Hourly |
| Manual | Curated by team | As needed |
| Dynamic | AI-powered recommendations | Per request |
Schema
Vertical-Collection Mapping
Copy
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);
Product-Collection Links
Copy
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
Copy
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
Copy
// 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
Copy
// 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
Copy
// 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
Copy
// 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
Copy
// 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)
}
Trending Products Collection
Copy
// 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:Copy
// 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
Copy
// 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
Copy
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
Copy
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
Copy
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
Copy
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
Copy
// 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
Copy
// 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`)
}
Related Documentation
| Document | Purpose |
|---|---|
| shopify-sync.md | Product synchronization |
| dropship-network.md | Fulfillment sources |
| fulfillment.md | Order processing |
| ../publisher/affiliate-system.md | Affiliate revenue |