Need help finding the next status within a group and comparing dates

Question & Answer
,
1

Hello!

I have a dataset sourced from our phone system that looks like this:

timestamp Operator Line Answered by Phone #
1/05/2024 11:00 AM Sam 1111-1111
1/05/2024 11:15 AM Sam 1111-1111
1/05/2024 11:16 AM Alex Alex 2222-2222
1/05/2024 12:05 PM Sam Sam 1111-1111

if the ‘Answered by’ field is blank, then the phone call was missed/not answered. I’ve added a calculated field to show the status:

timestamp Operator Line Answered by Phone # Call status
1/05/2024 11:00 AM Sam 1111-1111 Missed
1/05/2024 11:15 AM Sam 1111-1111 Missed
1/05/2024 11:16 AM Alex Alex 2222-2222 Answered
1/05/2024 12:05 PM Sam Sam 1111-1111 Answered

I want to know if any missed calls were then answered within an hour for each phone #. If yes, the ‘Missed’ status should be changed to ‘Returned’. So, I want to see the following result:

timestamp Operator Line Answered by Phone # Call status Final status
1/05/2024 11:00 AM Sam 1111-1111 Missed Missed
1/05/2024 11:15 AM Sam 1111-1111 Missed Returned
1/05/2024 11:16 AM Alex Alex 2222-2222 Answered Answered
1/05/2024 12:05 PM Sam Sam 1111-1111 Answered Answered

Could someone please help find a solution. Thank you!!

Julia

1 Like
2

Hi @julia_z - Welcome to AWS QuickSight community and thanks for posting the question. Couple of questions.

  1. What is your data source? is it relational database? Then we can think of write a custom sql and do self join and populate the final status.

Also the phone number needs to answered on the same date, for example if someone called today and it is not answered and the same person call next day and answered, how you are planning to maintain the status.

I am thinking of comparing the data with answered data set on the basis of phone number and date and then populate the final status, however let’s hear from other experts, tagging @David_Wong @sagmukhe @Biswajit_1993 @ErikG @duncan for their advise as well.

Regards - Sanjeeb

1 Like
3

Hi @Sanjeeb2022 It’s an Excel file and I’m unable to use sql.

Ideally, it would be great to consider business hours (9am - 5 pm). If someone calls today at 4:50 pm and is not answered, but then the same person calls back at 9:15 am the next day and it is answered, the status of the phone call should be ‘Returned’.

However, for simplicity, we can skip the business hours consideration and mark the unanswered 4:50 pm call as ‘Missed’.

4

Hello @julia_z and @Sanjeeb2022 !

This might be possible in QuickSight as a calculated using ifelse. Is it safe to assume that in the same CSV that you will have the time stamp of when a caller (same phone number) called back with a timestamp?

5

Hi @duncan, yes, there will also be a timestamp for when the same phone number calls back. I’m not sure how to use the ifelse function in this case, as we need to group by phone number and continue looping until we find the ‘Answered’ status and then compare the timestamps… Could you please help?

1 Like
6

Hi @duncan - Need your help on this, looks like it is a very interesting problem. I am also exploring how to get the desired result using calculated field with if else in a single data set. Please see if you are able to find something, please share.

Regards - Sanjeeb

7

Hello @Sanjeeb2022 and @julia_z !

I’m sorry about the late reply!

The big thing here is making sure that you have enough timestamps/attributes to show that the some one called in and wasn’t answered, and then either called back and was answered or someone on your team called them back and was able to reach the original caller. Can you provide a wider view of your table or what it might look like when a caller calls back?

We can circle back on the looping function, but for now this is my thinking:
Final Status:

ifelse(
dateDiff({timestampFirstCall}, {timestampSecondCall}, 'HH') <= 1 
  AND {CallStatus} = 'Missed'
    AND {AnsweredBy} = ' ', 
'Returned', 
'Missed'
)

My thought process is that we want to make sure that the timsetamps for the two calls are within an hour of each other, that the original call status is 'Missed", and that {AnsweredBy} is an empty string for the first call. What will help me is to know if you are generating new rows for when the call comes back in. Because then it will change the logic here.

8

Hi @duncan . Here is the wider table:

timestamp Operator Line Answered by Phone #
1/05/2024 11:00 AM Sam 1111-1111
1/05/2024 11:15 AM Sam 1111-1111
1/05/2024 11:16 AM Alex Alex 2222-2222
1/05/2024 12:05 PM Sam Sam 1111-1111
1/05/2024 1:03 PM Sam 1111-1111
1/05/2024 1:04 PM Sam 1111-1111
1/05/2024 1:05 PM Sam 1111-1111
1/05/2024 1:06 PM Sam 1111-1111
1/05/2024 1:07 PM Sam 1111-1111
1/05/2024 1:08 PM Sam 1111-1111
1/05/2024 1:30 PM outbound Sam 1111-1111
1/05/2024 2:00 PM Tom 3333-3333
1/05/2024 2:10 PM Tom 3333-3333
1/05/2024 3:10 PM Tom 3333-3333
1/05/2024 3:15 PM outbound Sam 3333-3333

We are also working on integrating this data into our data lake to enable us to use SQL. Would this be easier? How the query would look like?

Thanks!

9

Just to clarify:

timestamp - the time of the phone call
Operator Line - the person the customer was calling. ‘Outbound’ means call was initiated by
the operator, not the customer
Answered by - The name of the operator who answered/initiated the call.
Phone # - the client’s phone number

10

Hi @julia_z, this maybe out of scope for QuickSight, you may want to consult a Database Solutions Architect, in any case, based on my experience, below are a few example SQL queries to retrieve specific information from your CallLogs table:

Example Queries

  1. Retrieve all calls made by a specific operator (e.g., ‘Sam’):
SELECT * 
FROM CallLogs 
WHERE operator_line = 'Sam';
  1. Retrieve all outbound calls:
SELECT * 
FROM CallLogs 
WHERE operator_line = 'outbound';
  1. Count the number of calls handled by each operator:
SELECT answered_by, COUNT(*) as call_count
FROM CallLogs
GROUP BY answered_by;
  1. Get calls for a specific client phone number (e.g., ‘1111-1111’):
SELECT * 
FROM CallLogs 
WHERE phone_number = '1111-1111';
  1. Retrieve calls within a specific time range:
SELECT * 
FROM CallLogs 
WHERE timestamp BETWEEN '2024-01-05 11:00:00' AND '2024-01-05 12:00:00';
  1. Count the number of outbound calls made by each operator:
SELECT answered_by, COUNT(*) as outbound_call_count
FROM CallLogs
WHERE operator_line = 'outbound'
GROUP BY answered_by;
  1. List all unique client phone numbers:
SELECT DISTINCT phone_number 
FROM CallLogs;
  1. Find the operator who answered the most calls:
SELECT answered_by, COUNT(*) as call_count
FROM CallLogs
GROUP BY answered_by
ORDER BY call_count DESC
LIMIT 1;

Example Query Results

1. Retrieve all calls made by ‘Sam’:

SELECT * 
FROM CallLogs 
WHERE operator_line = 'Sam';
timestamp operator_line answered_by phone_number
2024-01-05 11:00:00 Sam 1111-1111
2024-01-05 11:15:00 Sam 1111-1111
2024-01-05 12:05:00 Sam Sam 1111-1111
2024-01-05 13:03:00 Sam 1111-1111
2024-01-05 13:04:00 Sam 1111-1111
2024-01-05 13:05:00 Sam 1111-1111
2024-01-05 13:06:00 Sam 1111-1111
2024-01-05 13:07:00 Sam 1111-1111
2024-01-05 13:08:00 Sam 1111-1111
2024-01-05 13:30:00 outbound Sam 1111-1111
2024-01-05 15:15:00 outbound Sam 3333

Additionally, please reach out to your account manager to request assistance from an AWS Solutions Architect who specializes in Data Lakes.