Updating the Notify Service Reporting Sheet with new metrics

[sharlychan-cds]

Give your user story a simple, clear title

Description

As a Notify or platform team member, I need to be able to have clear metrics so that I can achieve reporting needs for growth, reporting etc. Updating the Notify Service Reporting sheet with new metrics and tables will help.

Related cards:

• List the stats or metrics we'd like to pull from Notify#259
• Metrics Dashboard#248

WHY are we building? To provide more meaningful metrics the Notify and platform team can use. We can also completely handover the management of the sheet from Lucas (who created it last year!)

WHAT are we building? More robust notify service reporting sheet

VALUE created by our solution Better metrics for reporting

Acceptance Criteria** (Definition of done)

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

• [x] Sharly and other team members to team up and learn from Lucas to completely manage the Notify Service Reporting sheet
• [ ] Add this metric to track: How long ago did a service sent a notification? (we can see if we want to put services back on trial after x months or years? – I think this is something in our ATO risk mitigation too?)
• [ ] Add this metric to track: New live services this month by department % new services this month
• [ ] Add this metric to track: Most active services this month )sms and email)
• [ ] Add this metric to track: Most active department
• [ ] Look into seeing whether/ how we can connect qualitative data: # of helpdesk tickets, CRM (e.g. data point where we contact clients and when they contact us)
• [ ] Add metrics on active clients (from data portage)

[sharlychan-cds]

New issue found in sheet to consider:

• Issue: If the raw_data tab is too long some of the tables are off (where there should only have been two subsections, "[COVID?] FALSE" and "[COVID?] TRUE", there was a third that was "[COVID?] " just blank, which seemed wrong).

• Reason: raw data tab had grown so long that the processed_data tab was no longer processing all the data

• The Fix: selected the calculated columns in the processed_data tab (the ones with [square braces], aka F, G, H, and I) and copied them down, adding another 1000 rows or so to provide some buffer.

• Future: May be something to add as a check, like, "Are the rows at the bottom of the processed_data tab complete? The first five columns should mimic the raw_data tab, and the last four should be filled out, not blank, automatically. If not, copy down the four columns to ensure the calculated values populate."

[yaelberger-commits]

@sharlychan-cds Is this card a duplicate?

[sharlychan-cds]

This card is for more in-depth work that needs to be done to the service reporting sheet to revamp it.

(#401 is the weekly reminder update where I just pull reports to get #s)

[yaelberger-commits]

We now have Blazer that we can use to query for new metrics and data we want. Iceboxing this ticket