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

This table captures the metadata of the promotion associated with the points allocation/ deduction.

Column NameData TypeDescription
nameStringName of the promotion associated with the points.
idBigintUnique identifier of the table, set by Capillary system.
identifierStringUnique identifier set by the brand.
auto_update_promotionBigintDate 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_idBigintIdentifier for the promotion.
typeStringType of the promotion, such as bill level or customer level.

Points Awarded Type

This table captures the type of points that are awarded.

Column NameData TypeDescription
idBigintUnique identifier of the table.
typeStringIndicates the type of points that are awarded. Possible values are listed in the table below.

Possible values for the awarded type:

ValueDescription
Point_AwardedRegular points awarded for making a transaction.
Point_Awarded_Bill_PromotionPromotional points awarded in addition to regular points at the transaction level.
Point_Awarded_LineitemRegular points awarded for purchasing a specific product.
Point_Awarded_Lineitem_PromotionPromotional points awarded in addition to regular points at the line item level.
Point_Awarded_Customer_PromotionPromotional points awarded in addition to regular points at the customer level.

Points Category

This table captures the metadata of the points category.

Column NameData TypeDescription
category_idBigintUnique identifier of the points category table.
redeemtion_typeStringType of redemption. Possible values are - Redeemable, and Non-redeemable.
auto_update_programBigintDate and time when the program table, available at the source was last updated. It is in the Unix timestamp format.
category_typeStringCategory 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_categoryBigintDate and time when the points_category table, available at the source was last updated. It is in the Unix timestamp format.

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:

ValueDescription
regular_pointsRedeemable points.
trackersThese will not appear in exports. Trackers are excluded from the point fact.
promised_pointsStrategy-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_pointsPromised 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

This table captures the type of points event (awarded or deducted).

Column NameData TypeDescription
idBigintUnique identifier of the table.
typeStringIndicates whether the points were awarded or deducted.

Deduction type

Column NameData TypeDescription
idBigintUnique identifier of the table.
typeStringIndicates the type of points deduction. Possible values are listed in the table below.

Possible values for the type of points deduction:

ValueDescription
cancelledPoints that are cancelled.
expiredPoints that are expired. These appear against the awarded_ref_id and reduce the customer's point balance in the awarded program.
migrationNot in use.
redeemedPoints that are redeemed by the customer.
returnPoints 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_transferPoints transferred from one customer's account to another. The original account reflects this deduction. To know more, refer redeemed_by_transfer.
redemption_reversalScenario 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_revertedPoints 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_revertedReversal of points redeemed against a bill when that bill is returned. To know more, refer redemption_reverted.
redeemed_by_transfer_revertedPoints 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_conversionScenario 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_historicalScenario 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_adjustmentScenario 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

Column NameData TypeDescription
idBigintUnique identifier of the table.
redemption_typeStringIdentifier for the points redemption type. Possible values are redemption, reversal, group_redemption, and reversal_on_return.

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 NameData TypeDescription
date_idBigintUnique identifier for a date in the table.
dateStringRepresents the date in the format DD-MM-YYYY (e.g., 2018-01-01).
day_of_monthIntegerDay component of the date (range: 1 to 31).
week_of_yearIntegerCalendar week of the year (range: 1 to 52).
monthStringMonth and year (e.g., Jan 2018).
yearIntegerYear in YYYY format.
quarterStringQuarterly split of the calendar year (e.g., Q1: Jan, Feb, Mar; Q2: Apr, May, Jun; etc.).
quarter_noIntegerCumulative quarter count from the beginning of the date calendar.
year_quarter_noIntegerQuarter number of the year (1 for Q1, 2 for Q2, etc.).
week_numberIntegerCumulative week count from the beginning of the date calendar.
week_start_dateStringStart date of the calendar week in DD-MM-YYYY format.
week_end_dateStringEnd date of the calendar week in DD-MM-YYYY format.
day_of_weekStringDay of the week (Monday to Sunday).
day_of_week_noIntegerDay number of the week (1 for Monday to 7 for Sunday).
month_noIntegerCumulative month count from the beginning of the date calendar.
month_no_of_yearIntegerMonth number of the year (1 for January, 2 for February, etc.).
month_of_yearStringMonth of the year (January to December).

Zone till

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 NameData TypeDescription
store_countryStringCountry where the store is located.
external_id_1StringStore identifier from the brand side.
storeStringStore where the transaction was done.
auto_update_till_parentBigintDate and time when the corresponding record in till_parent table, available at the source was last updated. It is in the Unix timestamp format.
areaStringArea of the store location.
store_nameStringStore name given by Capillary System.
store_cityStringCity where the store belongs.
store_stateStringState where the store belongs.
tillStringName of the point-of-sale (POS) terminal within a store.
auto_update_till_store_relationBigintDate 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_idBigintUnique identifier assigned to the point-of-sale (POS) terminal within a store.
typeStringIndicates the nature of the store. If set to "general," the store contributes genuine data. If set to "admin," the store functions as a test environment, containing simulated or fake data.
till_descriptionStringDescription of the point of sale (till).
auto_update_tillBigintDate 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_channelStringChannel through which the store operates. Possible values are online and offline.
external_idStringStore identifier from the brand side.
is_billableStringIndicates whether the store is billable or not.
store_idBigintUnique identifier for the store.
is_activeStringStatus indicating whether the store is active or inactive.
auto_update_storeBigintDate 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_descriptionStringDescription of the store, used for external client nomenclature.
latitudeStringLatitude coordinate of the store's location.
timezoneStringTimezone of the store's location.
external_id_2StringStore identifier from the brand side.
till_nameStringName of the point of sale (till).
zone_nameStringName of the zone where the store is located.

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 NameData TypeDescription
time_idIntegerUnique identifier of a date in the table.
timeStringRepresents time in HH:MM:SS For example, 12:30:12
hour_of_dayIntegerHour of the day (0 to 24)
minute_of_dayIntegerMinute of day (0 to 60)
day_shiftStringShift of the day (Morning/ Afternoon/ Night)
day_shift_noIntegerShift Number of day (1 to 5)
hour_rangeStringHour range of the day (0-1, 1-2, and so on)

Program

This dimension tables captures the metadata of loyalty programs.

Column NameData TypeDescription
points_currency_ratioIntegerRatio of points to currency. It indicates how many points are equivalent to one unit of currency.
auto_update_timeStringDate and time when the program table available at the source was last updated. It is in the Unix timestamp format.
redeemable_point_category_idIntegerCategory ID for points that can be redeemed.
program_idIntegerUnique identifier of the table.
is_activeStringIndicates whether the program is currently active or not.
program_nameStringName of the program.
is_defaultStringIndicates whether the program is the default program.
descriptionStringDescription for the program.

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 NameData TypeDescription
idIntegerUnique identifier of the table.
channelStringIndicates the channel used for communication. Possible values are - SMS, email, voicemail, WeChat, Facebook, Viber, etc.
activity_nameStringCategorizes the channel used for communication.

Communication client

This table captures the metadata of the client associated with the communication.

Column NameData TypeDescription
idIntegerUnique identifier of the table.
nameStringName of the client associated with the communication.
descriptionStringDescription of the client.

Credit type

This table captures the type of communication credit.

Column NameData TypeDescription
idIntegerUnique identifier of the table.
valuesStringIndicates the type of credit transaction. Possible values are, credits - Added, or Removed.

Custom field 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 NameData TypeDescription
auto_update_custom_fieldsBigintDate 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_idBigintUnique identifier for the custom field.
is_disabledIntegerIndicates if the custom field is enabled or disabled.
typeStringType of the custom field. Possible values are - text, select, text area, date picker, radio, and check box.
nameStringName of the custom field.
cf_scopeStringScope of the custom field. Possible values are - store custom field, zone custom field, customer card, customer feedback, loyalty transaction, and loyalty registration.

Admin users

This tables captures the metadata of the admin user associated with the event.

Column NameData TypeDescription
is_activeIntegerIndicates if the user is active.
is_deletedIntegerIndicates if the user is deleted.
middle_nameStringMiddle name of the admin user.
first_nameStringFirst name of the admin user.
emailStringEmail address of the admin user.
idBigintUnique identifier of the table.
last_nameStringLast name of the admin user.

Nsadmin priority

This table captures the priority type of the nsadmin messages.

Column NameData TypeDescription
idIntegerUnique identifier of the table.
priorityStringCaptures the nsadmin message priority. Possible values are - High, default, and bulk.

Gateway

This table captures the metadata of the gateway used for communication.

Column NameData TypeDescription
short_nameStringShort name of the communication gateway reference in communication settings, e.g., airtel_trans, airtel_bulk.
auto_update_gatewaysBigintDate and time when the corresponding record in the gateway table available at the source was last updated. It is in the Unix timestamp format.
idBigintUnique identifier of the table.
full_nameStringFull name of the communication gateway.
host_nameStringURL used to post messages to the gateway.

Internal status

This table captures the metadata of the call task status.

Column NameData TypeDescription
external_statusStringGives the external status of the call task. Possible values are - Open, in progress, and closed.
idBigintUnique identifier of the table.
internal_statusStringGives the internal status of the call task. Possible values are - Open, and complete.

Tasks

This table captures the metadata of the call tasks.

Column NameData TypeDescription
action_typeStringType of action required for the task. Possible values are - call, and WhatsApp.
start_dateStringThe date when the task is scheduled to start.
auto_update_task_campaign_mapBigintDate 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_idBigintUnique identifier for the task.
descriptionStringDescription of the task.
campaignStringName of the campaign associated with the task.
auto_update_campaign_baseBigintDate 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_typeStringType of entity that created the task, e.g. user admin.
end_dateStringThe date by which the task needs to be completed.
valid_days_from_createIntegerNumber of days from the creation date within which the task needs to be closed.
auto_update_tasksBigintDate 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

Each row in this table captures the loyalty type of the customer.

Column NameData TypeDescription
idBigintUnique identifier of the table.
typeStringLoyalty type of the customer. Possible values are - loyalty/ non loyalty/ not registered (who have not provided their contact details).

Order Channel

This table captures the metadata of the order channel associated with the transaction.

Column NameData TypeDescription
idBigintUnique identifier of the table.
order_channelStringIdentifier 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_timeBigintDate 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

This table captures the metadata of the channel account used for the event.

Column NameData TypeDescription
channelStringChannel where the customer's profile was created. Possible values are - WhatsApp, Web_engage, Mobile_app, Line, and WeChat.
channel_account_idBigintIdentifier for the channel account.
auto_update_channelsBigintDate 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_nameStringName of the account.
auto_update_org_channel_accountsBigintDate 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

This table captures the source used for customer profile creation.

Column NameData TypeDescription
idBigintUnique identifier of the table.
typeStringSource 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

This dimension table captures the metadata of the type of entity.

Column NameData TypeDescription
idBigintUnique identifier of the table.
typeStringType of entity. Possible values are - Customer, and Store staff.

State

This dimension table captures the metadata of the status of the registration process.

Column NameData TypeDescription
idIntegerUnique identifier of the table.
valueStringIndicates the status of registration. Possible values are - Completed, executing, failed, queued, temporary failure, etc.

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 NameData TypeDescription
idBigintUnique identifier of the table.
StatusStringProvides the repeat status of the customer. Possible values are - first time, and repeat.

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 NameData TypeDescription
idIntegerUnique identifier of the table.
TypeStringIndicates the source of the event. Possible values are - instore, e-comm, newsletter, campaigns, NCA, WeChat, Facebook, etc.

Customer slab

This table captures the metadata of the slab to which a customer belongs.

Column NameData TypeDescription
slab_nameStringName of the slab. Possible values are - Platinum, diamond, gold, silver, bronze, tier 1, tier 2, member tier, VIP tier, etc.
slab_noIntegerNumber of the slab.
auto_update_program_slabUnixtimestampDate 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_noIntegerUnique identifier of the table.

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 NameData TypeDescription
IdStringUnique identifier of the table.
typeIntegerIndicates the SCD type. Possible values are - 1 and 2.

Slab change action (slab_change_action)

This table captures the action associated with the customer's slab change.

Column NameData TypeDescription
IdBigintUnique identifier of the table.
slab_change_actionStringIndicates the action associated with the slab change. Possible values are - Upgrade, downgrade, and renewal.

Slab change source (slab_change_source)

This table captures the source associated with the customer's slab change

Column NameData TypeDescription
IdBigintUnique identifier of the table.
slab_change_sourceStringIndicates the source associated with the customer's slab change. Possible values are - Import, merge, rule, strategy, partner program, etc.

Upgrade event type

This table captures event that caused the customer's slab upgrade.

Column NameData TypeDescription
idIntegerUnique identifier of the table.
categoryStringCategory of the slab upgrade event.
nameStringType 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

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 NameData TypeDescription
idIntegerUnique identifier of the table.
typeStringIndicates the type of return. Possible values are - Full, line item, amount, and cancelled.

Item

(inventory_items)

Each row in this table captures the metadata of the item, such as item code, price, size, and description.

Column NameData TypeDescription
item_codeStringUnique code assigned to each item for identification.
brand_nameStringName of the brand associated with the item.
styleStringThe specific style or design of the item.
sizeStringSize of the item.
inventory_descriptionStringDescription for item.
image_urlStringLink for the item image.
colorStringColour of the item.
is_validStringIndicates whether the item is considered valid.
item_idBigintUnique identifier of the table.
auto_update_inv_mastersBigintDate 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.
priceStringPrice of the item.

Outlier status

Each row in this table captures the outlier status of the bills or line items.

Column NameData TypeDescription
idBigintUnique identifier of the table.
statusStringCaptures the outlier status of the bills. Possible values are - failed, deleted, fraud, invalid, etc.

Buyer type

This table captures the type of buyer associated with the transaction.

Column NameData TypeDescription
auto_update_buyer_typebigintDate 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_typestringIdentifier for the type of buyer. Possible values are retail, wholesale, and others.
idbigintUnique identifier of the table.

Cashier

This table captures the id of the cashier processing the transaction.

Column NameData TypeDescription
idbigintUnique identifier of the table.
valuestringValue for the cashier.

Cashier name

This table captures the name of the cashier processing the transaction.

Column NameData TypeDescription
idstringUnique identifier of the table.
valuestringName of the cashier who processed the transaction.

Reason for the cashier discount (line item cashier discount reason)

This table captures the reason for discount given on the line item.

Column NameData TypeDescription
idbigintUnique identifier of the table.
discount_reasonstringReason behind the discount applied to the bill. Possible values for discount are manager_discount, gift_card_discount, employee_discount, etc.

Line item cashier ID

This table captures the identifier of the cashier who processed the transaction.

Column NameData TypeDescription
idbigintUnique identifier of the table.
valuestringIdentifier of the cashier who processed the transaction.

Line item type

This table captures type of the line item.

Column NameData TypeDescription
idbigintUnique identifier of the table.
typestringType of the line item. Possible values are add on item, combo item, combo parent, and split.

Line item discount type

This table captures the metadata of the type of discount applied to the line item.

Column NameData TypeDescription
auto_update_item_discount_typebigintDate 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_typestringType of discount applied to the line items. Possible values are flat discount, percentage discount, etc.
idbigintUnique identifier of the table.

Line item external coupon code

This table captures the external coupon code that is applied to the line item.

Column NameData TypeDescription
idbigintUnique identifier of the table.
codestringCoupon code - if any external coupon has been applied to the line items.

Line item return reason ID

This table captures the reason for returning the line item.

Column NameData TypeDescription
idbigintUnique identifier of the table.
reasonstringReason for returning the line item.

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 NameData TypeDescription
idbigintUnique identifier of the table.
typestringSpecifies the membership type of the customer who completed the transaction. Possible values are - member/ not interested/ loyalty-not-interested.

Membership card present

This table indicates if the customer has a membership card or not.

Column NameData TypeDescription
idbigintUnique identifier of the table.
is_presentstringIndicates whether a membership card is present for the customer or not.

NPS score

This table captures the Net Promoter Score given by the customer.

Column NameData TypeDescription
idbigintUnique identifier of the table.
scorestringGives the NPS (Net Promoter Score) value from 1 to 10.

Line item promotion code

This table captures the promotion code that is applied to the line item.

Column NameData TypeDescription
auto_update_item_promotion_codebigintDate 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_codestringGives the line item promotion code information (such as 'SAVE20' code).
idbigintUnique identifier of the table.

Line item tax code

This table captures the metadata of the tax code that is applied to the line item.

Column NameData TypeDescription
auto_update_item_tax_codebigintDate 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_codestringUnique 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.
idbigintUnique identifier of the table.

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 NameData TypeDescription
idintegerUnique identifier of the table.
statusstringGives the subscription status of the customer. Possible values are - Opted_out, and Not_Yet.

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 NameData TypeDescription
status_idbigintUnique identifier for the table.
veneno_status_labelstringDelivery status of the campaign message. Possible values are - Delayed_delivery, Clicked, Opened, Sent, Delivered, Not_delivered, and Failed.
campaign_legend_status_idintegerIdentifier for the campaign legend label (ranges from 0 to 13).
campaign_legend_labelstringGives a generic label for the veneno status. For instance if the veneno status is RETRY_TIMEOUT, campaign legend label will be - System error.

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 NameData TypeDescription
is_recurringstringIndicates whether the campaign is a recurring campaign or a one-time campaign.
campaign_end_datestringEnd date of the campaign.
is_migratedstringThe field was a part of the v1 campaign. It is no longer in use.
auto_update_campaignbigintDate 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_idbigintUnique identifier for the campaign.
campaign_typestringRefers to the type of campaign. Possible values are - referral campaign, survey campaign, action campaign, etc.
roi_typestringThe 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_datestringStart date of the campaign.
namestringName of the campaign.

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 NameData TypeDescription
is_recurringstringIndicates whether the campaign is a recurring campaign or a one-time campaign.
campaign_end_datestringEnd date of the campaign.
group_version_numberstringVersion number associated with the campaign group as per the source.
group_typestringThe category of the campaign group - Test or Control.
namestringName of the campaign group.
idbigintUnique identifier for the table.
campaignstringThe specific campaign associated with the group.
campaign_start_datestringStart date of the campaign.
roi_typestringThe 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)

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 NameData TypeDescription
campaign_end_datestringEnd date of the campaign.
campaignstringName of the campaign.
auto_update_campaignbigintDate 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_typestringType of the message, sent for the campaign.
auto_update_msgbigintDate and time when the corresponding record in the msg table available at the source was last updated. It is in the Unix timestamp format.
guidstringExtended field created for particular org.
statusstringGives the status of the campaign message. Possible values are - Open, or Sent.
idbigintUnique identifier of the table.
scheduled_typestringIndicates 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_datestringStart date of the campaign.
msg_namestringName of the campaign message.
is_recurringstringIndicates whether the campaign message is recurring or not. Possible values are - True, or False.

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 NameData TypeDescription
idintegerUnique identifier of the table.
typestringType of communication. Possible values are - Test and Control.

Payment mode

This table captures the mode of payment used by the customer, for the transaction.

Column NameData TypeDescription
idintegerUnique identifier of the table.
labelstringCaptures the mode of payment. Possible values are- Netbanking, PhonePe, Mastercard, Visa card, etc.

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 NameData TypeDescription
idbigintUnique identifier of the table.
valuestringIndicates the bill type. Possible values are - Regular; and Not-interested.

Users (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 NameData TypeDescription
auto_update_merged_customerBigintDate 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_loyaltyBigintDate 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_statusStringIndicates the fraud status of the customer. Possible values are - Not fraud, Confirmed, Not interested, and Not captured.
test_control_bucketStringDetermines 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_customerStringIndicates if the customer is the result of merging multiple customers.
subscription_status_email_bulkStringSubscription status (opt-in/opt-out) for email.
first_nameStringFirst name of the customer.
slab_nameStringSlab name for the customer. For example, platinum, gold etc.
subscription_status_wechat_bulkStringSubscription status (opt-in/opt-out) for WeChat.
emailStringEmail ID of the customer.
last_nameStringLast name of the customer.
merged_user_idBigintUser 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_idBigintUser ID of the customer.
subscription_status_wechat_transStringSubscription status (opt-in/opt-out) for the WeChat transactions.
subscription_status_sms_bulkStringSubscription status (opt-in/opt-out) for SMS.
ndnc_statusStringIndicates the Do Not Disturb status.
subscription_status_email_transStringSubscription status (opt-in/opt-out) for email transaction alerts.
test_control_statusStringIndicates if the customer belongs to the Test or the Control group.
auto_update_fraud_userBigintDate 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_inactiveStringIndicates if the customer is inactive.
sourceStringIndicates the source of the customer. For example, Instore, WebEngage etc.
slab_numberIntegerSlab number associated with the slab of the customer.
auto_update_usersBigintDate 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_idbigintRegistered till ID of the customer.
auto_update_customer_enrollmentBigintDate 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_dateStringExpiry date of the slab.
customer_external_idStringExternal ID of the customer.
subscription_status_sms_transStringSubscription status for the SMS transaction alerts.
mobileStringMobile number of the customer.
loyalty_typeStringLoyalty type of the customer. For example, loyalty, non-loyalty.
auto_update_ndnc_statusBigintDate 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

This table captures the current status of the journey.

Column NameData TypeDescription
idIntegerUnique identifier of the table.
valueStringIndicates the status of the journey. Possible values are - in-progress, paused, completed, failed, exited, and stopped.

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 NameData TypeDescription
block_typeStringCaptures type of the journey block. Possible values are - Entry, join, jump, reminder, incentives, WhatsApp engagement, email engagement, SMS engagement, etc.
ends_atStringDate when the journey meta ends.
multi_entry_allowedStringIndicates if multiple entries are allowed for the journey meta. Possible values are - 'true' and 'false'.
use_tiny_urlStringIndicates if tiny URL is to be used for the journey meta. Possible values are - 'true' and 'false'.
ou_idIntegerOrganizational unit identifier associated with the journey meta.
start_typeStringThe type of start condition for the journey meta. Possible values are - 'immediate' and 'particular date'.
journey_meta_nameStringName of the journey meta.
journey_meta_idStringUnique identifier for the journey meta.
levelStringThe level or scope of the journey meta.
link_tracking_enabledStringIndicates if link tracking is enabled. Possible values are - 'true' and 'false'.
block_nameStringName of the journey block.
end_typeStringThe type of end condition for the journey meta. Possible values are - 'never' and 'particular date'.
block_idStringUnique identifier for the journey block.
encrypt_urlStringIndicates if URL is to be encrypted. Possible values are - 'true' and 'false'.
journey_meta_group_idStringIdentifier for the group to which the journey meta group belongs.
starts_fromStringDate from when the user journey can start for the journey meta.
journey_versionIntegerVersion number of the journey meta.
simulation_modeStringIndicates if the journey meta is in simulation mode. Possible values are - 'true' and 'false'.
test_control_mode_disabledStringIndicates if the test control mode is disabled (e.g., 'true' or 'false').
journey_meta_descriptionStringDescription of the journey meta.
journey_statusStringIndicates state of the journey meta. Possible values are - live, paused, stopped, draft, approved, rejected, etc.
objectiveStringThe objective or goal of the journey meta.
time_compression_factorStringFactor used for compressing the time by a factor for the journey meta wait blocks.
journey_meta_version_idStringIdentifier for the version of the journey meta.
consent_status_for_previous_versionStringConsent status for the previous version of the journey meta. Possible values are - Sunset, stop and null.
auto_update_timeBigintDate 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

This table captures the status of the journey block.

Column NameData TypeDescription
idIntegerUnique identifier of the table.
valueStringIndicates the status of the journey block. Possible values are - completed, executing, int_wait, failed, queued, temporary_failure, and ext_wait.

Journey block type

This table captures the type of journey block.

Column NameData TypeDescription
idIntegerUnique identifier of the table.
valueStringCaptures 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)

This table captures the metadata of the reward, such as its name, identifier, type, etc.

Column NameData TypeDescription
nameStringName of the reward.
redemption_typeStringType of intouch reward or vendor reward. For example, INTOUCH_REWARD, VENDOR_INTOUCH_REWARD etc.
idBigintUnique identifier for the table.
is_enabledIntegerIndicates if the reward is active.
reward_typeStringType of the reward. Possible values are - Voucher, Points, and Free voucher

Catalog promotion (reward_catalog_promotion)

This table captures the metadata of the rewards catalog promotion.

Column NameData TypeDescription
nameStringName of the catalog promotion.
idBigintUnique identifier for the table.
language_codeStringLanguage code of the language used for the promotion. Possible values are - en (for English), ja (for Japanese), etc.

Active status

This table captures the metadata of the customer issue status.

Column NameData TypeDescription
idBigintUnique identifier of the table.
valueStringIndicates the status, if it is active or not. Possible values are 0 and 1.

Issue type

This table captures the metadata of the customer issue types.

Column NameData TypeDescription
idBigintUnique identifier of the table.
typeStringIndicates the type or category of the issue. Possible values are - Store, and Customer.

Reported by

This table captures the metadata of the medium used to report the customer issue.

Column NameData TypeDescription
idBigintUnique identifier of the table.
valueStringCaptures the medium through which the issue was reported. Possible values are - email, intouch, call centre, client and microsite.

Badges meta (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 NameData TypeDescription
badgeRankStringThe rank of a badge within a group.
groupRankStringThe hierarchical order of various groups, determining their priority.
expiresOnBigintExpiry date of the badge.
isActiveStringIndicates whether the badge is currently active.
startOnBigintTimestamp indicating when the badge starts.
badgeMetaIdStringUnique identifier for each badge. This is generated during the creation of the badge.
earnTypeStringThe method the brand has used to issue the badge, either "earn" or "issue earn".
badgeNameStringName of the badge.
groupNameStringName of the badge group.
groupIsActiveStringIndicates whether the group is currently active or not
badgeGroupIdStringUnique identifier for the badge group.

Badge owner type (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 NameData TypeDescription
idIntegerA unique identifier associated with the owner.
valueStringThe 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

Each row in this table indicates if the badge is active or not.

Column NameData TypeDescription
idIntegerUnique identifier for the table.
valueStringGives the enabled status of the badge. Possible values are: True and False.

Milestone

This table captures the metadata of milestones, such as its name, identifier, and last updated date and time.

Column NameData TypeDescription
milestone_idIntegerIdentifier for the milestone.
org_config_idIntegerIdentifier for the org.
auto_update_milestoneBigintDate and time when the corresponding record in the milestone table available at the source was last updated. It is in the Unix timestamp format.
idBigintUnique identifier for the table.
nameStringName of the milestone.

User target (user_target)

This table captures the metadata of the customer milestone targets.

Column NameData TypeDescription
recurring_cyclesBigintNumber of times the period cycles should recur/ repeat.
frequencyBigintGives 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_entityStringEntity for which we need to track the target. Possible values are - Transaction, Line item, Points, and Events.
enrollment_typeStringType of enrollment for the customer. Possible values are - Transaction, Import, and Audience_filter.
targetRuleIsActiveBigintIndicates if the tracking is active or not.
end_dateStringEnd date of the period.
target_typeStringType of each target, possible values are - All_points, Gross_sales, All_points, Count, Sales, Quantity, etc.
target_cycle_start_dateBigintStart date and time for the target cycle.
targetGroupCreatedOnBigintDate and time when the target group is created. It is in the Unix timestamp format.
evaluation_typeStringRefers to the type of target evaluation being used. Possible values are - Cyclic_Window, Period_Agnostic_Window, and Fixed_Calendar_Window.
targetGroupIsActiveBigintIndicates if the target group is active.
target_rule_nameStringName of the target rule. Enables the creation of rules based on the names of milestones.
target_group_nameStringName of the target group.
target_rule_idBigintIdentifier for the target rule.
user_idBigintUnique identifier for the customer associated with the target.
target_valueBigintRepresents the specific objective or goal that customers are required to achieve. Such as, $100 monthly purchase target.
period_nameStringName of the period.
entityTypeStringRefers to the category of customer activity that the milestone is designed to track. Possible values are - Store, Zone, and Concept.
target_group_idBigintIdentifier linking each period to a target group.
start_dateStringStart date of the period.
user_target_idBigintUnique identifier for the table.
preferred_till_idBigintIdentifier assigned to the preferred till, point-of-sale (POS) terminal within a store.
period_idBigintIdentifier for the period.
entityIdsStringThe IDs of the entities to which the filter applies.
targetRuleCreatedOnBigintDate and time when the target rule is created. It is in the Unix timestamp format.
frequency_typeStringThe 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)

This table captures the metadata of the streak.

Column NameData TypeDescription
target-sequence-countIntegerDesired 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_countStringName of the streak.
streak_idBigintUnique identifier of the table.

Streak status (streak_status)

This table captures the status of the streak.

Column NameData TypeDescription
idIntegerUnique identifier of the table.
valueStringStatus of the streak. Possible values are - Achieved, Dropped, and In progress.

Coupon series (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 NameData TypeDescription
purposeStringPurpose of the coupon series.
discount_typeStringType of discount. For example, ABS - the absolute discount type where the discount value is fixed.
descriptionStringDescription for the coupon series.
auto_update_campaign_baseBigintDate and time when the campaign_base table available at the source was last updated. It is in the Unix timestamp format.
owner_valid_till_dateStringValidity of the card series in UTC timestamp.
metadataStringMetadata associated with the coupon series.
campaignStringCampaign associated with the coupon series.
client_handling_typeStringDefines 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_valueStringThe discount value provided through the coupon.
auto_update_voucher_seriesBigintDate and time when the voucher_series table available at the source was last updated. It is in the Unix timestamp format.
campaign_idBigintCampaign ID associated with the coupon series.
expiry_strategy_typeStringExpiry strategy type. SERIES_EXPIRY - coupon expires along with the offer, MONTHS_END - Coupon expires at the end of specific month.
valid_till_dateStringValidity of the coupon.
series_typeStringCoupon series type. For example, Loyalty.
series_idBigintUnique coupon series ID.
discount_codeStringDiscount code associated with the series.
expiry_strategy_valueStringExpiry strategy value for the expiry strategy type MONTH END.
auto_update_owner_infoBigintDate 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)

This table captures the type of coupon that has been issued.

Column NameData TypeDescription
idBigintCoupon series ID from which the coupon should be issued.
typeStringIndicates the type of coupon issued. Possible values are - Single, Bulk, NCA.

Entry type (entry_type)

This table captures the entry type for the coupons issual or redemption event.

Column NameData TypeDescription
idBigintUnique identifier for the table.
valueStringCaptures the entry type for the coupons issual or redemption, whether it is a manual entry or through Intouch.

Redeemed status (redeemed_status)

This table captures the coupon event type (issual/ redemption).

Column NameData TypeDescription
idBigintUnique identifier for the table.
statusStringIndicates if the coupon is issued or redeemed.