Multi-Brand CRM & Lead Management Platform

The Problem

A customer buys from Brand A in January, subscribes to Brand B in March, then requests a refund through Brand C. Your support team has no idea it’s the same person. Three brands, three databases, three separate customer histories for one human being. Now try calculating lifetime value from that.

The Solution

A unified platform that brings together data from three sources: e-commerce orders from billing platforms, email campaign data from the email marketing platform, and pageviews and clicks from marketing landing pages. All of it normalized into a single database designed for cross-brand analytics from day one.

The platform is split into two modules:

Marketing Data Capture Engine

This module ingests data from two sources: email sending logs (sends, opens, clicks) from the email marketing platform, and landing page traffic (form submissions with marketing attribution parameters).

For landing page captures, a 10-middleware terminating chain handles the full processing flow. The redirect fires immediately, then all data persistence runs asynchronously in each middleware’s terminate() method: email deduplication with hashed identifiers, GeoIP lookup, marketing parameter storage, full HTTP request/response logging, crawler detection, contact record creation, outcome classification (5 types), per-field error categorization (8 types), and async relay job dispatch.

An intelligent redirect routing engine determines the post-submission URL based on three knowledge dimensions: whether the contact is known (exists in the system), known_active (has any active relationship), or known_active_same_brand (active under the same brand). Rules support wildcards across theme, partner, product, and brand.

Beyond simple active/inactive tracking, the system handles duplicate detection, soft-delete on unsubscription (preserving history), restored contact tracking when previously removed users return, and knowledge flags per event for precise funnel analysis.

The outbound integration layer dispatches affiliate postbacks and lead relay jobs asynchronously, with configurable S2S routing (redirect vs. POST depending on the network), full relay logging with payload and response tracking, and a resend command for failed events.

All public-facing entity references (partners, products, themes, brands) use hash-based IDs instead of sequential database IDs, with database indexes for lookup performance.

CRM Sales Module

Authenticated REST endpoints accept normalized JSON payloads from multiple e-commerce platforms. The ingestion pipeline runs inside a database transaction:

  1. Deduplicates emails, physical addresses, and personal info (unique composites enabling cross-brand customer matching)
  2. Resolves market and brand
  3. Creates or finds brand-specific client accounts
  4. For each order item: resolves product, product offer, subscription, and creates order item, invoice, and invoice item records
  5. Creates the parent order with accumulated totals
  6. Updates running financial totals on the customer account

The data model is heavily normalized across 30+ tables and designed for cross-brand analytics:

  • Shared entities: all personal data (emails, physical addresses, personal info) is deduplicated globally and stored with hashed identifiers (MD5 + SHA-256) to avoid PII exposure. The same person purchasing from different brands is linked via these shared records, enabling cross-brand customer identification.
  • Brand-scoped accounts: each brand + market + email combination gets its own BrandClientAccount with 6 running financial totals (invoiced, credited, paid, refunded, balance, outstanding).
  • Full financial lifecycle: orders, order items, invoices, invoice items, credit notes, credit note items, payments, and refunds, all linked and tracked.
  • Subscription tracking: 7 lifecycle states (Draft, Activation, Pending, Active, Suspended, Cancelled, Expired) with occurrence tracking and history logging.

This structure enables LTV calculations by acquisition period, coupon/campaign ROI segmented by new vs. existing customers, and cohort analysis by product and customer segment.

Refund Attribution

The upstream data had no link between a refund and the purchase it belonged to. The billing platform just tracked a running balance per customer account and allowed refunds as long as the balance was positive. To build proper financial reporting, I designed a waterfall attribution algorithm that matches each refund to the most recent invoice for the same product, cascading to older invoices until the refund amount is exhausted. Credit note records are created atomically, updating financial totals across the order, order item, and customer account levels in a single transaction.

Tech Stack

Outcomes

  • 30+ table normalized data model across both marketing and CRM modules
  • Cross-brand customer identification via globally deduplicated, hashed personal data
  • Full financial lifecycle tracked per customer: orders, invoices, credit notes, payments, refunds, with 6 running totals per account
  • 7 lifecycle states with duplicate detection, soft-delete unsubscription, and restored contact tracking
  • LTV, cohort, and campaign ROI analytics enabled by the normalized data model
  • Sub-second landing page redirects with all data persistence running asynchronously via the terminating middleware chain
  • Complete audit trail for every interaction: full HTTP request/response, GeoIP, marketing params, outcome classification, and error categorization
  • Affiliate postback and lead relay integration with S2S routing, full payload/response logging, and failed event resend capability
  • 20+ test files covering every middleware handler, subscription flows, redirect rules, and model behavior