Get SMS report usage for individual SMS cost

[jimleroyer]

Description

As a a product manager, I need to be able to do determine individual SMS costs so that I can achieve bill recovery.

WHY are we building?

To track down how much certain SMS costs. Long term is for charging back and short time it is to know how some of the SMS we send cost using short code, long code or to the international.

WHAT are we building?

Setting up SMS usage report into a S3 bucket.

VALUE created by our solution

Determine how much each SMS cost.

Acceptance Criteria

Given some context, when (X) action occurs, then (Y) outcome is achieved.

• [x] The SMS usage report is delivered in a daily manner into a S3 bucket, ready to be downloaded by people with AWS access to our GCNotify accounts.

QA Steps

• [x] Download the SMS report and correlate with SMS that we send using short code, long code and to international destinations.

[jimleroyer]

PR up for review: https://github.com/cds-snc/notification-terraform/pull/1173

[jimleroyer]

I merged the PR into staging an triggered many sentinel alerts, breaking the terraform apply (the plan was good). It seems there are account protections preventing us from doing this work. I will sync with the SRE team to bypass these.

[jimleroyer]

This was deployed successfully in staging yesterday and we got our first reports today.

The SMS usage report was activated yesterday in the staging environment and this is what we get in the file (I masked part of the phone numbers here):

PublishTimeUTC,MessageId,DestinationPhoneNumber,MessageType,DeliveryStatus,PriceInUSD,PartNumber,TotalParts
2024-02-22T12:18:26.455Z,f72ba81a-005d-5cdf-8660-f64b3d8ff62f,+1613xxxxxxx,Transactional,Message has been accepted by phone carrier,0.01181,1,1
2024-02-22T13:48:57.182Z,4a687617-f522-54ee-a615-2846a62b8f57,+1613xxxxxxx,Transactional,Message has been accepted by phone carrier,0.01181,1,1
2024-02-22T13:50:18.679Z,ba9ed2a3-f3c3-5d12-bcf9-8cf2a099eabd,+1613xxxxxxx,Transactional,Message has been accepted by phone carrier,0.01181,1,1
2024-02-22T21:47:57.012Z,06ff9a46-07c4-5981-8aaf-c417c48828fd,+1514xxxxxxx,Transactional,Message has been accepted by phone,0.01181,1,1

Notes

• We have the message ID from AWS perspective which we register into our notifications database table under the column reference.
• We have total parts here!
• And we have a reference to the part number..
• And the total cost.

So everything was a 1 fragment SMS send yesterday in staging and it all cost 0.01181 USD, which is the advertised cost on the AWS documentation so our estimates should have been on point for national SMS sending.

[sastels]

Will QA and do a demo when it's in prod

[sastels]

Reports should be there today - Steve will take a look!

[sastels]

Looked at results, as advertised! Created a pivot table to look at short code vs long codes usage :tada: