Как рассчитать балл аномалии на базе средних значений в метриках?

Question

[blabs]

У нас часто в игре бывает кейс, когда есть аномалия, где игроки получают награды в объеме, которое по идеи не соответствует среднему показателю активности в игре. Например, в игру зашло 1000 пользователей, а выдано 50000 наград, а за предыдущий период в игру зашло 5000 пользователей, но было выдано только 3000 наград. Соответственно есть аномалия в выдаче наград, при этом другие метрики находятся в пределах средних значений

Как можно описать формулу в google sheets или хотя бы логику для определения балла аномалии?

На скрине ниже привел пример метрик, а справа указао в колонке V аномалию

Answer 1

[blabs]

Для определения аномалий по каждому параметру можно использовать стандартное отклонение (standard deviation) и среднее значение (mean) для каждого параметра.

1. Рассчитать среднее значение и стандартное отклонение для каждого параметра.
2. Определить границы, за которыми значения считаются аномальными. Например, можно выбрать границу в 3 стандартных отклонениях от среднего значения. То есть, если значение параметра отличается от среднего более чем на 3 стандартных отклонения, то оно считается аномальным.
3. Рассчитать балл аномалии для каждого параметра, используя формулу:
- Если значение параметра находится в пределах границы, то балл аномалии равен 0.
- Если значение параметра выше границы, то балл аномалии равен разнице между значением параметра и верхней границей, деленной на стандартное отклонение.
- Если значение параметра ниже границы, то балл аномалии равен разнице между значением параметра и нижней границей, деленной на стандартное отклонение.
4. Суммировать баллы аномалии для всех параметров. Если сумма баллов аномалии превышает определенный порог, то это может указывать на наличие аномалий в данных.

Пример:
Для параметра 1, среднее значение = 74.8, стандартное отклонение = 54.2. Граница аномалий = 74.8 + 3*54.2 = 237.4. Если значение параметра равно 300, то балл аномалии = (300-237.4)/54.2 = 1.15.

Для всех параметров можно рассчитать баллы аномалии и суммировать их для получения общего балла аномалии. Если общий балл аномалии превышает определенный порог, то это может указывать на наличие аномалий в данных.

Стандартное отклонение для параметра можно рассчитать с помощью функции STDEV в Excel или Google Sheets. Нельзя сказать, что стандартное отклонение равно 50

Функция STDEV используется для расчета стандартного отклонения выборки данных. Она вычисляет среднее значение выборки, затем находит разницу между каждым значением выборки и средним значением, возводит эту разницу в квадрат, суммирует все полученные значения и делит на количество элементов в выборке минус один. Затем извлекается квадратный корень из полученного значения, что и дает стандартное отклонение. Формула для расчета стандартного отклонения выглядит так:

STDEV = SQRT(SUM((X - X_mean)^2) / (n - 1))

где X - значение каждого элемента выборки, X_mean - среднее значение выборки, n - количество элементов в выборке.

=IF(D4<=$P4+$A$2*$O10,0,IF(D4>$P4+$A$2*$O10,(D4-$P4-$A$2*$O10)/$O10,IF(D4<$P4-1*$O10,($P4-$A$2*$O10-D4)/$O10)))