First Data Pipeline

SELECT
  id as customer_id,
  first_name,
  last_name,
  email,
  LOWER(TRIM(city)) as city,
  UPPER(TRIM(state)) as state,
  created_date
FROM raw.salesforce.customers

SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  c.email,
  c.city,
  c.state,
  r.region,
  SUM(o.amount) as total_spend,
  COUNT(o.order_id) as order_count,
  SUM(o.amount) / COUNT(o.order_id) as avg_order_value,
  SUM(o.amount) * 0.85 as lifetime_value -- LTV calculation
FROM {{ ref('stg_customers') }} c
LEFT JOIN {{ ref('stg_orders') }} o ON c.customer_id = o.customer_id
LEFT JOIN {{ ref('dim_regions') }} r ON c.state = r.state
GROUP BY 1, 2, 3, 4, 5, 6, 7