Schema & Semantic Layer

Credit Union Member Analytics

member-analytics v3.0

Monitor member growth, retention, channel engagement, and segment performance

Metric Definitions

total_members
Total Members
primary ↑ higher

Count of active members at point in time

COUNT(DISTINCT member_id) WHERE status = 'Active'
new_acquisitions
New Acquisitions
primary ↑ higher

Count of new members joined in period

COUNT(DISTINCT member_id) WHERE join_date IN [period]
attrition_rate
Attrition Rate
primary ↓ lower

Percentage of members who left in period

(members_lost / beginning_members) * 100
stretch: 1.5 target: 2.5 warn high: 4.0 crit high: 5.0
digital_adoption
Digital Adoption
primary ↑ higher

Percentage of members using digital as primary channel

(digital_primary_members / total_active_members) * 100
stretch: 85 target: 75 warn low: 60 crit low: 50
net_growth
Net Growth
secondary ↑ higher

Acquisitions minus attrition

new_acquisitions - members_lost
retention_rate
Retention Rate
secondary ↑ higher

Percentage of members retained (100% - attrition)

100 - attrition_rate
avg_member_tenure
Average Tenure
secondary ↑ higher

Average membership duration in months

AVG(DATEDIFF(MONTH, join_date, CURRENT_DATE))
growth_rate
Growth Rate
secondary ↑ higher

Period-over-period membership growth percentage

((current_members - prior_members) / prior_members) * 100
digital_acquisitions
Digital Acquisitions
channel ↑ higher

New members acquired through digital channels

COUNT(member_id) WHERE acquisition_channel_type = 'digital'
branch_acquisitions
Branch Acquisitions
channel neutral

New members acquired through branch channels

COUNT(member_id) WHERE acquisition_channel_type = 'physical'
digital_attrition_rate
Digital Attrition
channel ↓ lower

Attrition rate for digital-primary members

(digital_members_lost / digital_beginning_members) * 100
branch_attrition_rate
Branch Attrition
channel ↓ lower

Attrition rate for branch-primary members

(branch_members_lost / branch_beginning_members) * 100
avg_engagement
Average Engagement
engagement ↑ higher

Average transactions per member per month

AVG(monthly_transaction_count)
churn_risk_score
Churn Risk Score
risk neutral

Likelihood of member attrition

Channels & Insight Templates

Channels

Digital Banking
digital_banking
digital primary
Branch Operations
branch
physical primary
Mobile App
mobile_app
digital primary
Online Portal
online_portal
digital secondary
ATM Network
atm
physical secondary
Phone Banking
phone
physical tertiary

Insight Templates

ID Trigger Condition Headline Severity
high_attrition_alert attrition_rate > 4.0 Attrition rate exceeds warning threshold high
strong_digital_growth digital_acquisitions > branch_acquisitions * 2 Digital acquisitions outpacing branch 2:1 info
branch_attrition_concern branch_attrition_rate > digital_attrition_rate * 1.5 Branch attrition significantly higher than digital medium
new_member_churn new_member_attrition > established_attrition * 2 New member retention challenge high

Segment Definitions

Tenure

ID Name Description Filter
new New Members Joined within the last 12 months tenure_months <= 12
established Established Members 1-5 years of membership tenure_months BETWEEN 13 AND 60
loyal Loyal Members More than 5 years of membership tenure_months > 60

Channel Preference

ID Name Description Filter
digital_primary Digital Primary Prefers digital channels primary_channel_type = 'digital'
branch_primary Branch Primary Prefers physical channels primary_channel_type = 'physical'
omnichannel Omnichannel Uses multiple channels regularly channel_count >= 3

Engagement Level

ID Name Description Filter
highly_engaged Highly Engaged 15+ transactions per month monthly_transactions >= 15
moderately_engaged Moderately Engaged 5-14 transactions per month monthly_transactions BETWEEN 5 AND 14
low_engagement Low Engagement Less than 5 transactions per month monthly_transactions < 5

Decision Type Definitions

retention_campaign
Retention Campaign
operational weekly

Proactive initiatives to reduce member attrition

Triggers
⚡ attrition_rate > 4.0
⚡ branch_attrition_rate > 3.5
⚡ churn_risk_score = high
Actions
• Launch Winback Campaign (marketing_manager) • Enhance Onboarding Program (member_services) • Personal Member Outreach (member_services) • Flag for Deeper Analysis (analyst)
Outcome Metrics
attrition_rate retention_rate member_satisfaction
acquisition_optimization
Acquisition Optimization
strategic monthly

Decisions to improve new member acquisition

Triggers
⚡ new_acquisitions < 2500
⚡ growth_rate < 5
⚡ digital_acquisitions < branch_acquisitions
Actions
• Increase Marketing Spend (executive) • Launch Referral Program (marketing_manager) • Optimize Digital Funnel (product_manager) • Increase Community Outreach (marketing_manager)
Outcome Metrics
new_acquisitions growth_rate acquisition_cost
channel_strategy
Channel Strategy
strategic quarterly

Decisions about channel mix and resource allocation

Triggers
⚡ digital_adoption > 75
⚡ branch_acquisitions < 1000
⚡ branch_attrition_rate > digital_attrition_rate * 1.5
Actions
• Shift Resources to Digital (executive) • Evaluate Branch Consolidation (executive) • Implement Hybrid Model (executive) • Enhance Digital Capabilities (product_manager)
Outcome Metrics
digital_adoption branch_operating_cost member_satisfaction
segment_intervention
Segment Intervention
operational weekly

Targeted programs for specific member segments

Triggers
⚡ churn_risk_score = high
⚡ avg_engagement < 5
⚡ retention_rate < 98
Actions
• New Member Nurture Track (member_services) • Re-Engagement Campaign (marketing_manager) • Loyalty Recognition Program (member_services) • Cross-Sell Initiative (marketing_manager)
Outcome Metrics
retention_rate avg_engagement products_per_member

Decision States & Flow

Workflow
Surfaced
→
Acknowledged
→
Investigating
→
Action Planned
→
Action Taken
→
Terminal States:
Deferred (terminal)
Dismissed (terminal)
Outcome Tracked (terminal)

State Definitions

State Description Terminal
surfaced
Insight generated, awaiting review active
acknowledged
Decision owner has seen the insight active
investigating
Additional analysis in progress active
action_planned
Decision made, action scheduled active
action_taken
Action executed, awaiting outcome active
deferred
Consciously postponed with rationale terminal
dismissed
Determined not actionable with rationale terminal
outcome_tracked
Results measured and recorded terminal

Service Level Agreements

Decision Type Response Window Escalation Path
retention_campaign 72 hours Member Services (24 hours) → Marketing Manager (48 hours) → Vp Member Experience (72 hours)
acquisition_optimization 14 days Marketing Manager (7 days) → Vp Marketing (14 days) → Cmo (21 days)
channel_strategy 30 days Analyst (7 days) → Vp Operations (21 days) → Coo (30 days)
segment_intervention 48 hours Member Services (24 hours) → Marketing Manager (48 hours) → Vp Member Experience (72 hours)

Authority Levels

Level Role Can Approve
L1 analyst
Data Analyst
 flag_for_analysis
L2 member_services
Member Services
 enhance_onboarding personal_outreach new_member_nurture loyalty_recognition
L3 marketing_manager
Marketing Manager
 launch_winback launch_referral community_outreach engagement_campaign cross_sell_initiative
L3 product_manager
Product Manager
 optimize_digital_funnel digital_enhancement
L5 executive
Executive Leadership
 increase_marketing shift_resources_digital branch_consolidation hybrid_model