SaaS Analytics Example

This example demonstrates a comprehensive data model for a SaaS platform, focusing on subscription metrics, user engagement, and product analytics.

Scenario

You're building a SaaS analytics platform that needs to track:

• User accounts and subscriptions
• Product usage and feature adoption
• Revenue metrics (MRR, ARR, churn)
• Customer health scores
• Product analytics events

Conceptual Model

File: models/conceptual/saas.mml

domain "Platform" {
  color = "#3498db"
  description = "Core platform entities"

  entity "User" {
    type = "entity"
    description = "Platform user accounts"
    pii = true

    attribute "email" {
      type = "string"
      required = true
      pii = true
    }

    attribute "signup_date" {
      type = "date"
      required = true
    }

    attribute "account_status" {
      type = "string"
      required = true
    }
  }

  entity "Organization" {
    type = "entity"
    description = "Customer organizations/workspaces"

    attribute "name" {
      type = "string"
      required = true
    }

    attribute "plan_tier" {
      type = "string"
      required = true
    }

    attribute "created_at" {
      type = "date"
      required = true
    }
  }

  entity "Subscription" {
    type = "entity"
    description = "Customer subscription records"

    attribute "plan_name" {
      type = "string"
      required = true
    }

    attribute "plan_amount" {
      type = "number"
      required = true
    }

    attribute "billing_interval" {
      type = "string"
      required = true
    }

    attribute "status" {
      type = "string"
      required = true
    }

    attribute "current_period_start" {
      type = "date"
      required = true
    }

    attribute "current_period_end" {
      type = "date"
      required = true
    }

    belongs-to "Platform.Organization"
  }

  entity "User_Membership" {
    type = "entity"
    description = "User membership in organizations"

    attribute "role" {
      type = "string"
      required = true
    }

    attribute "joined_at" {
      type = "date"
      required = true
    }

    belongs-to "Platform.User"
    belongs-to "Platform.Organization"
  }
}

domain "Product" {
  color = "#e74c3c"
  description = "Product usage and features"

  entity "Feature" {
    type = "entity"
    description = "Product features"

    attribute "feature_name" {
      type = "string"
      required = true
    }

    attribute "feature_tier" {
      type = "string"
    }
  }

  entity "Usage_Event" {
    type = "entity"
    description = "Product usage events"

    attribute "event_name" {
      type = "string"
      required = true
    }

    attribute "event_timestamp" {
      type = "date"
      required = true
    }

    attribute "properties" {
      type = "json"
    }

    belongs-to "Platform.User"
    belongs-to "Platform.Organization"
  }

  entity "Session" {
    type = "entity"
    description = "User sessions"

    attribute "session_start" {
      type = "date"
      required = true
    }

    attribute "session_end" {
      type = "date"
    }

    attribute "duration_seconds" {
      type = "number"
    }

    belongs-to "Platform.User"
  }
}

domain "Revenue" {
  color = "#2ecc71"
  description = "Revenue and financial metrics"

  entity "MRR" {
    type = "metric"
    description = "Monthly Recurring Revenue"
    business_logic = "SUM(Subscription.plan_amount WHERE status = 'active' AND billing_interval = 'month')"

    derived-from "Platform.Subscription"
  }

  entity "ARR" {
    type = "metric"
    description = "Annual Recurring Revenue"
    business_logic = "MRR * 12"

    derived-from "Platform.Subscription"
  }

  entity "New_MRR" {
    type = "metric"
    description = "New MRR from new subscriptions"
    business_logic = "SUM(plan_amount WHERE status = 'active' AND created_at >= period_start)"

    derived-from "Platform.Subscription"
  }

  entity "Expansion_MRR" {
    type = "metric"
    description = "MRR from upgrades"
    business_logic = "SUM(plan_change_delta WHERE change_type = 'upgrade')"

    derived-from "Platform.Subscription"
  }

  entity "Churned_MRR" {
    type = "metric"
    description = "MRR lost from cancellations"
    business_logic = "SUM(plan_amount WHERE status = 'cancelled' AND cancelled_at >= period_start)"

    derived-from "Platform.Subscription"
  }

  entity "Net_MRR_Growth" {
    type = "metric"
    description = "Net change in MRR"
    business_logic = "New_MRR + Expansion_MRR - Churned_MRR"

    derived-from "Platform.Subscription"
  }
}

domain "Analytics" {
  color = "#9b59b6"
  description = "Product analytics and customer insights"

  entity "Active_Users" {
    type = "metric"
    description = "Daily active users"
    business_logic = "COUNT(DISTINCT user_id WHERE last_activity >= NOW() - 1 day)"

    derived-from "Platform.User"
    derived-from "Product.Usage_Event"
  }

  entity "Feature_Adoption" {
    type = "metric"
    description = "Percentage of organizations using each feature"
    business_logic = "(COUNT(DISTINCT org_id WHERE feature_used) / COUNT(DISTINCT org_id)) * 100"

    derived-from "Product.Feature"
    derived-from "Product.Usage_Event"
  }

  entity "Customer_Health_Score" {
    type = "derivation"
    description = "Customer health score based on usage and engagement"
    business_logic = """
      Calculate weighted score based on:
      - Login frequency (30%)
      - Feature usage (40%)
      - Support tickets (10%)
      - Payment history (20%)
    """

    derived-from "Platform.User"
    derived-from "Product.Usage_Event"
    derived-from "Platform.Subscription"
  }

  entity "Cohort_Analysis" {
    type = "derivation"
    description = "Customer cohort retention and revenue analysis"
    business_logic = "GROUP BY signup_month WITH retention and revenue metrics over time"

    derived-from "Platform.Organization"
    derived-from "Platform.Subscription"
  }

  entity "Product_Analytics" {
    type = "derivation"
    description = "Feature usage funnel analysis"
    business_logic = "Track feature adoption and usage patterns across customer segments"

    derived-from "Product.Usage_Event"
    derived-from "Product.Feature"
  }
}

Logical Model

File: models/logical/sources.mml

source "PostgreSQL_Production" {
  type = "postgres"
  description = "Production application database"
  host = "prod-db.saas.com"
  database = "saas_app"
  owner = "platform-team"
  refresh_frequency = "realtime"

  entity "Platform.User" {
    table = "users"
  }

  entity "Platform.Organization" {
    table = "organizations"
  }

  entity "Platform.User_Membership" {
    table = "user_memberships"
  }
}

source "Stripe_API" {
  type = "api"
  description = "Stripe subscription and payment data"
  host = "api.stripe.com"
  owner = "finance-team"
  refresh_frequency = "hourly"

  entity "Platform.Subscription" {
    endpoint = "subscriptions"
    notes = "Subscription data including MRR"
  }
}

source "Segment_Analytics" {
  type = "kafka"
  description = "Product analytics events via Segment"
  host = "analytics.saas.com:9092"
  owner = "analytics-team"
  refresh_frequency = "realtime"

  entity "Product.Usage_Event" {
    topic = "product_events"
    notes = "Real-time product usage events"
  }

  entity "Product.Session" {
    topic = "sessions"
    notes = "User session tracking"
  }
}

Data Products

File: products/saas_metrics.mml

data_product "SaaS_Metrics" {
  description = "Core SaaS business metrics dashboard"
  owner = "finance-team"
  refresh_schedule = "0 * * * *"  // Hourly

  consumer "executives"
  consumer "finance-team"
  consumer "board-of-directors"

  report "Revenue_Dashboard" {
    type = "dashboard"
    description = "MRR, ARR, and growth metrics"

    uses "Platform.Subscription"
    uses "Revenue.MRR"
    uses "Revenue.ARR"

    metric "Current_MRR" {
      description = "Current monthly recurring revenue"
      calculation = "SUM(plan_amount WHERE status = 'active' AND billing_interval = 'month')"
      target_value = "$500K"
      uses "Platform.Subscription"
    }

    metric "Current_ARR" {
      description = "Annual recurring revenue"
      calculation = "Current_MRR * 12"
      target_value = "$6M"
      uses "Revenue.MRR"
    }

    metric "MRR_Growth_Rate" {
      description = "Month-over-month MRR growth"
      calculation = "((current_mrr - previous_mrr) / previous_mrr) * 100"
      target_value = "10%"
      uses "Revenue.MRR"
      uses "Revenue.Net_MRR_Growth"
    }

    metric "Logo_Churn_Rate" {
      description = "Percentage of customers churned this month"
      calculation = "(churned_customers / total_customers_start) * 100"
      target_value = "< 5%"
      uses "Platform.Organization"
      uses "Platform.Subscription"
    }

    metric "Revenue_Churn_Rate" {
      description = "Percentage of MRR churned"
      calculation = "(churned_mrr / beginning_mrr) * 100"
      target_value = "< 5%"
      uses "Revenue.Churned_MRR"
      uses "Revenue.MRR"
    }
  }

  report "Growth_Metrics" {
    type = "dashboard"
    description = "New, expansion, and churned MRR breakdown"

    uses "Revenue.New_MRR"
    uses "Revenue.Expansion_MRR"
    uses "Revenue.Churned_MRR"

    metric "New_MRR_This_Month" {
      description = "MRR from new customers"
      calculation = "SUM(new_mrr)"
      target_value = "$50K"
      uses "Revenue.New_MRR"
    }

    metric "Expansion_MRR_This_Month" {
      description = "MRR from upgrades"
      calculation = "SUM(expansion_mrr)"
      target_value = "$20K"
      uses "Revenue.Expansion_MRR"
    }

    metric "Net_MRR_Movement" {
      description = "Net change in MRR"
      calculation = "new_mrr + expansion_mrr - churned_mrr"
      target_value = "$60K"
      uses "Revenue.Net_MRR_Growth"
    }
  }
}

File: products/product_analytics.mml

data_product "Product_Analytics" {
  description = "Product usage and engagement metrics"
  owner = "product-team"
  refresh_schedule = "0 */6 * * *"  // Every 6 hours

  consumer "product-team"
  consumer "engineering-team"
  consumer "customer-success"

  report "Engagement_Dashboard" {
    type = "dashboard"
    description = "User engagement and activity metrics"

    uses "Platform.User"
    uses "Product.Usage_Event"
    uses "Analytics.Active_Users"

    metric "DAU" {
      description = "Daily active users"
      calculation = "COUNT(DISTINCT user_id WHERE last_activity >= NOW() - 1 day)"
      target_value = "10,000"
      uses "Platform.User"
      uses "Product.Usage_Event"
    }

    metric "WAU" {
      description = "Weekly active users"
      calculation = "COUNT(DISTINCT user_id WHERE last_activity >= NOW() - 7 days)"
      target_value = "30,000"
      uses "Platform.User"
      uses "Product.Usage_Event"
    }

    metric "MAU" {
      description = "Monthly active users"
      calculation = "COUNT(DISTINCT user_id WHERE last_activity >= NOW() - 30 days)"
      target_value = "50,000"
      uses "Platform.User"
      uses "Product.Usage_Event"
    }

    metric "DAU_WAU_Ratio" {
      description = "Stickiness metric (DAU/WAU)"
      calculation = "(DAU / WAU) * 100"
      target_value = "> 33%"
      uses "Analytics.Active_Users"
    }

    metric "Average_Session_Duration" {
      description = "Average session length in minutes"
      calculation = "AVG(duration_seconds) / 60"
      target_value = "15 minutes"
      uses "Product.Session"
    }
  }

  report "Feature_Adoption" {
    type = "dashboard"
    description = "Feature usage and adoption rates"

    uses "Product.Feature"
    uses "Product.Usage_Event"
    uses "Analytics.Feature_Adoption"

    metric "Feature_Adoption_Rate" {
      description = "Percentage of orgs using key features"
      calculation = "(orgs_using_feature / total_orgs) * 100"
      uses "Analytics.Feature_Adoption"
    }

    metric "Feature_Usage_Frequency" {
      description = "Average feature usage per active user"
      calculation = "COUNT(events) / COUNT(DISTINCT user_id)"
      uses "Product.Usage_Event"
    }
  }
}

File: products/customer_success.mml

data_product "Customer_Success" {
  description = "Customer health and success metrics"
  owner = "customer-success-team"
  refresh_schedule = "0 6 * * *"  // Daily at 6am

  consumer "customer-success"
  consumer "account-management"

  report "Health_Score_Dashboard" {
    type = "dashboard"
    description = "Customer health scores and risk indicators"

    uses "Platform.Organization"
    uses "Analytics.Customer_Health_Score"

    metric "At_Risk_Customers" {
      description = "Customers with health score < 50"
      calculation = "COUNT(DISTINCT org_id WHERE health_score < 50)"
      target_value = "< 50"
      uses "Analytics.Customer_Health_Score"
    }

    metric "Healthy_Customers" {
      description = "Customers with health score >= 75"
      calculation = "COUNT(DISTINCT org_id WHERE health_score >= 75)"
      uses "Analytics.Customer_Health_Score"
    }

    metric "Average_Health_Score" {
      description = "Average health score across all customers"
      calculation = "AVG(health_score)"
      target_value = "> 70"
      uses "Analytics.Customer_Health_Score"
    }
  }

  report "Cohort_Retention" {
    type = "table"
    description = "Monthly cohort retention analysis"

    uses "Analytics.Cohort_Analysis"

    metric "Retention_By_Cohort" {
      description = "Retention rate by signup cohort"
      calculation = "(active_customers / cohort_size) * 100"
      uses "Analytics.Cohort_Analysis"
    }
  }
}

Key Features Demonstrated

1. SaaS-Specific Metrics

   • MRR, ARR, churn rates
   • New, expansion, churned MRR breakdown
   • Logo vs. revenue churn

2. Product Analytics

   • DAU, WAU, MAU tracking
   • Feature adoption rates
   • Session analytics

3. Customer Success

   • Health score calculations
   • Cohort retention analysis
   • At-risk customer identification

4. Multiple Data Sources

   • PostgreSQL for core data
   • Stripe for billing
   • Segment for product events

5. Real-time + Batch

   • Real-time event streaming
   • Hourly metric refreshes
   • Daily customer reports

Visual Model

Platform Domain (Blue)
├── User (entity)
├── Organization (entity)
├── Subscription (entity)
│   └── belongs-to → Organization
└── User_Membership (entity)
    ├── belongs-to → User
    └── belongs-to → Organization

Product Domain (Red)
├── Feature (entity)
├── Usage_Event (entity)
│   ├── belongs-to → User
│   └── belongs-to → Organization
└── Session (entity)
    └── belongs-to → User

Revenue Domain (Green)
├── MRR (metric)
│   └── derived-from → Subscription
├── ARR (metric)
│   └── derived-from → Subscription
├── New_MRR (metric)
├── Expansion_MRR (metric)
├── Churned_MRR (metric)
└── Net_MRR_Growth (metric)

Analytics Domain (Purple)
├── Active_Users (metric)
│   └── derived-from → User + Usage_Event
├── Feature_Adoption (metric)
│   └── derived-from → Feature + Usage_Event
├── Customer_Health_Score (derivation)
├── Cohort_Analysis (derivation)
└── Product_Analytics (derivation)

Data Products
├── SaaS_Metrics
│   ├── Revenue_Dashboard
│   └── Growth_Metrics
├── Product_Analytics
│   ├── Engagement_Dashboard
│   └── Feature_Adoption
└── Customer_Success
    ├── Health_Score_Dashboard
    └── Cohort_Retention

Next Steps

1. Add LTV:CAC ratio - Calculate unit economics
2. Add payback period - Track how long to recover CAC
3. Add NPS tracking - Measure customer satisfaction
4. Add support tickets - Integrate support data
5. Add product roadmap - Link usage to feature requests

Related Examples

• Basic Model - Simple introduction
• E-Commerce Platform - Retail data model