# Understanding Dashboard Statistics

The Ideas & Issues dashboard provides four key statistics that give you an at-a-glance view of your feedback management performance. These metrics help you track volume, processing efficiency, backlog integration, and team responsiveness.

---

## The Four Core Metrics

### 1. Total Feedback

**What it measures:** The complete count of all active feedback items (ideas and issues) in your product.

**Formula:**
```
Total Feedback = COUNT of all feedback items WHERE:
  - product_id = current product
  - subscription_id = current subscription  
  - status = "active"
```

**Trend Indicator:**
```
Trend = COUNT of new items created in last 7 days
Display: "+X this week"
```

**What it tells you:**
- The overall volume of customer feedback you've collected
- Growth rate of feedback submissions
- Whether your feedback channels are being actively used

**Example:**
- **Value:** 247
- **Trend:** "+23 this week"
- **Interpretation:** You have 247 total feedback items, and 23 new ones were submitted in the past 7 days

**Icon:** Purple chat bubble outline

---

### 2. Unprocessed

**What it measures:** Feedback items that haven't been reviewed or assigned a status yet.

**Formula:**
```
Unprocessed Count = COUNT of feedback items WHERE:
  - product_id = current product
  - subscription_id = current subscription
  - status = "active"
  - AND (
      status_id IS NULL OR
      status_id = 0 OR
      status_id IN (lowest status ID per feedback type)
    )
```

**How "Unprocessed" is Determined:**

The system identifies unprocessed items using a heuristic approach:

1. **Null Status:** Items with no status assigned (brand new submissions)
2. **Zero Status:** Items explicitly marked with status_id = 0
3. **First Status:** Items in the very first status created for each type (idea/issue)

The system finds the lowest status ID for each feedback type (idea and issue) by:
```sql
SELECT id, feedback_item_type 
FROM statuses 
WHERE product_id = current_product
ORDER BY id ASC
```

Then takes the first unique status for "idea" and "issue" types.

**Trend Indicator:**
```
Percentage = (Unprocessed Count / Total Feedback) × 100
Display: "X% of total"
```

**What it tells you:**
- How much of your feedback backlog needs initial triage
- Whether your team is keeping up with new submissions
- Processing efficiency

**Example:**
- **Value:** 45
- **Trend:** "18% of total"
- **Interpretation:** 45 items need review, representing 18% of all feedback

**Icon:** Orange schedule/clock icon

**Best Practices:**
- Keep this percentage below 20% for healthy processing
- Review unprocessed items daily
- Set up automated notifications for new submissions

---

### 3. Linked to Backlog

**What it measures:** Feedback items that have been connected to work items in your product backlog.

**Formula:**
```
Linked Count = COUNT of feedback items WHERE:
  - product_id = current product
  - subscription_id = current subscription
  - status = "active"
  - AND (
      work_item_internal_id IS NOT NULL OR
      work_item_external_id IS NOT NULL
    )
```

**Trend Indicator:**
```
Trend = COUNT of linked items updated in last 30 days
Display: "+X this month"
```

**Note:** The trend uses `updated_at` as a proxy for "recently linked" since there's no dedicated "linked_at" timestamp.

**What it tells you:**
- How much customer feedback is actively influencing your roadmap
- The connection between customer voice and product development
- Accountability for addressing feedback

**Example:**
- **Value:** 89
- **Trend:** "+12 this month"
- **Interpretation:** 89 feedback items are linked to backlog work items, with 12 links created or updated this month

**Icon:** Deep purple checkmark circle outline

**Integration Points:**
- Jira work items (via external_id)
- Internal backlog items (via internal_id)
- Other project management tools

**Best Practices:**
- Aim to link high-priority feedback items within 48 hours
- Use linking to show customers their feedback is being acted upon
- Review unlinked items regularly to identify roadmap opportunities

---

### 4. Average Response Time

**What it measures:** The average time between when feedback is submitted and when your team posts the first comment.

**Formula:**
```
Current Week Average = AVG(
  TIMESTAMPDIFF(HOUR, 
    feedback_item.created_at,
    MIN(comment.created_at)
  )
) WHERE:
  - feedback_item.created_at BETWEEN (7 days ago) AND (now)
  - feedback_item.product_id = current product
  - feedback_item.subscription_id = current subscription
  - feedback_item.status = "active"
  - comment.feedback_item_id = feedback_item.id
```

**Trend Calculation:**
```
Previous Week Average = Same calculation for 14-7 days ago

Difference = Current Week Average - Previous Week Average

Trend Type:
  - "negative" if Difference < 0 (IMPROVEMENT - faster response)
  - "positive" if Difference > 0 (slower response)
  - "neutral" if Difference = 0 (no change)

Display: "X.Xh vs last week"
```

**Important:** A "negative" trend is GOOD because it means response time decreased (got faster).

**What it tells you:**
- How quickly your team engages with customer feedback
- Customer service responsiveness
- Team capacity and workload

**Example:**
- **Value:** 4.2h
- **Trend:** "-1.3h vs last week" (negative trend = improvement)
- **Interpretation:** Average response time is 4.2 hours, which is 1.3 hours faster than last week

**Icon:** Blue chart/analytics icon

**Response Time Benchmarks:**
- **Excellent:** < 4 hours
- **Good:** 4-12 hours
- **Acceptable:** 12-24 hours
- **Needs Improvement:** > 24 hours

**Best Practices:**
- Set up notifications for new feedback submissions
- Assign team members to monitor feedback daily
- Use templates for common responses to save time
- Track response time as a team KPI

---

## Understanding Trends

### Trend Types

**Positive Trend (Green)**
- Indicates growth or increase
- Used for: Total Feedback count increases
- Example: "+23 this week"

**Negative Trend (Red for most, Green for Response Time)**
- For Response Time: GOOD (faster responses)
- For other metrics: May indicate issues
- Example: "-1.3h vs last week"

**Neutral Trend (Gray)**
- No significant change
- Indicates stability
- Example: "18% of total"

### Trend Time Windows

| Metric | Trend Window | Comparison |
|--------|-------------|------------|
| Total Feedback | Last 7 days | New items created |
| Unprocessed | Current snapshot | Percentage of total |
| Linked to Backlog | Last 30 days | Items updated |
| Avg Response Time | Last 7 days | Previous 7 days |

---

## Using Statistics for Decision Making

### Daily Review

**Morning Check:**
1. **Total Feedback:** Is volume normal or spiking?
2. **Unprocessed:** Are there new items needing triage?
3. **Response Time:** Is the team keeping up?

**Action Items:**
- Assign unprocessed items to team members
- Respond to items approaching SLA limits
- Escalate items with unusual patterns

### Weekly Analysis

**Trends to Watch:**
1. **Increasing Unprocessed %:** May need more resources
2. **Declining Link Rate:** Feedback not reaching roadmap
3. **Rising Response Time:** Team capacity issues

**Strategic Questions:**
- Is feedback volume growing faster than processing capacity?
- Are we closing the loop with customers effectively?
- Which feedback is most valuable to link to backlog?

### Monthly Planning

**Use statistics to:**
- Set team goals (e.g., "Keep unprocessed below 15%")
- Identify resource needs
- Measure process improvements
- Report to leadership on customer engagement

---

## Common Scenarios

### Scenario 1: High Unprocessed Percentage (>30%)

**Symptoms:**
- Unprocessed count growing
- Percentage increasing week over week

**Possible Causes:**
- Sudden influx of feedback (product launch, marketing campaign)
- Team capacity constraints
- Process bottlenecks

**Actions:**
1. Temporarily increase triage frequency
2. Assign additional team members to review
3. Use bulk actions to categorize similar items
4. Consider automated categorization for common feedback types

### Scenario 2: Low Linking Rate

**Symptoms:**
- Linked to Backlog count not growing
- Trend shows "+0 this month"

**Possible Causes:**
- Feedback not actionable or relevant
- Backlog integration process unclear
- Team not prioritizing linking

**Actions:**
1. Review unlinked high-priority items
2. Clarify criteria for linking feedback
3. Train team on linking workflow
4. Set linking targets for high-value feedback

### Scenario 3: Increasing Response Time

**Symptoms:**
- Average Response Time trending upward
- Positive trend (slower responses)

**Possible Causes:**
- Increased feedback volume
- Team capacity issues
- Lack of notification setup

**Actions:**
1. Set up real-time notifications
2. Create response templates
3. Assign dedicated responders
4. Consider automated acknowledgment messages

---

## Technical Details

### Data Refresh

**Statistics update:**
- **Real-time:** When page loads
- **Automatic:** On navigation to dashboard
- **Manual:** Refresh browser to update

### Calculation Performance

All metrics are calculated using optimized database queries:
- Indexed fields for fast counting
- Parallel query execution
- Cached results where appropriate

### Time Zones

All timestamps use:
- **Server timezone** for calculations
- **Consistent timezone** across all metrics
- **UTC storage** with local display

---

## Formulas Quick Reference

### Total Feedback
```sql
SELECT COUNT(*) 
FROM idm_feedback_items
WHERE product_id = ? 
  AND subscription_id = ? 
  AND status = 'active'
```

### Unprocessed
```sql
SELECT COUNT(*) 
FROM idm_feedback_items
WHERE product_id = ? 
  AND subscription_id = ? 
  AND status = 'active'
  AND (status_id IS NULL 
    OR status_id = 0 
    OR status_id IN (first_status_ids))
```

### Linked to Backlog
```sql
SELECT COUNT(*) 
FROM idm_feedback_items
WHERE product_id = ? 
  AND subscription_id = ? 
  AND status = 'active'
  AND (work_item_internal_id IS NOT NULL 
    OR work_item_external_id IS NOT NULL)
```

### Average Response Time
```sql
SELECT AVG(
  TIMESTAMPDIFF(HOUR, 
    fi.created_at,
    (SELECT MIN(created_at) 
     FROM idm_comments 
     WHERE feedback_item_id = fi.id)
  )
)
FROM idm_feedback_items fi
WHERE fi.product_id = ?
  AND fi.subscription_id = ?
  AND fi.status = 'active'
  AND fi.created_at BETWEEN ? AND ?
```

---

## Troubleshooting

### Statistics Not Loading

**Symptoms:** Spinner shows indefinitely

**Solutions:**
1. Check browser console for errors
2. Verify network connectivity
3. Refresh the page
4. Clear browser cache
5. Contact administrator if persists

### Unexpected Values

**Symptoms:** Numbers seem incorrect

**Verification Steps:**
1. Check filter settings (product, date range)
2. Verify you're viewing the correct product
3. Compare with raw data in feedback list
4. Check for recent bulk operations

### Trend Not Updating

**Symptoms:** Trend shows old data

**Solutions:**
1. Refresh the page
2. Verify new data exists in the time window
3. Check that items are marked as "active"
4. Wait for cache to clear (if applicable)

---

## Best Practices Summary

✅ **Monitor daily** - Check statistics each morning  
✅ **Set targets** - Define acceptable ranges for each metric  
✅ **Track trends** - Watch for week-over-week changes  
✅ **Take action** - Use data to drive process improvements  
✅ **Communicate** - Share metrics with team and stakeholders  
✅ **Iterate** - Continuously refine your feedback process  

---

## Glossary

**Active Status:** Feedback items that haven't been deleted or archived  
**Feedback Item:** An idea or issue submitted by users  
**Status ID:** Identifier for the current workflow status of an item  
**Work Item:** A task or story in your product backlog  
**Response Time:** Time from submission to first team comment  
**Engagement Score:** Calculated metric of user segment activity  

---

These statistics provide the foundation for data-driven feedback management. Use them to optimize your team's responsiveness, ensure customer feedback influences your roadmap, and maintain a healthy feedback processing workflow.