What the module replaces, and what it does not build

This module replaces the marketing automation and email subscription: products such as HubSpot Marketing Hub, Mailchimp, ActiveCampaign, Klaviyo or Customer.io. Its purpose is to grow and nurture demand with full control of the data: dynamic segments, email campaigns and automated journeys, web tracking with consent, lead scoring with a hand-off to the CRM, multi-touch attribution, and paid-media spend so the cost per lead and per job is known by channel.

The rules that shape the design

Four sets of rules decide what the platform must refuse to do. They differ in detail, so the platform records facts that satisfy the strictest of them and applies the same gate to everyone.

Texting is not built here. The module sends email only. The sms purpose exists in the consent ledger so a person's agreement to texts can be recorded, but no text sending is built in this module.

The CAN-SPAM guide allows a business up to ten business days to honour an opt-out ^[1]. This module processes an unsubscribe immediately, because it costs nothing extra and a message that arrives after someone said stop is a complaint waiting to happen.

Two lists before anything else

Lab 1 is the consent ledger and the suppression list, built before a single segment or template exists. Everything later depends on them.

mkt_consent is a ledger: each row says that one contact granted or withdrew consent for one purpose (marketing_email, sms, tracking, profiling or third_party_sharing), from a source (the form, the import, the phone call), at captured_at, under a text_version (the exact wording the person saw), with an ip_hash instead of a raw address. A change of mind is a new row with status withdrawn; the old row is never edited. The newest row for a contact and purpose is the answer, and the older rows are the proof.

mkt_suppression is the do-not-send list. It has one row per email address (the address is unique) and a reason: unsubscribe, hard_bounce, complaint, manual or legal. Once an address is on it, no campaign, journey or import can send to it, whatever a segment says.

-- A sketch of the recipient query for one send (consent and suppression inside it)
SELECT c.id, c.email
FROM mkt_segment_member m
JOIN crm_contact c ON c.id = m.contact_ref
WHERE m.tenant_id = :tenant
  AND m.segment_id = :segment
  AND m.archived_at IS NULL
  AND c.do_not_contact = false
  AND (SELECT k.status FROM mkt_consent k
        WHERE k.tenant_id = m.tenant_id AND k.contact_ref = m.contact_ref
          AND k.purpose = 'marketing_email'
          AND k.archived_at IS NULL
        ORDER BY k.captured_at DESC LIMIT 1) = 'granted'
  AND NOT EXISTS (SELECT 1 FROM mkt_suppression s
        WHERE s.tenant_id = m.tenant_id AND lower(s.email) = lower(c.email));

Read the query in plain English. SELECT names the columns to return (the contact's id and email). FROM and JOIN start with the segment's members and attach each member's contact record. WHERE keeps a row only if every test passes, and NOT EXISTS keeps a contact only if the search inside it finds no row, here no suppression row for that address.

Making the mail trusted

Mailbox providers decide whether to deliver a message by asking who sent it and whether it was changed on the way. Three standards answer that, and a fourth makes leaving easy. Your ESP signs and sends; you publish the records for your own sending domain ^[6][7][8].

One-click unsubscribe (RFC 8058) adds the header List-Unsubscribe-Post to the List-Unsubscribe header (which comes from RFC 2369), so every marketing message carries two headers ^[11]. A mailbox shows an Unsubscribe button next to the sender, and pressing it makes the mailbox send an automatic POST (a request that sends information to a web address, as a form does when you press Submit) to your address with no page to load and no login. Your route must treat that call exactly like a click on the link in the message: write the suppression row, add a withdrawn consent row, and publish the event mkt.contact.unsubscribed ^[9].

List-Unsubscribe: <the one-time unsubscribe address for this message>
List-Unsubscribe-Post: List-Unsubscribe=One-Click

The tables you build

The specification names eighteen tables, all with the standard columns and the mkt_ prefix. Contacts, leads and opportunities belong to the CRM (module M15), so the module points at them with soft links named contact_ref, lead_ref and opportunity_ref: an id with no database foreign key, so the two modules can change independently.

Segments

A segment is an audience. A dynamic segment keeps its rule in definition (JSON) and is rebuilt on a schedule, so mkt_segment_member holds the current members and member_count and refreshed_at say how fresh the count is. A static segment (is_dynamic false) is a fixed list. The rules can use contact, account, behaviour and CRM data.

{ "all": [
    { "field": "contact.city", "op": "in", "value": ["Cleveland", "Lakewood"] },
    { "field": "behaviour.clicked_email", "op": "within_days", "value": 30 }
] }

Templates and sends

A template is a versioned message: subject, preheader, HTML, plain-text body, a version number and a status of draft, approved or archived. Only an approved version can be sent, and an edit to an approved template creates the next version so that every send records exactly what went out. Personalisation tokens fill in the name and other fields at send time, and a preview or test send goes to a staff address first.

A send (mkt_email_send) joins a template, a segment and optionally a campaign. The variant is a, b, control or single: an A/B test is two sends from the same segment with different subjects or content, and the winner is judged on clicks or replies, not opens. The esp_batch_id links the send to the ESP's own record. Throttling, queueing and retries are the ESP's job.

Events from the ESP

Every outcome arrives as an event on mkt_email_event: delivered, open, click, bounce_hard, bounce_soft, complaint or unsubscribe, with the time, the contact, the link clicked and the ESP's own esp_event_id ^[1]. Three habits keep the webhook safe.

• Check who is calling. Verify the signature or secret your ESP documents before trusting the body; an open endpoint lets anyone write fake complaints or unsubscribes.
• Make it idempotent. ESPs retry. Store the esp_event_id and ignore one you have already seen, so a retry does not count twice.
• Act on the event. A hard bounce, a complaint or an unsubscribe writes a mkt_suppression row at once (and an unsubscribe or a complaint also adds a withdrawn consent row; an unsubscribe publishes mkt.contact.unsubscribed). A click can raise a lead score (chapter 3).

Is the mail healthy?

Journeys

A journey (mkt_flow) runs when a trigger fires: a form submission, a page visit, a score reaching a threshold, a field change or a date. Its steps are rows in mkt_flow_step, each with a step_type (send_email, wait, branch, update_field, add_to_segment, notify_owner, create_crm_task, webhook or exit), a JSON config and the number of the next step. A branch has two next steps, one for true and one for false.

Each contact in a journey has one mkt_flow_enrollment row: the current_step_no, a status of active, completed, exited or errored, and next_run_at. Three rules keep journeys safe.

• Every send step uses the gate. A contact who unsubscribed on day 2 must not get the reminder on day 4. The journey calls the same recipient function as a campaign.
• Re-entry is a choice. reentry_allowed says whether a contact may start the same journey twice; without it, a form submitted twice sends two welcome series.
• Finishing is an event. A journey that completes publishes mkt.flow.completed, so other modules can react.

Tracking, with consent

First-party tracking records a visit in mkt_web_visit: an anonymous_id, the URL, the referrer, the UTM parameters (utm_source, utm_medium, utm_campaign and the rest) and a session. When a visitor submits a form (the event cms.web_form.submitted), the visit history is attached to the new or existing contact, and the UTM values are kept on the lead so the first source is never lost. Where the rules in chapter 1 require consent for tracking, nothing is recorded until the visitor has accepted tracking: for a known contact, a tracking consent row says granted; before a visitor is known, their banner choice is held in a first-party cookie (a small note your own site stores in their browser) that the tracking route checks, and it is written to mkt_consent when they identify themselves.

Lead scoring and the MQL hand-off

A score ranks the people worth a phone call. Rules live in mkt_score_rule with a rule_type of fit (who they are: in the service area, owns the building), engagement (what they did: submitted a form, visited the pricing page, clicked) or negative (a competitor, a bounced address). Each rule has points and an optional decay_days. When a rule matches, a row is written to mkt_score_event with the points and an expires_at. A contact's score is the sum of their events that have not expired; no total is stored or edited, the same append-only idea as the ledger.

When the score first reaches the MQL (marketing-qualified lead) threshold, the platform publishes mkt.lead.mql, assigns the contact to the CRM owner by the CRM's routing rule, and can create a CRM task through a journey step. The KPI is MQL to SQL conversion: SQLs (sales-qualified leads) divided by MQLs. If few MQLs become SQLs, the threshold or the rules are wrong, and the fix is to change a rule row and a number, not code ^[1].

Campaigns, budget and paid media

A campaign (mkt_campaign) has a type (email, nurture, event, webinar, paid_social, paid_search, content, trade_show or referral), a status, dates, a budget, an actual_spend and a utm_campaign value that ties web visits to it. Money is whole cents in the database, as everywhere in the platform. Members (mkt_campaign_member) move from targeted to sent, responded and converted.

Paid media adds the cost side. An ad account (mkt_ad_account) is a connected account on LinkedIn, Meta, Google, TikTok or Microsoft. Each day, an ingestion job writes one mkt_ad_spend_daily row per account, campaign and date with spend, impressions, clicks and conversions. Make the write an upsert on those three keys so a re-run replaces the day instead of doubling it. When an opportunity is won (crm.opportunity.won), the platform could later upload the won deal back to the ad platform as an offline conversion, so the platform learns which clicks became jobs. That upload, and rolling child campaigns up to a parent campaign, are later work and not built in this course.

Attribution: who gets the credit

Attribution splits the value of a won job across the touches that led to it. A touch is a row in mkt_attribution_touch with a channel, a touch_type (first, lead_creation, opportunity_creation, close or other) and a time. Four models are taught, and none of them is the truth; each is a stated convention.

• First touch: all credit to the first touch. It answers: where do people first find us?
• Last touch: all credit to the last touch before the sale. It answers: what closes?
• Linear: equal credit to every touch.
• W-shaped: a common convention gives 30% each to the first touch, the lead-creation touch and the opportunity-creation touch, and shares the remaining 10% among the other touches. Your platform states its own weights and uses them everywhere.

The credit is stored in the credit JSON of each touch, in whole cents. Dividing cents can leave a remainder: three equal shares of $10,000.00 are 333,333 cents each, which makes 999,999, so one cent is left. Give leftover cents to the last touch, always the same way. Then each model's credit adds up to the won revenue exactly, which is the third definition-of-done check for this module: attribution totals reconcile to won revenue for each model.

Move the data in, and cut over

Migration has one hard rule: never import a contact without its consent provenance. Export contacts from the old tool with their consent status, lists, templates and engagement history. Load the suppressions first. A contact with no record of where and when they agreed is imported as not consented and is not mailed until they opt in again. Where consent for EU residents is exchanged with an advertising or consent platform, the IAB Europe Transparency and Consent Framework defines a standard consent string ^[4].

Cutover follows the same pattern as the other modules: run the old tool and the new platform side by side, compare, then cancel. Before you do, check that the complaint rate on the new platform is under 0.3%, that the attribution totals reconcile to won revenue under each model, and that a suppressed address cannot be sent to. Then cancel the old subscription and record the saving from the invoice.