379e28df1f
The bot metrics dashboard can show `handoff_rate + resolution_rate >
100%`. A single conversation can accumulate both
`conversation_bot_handoff` and `conversation_bot_resolved` events, and
the rate queries count them independently against a shared denominator.
## How it happens
```
Customer messages bot inbox
│
▼
┌──────────┐
│ pending │ (bot handling)
└────┬─────┘
│ bot can't help
▼
┌──────────┐
│ open │ (handed off → conversation_bot_handoff event created)
└────┬─────┘
│ agent clicks "Resolve" WITHOUT sending a message
▼
┌──────────┐
│ resolved │ conversation_resolved fires
└──────────┘
│
▼
create_bot_resolved_event guard checks:
✅ inbox.active_bot?
✅ no outgoing messages with sender_type: 'User' ← agent never messaged!
│
▼
conversation_bot_resolved event ALSO created ← BUG
│
▼
Same conversation counted in BOTH rates → sum exceeds 100%
```
## Why fix at the read path, not the write path
An earlier attempt added guards in the listener to make the two events
mutually exclusive per conversation — deleting `bot_resolved` when a
handoff fires, suppressing resolutions when a handoff exists. This was
rejected because conversations can be reopened across multiple cycles
(bot resolves on day 1, customer returns on day 5, bot hands off).
Deleting the day-1 resolution corrupts historical reports, and the async
event dispatcher makes listener-level guards vulnerable to race
conditions.
## What this PR does
Within a reporting window, if a conversation has both events, **handoff
wins** — the conversation is excluded from the resolution count. This is
applied via SQL subquery across all three read paths:
```
┌─────────────────────────┐
│ Reporting Events DB │
│ │
│ conv_bot_handoff: [A,B] │
│ conv_bot_resolved: [A,C]│
└────────┬────────────────┘
│
┌──────────────┼──────────────┐
▼ ▼ ▼
BotMetricsBuilder ReportHelper CountReportBuilder
(rate cards) (bot_summary) (timeseries charts)
│ │ │
▼ ▼ ▼
resolutions: resolutions: resolutions:
[A,C] minus [A,B] same logic same logic
= [C] only = [C] only = [C] only
Result: Conversation A → handoff only
Conversation B → handoff only
Conversation C → resolution only
```
For wide date ranges spanning multiple lifecycles, a conversation
bot-resolved in one cycle and handed off in a later cycle will only show
as a handoff. This is an acceptable tradeoff — the alternative (>100%
rates) is clearly worse, and narrow ranges handle this correctly since
the events fall into different windows. No reporting events are
modified, so historical data stays intact.
## Diagnostic tool
`rake bot_metrics:diagnose` — read-only task that prompts for account ID
and date range, shows a before/after rate comparison without modifying
data.
---------
Co-authored-by: aakashb95 <aakashbakhle@gmail.com>
Co-authored-by: Aakash Bakhle <48802744+aakashb95@users.noreply.github.com>
66 lines
1.9 KiB
Ruby
66 lines
1.9 KiB
Ruby
class V2::Reports::BotMetricsBuilder
|
|
include DateRangeHelper
|
|
attr_reader :account, :params
|
|
|
|
def initialize(account, params)
|
|
@account = account
|
|
@params = params
|
|
end
|
|
|
|
def metrics
|
|
{
|
|
conversation_count: bot_conversations.count,
|
|
message_count: bot_messages.count,
|
|
resolution_rate: bot_resolution_rate.to_i,
|
|
handoff_rate: bot_handoff_rate.to_i
|
|
}
|
|
end
|
|
|
|
private
|
|
|
|
def bot_activated_inbox_ids
|
|
@bot_activated_inbox_ids ||= account.inboxes.filter(&:active_bot?).map(&:id)
|
|
end
|
|
|
|
def bot_conversations
|
|
@bot_conversations ||= account.conversations.where(inbox_id: bot_activated_inbox_ids).where(created_at: range)
|
|
end
|
|
|
|
def bot_messages
|
|
@bot_messages ||= account.messages.outgoing.where(conversation_id: bot_conversations.ids).where(created_at: range)
|
|
end
|
|
|
|
def bot_resolutions_count
|
|
# Exclude conversations that also had a handoff in the same range — handoff wins
|
|
account.reporting_events.joins(:conversation).select(:conversation_id)
|
|
.where(account_id: account.id, name: :conversation_bot_resolved, created_at: range)
|
|
.where.not(conversation_id: bot_handoff_conversation_ids_subquery)
|
|
.distinct.count
|
|
end
|
|
|
|
def bot_handoffs_count
|
|
account.reporting_events.joins(:conversation).select(:conversation_id)
|
|
.where(account_id: account.id, name: :conversation_bot_handoff, created_at: range)
|
|
.distinct.count
|
|
end
|
|
|
|
def bot_handoff_conversation_ids_subquery
|
|
account.reporting_events
|
|
.where(name: :conversation_bot_handoff, created_at: range)
|
|
.where.not(conversation_id: nil)
|
|
.select(:conversation_id)
|
|
end
|
|
|
|
def bot_resolution_rate
|
|
return 0 if bot_conversations.count.zero?
|
|
|
|
bot_resolutions_count.to_f / bot_conversations.count * 100
|
|
end
|
|
|
|
def bot_handoff_rate
|
|
return 0 if bot_conversations.count.zero?
|
|
|
|
bot_handoffs_count.to_f / bot_conversations.count * 100
|
|
end
|
|
end
|