Close rate and upsell rate: CC Dashboard Metrics Build out

Single source of truth for dashboard metrics before ATT-1029 implementation.

https://docs.google.com/spreadsheets/d/1sWj_SvyeNIWs7ahNbrj6s64nzYwJPGN0485sDEQuJsE/edit?gid=1251726975#gid=1251726975

We want to use mongo views to create a living place that these calculations can be queried per company. This is a lot of data so we need to ensure that the the query is fast. We want to start with a standard view and if it is not fast enough, convert to a On-Demand Materialized View.

There are 2 columns. One for all and one for "core only". Core only will basically be jobs where the primary is excludeFromClose: false .

We likely need to have the view save these calculations by company and by day. Here are the documents:

DPO: SKIP THIS DO NOT CALC Total Revenue: All jobs, totaled by "total" key

Close rate (draft from Mar 16 session)

• Numerator: confirmed, non-cancelled jobs where the scheduledTs is in date range. (Count of these not by revenue)
• Denominator: all "created" in range—jobs (from quotes or one-call) + pending quotes + rejected quotes. (Count of these not by revenue). Quotes are sorted by the "createdAt" date. Inspections by the "ScheduledTs"
• Exclusions: cancelled jobs, on-hold quotes, draft quotes.
• Attribution: created by on quote/order, not who confirmed.
• Date field: align on job scheduled time (or one agreed field) for quote-sourced vs one-call—document edge cases and transition from spreadsheet logic.
• If the primary service for a job is in a excludeFromClose: false business category, this should not be included in the either the top or bottom number in the core only column.

Upsell rate

• Definition, business-segment rules, core-only variant, optional umbrella focus add-ons.
• upsellable this is where this boolean comes in to play. We want to take all the jobs where the primary service is upsellable: true and see if there is a 2nd service on the job or not. This gives you your global upsell rate.

# of Orders Created are excludeFromClose: false

• Basic count of jobs in the time frame.

# of Orders Confirmed are excludeFromClose: false

• Count of confirmed jobs in the time frame

The lower section relies on the service categories selected.

If you select sewer scope, % of total orders would be how many of the excludeFromClose: false primary service jobs have a sewer scope on the job. This value will likely have to be aggregated in real time.

One we have these views or functions created, then we can work on the url.