hub/docs/segmentation.md

Segmentation System

Overview

The segmentation system allows brands to create dynamic audience segments based on buyer behavior, demographics, and purchase history. Segments are brand-scoped and automatically refresh to reflect current data.

Database Schema

segments Table

• id - Primary key
• brand_id - Owner brand (scoped to single brand)
• name - Segment name (e.g., "High-Value Buyers", "Inactive 90 Days")
• description - Purpose/criteria description
• conditions - JSON array of conditions (see format below)
• buyer_count - Cached count (refreshed periodically)
• last_refreshed_at - Last time segment was calculated
• is_active - Toggle for active/inactive
• created_at, updated_at

segment_buyers Pivot Table

• segment_id
• buyer_business_id - Business in the segment
• Tracks which buyers currently match segment conditions

Condition Format

Conditions stored as JSON array in segments.conditions:

[
  {
    "field": "total_order_value",
    "operator": "greater_than",
    "value": 10000
  },
  {
    "field": "last_order_date",
    "operator": "within_days",
    "value": 30
  },
  {
    "field": "purchased_product_sku",
    "operator": "equals",
    "value": "TB-BM-AZ1G"
  }
]

Available Fields

Buyer Fields

• total_order_value - Lifetime order total
• total_order_count - Number of orders placed
• average_order_value - Average order amount
• last_order_date - Date of most recent order
• first_order_date - Date of first order
• buyer_type - Business type
• buyer_state - Geographic state
• buyer_city - Geographic city

Product Purchase Fields

• purchased_product_sku - Has purchased specific SKU
• purchased_product_category - Has purchased from category
• purchased_brand - Has purchased from specific brand
• purchase_frequency - Orders per month

Available Operators

• equals - Exact match
• not_equals - Does not match
• greater_than - Numeric comparison (>)
• less_than - Numeric comparison (<)
• greater_than_or_equal - Numeric comparison (>=)
• less_than_or_equal - Numeric comparison (<=)
• contains - String contains substring
• within_days - Date within X days
• older_than_days - Date older than X days

Segment Refresh Process

Automatic Refresh

Segments refresh automatically via scheduled task:

Schedule: Hourly (or configurable)

Command: php artisan segments:refresh

Process:

1. Loop through all active segments
2. For each segment, query buyers matching conditions
3. Update segment_buyers pivot table
4. Update buyer_count and last_refreshed_at

Manual Refresh

Admin can trigger refresh:

• Via admin panel: "Refresh Segment" button
• Route: POST /s/{business}/marketing/segments/{segment}/refresh

Refresh Logic

// Example refresh logic
$segment = Segment::find($id);
$query = Business::where('type', 'buyer');

foreach ($segment->conditions as $condition) {
    switch ($condition['field']) {
        case 'total_order_value':
            $query->whereHas('orders', function($q) use ($condition) {
                $q->havingRaw('SUM(total) ' . $condition['operator'] . ' ?', [$condition['value']]);
            });
            break;

        case 'last_order_date':
            if ($condition['operator'] === 'within_days') {
                $query->whereHas('orders', function($q) use ($condition) {
                    $q->where('created_at', '>=', now()->subDays($condition['value']));
                });
            }
            break;

        case 'purchased_product_sku':
            $query->whereHas('orders.items.product', function($q) use ($condition) {
                $q->where('sku', $condition['operator'], $condition['value']);
            });
            break;
    }
}

$buyers = $query->pluck('id');
$segment->buyers()->sync($buyers);
$segment->update([
    'buyer_count' => $buyers->count(),
    'last_refreshed_at' => now()
]);

Brand Scoping

Critical: Segments are ALWAYS scoped to a single brand.

Why Brand-Scoped?

1. Privacy: Brand A shouldn't see Brand B's buyers
2. Relevance: Segments based on purchases from specific brand
3. Campaign Targeting: Campaigns send from a brand to their buyers

Implementation

// Controller
$segments = Segment::where('brand_id', $brand->id)->get();

// Creating segment
$segment = $brand->segments()->create([
    'name' => $request->name,
    'conditions' => $request->conditions
]);

Usage in Campaigns

Segments are used to target campaigns:

1. Campaign Creation: Select segments to target
2. Recipient Resolution: When campaign sends:
   • Resolve segment → get buyer list
   • For each buyer, send message
3. Dynamic: Segment refreshes before send for latest data

Example:

$campaign->segments()->attach([1, 2, 3]); // Attach multiple segments
$buyers = $campaign->segments->flatMap->buyers->unique('id');
// Send campaign to each buyer

Segment Builder UI

Creating Segments (/s/{business}/marketing/segments/create)

Form Fields:

• Name (required)
• Description
• Conditions builder:
  • Add condition button
  • Field dropdown
  • Operator dropdown (dynamic based on field)
  • Value input (text/number/date based on field)
  • Remove condition button

Condition Builder

<div x-data="{ conditions: [] }">
    <template x-for="(condition, index) in conditions">
        <div class="flex gap-2">
            <select x-model="condition.field">
                <option value="total_order_value">Total Order Value</option>
                <option value="last_order_date">Last Order Date</option>
                {{-- etc --}}
            </select>

            <select x-model="condition.operator">
                {{-- Dynamic based on field type --}}
            </select>

            <input type="text" x-model="condition.value">

            <button @click="conditions.splice(index, 1)">Remove</button>
        </div>
    </template>

    <button @click="conditions.push({field: '', operator: '', value: ''})">
        Add Condition
    </button>
</div>

Performance Considerations

• Caching: Store buyer_count to avoid expensive queries on list view
• Indexing: Index on brand_id, is_active, last_refreshed_at
• Queue: Run refresh in background job for large segments
• Throttling: Limit refresh frequency (max once per hour per segment)

Future Enhancements

• Condition groups (AND/OR logic)
• Nested conditions
• Predictive segments (ML-based)
• Segment comparison (overlap analysis)
• Export segment members to CSV