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 period:

• previous day
• current day

Successful/unsuccessful SMS submissions stats

Fetch records from VERIFICATION.sms_logs, where:

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

• now()-inserted_at<=inputed_period
• 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
• 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
• type

Calculate SMS delivery latency based on groups:

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