Tool Usage Metrics

The tool_usage_metrics mart presents metrics regarding the use of various LTI tools. The mart is refreshed hourly. The LTI tools included in the mart are any tools that are configured to send Caliper events to the event_store.expanded table. The metrics defined for each tool are the total number of events, the earliest event time, and the latest event time for different time frames. The metrics are defined based on the current hour, and the time frames included are the:

• previous hour

• previous 6 hours

• previous 12 hours

• previous day

• previous week

• previous month

• previous year

• all time (2021 to the current date)

The mart also includes metrics that can be used to check if there are any current tool outages, such as the amount of time since an event has occurred for the tool and flags that indicate whether the tool is considered to have low events.

BQ Prod Dataset Locations

• mart_general

Interactive Mart Dependency Diagram

The following visualization shows the construction of this data mart defined in the UDP marts repository. More information on the repository and diagram can be found on this marts page.

Click here to open the interactive chart.

mart_general.tool_usage_metrics

Schema

mart/general/tool_usage_metrics

Field name	Type	Description
ed_app_id	STRING	The EdApp ID of the tool.
run_hour	DATETIME	The date and hour of when the mart was last updated.
total_events	INTEGER	The total number of events associated with the tool.
total_events_1hour	INTEGER	The total number of events for the tool in the previous hour.
total_events_6hour	INTEGER	The total number of events for the tool in the previous 6 hours.
total_events_12hour	INTEGER	The total number of events for the tool in the previous 12 hours.
total_events_day	INTEGER	The total number of events for the tool over the past day.
total_events_week	INTEGER	The total number of events for the tool over the past week.
total_events_month	INTEGER	The total number of events for the tool over the past month.
total_events_year	INTEGER	The total number of events for the tool over the past year.
earliest_event_time	DATETIME	The overall earliest event time associated with the tool.
latest_event_time	DATETIME	The overall latest event time associated with the tool.
earliest_event_time_1hour	DATETIME	The earliest event time for the tool in the previous hour.
latest_event_time_1hour	DATETIME	The latest event time for the tool in the previous hour.
earliest_event_time_6hour	DATETIME	The earliest event time for the tool in the previous 6 hours.
latest_event_time_6hour	DATETIME	The latest event time for the tool in the previous 6 hours.
earliest_event_time_12hour	DATETIME	The earliest event time for the tool in the previous 12 hours.
latest_event_time_12_hour	DATETIME	The latest event time for the tool in the previous 6 hours.
earliest_event_time_day	DATETIME	The earliest event time for the tool over the past day.
latest_event_time_day	DATETIME	The latest event time for the tool over the past day.
earliest_event_time_week	DATETIME	The earliest event time for the tool over the past week.
latest_event_time_week	DATETIME	The latest event time for the tool over the past week.
earliest_event_time_month	DATETIME	The earliest event time for the tool over the past month.
latest_event_time_month	DATETIME	The latest event time for the tool over the past month.
earliest_event_time_year	DATETIME	The earliest event time for the tool over the past year.
latest_event_time_year	DATETIME	The latest event time for the tool over the past year.
num_seconds_since_latest_event	INTEGER	The number of seconds since the latest event for the tool.
num_minutes_since_latest_event	INTEGER	The number of minutes since the latest event for the tool.
num_hours_since_latest_event	INTEGER	The number of hours since the latest event for the tool.
num_days_since_latest_event	INTEGER	The number of days since the latest event for the tool.
hourly_low_events_threshold	INTEGER	The number of events for an hour under which a tool is considered to have low hourly events and the low_hourly_events_flag is raised.
six_hr_low_events_threshold	INTEGER	The number of events for 6 hours under which a tool is considered to have low 6 hour events and the low_six_hr_events_flag is raised.
twelve_hr_low_events_threshold	INTEGER	The number of events for 12 hours under which a tool is considered to have low 12 hour events, and the low_twelve_hr_events_flag is raised.
daily_low_events_threshold	INTEGER	The number of events for a day under which a tool is considered to have low daily events, and the low_daily_events_flag is raised.
low_hourly_events_flag	INTEGER	Flag indicating low event volume for the previous hour of events for the tool.
low_six_hr_events_flag	INTEGER	Flag indicating low event volume for the previous 6 hours of events for the tool.
low_twelve_hr_events_flag	INTEGER	Flag indicating low event volume for the previous 12 hours of events for the tool.
low_daily_events_flag	INTEGER	Flag indicating low event volume for the previous day of events for the tool.
low_events_flag	INTEGER	Flag indicating low event volume for the tool overall.

Total Events

The total_events_x fields present the total number of events associated with a given tool for a certain time frame. Each metric is defined by counting the number of events for the tool in the given time frame. The timeframe is defined using the DATETIME_SUB function in BigQuery.

Earliest/Latest Event Time

The earliest_event_time_x and latest_event_time_x fields respectively present the earliest and latest event times associated with a tool for a certain time frame. The earliest_event_time_x field is defined as the minimum event time for the tool over the given time frame, and the latest_event_time_x field is defined as the maximum event time for the tool over the given time frame.

Time since Latest Event

The num_x_since_latest_event fields define the amount of time since the latest event occurred for the tool for different units of time. The amount of time between the latest event for the tool and the run_hour of the mart is computed using the DATETIME_DIFF function in BigQuery.

Low Events Flags

The low_x_events_flag fields can help indicate if a tool outage may be occurring. For each tool, we define low events flags for 4 different time frames: the past hour, the past 6 hours, the past 12 hours, and the past day. The final low_events_flag coalesces all of these flags to present an overall flag for the tool.

The logic for determining whether a tool has low events for a given time frame depends on the distribution of that tool's events for that time frame. To define this distribution, we first group the number of events that could occur for the tool within a time frame as:

• 0 events

• 1-100 events

• 101-500 events

• 501-1,000 events

• 1,001-5,000 events

• 5,001-10,000 events

• 10,001-50,000 events

• 50,001-100,000 events

• 100,001-500,000 events

• 500,001-1,000,000 events

• ≥ 1,000,000 events

Using these groups, the distribution of the number of events in each time frame is then defined for the tool. Specifically, we define the percentage of occurrences for each of these groups, based on all events for the tool. This allows us to define the probability of a tool having a certain number of events occur in a time frame.

The next step is to define the tool's low events threshold for different time frames based on these distributions. The threshold is derived from the smallest number of events group that make up more than 1% of occurrences for the tool. The threshold is the minimum number of events for the chosen group. For example, assume that there was a .5% chance that a tool has 0 events in any given hour, a 7% chance that a tool has 1-100 events in any given hour, a 10% chance that a tool has 101-500 events in any given hour, and so on. The smallest group with a probability greater than 1% is the '1-100' group. The threshold is then defined as 1 event.

The threshold is used to define whether a tool has low events for the given time frame. The current number of events for the time frame for the tool is compared to the threshold. In terms of the fields in the mart, the total_events_x value is compared to the x_low_events_threshold value, with x representing the time frame of interest. If the number of events is less than the threshold, the tool is considered to have low events, and the low_x_events_flag takes the value of 1. If the threshold is 0 events and the number of events is 0, then the flag takes a null value, as we cannot determine if the tool has low events based on the threshold. Otherwise, the tool is not considered to have low events, and the flag takes a value of 0. This logic is applied for all the flags for the 4 possible time frames.

Low Events Flag Value	Requirements
1	The threshold for the given time frame is greater than 0 events and the volume of events we see for the time frame is lower than the threshold.
0	The threshold for the given time frame is greater than 0 events and the volume of events is greater than or equal to the threshold. OR The threshold for the given time frame is 0 events and the volume of events is greater than the threshold.
null	The threshold for the given time frame is 0 events and the volume of events is 0.

The overall low_events_flag is defined as a coalescing of the flags for the different time frames:

  COALESCE(low_hourly_events_flag,low_six_hr_events_flag,low_twelve_hr_events_flag,low_daily_events_flag,0)

The purpose of this flag is to take into account all the possible flags based on different time frames. By coalescing the different flags, we establish a preference for the smaller time frames. If the hourly flag has either a 1 or 0 defined, meaning that the tool is defined as having low events or not having low events, the overall flag reflects that definition. It is only when the hourly flag is null, meaning that there was 0 events for the hour and the threshold for the hour is also 0 events, that the overall flag considers the other flags. If all of the flags are null, the overall flag defaults to 0, and the tool is defined as not having low events.