Appuri Documentation

Welcome to the Appuri Documentation. You'll find comprehensive guides and instructions to help you start working with Appuri as quickly as possible, as well as support if you get stuck.

Documentation

Calculated Columns

Calculated columns allow new entity properties to be dynamically calculated based on events, event properties and user properties available in Appuri. The historic values for properties can also be used. Events and properties brought in from different data sources can be combined within a single calculated column.

To create a new calculated column, from the entity view, open the list of columns on the top right, then click new.

To modify a previously created calculated column, all normal and calculated columns will appear in this list, and calculated columns will have a small gear next to them to allow them to be renamed, modified, or deleted.

Calculated Column Basics

First, Calculated Columns can be calculated from existing Properties (such as _first_event_at or cancellation_score), Events (such as user.purchasecomplete or user.login), or Event Properties (user.purchasecomplete.amount or user.login.timestamp).

Calculated Columns allows per-user values to be calculated from one or more sources of data. The columns will be automatically updated when new data enters the system. Additionally, Calculated Columns can combine two or more events, or two or more user properties, or a mix of user properties and user events.

User Properties

If you are using a user property, it will by default use the current value of the property. You can also use the user property in a function to use the historic values as well, such as Maximum or Count which will apply to the current and historic user properties. If you are basing a calculated column off of events or event properties, they must be used in a function in order to specify which event you wish to use.

User Events

If you are using an event or event property you must use it in a function to specify which event of the user to use. For example, to use the most recent event, you can use latest(user.purchasecomplete.amount) which will specify the most recent event.

Examples

If you reference a user property on its on, it will use the current value.

monthly_subscription_rate * 12 

If you reference a user property in some of the available functions, it will examine the complete historical values for each user. Thus:

max(payment_amount)

Will return the single largest historic value for this property.

Learn more about event types and data relationships

For more information on event and property schema, the following articles may be a good starting point.

Data Types

Currently, Calculated Columns relies on the data type stored in SQL of the parameters and events you’re using in the equation. Thus, if you have an integer, and combine it with a decimal, the result will be a decimal. However, if you have an integer and divide it, it will remain an integer and there will be no decimal and any remainder will be rounded to the nearest integer. For more details, see the following table

Data type(s) inputted
Data type outputted

Integer + Decimal

Decimal

Integer / Integer

Integer (rounded if needed)

Boolean evaluation (ex. ‘Contains’ function)

Boolean

Date + 2

A date 2 days later

Functions

The following functions are supported in the Calculated Columns editor.

Function Name
Parameter Types
Parameters
Return Type
Return Value

Sum

Integer or Decimal

User Property, Event Property, or Event

Integer or Ddecimal

Sum of historic user property values or sum of all specified event property values.

Average

Integer or Decimal

User Property or Event Property

Integer or Decimal

Average of historic user property value or the average of all the specified event property values.

Min/Max

Integer, Decimal, date-time string

User Property or Event Property

Integer, decimal, date time string

min/max of historic user property values or the min/max of all specified event property values

Count

Any

User Property, Event Property, or Event

Integer

The total number of distinct, historical vlaues of the user property, or the total number of occurences of the event property

getdate

None

None

date-time string †

Present date-time †

datediff

3 parameters: String, date-string, date-string

date units such as "days" "months" or "years" in double quotes, startdate, enddate†

date-time string †

date-time †

contains

2 parameters: String to search within, string to search for

User property, Search phrase

boolean (true/false)

Whether the property contains the specified search phrase (true/fase)

first

Any Event

Any Event

date-time string †

First time a given user has sent the specified event.

last

Any Event

Any Event

date-time string †

Last time a given user has sent the specified event.

ifelse

3 parameters: evaluation, value if true value if false

User Property or Event Property

Returns same type as output source

Value if true or value if false

trailing

3 parameters: event to be used, number of units, and time increment to be used.

Any Event, any positive integer, and units such as "days" "months" or "years" in double quotes.

Must be used in another function (such as count, sum, max, etc)

Limits function it is used in to specific timerange.

where

2 parameters: event property, event property

event property to act on, event property to check

Must be used in another function (such as count, sum, max, etc)

Limits function it is used in to specific event properties.

most_common

Any

Any Event Property

Same as source

Same as source

† Dates and datetime expanded:
Appuri supports ISO8601 as our standard for dates and datetimes. Timestamps in Appuri are recorded in UTC. Thus, valid formats appear as such:

Date or Datetime
Examples for use in Calculated Columns

January 3rd, 2017

"2017-01-03"

January 3rd, 2017 at 2:15pm and 33 seconds UTC

"2016-01-03T14:15:33Z"

To use this date or datetime example in a calculated column, use them as a string by wrapping them in double quotes. For more details, see ISO 8601
For datediff function specifically:

  • datepart: The units of time (year, quarter, month, dayofyear, day, week) in double quotes.
  • startdate: A string value that can be resolved to a time, date, or date-time.
  • enddate: See startdate

In addition to functions, boolean evaluations and simple math can be used in Calculated columns such as:

Boolean evaluations
Boolean evaluations will return true or false.

gold_coins * 10 + silver_coins > 1000

Supported operations and their symbols

Operation / Name
Symbol(s)

Addition

+

Subtraction

-

Multiplication

*

Division

/

Parenthesis

( )

Specify string with double quotes

"string goes here"

Greater than

>

Less than

<

Greater than or equal to

>=

Less than or equal to

<=

Equal

=

Not equal to

!=

Examples

Determine a customer's lifetime value (LTV)

 sum(mobile.itunes_purchase.amount) + sum(mobile.google_purchase.amt) + sum(web.amzn_purchase.money)

Result: Total lifetime value for each user is recorded as a new user property.

Get the average balance of credits, balance, or currency

avg(virtual_credit_balance)

Result: New user property with their average balance is recorded.

Evaluate two or more user or event properties with elseif

ifelse(count(user.transaction_complete) + count(ad_watched) > 0, "monetized", "unmonetized") 

Result: Players who have never purchased and never watched an ad are labeled 'unmonetized' while players who have done either are labeled 'monetized'.
Note: if using ifelse to evaluate an event, it must be paired with another function in order to specify which event to examine. In this example count is used to aggregate events before the ifelse evaluation.

Calculate a function but limit to a specific timeframe

sum(trailing(user.purchase_complete.amount, 30, "days")) 

Result: First, trailing() will limit it to a set of events within the given timerange, in this case the last 30 days. Next, sum() will count the number of unique events, by timestamp.

For unit of time, singular and plural are interchangeable

Valid options are "day" "days" "month" "months" "year" and "years" - The singular and plural are treated identically for the purposes of this forumula.

Calculate a function but limit to specific event property

sum(where(user.purchase_complete.amount, user.purchase_complete.type = "large_package_premium")) 

Result: First, where() will return a set of events (user.purchase.amount) but only where another property of the same event is equal to a specified value, in this case a string with a value of "large_package_premium". Then, sum will sum the value of the first event and property. In this case, calcuating the total spent on a specific package.

Check if an email is from gmail

contains(email, "@gmail.com")

Result: User property is set for every user to “true” or “false” depending if each users email contains the string.

Get the total number of a specific event
This can be useful to determine how many times a given user has used a specific feature or done an action.

count(mobile.itunes_purchase.timestamp)

Result: Each user would have a new property with the total number of unique (by timestamp) purchases from the iTunes store.

Get the last time an event occurred

max(mobile.itunes_purchase.timestamp)

Result: If a user has ever made a purchase, it will return the time of their latest purchase. In the Segments screen, you can then segment on users who have recently purchased, or have not recently purchased for example.

Difference in time between two events

datediff("days",_first_event_at,_last_event_at)

Result: The number of days between the timestamp of the first property, and the timestamp for the last property. If "days" was exchanged for "weeks" or "months" the result would be the number of weeks or months between the two timestamps.

Most common browser for a given user

most_common(trailing(user.login.browser, 30, "days"))

Result: The value will be the most common browser each user has used in the past 30 days. If you used max() or first() their browser may have changed more recently. The trailing function keeps the value up to date if a long term user changes their browser.

Calculated Columns