6-3.Athena でのデータ分析

SELECT ctr.queue.name as "Queue Name",  
  AVG(ctr.queue.duration) as "Average Hold Duration"  
FROM "amazonconnect_ctr" ctr WHERE ctr.agent is null  
  AND ctr.queue is not null 
  AND ctr.queue.dequeuetimestamp = ctr.disconnectTimestamp 
  AND initiationmethod = 'INBOUND' GROUP BY ctr.queue.name; 

SELECT
	ctr.customerendpoint.address as "Customer Endpoint", 
	count(CASE WHEN ctr.initiationmethod = 'CALLBACK' THEN 1 END) as "Count of Callbacks", 
	count(CASE WHEN ctr.initiationmethod = 'CALLBACK' and ctr.agent is not NULL THEN 1 END) as "Count of Connected Callbacks", 
	count(CASE WHEN ctr.initiationmethod = 'OUTBOUND' THEN 1 END) as "Count of Outbound Contacts", 
	count(CASE WHEN ctr.initiationmethod = 'OUTBOUND' AND ctr.connectedtosystemtimestamp is not NULL THEN 1 END) as "Count of Connected Outbound Contacts", 
	count(CASE WHEN ctr.initiationmethod = 'INBOUND' THEN 1 END) as "Count of Inbound Contacts",   
	count(CASE WHEN ctr.initiationmethod = 'INBOUND' AND (ctr.agent is not NULL or ctr.transfercompletedtimestamp is not NULL) THEN 1 END) as "Count of Connected or Transferred Inbound Contacts", 
	count(CASE WHEN ctr.initiationmethod = 'API' THEN 1 END) as "Count of API Contacts",   
	count(ctr.customerendpoint.address) as "Total Contacts" 
FROM "amazonconnect_ctr" ctr 
WHERE ctr.customerendpoint.address is not null 
  AND ctr.customerendpoint.address <> '' GROUP BY ctr.customerendpoint.address; 

SELECT 
	ctr.customerendpoint.address as "Customer Endpoint", 
	ctr.initiationmethod as CallInitiationMethod, 
	ctr.ContactId, 
	ctr.InitiationTimestamp as CallInitiationTimestamp, 
	ctr.Agent.ConnectedToAgentTimestamp as ConnectedToAgentTimestamp, 
	ctr.DisconnectTimestamp as DisconnectTimestamp, 
	ctr.ConnectedToSystemTimestamp as ConnectedToSystemTimestamp,
	ctr.queue.enqueuetimestamp as EnqueuedTimestamp, 
	ctr.queue.dequeuetimestamp as DequeuedTimestamp, 
	ctr.transfercompletedtimestamp as ExternalTransferTimestamp, 
	CAST(to_unixtime(from_iso8601_timestamp(ctr.DisconnectTimestamp)) as 
INTEGER)-CAST(to_unixtime(from_iso8601_timestamp(ctr.ConnectedToSystemTimestamp)) as 
INTEGER) as CustomerCallLength_inSeconds 
 FROM "amazonconnect_ctr" ctr 
 ORDER BY ctr.InitiationTimestamp DESC 

SELECT *, CAST((HandleRate.IVRHandleCount+HandleRate.AgentHandleCount) as 
DECIMAL(16,2))/CAST(HandleRate.ContactCount as DECIMAL(16,2)) as 
CaseHandleRate 
FROM (   SELECT 
  ctr.queue.name as "QueueName",  
  SUM(CASE WHEN  
	  (ctr.agent is null 
	  AND ctr.agentconnectionattempts = 0 
	  AND ctr.queue.dequeuetimestamp = ctr.disconnectTimestamp 
	  AND ctr.attributes NOT LIKE '%"testattribute":"true"%')  
	THEN 1 ELSE 0 END) as "AbandonedCallCount", 
  SUM(CASE WHEN  
	  (ctr.agent is null  
	  AND ctr.queue.dequeuetimestamp = ctr.disconnectTimestamp 
	  AND ctr.agentconnectionattempts > 0 
	  AND ctr.attributes NOT LIKE '%"testattribute":"true"%')      
	THEN 1 ELSE 0 END) as "AbandonedCall-NoAgentAvailable-Count", 
  SUM( CASE WHEN  
	  (ctr.agent is null  
	  AND ctr.attributes LIKE '%"testattribute":"true"%')  
	 -- Change this attribute to something in your flow that signifies the customer inquiry has been successfully closed    
	THEN 1 ELSE 0 END) as "IVRHandleCount", 
  SUM(CASE WHEN  
	  (ctr.agent is not null) 
	THEN 1 ELSE 0 END) as "AgentHandleCount", 
  COUNT(ctr.contactId) as "ContactCount" 
FROM "amazonconnect_ctr" ctr 
WHERE initiationmethod = 'INBOUND' 
GROUP BY ctr.queue.name ) as "HandleRate";