Campaign summary fact table
Bi_FactCampaignSummary_s3
Field | Field Type | Description | PrimaryKey | MappedDimensionTable DimensionTableField | |
FactCampaignID | VARCHAR(36) | The unique identifier that indicates the Campaign details | Yes | ||
CenterID | VARCHAR(36) | A unique identifier for the center, generated in Centre dimension(Bi_DimCentre). | Bi_DimCenter_s3 centerid | ||
OrganizationId | VARCHAR(36) | A code that uniquely identifies an Organization | Bi_DimCenter_s3 centerid | ||
CampaignID | VARCHAR(36) | The unique identifier that indicates the Campaign details(Bi_DimCampaign). | Bi_DimCampaign_s3 campaignId | ||
CampaignStartDate | TIMESTAMP | Start date that corresponds to the 'Offer is valid for x days' configuration while creating a campaign. | |||
CampaignEndDate | TIMESTAMP | End date that corresponds to the 'Offer is valid for x days' configuration while creating a campaign. | |||
CampaignSendId | VARCHAR(36) | The code that identifies the employee that sent the campaign. | Bi_Dimuser_s3 userid | ||
CampaignSendType | VARCHAR(40) | This flag indicates the type of the campaign. 1: Email/SMS/Push Notification 3: Opportunity 5: Notes campaign | |||
CampaignSendStatus | NVARCHAR(200) | Indicates the status of the campaign. The possible values are as follows: - Cancelled - Sent - Pending | |||
CampaignSendDate | TIMESTAMP | Date and time at which the campaign notification is sent. | |||
CampaignSentBy | NVARCHAR(1024) | Name of the employee who launched the campaign. | |||
NumGuests | INT | Number of guests that meet the criteria specified in the target segment. | |||
Processed | INT | Subset of guests out of the '# of guests' field who are eligible to receive notifications. | |||
SentTo | INT | Number of notifications that were actually processed on the corresponding date for the corresponding campaign. # of sent to' = '# of dnd' + '# of mms failed' + '# of undelivered' + '# of queued' + '# of delivered' | |||
MMSFailed | INT | Number of notifications that were not delivered to the guest because of a failure at Zenoti's end. | |||
Queued | INT | Number of notifications that were successfully processed by the notifications engine, but the guest's carrier did not yet return a status. | |||
Delivered | INT | Number of notifications that were successfully delivered to the guest. | |||
UnDelivered | INT | Number of notifications that were not delivered to the guest because of a failure at the notification engine's end. | |||
DND | INT | Number of notifications that were not delivered because the guest had registered for Do Not Disturb (DND) mode. | |||
Opened | INT | Number of emails that were opened by guests. This field is applicable only for email notifications. | |||
Clicked | INT | Number of emails in which guests clicked a link. This field is applicable only for email notifications. | |||
Void | BOOLEAN | Indicates if the row should be inserted or deleted. 1- delete, 0-insert | |||
PodName | VARCHAR(20) | Internal to the ETL, says from which POD data is being synced the table | |||
groupid | BIGINT | Internal to Zenoti. Used for data processing. | |||