Behavior Tags & Study Groups
Behavior Tag Time Series
Most text in a data warehouse is descriptive text stored in dimension tables. Customer data-mining and clustering analyses can produce textual behavior tags that describe a customer's observed behavior during a particular period.
For example, a customer might be classified as New User, then At Risk, and later Loyalist based on periodic behavioral analysis.
Behavior Tags as a Fact Table
Behavior tags can be modeled in a fact table when the business wants to retain and analyze the behavior classification at each observation period.
Example (Behavior tag fact table):
| customer_key | customer_durable_key | date_key | behavior_tag |
|---|---|---|---|
| 4471 | C-100 | 20260101 | New User |
| 4472 | C-100 | 20260201 | At Risk |
| 4472 | C-100 | 20260301 | Loyalist |
| 6210 | C-105 | 20260101 | New User |
| 6210 | C-105 | 20260201 | Loyalist |
| 6210 | C-105 | 20260301 | At Risk |
customer_key identifies the Type 2 dimension row valid at the observation date. For customer C-100, an attribute change created a new version: January’s observation references row 4471, while February’s references row 4472. Both versions share the same customer_durable_key, C-100, which identifies the customer across their history.
This approach is particularly useful when the business needs to retain an unlimited or arbitrarily long history of behavior classifications. New observations are simply added as new rows, so the schema does not need to change as the history grows.
It also allows analysts to ask questions about behavior at any point in time, such as:
- How many customers were At Risk in March?
- How has the number of Loyalist customers changed over time?
- Which customers changed from At Risk to Loyalist?
- What behavior tag did a customer have at a particular point in time?
The main drawback is that queries involving multiple positions in the behavioral sequence can become complex. For example, finding customers who are currently Loyalist, were At Risk in the previous period and New User two periods ago, requires comparing multiple fact rows.
Behavior Tags as Positional Attributes
When the primary requirement is to perform complex simultaneous queries across a entity's recent behavioral history, the behavior tags can instead be stored in the dimension table as positional attributes.
The current tag and a limited number of prior tags can be stored explicitly, with an optional text attribute containing the complete sequence.
| customer_key | customer_durable_key | behavior_current | behavior_prior_1 | behavior_prior_2 | behavior_sequence |
|---|---|---|---|---|---|
| 4472 | C-100 | Loyalist | At Risk | New User | N-A-L |
| 6210 | C-105 | At Risk | Loyalist | New User | N-L-A |
Here, behavior_prior_1 represents the immediately preceding period, while behavior_prior_2 represents the period before that.
The positional approach is appropriate when there is a known, bounded number of prior periods that users commonly query. It should not be extended indefinitely by adding columns such as behavior_prior_3, behavior_prior_4, and so on.
If the business requires an unlimited behavioral history, the fact-table approach is generally more appropriate. The positional design can then be viewed as a convenient representation of a bounded behavioral window optimized for common pattern-based queries.
Behavior Study Groups
Some behaviors can be identified only through complex, iterative analyses that may be expensive to reproduce every time a BI application needs to query them.
Instead of embedding this analysis in every BI application, its results can be captured as a study group: a simple table containing the durable keys of the entities which satisfy a particular analytical condition.
NOTE
A durable key (often called a durable supernatural key) is an identifier that remains associated with the same entity and never changes. Unlike a surrogate key that changes every time a new historical row is created, a durable key remains exactly the same across all versions of that entity.
Study Group Table (High-Value At-Risk Customers):
For example, a data-science analysis might identify customers who are both high-value and at risk.
| study_group_id | customer_durable_key |
|---|---|
| SG-001 | C-100 |
| SG-001 | C-105 |
| SG-001 | C-212 |
The study group can then be joined or used as a filter against the Customer dimension in any dimensional schema that uses the same customer durable key. Using the durable key allows the study group to remain independent of the particular Type 2 version of the customer dimension.
Multiple study groups can be maintained:
| study_group_id | Description |
|---|---|
| SG-001 | High-Value At-Risk Customers |
| SG-002 | Frequent Online Buyers |
| SG-003 | Customers Likely to Churn |
Because study groups are the results of analytical processes, their membership can change when the analysis is rerun or when the underlying customer behavior changes over time. If only the current membership is required, the study group can simply be replaced with the latest analysis results.
If the history of study-group membership is important, the study group can instead be maintained as periodic snapshots:
Study Group Membership Snapshots:
| study_group_id | snapshot_date | customer_durable_key |
|---|---|---|
| SG-001 | 2026-01-01 | C-100 |
| SG-001 | 2026-01-01 | C-105 |
| SG-001 | 2026-04-01 | C-100 |
| SG-001 | 2026-04-01 | C-105 |
| SG-001 | 2026-04-01 | C-212 |
This allows analysts to determine which customers belonged to a study group at a particular point in time, even after subsequent analysis runs change the membership.
Because study groups are represented as sets of durable customer keys, new groups can also be derived by performing set operations such as intersections, unions, and differences. For example, the intersection of SG-001 and SG-002 identifies customers who are both high-value and at risk and frequent online buyers.