Table of Contents

Purpose

In order to understand to understand the SMS channel efficiency next metrics must be pre calculated and shown on datadog dashboard: 

...

All metrics must be continuous and are calculated based on input period. 

Input periodsperiod:

• previous day (last 1 day)
• month (last month)
• total 
• current day

Successful/unsuccessful SMS submissions stats

Fetch records from VERIFICATION.sms_logs, where:

• status not in  ('Accepted', 'Enroute')

• now()-inserted_at<=inputed_period (if inputed_period = total then all sms)
• if type<>'VERIFICATION' then 'Medication request' else type

Calculate successful ratio:

• count(status='Delivered')/count(*)

Undelivered SMS

Fetch records from VERIFICATION.sms_logs, where:

• status not in  ('Accepted', 'Enroute')

• now()-inserted_at<=inputed_period (if inputed_period = total then all sms)
• if type<>'VERIFICATION' then 'Medication request' else type

Calculate Undelivered SMS qty:

• count(status<>'Delivered')

SMS delivery latency

Fetch records from VERIFICATION.sms_logs, where:

• status = 'Delivered'

• now()-inserted_at<=inputed_period (if inputed_period = total then all sms)
• if type<>'VERIFICATION' then 'Medication request' else type

Calculate SMS delivery latency based on groups:

• define groups:
  • '<=1 2 min': status_changed_at - inserted_at<=1 2 min 
  • '1 2 - 5 min':  status_changed_at - inserted_at<=5 min 
  • '5 - 30 min': status_changed_at - inserted_at<=30 min 
  • '30 min - 12 hours': status_changed_at - inserted_at <=30 min - 12 hours 
  • '>12 hours': status_changed_at - inserted_at> 12 hourselse '>30 min'
• count(*) - sms qty in each group

...