CRM domain (Salesforce)

The crm domain is fed entirely by Salesforce LMS — one staging model per entity, feeding a matching intermediate model, a dimension and fact pair at marts, and a handful of reporting models. It's the smallest of the three domains by staging footprint, but it's where dealer identity, subscriptions, leads, and billing all live — the data other domains join against to attribute revenue to a dealer or a campaign.

The Salesforce entities

Staging here is a deliberately thin 1:1 mirror — sf_lms__leads_stg.sql's own header comment states it directly: "1:1 representation of raw LEAD with standardized naming and types. No joins or business logic applied in this layer." Every Salesforce field is renamed to a consistent UPPER_SNAKE_CASE convention and every date/timestamp field is explicitly converted to Australia/Sydney time — Salesforce's raw timestamps aren't in local time, and this is where that gets corrected, once, for every downstream model to rely on.

A single lead staging row carries a surprising amount: not just contact details, but a full UTM attribution block (UTM_SOURCE, UTM_MEDIUM, UTM_CAMPAIGN → aliased to CAMPAIGN_NAME, UTM_TERM, UTM_CONTENT, UTM_CHANNEL, UTM_KEYWORD, UTM_ADGROUP) and vehicle-enquiry context (VEHICLE_MAKE, VEHICLE_MODEL, VEHICLE_VARIANT, STOCK_ID, VIN_NUMBER) — a lead record is where marketing attribution and a specific piece of stock actually meet.

Intermediate: still thin, but this is where allocation and revenue fields surface

crm__lead_allocation_int.sql is close to a pure pass-through of its staging model, but it's the model that carries the fields the revenue logic in reporting depends on: ALLOCATION_STATUS, BILLING_STATUS, CHARGED_AMOUNT, CHARGEABLE_AMOUNT, NON_CHARGEABLE_REASON. crm__dealers_int similarly surfaces dealer identity and hierarchy (DEALER_ID, EXTERNAL_DEALER_ID, PARENT_DEALER_PK — dealers can belong to a parent dealer group).

Marts: a genuine dimensional split, dim vs. fact

This is the one domain in the project with a clean Kimball-style dim/fact separation on the same source model. crm__dealer_dim and crm__dealer_fact both read crm__dealers_int, but split its columns by kind:

-- crm__dealer_dim.sql — descriptive attributes
select
    DEALER_ID, DEALER_PK, EXTERNAL_DEALER_ID, PARENT_DEALER_PK,
    DEALER_NAME, DEALER_TYPE, DEALER_STATUS, DEALER_REGION,
    BILLING_STREET, BILLING_CITY, BILLING_STATE, ...
from {{ ref('crm__dealers_int') }}

-- crm__dealer_fact.sql — measures only
select
    DEALER_ID, DEALER_PK,
    DEALER_ANNUAL_REVENUE, NUMBER_OF_EMPLOYEES, EXPECTED_LISTING
from {{ ref('crm__dealers_int') }}

The same pattern repeats across every other CRM entity — crm__subscription_dim/crm__subscription_fact, crm__lead_dim/crm__lead_fact, crm__lead_allocation_dim/crm__lead_allocation_fact, crm__billing_cycle_dim, crm__products_dim/crm__products_fact. This is a more traditional dimensional-modelling split than paid_media (whose "dim" is really an SCD2 current-state filter) or web_analytics (mostly flat fact tables).

Reporting: where "chargeable" gets defined

Few reporting models in this domain, but they carry a real, named business rule. crm__revenue_daily_rpt's own header comment states it explicitly:

Gross Leads = total allocations (including duplicates) + non-allocated leads. Chargeable Leads = count of allocations where billing_status IN ('Chargeable', 'Trial').

This is the definition every dealer-billing figure downstream ultimately depends on — a dealer is billed for a lead only if its allocation's billing_status is Chargeable or Trial, nothing else. crm__lead_allocation_rpt and crm__finance_revenue_daily_rpt build on the same allocation data at different grains (allocation-level detail vs. finance-facing daily rollup).

business__campaign_performance_rpt (in the business reporting domain, not crm) reads crm__revenue_daily_rpt directly to compute campaign-level chargeable_rate — the CRM domain's own revenue definition propagates outward into campaign attribution reporting, not just dealer billing.

See also

• Marts
• Reporting layer
• Dealer status validation