The prevailing narrative in modern marketing is that a unified customer profile is an asset exclusive to enterprises with substantial budgets for commercial Customer Data Platforms. I posit this is a misconception. A robust single customer view is fundamentally a data modeling and engineering challenge, not solely a software procurement one. For growth teams operating with mid-market budgets (e.g., $1M-$10M ARR) and traffic volumes in the range of 50k to 500k monthly unique visitors, a purpose-built architecture using modern data stack components can deliver 80-90% of the required functionality at a fraction of the cost.
The core of this approach is treating your data warehouse as the source of truth and constructing your customer view as a modeled table—a `customers` dimension, if you will. This requires integrating key first-party data sources:
* **Core transactional data** from your application database (e.g., PostgreSQL, MySQL).
* **Product-led growth events** from your tracking tool (e.g., Segment, Rudderstack, Snowplow).
* **Marketing engagement data** from your email service provider (e.g., SendGrid, Mailchimp API) and ad platforms (via their respective APIs).
The critical step is identity resolution. A naive `JOIN` on `user_id` is insufficient, as users may interact before authentication. A more resilient SQL pattern involves creating a mapping of all known identifiers for an individual, then aggregating facts to that master entity.
Consider this foundational dbt model for an `stg_customer_ids` staging model:
```sql
WITH
web_events AS (
SELECT
anonymous_id,
user_id,
MIN(TIMESTAMP) AS first_seen_at
FROM {{ ref('web_events') }}
GROUP BY 1, 2
),
id_graph AS (
SELECT
COALESCE(user_id, anonymous_id) AS canonical_id,
anonymous_id,
user_id
FROM web_events
)
-- Use a recursive CTE or a dedicated function (like dbt_utils.surrogate_key)
-- to collapse the identity graph
SELECT
{{ dbt_utils.surrogate_key(['canonical_id']) }} AS customer_pk,
anonymous_id,
user_id
FROM id_graph
```
Subsequent models can then join fact tables to this identity spine, ensuring all events and attributes are attributed to the same persistent `customer_pk`. The final `customers` model would aggregate metrics such as:
* Lifetime value (LTV)
* First touch and last touch attribution channels
* Product usage frequency and feature adoption
* Email engagement scores
* Support ticket history
This modeled table becomes your de facto CDP. It can be surfaced in Looker (as an Explore) or reverse-synced via a tool like Hightouch to operational systems—your CRM, your email platform for segmentation, your support desk. The primary limitations compared to a commercial CDP are typically in real-time activation latencies (batch vs. streaming) and the depth of pre-built third-party connectors. However, for the stated business profile, the cost-benefit trade-off is overwhelmingly positive. The total spend for the necessary components (data ingestion, warehouse, transformation, BI) can often be kept under $10k/year at this scale, while providing unparalleled transparency and control over your customer logic.
- dan
Garbage in, garbage out.