Statistical Anomaly Detector

---
name: statistical-anomaly-detector
description: When the user requests anomaly detection in numerical datasets using statistical methods such as mean and standard deviation thresholds (e.g., 'amount > mean + 3*std'). This skill calculates per-group statistics, identifies outliers, and flags abnormal records. It is triggered by keywords like 'anomaly detection', 'outlier identification', 'statistical threshold', 'abnormal transactions', or any request involving standard deviation-based filtering in financial or transactional data.
---
# Instructions

## Core Objective
Detect anomalous records in a dataset by applying a statistical threshold rule (e.g., `value > mean + N * standard_deviation`) on a per-group basis. Output the flagged anomalies, typically to a structured file like an Excel spreadsheet.

## Trigger Keywords & Phrases
- "anomaly detection"
- "outlier identification"
- "statistical threshold"
- "abnormal transactions/records"
- "flag transactions where amount > mean + 3*std"
- "find outliers using standard deviation"
- "mark abnormal data points"

## Primary Workflow
Follow this sequence when the skill is triggered:

1.  **Understand the Request & Locate Inputs**
    *   Clarify the target dataset (e.g., BigQuery table, CSV file), the numerical column for analysis (e.g., `amount`), and the grouping column (e.g., `client_id`).
    *   Identify the threshold rule (e.g., `mean + 3 * std`). The default is `mean + 3 * std` unless specified otherwise.
    *   Locate the output destination (e.g., an Excel file path and target sheet).

2.  **Inspect the Output Template**
    *   Examine the structure of the target output file (e.g., using `excel-get_workbook_metadata`).
    *   Read a sample of the existing data to understand the required column headers and format (e.g., using `excel-read_data_from_excel`).
    *   **Remove any placeholder or sample data** from the output file before writing new results.

3.  **Extract Data & Calculate Statistics**
    *   Query or load the source data, filtering for the relevant records (e.g., by year, by group).
    *   Use a single, efficient query/calculation to:
        *   Compute the mean (`AVG`) and standard deviation (`STDDEV`) for the numerical column, grouped by the specified group column.
        *   Join these statistics back to the original records.
        *   Apply the threshold rule to flag anomalies (e.g., `WHERE numerical_column > (mean + N * std)`).
        *   **Sort the final results** as requested (commonly by a record ID).

4.  **Format and Write Results**
    *   Format the anomaly records (client_id, transaction_id, timestamp, etc.) to match the output template's column order and data types.
    *   Write the formatted records to the output file, starting from the first data row after the headers.
    *   Preserve all existing formatting and sheet structure.

5.  **Verify and Summarize**
    *   Read back a portion of the updated file to confirm successful writing.
    *   Provide a concise summary to the user: state the number of anomalies found, the groups analyzed, and the output file location.

## Key Considerations
*   **Data Volume:** For large datasets, ensure queries are efficient and use appropriate limits or batch processing.
*   **Output Handling:** Always check the output file's structure before writing. Do not overwrite unintended sheets or data.
*   **Clarity:** If the user's request is ambiguous (e.g., missing group column or threshold value), ask for clarification before proceeding.
*   **Tool Selection:** Prefer using direct database/query tools (e.g., `google-cloud-bigquery_run_query`) for statistical calculations over manual Python processing for efficiency and accuracy.

## Common Pitfalls to Avoid
*   Forgetting to clear sample data from the output template.
*   Incorrectly formatting timestamps or numerical values when writing to Excel.
*   Writing results in an unsorted order when sorting is explicitly requested.
*   Making multiple unnecessary queries; combine data extraction and statistical calculation into one operation where possible.