Fix the SMS cost reporting in QuickSight

[jimleroyer]

Describe the bug

The data set for Notifications with SMS pricing is broken as we started to use AWS Pinpoint instead of AWS SES.

Bug Severity

SEV-3 Minor

This is minor as this does not prevent the product from running properly and has no impact on our users, but it has an impact on our ability to make sound financial decisions if we do not track this.

To Reproduce

Get to any QuickSight analysis/dashboard using the Notifications with SMS pricing dataset and dig into its data. The cost reduced a lot since we switched from SES to Pinpoint, as the former is used only for a small subset of notifications and the latter is the main SMS conduct. We are missing this cost and we need to re-enable its tracking.

Expected behavior

The SES and Pinpoint cost should both be reported into QuickSight.

Impact

Describe overall user/system impact to consider when prioritizing this issue.

If applicable

Impact on Notify users: None

Impact on Recipients: None

Impact on Notify team: Inability to get SMS cost for the past 2-3 months.

Screenshots

The reported cost is missing per notifications. Here is a screenshot showing the aggregation of these per months. The sent notifications did not go down but one would think so when looking at this graph:

Additional context

We need to bring in the Pinpoint cost into QuickSight!

[jimleroyer]

We were able to get the SMS cost via a nightly dump of SMS usage report that was supported by AWS SNS via configuration. Unfortunately, I am not able to find a similar feature for Pinpoint or AWS End User Messaging.

I created an AWS case (Case ID 172721288500621 - Is there a SMS usage report feature, just like in SNS? - GCNotify production account) to ask if such similar feature exist.

If a similar feature does not exist, it seems our best way to get the SMS cost will be to parse the response back from AWS when we send the SMS notifications and look into the reported cost in there, to be saved directly into the database.

[jimleroyer]

Unfortunately, Pinpoint has no similar feature as the one for SES, i.e. a daily SMS usage report dumped into S3. We likely would have to go with listening to Pinpoint event or delivery reception to get the cost and save it into the database. This should be a new card because this will be more than a bug fix.

[sastels]

new card Add Pinpoint costing info to notifications table

[sastels]

New fields in the prod RDS tables. Now we can add them to the QuickSight Notifications dataset.

[jimleroyer]

There is a PR ready to be merged today. We will test what happens with the db activity in staging.

[sastels]

staging now the same size as prod, next step is to merge terraform PR to staging and monitor behaviour

[sastels]

staging quicksight tf apply started 10:25

[sastels]

TL;DR

• terragrunt apply didn't do an auto refresh this time, but did timeout and need to be rerun
• manual refresh took time and resources expected
• all load test notifications delivered

More details:

• tf apply timed out and failed GitHub action
• changed the dataset to the new fields, but did not kick off a refresh
• second run failed because there was a lock
• deleted the lock with ENVIRONMENT=staging terragrunt force-unlock 48935a0e-65ed-a4a3-77dd-3f73d6b15298 after copying the tfvars from 1Password
• third run passed immediately ("no changes")
• did a manual refresh and that succeeded in about 1 hour.
• all load test messages delivered
• rds metrics look fine. Usage jumps up, but just for one read instance
• rds free local storage (that crashed up before with QuickSight refreshes until we upgraded our instances) dropped from 67 G to 42 G for that instance during the refresh
• new data fields now in Notifications QuickSight dataset

[sastels]

releasing to prod 4:05

• GitHub action succeed right away and kicked off the refresh (as opposed to what happened on staging)
• I see that in prod it's been taking about 2 hours for the refresh, rather than an hour as we had in staging. So staging isn't behaving the same as prod, even with the notification history table bumped to the same size.
• free local storage in prod dropped from 67 to 27 G, also significantly different from staging (we've crashed instances in the past by getting this down to zero).
• data in QuickSight!

[sastels]

Will fix / remake the sms cost QuickSight report

[sastels]

Fixed (my own copy of) Jimmy's "JLR - SMS volume usage" analysis here.

Shall we publish this new version to a dashboard and mark this Done?

[P0NDER0SA]

Jimmy is gonna take a look today!

[P0NDER0SA]

Jimmy can look today because it was busted yesterday!

[jimleroyer]

I renamed the analysis SMS volume usage - SJA copy to SMS volume usage. I moved a widget on the cost pane that should be elsewhere and that might have been some previous experimentation unrelated to SMS cost data.

Everything looks good. ✅