CTHUB - Metabase: Write a Metabase Model to compile ITVR: Driver License Number's for Redeemed Application

[shayjeff]

Describe the task Write an SQL statement in Metabase (Model) to develop a custom query within CTHUB, allowing the business users the ability to obtain a list of driver license numbers corresponding to redeemed rebate applications from the ITVR system for a specific time period. (i.e. day, month, year)

Purpose This will enable the business area to swiftly and efficiently obtain information on redeemed application license numbers within a specific time frame when requested.

Columns to include Drivers license number Redeemed applications Date field?

Acceptance Criteria

• [ ] create question in metabase that shows information, that can be copied and reused by analysts
• [ ] The query allows filtering by specific dates and months in the year
• [ ] Only the driver's license numbers of redeemed applications are needed.

Development Checklist

• There's still some uncertainties around this; please see the TBDs in the additional context section for details.

Additional context

• This ticket was initiated by the business area in response to a request from BCHydro. BCHydro assumed the funding of the vehicle rebate program in May. With this transition, they now have the authority to request information for audit purposes. Specifically, BC Hydro is seeking the driver's license numbers of redeemed rebate recipients to cross-check the data received from the NCDA against the records in the ITVR system. They have requested a monthly report to facilitate this cross-check process before each payment is remitted to the NCDA.
• TBD: Is this a CTHUB query or a feature we should build into ITVR? If we go with a CTHUB solution, do we need to write a "base" query for them, or is it simple enough that business side users can extract the information they want with just a few clicks?
• TBD: Is the "date" field mentioned above the "redeemed on" date? If so, we don't have that date, but we do have a "modified" date on the go_electric_rebate table, which, in most cases, shouldn't be too far off from the actual "redeemed on" date.
• TBD: The drivers_licence field in the go_electric_rebate table is hidden in metabase (probably for privacy reasons); are we allowed to unhide it?

[airinggov]

how is the NCDA sending this info to BC Hydro?

[shayjeff]

The business area is still awaiting a reply to this question. I'll keep you all posted as soon as we have the information.