Derived property formula examples

이 페이지는 아직 귀하의 언어로 번역되지 않았습니다. 현재 작업 중이므로 곧 다시 확인해 주십시오.

Calculate the age of a customer

plaintext
CEIL(
    DIVIDE(
        MINUS(
            PROPERTY('server_upload_time', 'amplitude_user'),
            TIME_TO_LONG(PROPERTY('Created At', 'user'))
        ),
        86400000
    )
)

Use this formula when you track a user property with a date-time data type and want to calculate the age of that user (how long that user has existed in your system) since the event that set this user property.

Calculate days since registration

Use Event Time with a date or datetime property to calculate how many days passed between an event and a fixed date, such as a user's registration date. In the derived property builder, select Event Time from the Insert Property dropdown, then build a formula like this:

plaintext
DIVIDE(
    MINUS(
        PROPERTY('Event Time', 'event'),
        TIME_TO_LONG(PROPERTY('registration_date', 'user'))
    ),
    86400000
)

This example assumes registration_date is a datetime string (for example, "2023-08-18 17:37:39"). Inside a formula, Event Time acts as a long in epoch milliseconds. The formula converts the registration date to a long with TIME_TO_LONG, subtracts it from Event Time, and divides by 86400000, the number of milliseconds in one day.

Get the difference between two dates

plaintext
DIVIDE(  
  MINUS(  
    DATE_TO_LONG(  
        PROPERTY(  
            'start_date', 'user'  
        )  
    ),  
    DATE_TO_LONG(  
        PROPERTY(  
            'end_date', 'user'  
        )  
    )  
  ),  
  86400000   
)

Sample output.

In the derived property above, Amplitude converts the properties end_date and start_date into UNIX timestamps to calculate the difference between them. Amplitude then divides that result by 86400000, the number of milliseconds in one day.

This output is a double type (for example, 2.0).

Output a standardized date format

plaintext
IF(  
    DATE_TIME_FORMATTER(  
        PROPERTY(  
            'publishDate',  
            'event'  
        ),  
        "yyyy-MM-dd'T'HH:mm:ssX",  
        'yyyy-MM-dd'  
    ) 
    contains '-',  
    DATE_TIME_FORMATTER(  
        PROPERTY(  
            'publishDate',  
            'event'  
        ),  
        "yyyy-MM-dd'T'HH:mm:ssX",  
        'yyyy-MM-dd'  
    ),  
    IF(  
        DATE_TIME_FORMATTER(  
            PROPERTY(  
                'publishDate',  
                'event'  
            ),  
            "yyyy-MM-dd HH:mm:ss",  
            'yyyy-MM-dd'  
        ) 
        contains '-',  
        DATE_TIME_FORMATTER(  
            PROPERTY(  
                'publishDate',  
                'event'  
            ),  
            "yyyy-MM-dd HH:mm:ss",  
            'yyyy-MM-dd'  
        ),  
        DATE_TIME_FORMATTER(  
            PROPERTY(  
                'publishDate',  
                'event'  
            ),  
            "yyyy-MM-dd",  
            'yyyy-MM-dd'  
        )  
    )  
)

Sample output.

One way to format dates to Standard Date Format is to use a series of IF statements. Make sure the higher specificity conditional comes first. Replace the $<number> here with the actual properties.

Get the month and year a customer signed up

plaintext
CONCAT(  
    REGEXEXTRACT(  
        PROPERTY(  
            'start_date',  
            'user'  
        ),  
        'dddd-dd'  
    ), "-01"  
)

In this example, the derived property pulls the sign-up month and year from a property that contains a more detailed value, then appends "-01" to set it to the beginning of the month. Use REGEXEXTRACT() to pull the year and month from the value, and use CONCAT() to append "-01". Replace the $<number> here with the actual properties.

Replace existing property values

plaintext
IF(
    OR(
        REGEXEXTRACT(
            PROPERTY(
                "package",
                "event"
                ),
            'Casual'
        ) =='Casual',
        REGEXEXTRACT(
            PROPERTY(
                "package",
                "event"
            ),
            '1 Job Posting'
        )=='1 Job Posting',
        REGEXEXTRACT(
            PROPERTY(
                "package",
                "event"
            ),
            '1 Basic'
        )=='1 Basic'
    ),
    'Casual',
    'False'
)

Sample output.

To replace multiple property values, use REGEXEXTRACT() to pull the string from the property, and use OR statements inside an IF statement to check whether the value pulled from the properties contains any of the values you want to replace. In this example, if the property value matches any of the specified values, the formula replaces the value with Casual. Otherwise, the formula replaces the property with False. Replace the $<number> here with the actual properties.

Count length of an array that was accidentally ingested as a string

Convert the string to an array format by using SPLIT:

plaintext
SPLIT(  
PROPERTY('color', 'event'),  
','  
)

Sample output:

Screenshot 2023-09-28 at 14.39.54.png

Amplitude ingested the property color as the string "Red, Green, Blue". After using SPLIT, the resulting value is [Red, Green, Blue].

Use the above derived property within a new derived property that uses ITEM_COUNT.

plaintext
ITEM_COUNT(  
PROPERTY("Transform into Array", "derived")  
)

Sample output:

Screenshot 2023-09-28 at 14.42.30.png

Now the underlying data is an array. ITEM_COUNT counts the number of items that make up the array.

이 내용이 도움이 되었나요?

For AI agents: a documentation index is available at /docs/llms.txt. Append .md to any page URL for markdown, or send Accept: text/markdown.

이 페이지에서