Dimension tables
Dimension tables provide context and additional details about the data stored in fact tables.
Points Promotion
column name | column type | Description |
---|---|---|
id | integer | Unique identifier of a promotions id in the table |
name | String | promotion's name for the points that are awarded |
type | String | Type of the promotion ex:- Bill level,Customer level |
auto_update_promotion | integer | Date and time when the points_promotions table is recently updated (Unix timestamp) |
identifier | String | - |
Points Awarded Type
id | ENUM | description |
---|---|---|
-10003 | NOT-APPLICABLE | |
-10002 | INVALID | |
-10001 | NOT-CAPTURED | |
1 | POINT_AWARDED | Regular points awarded for making transaction |
2 | POINT_AWARDED_BILL_PROMOTION | Promotional points given on top of regular point at transaction level |
3 | POINT_AWARDED_LINEITEM | Regular points awarded for purchashing a specific product |
4 | POINT_AWARDED_LINEITEM_PROMOTION | Promotional points given on top of regular point at line item level |
5 | POINT_AWARDED_CUSTOMER_PROMOTION | Promotional points given on top of regular point at customer level |
Points Category
redemption_type | category_type | category_id |
---|---|---|
NON-REDEEMABLE | PROMISED_POINTS | 1,097 |
NOT-APPLICABLE | NOT-APPLICABLE | -10,003 |
REDEEMABLE | REGULAR_POINTS | 336 |
NON-REDEEMABLE | EXTERNAL_TRIGGER_BASED_POINTS | 1,098 |
NOT-CAPTURED | NOT-CAPTURED | -10,001 |
NON-REDEEMABLE | TRACKERS | 810 |
INVALID | INVALID | -10,002 |
Points category can be of 4 types at Capillary backend, of which 3 shall be expected to show up at the
front-end in exports
‘REGULAR_POINTS’ – These are redeemable points.
‘TRACKERS’ – These shall not be available in the export.
‘PROMISED_POINTS’ – These are strategy based and return window based delayed accrual points
(conversion date is known for these and will be available in awarded_date)
‘EXTERNAL_TRIGGER_BASED_POINTS’ – These are promised points that are expected to convert to
regular points based on an externally received trigger. (conversion date is not known for these - don’t
search for this anywhere)
Trackers will not be part of point fact.
The treatment of promised points must be done based on points categories only (and not based on non-redeemable values during query writing)
Screen reader support enabled.
Points events type
id | ENUM | Description |
---|---|---|
-10003 | NOT-APPLICABLE | |
-10002 | INVALID | |
-10001 | NOT-CAPTURED | |
1 | POINTS_AWARDED | Depicts that the points have been awarded |
2 | POINTS_DEDUCTED | Depicts that the points have been deducted |
Date
Column Name | type | Description |
---|---|---|
date_id | Integer | Unique identifier of a date in the table |
date | Date | Represents date in the format of DD-MM-YYYY. For example, 2018-01-01 |
day_of_month | Integer | Stands for the day component (range between 1 to 31) |
week_of_year | Integer | Stands for the calendar week of the year ( range between 1 to 52) |
month | String | Stands for each month part Jan 2018 |
year | Integer | Year in YYYY format |
quarter | String | Quarterly split of calendar year - Q1 Jan, Feb Mar; Q2 Apr, May, Jun, and so on |
quarter_no | Integer | Denotes the cumulative quarter count from beginning of date calendar. This is of no practical use case in report or list |
year_quarter_no | Integer | Denotes quarter number of a year - 1, 2, 3, 4 (1 for Q1, 2 for Q2 and so forth) |
week_number | Integer | It is the cumulative week count from beginning of a date calendar. This is of no practical use case in report or list |
week_start_date | Date | Start date of the calendar week in DD-MM-YYYY format |
week_end_date | Date | The date in the format of DD-MM-YYYY when the calendar week end |
day_of_week | Integer | Day of a week - Monday to Sunday |
month_no | Integer | Denotes the cumulative month count from beginning of date calendar. This is of no practical use case in report or list |
month_no_of_year | Integer | Denotes the month number of a year (1 for January , 2 for February and so forth) |
month_of_year | Integer | Month of a year - January, February,..December |
Zone till
Column Name | Type | Description |
---|---|---|
till_id | Integer | Denotes point of sale identifier |
till | String | Point of sale name |
type | String | If general the store is a genuine data contributing store, if admin the store is a test store with fake data |
is_active | String | If 1 it means active otherwise its inactive. Active stores are stores where capillary software is activated |
store | String | Store code name |
store_description | String | Sometime used to capture external nomenclature of client for store |
store_name | String | Store name given by Capillary System |
zone_name | String | Name of zone |
Time
Column Name | Type | Description |
---|---|---|
time_id | Integer | Unique identifier of a date in the table |
time | time | 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 day - (0-1),(1-2)... |
Program
Column Name | Type | Description |
---|---|---|
points_currency_ratio | Interger | Ratio of the point and currency (Point/curreency) |
redeemable_point_category_id | integer | Category id for the points which are redeemable |
description | String | Descrption for the program |
program_name | String | Name of the program |
program_id | Integer | Unique identifier of the tables |
Communication channel
Column Name | Type | Description |
---|---|---|
id | Integer | Unique identifier of the tables |
channel | String | Name of the channel for which the communication happen |
activity_name | String | Name of the activity for which the communication happen |
Loyalty type
Column Name | Type | Description |
---|---|---|
id | Integer | Unique identifier of the tables |
Loyalty type | String | Type of loyalty for the user |
Order Channel
Column Name | Type | Description |
---|---|---|
id | Integer | Unique identifier of the tables |
order_channel | String | Name of the channel using that the order has been placed |
Repeat status
Column Name | Type | Description |
---|---|---|
id | Integer | Unique identifier of the tables |
Status | String | Status will be from below mentioned list FIRST_TIME INVALID NOT-APPLICABLE NOT-CAPTURED REPEAT |
Source type
Column Name | Type | Description |
---|---|---|
id | Integer | Unique identifier of the tables |
Type | String | Source type of event instore e-comm newsletter campaigns NCA MARTJACK WEB_ENGAGE TMALL OTHERS TAOBAO |
Customer slab
Column Name | Type | Description |
---|---|---|
slab_name | String | Name of the Slab |
slab_no | Integer | Number of the slab |
auto_update_program_slab | Unixtimestamo | Updated time when the program slab table the bee updated |
serial_no | Integer | Unique identifier of the tables |
SCD type
Column Name | Type | Description |
---|---|---|
Id | String | Unique identifier of the tables |
type | Integer | Type of the SCD (0,1) |
Upgrade event type
Column Name | Type | Description |
---|---|---|
id | Integer | Unique identifier of the tables |
category | String | category type for upgrade_event_type |
name | Name | Name of the categories |
Return type
Column Name | Type | Description |
---|---|---|
id | Integer | Unique identifier of the tables |
type | String | NOT-APPLICABLE INVALID NOT-CAPTURED FULL LINE_ITEM AMOUNT CANCELLED |
Item
Column Name | Type | Description |
---|---|---|
item_code | string | code for each and every item |
brand_name | String | Brand name for the item |
style | String | Style for the item |
size | String | Size of the Item |
inventory_description | String | Description for item |
image_url | Sting | Link for the item image |
color | Sting | Color of the item |
is_valid | Boolen | Stands for the validation for item |
item_id | Integer | Unique identifier of the tables |
auto_update_inv_masters | Unixtimestamp | Timestamp when the inventory master table has been updated. |
price | Integer | Price of the item |
Outlier status
Column Name | 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
Column Name | 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
Column Name | Type | Description |
---|---|---|
id | bigint | Unique identifier of the table. |
value | string | Value for the cashier. |
Cashier name
Column Name | 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)
Column Name | 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
Column Name | Type | Description |
---|---|---|
id | bigint | Unique identifier of the table. |
value | string | Identifier of the cashier who conducted the transaction. |
Line item type
Column Name | 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. |
Line item discount type
Column Name | 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
Column Name | Type | Description |
---|---|---|
id | bigint | Unique identifier of the table. |
code | string | Coupon code - if any external coupon has been applied on the line items. |
Line item return reason ID
Column Name | Type | Description |
---|---|---|
id | bigint | Unique identifier of the table. |
reason | string | Reason for returning the line item. |
Membership card present
Column Name | 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
Column Name | 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
Column Name | 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 lineitem promotion code info (such as 'SAVE20' code). |
id | bigint | Unique identifier of the table. |
Line item tax code
Column Name | 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. |
Users (users)
Column Name | 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 cistomer. |
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 we-chat 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. |
Badges meta (badgemeta)
Column Name | 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 (EARN/ISSUE EARN) the brand has used to issue the badge. |
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)
Column type | Type | Description |
---|---|---|
id | Integer | A unique identifier associated with the owner. |
value | String | The module for which the badge is created. For example, Loyalty. |
Streaks (streaks)
Column type | Type |
---|---|
target-sequence-count | Integer |
streak_count | String |
streak_id | Bigint |
Streak status (streak_status)
Column type | Type |
---|---|
id | Integer |
value | String |
Rewards (rewards)
Column Name | 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 reward |
is_enabled | Integer | Indicates if the reward is active. |
reward_type | String | Type of the reward. For example, POINTS, VOUCHER etc. |
Catalog promotion (reward_catalog_promotion)
Column name | Type | Description |
---|---|---|
name | String | Name of the catalog promotion. |
id | Bigint | Unique identifier for the catalog promotion. |
language_code | String | Language code of the language used for the promotion. For example, en. |
Coupon series (coupon_series)
Column name | 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)
Column name | Type | Description |
---|---|---|
id | Bigint | Coupon series ID from which the coupon should be issued. |
type | String |
Updated about 11 hours ago