Badges
Introduction to Badges Facts and Dimension Tables
Badges are a form of recognition or achievement typically used in various systems or platforms to acknowledge users' accomplishments or participation in certain activities. They serve as visual indicators of accomplishment and can be earned through completing tasks, reaching milestones, or meeting specific criteria set by the system administrators. Badges can enhance user engagement, motivate users to participate more actively, and foster a sense of accomplishment and community within the platform.
According to the Star Schema Layout, for badges, there are three fact tables:
- badges_earn
- badges_issue
- badges_earned_benefits**
Each of these tables contains information about the earning, issuance, and associated benefits of badges, respectively. Additionally, there are several dimension tables providing context and additional details for the badges data.
Linking these fact tables with dimension tables allows analysts to perform more comprehensive analyses and derive insights. Below are the ways in which these linkages can prove beneficial with respect to the following use case:
Awarding Activity-Based Badge for Purchases. For example a customer earns an activity based badge by making a purchase. The objective is to incentivize customer engagement and increase sales by rewarding users with activity-based badges upon making purchases, utilizing the Badges Facts and Dimension Tables.
- Understanding Badge Attributes: Dimension tables like badgemeta and coupon_series provide additional attributes related to badges and benefits. By linking these tables with the fact tables, analysts can enrich their understanding of the badges being earned or issued and the associated benefits.
Example - The badge is configured in the system to be awarded to customers who meet the specified criteria (eg. a purchase of more or equal to Rs.1000). A customer makes a purchase. The purchase transaction details, including the customer ID, purchase date, and purchase amount, are recorded in the badges_earn fact table. This facts table is linked to badgemeta and users dimension table giving access to the information contained in these tables.
- Customer Analysis: Linking the dim_customer_id in fact tables with the users dimension table allows analysts to analyse the badge earning and issuance patterns for specific customers. This helps in understanding customer behaviour, preferences, and engagement levels.
Example - The customer meets the criteria (e.g., customer has purchased for Rs. 1100), and earns the badge. The badges_earn table is linked to the `dim_customer_id’ and ‘users’ dimension table, where the analysts can get more information related to the customer and his behaviour pattern.
- Temporal Analysis: The date dimension table facilitates temporal analysis by providing various date-related attributes
Example - When a customer earns a badge, the badge is issued and the customer gets the associated benefit. All these actions are recorded in the facts tables which are connected to the date dimension table. Analysts can link these attributes with the corresponding date IDs in the fact tables to analyse badge earning and issuance trends over time, such as monthly or quarterly trends.
- Benefit Utilization: Analysts can examine the utilization of benefits associated with earned badges by linking dim_coupon_series_id with the coupon_series dimension table.
Example - The customer has received some coupons as the benefit from the earned badge. The benefits facts table is linked to the dim_coupon_series_id with the coupon_series dimension table. This helps in creating a relationship between the benefits accrued by customers, and the specific characteristics or attributes of the coupon series itself. This linkage facilitates tracking and analysis of how benefits are associated with different coupon series, providing insights into the effectiveness and performance of various promotional campaigns or incentive programs.
Overall, by establishing these linkages between fact and dimension tables, analysts can perform various analyses such as customer segmentation, trend analysis, program effectiveness assessment, and optimization of reward strategies, leading to informed decision-making and improved user engagement within the platform.
Badges - Earn (badges_earn)
This table records the issual of badges to the customers. It includes information such as the badge ID, customer ID, earning date, expiry date, and whether the badge is active or revoked.
| Column name | Data type | Description | Linked Table |
|---|---|---|---|
| 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. | |
| 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 |
| dim_badges_earn_active_id | bigint | Indicates if the badge is currently active or not. | enabled |
| 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 |
| dim_createdOn_date_id | bigint | Date on which the badge was earned by the customer. | date |
| dim_createdOn_id | bigint | Time when the badge was earned by the customer. | time |
| dim_customer_id | bigint | Unique identifier for the customer associated with the badge. | users |
| dim_event_date_id | bigint | Date on which the badge was earned. | date |
| dim_event_time_id | bigint | Time when the badge was earned. | time |
| dim_expiresOn_date_id | bigint | Date on which the badge expires. | date |
| dim_expiresOn_id | bigint | Time when the badge expires. | time |
| dim_latest_updated_date_id | bigint | Date when the table was last updated. | date |
| dim_latest_updated_time_id | bigint | Time when the table was last updated. | time |
| earnId | string | Unique identifier assigned to each row of the earn badges facts 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. | |
| isActive | string | Indicates if the badge is currently active or not. | |
| 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. | |
| ownerReferenceId | string | Orchestered ID assigned to the module responsible for awarding the badge, e.g, milestone id, journey id, campaign id, loyalty id, etc. | |
| year | int | Year of the event. |
Badges - Issue (badges_issue)
This table logs the enrolment of customers to badges. It contains details such as the badge ID, customer ID, issuance date, expiry date, and whether the badge is active or revoked.
| Column Name | Data Type | Description | Linked Table |
|---|---|---|---|
| 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. | |
| badges_issued_id | string | It is an unique identifier for the 'badges issued' facts 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. | |
| createdBy | bigint | Identifier of the user who issued the badge, such a brand representative. | |
| 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 |
| dim_badges_issue_active_id | bigint | Indicates whether the badge is currently active or not. | enabled |
| 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 |
| dim_createdOn_date_id | bigint | Date when the badge was issued to the customer. | date |
| dim_createdOn_id | bigint | Time when the badge was issued to the customer. | time |
| dim_customer_id | bigint | Unique identifier for the customer associated with the badge. | users |
| dim_event_date_id | bigint | Date on which the badge was issued. | date |
| dim_event_time_id | bigint | Time when the badge was issued. | time |
| dim_expiresOn_date_id | bigint | Date on which the badge expires. | date |
| dim_expiresOn_id | bigint | Time when the badge expires. | time |
| dim_latest_updated_date_id | bigint | Date when the table was last updated. | date |
| dim_latest_updated_time_id | bigint | Time when the table was last updated. | time |
| isActive | string | Indicates whether the badge is currently active or not. | |
| 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. | |
| ownerReferenceId | string | Orchestered ID assigned to the module responsible for awarding the badge, e.g. milestone id, journey id, campaign id, loyalty id, etc. | |
| 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. | |
| year | int | Year of the event. |
Badges - Earned benefit (badges_earned_benefits)
This table associates benefits with badges earned by customers. It includes information about the benefit type, coupon code, description, points earned, and validity of the benefit.
| Column Name | Data Type | Description | Linked Table |
|---|---|---|---|
| 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. | |
| 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. | |
| benefitType | string | Type of benefit linked to the badge. The benefit types are: coupons and points. | |
| couponCode | string | The coupon code associated with the benefit. It is generated by the coupons system. | |
| description | string | Description of the coupon. | |
| 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 |
| 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 |
| dim_customer_id | bigint | Unique identifier for the customer associated with the badge. | users |
| dim_event_date_id | bigint | Date on which the benefit (coupon or point) was earned. | date |
| dim_event_time_id | bigint | Time when the benefit (coupon or point) was earned. | time |
| dim_latest_updated_date_id | bigint | Date when the table was last updated. | date |
| dim_latest_updated_time_id | bigint | Time when the table was last updated. | time |
| dim_validTill_id | bigint | Denotes the coupon validity date. | date |
| discountCode | string | Discount code associated with the coupon series. | |
| discountType | string | Type of discount. Can be ABS - absolute discount type where the discount value is fixed, or percent discount or a fixed discount. | |
| earnId | string | Unique identifier for the badges_earn table. | |
| expirationStrategyId | bigint | Denotes the strategy used for deciding how points expire for the customer, such as fixed expiry date, or no expiry, etc. | |
| notes | string | Provides additional information or remarks related to badges. | |
| pointsEarned | bigint | Points earned by the customer. | |
| programId | bigint | The Loyalty program ID is the identifier assigned to the loyalty program linked to the allocated points. | |
| promoIdentifier | string | Unique identifier assigned to a promotion. | |
| promotionId | string | Unique id assigned to a particular promotion. | |
| promotionName | string | Refers to the name of the promotion to which points were allocated, redeemed, or expired. | |
| statusCode | int | Status code for the action (used for internal purpose). | |
| 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. | |
| year | int | Year of the event. |
Updated about 3 hours ago