Managing Product Categories
Introduction
Thanks to the Repository tables, you can easily store your whole product catalog and link it to Interaction tables (Orders, Tickets,...). This allows you to target on products purchased by each profile.
However, you may be interested in more global trends, like grouping products together and identifying profiles who have bought items belonging to a specific product category.
Yet products rarely belong to only 1 category. For instance, a dress could belong to the categories 'Clothes' and 'Summer', while a coat could belong to the Categories 'Clothes' and 'Winter'. Assigning multiple categories to a product is precisely how to make these categories relevant.
In such cases, simple Repository tables might not be enough, as you cannot set multiple values for the 'category' of a 'product' entry.
For this reason, Actito has implemented a way to store and visualize Product Categories and to calculate aggregates among those categories.
Understanding Product Categories tables
Managing Product Categories in Actito (in targeting, conditions,...) will mainly involve 2 new tables:
- A specific Repository table to do the mapping between product categories. You will be able to populate this table to define your own categories assignations.
- A Linked Data table that will calculate aggregates based on the product categories and the interactions of the profiles of your DB. This table will be calculated automatically based on your Data Model.
Interested in using Product categories? These tables have a specific structure and require a dedicated set-up by our Integration & Data experts. We invite you to contact your account manager. Once the tables have been set up, you are able to use them in full autonomy.
Pre-requisites
To be able to use Product Categories, your data model needs to have an Interaction table (for example: Order Lines) that contains
-
a productId field (or SKU, ... The name of the field isn't important, as long as it used to store product references). This productId field can already be the link to a 'Products' Repository table. This is not required and not an issue either if it's not the case.
-
a value field that identifies the price (such as 'amount', 'unitPrice',...). The value can be assigned directly in the 'Manage tables structure' app > 'View' > 'Set as value'
In addition, the following fields can be defined but are not mandatory to set up Product Categories:
- a field representing the transaction date. By default, the technical field 'creationMoment' is used, but any
DATEfield can be be defined as 'creationTimeField' in the structure of the table and be used instead.
To define a custom DATE field as 'creationTimeField' of your Interaction table, please discuss it with your project manager.
- a field representing the quantity. If the value of your Interaction lines is expressed as 'unitPrice' rather than 'totalAmount', your table probably has a 'quantity' field. In this case, the value field will be automatically multiplied by the quantity when calculating aggregates on the monetary value.
Make sure to communicate the names of the fields corresponding to these concepts in your Interaction table when starting the set-up project.
Visualizing the tables
The mapping table
This mapping table is a Repository table used to define which categorie(s) each product belongs to.
By default, it is named 'mapping Product Categories'.
If you want to customize the name of the mapping table, please discuss it with your project manager.
It contains a 'productId' field and a 'categoryName' field, which are both non-unique (but their combination form a composite key). This means that the same productId can be repeated multiple times, allowing you to assign several categories to the same product... and to put several products in the same category.
Loading data into the mapping table
This repository table must be fed through the usual import methods: ETL or API.
Indeed, you are assigning categories to your products, so the data is in your hands. It is possible to create or update the product categories through the imports.
If you already have a 'Products' repository table, you can even set up a multi-file ETL to synchronize both tables at the same time.
Example:
| ProductId | CategoryName |
|---|---|
| 123 | Shoes |
| 123 | Winter |
| 456 | Clothes |
| 456 | Winter |
| 789 | Accessories |
The aggregate table
Once a mapping table is available for you to import data, our Integration & Data expert will be able to build an aggregate table to combine the data from the interaction table and from the products categories, allowing you to harness their full potential.
This is a Linked Data table, directly linked to the profile table. By default, it is named 'RFM Product Categories'.
It contain the following fields:
- the 'group by' field, which corresponds to the attribute by which the data is aggregated. It is typically the 'category name'.
- the 'Amount of purchases': this where the value of the purchases is aggregated, namely the total spent by a profile on a category of product during a monthly time period.
If you have an optional Quantity field in your interaction table (and your value attribute is a unitary price), this total value will be unit price amount multiplied by the quantity.
- the 'profileId': this is the technical id of the profile whose interactions are aggregated.
You can visualize all the details of the profile (such as your own keys, like their e-mail address) by clicking on the 'View profile' button.
- the 'Purchase month': this where the date of purchases are aggregated, by month.
- the 'Number of purchases': this is the frequency of the purchases, namely the number of times a profile bought a product from a category during a monthly time period.
The name of the table and of the fields can be customized. For instance, you can display 'Total value of purchases' instead of 'Amount of purchases'. Simply state your preferred copy to your project manager.
The time period is however fixed: product categories are always aggregated by month.
In the example above, we can see that, in August 2023, the profile 1004075 has purchased 1 item in the 'winter' category for a total of 66€, 2 items in the 'summer' category for a total of 50€ and 3 items in the 'clothes' category'for a total of 116€. These categories can refer to the same items, but as products can belong to different categories, each category is updated on its own.
As 'Purchase Month' is typed as a date, the value will be the first day of the month. (ex.: 01/10/2023 for October 2023).
The aggregate table is synchronized in real time with the Interaction table. For instance, when a new ticket line is imported, the corresponding Product Categories will be updated for the linked profile.
Deleting a ticket line will not automatically remove the corresponding aggregate data.
Entries in the aggregate table will be cleaned when the purchase moment is older than 2 years (730 days).
If the cleaning logic of the interaction table is different, it's also possible to manually reset the aggregate data.
This table is the main table of the aggregate model. Click on 'More' than 'View aggregate details' to visualize the links between all the tables.
- The source table is the pre-requisite Interaction table from which the transactions are aggregated.
- The mapping table is the Repository table mentioned above.
The 'groupBy' table
In addition to the mapping table that you will feed through synchronizations, and the automated aggregate table that allows you to target on Product Categories, a third table is created automatically.
It is a repository table named by default 'RFM Product Categories - category' and linked to the aggregate table, as can be seen in the Datamart studio.
This is a technical table, whose role is to link the aggregate table to the category mapping table by generating a technicalId for each category.
It is populated automatically and serves no practical purposes to you as a user. You therefore do not need to include it in your targetings or to consider it whatsoever.
Targeting on Product Categories
The aggregate table (by default 'RFM Product Categories') can now be used to build powerful targetings to select profiles who have purchased from a specific category of products during a specific time frame.
It can be selected as a targeting module like any other Linked Data or Interaction table. However, it only contains 4 standard criteria that allow you to easily build targetings on aggregated product categories.
- Category: this is the name of the product category.
The criteria on 'Category' is mandatory, as the aggregate table is specifically built to target on Product Categories.
To ensure accurate results on the 'amount' and 'number' criteria, you can only select one category.
If you need to target on multiple categories, use a second block separated by an 'OR' operator.
-
Amount of the purchases: this is the total value of the profile interactions in the given time period.
-
Number of purchases: this is a frequency criteria, to count the number of profile interactions in the given time period.
-
Purchase month: this is a time criteria related to the moment of the interactions. 5 relevant time periods can be selected by default: the previous month, the last 3 months, last 6 months, last 12 months & last 24 months.
Only complete months are considered, meaning that the current month will not be analyzed until it is finished. For instance, if we are the 20th of September, the previous month is August, and the last 3 months are June, July and August.
Practically, the targeting in the screenshot above will select all the profiles who have made at least 3 purchases in the 'Jeans' category over the last (complete) year, for a total value of over 150€.
All criteria are applied together on aggregate tables, as 'AND' is the only operator available between them. 'OR' is currently not available.
Advanced targeting criteria
While we recommend to use the standard criteria that are optimized for targeting on aggregates, you can access additional criteria by toggling the 'Advanced criteria' button.
These criteria are the usual criteria you would be able to see on any Linked Data or Interaction table, including dynamic criteria such as the total or average.
They are less optimized to build targetings on Product Categories aggregates, but allow more flexibility, especially for the time ranges on the 'Purchase Month'.
Conditioning on Product Categories
The 'Product Categories' aggregate table can also be used to personalize and condition your e-mails. For instance, to display parts of the e-mail only to the profiles who have purchased items from a specific category.
Like for any custom table, the aggregate table should be first added as a source.
Then you can build conditions on all the fields of the table.
In the screenshot above, the block of the e-mail will be displayed to all profiles who have purchased for more than 500€ in the 'Jeans' category.
Resetting the Product Category table
By default, the Product Categories keeps data aggregates for all interactions with a purchase moment more recent than 2 years ago. All entries with a purchase moment older than 730 days are automatically cleaned.
If the cleaning logic of the Interaction table (e.g. 'Tickets lines' table) is different, it is possible to manually reset the aggregate table and recalculate it from scratch: older data not present in the Interaction table anymore will exit the aggregate table and won't be present after the recalculation.
To reset an aggregate table, select it, click on the 'More' button, then choose 'Reset aggregate data'.
The table will be emptied entirely, then repopulated based on the data currently present in the related Interaction table.