Badges Fact Table

Introduction to Badges Fact Tables

Badges are a type of visual recognition used in loyalty programs to reward customers for their progress, specific actions or achievements. They act as a form of gamification and instill a sense of pride and accomplishment within the user, thus, reinforcing the targeted behaviors and boosting engagement.

For more information about how badges work in Capillary product suite, please refer to the Badges Documentation. This part of the documentation covers how you can analyze and report on badges featured in the Capillary system.

There are a few crucial elements of Badges lifecycle which a user would like to track and analyze. They include events such as earning a badge (without enrolment), the issual of badge (based on enrolment) and the promotions linked with the badge. The information of these events are stored in the form of fact and dimension tables based on star schema.
The fact tables store the details of these events and the corresponding dimension tables contain the fields on the basis of which the entries in these fact tables can be grouped/filtered for reporting.

Badges - Entity Relationship Diagram (ERD)

Zoom in the table for more clarity. Click the table title to view its details.

Badges - Earn (badges_earn)

Each row in this table represents an event in which a customer earns a badge on completing a specified activity or meeting a set of pre-defined criteria. This doesn’t require the customer to enrol for the badge beforehand. It captures information like the badge ID, customer ID, earning date, expiry date, and whether the badge is active or revoked.

For example-
When a customer registers, they automatically receive the "New Member" badge if the configuration specifies that pre-enrollment is not needed. These types of events are stored in the Badges_earn fact table.
By linking this fact table with the dimension tables, you would be able to have a complete picture of this event.

Databricks Table Name: badges_earn

Badges Earn Fact Table

Column name	Data type	Description	Linked Table	Availability for Export in the Badges Earn Template
auto_update_time_badges_earn	bigint	Date and time when the badges_earn table was last updated. It is in the Unix timestamp format. The update takes place only when the badge deactivation occurs.	_	Yes; Measure Name: Auto Update Time Badges Earn
dim_badgeMeta_id	string	Unique identifier assigned to a badge when it is generated. It is a common key that connects to the badgeMeta dimension table for providing badge details.	badgemeta	Yes; Dimension Name: Badgemeta
dim_badges_earn_active_id	bigint	Indicates if the badge is currently active or not.	enabled	Yes; Dimension Name: Badges Earn Active
dim_badges_owner_type_id	string	Provides information about the module (name) that granted the customer a particular badge. For instance, if the badge was awarded by the Journeys module, you can retrieve Journeys module details by linking to the badges_owner_type field using this column. The types of modules are - Loyalty/ Referral_Campaigns/ Journeys/ Auidence_Campaigns/ Rewards_Catalog/ Goodwill_Module/ Milestones.	badges_owner_type	Yes; Dimension Name: Badges Owner Type
dim_createdOn_date_id	bigint	Date on which the badge was earned by the customer.	date	Yes; Dimension Name: Date
dim_createdOn_id	bigint	Time when the badge was earned by the customer.	time	Yes; Dimension Name: Time
dim_customer_id	bigint	Unique identifier for the customer associated with the badge.	users	Yes; Dimension Name: Customer user Id
dim_event_date_id	bigint	Date on which the badge was earned.	date	Yes; Dimension Name: Date
dim_event_time_id	bigint	Time when the badge was earned.	time	Yes; Dimension Name: Time
dim_expiresOn_date_id	bigint	Date on which the badge expires.	date	Yes; Dimension Name: Date
dim_expiresOn_id	bigint	Time when the badge expires.	time	Yes; Dimension Name: Time
dim_latest_updated_date_id	bigint	Date when the table was last updated.	date	Yes; Dimension Name: Date
dim_latest_updated_time_id	bigint	Time when the table was last updated.	time	Yes; Dimension Name: Time
earnId	string	Unique identifier assigned to each row of the earn badges fact table. It is the primary key of this table. It is useful in accessing information related to the customer, badge earn date, badge expiration details, etc.	_	Yes; Measure Name: Earnid
isActive	string	Indicates if the badge is currently active or not.	_	Yes; Measure Name: Isactive
lastUpdatedBy	bigint	Identifier of the user who last updated the badge. It could be a brand representative, a Customer Success Manager (CSM), or someone similar.	_	Yes; Measure Name: Lastupdatedby
ownerReferenceId	string	Orchestered ID assigned to the module responsible for awarding the badge, e.g, milestone id, journey id, campaign id, loyalty id, etc.	_	Yes; Measure Name: Ownerreferenceid
year	int	Year of the event.	_	Yes; Dimension Name: Year

Badges - Issue (badges_issue)

Each row in this table represents the event, where the customer has to enrol in the badge before they can earn it. It captures information like the badge ID, customer ID, issuance date, expiry date, and whether the badge is active or revoked.

For example-
A brand launches a campaign to ensure proactive engagement. In this campaign, the first 1000 participants would have the opportunity to become premium members. As configured, the "premium member" badge requires pre-enrollment. Therefore, such types of events are stored in this table.

Databricks Table Name: badges_issue

Badges Issue Fact Table

Column Name	Data Type	Description	Linked Table	Availability for Export in the Badges Issue Template
auto_update_time_badges_issue	bigint	Date and time when the badges_issue table was last updated. It is in the Unix timestamp format. The update takes place only when the badge deactivation occurs.	_	Yes; Measure Name: Auto Update Time Badges Issue
badges_issued_id	string	It is an unique identifier for the 'badges issued' fact table. As a primary key, it uniquely identifies each record or entry within the table. It allows tracking and analysis of individual badge issuance events.	_	Yes; Measure Name: Badges Issued Id
createdBy	bigint	Identifier of the user who issued the badge, such a brand representative.	_	Yes; Measure Name: Createdby
dim_badgeMeta_id	string	Unique identifier assigned to a badge when it is generated. It is a common key that connects to the badgeMeta dimension table for providing badge details.	badgemeta	Yes; Dimension Name: Badgemeta
dim_badges_issue_active_id	bigint	Indicates whether the badge is currently active or not.	enabled	Yes; Dimension Name: Badges Issue Active
dim_badges_owner_type_id	string	Provides information about the module (name) that granted the customer a particular badge. For instance, if the badge was awarded by the Journeys module, you can retrieve Journeys module details by linking to the badges_owner_type field using this column. The types of modules are - Loyalty/ Referral_Campaigns/ Journeys/ Auidence_Campaigns/ Rewards_Catalog/ Goodwill_Module/ Milestones.	badges_owner_type	Yes; Dimension Name: Badges Owner Type
dim_createdOn_date_id	bigint	Date when the badge was issued to the customer.	date	Yes; Dimension Name: Date
dim_createdOn_id	bigint	Time when the badge was issued to the customer.	time	Yes; Dimension Name: Time
dim_customer_id	bigint	Unique identifier for the customer associated with the badge.	users	Yes; Dimension Name: Customer user Id
dim_event_date_id	bigint	Date on which the badge was issued.	date	Yes; Dimension Name: Date
dim_event_time_id	bigint	Time when the badge was issued.	time	Yes; Dimension Name: Time
dim_expiresOn_date_id	bigint	Date on which the badge expires.	date	Yes; Dimension Name: Date
dim_expiresOn_id	bigint	Time when the badge expires.	time	Yes; Dimension Name: Time
dim_latest_updated_date_id	bigint	Date when the table was last updated.	date	Yes; Dimension Name: Date
dim_latest_updated_time_id	bigint	Time when the table was last updated.	time	Yes; Dimension Name: Time
isActive	string	Indicates whether the badge is currently active or not.	_	Yes; Measure Name: Isactive
lastUpdatedBy	bigint	Identifier of the user who last updated the badge. It could be a brand representative, a Customer Success Manager (CSM), or someone similar.	_	Yes; Measure Name: Lastupdatedby
ownerReferenceId	string	Orchestered ID assigned to the module responsible for awarding the badge, e.g. milestone id, journey id, campaign id, loyalty id, etc.	_	Yes; Measure Name: Ownerreferenceid
requestId	string	It is a request ID generated during the issue of a badge. This ID serves as a reference point for tracking and managing the issuance of badges.	_	Yes; Measure Name: Requestid
year	int	Year of the event.	_	Yes; Dimension Name: Year

Badges - Earned Benefits (badges_earned_benefits)

Each row in this table represents the event, wherein the promotions or benefits are linked to the badge. It captures information on the benefit type, coupon code, points earned, and validity of the benefit that comes along.

For example-
A brand wants to run a campaign where customers who purchase items worth Rs. 10,000 during the campaign will earn a "Super Customer" badge. Additionally, the brand will provide extra coupons to "Super Customers." Here, the event of badge creation is linked to a promotion, and thus such events will be captured in this table.

Databricks Table Name: badges_earned_benefits

Badges Earned Benefits Fact Table

Column Name	Data Type	Description	Linked Table	Availability for Export in the Badges Earned Benefits Template
allocationStrategyId	bigint	Denotes the strategy selected to allocate points for badges. In case of tier-based allocation, points are allocated according to predefined tiers or categories.	_	Yes: Measure Name: Allocationstrategyid
auto_update_time_badges_benefits_earn	bigint	Date and time when the badges_benefits_earn table was last updated. It is in the Unix timestamp format. The update takes place only when the badge deactivation occurs.	_	Yes: Measure Name: Auto Update Time Badges Benefits Earn
benefitType	string	Type of benefit linked to the badge. The benefit types are: coupons and points.	_	Yes: Measure Name: Benefittype
couponCode	string	The coupon code associated with the benefit. It is generated by the coupons system.	_	Yes: Measure Name: Couponcode
description	string	Description of the coupon.	_	Yes: Measure Name: Description
dim_badgeMeta_id	string	Unique identifier assigned to a badge when it is generated. It is a common key that connects to the badgeMeta dimension table for providing badge details.	badgemeta	Yes; Dimension Name: Badgemeta
dim_coupon_series_id	string	Unique identifier for coupon series id. Whenever a coupon is generated, it is allocated a unique coupon series ID. It links to the coupon series dimension table, establishing a relationship between the benefits earned and the details of the coupon series.	coupon_series	Yes; Dimension Name: Coupon Series
dim_customer_id	bigint	Unique identifier for the customer associated with the badge.	users	Yes; Dimension Name: Customer user Id
dim_event_date_id	bigint	Date on which the benefit (coupon or point) was earned.	date	Yes; Dimension Name: Date
dim_event_time_id	bigint	Time when the benefit (coupon or point) was earned.	time	Yes; Dimension Name: Time
dim_latest_updated_date_id	bigint	Date when the table was last updated.	date	Yes; Dimension Name: Date
dim_latest_updated_time_id	bigint	Time when the table was last updated.	time	Yes; Dimension Name: Time
dim_validTill_id	bigint	Denotes the coupon validity date.	date	Yes; Dimension Name: Validtill
discountCode	string	Discount code associated with the coupon series.	_	Yes; Measure Name: Discountcode
discountType	string	Type of discount. Can be ABS - absolute discount type where the discount value is fixed, or percent discount or a fixed discount.	_	Yes; Measure Name: Discounttype
earnId	string	Unique identifier for the badges_earn table.	_	Yes; Measure Name: Earnid
expirationStrategyId	bigint	Denotes the strategy used for deciding how points expire for the customer, such as fixed expiry date, or no expiry, etc.	_	Yes; Measure Name: Expirationstrategyid
notes	string	Provides additional information or remarks related to badges.	_	Yes; Measure Name: Notes
pointsEarned	bigint	Points earned by the customer.	_	Yes; Measure Name: Pointsearned
programId	bigint	The Loyalty program ID is the identifier assigned to the loyalty program linked to the allocated points.	_	Yes; Measure Name: Programid
promoIdentifier	string	Unique identifier assigned to a promotion.	_	Yes; Measure Name: Promoidentifier
promotionId	string	Unique id assigned to a particular promotion.	_	Yes; Measure Name: Promotionid
promotionName	string	Refers to the name of the promotion to which points were allocated, redeemed, or expired.	_	Yes; Measure Name: Promotionname
statusCode	int	Status code for the action (used for internal purpose).	_	Yes; Measure Name: Statuscode
uniqueId	string	Unique identifier for the table. As a primary key, it uniquely identifies each record or entry within the table. It allows tracking and analysis of individual badge benefits events.	_	Yes; Measure Name: Uniqueid
year	int	Year of the event.	_	Yes; Dimension Name: Year

You can use Insights and perform the following:

Create a chart using Badges KPI and Dimensions
Create a custom report on Badges
Export Badges using standard Badges export templates

Updated 2 days ago

Introduction to Badges Fact Tables

LegendPK Primary KeyLK Linking Key

Badges - Earn (badges_earn)

Badges - Issue (badges_issue)

Badges - Earned Benefits (badges_earned_benefits)

Legend

PK Primary Key

LK Linking Key