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 WECHAT MARTJACK WEB_ENGAGE FACEBOOK 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

Users (users)

Column Name	Type	Description
auto_update_merged_customer	Bigint	This denotes the timestamp indicating when the merged customer table is last updated.
auto_update_loyalty	Bigint	Denotes the timestamp when the loyalty table is last updated.
fraud_status	String	Fraud status of the user.
test_control_bucket	String	Assists in determining 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 user.
slab_name	String	Slab name for the user. For example, platinum, gold etc.
subscription_status_wechat_bulk	String	Subscription status (opt-in/opt-out) for wechat.
email	String	email ID of the user.
last_name	String	Last name of the user
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	This denotes the timestamp indicating when the fraud user table is last updated.
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	Denotes the timestamp when the users table is last updated.
registered_till_id	bigint	Registered till ID of the customer.
auto_update_customer_enrollment	Bigint	This denotes the timestamp indicating when the customer enrollment table is last updated.
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	Denotes the timestamp when the NDNC (National Do Not Disturb, Invalid, DND, or Unknown) status table is last updated.

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
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
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

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 1 month ago