Purpose

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

• Successful/unsuccessful SMS submissions stats
• Undelivered SMS
• SMS delivery latency

Specification

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

Input periods:

• day (last 1 day)
• month (last 30 days)
• total 

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 body<>'VERIFICATION' then 'Medication request' else body

Calculate successful ratio:

• count(status='Delivered')/count(status<>'Delivered')

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 body<>'VERIFICATION' then 'Medication request' else body

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 body<>'VERIFICATION' then 'Medication request' else body

Calculate SMS delivery latency based on groups:

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