Automate SQL Alerts for Rolling Average/Week-on-Week Average

What is a Week-on-week average?

A "Week-on-week average" is an analytical calculation that involves averaging data over a specific week and comparing it to the average of the previous week. This is often used to assess and analyze trends or changes that occur over a one-week time frame.

What is a Moving/Rolling Average?

A moving average, commonly referred to as the rolling average,  is a statistical calculation used to analyze and smooth out fluctuations in data over a specific period. It is used in time series analysis to identify trends, patterns, and underlying movements in a dataset. The commonly used moving average is the Simple Moving Average(SMA).

‍

Use cases where moving averages and week-on-week averages help!

‍

Inventory Management

‍A logistics company must manage its inventory efficiently to meet customer demand while avoiding overstock or stockouts. Implementing a moving average for demand forecasting can help the logistics company achieve better inventory management.

WITH MovingAverageCTE AS (
    SELECT
        warehouse_id,
        date,
        inventory_count,
        AVG(inventory_count) OVER (PARTITION BY warehouse_id ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average
    FROM
        your_inventory_table
)
SELECT
    warehouse_id,
    date,
    inventory_count,
    moving_average
FROM
    MovingAverageCTE
WHERE
    moving_average < 20;

‍

Last Mile Delivery

‍A last-mile delivery company wants to assess the performance of its delivery operations and identify trends in key metrics, such as delivery times, successful deliveries, and customer satisfaction. They need a tool to analyze these metrics on a week-on-week basis to understand if there are any improvements or deteriorations over time.

WITH WeeklyAverageCTE AS (
    SELECT
        logistics_company_id,
        DATE_TRUNC('week', delivery_date) AS week_start,
        AVG(delivery_count) OVER (PARTITION BY logistics_company_id, DATE_TRUNC('week', delivery_date) ORDER BY delivery_date) AS weekly_average
    FROM
        your_deliveries_table
)
SELECT
    logistics_company_id,
    week_start,
    AVG(weekly_average) OVER (PARTITION BY logistics_company_id ORDER BY week_start) AS overall_weekly_average
FROM
    WeeklyAverageCTE
WHERE
    overall_weekly_average < 50;

‍

Customer Support

Customer Support teams can assess the week-on-week averages of their response times, resolution rates, and customer satisfaction scores to improve service quality.

WITH ResolutionRateCTE AS (
    SELECT
        support_team_id,
        date,
        resolution_rate,
        AVG(resolution_rate) OVER (PARTITION BY support_team_id ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average
    FROM
        your_support_metrics_table
)
SELECT
    support_team_id,
    date,
    resolution_rate,
    moving_average
FROM
    ResolutionRateCTE
WHERE
    moving_average < 80;

‍

User Engagement

‍An EdTech company wants to understand and improve user engagement on its online learning platform. The company aims to identify trends in user activity over time and can use weekly averages to identify patterns in user behavior.

WITH WeeklyAverageCTE AS (
    SELECT
        ed_tech_team_id,
        DATE_TRUNC('week', attendance_date) AS week_start,
        AVG(attendance_rate) OVER (PARTITION BY ed_tech_team_id, DATE_TRUNC('week', attendance_date) ORDER BY attendance_date) AS weekly_average
    FROM
        your_attendance_table
)
SELECT
    ed_tech_team_id,
    week_start,
    weekly_average
FROM
    WeeklyAverageCTE
WHERE
    weekly_average < 70;

‍

What are the challenges with manual calculations?

While manually calculating and monitoring week-on-week averages or moving averages can be insightful, there are several challenges associated with this approach:

Time-Consuming

‍Manually calculating averages, especially for large datasets, can be time-consuming. This is particularly true when dealing with a high frequency of data points.

‍

Prone to Errors

‍Manual calculations are susceptible to human errors that can compromise the accuracy of the results and, in turn, the validity of any decisions based on those results.

‍

Limited Scalability

‍Handling a large number of data points and performing calculations for an extended period can be overwhelming and impractical.

‍

Lack of Real-Time Insights

‍Manual monitoring typically introduces delays in obtaining insights. Waiting for the manual calculation of averages may result in outdated information, hindering the ability to respond quickly to emerging trends or issues.

‍

Frequency of Updates

‍Maintaining a consistent manual update and monitoring schedule can be challenging. Automated systems can provide real-time updates without human intervention.

‍

How does Locale.ai help?

Locale is an operations automation and issue-tracking solution, mainly focusing on two important steps in the operations process:

• Automating monitoring dashboards and reports and creating a list of actionable tasks to avoid manual monitoring of data, eliminating redundant work in the process.
• Create new incidents every time a new problem occurs and alert the right stakeholders on the operations team.

Using Locale, teams need to set up an alert once to ensure that their averages are constantly monitored and any deviation can be quickly notified to the right stakeholders, to take corrective measures and resolve problems within the desired and agreed-upon timelines.

‍

How to set up an alert on Locale.ai in under 5 minutes?

‍

Step 1: Connect a Data Source [Database, Google Sheets or SaaS Tools]

1.1 Connecting a Database: Locale helps connect with the major databases and data warehouses present today, and integrations are as easy as filling out a few fields and whitelisting Locale’s IP!

1.2 Connecting a Google Sheet: A recently launched feature is the ability to integrate with Google Sheets. GSheets are a major source of data in any organization and all it takes to connect is to specify the GSheet URL.

‍1.3 Connecting to a SaaS tool via API Integration: Locale also connects with SaaS applications supporting an Open API and this helps assimilate data from the various tools that businesses use on a daily basis.

‍

Step 2: Set up Alert Configurations

In this step, we set rules to monitor the data. This starts with a SQL query or adding filters. Once you do this, you’ll be able to receive the results of the query or filter at a set frequency.

Playbooks can be set to ensure faster resolution, and automatic resolution can be enabled to ensure incidents on Locale are resolved with zero manual intervention, once they move out of the queue on your data source!Dynamic stakeholder assignments are possible for larger teams. Incidents can be configured to have labels, escalations, and playbooks to ensure adherence.

‍

Step 3: Notify Stakeholders

Connect with your preferred notification streams to ensure that your stakeholders receive all updates on time! Group your notifications to ensure end users don’t get spammed and yet receive all the incidents that they need to prioritize and work on.

‍

You’re good to go! Once the alert is set, incidents will start flowing in when there are new rows identified in your datasource!

‍

Conclusion

Utilizing weekly averages provides teams with a valuable perspective on performance trends over time. When integrated with an alerting tool, this approach becomes a powerful mechanism for swiftly identifying and prioritizing key tasks, enabling teams to respond proactively to emerging challenges and optimize their workflow efficiency.