Customer Insights

For Business Users

Understanding customers is essential for growth. Olytix Core provides a unified customer view that connects data from sales, marketing, support, and product—giving every team the same customer understanding.

The Customer Data Challenge

Customer data is fragmented across systems:

┌─────────────────────────────────────────────────────────────────────┐
│                 Customer Data Fragmentation                          │
├─────────────────────────────────────────────────────────────────────┤
│                                                                      │
│  CRM              Marketing           Support           Product      │
│  ┌────────┐       ┌────────┐         ┌────────┐       ┌────────┐   │
│  │Contacts│       │Campaigns│         │Tickets │       │  Usage │   │
│  │Pipeline│       │  Leads  │         │  NPS   │       │ Events │   │
│  │Revenue │       │  Email  │         │Feedback│       │Features│   │
│  └────────┘       └────────┘         └────────┘       └────────┘   │
│       │                │                  │                │        │
│       └────────────────┼──────────────────┼────────────────┘        │
│                        │                  │                          │
│              Different customer IDs                                 │
│              Different definitions                                  │
│              No unified view                                        │
│                                                                      │
└─────────────────────────────────────────────────────────────────────┘

Olytix Core Customer Solution

Unified Customer Model

# cubes/customers.yml
cubes:
  - name: customers
    sql: "SELECT * FROM {{ ref('dim_customers') }}"
    description: "Unified customer analytics across all touchpoints"

    measures:
      # Count metrics
      - name: total_count
        type: count
        description: "Total number of customers"

      - name: active_count
        type: count
        filters:
          - sql: "last_activity_date > CURRENT_DATE - INTERVAL '30 days'"
        description: "Customers active in last 30 days"

      # Revenue metrics
      - name: total_revenue
        type: sum
        sql: lifetime_revenue
        format: currency
        description: "Total lifetime revenue"

      - name: avg_lifetime_value
        type: avg
        sql: lifetime_revenue
        format: currency
        description: "Average customer lifetime value"

      # Engagement metrics
      - name: avg_nps_score
        type: avg
        sql: nps_score
        description: "Average NPS score"

      - name: avg_support_tickets
        type: avg
        sql: support_ticket_count
        description: "Average support tickets per customer"

    dimensions:
      - name: customer_id
        type: string
        sql: customer_id
        primary_key: true

      - name: created_date
        type: time
        sql: created_at
        granularities: [day, week, month, quarter, year]

      - name: segment
        type: string
        sql: customer_segment
        description: "Customer segment (enterprise, mid-market, smb)"

      - name: industry
        type: string
        sql: industry

      - name: region
        type: string
        sql: region

      - name: acquisition_channel
        type: string
        sql: acquisition_channel

      - name: lifecycle_stage
        type: string
        sql: lifecycle_stage
        description: "Customer lifecycle (prospect, active, at-risk, churned)"

      - name: health_score
        type: number
        sql: health_score
        description: "Calculated customer health (0-100)"

Key Customer Metrics

# metrics/customers.yml
metrics:
  # Customer Lifetime Value
  - name: customer_lifetime_value
    description: |
      Average total revenue from a customer over their relationship.
      Calculated using historical cohort analysis.
    type: simple
    expression: customers.avg_lifetime_value
    meta:
      owner: customer-success
      certified: true

  # Customer Acquisition Cost
  - name: customer_acquisition_cost
    description: |
      Total acquisition cost divided by new customers acquired.
      Includes marketing and sales costs.
    type: ratio
    numerator: marketing.total_spend
    denominator: customers.new_customer_count
    format: currency
    meta:
      owner: marketing

  # LTV to CAC Ratio
  - name: ltv_cac_ratio
    description: |
      Lifetime value divided by acquisition cost.
      Target: > 3x for healthy unit economics.
    type: derived
    expression: customer_lifetime_value / customer_acquisition_cost
    format: number

  # Churn Rate
  - name: monthly_churn_rate
    description: |
      Percentage of customers who churned in the month.
      Churned = no activity or subscription cancelled.
    type: ratio
    numerator: customers.churned_count
    denominator: customers.start_of_period_count
    format: percentage
    time_grain: month

  # Net Revenue Retention
  - name: net_revenue_retention
    description: |
      Revenue from existing customers compared to same period last year.
      Includes expansion, contraction, and churn.
    type: derived
    expression: |
      (current_period.existing_customer_revenue)
      / prior_year_period.existing_customer_revenue
    format: percentage

Customer 360 View

Single Customer Profile

Customer Profile: Acme Corporation
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Overview
────────────────────────────────────────────────────────────
Customer Since:    January 2021
Segment:           Enterprise
Industry:          Manufacturing
Region:            North America
Health Score:      85/100 ✓

Financial Summary
────────────────────────────────────────────────────────────
Lifetime Revenue:  $450,000
Current ARR:       $120,000
Last 12 Months:    $125,000 (↑ 15% YoY)
Payment Status:    Current

Engagement
────────────────────────────────────────────────────────────
NPS Score:         9 (Promoter)
Last Activity:     2 days ago
Product Adoption:  78%
Support Tickets:   3 (last 90 days)
Avg Resolution:    4 hours

Key Contacts
────────────────────────────────────────────────────────────
Primary:           John Smith (VP Operations)
Champion:          Jane Doe (Data Analyst)
Executive Sponsor: Bob Johnson (CTO)

Recent Events
────────────────────────────────────────────────────────────
Jan 15  Renewed annual contract (+20% expansion)
Jan 10  Attended product webinar
Jan 05  Submitted feature request (logged)
Dec 20  Completed implementation of new module
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Customer Segmentation

{
  "metrics": [
    "customers.total_count",
    "customers.total_revenue",
    "customers.avg_lifetime_value",
    "monthly_churn_rate"
  ],
  "dimensions": ["customers.segment"],
  "order_by": [{ "field": "customers.total_revenue", "direction": "desc" }]
}

Result:

Segment      │ Customers │ Total Revenue │  Avg LTV  │ Churn Rate
─────────────┼───────────┼───────────────┼───────────┼───────────
Enterprise   │       150 │      $15.0M   │  $100,000 │     2.1%
Mid-Market   │       800 │       $8.0M   │   $10,000 │     4.5%
SMB          │     3,500 │       $3.5M   │    $1,000 │     8.2%

Customer Analytics Use Cases

Cohort Analysis

Track customer behavior by signup cohort:

cubes:
  - name: customer_cohorts
    sql: |
      SELECT
        customer_id,
        DATE_TRUNC('month', first_order_date) AS cohort_month,
        DATEDIFF('month', first_order_date, order_date) AS months_since_first
      FROM {{ ref('fct_orders') }}

    measures:
      - name: revenue
        type: sum
        sql: order_amount

      - name: customers
        type: count_distinct
        sql: customer_id

    dimensions:
      - name: cohort_month
        type: time
        sql: cohort_month

      - name: months_since_first
        type: number
        sql: months_since_first

Cohort Retention Query:

{
  "metrics": ["customer_cohorts.customers"],
  "dimensions": ["customer_cohorts.cohort_month", "customer_cohorts.months_since_first"],
  "filters": [
    { "dimension": "customer_cohorts.cohort_month", "operator": "gte", "value": "2023-01-01" }
  ]
}

Churn Prediction Inputs

cubes:
  - name: churn_indicators
    sql: "SELECT * FROM {{ ref('fct_customer_health') }}"
    description: "Features for churn prediction"

    measures:
      - name: customer_count
        type: count_distinct
        sql: customer_id

    dimensions:
      - name: customer_id
        type: string
        primary_key: true

      - name: days_since_last_login
        type: number
        sql: days_since_last_login

      - name: login_trend
        type: string
        sql: |
          CASE
            WHEN login_count_30d > login_count_60d THEN 'increasing'
            WHEN login_count_30d < login_count_60d * 0.5 THEN 'declining'
            ELSE 'stable'
          END

      - name: support_sentiment
        type: string
        sql: latest_support_sentiment

      - name: feature_adoption
        type: number
        sql: features_used_pct

      - name: payment_issues
        type: boolean
        sql: "failed_payment_count > 0"

      - name: contract_ending_soon
        type: boolean
        sql: "days_until_renewal < 60"

Customer Health Scoring

metrics:
  - name: customer_health_score
    description: |
      Composite health score from 0-100 based on:
      - Product usage (30%)
      - Support sentiment (25%)
      - Payment history (20%)
      - Engagement trend (15%)
      - NPS (10%)
    type: simple
    expression: customers.health_score

Health Score Distribution:

{
  "metrics": ["customers.total_count"],
  "dimensions": ["customers.health_score_band"],
  "filters": [
    { "dimension": "customers.lifecycle_stage", "operator": "equals", "value": "active" }
  ]
}

Health Band    │ Customers │ % of Total
───────────────┼───────────┼───────────
Excellent (80+)│     1,200 │    35%
Good (60-79)   │     1,500 │    44%
At Risk (40-59)│       500 │    15%
Critical (<40) │       200 │     6%

Customer Journey Tracking

Journey Stage Cube

cubes:
  - name: customer_journey
    sql: "SELECT * FROM {{ ref('fct_journey_events') }}"

    measures:
      - name: event_count
        type: count

      - name: unique_customers
        type: count_distinct
        sql: customer_id

      - name: avg_time_to_stage
        type: avg
        sql: days_from_signup

    dimensions:
      - name: journey_stage
        type: string
        sql: stage_name
        description: "signup, onboarding, first_value, expansion, advocacy"

      - name: conversion_status
        type: string
        sql: converted_to_next_stage

      - name: segment
        type: string
        sql: customer_segment

Conversion Funnel

Customer Journey Funnel
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Signup           ████████████████████████████████████  10,000 (100%)
                            │
                            ▼
Onboarding       ████████████████████████████░░░░░░░░   7,500 (75%)
Started                     │
                            ▼
First Value      █████████████████████░░░░░░░░░░░░░░░   5,000 (50%)
Achieved                    │
                            ▼
Paid             ███████████████░░░░░░░░░░░░░░░░░░░░░   3,500 (35%)
Conversion                  │
                            ▼
Expansion        █████████░░░░░░░░░░░░░░░░░░░░░░░░░░░   2,000 (20%)
                            │
                            ▼
Advocacy         █████░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░   1,000 (10%)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Integration with Tools

CRM Integration

from olytix-core import Olytix CoreClient

client = Olytix CoreClient("http://localhost:8000")

# Get at-risk customers for CRM alerts
at_risk = client.query(
    metrics=["customers.total_count"],
    dimensions=["customers.customer_id", "customers.health_score"],
    filters=[
        {"dimension": "customers.health_score", "operator": "lt", "value": 40},
        {"dimension": "customers.lifecycle_stage", "operator": "equals", "value": "active"}
    ]
).data

# Push to CRM for follow-up
for customer in at_risk:
    crm.create_task(
        customer_id=customer["customers.customer_id"],
        task_type="health_check",
        priority="high",
        reason=f"Health score: {customer['customers.health_score']}"
    )

Marketing Automation

# Get expansion-ready customers
expansion_candidates = client.query(
    metrics=["customers.total_revenue", "customers.avg_nps_score"],
    dimensions=["customers.customer_id", "customers.segment"],
    filters=[
        {"dimension": "customers.health_score", "operator": "gte", "value": 80},
        {"dimension": "customers.nps_score", "operator": "gte", "value": 8}
    ]
).data

# Trigger expansion campaign
marketing_automation.add_to_campaign(
    campaign="expansion_q1",
    customers=[c["customers.customer_id"] for c in expansion_candidates]
)

Best Practices

Customer ID Resolution

# Ensure consistent customer identity
models:
  - name: dim_customers
    description: "Master customer dimension with resolved identities"

    identity_resolution:
      primary_key: customer_id
      match_keys:
        - email
        - phone
        - company_domain
      merge_strategy: most_recent

Privacy Compliance

cubes:
  - name: customers
    meta:
      contains_pii: true
      gdpr_relevant: true

    dimensions:
      - name: email
        type: string
        sql: email
        meta:
          pii: true
          masking: hash  # Hash for non-privileged users

Next Steps

Ready to build customer insights?

1. Explore operational metrics →
2. Set up customer cube →
3. Build a Customer 360 recipe →

---

Start Simple

Begin with basic customer counts and revenue metrics. Once those are trusted, expand to behavioral metrics and health scoring.