Compound Attributes

This article describes how to implement attribute with compound data type.

Imagine use-case with flights in CDP. Flight can have multiple attributes like Origin city, Destination city, Date of flight and Price.

With regular data types like string, boolean, date, datetime, int or float, we are able to create separate attributes like:

List of destination cities - London, Prague, …

Last destination city - London

Total revenue from all flights in USD - 1024

Dates of all flights - 2018-12-01, 2019-01-01, …

However, using separate attributes one cannot build a segment containing “all customers who flew to London in December 2018”, because we do not know the which date of flight belongs to which destination. That is why we need attributes of compound data type.

The goal is to create 1 attribute with origin, destination, date and price as sub-attributes (dimensions) of the attribute.

This is done by creating a new, independent, isolated attribute with compound data type (sub-attributes can have any of standard scalar data types) in attributes table. Then, calculating values for the attribute.

In attributes table create a new attribute (row). Fill in all standard values like ID, name, description, all flags, etc. Then, for data type, fill compound like this:

compound([["origin","Origin","string"],["destination","Destination","string"],["date","Date","date"],["price","Price","float"]])

To see, what is going on, let’s break it down with some whitespace:

compound(
	[
		["origin","Origin","string"],
		["destination","Destination","string"],
		["date","Date","date"],
		["price","Price","float"]
	]
)

As you can see, the compound() is the name of the data type. Inside parentheses there is a JSON array or arrays. The outer array is array of sub-attributes. Each nested array is one sub-attribute.

The meaning of elements in nested array is based on order. First value is ID of sub-attribute (needs to be unique on sub-attribute level - no 2 sub-attributes in one attribute can have same ID). Second value is a name of sub-attribute (shown in UI). Third value is data type of the sub-attribute, which can be any attribute scalar type: string, date, datetime, boolean, int or float. Sub-attribute can not be of type compound (no nested compound attributes). We are done with attribute definition.

The value for such attribute will look like this:

["Prague","London","2018-12-01","128"]

The value is, again, JSON array of 4 elements. First element is Origin, second Destination, third Date and fourth Price. As you can see - the values follow order of the sub-attributes defined in the attributes table’s column data_type. Note that all elements in the array must have double-quotes - be strings. The array must be homogenous.

There are multiple ways on how to calculate this attribute, here is one suggestion on how to do it.

SELECT
	customer_entity_id,
	'attribute_id' as "attribute_id",
	json_build_array(
		"origin"::text, 
		"destination"::text, 
		"date"::text, 
		"price"::text
	) as "value"
FROM (
	SELECT
		"customer_entity_id",
		payload->>'origin_city' as "origin",
		payload->>'destination_city' as "destination",
		payload->>'flight_date' as "date",
		payload->>'flight_price' as "price"
	FROM
		"public"."customer_events"
	WHERE
		"source_id"='flights_system'
		 AND "version" IN ('1-0-0')
		 AND "type" IN ('flight')
		 AND "customer_entity_id" IS NOT null
	) AS prep

The whole idea behind this query is that one takes values of interest from the customer_events and combine it into the JSON array using json_build_array function.