CRM Analytics Overview
Leverage built-in analytics to understand your contacts, deals, tasks, and overall CRM performance.
CRM Analytics works on the standard 3-tier pipeline: you write base SQL over the org CRM tables (contacts, deals, companies, tasks, and more) in a Dataset, and the chart builder adds GROUP BY, aggregation, date grouping, and filters on top. Datasets can also target external PostgreSQL databases, CSV/Excel uploads, or Google Sheets.
Time to complete: 15-20 minutes
CRM Analytics Overview
Expedify provides analytics across all CRM entities:
| Entity | Key Metrics |
|---|---|
| Contacts | Growth, sources, engagement, lifecycle |
| Companies | Industry, size, revenue, deal value |
| Deals | Pipeline, velocity, win rate, value |
| Tasks | Completion, overdue, productivity |
| Activities | Engagement, touchpoints, timing |
Contact Analytics
Key Metrics
| Metric | Description | Formula |
|---|---|---|
| Contact Growth | New contacts over time | COUNT by period |
| Lead Score Distribution | Score breakdown | GROUP BY score range |
| Source Attribution | Where leads come from | GROUP BY source |
| Lifecycle Stages | Contact status | GROUP BY lifecycle_stage |
| Engagement Score | Activity level | Based on interactions |
Contact Growth Dashboard
Track how your contact database is growing:
Dataset: Contact Growth by Month
Data Source: contacts
Columns: DATE_TRUNC(month, created_at), COUNT(*)
Group By: month
Chart: Line Chart
Insights to track:
- Monthly new contacts
- Year-over-year comparison
- Growth rate trends
- Acquisition spikes
Lead Source Analysis
Understand which channels bring the best leads:
Dataset: Contacts by Source
Data Source: contacts
Columns: lead_source, COUNT(*), AVG(lead_score)
Group By: lead_source
Sort By: count DESC
Chart: Bar Chart
Questions answered:
- Which sources generate most leads?
- Which sources have highest quality (score)?
- Where should marketing invest?
Engagement Tracking
Monitor contact activity levels:
| Engagement Level | Criteria |
|---|---|
| Highly Active | Activity in last 7 days |
| Active | Activity in last 30 days |
| Dormant | No activity 30-90 days |
| Inactive | No activity 90+ days |
Dataset: Contact Engagement Levels
Data Source: contacts
Columns: engagement_level, COUNT(*)
Filter: Calculate from last_activity_at
Chart: Donut Chart
Deal Analytics
Sales Pipeline Metrics
| Metric | Description | Importance |
|---|---|---|
| Pipeline Value | Total open deal value | Revenue forecast |
| Win Rate | Won / Total | Sales effectiveness |
| Average Deal Size | Avg closed deal value | Pricing insight |
| Sales Velocity | Speed to close | Process efficiency |
| Stage Conversion | Movement between stages | Pipeline health |
Pipeline Value by Stage
Visualize your current pipeline:
Dataset: Pipeline by Stage
Data Source: deals
Columns: stage, COUNT(*), SUM(amount)
Filter: status = 'open'
Group By: stage
Chart: Funnel
Actionable insights:
- Identify bottlenecks (stages with high drop-off)
- Focus resources on largest opportunities
- Forecast revenue based on stage probability
Win Rate Analysis
Track sales effectiveness over time:
Dataset: Monthly Win Rate
Data Source: deals
Columns:
- month(closed_at)
- SUM(CASE WHEN status='won' THEN 1 ELSE 0 END) / COUNT(*)
Filter: status IN ('won', 'lost'), closed_at IS NOT NULL
Group By: month
Chart: Line Chart
Benchmark targets:
- Industry average: 20-30%
- High performers: 30-50%
- Track trend direction
Sales Velocity
Measure how fast deals close:
Sales Velocity = (Number of Opportunities × Average Deal Value × Win Rate) / Sales Cycle Length
Metrics to track:
| Component | Query |
|---|---|
| Opportunities | COUNT(deals) WHERE status='open' |
| Avg Deal Value | AVG(amount) WHERE status='won' |
| Win Rate | won / (won + lost) |
| Cycle Length | AVG(closed_at - created_at) |
Deal Aging Report
Identify stale deals:
Dataset: Deal Aging
Data Source: deals
Columns: name, amount, stage, days_in_stage
Filter: status = 'open', days_in_stage > 30
Sort By: days_in_stage DESC
Chart: Table
Task Analytics
Productivity Metrics
| Metric | Description |
|---|---|
| Tasks Created | New tasks per period |
| Tasks Completed | Finished tasks |
| Completion Rate | Completed / Created |
| Overdue Tasks | Past due date |
| Avg Completion Time | Days to complete |
Task Status Dashboard
Dataset: Tasks by Status
Data Source: tasks
Columns: status, COUNT(*)
Group By: status
Chart: Donut Chart
Status categories:
- To Do
- In Progress
- Completed
- Overdue
Team Productivity
Compare team member performance:
Dataset: Tasks by Assignee
Data Source: tasks
Columns: assigned_to, COUNT(*) as total,
SUM(status='completed') as completed
Group By: assigned_to
Chart: Horizontal Bar
Overdue Analysis
Track and reduce overdue tasks:
Dataset: Overdue Tasks by Owner
Data Source: tasks
Columns: assigned_to, COUNT(*)
Filter: due_date < NOW(), status != 'completed'
Group By: assigned_to
Sort By: count DESC
Chart: Bar Chart
Company Analytics
Company Segmentation
Analyze your company database:
Dataset: Companies by Industry
Data Source: companies
Columns: industry, COUNT(*), SUM(deal_value)
Group By: industry
Sort By: deal_value DESC
Chart: Bar Chart
Company Size Distribution
Dataset: Companies by Size
Data Source: companies
Columns: company_size, COUNT(*)
Group By: company_size
Order: Custom (SMB → Mid-Market → Enterprise)
Chart: Donut Chart
Revenue by Company
Top accounts analysis:
Dataset: Top Companies by Revenue
Data Source: deals
Columns: company_name, SUM(amount) as total_revenue
Filter: status = 'won'
Group By: company_id
Sort By: total_revenue DESC
Top N: 20
Chart: Bar Chart
Activity Analytics
Engagement Metrics
| Activity Type | What to Track |
|---|---|
| Emails | Sent, opened, clicked, replied |
| Calls | Made, duration, outcomes |
| Meetings | Scheduled, completed |
| Notes | Created, mentions |
Activity Volume by Type
Dataset: Activities by Type
Data Source: activities
Columns: activity_type, COUNT(*)
Filter: created_at last 30 days
Group By: activity_type
Chart: Pie Chart
Activity Trends
Dataset: Daily Activity Count
Data Source: activities
Columns: DATE(created_at), COUNT(*)
Filter: created_at last 30 days
Group By: date
Chart: Area Chart
Rep Activity Report
Dataset: Activities by Rep
Data Source: activities
Columns: user_id, activity_type, COUNT(*)
Filter: created_at last 7 days
Group By: user_id, activity_type
Chart: Stacked Bar
Pre-Built Reports
Sales Performance Report
Includes:
- Monthly revenue trend
- Win/loss ratio
- Average deal size
- Top performers
Access: Analytics → Reports → Sales Performance
Pipeline Health Report
Includes:
- Pipeline value by stage
- Stage conversion rates
- Deal aging
- Forecast accuracy
Marketing Attribution Report
Includes:
- Leads by source
- Source to customer conversion
- Channel ROI
- Campaign performance
Team Activity Report
Includes:
- Activities by team member
- Response times
- Task completion rates
- Engagement scores
Building CRM Dashboards
Executive Dashboard
High-level KPIs for leadership:
| Widget | Metric |
|---|---|
| KPI | Total Pipeline Value |
| KPI | Monthly Revenue |
| KPI | Win Rate |
| Line Chart | Revenue Trend |
| Funnel | Pipeline by Stage |
| Table | Top Deals |
Sales Rep Dashboard
Individual performance tracking:
| Widget | Metric |
|---|---|
| KPI | My Open Deals |
| KPI | This Month Revenue |
| KPI | Quota Attainment |
| Table | My Tasks |
| Bar Chart | My Activity |
Marketing Dashboard
Campaign and lead metrics:
| Widget | Metric |
|---|---|
| KPI | New Leads This Month |
| Pie Chart | Lead Sources |
| Line Chart | Contact Growth |
| Funnel | Lead to Customer |
| Bar Chart | Campaign Performance |
Best Practices
Data Quality
Good Analytics Requires:
- Consistent data entry
- Complete field population
- Regular data cleanup
- Standardized values
Red Flags:
- Many null values
- Inconsistent formats
- Duplicate records
- Outdated information
Metric Selection
Choose Metrics That:
- Drive action
- Are measurable
- Have clear targets
- Tell a story
Avoid:
- Vanity metrics
- Too many KPIs
- Metrics without context
- Numbers without trends
Reporting Cadence
| Report Type | Frequency | Audience |
|---|---|---|
| Executive | Weekly/Monthly | Leadership |
| Team | Daily/Weekly | Managers |
| Individual | Real-time | Reps |
| Strategic | Quarterly | Planning |
Troubleshooting
Missing Data
- Check field mapping - Is data being captured?
- Review required fields - Are they enforced?
- Validate imports - Data mapped correctly?
- Check permissions - Can you see the data?
Inaccurate Numbers
- Verify filters - Correct date range?
- Check duplicates - Counting multiple times?
- Review formulas - Calculations correct?
- Compare sources - Same definitions?
Slow Reports
- Add date filters - Limit data scope
- Reduce columns - Only needed fields
- Enable caching - Store results
- Optimize queries - Use indexes
Next Steps
With CRM analytics mastered:
- Connecting Third-Party Tools - Bring in external data
- Building Custom Dashboards - Create custom views
Quick Reference
Key CRM Formulas
| Metric | Formula |
|---|---|
| Win Rate | Won Deals / (Won + Lost) |
| Avg Deal Size | Total Won Revenue / Won Deals |
| Sales Cycle | Avg(Close Date - Create Date) |
| Conversion Rate | Customers / Leads |
| Pipeline Coverage | Pipeline Value / Quota |
Standard KPIs by Role
| Role | Primary KPIs |
|---|---|
| Sales Rep | Quota attainment, deals closed, activity |
| Sales Manager | Team quota, win rate, pipeline health |
| Marketing | Lead volume, conversion, source ROI |
| Executive | Revenue, growth rate, customer acquisition |