Advanced guide to creating custom metrics, using formulas, aggregations, and building business-specific calculations.
Understanding Custom Metrics
What Are Custom Metrics?
- Calculated Fields: Derived from existing data
- Business Logic: Specific to your industry/company
- Advanced Aggregations: Complex mathematical operations
- KPI Formulas: Key performance indicator calculations
When to Use Custom Metrics
- Unique Business Calculations: Industry-specific formulas
- Complex Ratios: Multi-field calculations
- Time-Based Analysis: Period-over-period comparisons
- Conditional Logic: If-then business rules
Basic Formula Creation
Step 1: Access Formula Builder
- Go to Dashboards → Edit Mode
- Add new widget or edit existing
- Click Add Custom Metric
- Open Formula Builder
Step 2: Build Formula
- Select Fields: Choose source data fields
- Add Operators: Mathematical operations (+, -, *, /)
- Apply Functions: Built-in calculation functions
- Test Formula: Validate with sample data
- Save Metric: Name and save for reuse
Common Business Calculations
Revenue Metrics
```
• Customer Lifetime Value (CLV)
Formula: (Average Order Value × Purchase Frequency) × Customer Lifespan
• Monthly Recurring Revenue (MRR)
Formula: SUM(Active Subscriptions × Monthly Price)
• Average Revenue Per User (ARPU)
Formula: Total Revenue / Total Active Users
• Revenue Growth Rate
Formula: ((Current Period Revenue - Previous Period Revenue) / Previous Period Revenue) × 100
```
Performance Metrics
```
• Conversion Rate
Formula: (Conversions / Total Visitors) × 100
• Customer Acquisition Cost (CAC)
Formula: Total Marketing Spend / New Customers Acquired
• Return on Investment (ROI)
Formula: ((Revenue - Investment) / Investment) × 100
• Churn Rate
Formula: (Customers Lost / Total Customers at Start) × 100
```
Operational Metrics
```
• Inventory Turnover
Formula: Cost of Goods Sold / Average Inventory Value
• Employee Productivity
Formula: Total Output / Total Input Hours
• Cost Per Lead
Formula: Total Marketing Spend / Total Leads Generated
• Net Promoter Score (NPS)
Formula: % Promoters - % Detractors
```
Advanced Formula Functions
Mathematical Functions
- SUM(): Add values together
- AVERAGE(): Calculate mean value
- COUNT(): Count non-empty values
- MAX()/MIN(): Find maximum/minimum values
- ROUND(): Round to specified decimal places
Conditional Functions
- IF(): Conditional logic (if-then-else)
- CASE(): Multiple condition handling
- AND()/OR(): Logical operators
- ISNULL(): Handle missing data
- COALESCE(): Return first non-null value
Date Functions
- DATEADD(): Add time periods to dates
- DATEDIFF(): Calculate difference between dates
- YEAR()/MONTH()/DAY(): Extract date components
- WEEKDAY(): Get day of week
- QUARTER(): Extract quarter from date
Text Functions
- CONCAT(): Combine text strings
- SUBSTRING(): Extract part of text
- UPPER()/LOWER(): Change text case
- LENGTH(): Get text length
- REPLACE(): Replace text patterns
Complex Calculations Examples
E-commerce Metrics
```sql
-- Customer Lifetime Value with Seasonality
CLV = (AOV × Purchase_Frequency × Margin_Rate) ×
(Customer_Lifespan × Seasonal_Factor)
-- Cart Abandonment Rate
Cart_Abandonment = (Started_Checkouts - Completed_Orders) /
Started_Checkouts × 100
-- Inventory Efficiency
Inventory_Efficiency = (Units_Sold / Average_Inventory) ×
(Days_in_Period / Inventory_Days)
```
SaaS Metrics
```sql
-- Monthly Churn Rate
Monthly_Churn = (Customers_Lost_This_Month /
Customers_Start_Month) × 100
-- Net Revenue Retention
NRR = ((Starting_ARR + Expansion_ARR - Churned_ARR) /
Starting_ARR) × 100
-- Lead Velocity Rate
LVR = ((This_Month_Qualified_Leads - Last_Month_Qualified_Leads) /
Last_Month_Qualified_Leads) × 100
```
Marketing Metrics
```sql
-- Marketing Qualified Lead Score
MQL_Score = (Email_Opens × 1) + (Page_Views × 2) +
(Content_Downloads × 5) + (Demo_Requests × 10)
-- Customer Acquisition Efficiency
CAC_Efficiency = (New_Customer_Revenue × Gross_Margin) /
Customer_Acquisition_Cost
-- Marketing ROI by Channel
Marketing_ROI = (Revenue_Attributed_to_Channel - Channel_Cost) /
Channel_Cost × 100
```
Best Practices
Formula Design
- Keep It Simple: Start with basic calculations
- Document Logic: Add comments explaining formulas
- Test Thoroughly: Validate with known results
- Handle Nulls: Account for missing data
- Use Meaningful Names: Clear metric names
Performance Optimization
- Minimize Complexity: Avoid overly complex formulas
- Use Aggregations: Pre-calculate when possible
- Index Key Fields: Ensure source fields are indexed
- Cache Results: Store calculated values when appropriate
- Monitor Performance: Track calculation time
Data Quality
- Validate Inputs: Ensure source data quality
- Handle Exceptions: Account for edge cases
- Regular Audits: Verify calculation accuracy
- Version Control: Track formula changes
- Documentation: Maintain calculation documentation
Troubleshooting Formulas
Common Errors
- Division by Zero: Check for zero denominators
- Data Type Mismatches: Ensure compatible data types
- Null Values: Handle missing data appropriately
- Syntax Errors: Verify formula syntax
- Logic Errors: Test calculation logic
Debugging Steps
- Start Simple: Test with basic formula
- Check Data: Verify source data quality
- Validate Types: Ensure correct data types
- Test Components: Test each part separately
- Use Sample Data: Test with known values
Sharing Custom Metrics
Metric Library
- Save to Library: Make metrics reusable
- Organize by Category: Group related metrics
- Share with Team: Enable team access
- Version Control: Track metric versions
- Documentation: Include usage guidelines
Collaboration
- Team Reviews: Peer review formulas
- Standards: Establish calculation standards
- Training: Educate team on custom metrics
- Governance: Control metric creation
- Quality Assurance: Regular metric audits
Custom metrics transform raw data into meaningful business insights specific to your organization's needs.