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 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. |
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
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. |
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
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
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
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
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
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 | Indicates 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_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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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)
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)
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
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
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
(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
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
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
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
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)
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
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
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. |
Line item 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
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
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
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
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
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
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
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. |
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
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
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
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)
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
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
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. |
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)
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
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
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
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
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)
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)
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. |
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
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
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)
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)
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
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
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)
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)
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)
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)
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)
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)
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)
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 14 days ago