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 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 3 months ago