Journey Reports schema

The following tables describe the reports tables.

Table 1. PointReports
Field	Type	Length	NULL?	Description
`id` (Primary Key)	BIGINT	20	No	Generated by default on null as identity
`primaryTouchPointId`	NUMBER	20
`name`	VARCHAR2	250
`journeyId`	NUMBER	20	Yes	Display the Journey id
`pointType`	VARCHAR2	250
`createdDate`	TIMESTAMP
`createdDateEpoch`	NUMBER		No	Date time stamp when the record was added in UTC timezone and Epoch forma

Table 2. JourneyReports
Field	Type	Length	NULL?	Description
`id` (Primary Key)	BIGINT	20	No	Generated by default on null as identity
`primaryJourneyId`	NUMBER	20	Yes	Display the Journey id
`name`	VARCHAR2	250	Yes	Name of Journey
`code`	VARCHAR2	11	Yes	Unique identifier for Journey. Every Journey has a unique code for identification. e.g. JS-00000104
`journeyStatus`	VARCHAR2	30	Yes	This contain ordinal NUMBER for Journey status as below: 0 - DRAFT 1 - PUBLISHED 2 - COMPLETED 3 - PAUSED
`createdDate`	TIMESTAMP	10	Yes	Display the creation date and time of record
`lastModifiedDate`	TIMESTAMP	10	Yes	Display last modified date and time of records
`publishDate`	TIMESTAMP	10	Yes	Date and time when Journey was published
`pausedDate`	TIMESTAMP	10	Yes	Date and time at which Journey is paused
`completedDate`	TIMESTAMP	10	Yes	Date and time at which Journey is completed (if goal is completed or manually by mark Journey as complete)
`journeyCreatedDate`	TIMESTAMP	10	Yes	Displays the Journey creation date.
`createdDateEpoch`	DECIMAL	15	No	Date time stamp when the record was added in UTC timezone and Epoch forma
`lastModifiedDateEpoch`	DECIMAL	15	No	Date time stamp when the record was added in UTC timezone and Epoch format.
`publishDateEpoch`	DECIMAL	15	Yes	Captures date and time in milliseconds when journey gets published for 1st time
`pausedDateEpoch`	DECIMAL	15	Yes	Captures date and time in milliseconds when journey gets paused
`completedDateEpoch`	DECIMAL	15	Yes	The date time stamp when the journey was completed
`journeyCreatedDateEpoch`	DECIMAL	15	No	Date time stamp when the record was added in UTC timezone and Epoch forma

Table 3. AudienceReports
Field	Type	Length	NULL?	Description
`id` (Primary Key)	BIGINT	20	No	Generated by default on null as identity
`primaryAudienceId`	NUMBER	20	No	Display Id of audience record
`email`	VARCHAR2	250	Yes	Email Id of audience
`mobile`	VARCHAR2	250	Yes	Displays Audience mobile number
`journeyId`	NUMBER	20	Yes	Unique Identifier for Journey
`pointId`	BIGINT	8	Yes	Displays the touchpoint ID.
`detail`	CLOB	1048576	Yes	Journeyaudience details
`eventRequestDate`	TIMESTAMP	10	Yes
`createdDate`	TIMESTAMP	10	Yes	Display the creation date and time of record
`eventRequestDateEpoch`	DECIMAL	15	Yes	Date time stamp when the record was added in UTC timezone and Epoch forma
`createdDateEpoch`	DECIMAL	15	No	Date time stamp when the record was added in UTC timezone and Epoch forma

Table 4. AudienceResponseReports
Field	Type	Length	NULL?	Description
`id` (Primary Key)	BIGINT	20	No	Generated by default on null as identity
`primaryAudienceResponseId`	BIGINT	8	Yes	Displays the primary Audience Response Id.
`audienceId`	NUMBER	20	No	Display Id of audience record
`eventName`	VARCHAR2	250	Yes	Display name of the event
`eventId`	NUMBER	20	No	Display id of AudienceResponseReports
`journeyId`	NUMBER	20	Yes	Display the Journey id
`pointId`	BIGINT	8	Yes	Displays the touchpoint ID.
`eventResponseDate`	TIMESTAMP	10	Yes
`createdDate`	TIMESTAMP	10	Yes	Display the creation date and time of record
`eventResponseDateEpoch`	DECIMAL	15	Yes	Date time stamp when the record was added in UTC timezone and Epoch forma
`createdDateEpoch`	DECIMAL	15	No	Date time stamp when the record was added in UTC timezone and Epoch forma

Table 5. AudienceRespMetaDataReports
Field	Type	Length	NULL?	Description
`id` (Primary Key)	BIGINT	20	No	Generated by default on null as identity
`primaryAudienceRespMetaDataId`	NUMBER	20	Yes	Displays the primary audience response meta data id.
`audienceResponseId`	NUMBER	20	Yes	Foreign key of the AudienceResponse table of which record the extended information it relates to
`linkURL`	VARCHAR2	250	Yes	URL of the Link which is clicked by the audience related to the responseId
`linkId`	NUMBER	20	Yes	LinkId of the Link which is clicked by the audience related to the responseId
`createdDate`	TIMESTAMP	10	Yes	Display the creation date and time of record
`createdDateEpoch`	DECIMAL	15	No	Date time stamp when the record was added in UTC timezone and Epoch forma

Table 6. AudienceRespInteractionReports
Field	Type	Length	NULL?	Description
`id` (Primary Key)	BIGINT	20	No	Generated by default on null as identity
`primaryAudienceRespIntId`	NUMBER	20	Yes	Displays the primary audience response interaction id.
`engagementSplitPointId`	NUMBER	20	N0	Display EngagementSplitPointId
`audienceResponseId`	NUMBER	20	N0	Foregin key of AudienceResponse table, Refrence column is ID
`audienceRespMetaDataId`	NUMBER	20	Yes	Displays the audience response meta data id.
`eventId`	NUMBER	20	No	Display id of AudienceRespInteractionReports
`createdDate`	TIMESTAMP	10	Yes	Display the creation date and time of record
`createdDateEpoch`	DECIMAL	15	No	Date time stamp when the record was added in UTC timezone and Epoch forma

Table 7. EmailPerformanceSent
Field	Type	Length	NULL?	Description
`id` (Primary Key)	BIGINT	20	No	Generated by default on null as identity
`journeyId`	NUMBER	20,0	Yes	Display the Journey id
`pointId`	BIGINT	8	Yes	Displays the touchpoint ID.
`pointName`	VARCHAR2	250	Yes	Displays the touchpoint name
`audienceId`	NUMBER	20	No	Display Id of audience record
`audienceEmail`	VARCHAR	250	No	Email Id of audience
`template`	VARCHAR2	100	Yes	Displays the selected email template id.
`channel`	VARCHAR2	50	Yes	Name of interactive channel associated with NBO (Next Best Offer)
`sentTimeStamp`	TIMESTAMP	10	Yes	Displays the time when email was sent.
`sentTimeStampEpoch`	DECIMAL	15	Yes	Date time stamp when the record was added in UTC timezone and Epoch format
`active`	INTEGER	4	No	Display Journey Associations active status
`iteration`	NUMBER	20	No

Table 8. EmailPerformanceDelivery
Field	Type	Length	NULL?	Description
`id` (Primary Key)	BIGINT	20	No	Generated by default on null as identity
`journeyId`	NUMBER	20,0	Yes	Display the Journey id
`pointId`	BIGINT	8	Yes	Displays the touchpoint ID.
`pointName`	VARCHAR2	250	Yes	Displays the touchpoint name
`audienceId`	NUMBER	20	No	Display Id of audience record
`audienceEmail`	VARCHAR	250	No	Email Id of audience
`template`	VARCHAR	100	Yes	Displays the selected email template id.
`channel`	VARCHAR	50	Yes	Name of interactive channel associated with NBO (Next Best Offer)
`deliveryTimeStamp`	TIMESTAMP	10	Yes	Displays the time when email was delivered.
`deliveryTimeStampEpoch`	DECIMAL	15	Yes	Date time stamp when the record was added in UTC timezone and Epoch format.
`active`	INTEGER	4	No	Display Journey Associations active status
`iteration`	NUMBER	20	No

Table 9. EmailPerformanceOpen
Field	Type	Length	NULL?	Description
`id` (Primary Key)	BIGINT	20	No	Generated by default on null as identity
`journeyId`	NUMBER	20,0	Yes	Display the Journey id
`pointId`	BIGINT	8	Yes	Displays the touchpoint ID.
`pointName`	VARCHAR2	250	Yes	Displays the touchpoint name
`audienceId`	NUMBER	20	No	Display Id of audience record
`audienceEmail`	VARCHAR	250	No	Email Id of audience
`template`	VARCHAR2	100	Yes	Displays the selected email template id.
`channel`	VARCHAR2	50	Yes	Name of interactive channel associated with NBO (Next Best Offer)
`openTimeStamp`	TIMESTAMP	10	Yes	Displays the time when email was opened.
`openTimeStampEpoch`	DECIMAL	15	Yes	Date time stamp when the record was added in UTC timezone and Epoch format.
`averageOpenInteractionTime`	NUMBER	10	Yes	Displays the average email opening time.
`active`	INTEGER	4	No	Display Journey Associations active status
`iteration`	NUMBER	20	No

Table 10. EmailPerformanceLinkClick
Field	Type	Length	NULL?	Description
`id` (Primary Key)	BIGINT	20	No	Generated by default on null as identity
`journeyId`	NUMBER	20,0	Yes	Display the Journey id
`pointId`	BIGINT	8	Yes	Displays the touchpoint ID.
`pointName`	VARCHAR2	250	Yes	Displays the touchpoint name
`audienceId`	NUMBER	20	No	Display Id of audience record
`audienceEmail`	VARCHAR	250	No	Email Id of audience
`template`	VARCHAR2	100		Displays the selected email template id.
`channel`	VARCHAR2	50	Yes	Name of interactive channel associated with NBO (Next Best Offer)
`linkId`	NUMBER	10	Yes	LinkId of the Link which is clicked by the audience related to the responseId
`linkUrl`	VARCHAR	250	Yes	URL of the Link which is clicked by the audience related to the responseId
`clickTimeStamp`	TIMESTAMP	10	Yes	Displays the time when email was clicked.
`clickTimeStampEpoch`	DECIMAL	15	Yes	Date time stamp when the record was added in UTC timezone and Epoch format.
`averageclickinteractionTime`	NUMBER	10	Yes	Displays the average email click time.
`active`	INTEGER	4	No	Display Journey Associations active status
`iteration`	NUMBER	20	No

Table 11. SmsPerformanceSent
Field	Type	Length	NULL?	Description
`id` (Primary Key)	BIGINT	20	No	Generated by default on null as identity
`journeyId`	NUMBER	20	Yes	Display the Journey id
`pointId`	BIGINT	8	Yes	Displays the touchpoint ID.
`pointName`	VARCHAR2	250	Yes	Displays the touchpoint name
`audienceId`	NUMBER	20	No	Display Id of audience record
`audienceMobileNumber`	VARCHAR	20	Yes	Displays audience mobile number.
`template`	VARCHAR2	100	Yes	Displays the selected sms template id.
`channel`	VARCHAR2	50	Yes	Name of interactive channel associated with NBO (Next Best Offer)
`sentTimeStamp`	TIMESTAMP	10	Yes	Displays the time when sms was sent.
`sentTimeStampEpoch`	DECIMAL	15	Yes	Date time stamp when the record was added in UTC timezone and Epoch format.
`active`	INTEGER	4	No	Display Journey Associations active status
`iteration`	NUMBER	20	No

Table 12. SmsPerformanceDelivery
Field	Type	Length	NULL?	Description
`id` (Primary Key)	BIGINT	20	No	Generated by default on null as identity
`journeyId`	NUMBER	20	Yes	Display the Journey id
`pointId`	BIGINT	8	Yes	Displays the touchpoint ID.
`pointName`	VARCHAR2	250	Yes	Displays the touchpoint name
`audienceId`	NUMBER	20	No	Display Id of audience record
`audienceMobileNumber`	VARCHAR	20		Displays audience mobile number.
`template`	VARCHAR2	100	Yes	Displays the selected sms template id.
`channel`	VARCHAR2	50	Yes	Name of interactive channel associated with NBO (Next Best Offer)
`deliveryTimeStamp`	TIMESTAMP	10	Yes	Displays the time when sms was delivered.
`deliveryTimeStampEpoch`	DECIMAL	15	Yes	Date time stamp when the record was added in UTC timezone and Epoch format.
`active`	INTEGER	4	No	Display Journey Associations active status
`iteration`	NUMBER	20	No

Table 13. SmsPerformanceLinkClick
Field	Type	Length	NULL?	Description
`id` (Primary Key)	BIGINT	20	No	Generated by default on null as identity
`journeyId`	NUMBER	20	Yes	Display the Journey id
`pointId`	BIGINT	20	Yes	Displays the touchpoint ID.
`pointName`	VARCHAR2	250	Yes	Displays the touchpoint name
`audienceId`	NUMBER	20	No	Display Id of audience record
`audienceMobileNumber`	VARCHAR	20	Yes	Displays audience mobile number.
`template`	VARCHAR2	100	Yes	Displays the selected sms template id.
`channel`	VARCHAR2	50	Yes	Name of interactive channel associated with NBO (Next Best Offer)
`linkId`	NUMBER	10	Yes	LinkId of the Link which is clicked by the audience related to the responseId
`linkUrl`	VARCHAR	250	Yes	URL of the Link which is clicked by the audience related to the responseId
`clickTimeStamp`	TIMESTAMP	10	Yes	Displays the time when sms was clicked.
`clickTimeStampEpoch`	DECIMAL	15	Yes	Date time stamp when the record was added in UTC timezone and Epoch format.
`averageclickinteractionTime`	NUMBER	10	Yes	Displays the average sms click time.
`active`	INTEGER	4	No	Display Journey Associations active status
`iteration`	NUMBER	20	No

Table 14. PushPerformanceSent
Field	Type	Length	NULL?	Description
`id` (Primary Key)	BIGINT	20	No	Generated by default on null as identity
`journeyId`	NUMBER	20	Yes	Display the Journey id
`pointId`	BIGINT	8	Yes	Displays the touchpoint ID.
`pointName`	VARCHAR2	250	Yes	Displays the touchpoint name
`audienceId`	NUMBER	20	No	Display Id of audience record
`audienceMobileNumber`	VARCHAR	20		Displays audience mobile number.
`template`	VARCHAR2	100	Yes	Displays the selected push template id.
`channel`	VARCHAR2	50	Yes	Name of interactive channel associated with NBO (Next Best Offer)
`sentTimeStamp`	TIMESTAMP	10	Yes	Displays the time when push was sent.
`sentTimeStampEpoch`	DECIMAL	15	Yes	Date time stamp when the record was added in UTC timezone and Epoch format.
`active`	INTEGER	4	No	Display Journey Associations active status
`iteration`	NUMBER	20	No

Table 15. PushPerformanceDelivery
Field	Type	Length	NULL?	Description
`id` (Primary Key)	BIGINT	20	No	Generated by default on null as identity
`journeyId`	NUMBER	20	Yes	Display the Journey id
`pointId`	BIGINT	8	Yes	Displays the touchpoint ID.
`pointName`	VARCHAR2	250	Yes	Displays the touchpoint name
`audienceId`	NUMBER	20	No	Display Id of audience record
`audienceMobileNumber`	VARCHAR	20	Yes	Displays audience mobile number.
`template`	VARCHAR2	100	Yes	Displays the selected push template id.
`channel`	VARCHAR2	50	Yes	Name of interactive channel associated with NBO (Next Best Offer)
`deliveryTimeStamp`	TIMESTAMP	10	Yes	Displays the time when push was delivered.
`deliveryTimeStampEpoch`	DECIMAL	15	Yes	Date time stamp when the record was added in UTC timezone and Epoch format.
`active`	INTEGER	4	No	Display Journey Associations active status
`iteration`	NUMBER	20	No

Table 16. PushPerformanceOpen
Field	Type	Length	NULL?	Description
`id` (Primary Key)	BIGINT	20	No	Generated by default on null as identity
`journeyId`	NUMBER	20	Yes	Display the Journey id
`pointId`	BIGINT	8	Yes	Displays the touchpoint ID.
`pointName`	VARCHAR2	250	Yes	Displays the touchpoint name
`audienceId`	NUMBER	20	No	Display Id of audience record
`audienceMobileNumber`	VARCHAR	20		Displays audience mobile number.
`template`	VARCHAR2	100	Yes	Displays the selected push template id.
`channel`	VARCHAR2	50	Yes	Name of interactive channel associated with NBO (Next Best Offer)
`openTimeStamp`	TIMESTAMP	10	Yes	Displays the time when push was opened.
`openTimeStampEpoch`	DECIMAL	15	Yes	Date time stamp when the record was added in UTC timezone and Epoch format.
`averageOpenInteractionTime`	NUMBER	10	Yes	Displays the average push opening time.
`active`	INTEGER	4	No	Display Journey Associations active status
`iteration`	NUMBER	20	No

Rate this topic

5 stars 4 stars 3 stars 2 stars 1 star

Comment on this topic.

By clicking this box, you acknowledge that you are NOT a U.S. Federal Government employee or agency, nor are you submitting information with respect to or on behalf of one. HCL provides software and services to U.S. Federal Government customers through its partners immixGroup, Inc. Contact this team at https://hcltechsw.com/resources/us-government-contact. Do not include any personal data in this Comment box.