Contact Info Fact Table
Whenever a campaign is run, the brand sends multiple messages to the customers. Each row in this fact table captures the communication that is done with the customer, as a part of the campaign. It captures data such as the date and time when the customer was contacted, the time when the customer accessed the campaign email, and the channel used for communication (like SMS, Whatsapp). This fact table is linked to multiple dimension tables, which store the communication related attributes.
Databricks Table Name: contact_info
Contact Info - Entity Relationship Diagram (ERD)
Zoom in the table for more clarity. Click the table title to view its details.
Contact Info Fact Table
| Column Name | Data Type | Description | Linked Table | Availability for Export in the Contacted Template |
|---|---|---|---|---|
| auto_update_time _comm_details | bigint | Date and time when the corresponding record in the communication details table available at the source was last updated. It is in the Unix timestamp format. | _ | Yes; Measure Name: Auto Update Time Comm Details |
| auto_update_time_contact | bigint | Date and time when the corresponding record in the contact table available at the source was last updated. It is in the Unix timestamp format. | _ | Yes; Measure Name: Auto Update Time Contact |
| auto_update_time _email_stats | bigint | Date and time when the corresponding record in the email stats table available at the source was last updated. It is in the Unix timestamp format. | _ | Yes; Measure Name: Auto Update Time Email Stats |
| auto_update_time _subs_stats | bigint | Date and time when the corresponding record in the subscription status table available at the source was last updated. It is in the Unix timestamp format. | _ | Yes; Measure Name: Auto Update Time Subs Stats |
| dim_campaign _delivery_status_id | bigint | Unique identifier assigned to the campaign delivery status. Possible values are - Sent, Delivered, Not_delivered, and Failed. | 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_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_contact_date_id | bigint | Date when the customer was contacted. | date | Yes; Dimension Name: Campaign Contact Date > Date |
| dim_contact_time_id | bigint | Time when the customer was contacted. | time | Yes; Dimension Name: Campaign Contact Time > Time Id |
| dim_email_open_date_id | bigint | Date when the email within a campaign, was accessed. | date | Yes; Dimension Name: Link Open Date > Date |
| 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 | Date when the communication with the customer was done. | date | Yes; Dimension Name: Date > Date |
| dim_event_time_id | bigint | Time when the communication with the customer was done. | time | Yes; Dimension Name: Time > Time Id |
| dim_event_user_id | bigint | Unique identifier assigned to the user by Capillary. It is the primary key for this table. | 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_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 Id |
| dim_unsubscription _status_id | bigint | Unique id generated by the system for the unsubscription status. It indicates whether the customer has opted to unsubscribe or not. | unsubscription _status Link | Yes; Dimension Name: Unsubscription Status > Id |
| message_body | string | Contains the actual content of the message that was sent to the customers. | _ | Yes; Measure Name: Message Body |
| msg_id | bigint | Represents the template id generated for each message template within a campaign. It is the primary key for this table. | _ | Yes; Measure Name: Msg Id |
| nsadmin_id | bigint | Communication id corresponding to a specific communication to a customer. This represents a granular level view of a specific communication. | _ | Yes; Measure Name: Nsadmin Id |
| open_count | int | Number of times the email message has been opened. | _ | Yes; Measure Name: Open Count |
| total_link_click_count | int | Total number of times the links within an email have been clicked by the customer. | _ | Yes; Measure Name: Total Link Click Count |
| unsubscribed | string | Captures the subscription status of the customer. If the customer has unsubscribed, it will be marked as "Yes"; if not, it will be marked as "Not yet". | _ | Yes; Measure Name: Unsubscribed |
| year | int | Year of the communication. | _ | Yes; Dimension Name: Year |
| dim_communication _type_id | bigint | Identifier for the type of communication received by the customer. It is the primary key for this table. | communication _type Link | Yes; Dimension Name: Communication Type > Id |
| auto_update_time _messageMetaDetails | bigint | Date and time when the corresponding record in the messageMetaDetails table available at the source was last updated. It is in the Unix timestamp format. | _ | Yes; Measure Name: Auto Update Time Messagemetadetails |
| dim_journey _meta_block_id | string | Identifier of the journey block. It is the combination of journeyMetaId - journeyVersion - blockId. | journey_block | Yes; Dimension Name: Journey Block > Journey Meta Id |
| messageEntityId | string | Identifier of the message. | _ | Yes; Measure Name: Messageentityid |
Updated 2 months ago