Affiliate Program Platform Development

Affiliate Program Platform Development

Affiliate program allows traffic owners to promote product for commission. Partner gets unique link, brings customer, system records this and credits reward. Technically the task is reliable tracking, honest commission calculation and convenient partner dashboard.

Data Schema

CREATE TABLE affiliates (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID UNIQUE NOT NULL REFERENCES users(id),
    ref_code        VARCHAR(20) UNIQUE NOT NULL,
    status          VARCHAR(20) NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending','active','suspended','terminated')),
    commission_rate NUMERIC(5,2) NOT NULL DEFAULT 10,  -- % of amount
    commission_type VARCHAR(20) NOT NULL DEFAULT 'percent'
                    CHECK (commission_type IN ('percent','fixed','hybrid')),
    fixed_amount    NUMERIC(12,2),           -- for fixed/hybrid types
    cookie_days     INTEGER NOT NULL DEFAULT 30,
    payout_min      NUMERIC(12,2) NOT NULL DEFAULT 1000,
    approved_at     TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE affiliate_links (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    affiliate_id    UUID NOT NULL REFERENCES affiliates(id),
    campaign_name   VARCHAR(200),
    landing_url     VARCHAR(500) NOT NULL,
    params          JSONB DEFAULT '{}',  -- UTM and custom parameters
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE affiliate_clicks (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    affiliate_id    UUID NOT NULL REFERENCES affiliates(id),
    link_id         UUID REFERENCES affiliate_links(id),
    ip              INET,
    user_agent      TEXT,
    referrer        VARCHAR(500),
    fingerprint     VARCHAR(64),   -- device fingerprint for cross-device
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_affiliate_clicks_date ON affiliate_clicks(affiliate_id, created_at DESC);

CREATE TABLE affiliate_conversions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    affiliate_id    UUID NOT NULL REFERENCES affiliates(id),
    click_id        UUID REFERENCES affiliate_clicks(id),
    customer_id     UUID REFERENCES users(id),
    order_id        UUID,
    order_amount    NUMERIC(15,2),
    commission      NUMERIC(15,2) NOT NULL,
    status          VARCHAR(20) NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending','confirmed','rejected','paid')),
    rejection_reason VARCHAR(200),
    hold_days       INTEGER NOT NULL DEFAULT 30,
    available_at    DATE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Click Tracking

import hashlib
from django.core.cache import cache


def track_affiliate_click(request, ref_code: str, landing_url: str) -> str | None:
    affiliate = Affiliate.objects.filter(
        ref_code=ref_code, status='active'
    ).first()

    if not affiliate:
        return None

    # Device fingerprint for cross-device attribution
    fingerprint_raw = f'{request.META.get("HTTP_USER_AGENT")}:{request.META.get("ACCEPT_LANGUAGE")}'
    fingerprint = hashlib.sha256(fingerprint_raw.encode()).hexdigest()[:32]

    click = AffiliateClick.objects.create(
        affiliate=affiliate,
        ip=get_client_ip(request),
        user_agent=request.META.get('HTTP_USER_AGENT', ''),
        referrer=request.META.get('HTTP_REFERER', ''),
        fingerprint=fingerprint,
    )

    # Cookie for attribution
    cookie_value = str(click.id)
    return cookie_value  # set in response

Conversion Attribution

def attribute_conversion(order, request):
    """Called after successful order payment"""
    click_id = request.COOKIES.get('aff_click')
    affiliate = None
    click = None

    if click_id:
        click = AffiliateClick.objects.filter(id=click_id).first()
        if click:
            # Check if cookie window hasn't expired
            cutoff = timezone.now() - timedelta(days=click.affiliate.cookie_days)
            if click.created_at >= cutoff:
                affiliate = click.affiliate

    if not affiliate:
        # Fallback: search by fingerprint (cross-device)
        fingerprint = compute_fingerprint(request)
        recent_click = AffiliateClick.objects.filter(
            fingerprint=fingerprint,
            created_at__gte=timezone.now() - timedelta(days=30)
        ).order_by('-created_at').first()
        if recent_click:
            affiliate = recent_click.affiliate
            click = recent_click

    if affiliate:
        commission = calculate_commission(affiliate, order.total_amount)
        AffiliateConversion.objects.create(
            affiliate=affiliate,
            click=click,
            customer=order.user,
            order=order,
            order_amount=order.total_amount,
            commission=commission,
            hold_days=affiliate.hold_days,
            available_at=date.today() + timedelta(days=affiliate.hold_days),
        )

        # Invalidate cookie after conversion
        return True

    return False


def calculate_commission(affiliate, order_amount: Decimal) -> Decimal:
    if affiliate.commission_type == 'percent':
        return (order_amount * affiliate.commission_rate / 100).quantize(Decimal('0.01'))
    elif affiliate.commission_type == 'fixed':
        return affiliate.fixed_amount
    else:  # hybrid
        percent_part = order_amount * affiliate.commission_rate / 100
        return (percent_part + affiliate.fixed_amount).quantize(Decimal('0.01'))

Multi-Level Affiliate Program

class AffiliateRelation(models.Model):
    """Partner tree for multi-level program"""
    affiliate = models.OneToOneField(Affiliate, on_delete=models.CASCADE)
    parent = models.ForeignKey(
        'self', null=True, blank=True,
        on_delete=models.SET_NULL, related_name='children'
    )
    level = models.IntegerField(default=1)


REFERRAL_RATES = {
    1: Decimal('10'),   # direct partner: 10%
    2: Decimal('3'),    # partner of partner: 3%
    3: Decimal('1'),    # third level: 1%
}


def distribute_multilevel_commission(conversion):
    """Credit commission through entire chain upward"""
    relation = AffiliateRelation.objects.filter(
        affiliate=conversion.affiliate
    ).first()

    level = 1
    current = relation

    while current and level <= 3:
        rate = REFERRAL_RATES.get(level)
        if rate:
            commission = (conversion.order_amount * rate / 100).quantize(Decimal('0.01'))
            AffiliateConversion.objects.create(
                affiliate=current.affiliate,
                order=conversion.order,
                order_amount=conversion.order_amount,
                commission=commission,
                status='pending',
                hold_days=current.affiliate.hold_days,
                available_at=date.today() + timedelta(days=current.affiliate.hold_days),
            )
        current = current.parent
        level += 1

Partner Payouts

@shared_task
def process_affiliate_payouts():
    """Daily: pay partners with available balance"""
    affiliates_with_balance = (
        Affiliate.objects
        .annotate(
            available=Coalesce(
                Subquery(
                    AffiliateConversion.objects.filter(
                        affiliate=OuterRef('pk'),
                        status='confirmed',
                        available_at__lte=date.today()
                    ).values('affiliate').annotate(s=Sum('commission')).values('s')
                ),
                Decimal('0')
            )
        )
        .filter(available__gte=F('payout_min'), status='active')
    )

    for affiliate in affiliates_with_balance:
        initiate_payout(affiliate, affiliate.available)

Anti-Fraud

def check_conversion_fraud(conversion) -> bool:
    """Basic fraud checks"""
    # Self-referral
    if conversion.customer == conversion.affiliate.user:
        conversion.status = 'rejected'
        conversion.rejection_reason = 'self_referral'
        conversion.save()
        return True

    # Too many conversions from one IP per hour
    recent_from_ip = AffiliateConversion.objects.filter(
        click__ip=conversion.click.ip if conversion.click else None,
        created_at__gte=timezone.now() - timedelta(hours=1)
    ).count()

    if recent_from_ip > 10:
        flag_for_review(conversion, 'high_conversion_rate_from_ip')
        return True

    # Conversion within seconds after click
    if conversion.click:
        time_to_convert = (conversion.created_at - conversion.click.created_at).total_seconds()
        if time_to_convert < 30:
            flag_for_review(conversion, 'instant_conversion')
            return True

    return False

Timeline

Basic affiliate program (single-level, percent commission, partner dashboard, card payout): 3–4 weeks. With multi-level structure, anti-fraud, detailed campaign analytics and automatic payouts: 6–8 weeks.