draft-analytics.md10.8 KBView on GitHub Draft Analytics Skill
Run a full audit of drafted tasks and emails for a Cedar user to surface quality issues, anomalies, and cleanup opportunities. Use the psql connection from the query skill.
**User ID**: $ARGUMENTS
---
Run ALL of the following queries against the database. After running them, produce a structured report with findings, counts, and ready-to-use cleanup SQL. Never run UPDATE or DELETE statements — only SELECTs. Always substitute $ARGUMENTS for the user ID.
---
## 1. Draft ranking — GOOD / STALE / COLD / DUPLICATE
```sql
WITH outstanding AS (
SELECT
ada.draft_id, ada.origin_time, ae.conversation_id,
ada.origin_email->>'subject' AS draft_subject,
ada.origin_email->>'to' AS draft_to,
ut.task_type, cc.priority, cc.name AS conversation
FROM analytics_draft_actions ada
JOIN agent_executions ae ON ae.run_id = ada.run_id
JOIN agent_operating_procedures aop ON aop.id = ae.aop_id
LEFT JOIN crm_conversations cc ON cc.id = ae.conversation_id
LEFT JOIN user_tasks ut ON ut.task_action_data->>'draftId' = ada.draft_id
WHERE ada.user_id = '$ARGUMENTS'
AND ada.status = 'not_sent'
AND ada.origin_time >= NOW() - INTERVAL '30 days'
AND aop.name = 'Deals'
AND ut.status = 'todo'
),
conv_last_activity AS (
SELECT conversation_id, MAX(occurred_at) AS last_activity
FROM crm_events
WHERE conversation_id IN (SELECT DISTINCT conversation_id FROM outstanding)
AND event_type = 'email'
GROUP BY conversation_id
),
subsequent_inbounds AS (
SELECT o.draft_id, COUNT(ce.id) AS inbound_count
FROM outstanding o
LEFT JOIN crm_events ce
ON ce.conversation_id = o.conversation_id
AND ce.event_type = 'email' AND ce.direction = 'inbound'
AND ce.occurred_at > o.origin_time
GROUP BY o.draft_id
),
ranked AS (
SELECT draft_id,
ROW_NUMBER() OVER (PARTITION BY conversation_id ORDER BY origin_time DESC) AS rank_in_deal
FROM outstanding
)
SELECT
o.draft_id, o.task_type, o.priority, o.conversation,
o.origin_time AS drafted_at, cla.last_activity,
si.inbound_count AS replies_after_draft, r.rank_in_deal,
CASE
WHEN si.inbound_count > 0 THEN 'STALE'
WHEN r.rank_in_deal > 1 THEN 'DUPLICATE (older)'
WHEN cla.last_activity < NOW() - INTERVAL '14 days' THEN 'COLD'
ELSE 'GOOD'
END AS recommendation,
o.draft_subject
FROM outstanding o
JOIN crm_conversations cc ON cc.id = o.conversation_id
LEFT JOIN conv_last_activity cla ON cla.conversation_id = o.conversation_id
LEFT JOIN subsequent_inbounds si ON si.draft_id = o.draft_id
LEFT JOIN ranked r ON r.draft_id = o.draft_id
ORDER BY
CASE WHEN si.inbound_count > 0 OR r.rank_in_deal > 1 THEN 1 ELSE 0 END,
cla.last_activity DESC NULLS LAST;
```
## 2. No-draft tasks that should have fired but didn't
```sql
SELECT
ut.id, ut.task_type, ut.due_date, ut.created_at,
cc.name AS conversation, cc.priority, ut.description
FROM user_tasks ut
LEFT JOIN crm_conversations cc ON cc.id = ut.conversation_id
WHERE ut.user_id = '$ARGUMENTS'
AND ut.agent_execution_enabled = true
AND ut.status = 'todo'
AND ut.due_date < NOW()
AND ut.task_action_data->>'draftId' IS NULL
AND ut.execution_run_id IS NULL
ORDER BY cc.priority NULLS LAST, ut.due_date DESC;
```
## 3. Duplicate tasks per conversation (multiple runs, same deal)
```sql
SELECT
cc.name AS conversation, cc.priority,
COUNT(ut.id) AS task_count,
COUNT(DISTINCT ut.creation_run_id) AS distinct_runs,
array_agg(ut.task_type ORDER BY ut.created_at DESC) AS task_types,
array_agg(ut.description ORDER BY ut.created_at DESC) AS descriptions
FROM user_tasks ut
JOIN crm_conversations cc ON cc.id = ut.conversation_id
WHERE ut.user_id = '$ARGUMENTS'
AND ut.status = 'todo'
AND ut.creation_run_id IS NOT NULL
GROUP BY cc.id, cc.name, cc.priority
HAVING COUNT(ut.id) > 1 AND COUNT(DISTINCT ut.creation_run_id) > 1
ORDER BY task_count DESC;
```
## 4. Conversations with multiple live unsent drafts
```sql
SELECT
cc.name AS conversation, cc.priority,
COUNT(ada.draft_id) AS live_draft_count,
MIN(ada.origin_time)::date AS oldest_draft,
MAX(ada.origin_time)::date AS newest_draft,
array_agg(ada.origin_email->>'subject' ORDER BY ada.origin_time DESC) AS subjects
FROM analytics_draft_actions ada
JOIN agent_executions ae ON ae.run_id = ada.run_id
JOIN agent_operating_procedures aop ON aop.id = ae.aop_id
JOIN crm_conversations cc ON cc.id = ae.conversation_id
JOIN user_tasks ut ON ut.task_action_data->>'draftId' = ada.draft_id
WHERE ada.user_id = '$ARGUMENTS'
AND ada.status = 'not_sent'
AND aop.name = 'Deals'
AND ut.status = 'todo'
GROUP BY cc.id, cc.name, cc.priority
HAVING COUNT(ada.draft_id) > 1
ORDER BY live_draft_count DESC;
```
## 5. Misdirected drafts (notification address in `to` or `cc`)
```sql
SELECT
ut.status, ada.draft_id, ut.due_date::date,
cc.name AS conversation, cc.priority, ut.task_type,
ada.origin_email->>'to' AS draft_to,
ada.origin_email->>'cc' AS draft_cc,
ada.origin_email->>'subject' AS subject
FROM user_tasks ut
JOIN crm_conversations cc ON cc.id = ut.conversation_id
JOIN analytics_draft_actions ada ON ada.draft_id = ut.task_action_data->>'draftId'
WHERE ut.user_id = '$ARGUMENTS'
AND ut.status = 'todo'
AND (
ada.origin_email->>'to' ILIKE '%do-not-reply%'
OR ada.origin_email->>'to' ILIKE '%noreply%'
OR ada.origin_email->>'to' ILIKE '%no-reply%'
OR ada.origin_email->>'to' ILIKE '%chilipiper%'
OR ada.origin_email->>'to' ILIKE '%gong.io%'
OR ada.origin_email->>'to' ILIKE '%superhuman%'
OR ada.origin_email->>'cc' ILIKE '%chilipiper%'
OR ada.origin_email->>'cc' ILIKE '%gong.io%'
)
ORDER BY ut.due_date DESC;
```
## 6. Notification email contamination in CRM timeline
```sql
SELECT
cee.from_email,
COUNT(*) AS total,
COUNT(*) FILTER (WHERE ce.is_significant = true) AS marked_significant,
MIN(ce.occurred_at)::date AS earliest,
MAX(ce.occurred_at)::date AS latest
FROM crm_events ce
JOIN crm_email_events cee ON cee.event_id = ce.id
WHERE ce.user_id = '$ARGUMENTS'
AND (
cee.from_email ILIKE '%superhuman%'
OR cee.from_email ILIKE '%chilipiper%'
OR cee.from_email ILIKE '%gong.io%'
OR cee.from_email ILIKE '%zoom.us%'
OR cee.from_email ILIKE 'noreply@%'
OR cee.from_email ILIKE 'no-reply@%'
OR cee.from_email ILIKE 'do-not-reply@%'
)
GROUP BY cee.from_email
ORDER BY marked_significant DESC, total DESC;
```
## 7. Tasks triggered directly by notification emails
```sql
SELECT
ut.task_action_data->>'draftId' AS draft_id,
ut.status, ut.task_type, ut.due_date::date,
cc.name AS conversation, cc.priority,
cee.from_email AS triggering_from,
cee.subject AS triggering_subject
FROM user_tasks ut
JOIN crm_conversations cc ON cc.id = ut.conversation_id
JOIN agent_executions ae ON ae.run_id = ut.creation_run_id
JOIN crm_events ce ON ce.id = ae.event_id
JOIN crm_email_events cee ON cee.event_id = ce.id
WHERE ut.user_id = '$ARGUMENTS'
AND ut.status = 'todo'
AND (
cee.from_email ILIKE '%chilipiper%'
OR cee.from_email ILIKE '%gong.io%'
OR cee.from_email ILIKE '%superhuman%'
OR cee.from_email ILIKE 'noreply@%'
OR cee.from_email ILIKE 'no-reply@%'
OR cee.from_email ILIKE 'do-not-reply@%'
)
ORDER BY ut.due_date DESC;
```
## 8. Scheduled future tasks — quality check
```sql
SELECT
COALESCE(cc.priority, 'not set') AS priority,
ut.task_type,
CASE
WHEN ut.task_action_data->>'draftId' IS NOT NULL THEN 'has draft'
ELSE 'no draft yet'
END AS draft_state,
COUNT(*) AS count
FROM user_tasks ut
LEFT JOIN crm_conversations cc ON cc.id = ut.conversation_id
WHERE ut.user_id = '$ARGUMENTS'
AND ut.status = 'todo'
AND ut.due_date > NOW()
AND ut.agent_execution_enabled = true
GROUP BY cc.priority, ut.task_type, draft_state
ORDER BY cc.priority NULLS LAST, count DESC;
```
---
---
## What Each Query Tells You
| # | Query | What a "bad" result looks like |
|---|---|---|
| 1 | Draft ranking | Many STALE or DUPLICATE rows; GOOD drafts for COLD conversations |
| 2 | No-draft tasks | Any rows = scheduler miss or agentExecutionEnabled bug; past-due with no draftId and no executionRunId means it never fired |
| 3 | Duplicate tasks | `distinct_runs > 1` for same conversation = agent didn't dedup on re-run; similar descriptions across rows = redundant tasks |
| 4 | Multiple live drafts | Any conversation with > 1 not_sent draft = agent created a second draft without checking if one already exists |
| 5 | Misdirected drafts | Any rows = draft going to a notification address instead of the real contact; delete these |
| 6 | CRM contamination | High `marked_significant` counts for notification senders = agent is treating system emails as real deal activity |
| 7 | Notification-triggered tasks | Any todo rows = task or draft was created because a scheduling/reminder notification fired the agent, not real prospect activity |
| 8 | Scheduled task quality | Large counts for low/not-set priority = agent scheduling work on deals it shouldn't touch; `no draft yet` rows that are past-due = never-fired tasks |
---
## Output Format
After running all queries, produce a report with these sections:
### Summary
A single table with one row per category:
| Category | Count | Severity |
|---|---|---|
| GOOD drafts | N | — |
| STALE drafts (inbound after draft created) | N | High |
| DUPLICATE drafts (older, superseded) | N | Medium |
| COLD drafts (no activity >14 days) | N | Medium |
| No-draft tasks that never fired | N | High |
| Conversations with duplicate tasks (multiple runs) | N | Medium |
| Conversations with multiple live drafts | N | Medium |
| Misdirected drafts (wrong recipient) | N | High |
| CRM events from notification senders | N | Medium |
| Tasks triggered by notification emails | N | High |
### Issues Found
For each category with count > 0, list:
- What the issue is
- 2–3 specific examples (conversation name, draft ID or task ID, description)
- Root cause hypothesis
### Cleanup SQL
Provide ready-to-run SQL for:
1. Draft IDs to delete via the UI tool (STALE + DUPLICATE + misdirected) — list as a code block, one per line
2. Marking notification sender events as not significant:
```sql
UPDATE crm_events ce
SET summary = 'Automated notification — not real prospect activity',
is_significant = false, updated_at = NOW()
FROM crm_email_events cee
WHERE cee.event_id = ce.id
AND ce.user_id = '$ARGUMENTS'
AND (
cee.from_email ILIKE '%superhuman%'
OR cee.from_email ILIKE '%chilipiper%'
OR cee.from_email ILIKE '%gong.io%'
OR cee.from_email ILIKE 'noreply@%'
OR cee.from_email ILIKE 'no-reply@%'
OR cee.from_email ILIKE 'do-not-reply@%'
);
```
Do NOT run any UPDATE or DELETE statements automatically — only provide them for the user to review and execute.