Report not found

There was a problem finding this report.
Dashboard - Intercom (Customer Support)
May 5, 2017 · Refreshed 7 days ago

Collaborators

Run History
SELECT * FROM (WITH day AS ( SELECT time_id FROM demo.rollup_periods WHERE period_id = 1 AND time_id BETWEEN '2016-01-01' AND '2016-12-31' ), conversations AS ( SELECT DATE_TRUNC('day',c.created_at AT TIME ZONE 'America/Los_Angeles') AS time_id, COUNT(c.id) AS new_conversations, COUNT(r.id) AS reassigned_conversations, ROUND(AVG(EXTRACT('epoch' FROM ir.initial_response_at - c.created_at) / 60)::numeric,0) AS avg_irt_min, ROUND(AVG(EXTRACT('epoch' FROM ir.resolution_at - c.created_at) / 60)::numeric,0) AS avg_resolution_min FROM demo.intercom_conversations c LEFT JOIN ( SELECT p.conversation_id, MIN(p.created_at) as initial_response_at, MIN(CASE WHEN p.part_type = 'close' THEN p.created_at ELSE NULL END) AS resolution_at FROM demo.intercom_conversation_parts p WHERE p.author_type = 'admin' AND p.part_type NOT IN ('note','assignment') GROUP BY 1 ) ir ON ir.conversation_id = c.id LEFT JOIN ( SELECT DISTINCT c.id FROM demo.intercom_conversations c JOIN demo.intercom_conversation_parts p1 ON c.id = p1.conversation_id JOIN demo.intercom_conversation_parts p2 ON c.id = p2.conversation_id AND p1.assigned_to_id != p2.assigned_to_id ) r ON c.id = r.id WHERE c.message_author_type = 'user' GROUP BY 1 ), conversation_parts AS ( SELECT DATE_TRUNC('day',p.created_at AT TIME ZONE 'America/Los_Angeles') AS time_id, COUNT(DISTINCT CASE WHEN p.author_type = 'user' THEN p.conversation_id ELSE NULL END) AS inflow, COUNT(DISTINCT CASE WHEN p.author_type = 'admin' THEN p.conversation_id ELSE NULL END) AS outflow FROM demo.intercom_conversation_parts p JOIN demo.intercom_conversations c ON c.id = p.conversation_id GROUP BY 1 ) SELECT day.time_id, COALESCE(conversation_parts.inflow,0) AS conversation_inflow, COALESCE(conversation_parts.outflow,0) AS conversation_outflow, COALESCE(conversations.new_conversations,0) AS new_conversations, COALESCE(conversations.reassigned_conversations,0) AS reassigned_conversations, conversations.avg_irt_min / 60 AS avg_initial_response_hrs, conversations.avg_resolution_min / 60 AS avg_resolution_hrs, ROUND(COALESCE(conversations.reassigned_conversations,0) / conversations.new_conversations::NUMERIC,2) AS reassignment_rate FROM day LEFT JOIN conversations ON day.time_id = conversations.time_id LEFT JOIN conversation_parts ON day.time_id = conversation_parts.time_id ORDER BY 1 DESC ) AS daily_support ORDER BY time_id DESC LIMIT 60
SELECT * FROM {{ @daily_support }} ORDER BY time_id DESC LIMIT 60
<div class="mode-header embed-hidden"> <h1>{{ title }}</h1> <p>{{ description }}</p> </div> <div class="mode-grid container"> <div class="row" data-row-height="medium"> <div class="col-md-9"> <mode-chart id="chart_5e61771f1b94" dataset="dataset" options="chart_options"></mode-chart> </div> <div class="col-md-3"> <mode-chart id="chart_2ef116522fc0" dataset="dataset" options="chart_options"></mode-chart> </div> </div> <div class="row"> <div class="col-md-6"> <mode-chart id="chart_10387be730a0" dataset="dataset" options="chart_options"></mode-chart> </div> <div class="col-md-6"> <mode-chart id="chart_7a5e0eb7d8a2" dataset="dataset" options="chart_options"></mode-chart> </div> </div> <div class="row"> <div class="col-md-12"> <mode-chart id="chart_edea683943b7" dataset="dataset" options="chart_options"></mode-chart> </div> </div> <div class="row" data-row-height="medium"> <div class="col-md-9"> <mode-chart id="chart_ac4300828d8e" dataset="dataset" options="chart_options"></mode-chart> </div> <div class="col-md-3"> <mode-chart id="chart_f8f46dda82f0" dataset="dataset" options="chart_options"></mode-chart> </div> </div> </div>
{{ dataSourceName(params.queryId) }}

The dataset is too large to view in browser

Export

Looks like something went wrong with your query.

{{ DS.queryRuns[params.queryId].errorMessage }}
This query was cancelled