Automation Data View Example
Using Views to Simplify Reporting
Creating Views Over Automation Entities
These views encapsulate filtering to specific Automation entities and selecting out all useful information from the JSON payload into named columns.
See Appendix for view SQL
Get All rows for a Single Data Hub Entity
This example gets all Automation Assignments. No filtering or JSON parsing is needed because that is done by the view.
SELECT
sourceId AS [Automation ID],
AutomationName AS [Automation Name],
dateAddedInSourceSystem AS [Date Started],
DateEnded AS [Date Ended],
candidateId AS [ATS Candidate ID],
jobOrderId AS [ATS Job ID],
placementId AS [ATS Placement ID]
FROM DataHub_Automation_AutomationAssignments
| Automation ID | Automation Name | Date Started | Date Ended | ATS Candidate ID | ATS Job ID | ATS Placement ID |
|---|---|---|---|---|---|---|
| 1 | Add Credentials | 2024-01-01 | 2024-01-05 | 123 | 456 | 789 |
| 2 | Email Reminder | 2024-01-15 | 234 | 345 | 567 |
Filtering and Aggregating by Entity Specific Data
This example shows the count of Automations that have finished in 2024, broken down by entity.
SELECT EntityType AS [ATS Entity], COUNT(*) AS [Number of Automations Run]
FROM DataHub_Automation_AutomationAssignments
WHERE DateEnded >= '2024-01-01'
GROUP BY EntityType
| ATS Entity | Number of Autmations Completed |
|---|---|
| Candidate | 5052 |
| Job | 145 |
Joining to ATS entities
This example uses the top level ATS entity id fields to join from Automation Assignments to Placements. The ATS entity ID fields will only be populated where a link exists, otherwise they will be empty.
SELECT
aa.sourceId AS [Automation ID],
aa.AutomationName AS [Automation Name],
j.title AS [Job Title],
c.name AS [Candidate Name],
p.fee AS [Placement Fee]
FROM DataHub_Automation_AutomationAssignments aa
JOIN dbo.Placement p ON p.placementId = aa.placementId
LEFT JOIN dbo.Candidate c ON c.candidateID = p.candidateID
LEFT JOIN dbo.JobOrder j ON j.jobOrderID = p.jobOrderID;
| Automation ID | Automation Name | Job Title | Candidate Name | Placement Fee |
|---|---|---|---|---|
| 1 | Add Credentials | Software Developer | Jon Smith | $1,234 |
| 2 | Email Reminder | Product Manager | Mandy Jones | $2,345 |
Putting it all Together
This example uses joins, filtering and aggregation to show placement value for candidates who interacted with Automations.
SELECT
aa.sourceId AS [Automation ID],
aa.AutomationName AS [Automation Name],
SUM(p.fee) AS [Placement Fees]
FROM DataHub_Automation_AutomationAssignments aa
LEFT JOIN dbo.Placement p ON p.placementId = aa.placementId
WHERE
aa.EntityType = 'Candidate'
AND aa.dateAddedInSourceSystem < p.dateAdded
GROUP BY
aa.sourceId,
aa.AutomationName;
| Automation ID | Automation Name | Placement Fees |
|---|---|---|
| 16 | Update Candidate Skills | 1,240,534 |
| 53 | Notify Expired Credentials | 340,534 |
Appendix
SQL to create views over all Automation entities
DROP VIEW IF EXISTS DataHub_Automation_Events;
GO
CREATE VIEW DataHub_Automation_Events AS
SELECT
sourceId,
entityTypeId,
entityTypeSchemaVersionId,
dateAddedInSourceSystem,
dateLastModifiedInSourceSystem,
isDeleted,
JSON_VALUE(payload, '$.Type') AS [Type],
JSON_VALUE(payload, '$.PageTitle') AS [PageTitle],
JSON_VALUE(payload, '$.Url') AS [Url],
JSON_VALUE(payload, '$.Description') AS [Description],
JSON_VALUE(payload, '$.TextMessageTemplate_Id') AS [TextMessageTemplate_Id],
JSON_VALUE(payload, '$.EmailTemplate_Id') AS [EmailTemplate_Id],
candidateId,
clientContactId,
clientCorporationId,
jobOrderId,
jobSubmissionId,
placementId,
leadId,
opportunityId,
corporateUserId,
noteId,
appointmentId,
payableChargeId,
billableChargeId
FROM dbo.EdsData
WHERE entityTypeId = (SELECT edsEntityTypeId FROM dbo.EdsEntityType WHERE name = 'Events' AND sourceSystemId = (SELECT edsSourceSystemId FROM dbo.EdsSourceSystem WHERE name = 'Automation'));
GO
DROP VIEW IF EXISTS DataHub_Automation_AutomationAssignments;
GO
CREATE VIEW DataHub_Automation_AutomationAssignments AS
SELECT
sourceId,
entityTypeId,
entityTypeSchemaVersionId,
dateAddedInSourceSystem,
dateLastModifiedInSourceSystem,
isDeleted,
JSON_VALUE(payload, '$.DateEnded') AS [DateEnded],
JSON_VALUE(payload, '$.Status') AS [Status],
JSON_VALUE(payload, '$.AutomationName') AS [AutomationName],
JSON_VALUE(payload, '$.EntityId') AS [EntityId],
JSON_VALUE(payload, '$.EntityType') AS [EntityType],
candidateId,
clientContactId,
clientCorporationId,
jobOrderId,
jobSubmissionId,
placementId,
leadId,
opportunityId,
corporateUserId,
noteId,
appointmentId,
payableChargeId,
billableChargeId
FROM dbo.EdsData
WHERE entityTypeId = (SELECT edsEntityTypeId FROM dbo.EdsEntityType WHERE name = 'AutomationAssignments' AND sourceSystemId = (SELECT edsSourceSystemId FROM dbo.EdsSourceSystem WHERE name = 'Automation'));
GO
DROP VIEW IF EXISTS DataHub_Automation_Surveys;
GO
CREATE VIEW DataHub_Automation_Surveys AS
SELECT
sourceId,
entityTypeId,
entityTypeSchemaVersionId,
dateAddedInSourceSystem,
dateLastModifiedInSourceSystem,
isDeleted,
JSON_VALUE(payload, '$.AddResponsesToNote') AS [AddResponsesToNote],
JSON_VALUE(payload, '$.SendSurveResponsesAsCsvEmails') AS [SendSurveResponsesAsCsvEmails],
JSON_VALUE(payload, '$.CustomerPageUrl') AS [CustomerPageUrl],
JSON_VALUE(payload, '$.Type') AS [Type],
JSON_VALUE(payload, '$.SendNotificationToOwner') AS [SendNotificationToOwner],
JSON_VALUE(payload, '$.SendNotification') AS [SendNotification],
JSON_VALUE(payload, '$.NoteAction') AS [NoteAction],
JSON_VALUE(payload, '$.SubmitLabel') AS [SubmitLabel],
JSON_VALUE(payload, '$.IntroText') AS [IntroText],
JSON_VALUE(payload, '$.NotificationMessage') AS [NotificationMessage],
JSON_VALUE(payload, '$.IsUnknownVisitorChat') AS [IsUnknownVisitorChat],
JSON_VALUE(payload, '$.CompletionMessage') AS [CompletionMessage],
JSON_VALUE(payload, '$.NotificationEmails') AS [NotificationEmails],
JSON_VALUE(payload, '$.ContactType') AS [ContactType],
JSON_VALUE(payload, '$.Name') AS [Name],
JSON_VALUE(payload, '$.EntityType') AS [EntityType],
candidateId,
clientContactId,
clientCorporationId,
jobOrderId,
jobSubmissionId,
placementId,
leadId,
opportunityId,
corporateUserId,
noteId,
appointmentId,
payableChargeId,
billableChargeId
FROM dbo.EdsData
WHERE entityTypeId = (SELECT edsEntityTypeId FROM dbo.EdsEntityType WHERE name = 'Surveys' AND sourceSystemId = (SELECT edsSourceSystemId FROM dbo.EdsSourceSystem WHERE name = 'Automation'));
GO
DROP VIEW IF EXISTS DataHub_Automation_SurveyQuestions;
GO
CREATE VIEW DataHub_Automation_SurveyQuestions AS
SELECT
sourceId,
entityTypeId,
entityTypeSchemaVersionId,
dateAddedInSourceSystem,
dateLastModifiedInSourceSystem,
isDeleted,
JSON_VALUE(payload, '$.IsRequired') AS [IsRequired],
JSON_VALUE(payload, '$.Order') AS [Order],
JSON_VALUE(payload, '$.Type') AS [Type],
JSON_VALUE(payload, '$.IntValue') AS [IntValue],
JSON_VALUE(payload, '$.SurveyBranchContainer_Id') AS [SurveyBranchContainer_Id],
JSON_VALUE(payload, '$.MinScaleText') AS [MinScaleText],
JSON_VALUE(payload, '$.Scale') AS [Scale],
JSON_VALUE(payload, '$.DisplayOption') AS [DisplayOption],
JSON_VALUE(payload, '$.SurveyBranch_Id') AS [SurveyBranch_Id],
JSON_VALUE(payload, '$.Survey_Id') AS [Survey_Id],
JSON_VALUE(payload, '$.MaxScaleText') AS [MaxScaleText],
JSON_VALUE(payload, '$.QuestionText') AS [QuestionText],
candidateId,
clientContactId,
clientCorporationId,
jobOrderId,
jobSubmissionId,
placementId,
leadId,
opportunityId,
corporateUserId,
noteId,
appointmentId,
payableChargeId,
billableChargeId
FROM dbo.EdsData
WHERE entityTypeId = (SELECT edsEntityTypeId FROM dbo.EdsEntityType WHERE name = 'SurveyQuestions' AND sourceSystemId = (SELECT edsSourceSystemId FROM dbo.EdsSourceSystem WHERE name = 'Automation'));
GO
DROP VIEW IF EXISTS DataHub_Automation_SurveyAnswers;
GO
CREATE VIEW DataHub_Automation_SurveyAnswers AS
SELECT
sourceId,
entityTypeId,
entityTypeSchemaVersionId,
dateAddedInSourceSystem,
dateLastModifiedInSourceSystem,
isDeleted,
JSON_VALUE(payload, '$.Order') AS [Order],
JSON_VALUE(payload, '$.Question_Id') AS [Question_Id],
JSON_VALUE(payload, '$.SurveyBranch_Id') AS [SurveyBranch_Id],
JSON_VALUE(payload, '$.AnswerText') AS [AnswerText],
candidateId,
clientContactId,
clientCorporationId,
jobOrderId,
jobSubmissionId,
placementId,
leadId,
opportunityId,
corporateUserId,
noteId,
appointmentId,
payableChargeId,
billableChargeId
FROM dbo.EdsData
WHERE entityTypeId = (SELECT edsEntityTypeId FROM dbo.EdsEntityType WHERE name = 'SurveyAnswers' AND sourceSystemId = (SELECT edsSourceSystemId FROM dbo.EdsSourceSystem WHERE name = 'Automation'));
GO
DROP VIEW IF EXISTS DataHub_Automation_SurveySubmittals;
GO
CREATE VIEW DataHub_Automation_SurveySubmittals AS
SELECT
sourceId,
entityTypeId,
entityTypeSchemaVersionId,
dateAddedInSourceSystem,
dateLastModifiedInSourceSystem,
isDeleted,
JSON_VALUE(payload, '$.AddedNotification') AS [AddedNotification],
JSON_VALUE(payload, '$.UnknownVisitorId') AS [UnknownVisitorId],
JSON_VALUE(payload, '$.Survey_Id') AS [Survey_Id],
JSON_VALUE(payload, '$.Candidate_Id') AS [Candidate_Id],
JSON_VALUE(payload, '$.AllQuestionsAnswered') AS [AllQuestionsAnswered],
JSON_VALUE(payload, '$.EntityId') AS [EntityId],
JSON_VALUE(payload, '$.Completed') AS [Completed],
candidateId,
clientContactId,
clientCorporationId,
jobOrderId,
jobSubmissionId,
placementId,
leadId,
opportunityId,
corporateUserId,
noteId,
appointmentId,
payableChargeId,
billableChargeId
FROM dbo.EdsData
WHERE entityTypeId = (SELECT edsEntityTypeId FROM dbo.EdsEntityType WHERE name = 'SurveySubmittals' AND sourceSystemId = (SELECT edsSourceSystemId FROM dbo.EdsSourceSystem WHERE name = 'Automation'));
GO
DROP VIEW IF EXISTS DataHub_Automation_SurveyAnswerSubmittals;
GO
CREATE VIEW DataHub_Automation_SurveyAnswerSubmittals AS
SELECT
sourceId,
entityTypeId,
entityTypeSchemaVersionId,
dateAddedInSourceSystem,
dateLastModifiedInSourceSystem,
isDeleted,
JSON_VALUE(payload, '$.IntValue') AS [IntValue],
JSON_VALUE(payload, '$.DateValue') AS [DateValue],
JSON_VALUE(payload, '$.Answer_Id') AS [Answer_Id],
JSON_VALUE(payload, '$.Candidate_Id') AS [Candidate_Id],
JSON_VALUE(payload, '$.EntityId') AS [EntityId],
JSON_VALUE(payload, '$.SurveySubmittal_Id') AS [SurveySubmittal_Id],
JSON_VALUE(payload, '$.StringValue') AS [StringValue],
candidateId,
clientContactId,
clientCorporationId,
jobOrderId,
jobSubmissionId,
placementId,
leadId,
opportunityId,
corporateUserId,
noteId,
appointmentId,
payableChargeId,
billableChargeId
FROM dbo.EdsData
WHERE entityTypeId = (SELECT edsEntityTypeId FROM dbo.EdsEntityType WHERE name = 'SurveyAnswerSubmittals' AND sourceSystemId = (SELECT edsSourceSystemId FROM dbo.EdsSourceSystem WHERE name = 'Automation'));
GO
DROP VIEW IF EXISTS DataHub_Automation_SurveyNpsAnswerSubmittals;
GO
CREATE VIEW DataHub_Automation_SurveyNpsAnswerSubmittals AS
SELECT
sourceId,
entityTypeId,
entityTypeSchemaVersionId,
dateAddedInSourceSystem,
dateLastModifiedInSourceSystem,
isDeleted,
JSON_VALUE(payload, '$.IntValue') AS [IntValue],
JSON_VALUE(payload, '$.Scale') AS [Scale],
JSON_VALUE(payload, '$.Answer_Id') AS [Answer_Id],
JSON_VALUE(payload, '$.SurveyName') AS [SurveyName],
JSON_VALUE(payload, '$.Candidate_Id') AS [Candidate_Id],
JSON_VALUE(payload, '$.EntityId') AS [EntityId],
JSON_VALUE(payload, '$.QuestionText') AS [QuestionText],
JSON_VALUE(payload, '$.SurveySubmittal_Id') AS [SurveySubmittal_Id],
candidateId,
clientContactId,
clientCorporationId,
jobOrderId,
jobSubmissionId,
placementId,
leadId,
opportunityId,
corporateUserId,
noteId,
appointmentId,
payableChargeId,
billableChargeId
FROM dbo.EdsData
WHERE entityTypeId = (SELECT edsEntityTypeId FROM dbo.EdsEntityType WHERE name = 'SurveyNpsAnswerSubmittals' AND sourceSystemId = (SELECT edsSourceSystemId FROM dbo.EdsSourceSystem WHERE name = 'Automation'));
GO