Recipe

Affiliate / sub-ID tracking

Stamp every contact with the affiliate, click-tracker ID, and dollar value that brought them in. Once it's on the contact you can segment campaigns by source, attribute conversions back to specific affiliates, and pay commissions without rebuilding the data in your warehouse.

Why bother

If you run partner / affiliate / paid-acquisition traffic into your signup form, the click-tracker (Voluum, RedTrack, Everflow, etc.) passes you a few extra IDs on the inbound URL. You need to capture them on the contact at create-time — otherwise by the time the contact opens your welcome email two weeks later, the original referrer is long gone from session storage.

Concretely, you want to be able to answer:

• "Which affiliate sent us this paying customer?" — for commission payout
• "What was the open-rate on Q4 campaign traffic vs. Q3?" — for source-quality scoring
• "Suppress affiliate 42 from the next blast." — for source-level pause/unpause

Recommended custom_fields keys

SendBolt's contacts.custom_fields is a free-form JSONB column — you can stuff anything in. But coordinating naming across your signup form, segment definitions, and downstream reports is the actual work. The keys below are the convention we recommend (and what most tenants migrating off Pinpointe-style ESPs already use):

If you're also doing UTM-based attribution, layer source, source_url, and the utm_* family alongside these — those are covered (or will be, in W185) in a separate UTM-tracking recipe. The two systems play nicely: aff is your commercial partner ID, utm_source is the marketing channel that delivered them.

Case-sensitivity note: SID, S1, and Value1care intentionally capitalised to match the legacy Pinpointe URL parameters affiliates already template against. If you're starting fresh and have no inherited affiliate URLs, pick whatever case you like — but be consistent across signup, segment, and report.

How to capture on signup

Parse the inbound URL on your signup page, then pass the extracted values straight through to POST /api/v1/contacts in the custom_fields object:

// app/api/signup/route.ts
import { NextRequest, NextResponse } from "next/server";

export async function POST(req: NextRequest) {
  const body = await req.json();
  const { email, first_name, aff, SID, S1 } = body;

  // Persist locally first; an affiliate ID is your own truth-of-record,
  // not SendBolt's.
  const user = await db.users.create({ email, first_name, aff, SID, S1 });

  // Stamp the contact on SendBolt with the same attribution.
  await fetch(`${process.env.SB_API_URL}/api/v1/contacts`, {
    method: "POST",
    headers: {
      "Authorization": `Bearer ${process.env.SB_API_KEY}`,
      "Content-Type": "application/json",
    },
    body: JSON.stringify({
      email,
      first_name,
      custom_fields: {
        aff: aff ? Number(aff) : null,
        SID: SID || null,
        S1: S1 || null,
        // Value1c is set later, on conversion — leave it absent for now.
      },
    }),
  });

  return NextResponse.json({ ok: true });
}

On conversion (your Stripe / Paddle / internal purchase webhook), patch the contact with the dollar value so reports can roll up revenue by affiliate:

# Look up contact by email, then PUT the merged custom_fields.
# (PUT replaces the whole custom_fields object — re-send the existing keys.)
curl -X PUT "$SENDBOLT_API_URL/api/v1/contacts/$CONTACT_ID" \
  -H "Authorization: Bearer $SENDBOLT_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "custom_fields": {
      "aff": 42,
      "SID": "click-abc123",
      "S1": "campaign-q4",
      "Value1c": "149.00"
    }
  }'

How to segment by affiliate / sub-ID

Segment definitions reference custom_fields via dotted field names: custom_fields.aff, custom_fields.SID, etc. The grammar supports eq, ne, contains, starts_with, gt, lt, gte, lte, and between on these fields. AND/OR is chosen at the definition level via op.

Example: build a segment of everyone from affiliate 42, OR anyone whose SID starts with the Q4 campaign tag:

curl -X POST "$SENDBOLT_API_URL/api/v1/segments" \
  -H "Authorization: Bearer $SENDBOLT_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Q4 affiliate cohort",
    "definition": {
      "op": "OR",
      "rules": [
        {
          "type": "field",
          "field": "custom_fields.aff",
          "operator": "eq",
          "value": "42"
        },
        {
          "type": "field",
          "field": "custom_fields.SID",
          "operator": "contains",
          "value": "campaign-q4"
        }
      ]
    }
  }'

Preview the row count without persisting — handy for sanity-checking a rule before you point a 50k blast at it:

# Preview an already-saved segment (returns recipient count + sample emails)
curl -X GET "$SENDBOLT_API_URL/api/v1/segments/$SEGMENT_ID/preview?limit=20" \
  -H "Authorization: Bearer $SENDBOLT_API_KEY"

Notes on the grammar:

• Values are strings on the wire. aff: 42 on the contact is fine as an integer, but on the segment rule you pass "value": "42". The builder does a JSONB ->> extract (text), then compares
• No in operator. To match several affiliate IDs at once, OR multiple eq rules with op: "OR", or use between for a contiguous numeric range
• Custom-field keys are validated against ^[a-zA-Z0-9_-]+$ — keep keys ASCII, no spaces or dots inside the key itself
• Combine with list include/exclude by adding include / exclude arrays of source refs alongside rules — see the segment builder UI for the full shape

Wire it into a campaign

Once the segment exists, point a campaign at it via segment_id in the POST /api/v1/campaigns payload (same shape as the newsletter recipe, just segment_id instead of list_id). Or use the in-dashboard targeting picker at /dashboard/campaigns/new.

Phase 2 — when JSONB stops being enough

JSONB filtering on custom_fields uses Postgres ->>extracts at query time. For tenants under ~5M contacts and segment refresh latency under a few seconds, this is fine and you don't need to do anything. Past that scale:

• Add a GIN index on custom_fields with jsonb_path_ops. Hits any rule that uses ? / ?& / @> in the generated SQL
• Or promote hot keys to real columns — contacts.aff_id INT, contacts.sid TEXT, with B-tree indexes. The segment builder already special-cases first-class columns; a one-line allowlist addition is all it takes to expose them

A dedicated wave (likely W187-B) will land this promotion once a tenant's segment-refresh latency or row counts cross the threshold. For now, JSONB + the grammar above covers everything you need.

Don't forget

• Strip empty-string values before posting — sending "aff": "" instead of omitting the key pollutes your segments (a contains-anything rule will match it)
• Don't leak affiliate IDs in the email body. If you template {{custom_fields.aff}}into the rendered HTML, you'll show partner 42 to a recipient and the screenshot will end up on Twitter. Keep affiliate metadata server-side
• Honour the master unsubscribe.Affiliate-source attribution doesn't override a user's opt-out — the campaign engine excludes unsubscribed contacts before evaluating the segment rules
• Reconcile with your click-tracker daily. Affiliates will dispute payouts if your numbers diverge from Voluum / RedTrack by more than ~1%. A nightly job that joins contacts.custom_fields->>'SID'against the tracker's click table catches drift early