Rewards Fact Tables
Rewards are perks or benefits offered to customers as part of a loyalty program or promotional campaign. These can be in the form of discounts, free products, points, cashback, or exclusive offers, given to encourage customer engagement, repeat purchases, or brand loyalty.
Given below are the fact tables associated with the rewards data:
- reward_issue_fact
- reward_payment_currency_fact
- reward_transaction_fact
- reward_transaction_customfield_fact
- reward_transaction_fullfillment_fact
Reward Issue Fact
This table captures details of rewards issued to customers. It includes unique identifiers for brands/ organisations, and promotions, points deducted, vouchers issued, and associated expiry dates within the Intouch system.
Databricks Table Name: reward_issue_fact
Reward Issue Fact - Entity Relationship Diagram (ERD)
All the rewards fact tables share the same ERD.
Zoom in the table for more clarity. Click the table title to view its details.
Reward Issue Fact Table
| Column Name | Data Type | Description | Linked Table |
|---|---|---|---|
| brand_id | bigint | Unique identifier of the brand. | _ |
| Id | bigint | Unique identifier for the table. | _ |
| intouch_points_deducted | string | Points deducted for the reward in the Intouch system. | _ |
| intouch_user_id | bigint | Intouch user ID of the customer. | _ |
| intouch_voucher | string | Voucher associated with the reward within the Intouch system. | _ |
| intouch_voucher_expiry | string | Date and time for the voucher expiry in the Intouch system. | _ |
| issued_ref_id | string | Reference id for cart promotion or gift voucher. | _ |
| org_id | bigint | Identifier associated with the org. | _ |
| points_ref_id | bigint | Reference id for points-related transactions, and is null for transactions that are free or paid with cash. | _ |
| reward_promotion_link_id | bigint | Identifier for the promotion that is linked to the reward. | _ |
| vendor_reward | string | Reward provided by the vendor. | _ |
| dim_catalog_promotion_id | bigint | Identifier for the catalogue promotion. | rewards_catalogue_promotion |
| dim_event_date_id | bigint | Date when the reward is issued. It is in the YYYYMMDD format. | date |
| dim_latest_updated_date_id | bigint | Date when the data corresponding to this event/ row is changed in the source table. It is in the YYYYMMDD format. | date |
| dim_latest_updated_time_id | bigint | Identifier for the time when the data corresponding to this event/ row is changed in the source table. | time |
| dim_reward_id | bigint | Identifier of the reward. | rewards |
| dim_reward_issue_update_on_id | bigint | Date when the reward issue table is updated. It is in the YYYYMMDD format. | time |
| transaction_id | bigint | Identifier of the transaction. | |
| year | integer | Year of the reward issual. | _ |
Reward Payment Currency Fact
Brands can issue rewards individually or in bulk. This table captures the transaction event where a reward is issued, including the mode of payment used for redeeming the reward. It has entries based on the type of reward and the type of mode of payment.
Customers can purchase rewards with cash, fixed points, variable points, or a combination of both. So, if a customer chooses to use points plus cash, the system issues rewards based on redeeming both cash and points from the customer's wallet. In this case, even for a single reward, two entries will be made in this fact table, as it would capture the total number of points needed and the total amount of cash needed for the reward issual, as separate entries.
Databricks Table Name: reward_payment_currency_fact
Reward Payment Currency Fact Table
| Column Name | Data Type | Description | Linked Table |
|---|---|---|---|
| auto_update_time | bigint | Date and time when the reward_transaction source table was last updated. It is in the Unix timestamp format. | - |
| auto_update_time_redemption | bigint | Date and time when the corresponding record in the redemption source table was last updated. It is in the Unix timestamp format. | - |
| dim_event_date_id | bigint | Date on which the issue reward transaction occurred. It is in the YYYYMMDD format. | date |
| dim_event_time_id | bigint | Identifier for the time when the issue reward transaction occurred. | time |
| dim_event_user_id | bigint | Identifier for the customer associated with the reward. | users |
| dim_latest_updated_date_id | bigint | Date when the data corresponding to this event/ row is changed in the source table. It is in the YYYYMMDD format. | date |
| dim_latest_updated_time_id | bigint | Identifier for the time when the data corresponding to this event/ row is changed in the source table. | time |
| dim_owner_id | string | Identifier for the owner module to which the reward belongs. They include journeys, goodwill, loyalty program, milestone, and campaigns. | reward_owner_dim |
| dim_owner_type_id | int | Identifier for the owner module type. Possible values of owner module types, in the linked dimension table include journeys, goodwill, loyalty program, milestone, and campaigns. | reward_owner_standard_dim |
| dim_payment_config_currency_type_id | bigint | Identifier for the currency type used in the payment configuration. Possible values of the currency types, in the linked dimension table include - conv_ratio, points, and cash. | reward_payment_config_currency_dim |
| dim_payment_config_id | bigint | Identifier for the payment configuration used by the customer to redeem rewards. Possible values of the payment configurations, in the linked dimension table include - cash, conv_ratio, free, points, and points_cash. | reward_payment_config_dim |
| dim_redemption_type_identifier_id | bigint | Identifier for the redemption type associated with the reward. Possible values of the redemption types, in the linked dimension table include: games, auction, cart_promotion, cash_wallet, vendor_only_reward, voucher, cash_back, intouch_reward, physical_reward, charity, miles, gift_card, sweepstakes, vendor_intouch_reward, card_disc. | reward_redemption_types_dim |
| dim_reward_id | bigint | Identifier for the reward. | rewards |
| dim_vendor_id | bigint | Identifier for the vendor associated with the reward transaction. | reward_vendor_dim |
| dim_vendor_redemption_id | bigint | Identifier for the redemption transaction from the vendor. | reward_vendor_redemption_dim |
| group_loyalty_redemption | int | Identifier for group loyalty redemption. | - |
| id | bigint | Unique identifier for the table record. It is the primary key for the table. | - |
| intouch_series_id | string | Identifier for the series within the Intouch system. It can be the promotion id or coupons series id. | - |
| issue_reference_id | string | Reference id for cart promotion or gift voucher. | - |
| payment_config_currency_value | double | The monetary value associated with the payment configuration. It can be either the cash value or the number of points. | - |
| quantity | bigint | Number of rewards issued in the transaction. For instance, if two units of Reward Type A and three units of Reward Type B are issued, there will be two entries: one with a quantity of two and another with a quantity of three. | - |
| redemption_id | bigint | Internal identifier for the redemption transaction. | - |
| request_id | string | Unique identifier for the request related to the reward issuance. | - |
| status_code | string | Status code indicating the result of the reward issuance process. | - |
| status_message | string | Message describing the status of the reward issuance, such as Reward issued successfully, or Promotion issued successfully. | - |
| success | int | Indicator for whether the reward issuance was successful (1 for success/ partial success, 0 for failure). | - |
| tender_details_id | bigint | Identifier for payment details where the currency is CASH. Although Capillary does not process this payment method, it stores related information (such as credit card usage) for reference. | - |
| user_payment_config_currency_id | bigint | Internal identifier for the user's payment configuration currency. | - |
| user_payment_config_id | bigint | Internal identifier for the user's payment configuration. | - |
| year | int | Year of the reward issual. | - |
Reward Transaction Fact
This table captures transaction details whenever a reward is issued. It shows one entry per reward issue transaction. Unlike the payment currency fact table entries, where there are two entries for a points plus cash redemption method, this table shows only one entry for the same transaction.
Databricks Table Name: reward_transaction_fact
Reward Transaction Fact Table
| Column Name | Data Type | Description | Linked Table |
|---|---|---|---|
| auto_update_time | bigint | Date and time when the reward_transaction source table was last updated. It is in the Unix timestamp format. | - |
| auto_update_time_redemption | bigint | Date and time when the corresponding record in the redemption source table was last updated. It is in the Unix timestamp format. | - |
| dim_event_date_id | bigint | Date when the reward transaction occurred. It is in the YYYYMMDD format. | date |
| dim_event_time_id | bigint | Identifier for the time when the reward transaction occurred. | time |
| dim_event_user_id | bigint | Identifier for the customer associated with the reward. | users |
| dim_latest_updated_date_id | bigint | Date when the data corresponding to this event/ row is changed in the source table. It is in the YYYYMMDD format. | date |
| dim_latest_updated_time_id | bigint | Identifier for the time when the data corresponding to this event/ row is changed in the source table. | time |
| dim_owner_id | string | Identifier for the owner module to which the reward belongs. They include journeys, goodwill, loyalty program, milestone, and campaigns. | reward_owner_dim |
| dim_owner_type_id | int | Identifier for the owner module type. Possible values of owner module types, in the linked dimension table include journeys, goodwill, loyalty program, milestone, and campaigns. | reward_owner_standard_dim |
| dim_redemption_type_identifier_id | bigint | Identifier for the redemption type associated with the reward. Possible values of the redemption types, in the linked dimension table include: games, auction, cart_promotion, cash_wallet, vendor_only_reward, voucher, cash_back, intouch_reward, physical_reward, charity, miles, gift_card, sweepstakes, vendor_intouch_reward, card_disc. | reward_redemption_types_dim |
| dim_reward_id | bigint | Identifier for the reward. | rewards |
| dim_vendor_id | bigint | Identifier for the vendor associated with the reward transaction. | reward_vendor_dim |
| dim_vendor_redemption_id | bigint | Identifier for the redemption transaction from the vendor. | reward_vendor_redemption_dim |
| group_loyalty_redemption | int | Identifier for group loyalty redemption. | - |
| id | bigint | Unique identifier for the table record. It is the primary key for the table. | - |
| intouch_series_id | string | Identifier for the series within the Intouch system. It can be the promotion id or coupons series id or null. | - |
| issue_reference_id | string | Reference id for cart promotion or gift voucher. | - |
| quantity | bigint | Number of rewards issued in the transaction. For instance, if two units of Reward Type A and three units of Reward Type B are issued, there will be two entries: one with a quantity of two and another with a quantity of three. | - |
| redemption_id | bigint | Internal identifier for the redemption transaction. | - |
| request_id | string | Unique identifier for the request related to the reward issuance. | - |
| status_code | string | Status code indicating the result of the reward issuance process. | - |
| status_message | string | Message describing the status of the reward issuance, such as Reward issued successfully, or Promotion issued successfully. | - |
| success | int | Indicator for whether the reward issuance was successful (1 for success/ partial success, 0 for failure). | - |
| tender_details_id | bigint | Identifier for payment details where the currency is CASH. Although Capillary does not process this payment method, it stores related information (such as credit card usage) for reference. | - |
| year | int | Year of the reward is issual. | - |
Reward Transaction Custom Field Fact
This table captures the reward issue transaction along with its custom field data. Entries are based on the number of custom fields. For example, if a reward with three custom fields is issued, the table will have three entries for that single transaction, each with the same transaction ID.
Databricks Table Name: reward_transaction_customfield_fact
Reward Transaction Custom Field Fact Table
| Column Name | Data Type | Description | Linked Table |
|---|---|---|---|
| auto_update_time | bigint | Date and time when the reward_transaction source table was last updated. It is in the Unix timestamp format. | - |
| auto_update_time_redemption | bigint | Date and time when the corresponding record in the redemption source table was last updated. It is in the Unix timestamp format. | - |
| auto_update_time_user_custom_field | bigint | Date and time when the corresponding record in the user_custom_field source table was last updated. It is in the Unix timestamp format. | - |
| custom_field_mapping_isactive | int | Indicates whether the custom field mapping is active (1 for active, 0 for inactive). | - |
| custom_field_value | string | The value associated with a custom field. | - |
| dim_custom_field_id | bigint | Identifier for the rewards custom field. | reward_custom_fields_dim |
| dim_event_date_id | bigint | Date when the reward transaction occurred. It is in the YYYYMMDD format. | date |
| dim_event_time_id | bigint | Identifier for the time when the reward transaction occurred. | time |
| dim_event_user_id | bigint | Identifier for the user associated with the reward transaction. | users |
| dim_latest_updated_date_id | bigint | Date when the data corresponding to this event/ row is changed in the source table. It is in the YYYYMMDD format. | date |
| dim_latest_updated_time_id | bigint | Identifier for the time when the data corresponding to this event/ row is changed in the source table. | time |
| dim_owner_id | string | Identifier for the module to which the reward belongs. They include journeys, goodwill, loyalty program, milestone, and campaigns. | reward_owner_dim |
| dim_owner_type_id | int | Identifier for the owner module type. Possible values of owner module types, in the linked dimension table include journeys, goodwill, loyalty program, milestone, and campaigns. | reward_owner_standard_dim |
| dim_payment_config_id | bigint | Identifier for the payment configuration used by the customer to redeem rewards. Possible values of the payment configurations, in the linked dimension table include - cash, conv_ratio, free, points, and points_cash. | reward_payment_config_dim |
| dim_redemption_type_identifier_id | bigint | Identifier for the redemption type associated with the reward. Possible values of the redemption types, in the linked dimension table include: games, auction, cart_promotion, cash_wallet, vendor_only_reward, voucher, cash_back, intouch_reward, physical_reward, charity, miles, gift_card, sweepstakes, vendor_intouch_reward, card_disc. | reward_redemption_types_dim |
| dim_reward_id | bigint | Identifier for the reward. | rewards |
| dim_scope_id | bigint | Identifier for the scope. Scope defines where the additional custom fields will apply: - REWARD: While creating or updating a reward. - CATALOGUE_PROMOTION: While creating or updating a promotion. - ISSUE_REWARD: While issuing a reward. | reward_fulfillment_details_scope_dim |
| dim_vendor_id | bigint | Identifier for the vendor associated with the reward. | reward_vendor_dim |
| dim_vendor_redemption_id | bigint | Identifier for the vendor redemption. | reward_vendor_redemption_dim |
| group_loyalty_redemption | int | Identifier for group loyalty redemption. | - |
| id | bigint | Unique identifier for the table record. It is the primary key for the table. | - |
| intouch_series_id | string | Identifier for the series within the Intouch system. It can be the promotion id or coupons series id or null. | - |
| issue_reference_id | string | Reference ID for cart promotion or gift voucher. | - |
| quantity | bigint | Number of rewards issued in the transaction. For instance, if two units of Reward Type A and three units of Reward Type B are issued, there will be two entries: one with a quantity of two and another with a quantity of three. | - |
| redemption_id | bigint | Internal identifier for the redemption transaction. | - |
| request_id | string | Unique identifier for the request related to the reward issuance. | - |
| status_code | string | Status code indicating the result of the reward issuance process. | - |
| status_message | string | Message describing the status of the reward issuance, such as Reward issued successfully, or Promotion issued successfully. | - |
| success | int | Indicator for whether the reward issuance was successful (1 for success, 0 for failure). | - |
| tender_details_id | bigint | Identifier for payment details where the currency is CASH. Although Capillary does not process this payment method, it stores related information (such as credit card usage) for reference. | - |
| user_custom_field_id | bigint | Internal identifier for the user's custom field. | - |
| user_payment_config_id | bigint | Internal identifier for the user's payment configuration. | - |
| year | int | Year of the reward issual. | - |
Reward Transaction Fullfillment Fact
This fact table captures the issue reward event, along with the fullfillment details of the transactions. It includes multiple entries based on the number of fullfillment details. Fullfillment details include statuses of the reward issual such as order confirmed, voucher issued, shipped, or delivered.
Databricks Table Name: reward_transaction_fullfillment_fact
Reward Transaction Fullfillment Fact Table
| Column Name | Data Type | Description | Linked Table |
|---|---|---|---|
| auto_update_time | bigint | Date and time when the reward_transaction source table was last updated. It is in the Unix timestamp format. | - |
| auto_update_time_fulfillment_details | bigint | Date and time when the fulfillment_details source table was last updated. It is in the Unix timestamp format. | - |
| auto_update_time_redemption | bigint | Date and time when the redemption source table was last updated. It is in the Unix timestamp format. | - |
| dim_event_date_id | bigint | Date when the reward transaction occurred. It is in the YYYYMMDD format. | date |
| dim_event_time_id | bigint | Identifier for the time when the reward transaction occurred. | time |
| dim_event_user_id | bigint | Identifier for the user associated with the reward transaction. | users |
| dim_ful_fillment_status_id | bigint | Identifier for the fulfillment status of the reward. Possible values are integers that represent statuses such as, Shipped, Delivered, Order Confirmed, On the Way, Voucher Issued, Delivering Soon, or In Transit. | reward_fulfillment_status_dim |
| dim_latest_updated_date_id | bigint | Date when the data corresponding to this event/ row is changed in the source table. It is in the YYYYMMDD format. | date |
| dim_latest_updated_time_id | bigint | Identifier for the time when the data corresponding to this event/ row is changed in the source table. | time |
| dim_owner_id | string | Identifier for the owner module to which the reward belongs. They include journeys, goodwill, loyalty program, milestone, and campaigns. | reward_owner_dim |
| dim_owner_type_id | int | Identifier for the owner module type. Possible values of owner module types, in the linked dimension table include journeys, goodwill, loyalty program, milestone, and campaigns. | reward_owner_standard_dim |
| dim_payment_config_id | bigint | Identifier for the payment configuration used by the customer to redeem rewards. Possible values of the payment configurations, in the linked dimension table include - cash, conv_ratio, free, points, and points_cash. | reward_payment_config_dim |
| dim_redemption_type_identifier_id | bigint | Identifier for the redemption type associated with the reward. Possible values of the redemption types, in the linked dimension table include: games, auction, cart_promotion, cash_wallet, vendor_only_reward, voucher, cash_back, intouch_reward, physical_reward, charity, miles, gift_card, sweepstakes, vendor_intouch_reward, card_disc. | reward_redemption_types_dim |
| dim_reward_id | bigint | Identifier for the reward. | rewards |
| dim_scope_id | bigint | Identifier for the scope. Scope defines where the additional custom fields will apply: - REWARD: While creating or updating a reward. - CATALOGUE_PROMOTION: While creating or updating a promotion. - ISSUE_REWARD: While issuing a reward. | reward_fulfillment_details_scope_dim |
| dim_vendor_id | bigint | Identifier for the vendor associated with the reward transaction. | reward_vendor_dim |
| dim_vendor_redemption_id | bigint | Identifier for the redemption transaction from the vendor. | reward_vendor_redemption_dim |
| fulfillment_details_enabled | int | Indicator of whether fulfillment details tracking is enabled (1 for yes, 0 for no). | - |
| fullfillment_details_id | bigint | Identifier for the specific fulfillment details associated with the transaction. | - |
| group_loyalty_redemption | int | Identifier for group loyalty redemption. | - |
| id | bigint | Unique identifier for the reward transaction fulfillment record. This is the primary key of this table. | - |
| intouch_series_id | string | Identifier for the series within the Intouch system. It can be the promotion id or coupons series id or null. | - |
| issue_reference_id | string | Reference id for cart promotion or gift voucher. | - |
| quantity | bigint | Number of rewards issued in the transaction. For instance, if two units of Reward Type A and three units of Reward Type B are issued, there will be two entries: one with a quantity of two and another with a quantity of three. | - |
| redemption_id | bigint | Internal identifier for the redemption transaction. | - |
| request_id | string | Unique identifier for the request related to the reward issuance. | - |
| status_code | string | Status code indicating the result of the reward issuance process. | - |
| status_message | string | Message describing the status of the reward issuance, such as Reward issued successfully, or Promotion issued successfully. | - |
| success | int | Indicator for whether the reward issuance was successful (1 for yes, 0 for no). | - |
| tender_details_id | bigint | Identifier for payment details where the currency is CASH. Although Capillary does not process this payment method, it stores related information (such as credit card usage) for reference. | - |
| user_payment_config_id | bigint | Internal identifier for the user's payment configuration. | - |
| year | int | Year of the reward issual. | - |
Updated 15 days ago