Response Info Fact Table
This table captures the transaction data of customers who have responded to outbound campaigns, among those contacted during the campaign duration. The captures fields such as the bill amount, bill number, campaign details, and customer loyalty status.
Databricks Table Name: response_info
Response Info - Entity Relationship Diagram (ERD)
Zoom in the table for more clarity. Click the table title to view its details.
Response Info Fact Table
| Column Name | Data Type | Description | Linked Table | Availability for Export in the Responders Template |
|---|---|---|---|---|
| bill_amount | double | Net amount of the transaction made by the customer. Refers to the total amount of the transaction after adjusting any discounts, taxes, etc. It represents the final amount that the customer is required to pay. | - | Yes; Measure Name: Bill Amount |
| bill_discount | double | Total discount applied on the bill. Possible values are flat discount, percentage discount, etc. | - | Yes; Measure Name: Bill Discount |
| bill_id | bigint | Unique identifier assigned to a bill internally by Capillary system. | - | Yes; Measure Name: Bill Id |
| bill_number | string | Unique identifier for the transaction which is either auto-generated at the POS machine or provided manually. | - | Yes; Measure Name: Bill Number |
| dim_any_touch_status_id | bigint | To filter out invalid event date entries. | _ | _ |
| dim_bill_outlier_status_id | bigint | Captures whether a bill, identified by its bill_id, contains outlier items. | outlier_status | Yes; Dimension Name: Bill Outlier > Id |
| dim_campaign _delivery_status_id | bigint | Unique identifier assigned to the campaign delivery status. Possible values are - Sent, Delivered, Not_delivered, Failed, etc. | campaign_ delivery_status Link | Yes; Dimension Name: Campaign Delivery Status > Status Id |
| dim_campaign_group_id | bigint | Unique identifier assigned to the campaign group. Possible values are - Test and Control. | campaign_group | Yes; Dimension Name: Campaign Group > Id |
| dim_campaign_id | bigint | Unique identifier assigned to the campaign. | campaigns | Yes; Dimension Name: Campaign > Campaign Id |
| dim_campaign_message_id | bigint | Unique identifier assigned to the message sent for the campaign. | campaign_msg | Yes; Dimension Name: Campaign Message > Id |
| dim_campaign _schedule_date_id | bigint | Date when the campaign has been scheduled. | date | Yes; Dimension Name: Campaign Schedule Date > Date Id |
| dim_campaign _schedule_time_id | bigint | Time at which the campaign has been scheduled. It is the time when campaign execution starts. | time | Yes; Dimension Name: Campaign Schedule Time > Time Id |
| dim_communication _channel_id | bigint | Indicates the channel used for communicating with the customer. Possible values are - SMS, email, voicemail, Wechat, Facebook, Viber, etc. | communication _channel Link | Yes; Dimension Name: Communication Channel > Id |
| dim_communication _type_id | bigint | Type of communication. Possible values are - Test and Control. | communication _type Link | Yes; Dimension Name: Communication Type > Id |
| dim_email_open_date_id | bigint | Date when the email within a campaign was accessed. | date | Yes; Dimension Name: Link Open Date > Date Id |
| dim_email_open_time_id | bigint | Time when the email within a campaign was accessed. | time | Yes; Dimension Name: Link Open Time > Time Id |
| dim_event_date_id | bigint | Transaction date when customers contacted during the campaign made a purchase. | date | Yes; Dimension Name: Date > Date |
| dim_event_time_id | bigint | Transaction time when customers contacted during the campaign made a purchase. | time | Yes; Dimension Name: Time > Time |
| dim_event_user_id | bigint | Unique identifier assigned to the user by Capillary. | users | Yes; Dimension Name: User > User Id |
| dim_event_zone_till_id | bigint | Identifier assigned to the point-of-sale (POS) terminal within a store. It distinguishes one checkout location from another within the same store. | zone tills | Yes; Dimension Name: Store Hierarchy > Till Id |
| dim_item_id | bigint | Unique identifier generated by the system for the item. | item | Yes; Dimension Name: Product > Item Id |
| dim_journey _meta_block_id | string | Identifier of the journey block. It is the combination of journeyMetaId - journeyVersion - blockId. This value will be present only for messages related to journeys (and not campaigns). | journey_block | Yes; Dimension Name: Journey Block > Journey Meta Id |
| dim_latest_touch_status_id | bigint | Refers to whether a communication linked to a transaction is the latest one or not. It has a value of 2 if it is the latest communication, and a value of 1 if it is one of the other/ earlier communications. | _ | _ |
| dim_latest_updated_date_id | bigint | Date when the data corresponding to this event/ row is changed in the source table. | date | Yes; Dimension Name: Latest Updated Date > Date |
| dim_latest_updated_time_id | bigint | Time when the data corresponding to this event/ row is changed in the source table. | time | Yes; Dimension Name: Latest Updated Time > Time |
| dim_line_item _outlier_status_id | bigint | Identifier of the outlier status of the bills at the line item level. | outlier_status | Yes; Dimension Name: Line Item Outlier > Id |
| dim_loyalty_type_id | bigint | Identifier of the Loyalty type of the customer. Possible values are - loyalty, non loyalty, not registered (who have not provided their contact details). | loyalty_type | Yes; Dimension Name: Loyalty > Id |
| dim_repeat_status_id | bigint | Shows if the customer has made previous purchases or if it's their first transaction. | repeat_status | Yes; Dimension Name: Repeat > Id |
| dim_source_type_id | bigint | Identifier for the source of the bill. Possible values are instore, e-comm, newsletter, campaigns, NCA, Wechat, Facebook, etc. | source_type | Yes; Dimension Name: Source > Id |
| dim_unsubscription _status_id | bigint | Unique id generated by the system for the unsubscription status. Indicates if the customer has unsubscribed or not. | unsubscription _status Link | Yes; Dimension Name: Unsubscription Status > Id |
| item_code | string | Unique code assigned to a line item within the transaction. | - | Yes; Measure Name: Item Code |
| line_item_amount | double | Represents the total amount for the line item after discounts and taxes have been applied. | - | Yes; Measure Name: Line Item Amount |
| line_item_discount | double | Denotes the discount given for the particular line item. | - | Yes; Measure Name: Line Item Discount |
| line_item_id | bigint | Unique identifier generated by the system for the line item. | - | Yes; Measure Name: Line Item Id |
| msg_id | bigint | Unique identifier for the message configured in the campaign. | - | Yes; Measure Name: Msg Id |
| quantity | double | Number of units/ line items in the transaction. | - | Yes; Measure Name: Quantity |
| redeemed_points | double | Total points redeemed in the transaction. | - | Yes; Measure Name: Redeemed Points |
| total_link_click_count | integer | Total number of times the links within an email have been clicked by the customer. | - | Yes; Measure Name: Total Link Click Count |
| year | integer | Year of the transaction made by the contacted customer, as a part of the campaign. | _ | Yes; Dimension Name: Date > Year |
Updated 4 months ago