Dimension tables
Dimension tables contain descriptive attributes or characteristics of the data in the fact table. These attributes provide context for analysing the data in the fact table. In the current example, For example, a dimension table in a sales environment might contain details about products (product ID, product name, category, brand) or customers (customer ID, name, address).
Points Promotion
Databricks table name: points_promotions
This table captures the metadata of the promotion associated with the points allocation/ deduction.
Column Name | Data Type | Description |
---|---|---|
name | String | Name of the promotion associated with the points. |
id | Bigint | Unique identifier of the table, set by Capillary system. |
identifier | String | Unique identifier set by the brand. |
auto_update_promotion | Bigint | Date and time when the corresponding record in the points_promotions table available at the source was last updated. It is in the Unix timestamp format. |
points_promotion_id | Bigint | Identifier for the promotion. |
type | String | Type of the promotion, such as bill level or customer level. |
is_active | Integer | Indicates if the promotion is active or not (values 1 for yes, 0 for no). |
event_name | String | Name of the event linked to the points promotion. |
behavioral_event_name | String | Name of the behavioral event linked to the points promotion. |
targetGroupIds | String | Identifier for the target group. |
earnRestrictions | String | Defines conditions or limitations that determine how and when points can be earned for a particular promotion. |
end_date | String | Promotion end date, in YYYY-MM-DD format. |
program_id | String | Identifier of the program associated with the promotion. |
issualRestrictions | String | Defines conditions that determine the issuance of a promotion. |
expiryRestrictions | String | Defines conditions that determine the expiration of points. |
redemptionRestrictions | String | Defines conditions that determine the redemption of points. |
description | String | Description of the promotion. |
start_date | String | Promotion start date, in YYYY-MM-DD format. |
stackability | String | Indicates if the promotion is stackable (true for yes, false/ null for no). Stackability rules define which promotions can be used together. |
Points Awarded Type
Databricks table name: points_awarded_type
This table captures the type of points that are awarded.
Column Name | Data Type | Description |
---|---|---|
id | Bigint | Unique identifier of the table. |
type | String | Indicates the type of points that are awarded. Possible values are listed in the table below. |
Possible values for the awarded type:
Value | Description |
---|---|
Point_Awarded | Regular points awarded for making a transaction. |
Point_Awarded_Bill_Promotion | Promotional points awarded in addition to regular points at the transaction level. |
Point_Awarded_Lineitem | Regular points awarded for purchasing a specific product. |
Point_Awarded_Lineitem_Promotion | Promotional points awarded in addition to regular points at the line item level. |
Point_Awarded_Customer_Promotion | Promotional points awarded in addition to regular points at the customer level. |
Points Category
Databricks table name: points_category
This table captures the metadata of the points category.
Column Name | Data Type | Description |
---|---|---|
category_id | Bigint | Unique identifier of the points category table. |
redeemtion_type | String | Type of redemption. Possible values are - Redeemable, and Non-redeemable. |
auto_update_program | Bigint | Date and time when the program table, available at the source was last updated. It is in the Unix timestamp format. |
category_type | String | Category against which the points are awarded or deducted. Possible values are - Regular points, Trackers, Promised points, Alternate currencies, and External trigger based points. |
auto_update_points_category | Bigint | Date and time when the points_category table, available at the source was last updated. It is in the Unix timestamp format. |
category_name | String | Name of the points category. |
sub_category_type | String | Sub category against which the points are awarded or deducted. |
alternate_currency_name | String | Name of the alternate currency, such as tier points, coins, stars, credits. |
At Capillary backend, points categories are classified into four types, with three expected to be visible on the front-end exports. Following table gives the category types:
Value | Description |
---|---|
regular_points | Redeemable points. |
trackers | These will not appear in exports. Trackers are excluded from the point fact. |
promised_points | Strategy-based based points that accrue with a delay based on a specified return window. Their conversion date is known and available in the "awarded_date" field. |
external_trigger_based_points | Promised points expected to convert to regular points upon receipt of an external trigger. The conversion date is not predetermined. |
When handling promised points, the categorization must be done based on points categories only (and not based on non-redeemable values during query writing).
Screen reader support is enabled.
Points events type
Databricks table name: points_event_type
This table captures the type of points event (awarded or deducted).
Column Name | Data Type | Description |
---|---|---|
id | Bigint | Unique identifier of the table. |
type | String | Indicates whether the points were awarded or deducted. |
Deduction type
Databricks table name: deduction_type
This table captures the type of points deduction.
Column Name | Data Type | Description |
---|---|---|
id | Bigint | Unique identifier of the table. |
type | String | Indicates the type of points deduction. Possible values are listed in the table below. |
Possible values for the type of points deduction:
Value | Description |
---|---|
cancelled | Points that are cancelled. |
expired | Points that are expired. These appear against the awarded_ref_id and reduce the customer's point balance in the awarded program. |
migration | Not in use. |
redeemed | Points that are redeemed by the customer. |
return | Points that are deducted from a customers balance when a transaction, for which points were allocated, is returned before the points are redeemed or expired. |
redeemed_by_transfer | Points transferred from one customer's account to another. The original account reflects this deduction. To know more, refer redeemed_by_transfer. |
redemption_reversal | Scenario where a customer redeems points for a transaction but then decides not to use those points, leading to a reversal of the redemption. Also applies if the transaction on which the points were redeemed is reversed, which restores the redeemed points to the customers balance. To know more, refer redemption_reversal. |
expiry_reverted | Points deducted due to expiry but returned due to a subsequent transaction return. This follows a sequence of deduction and credit entries associated with the same awarded_ref_id. To know more, refer expiry_reverted. |
redemption_reverted | Reversal of points redeemed against a bill when that bill is returned. To know more, refer redemption_reverted. |
redeemed_by_transfer_reverted | Points transferred to another customer but reversed when the original transaction associated with those points is returned. To know more, refer redeemed_by_transfer_reverted. |
redeemed_by_conversion | Scenario where delayed accrual is enabled, and promised points are converted into regular points after a specified delay period. When the delay period ends, a bulk job runs, converting the promised points into regular points. A redeemed_by_conversion entry is created in the deduction table, indicating that the points are deducted from the promised points category. To know more, refer redeemed_by_conversion. |
return_historical | Scenario involving partial returns that occur to promised points before they are converted to regular points. When a partial return is made during the promised points phase, the corresponding portion of points is deducted under return_historical to account for the returned transaction. To know more, refer return_historical. |
manual_adjustment | Scenario where manual adjustment of points is made in a customer's account when points need to be deducted due to errors or unlinked transactions. To know more, refer manual_adjustment. |
Redemption type
Databricks table name: redemption_type
This table captures the type of points redemption.
Column Name | Data Type | Description |
---|---|---|
id | Bigint | Unique identifier of the table. |
redemption_type | String | Identifier for the points redemption type. Possible values are redemption, reversal, group_redemption, and reversal_on_return. |
Date
Databricks table name: date
This table captures the event date, including the year, week of the year, and other relevant time details, enabling time-based analysis and mapping of the specific event.
Column Name | Data Type | Description |
---|---|---|
date_id | Bigint | Unique identifier for a date in the table. |
date | String | Represents the date in the format DD-MM-YYYY (e.g., 2018-01-01). |
day_of_month | Integer | Day component of the date (range: 1 to 31). |
week_of_year | Integer | Calendar week of the year (range: 1 to 52). |
month | String | Month and year (e.g., Jan 2018). |
year | Integer | Year in YYYY format. |
quarter | String | Quarterly split of the calendar year (e.g., Q1: Jan, Feb, Mar; Q2: Apr, May, Jun; etc.). |
quarter_no | Integer | Cumulative quarter count from the beginning of the date calendar. |
year_quarter_no | Integer | Quarter number of the year (1 for Q1, 2 for Q2, etc.). |
week_number | Integer | Cumulative week count from the beginning of the date calendar. |
week_start_date | String | Start date of the calendar week in DD-MM-YYYY format. |
week_end_date | String | End date of the calendar week in DD-MM-YYYY format. |
day_of_week | String | Day of the week (Monday to Sunday). |
day_of_week_no | Integer | Day number of the week (1 for Monday to 7 for Sunday). |
month_no | Integer | Cumulative month count from the beginning of the date calendar. |
month_no_of_year | Integer | Month number of the year (1 for January, 2 for February, etc.). |
month_of_year | String | Month of the year (January to December). |
Zone till
Databricks table name: zone_tills
This table captures the metadata of the point-of-sale (POS) terminal associated with the store. The till id distinguishes one checkout location from another within the same store, aiding in transaction tracking and management. For example:
By mapping the information in this table, brands can conduct store wise performance analysis.
Column Name | Data Type | Description |
---|---|---|
store_country | String | Country where the store is located. |
external_id_1 | String | Store identifier from the brand side. |
store | String | Store where the transaction was done. |
auto_update_till_parent | Bigint | Date and time when the corresponding record in till_parent table, available at the source was last updated. It is in the Unix timestamp format. |
area | String | Area of the store location. |
store_name | String | Store name given by Capillary System. |
store_city | String | City where the store belongs. |
store_state | String | State where the store belongs. |
till | String | Name of the point-of-sale (POS) terminal within a store. |
auto_update_till_store_relation | Bigint | Date and time when the corresponding record in the till_store_relation table, available at the source was last updated. It is in the Unix timestamp format. |
till_id | Bigint | Unique identifier assigned to the point-of-sale (POS) terminal within a store. |
type | String | Identifies whether a store is genuine or used for testing. If the store is set to "general," it contributes real data. If the store is set to "admin," it is a test environment with simulated data, and needs to be excluded from reporting. |
till_description | String | Description of the point of sale (till). |
auto_update_till | Bigint | Date and time when the corresponding record in the till table, available at the source was last updated. It is in the Unix timestamp format. |
store_channel | String | Channel through which the store operates. Possible values are online and offline. |
external_id | String | Store identifier from the brand side. |
is_billable | String | Indicates whether the store is billable or not. |
store_id | Bigint | Unique identifier for the store. |
is_active | String | Status indicating whether the store is active or inactive. |
auto_update_store | Bigint | Date and time when the corresponding record in the store table, available at the source was last updated. It is in the Unix timestamp format. |
store_description | String | Description of the store, used for external client nomenclature. |
latitude | String | Latitude coordinate of the store's location. |
timezone | String | Timezone of the store's location. |
external_id_2 | String | Store identifier from the brand side. |
till_name | String | Name of the point of sale (till). |
zone_name | String | Name of the zone where the store is located. |
Time
Databricks table name: time
This table captures the event time, such as the hour of the day, minute of the day, etc., enabling time-based analysis and mapping of the specific event.
Column Name | Data Type | Description |
---|---|---|
time_id | Integer | Unique identifier of a date in the table. |
time | String | Represents time in HH:MM:SS For example, 12:30:12 |
hour_of_day | Integer | Hour of the day (0 to 24) |
minute_of_day | Integer | Minute of day (0 to 60) |
day_shift | String | Shift of the day (Morning/ Afternoon/ Night) |
day_shift_no | Integer | Shift Number of day (1 to 5) |
hour_range | String | Hour range of the day (0-1, 1-2, and so on) |
Program
Databricks table name: program
This dimension tables captures the metadata of loyalty programs.
Column Name | Data Type | Description |
---|---|---|
points_currency_ratio | Integer | Ratio of points to currency. It indicates how many points are equivalent to one unit of currency. |
auto_update_time | String | Date and time when the program table available at the source was last updated. It is in the Unix timestamp format. |
redeemable_point_category_id | Integer | Category ID for points that can be redeemed. |
program_id | Integer | Unique identifier of the table. |
is_active | String | Indicates whether the program is currently active or not. |
program_name | String | Name of the program. |
is_default | String | Indicates whether the program is the default program. |
description | String | Description for the program. |
Communication channel
Databricks table name: communication_channel
This table captures the metadata of the channel through which the campaign related communication is sent to the customer. This information can be used to determine the communication channels (SMS, email, etc.) that are most effective for different users.
Column Name | Data Type | Description |
---|---|---|
id | Integer | Unique identifier of the table. |
channel | String | Indicates the channel used for communication. Possible values are - SMS, email, voicemail, WeChat, Facebook, Viber, etc. |
activity_name | String | Categorizes the channel used for communication. |
Communication client
Databricks table name: communication_client
This table captures the metadata of the client associated with the communication.
Column Name | Data Type | Description |
---|---|---|
id | Integer | Unique identifier of the table. |
name | String | Name of the client associated with the communication. |
description | String | Description of the client. |
Credit type
Databricks table name: credit_type
This table captures the type of communication credit.
Column Name | Data Type | Description |
---|---|---|
id | Integer | Unique identifier of the table. |
values | String | Indicates the type of credit transaction. Possible values are, credits - Added, or Removed. |
Custom field info
Databricks table name: custom_fields_info
This table captures the metadata of custom fields, including their unique identifiers, types, names, and scopes. It also records the status of each custom field.
Column Name | Data Type | Description |
---|---|---|
auto_update_custom_fields | Bigint | Date and time when the corresponding record in the custom field table available at the source was last updated. It is in the Unix timestamp format. |
cf_id | Bigint | Unique identifier for the custom field. |
is_disabled | Integer | Indicates if the custom field is enabled or disabled. |
type | String | Type of the custom field. Possible values are - text, select, text area, date picker, radio, and check box. |
name | String | Name of the custom field. |
cf_scope | String | Scope of the custom field. Possible values are - store custom field, zone custom field, customer card, customer feedback, loyalty transaction, and loyalty registration. |
Admin users
Databricks table name: admin_users
This tables captures the metadata of the admin user associated with the event.
Column Name | Data Type | Description |
---|---|---|
is_active | Integer | Indicates if the user is active. |
is_deleted | Integer | Indicates if the user is deleted. |
middle_name | String | Middle name of the admin user. |
first_name | String | First name of the admin user. |
String | Email address of the admin user. | |
id | Bigint | Unique identifier of the table. |
last_name | String | Last name of the admin user. |
Nsadmin priority
Databricks table name: nsadmin_priority
This table captures the priority type of the nsadmin messages.
Column Name | Data Type | Description |
---|---|---|
id | Integer | Unique identifier of the table. |
priority | String | Captures the nsadmin message priority. Possible values are - High, default, and bulk. |
Gateway
Databricks table name: gateway
This table captures the metadata of the gateway used for communication.
Column Name | Data Type | Description |
---|---|---|
short_name | String | Short name of the communication gateway reference in communication settings, e.g., airtel_trans, airtel_bulk. |
auto_update_gateways | Bigint | Date and time when the corresponding record in the gateway table available at the source was last updated. It is in the Unix timestamp format. |
id | Bigint | Unique identifier of the table. |
full_name | String | Full name of the communication gateway. |
host_name | String | URL used to post messages to the gateway. |
Internal status
Databricks table name: internal_status
This table captures the metadata of the call task status.
Column Name | Data Type | Description |
---|---|---|
external_status | String | Gives the external status of the call task. Possible values are - Open, in progress, and closed. |
id | Bigint | Unique identifier of the table. |
internal_status | String | Gives the internal status of the call task. Possible values are - Open, and complete. |
Tasks
Databricks table name: tasks
This table captures the metadata of the call tasks.
Column Name | Data Type | Description |
---|---|---|
action_type | String | Type of action required for the task. Possible values are - call, and WhatsApp. |
start_date | String | The date when the task is scheduled to start. |
auto_update_task_campaign_map | Bigint | Date and time when the corresponding record in the task campaign map table available at the source was last updated. It is in the Unix timestamp format. |
task_id | Bigint | Unique identifier for the task. |
description | String | Description of the task. |
campaign | String | Name of the campaign associated with the task. |
auto_update_campaign_base | Bigint | Date and time when the corresponding record in the campaign base table available at the source was last updated. It is in the Unix timestamp format. |
created_by_type | String | Type of entity that created the task, e.g. user admin. |
end_date | String | The date by which the task needs to be completed. |
valid_days_from_create | Integer | Number of days from the creation date within which the task needs to be closed. |
auto_update_tasks | Bigint | Date and time when the corresponding record in the tasks table available at the source was last updated. It is in the Unix timestamp format. |
Loyalty type
Databricks table name: loyalty_type
Each row in this table captures the loyalty type of the customer.
Column Name | Data Type | Description |
---|---|---|
id | Bigint | Unique identifier of the table. |
type | String | Loyalty type of the customer. Possible values are - loyalty/ non loyalty/ not registered (who have not provided their contact details). |
Order Channel
Databricks table name: order_channel
This table captures the metadata of the order channel associated with the transaction.
Column Name | Data Type | Description |
---|---|---|
id | Bigint | Unique identifier of the table. |
order_channel | String | Identifier for the order channel with which the order has been placed. It indicates the specific channel through which the transaction was completed. Possible values are - online, in-store, through a mobile app, etc. |
auto_update_time | Bigint | Date and time when the corresponding record in the order_channel table, available at the source was last updated. It is in the Unix timestamp format. |
Channel Account
Databricks table name: channel_account
This table captures the metadata of the channel account used for the event.
Column Name | Data Type | Description |
---|---|---|
channel | String | Channel where the customer's profile was created. Possible values are - WhatsApp, Web_engage, Mobile_app, Line, and WeChat. |
channel_account_id | Bigint | Identifier for the channel account. |
auto_update_channels | Bigint | Date and time when the corresponding record in the channels table available at the source was last updated. It is in the Unix timestamp format. |
account_name | String | Name of the account. |
auto_update_org_channel_accounts | Bigint | Date and time when the corresponding record in the org_channel_accounts table available at the source was last updated. It is in the Unix timestamp format. |
Source Profile Type
Databricks table name: source_profile_type
This table captures the source used for customer profile creation.
Column Name | Data Type | Description |
---|---|---|
id | Bigint | Unique identifier of the table. |
type | String | Source where the customer's profile was created. If the customer's profile was created in-store, the value will be 'Instore', and if not, the value will be 'Not-instore'. |
Entity Type
Databricks table name: entity_type
This dimension table captures the metadata of the type of entity.
Column Name | Data Type | Description |
---|---|---|
id | Bigint | Unique identifier of the table. |
type | String | Type of entity. Possible values are - Customer, and Store staff. |
State
Databricks table name: state
This dimension table captures the metadata of the status of the registration process.
Column Name | Data Type | Description |
---|---|---|
id | Integer | Unique identifier of the table. |
value | String | Indicates the status of registration. Possible values are - Completed, executing, failed, queued, temporary failure, etc. |
Repeat status
Databricks table name: repeat_status
Each row in this table captures the repeat status, indicating whether the transaction done by the customer is for the first time or not.
Column Name | Data Type | Description |
---|---|---|
id | Bigint | Unique identifier of the table. |
Status | String | Provides the repeat status of the customer. Possible values are - first time, and repeat. |
Source type
Databricks table name: source_type
Each row in this table captures the metadata of the source type, specifying the source of the event (such as, in-store, online, campaigns).
Column Name | Data Type | Description |
---|---|---|
id | Integer | Unique identifier of the table. |
Type | String | Indicates the source of the event. Possible values are - instore, e-comm, newsletter, campaigns, NCA, WeChat, Facebook, etc. |
Customer slab
Databricks table name: customer_slab
This table captures the metadata of the slab to which a customer belongs.
Column Name | Data Type | Description |
---|---|---|
slab_name | String | Name of the slab. Possible values are - Platinum, diamond, gold, silver, bronze, tier 1, tier 2, member tier, VIP tier, etc. |
slab_no | Integer | Number of the slab. |
auto_update_program_slab | Unixtimestamp | Date and time when the corresponding record in the program slab table available at the source was last updated. It is in the Unix timestamp format. |
serial_no | Integer | Unique identifier of the table. |
SCD type
Databricks table name: scd_type
SCD, or Slowly Changing Dimension, is a type of customer segmentation to track the behaviour of customers over time. This table captures the SCD type used for customer segmentation.
Column Name | Data Type | Description |
---|---|---|
Id | String | Unique identifier of the table. |
type | Integer | Indicates the SCD type. Possible values are - 1 and 2. |
Slab change action (slab_change_action)
Databricks table name: slab_change_action
This table captures the action associated with the customer's slab change.
Column Name | Data Type | Description |
---|---|---|
Id | Bigint | Unique identifier of the table. |
slab_change_action | String | Indicates the action associated with the slab change. Possible values are - Upgrade, downgrade, and renewal. |
Slab change source (slab_change_source)
Databricks table name: slab_change_source
This table captures the source associated with the customer's slab change
Column Name | Data Type | Description |
---|---|---|
Id | Bigint | Unique identifier of the table. |
slab_change_source | String | Indicates the source associated with the customer's slab change. Possible values are - Import, merge, rule, strategy, partner program, etc. |
Upgrade event type
Databricks table name: upgrade_event_type
This table captures event that caused the customer's slab upgrade.
Column Name | Data Type | Description |
---|---|---|
id | Integer | Unique identifier of the table. |
category | String | Category of the slab upgrade event. |
name | String | Type of event that caused the customer's slab upgrade. Possible values are - Customer registration, new bill, points redemption, voucher redemption, campaign referral, etc. |
Return type
Databricks table name: return_type
This dimension table captures the metadata of the type of return transaction. It helps identify the most common types of returns and areas for improvement.
Column Name | Data Type | Description |
---|---|---|
id | Integer | Unique identifier of the table. |
type | String | Indicates the type of return. Possible values are - Full, line item, amount, and cancelled. |
Item
Databricks table name: inventory_items
Each row in this table captures the metadata of the item, such as item code, price, size, and description.
Column Name | Data Type | Description |
---|---|---|
item_code | String | Unique code assigned to each item for identification. |
brand_name | String | Name of the brand associated with the item. |
style | String | The specific style or design of the item. |
size | String | Size of the item. |
inventory_description | String | Description for item. |
image_url | String | Link for the item image. |
color | String | Colour of the item. |
is_valid | String | Indicates whether the item is considered valid. |
item_id | Bigint | Unique identifier of the table. |
auto_update_inv_masters | Bigint | Date and time when the corresponding record in the inventory_masters table, available at the source was last updated. It is in the Unix timestamp format. |
price | String | Price of the item. |
Outlier status
Databricks table name: outlier_status
Each row in this table captures the outlier status of the bills or line items.
Column Name | Data Type | Description |
---|---|---|
id | Bigint | Unique identifier of the table. |
status | String | Captures the outlier status of the bills. Possible values are - failed, deleted, fraud, invalid, etc. |
Buyer type
Databricks table name: buyer_type
This table captures the type of buyer associated with the transaction.
Column Name | Data Type | Description |
---|---|---|
auto_update_buyer_type | bigint | Date and time when the corresponding record in the buyer_type table, available at the source was last updated. It is in the Unix timestamp format. |
buyer_type | string | Identifier for the type of buyer. Possible values are retail, wholesale, and others. |
id | bigint | Unique identifier of the table. |
Cashier
Databricks table name: cashier
This table captures the id of the cashier processing the transaction.
Column Name | Data Type | Description |
---|---|---|
id | bigint | Unique identifier of the table. |
value | string | Value for the cashier. |
Cashier name
Databricks table name: cashier_name
This table captures the name of the cashier processing the transaction.
Column Name | Data Type | Description |
---|---|---|
id | string | Unique identifier of the table. |
value | string | Name of the cashier who processed the transaction. |
Reason for the cashier discount (line item cashier discount reason)
Databricks table name: lineitem_cashier_discount_reason
This table captures the reason for discount given on the line item.
Column Name | Data Type | Description |
---|---|---|
id | bigint | Unique identifier of the table. |
discount_reason | string | Reason behind the discount applied to the bill. Possible values for discount are manager_discount, gift_card_discount, employee_discount, etc. |
Line item cashier ID
Databricks table name: lineitem_cashier_id
This table captures the identifier of the cashier who processed the transaction.
Column Name | Data Type | Description |
---|---|---|
id | bigint | Unique identifier of the table. |
value | string | Identifier of the cashier who processed the transaction. |
Line item type
Databricks table name: line_item_type
This table captures type of the line item.
Column Name | Data Type | Description |
---|---|---|
id | bigint | Unique identifier of the table. |
type | string | Type of the line item. Possible values are add on item, combo item, combo parent, and split. |
Special line item type
Databricks table name: special_lineitem_type
This table captures special type of the line item.
Column Name | Data Type | Description |
---|---|---|
id | bigint | Unique identifier of the table. |
type | string | Type of the line item. Possible values are free item, and processing fee. |
auto_update_extnd_std_enum | bigint | Date and time when the corresponding record in the extnd_std_enum table, available at the source was last updated. It is in the Unix timestamp format. |
Line item discount type
Databricks table name: lineitem_discount_type
This table captures the metadata of the type of discount applied to the line item.
Column Name | Data Type | Description |
---|---|---|
auto_update_item_discount_type | bigint | Date and time when the corresponding record in the item_discount_type table, available at the source was last updated. It is in the Unix timestamp format. |
item_discount_type | string | Type of discount applied to the line items. Possible values are flat discount, percentage discount, etc. |
id | bigint | Unique identifier of the table. |
Line item external coupon code
Databricks table name: lineitem_external_coupon_code
This table captures the external coupon code that is applied to the line item.
Column Name | Data Type | Description |
---|---|---|
id | bigint | Unique identifier of the table. |
code | string | Coupon code - if any external coupon has been applied to the line items. |
Line item return reason ID
Databricks table name: lineitem_return_reason_id
This table captures the reason for returning the line item.
Column Name | Data Type | Description |
---|---|---|
id | bigint | Unique identifier of the table. |
reason | string | Reason for returning the line item. |
Membership type
Databricks table name: membership_type
The membership type table captures the metadata of the customer membership types. This data can be used by brands to analyze the membership preferences of their customers.
Column Name | Data Type | Description |
---|---|---|
id | bigint | Unique identifier of the table. |
type | string | Specifies the membership type of the customer who completed the transaction. Possible values are - member/ not interested/ loyalty-not-interested. |
Membership card present
Databricks table name: membership_card_present
This table indicates if the customer has a membership card or not.
Column Name | Data Type | Description |
---|---|---|
id | bigint | Unique identifier of the table. |
is_present | string | Indicates whether a membership card is present for the customer or not. |
NPS score
Databricks table name: nps_score
This table captures the Net Promoter Score given by the customer.
Column Name | Data Type | Description |
---|---|---|
id | bigint | Unique identifier of the table. |
score | string | Gives the NPS (Net Promoter Score) value from 1 to 10. |
Line item promotion code
Databricks table name: lineitem_promotion_code
This table captures the promotion code that is applied to the line item.
Column Name | Data Type | Description |
---|---|---|
auto_update_item_promotion_code | bigint | Date and time when the corresponding record in the item_promotion_code table, available at the source was last updated. It is in the Unix timestamp format. |
item_promotion_code | string | Gives the line item promotion code information (such as 'SAVE20' code). |
id | bigint | Unique identifier of the table. |
Line item tax code
Databricks table name: lineitem_tax_code
This table captures the metadata of the tax code that is applied to the line item.
Column Name | Data Type | Description |
---|---|---|
auto_update_item_tax_code | bigint | Date and time when the corresponding record in the item_tax_code table, available at the source was last updated. It is in the Unix timestamp format. |
item_tax_code | string | Unique identifier or code assigned to a specific tax category. They include GST at the central and state levels, interstate GST, and taxes levied to the total bill. |
id | bigint | Unique identifier of the table. |
Card used (card_used)
Databricks table name: card_used
This table captures the details of the card used in the transaction.
Column Name | Data Type | Description |
---|---|---|
user_id | String | Identifier for the customer who holds the card. |
number | String | Unique card number assigned to the customer. |
is_active | String | Indicates whether the card is active (1 for yes, 0 for no). |
external_id | String | Card external id is a unique identifier assigned by brands to loyalty cards generated by the Capillary system. This identifier allows brands to tag and reference the cards within their own systems. |
is_generated | String | Indicates whether the card number was system-generated (1 for yes, 0 for no). |
created_by | String | Identifier of the user that created the card record. |
auto_update_time | String | Date and time when the time table available at the source, was last updated. It is in the Unix timestamp format. |
issued_date | String | Date the card was issued. It is in the Unix timestamp format. |
last_updated_by | String | Identifier of the user that last updated the card record. |
series_id | String | Identifier for the card series. |
expiry_date | String | The expiration date of the card. |
created_on | String | Date when the card was created, in the YYYY-MM-DD format. |
id | String | Unique identifier for the table. |
Unsubscription status
Databricks table name: unsubscription_status
Each row in this table captures the subscription status of customers. This status indicates that, if a customer has chosen to unsubscribe from a communication, they will no longer receive those messages.
Column Name | Data Type | Description |
---|---|---|
id | integer | Unique identifier of the table. |
status | string | Gives the subscription status of the customer. Possible values are - Opted_out, and Not_Yet. |
Campaign delivery status
Databricks table name: campaign_delivery_status
Each row in this table captures the metadata of the delivery status of the campaign message. This information can be used by brands to analyse the user behaviour based on the status of the message delivery.
Column Name | Data Type | Description |
---|---|---|
status_id | bigint | Unique identifier for the table. |
veneno_status_label | string | Delivery status of the campaign message. Possible values are - Delayed_delivery, Clicked, Opened, Sent, Delivered, Not_delivered, and Failed. |
campaign_legend_status_id | integer | Identifier for the campaign legend label (ranges from 0 to 13). |
campaign_legend_label | string | Gives a generic label for the veneno status. For instance if the veneno status is RETRY_TIMEOUT, campaign legend label will be - System error. |
Campaigns
Databricks table name: campaigns
The campaigns dimension table stores the metadata of the campaign, such as the campaign ID, type, name of the campaign, start date and end date of the campaign.
Column Name | Data Type | Description |
---|---|---|
is_recurring | string | Indicates whether the campaign is a recurring campaign or a one-time campaign. |
campaign_end_date | string | End date of the campaign. |
is_migrated | string | The field was a part of the v1 campaign. It is no longer in use. |
auto_update_campaign | bigint | Date and time when the corresponding record in the campaign table available at the source was last updated. It is in the Unix timestamp format. |
campaign_id | bigint | Unique identifier for the campaign. |
campaign_type | string | Refers to the type of campaign. Possible values are - referral campaign, survey campaign, action campaign, etc. |
roi_type | string | The roi_type field represents the type of Return on Investment (ROI) associated with the campaign group. This field is no longer in use. |
campaign_start_date | string | Start date of the campaign. |
name | string | Name of the campaign. |
Campaign group
Databricks table name: campaign_group
The campaign audience is grouped as test group and control group. The campaign_group table captures metadata of these campaign groups. These include the group id, campaign start date, campaign end date, group type, name of the group, etc.
Column Name | Data Type | Description |
---|---|---|
is_recurring | string | Indicates whether the campaign is a recurring campaign or a one-time campaign. |
campaign_end_date | string | End date of the campaign. |
group_version_number | string | Version number associated with the campaign group as per the source. |
group_type | string | The category of the campaign group - Test or Control. |
name | string | Name of the campaign group. |
id | bigint | Unique identifier for the table. |
campaign | string | The specific campaign associated with the group. |
campaign_start_date | string | Start date of the campaign. |
roi_type | string | The roi_type field represents the type of Return on Investment (ROI) associated with the campaign group. This field is no longer in use. |
Campaign message (campaign_msg)
Databricks table name: campaign_msg
This table captures the metadata of the message that was sent as a part of the campaign. Each row represents a unique message with fields such as id, campaign, message name, message type, status, etc.
Column Name | Data Type | Description |
---|---|---|
campaign_end_date | string | End date of the campaign. |
campaign | string | Name of the campaign. |
auto_update_campaign | bigint | Date and time when the corresponding record in the campaign table available at the source was last updated. It is in the Unix timestamp format. |
msg_type | string | Type of the message, sent for the campaign. |
auto_update_msg | bigint | Date and time when the corresponding record in the msg table available at the source was last updated. It is in the Unix timestamp format. |
guid | string | Extended field created for particular org. |
status | string | Gives the status of the campaign message. Possible values are - Open, or Sent. |
id | bigint | Unique identifier of the table. |
scheduled_type | string | Indicates whether the campaign message is to be sent at a future time, or is set for a specific date, or intended to be delivered immediately. Possible values are - Scheduled, particular_date, and immediately. |
campaign_start_date | string | Start date of the campaign. |
msg_name | string | Name of the campaign message. |
is_recurring | string | Indicates whether the campaign message is recurring or not. Possible values are - True, or False. |
Communication type
Databricks table name: communication_type
This table captures the metadata for the type of communication used in the campaign, differentiating between Test Group and Control Group. It helps calculate the responder sales KPI by distinguishing test communications from control communications.
Column Name | Data Type | Description |
---|---|---|
id | integer | Unique identifier of the table. |
type | string | Type of communication. Possible values are - Test and Control. |
Payment mode
Databricks table name: payment_mode
This table captures the mode of payment used by the customer, for the transaction.
Column Name | Data Type | Description |
---|---|---|
id | integer | Unique identifier of the table. |
label | string | Captures the mode of payment. Possible values are- Netbanking, PhonePe, Mastercard, Visa card, etc. |
Payment attributes
Databricks table name: payment_attributes
This table captures the payment mode attributes such as the payment attribute name, value, and Id.
Column Name | Data Type | Description |
---|---|---|
attribute_id | bigint | Identifier for the payment attribute. |
id | bigint | Unique identifier for the table. |
attribute_name | string | Name of the payment attribute, such as Visa or MasterCard. |
auto_update_time_payment_attr | bigint | Date and time when the corresponding record in the time_payment_attr table was last updated (Unix timestamp). |
value | string | Attribute value, such as credit card or debit card. |
Bill type
Databricks table name: bill_type
This table captures the type of bill based on loyalty status of customers. 'Regular' for transactions with customer tagging and 'Not_interested' for transactions without any customer tagging.
Column Name | Data Type | Description |
---|---|---|
id | bigint | Unique identifier of the table. |
value | string | Indicates the bill type. Possible values are - Regular; and Not-interested. |
Users (users)
Databricks table name: users
Each row in this table captures the metadata of the user/ customer, such as the first name, last name, mobile number etc; facilitating customer level analysis.
Column Name | Data Type | Description |
---|---|---|
auto_update_merged_customer | Bigint | Date and time when the corresponding record in the merged_customer table, available at the source was last updated. It is in the Unix timestamp format. |
auto_update_loyalty | Bigint | Date and time when the corresponding record in the loyalty table, available at the source was last updated. It is in the Unix timestamp format. |
fraud_status | String | Indicates the fraud status of the customer. Possible values are - Not fraud, Confirmed, Not interested, and Not captured. |
test_control_bucket | String | Determines whether a user should be categorized as a test or control user for the campaign. While both test and control users are included in the campaign, only test users receive the campaign messages. |
is_merged_customer | String | Indicates if the customer is the result of merging multiple customers. |
subscription_status_email_bulk | String | Subscription status (opt-in/opt-out) for email. |
first_name | String | First name of the customer. |
slab_name | String | Slab name for the customer. For example, platinum, gold etc. |
subscription_status_wechat_bulk | String | Subscription status (opt-in/opt-out) for WeChat. |
String | Email ID of the customer. | |
last_name | String | Last name of the customer. |
merged_user_id | Bigint | User ID of the merged customer. For example, if customer A is merged with customer B, the user ID of customer A will be updated to that of customer B. |
user_id | Bigint | User ID of the customer. |
subscription_status_wechat_trans | String | Subscription status (opt-in/opt-out) for the WeChat transactions. |
subscription_status_sms_bulk | String | Subscription status (opt-in/opt-out) for SMS. |
ndnc_status | String | Indicates the Do Not Disturb status. |
subscription_status_email_trans | String | Subscription status (opt-in/opt-out) for email transaction alerts. |
test_control_status | String | Indicates if the customer belongs to the Test or the Control group. |
auto_update_fraud_user | Bigint | Date and time when the corresponding record in the fraud_user table, available at the source was last updated. It is in the Unix timestamp format. |
is_inactive | String | Indicates if the customer is inactive. |
source | String | Indicates the source of the customer. For example, Instore, WebEngage etc. |
slab_number | Integer | Slab number associated with the slab of the customer. |
auto_update_users | Bigint | Date and time when the corresponding record in the users table, available at the source was last updated. It is in the Unix timestamp format. |
registered_till_id | bigint | Registered till ID of the customer. |
auto_update_customer_enrollment | Bigint | Date and time when the corresponding record in the customer_enrollment table, available at the source was last updated. It is in the Unix timestamp format. |
slab_expiry_date | String | Expiry date of the slab. |
customer_external_id | String | External ID of the customer. |
subscription_status_sms_trans | String | Subscription status for the SMS transaction alerts. |
mobile | String | Mobile number of the customer. |
loyalty_type | String | Loyalty type of the customer. For example, loyalty, non-loyalty. |
auto_update_ndnc_status | Bigint | Date and time when the NDNC (National Do Not Disturb, Invalid, DND, or Unknown) status table available at the source, was last updated. It is in the Unix timestamp format. |
Journey status
Databricks table name: journey_status
This table captures the current status of the journey.
Column Name | Data Type | Description |
---|---|---|
id | Integer | Unique identifier of the table. |
value | String | Indicates the status of the journey. Possible values are - in-progress, paused, completed, failed, exited, and stopped. |
Journey block
Databricks table name: journey_block
This table captures the metadata of the journey block such as the journey block name, type, status, start type, end type, start and end date, etc.
Column Name | Data Type | Description |
---|---|---|
block_type | String | Captures type of the journey block. Possible values are - Entry, join, jump, reminder, incentives, WhatsApp engagement, email engagement, SMS engagement, etc. |
ends_at | String | Date when the journey meta ends. |
multi_entry_allowed | String | Indicates if multiple entries are allowed for the journey meta. Possible values are - 'true' and 'false'. |
use_tiny_url | String | Indicates if tiny URL is to be used for the journey meta. Possible values are - 'true' and 'false'. |
ou_id | Integer | Organizational unit identifier associated with the journey meta. |
start_type | String | The type of start condition for the journey meta. Possible values are - 'immediate' and 'particular date'. |
journey_meta_name | String | Name of the journey meta. |
journey_meta_id | String | Unique identifier for the journey meta. |
level | String | The level or scope of the journey meta. |
link_tracking_enabled | String | Indicates if link tracking is enabled. Possible values are - 'true' and 'false'. |
block_name | String | Name of the journey block. |
end_type | String | The type of end condition for the journey meta. Possible values are - 'never' and 'particular date'. |
block_id | String | Unique identifier for the journey block. |
encrypt_url | String | Indicates if URL is to be encrypted. Possible values are - 'true' and 'false'. |
journey_meta_group_id | String | Identifier for the group to which the journey meta group belongs. |
starts_from | String | Date from when the user journey can start for the journey meta. |
journey_version | Integer | Version number of the journey meta. |
simulation_mode | String | Indicates if the journey meta is in simulation mode. Possible values are - 'true' and 'false'. |
test_control_mode_disabled | String | Indicates if the test control mode is disabled (e.g., 'true' or 'false'). |
journey_meta_description | String | Description of the journey meta. |
journey_status | String | Indicates state of the journey meta. Possible values are - live, paused, stopped, draft, approved, rejected, etc. |
objective | String | The objective or goal of the journey meta. |
time_compression_factor | String | Factor used for compressing the time by a factor for the journey meta wait blocks. |
journey_meta_version_id | String | Identifier for the version of the journey meta. |
consent_status_for_previous_version | String | Consent status for the previous version of the journey meta. Possible values are - Sunset, stop and null. |
auto_update_time | Bigint | Date and time when the corresponding record in the journey meta version table available at the source was last updated. It is in the Unix timestamp format. |
Journey block status
Databricks table name: journey_block_status
This table captures the status of the journey block.
Column Name | Data Type | Description |
---|---|---|
id | Integer | Unique identifier of the table. |
value | String | Indicates the status of the journey block. Possible values are - completed, executing, int_wait, failed, queued, temporary_failure, and ext_wait. |
Journey block type
Databricks table name: journey_block_type
This table captures the type of journey block.
Column Name | Data Type | Description |
---|---|---|
id | Integer | Unique identifier of the table. |
value | String | Captures type of the journey block. Possible values are - wait_for_event, decision, wait_for_duration, entry, wait_since_event, mobile_push_engagement, join, sms_engagement, incentives, jump, reminder, engagement_ab_test, whatsapp_engagement, engagement, incentive, end, email_engagement, and exit. |
Rewards (rewards)
Databricks table name: rewards
This table captures the metadata of the reward, such as its name, identifier, type, etc.
Column Name | Data Type | Description |
---|---|---|
name | String | Name of the reward. |
redemption_type | String | Type of intouch reward or vendor reward. For example, INTOUCH_REWARD, VENDOR_INTOUCH_REWARD etc. |
id | Bigint | Unique identifier for the table. |
is_enabled | Integer | Indicates if the reward is active. |
reward_type | String | Type of the reward. Possible values are - Voucher, Points, and Free voucher |
Catalog promotion (reward_catalog_promotion)
Databricks table name: reward_catalog_promotion
This table captures the metadata of the rewards catalog promotion.
Column Name | Data Type | Description |
---|---|---|
name | String | Name of the catalog promotion. |
id | Bigint | Unique identifier for the table. |
language_code | String | Language code of the language used for the promotion. Possible values are - en (for English), ja (for Japanese), etc. |
Reward Payment Config Currency (reward_payment_config_currency_dim)
Databricks table name: reward_payment_config_currency_dim
This table captures the type of currency used in the payment configuration, while availing the reward by the customers.
Column Name | Data Type | Description |
---|---|---|
id | Int | Unique identifier of the table. |
value | String | Currency type used in the payment configuration. Possible values are: CONV_RATIO, POINTS and CASH. For more information click, Supported payment modes. |
Reward Owner (reward_owner_dim)
Databricks table name: reward_owner_dim
This table captures the metadata of the module to which the reward is attributed.
Column Name | Data Type | Description |
---|---|---|
owner_type | String | Module associated with the reward. |
created_on | String | Date when the reward was created. |
owner_id | String | Unique identifier associated with the owner type module. |
id | Bigint | Unique identifier of the table. |
last_updated_on | String | Date when the data was last updated. |
created_by | String | Till id or user id from which the reward was created. If created via API call, this will be the Till id; if created through the user interface (UI), it will be the User id. |
reward_id | Bigint | Identifier for the reward. |
is_active | Int | Indicates if the reward is mapped to the owner (module) or not (1 for yes, 0 for no). |
last_updated_by | String | Till id or User id from which the reward data was last updated. If updated via API call, this will be the Till id; if updated through the user interface (UI), it will be the User id. |
Reward Owner Standard Types (reward_owner_standard_dim)
Databricks table name: reward_owner_standard_dim
This table captures the module to which the reward is attributed.
Column Name | Data Type | Description |
---|---|---|
id | Int | Unique identifier of the table. |
value | String | Module to which the reward is attributed. Possible values are - Journeys, goodwill, loyalty program, milestone, and campaigns. |
Reward Payment Config (reward_payment_config_dim)
Databricks table name: reward_payment_config_dim
This table captures the metadata of the payment config by which the customer can redeem rewards.
Column Name | Data Type | Description |
---|---|---|
last_updated_on | String | Date when the payment config was last updated. |
config_type | Int | Indicates the payment mode used for availing the reward. Possible values are - Cash, conv_ratio, free, points, and points_cash. For more information, click Different payment modes for rewards in the catalog. |
last_updated_by | String | Till id used when the payment config was last updated. |
is_enabled | String | Indicates if the payment config is enabled or not. (1 for yes, 0 for no). |
created_on | Bigint | Date when the payment config was created. |
created_by | Bigint | Till id used for creating the payment config. |
id | Bigint | Unique identifier of the table. |
reward_id | Bigint | Identifier for the reward. |
Reward Redemption Types (reward_redemption_types_dim)
Databricks table name: reward_redemption_types_dim
This table captures the metadata of the redemption type associated with the reward.
Column Name | Data Type | Description |
---|---|---|
is_enabled | Int | Indicates whether the reward redemption is enabled or not. (1 for yes, 0 for no). |
created_by | String | User who created the redemption type. |
last_updated_by | String | User who last updated the redemption type. |
created_on | String | Date when the redemption type was created. |
last_updated_on | String | Date when the redemption type was last updated. |
identifier | Bigint | Unique identifier used for categorizing or indexing the reward redemption type. |
id | Bigint | Unique identifier for the table. |
name | String | Specifies the method by which the customer can redeem rewards. Possible values are: 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. VENDOR_ONLY_REWARD: These are either points or coupons created at the vendor end. VENDOR_INTOUCH_REWARD: These are vendor rewards created at the Capillary end. It is not recommended to use the Vendor Intouch reward redemption type. Instead, you can use the Vendor only reward redemption type to issue coupons directly through the vendor API. |
is_partner | Int | Indicates if the redemption type involves a partner or vendor (1 for yes, 0 for no). All the redemption types except Cart_promotions and Intouch_rewards, are partner based. |
description | String | Description providing details about the specific reward redemption type and its purpose. |
Reward Vendor (reward_vendor_dim)
Databricks table name: reward_vendor_dim
This table captures the metadata of the vendor associated with the reward.
Column Name | Data Type | Description |
---|---|---|
created_on | String | Date when the vendor is created. |
is_enabled | Int | Indicates if the vendor is enabled (1 for yes, 0 for no). |
vendor_class | String | Indicates the vendor classification used internally to identify the custom implementation. |
id | Bigint | Unique identifier for the table. |
type | String | The type of vendor, possible values are - rewards, and points. |
is_encryptiom_required | Int | Indicates if custom implementation is done for the vendor or not. (1 for yes, 0 for no). If this field is Yes, it will show value in the vendor_class field. |
name | String | The name of the reward vendor. |
last_updated_on | String | Date when the vendor information was last updated. |
Reward Vendor Redemption (reward_vendor_redemption_dim)
Databricks table name: reward_vendor_redemption_dim
This table captures the metadata of the vendor redemption of rewards.
Column Name | Data Type | Description |
---|---|---|
id | Bigint | Unique identifier for the vendor redemption. |
last_updated_on | String | Date when the vendor redemption data was last updated. |
redemption_type | Int | Indicates the type of redemption (e.g., games, auction, cart_promotion, cash_wallet, vendor_only_reward, etc). |
name | String | The name of the vendor redemption, such as miles, donation, or entry to sweepstakes, etc. |
vendor_id | Bigint | Identifier for the vendor. Corresponds to the vendor id from the vendor dimension table. |
created_on | String | Date when the vendor redemption was created. |
is_enabled | Int | Indicates whether the vendor redemption is enabled (1 for yes, 0 for no). |
response_keys | String | Specifies the keys for responses from the vendor API, such as voucher codes or unique identifiers. |
Reward Custom Fields (reward_custom_fields_dim)
Databricks table name: reward_custom_fields_dim
This table captures the metadata of the rewards custom field.
Column Name | Data Type | Description |
---|---|---|
created_by | String | Till id or User id used for creating the rewards custom field. |
is_mandatory | Int | Indicates whether the rewards custom field is mandatory (1 for yes, 0 for no). |
is_active | Int | Indicates whether the rewards custom field is active (1 for yes, 0 for no). |
created_on | String | Date when the rewards custom field was created. |
default_value | String | The default value assigned to the rewards custom field if no specific value is provided. |
last_updated_by | String | Till id or user id used when the rewards custom field was last updated. |
name | String | The name of the rewards custom field. |
data_type | String | The data type of the rewards custom field (String, Integer, Date, Boolean). |
last_updated_on | String | Date when the rewards custom field was last updated. |
id | Bigint | Unique identifier for the table. |
description | String | A brief description of the rewards custom field's purpose or usage. |
scope | String | Indicates scope of the rewards custom field. Possible values are REWARD, CATALOGUE_PROMOTION, and ISSUE_REWARD. |
Reward Fulfillment Scope (reward_fulfillment_details_scope_dim)
Databricks table name: reward_fulfillment_details_scope_dim
This table captures the scope of fulfillment of the reward custom field.
Column Name | Data Type | Description |
---|---|---|
last_updated_on | String | Date when the scope details were updated. |
id | Bigint | Unique identifier for the table. |
name | String | Name of the scope. Currently there is only one scope - ISSUE_REWARD. |
created_on | String | Date when the scope was created. |
Reward Fulfillment Status (reward_fulfillment_status_dim)
Databricks table name: reward_fulfillment_status_dim
This table captures the metadata of the fulfillment status of the reward.
Column Name | Data Type | Description |
---|---|---|
last_updated_on | String | Date when the fulfillment status was last updated on. |
id | Bigint | Unique identifier of the table. |
last_updated_by | String | Till id or User id used when the fulfillment status was last updated. |
is_enabled | Int | Indicator of whether the fulfillment status is enabled or not (1 for yes, 0 for no). |
created_on | String | Date when the fulfillment status was created. |
name | String | Name of the fulfillment status. Possible values are - Shipped, delivered, order confirmed, on the way, delivered, voucher issued, delivering soon, in transit, etc. |
created_by | String | Till id or User id used for creating the fulfillment status. |
Reward Program Mapping (reward_program_mapping_dim)
Databricks table name: reward_program_mapping_dim
This table captures the mapping details of the loyalty program with the reward. This dimension table is not linked to any fact table (useful for manual export by brands through Databricks).
Column Name | Data Type | Description |
---|---|---|
reward_id | Bigint | Identifier for the reward. |
loyalty_program_id | Bigint | Identifier for the loyalty program associated with the reward. |
created_on | String | Date when the mapping of the loyalty program with the reward was done. |
is_enabled | Int | Indicator of whether the mapping is enabled (1 for yes, 0 for no). |
entity_type | String | Type of entity associated with the reward, such as tier, subscription, all-loyalty. Tier: Reward linked to a specific loyalty tier (e.g., Silver, Gold). Subscription: Reward tied to a subscription. All-loyalty: Reward available to all loyalty program members. |
last_updated_on | String | Date when the mapping record was last updated. |
created_by | String | Identifier for the user or system that created the mapping record. |
entity_id | Bigint | Identifier for the specific entity type associated with the reward. |
id | Bigint | Unique identifier for the table. |
last_updated_by | String | Identifier for the user or system that last updated the mapping record. |
Reward Segment (reward_segment_dim)
Databricks table name: reward_segment_dim
This table captures the mapping details of the segment associated with the reward. This dimension table is not linked to any fact table (useful for manual export by brands through Databricks).
Column Name | Data Type | Description |
---|---|---|
created_by | String | Identifier for the user or system that created the reward-segment mapping. |
segment_id | Bigint | Identifier for the customer segment to which the reward is mapped. |
last_updated_on | String | Date when the reward-segment mapping was last updated. |
last_updated_by | String | Identifier for the user or system that last updated the reward-segment mapping. |
id | Bigint | Unique identifier for the table. |
reward_id | Bigint | Identifier for the reward associated with the customer segment. |
partition_id | Bigint | Identifier for the entity/ partition mapping to the reward. |
created_on | String | Date when the mapping was done. |
is_enabled | Int | Indicator of whether the reward-segment mapping is enabled (1 for yes, 0 for no). |
Active status
Databricks table name: active_status
This table captures the metadata of the customer issue status.
Column Name | Data Type | Description |
---|---|---|
id | Bigint | Unique identifier of the table. |
value | String | Indicates the status, if it is active or not. Possible values are 0 and 1. |
Issue type
Databricks table name: issue_type
This table captures the metadata of the customer issue types.
Column Name | Data Type | Description |
---|---|---|
id | Bigint | Unique identifier of the table. |
type | String | Indicates the type or category of the issue. Possible values are - Store, and Customer. |
Reported by
Databricks table name: reported_by
This table captures the metadata of the medium used to report the customer issue.
Column Name | Data Type | Description |
---|---|---|
id | Bigint | Unique identifier of the table. |
value | String | Captures the medium through which the issue was reported. Possible values are - email, intouch, call centre, client and microsite. |
Badges meta (badgemeta)
Databricks table name: badgemeta
Each row in this table captures the metadata of the badge, such as the name of the badge, the group level details, expiry date etc. For example - The brand can use this dimension to filter all the badges, expiring on a given date, and nudge the customers to complete specified activities to retain the badge.
Column Name | Data Type | Description |
---|---|---|
badgeRank | String | The rank of a badge within a group. |
groupRank | String | The hierarchical order of various groups, determining their priority. |
expiresOn | Bigint | Expiry date of the badge. |
isActive | String | Indicates whether the badge is currently active. |
startOn | Bigint | Timestamp indicating when the badge starts. |
badgeMetaId | String | Unique identifier for each badge. This is generated during the creation of the badge. |
earnType | String | The method the brand has used to issue the badge, either "earn" or "issue earn". |
badgeName | String | Name of the badge. |
groupName | String | Name of the badge group. |
groupIsActive | String | Indicates whether the group is currently active or not |
badgeGroupId | String | Unique identifier for the badge group. |
Badge owner type (badges_owner_type)
Databricks table name: badges_owner_type
Each row in this table captures the metadata of the module to which the particular badge belongs. These modules can be Membercare, Loyalty, Journeys, Rewards etc. For example- The data in this dimension table can be used to analyse the effectiveness of badges based on the module being used to issue these badges.
Column Name | Data Type | Description |
---|---|---|
id | Integer | A unique identifier associated with the owner. |
value | String | The module for which the badge is created. Possible values are - Referral_Campaigns, Audience_Campaigns, Membercare, Rewards_Catalog, Milestones, Historical_Import, Journeys, Goodwill_Module, and Loyalty. |
Enabled
Databricks table name: enabled
Each row in this table indicates if the badge is active or not.
Column Name | Data Type | Description |
---|---|---|
id | Integer | Unique identifier for the table. |
value | String | Gives the enabled status of the badge. Possible values are: True and False. |
Milestone
Databricks table name: milestone
This table captures the metadata of milestones, such as its name, identifier, and last updated date and time.
Column Name | Data Type | Description |
---|---|---|
milestone_id | Integer | Identifier for the milestone. |
org_config_id | Integer | Identifier for the org. |
auto_update_milestone | Bigint | Date and time when the corresponding record in the milestone table available at the source was last updated. It is in the Unix timestamp format. |
id | Bigint | Unique identifier for the table. |
name | String | Name of the milestone. |
User target (user_target)
Databricks table name: user_target
This table captures the metadata of the customer milestone targets.
Column Name | Data Type | Description |
---|---|---|
recurring_cycles | Bigint | Number of times the period cycles should recur/ repeat. |
frequency | Bigint | Gives the frequency count, relevant to the frequency type. Frequency type is the frequency of each cycle in which the customer has to achieve the target value. |
target_entity | String | Entity for which we need to track the target. Possible values are - Transaction, Line item, Points, and Events. |
enrollment_type | String | Type of enrollment for the customer. Possible values are - Transaction, Import, and Audience_filter. |
targetRuleIsActive | Bigint | Indicates if the tracking is active or not. |
end_date | String | End date of the period. |
target_type | String | Type of each target, possible values are - All_points, Gross_sales, All_points, Count, Sales, Quantity, etc. |
target_cycle_start_date | Bigint | Start date and time for the target cycle. |
targetGroupCreatedOn | Bigint | Date and time when the target group is created. It is in the Unix timestamp format. |
evaluation_type | String | Refers to the type of target evaluation being used. Possible values are - Cyclic_Window, Period_Agnostic_Window, and Fixed_Calendar_Window. |
targetGroupIsActive | Bigint | Indicates if the target group is active. |
target_rule_name | String | Name of the target rule. Enables the creation of rules based on the names of milestones. |
target_group_name | String | Name of the target group. |
target_rule_id | Bigint | Identifier for the target rule. |
user_id | Bigint | Unique identifier for the customer associated with the target. |
target_value | Bigint | Represents the specific objective or goal that customers are required to achieve. Such as, $100 monthly purchase target. |
period_name | String | Name of the period. |
entityType | String | Refers to the category of customer activity that the milestone is designed to track. Possible values are - Store, Zone, and Concept. |
target_group_id | Bigint | Identifier linking each period to a target group. |
start_date | String | Start date of the period. |
user_target_id | Bigint | Unique identifier for the table. |
preferred_till_id | Bigint | Identifier assigned to the preferred till, point-of-sale (POS) terminal within a store. |
period_id | Bigint | Identifier for the period. |
entityIds | String | The IDs of the entities to which the filter applies. |
targetRuleCreatedOn | Bigint | Date and time when the target rule is created. It is in the Unix timestamp format. |
frequency_type | String | The frequency of each cycle in which the customer has to achieve the target value. Possible values are - Monthly, Quarterly, Half-yearly, Yearly, and Weekly. |
Streaks (streaks)
Databricks table name: streaks
This table captures the metadata of the streak.
Column Name | Data Type | Description |
---|---|---|
target-sequence-count | Integer | Desired streak sequence count to fulfil the requirement. Refers to the predetermined number of consecutive actions or engagements that a customer must achieve to fulfil a specific streak objective or requirement. |
streak_count | String | Name of the streak. |
streak_id | Bigint | Unique identifier of the table. |
Streak status (streak_status)
Databricks table name: streak_status
This table captures the status of the streak.
Column Name | Data Type | Description |
---|---|---|
id | Integer | Unique identifier of the table. |
value | String | Status of the streak. Possible values are - Achieved, Dropped, and In progress. |
Coupon series (coupon_series)
Databricks table name: coupon_series
Each row in this table captures the metadata of coupon series issual, associated with the badge, along with campaign and offer/discount details.
Column Name | Data Type | Description |
---|---|---|
purpose | String | Purpose of the coupon series. |
discount_type | String | Type of discount. For example, ABS - the absolute discount type where the discount value is fixed. |
description | String | Description for the coupon series. |
auto_update_campaign_base | Bigint | Date and time when the campaign_base table available at the source was last updated. It is in the Unix timestamp format. |
owner_valid_till_date | String | Validity of the card series in UTC timestamp. |
metadata | String | Metadata associated with the coupon series. |
campaign | String | Campaign associated with the coupon series. |
client_handling_type | String | Defines the method using which the coupons should be generated. DISC_CODE (generate coupon codes automatically), GENERIC (provide a common coupon code), DISC_CODE_PIN (Upload existing coupon codes). |
discount_value | String | The discount value provided through the coupon. |
auto_update_voucher_series | Bigint | Date and time when the voucher_series table available at the source was last updated. It is in the Unix timestamp format. |
campaign_id | Bigint | Campaign ID associated with the coupon series. |
expiry_strategy_type | String | Expiry strategy type. SERIES_EXPIRY - coupon expires along with the offer, MONTHS_END - Coupon expires at the end of specific month. |
valid_till_date | String | Validity of the coupon. |
series_type | String | Coupon series type. For example, Loyalty. |
series_id | Bigint | Unique coupon series ID. |
discount_code | String | Discount code associated with the series. |
expiry_strategy_value | String | Expiry strategy value for the expiry strategy type MONTH END. |
auto_update_owner_info | Bigint | Date and time when the owner_info table available at the source was last updated. It is in the Unix timestamp format. |
Coupon issual type (coupon_issual_type)
Databricks table name: coupon_issual_type
This table captures the type of coupon that has been issued.
Column Name | Data Type | Description |
---|---|---|
id | Bigint | Coupon series ID from which the coupon should be issued. |
type | String | Indicates the type of coupon issued. Possible values are - Single, Bulk, NCA. |
Entry type (entry_type)
Databricks table name: entry_type
This table captures the entry type for the coupons issual or redemption event.
Column Name | Data Type | Description |
---|---|---|
id | Bigint | Unique identifier for the table. |
value | String | Captures the entry type for the coupons issual or redemption, whether it is a manual entry or through Intouch. |
Redeemed status (redeemed_status)
Databricks table name: redeemed_status
This table captures the coupon event type (issual/ redemption).
Column Name | Data Type | Description |
---|---|---|
id | Bigint | Unique identifier for the table. |
status | String | Indicates if the coupon is issued or redeemed. |
Updated about 1 month ago