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 Infobill_amount: doublebill_discount: doublePKbill_id: bigintbill_number: stringdim_bill_outlier_status_id: bigintdim_campaign_delivery_status_id: bigintdim_campaign_group_id: bigintdim_campaign_id: bigintdim_campaign_message_id: bigintdim_campaign_schedule_date_id: bigintdim_campaign_schedule_time_id: bigintdim_communication_channel_id: bigintdim_communication_type_id: bigintdim_email_open_date_id: bigintdim_email_open_time_id: bigintdim_event_date_id: bigintdim_event_time_id: bigintPKdim_event_user_id: bigintdim_event_zone_till_id: bigintdim_item_id: bigintdim_latest_updated_date_id: bigintdim_latest_updated_time_id: bigintdim_line_item_outlier_status_id: bigintdim_loyalty_type_id: bigintdim_repeat_status_id: bigintdim_source_type_id: bigintdim_unsubscription_status_id: bigintdim_journey_meta_block_id: stringdim_any_touch_status_id: bigintdim_latest_touch_status_id: bigintitem_code: stringline_item_amount: doubleline_item_discount: doublePKline_item_id: bigintPKmsg_id: bigintquantity: doubleredeemed_points: doubletotal_link_click_count: intyear: intCampaign Delivery StatusLKid: integerstatus: stringCampaign Groupis_recurring: stringcampaign_end_date: stringgroup_version_number: stringgroup_type: stringname: stringLKid: bigintcampaign: stringcampaign_start_date: stringroi_type: stringCampaignsis_recurring: stringcampaign_end_date: stringis_migrated: stringauto_update_campaign: bigintLKcampaign_id: bigintcampaign_type: stringroi_type: stringcampaign_start_date: stringname: stringdateLKdate_id: intday_of_month: intweek_of_year: intmonth: stringyear: intquarter: stringweek_number: intweek_start_date: stringweek_end_date: stringday_of_week: stringmonth_no: intmonth_no_of_year: intmonth_of_year: stringday_of_week_no: intquarter_no: intyearly_quarter_no: intdate: stringDateTimeLKtime_id: biginttime: stringhour_of_day: intminute_of_day: intday_shift: stringday_shift_no: inthour_range: stringTimeCommunication ChannelLKid: integerchannel: stringactivity_name: stringCommunication ChannelCommunication TypeLKid: integertype: stringusersauto_update_merged_customer: bigintauto_update_loyalty: bigintfraud_status: stringtest_control_bucket: stringis_merged_customer: stringsubscription_status_email_bulk: stringfirst_name: stringslab_name: stringsubscription_status_wechat_bulk: stringemail: stringlast_name: stringmerged_user_id: bigintLKuser_id: bigintsubscription_status_wechat_trans: stringsubscription_status_sms_bulk: stringndnc_status: stringsubscription_status_email_trans: stringtest_control_status: stringauto_update_fraud_user: bigintis_inactive: stringsource: stringslab_number: intauto_update_users: bigintregistered_till_id: bigintauto_update_customer_enrollment: bigintslab_expiry_date: stringcustomer_external_id: stringsubscription_status_sms_trans: stringmobile: stringloyalty_type: stringauto_update_ndnc_status: bigintUsersZone_tillsstore_country: stringexternal_id_1: stringstore: stringauto_update_till_parent: bigintarea: stringstore_name: stringstore_city: stringstore_state: stringtill: stringauto_update_till_store_relation: bigintis_ffc_enabled: stringLKtill_id: biginttype: stringtill_description: stringauto_update_till: bigintstore_channel: stringexternal_id: stringis_billable: stringstore_id: bigintis_active: stringauto_update_store: bigintstore_description: stringlatitude: stringtimezone: stringexternal_id_2: stringtill_name: stringzone_name: stringZone TillsItemitem_code: stringbrand_name: stringstyle: stringsize: stringinventory_description: stringimage_url: stringcolor: stringis_valid: stringLKitem_id: bigintauto_update_inv_masters: bigintprice: stringItemOutlier_statusLKid: bigintstatus: stringOutlier StatusLoyalty_typeLKid: biginttype: stringLoyalty TypeRepeat_statusLKid: bigintstatus: stringRepeat StatusSource_typeLKid: biginttype: stringSource TypeResponse InfoCommunication TypeCampaign GroupCampaign Delivery StatusCampaignsCampaign Messagecampaign_end_date: stringcampaign: stringauto_update_campaign: bigintmsg_type: stringauto_update_msg: bigintguid: stringstatus: stringLKid: bigintscheduled_type: stringcampaign_start_date: stringmsg_name: stringis_recurring: stringCampaign MessageUnsubscription StatusLKid: intstatus: stringUnsubscription StatusJourney Blockblock_type: stringends_at: stringmulti_entry_allowed: stringuse_tiny_url: stringou_id: intstart_type: stringjourney_meta_name: stringjourney_meta_id: stringlevel: stringlink_tracking_enabled: stringblock_name: stringend_type: stringLKblock_id: stringencrypt_url: stringjourney_meta_group_id: stringstarts_from: stringjourney_version: intsimulation_mode: stringtest_control_mode_disabled: stringjourney_meta_description: stringjourney_status: stringobjective: stringtime_compression_factor: stringjourney_meta_version_id: stringconsent_status_for_previous_version: stringauto_update_time: bigintJourney Block

Legend

PK   Primary Key

LK   Linking Key

Fact Table
Dimension Table

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

Yes; Dimension Name: Communication Channel > Id

dim_communication

  • type_id

bigint

Type of communication. Possible values are - Test and Control.

communication

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

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