Advanced functions
- Topics:
- Calculated Metrics
CREATED FOR:
- User
The Calculated metrics builder lets you apply statistical and mathematical functions. This article documents alphabetical list of the advanced functions and their definitions.
Access these functions by selecting Show all below
Table functions versus row functions
A table function is one where the output is the same for every row of the table. A row function is one where the output is different for every row of the table.
Where applicable and relevant, a function is annotated with the type of function: Table or Row
What does the include-zeros parameter mean?
It tells whether to include zeros in the computation. Sometimes zero means nothing, but sometimes it’s important.
For example, if you have a Revenue metric, and then add a Page Views metric to the report, there are suddenly more rows for your revenue, which are all zero. You probably don’t want that additional metric to affect any MEAN, ROW MINIMUM, QUARTILE, and more calculations that you have in the revenue column. In this case, you would check the include-zeros parameter.
An alternative scenario is that you have two metrics of interest and one has a higher average or minimum because some of the rows are zeros. In that case, you can opt not to check the parameter to include zeros.
And
Conjunction. Not equal to zero is considered to be true and equals zero is considered to be false. The output is either a 0 (false) or 1 (true).
| Argument | Description |
|---|---|
| logical_test | Requires at least one parameter, but can take any number of parameters. Any value or expression that can be evaluated to TRUE or FALSE |
Approximate Count Distinct
Returns the approximated distinct count of dimension items for the selected dimension.
| Argument | Description |
|---|---|
| dimension | The dimension for which you want to calculate the approximated distinct item count |
Example
A common use case for this function is when you want to get an approximate number of customers.
Arc Cosine
Row Returns the arccosine, or inverse of the cosine, of a metric. The arccosine is the angle whose cosine is number. The returned angle is given in radians in the range 0 (zero) to pi. If you want to convert the result from radians to degrees, multiply it by 180/PI().
| Argument | Description |
|---|---|
| metric | The cosine of the angle you want from -1 to 1 |
Arc Sine
Row Returns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine is a number. The returned angle is given in radians in the range -pi/2 to pi/2. To express the arcsine in degrees, multiply the result by 180/PI().
| Argument | Description |
|---|---|
| metric | The sine of the angle you want from -1 to 1 |
Arc Tangent
Row Returns the arctangent, or inverse tangent, of a number. The arctangent is the angle whose tangent is a number. The returned angle is given in radians in the range -pi/2 to pi/2. To express the arctangent in degrees, multiply the result by 180/PI().
| Argument | Description |
|---|---|
| metric | The tangent of the angle you want from -1 to 1 |
Cdf-T
Returns the probability that a random variable with student-t distribution with n degrees of freedom have a z-score less than col.
| Argument | Description |
|---|---|
| metric | The metric for which you would like the Cumulative Distribution Function of the student t-distribution |
| number | The degrees of freedom for the Cumulative Distribution Function of the student t-distribution |
Example
CDF-T(-∞, n) = 0
CDF-T(∞, n) = 1
CDF-T(3, 5) ? 0.99865
CDF-T(-2, 7) ? 0.0227501
CDF-T(x, ∞) ? cdf_z(x)
Cdf-Z
Returns the probability that a random variable with a normal distribution has a z-score less than col.
Examples
CDF-Z(-∞) = 0
CDF-Z(∞) = 1
CDF-Z(0) = 0.5
CDF-Z(2) ? 0.97725
CDF-Z(-3) ? 0.0013499
Ceiling
Row Returns the smallest integer not less than a given value. For example, if you want to avoid reporting currency decimals for revenue and a product has $569.34, use the formula CEILING(Revenue) to round revenue up to the nearest dollar, or $570.
Confidence
Calculate the any-time-valid confidence using the WASKR method as described in Time-uniform central limit theory and asymptotic confidence sequences.
Confidence is a probabilistic measure of how much evidence there is that a given variant is the same as the control variant. A higher confidence indicates less evidence for the assumption that control and non-control variant have equal performance.
Confidence (Lower)
Calculate the any-time-valid confidence lower using the WASKR method as described in Time-uniform central limit theory and asymptotic confidence sequences.
Confidence is a probabilistic measure of how much evidence there is that a given variant is the same as the control variant. A higher confidence indicates less evidence for the assumption that control and non-control variant have equal performance.
Confidence (Upper)
Calculate the any-time-valid confidence upper using the WASKR method as described in Time-uniform central limit theory and asymptotic confidence sequences.
Confidence is a probabilistic measure of how much evidence there is that a given variant is the same as the control variant. A higher confidence indicates less evidence for the assumption that control and non-control variant have equal performance.
Cosine
Row Returns the cosine of the given angle. If the angle is in degrees, multiply the angle by PI()/180.
Cube Root
Returns the positive cube root of a number. The cube root of a number is the value of that number raised to the power of 1/3.
Cumulative
Returns the sum of the last n elements of column x. If n > 0, sum the last n elements or x. If n < 0, sum the preceding elements.
Examples
Cumulative (Average)
Returns the average of the last n elements of column x. If n > 0, sum the last n elements or x. If n < 0, sum the preceding elements.
NOTEInstead, use CUMULATIVE(revenue)
Equal
Equal. The output is either a 0 (false) or 1 (true).
Example
Metric 1 = Metric 2
Exponential regression: Correlation coefficient
Table Exponential regression: Y = a exp(X) + b. Returns the correlation coefficient.
Exponential Regression: Predicted Y
Row Exponential regression: Y = a exp(X) + b. Returns Y.
Exponential Regression: Intercept
Table Exponential regression: Y = a exp(X) + b. Returns b.
Exponential Regression: Slope
Table Exponential regression: Y = a exp(X) + b. Returns a.
Floor
Row Returns the largest integer not greater than a given value. For example, if you want to avoid reporting currency decimals for revenue and a product has $569.34, use the formula FLOOR(Revenue) to round revenue down to the nearest dollar, or $569.
Greater Than
The output is either a 0 (false) or 1 (true).
Example
Metric 1 > Metric 2
Greater Than or Equal
Greater than or equal. The output is either a 0 (false) or 1 (true).
Example
Metric 1 >= Metric 2
Hyperbolic Cosine
Row Returns the hyperbolic cosine of a number.
Hyperbolic Sine
Row Returns the hyperbolic sine of a number.
Hyperbolic Tangent
Row Returns the hyperbolic tangent of a number.
If
Row If the value of the condition parameter is non-zero (true), the result is the value of the value_if_true parameter. Otherwise, it is the value of the value_if_false parameter.
Less Than
The output is either a 0 (false) or 1 (true).
Example
Metric 1 < Metric 2
Less Than or Equal
Less than or equal. The output is either a 0 (false) or 1 (true).
Example
Metric 1 <= Metric 2
Lift (#lift)
Linear Regression: Correlation coefficient
Table Linear regression: Y = a X + b. Returns the correlation coefficient.
Linear Regression: Intercept
Table Linear regression: Y = a X + b. Returns b.
Linear Regression: Predicted Y
Row Linear regression: Y = a X + b. Returns Y.
Linear Regression: Slope
Table Linear regression: Y = a X + b. Returns a.
Log Base 10
Row Returns the base-10 logarithm of a number.
Log Regression: Correlation coefficient
Table Log regression: Y = a ln(X) + b. Returns the correlation coefficient.
Log Regression: Intercept
Table Log regression: Y = a ln(X) + b. Returns b.
Log Regression: Predicted Y
Row Log regression: Y = a ln(X) + b. Returns Y.
Log Regression: Slope
Table Log regression: Y = a ln(X) + b. Returns a.
Natural Log
Returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904). LN is the inverse of the EXP function.
Not
Negation as a boolean. The output is either 0 (false) or 1 (true).
Not Equal
Not Equal. The output is either a 0 (false) or 1 (true).
Example
Metric 1 != Metric 2
Or
Row Disjunction. Not equal to zero is considered to be true and equals zero is considered to be false. The output is either a 0 (false) or 1 (true).
NOTEPi
Returns Pi: 3.14159…
Power Regression: Correlation coefficient
Table Power regression: Y = b X ^ a. Returns the correlation coefficient.
Power Regression: Intercept
Table Power regression: Y = b X ^ a. Returns b.
Power Regression: Predicted Y
Row Power regression: Y = b X ^ a. Returns Y.
Power Regression: Slope
Table Power regression: Y = b X ^ a. Returns a.
Quadratic Regression: Correlation coefficient
Table Quadratic regression: Y = (a + bX) ^ 2, Returns the correlation coefficient.
Quadratic Regression: Intercept
Table Quadratic regression: Y = (a + bX) ^ 2, Returns a.
Quadratic Regression: Predicted Y
Row Quadratic regression: Y = (a + bX) ^ 2, Returns Y.
Quadratic Regression: Slope
Table Quadratic regression: Y = (a + bX) ^ 2, Returns b.
Reciprocal Regression: Correlation coefficient
Table Reciprocal regression: Y = a + b X ^ -1. Returns the correlation coefficient.
Reciprocal Regression: Intercept
Table Reciprocal regression: Y = a + b X ^ -1. Returns a.
Reciprocal Regression: Predicted Y
Row Reciprocal regression: Y = a + b X ^ -1. Returns Y.
Reciprocal Regression: Slope
Table Reciprocal regression: Y = a + b X ^ -1. Returns b.
Sine
Row Returns the sine of the given angle. If the angle is in degrees, multiply the angle by PI()/180.
T-Score
The deviation from the MEAN, divided by the standard deviation. Alias for Z-Score.
T-Test
Performs an m-tailed t-test with t-score of x and n degrees of freedom.
Details
The signature is T-TEST(metric, degrees, tails). Underneath, it simply calls m
- m is the number of tails.
- n is the degrees of freedom, and should be a constant number for the whole report, that is, not changing on a row by row basis.
- x is the T-test statistic, and would often be a formula (for example, Z-SCORE) based on a metric and is evaluated on every row.
The return value is the probability of seeing the test statistic x given the degrees of freedom and number of tails.
Examples
-
Use the function to find outliers:
T-TEST(Z-SCORE(bouncerate), ROW COUNT - 1, 2) -
Combine the function with IF to ignore very high or low bounce rates, and count sessions on everything else:
IF(T-TEST(Z-SCORE(bouncerate), ROW COUNT - 1, 2) < 0.01, 0, sessions )
Tangent
Returns the tangent of the given angle. If the angle is in degrees, multiply the angle by PI()/180.
Z-Score
Row The deviation from the mean divided by the standard deviation.
A Z-score of 0 (zero) implies the score is the same as the mean. A Z-score can be positive or negative, indicating whether it is above or below the mean and by how many standard deviations.
The equation for Z-score is:
Where x is the raw score, μ is the mean of the population, and σ is the standard deviation of the population.
NOTEZ-Test
Performs an n-tailed z-test with a z-score of x.
NOTE