3

My Configurations:

Job Data View SQL Query to select the last 90 Days of records from the _Job Data View (here) based on SchedTime.

Send Jobs Tracking Extract (here) Setup to generate a CSV with the following:

  • Date Range: Rolling Range 90 Days
  • Extract Send Jobs = true
  • Include Test Sends = true

Note: I've confirmed that the tracking extract also filters based off of its SchedTime field.

--

Result of Data Pulls:

  • Every single record in the SendJobs Tracking Extract is in the set of the Jobs Extract
  • The Jobs Extract has records which do NOT exist in the SendJobs Tracking Extract or in the NotSent Tracking Extract.

--

The Problem: I am not able to find a pattern to determine why Jobs has more records than SendJobs.

Question: Does anyone know why the Data View has more records than the respective Tracking Extract? The problem I was originally trying to solve is that the JobStatus field is incorrect in the Data View, so SF Support suggested that I used the Tracking Extract. However, the Tracking Extract doesn't seem reliable either since it has approx. 50% fewer records as mentioned previously. Thank you.

1 Answer 1

1

I'd suggest mirroring Send SOAP object data into a DE and comparing those send jobs to your two sets. This aggregated data is for sends from account inception.

I suspect the discrepancy is related to triggered sends, which are recorded in the job data differently than batch sends. The dates only update when a publish event occurs. This is in play for traditional triggered sends, TXM Email API sends and Journey sends.

Edit

Here's some WSProxy code to create the Send DE:

var p=new Script.Util.WSProxy();var result=p.createItem("DataExtension",{"CustomerKey":"Send","Name":"Send","Description":"Send SOAP Object","Fields":[
{"CustomerKey":"ID","Name":"ID","FieldType":"Number","IsPrimaryKey":true,"IsRequired":true},
{"CustomerKey":"SendDate","Name":"SendDate","FieldType":"Date","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"EmailName","Name":"EmailName","FieldType":"Text","MaxLength":"100","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"Subject","Name":"Subject","FieldType":"Text","MaxLength":"200","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"Status","Name":"Status","FieldType":"Text","MaxLength":"15","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"Additional","Name":"Additional","FieldType":"Text","MaxLength":"50","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"BCCEmail","Name":"BCCEmail","FieldType":"EmailAddress","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"Client_ID","Name":"Client_ID","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"Client_PartnerClientKey","Name":"Client_PartnerClientKey","FieldType":"Text","MaxLength":"64","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"CreatedDate","Name":"CreatedDate","FieldType":"Date","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"Duplicates","Name":"Duplicates","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
{"CustomerKey":"Email_ID","Name":"Email_ID","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"Email_PartnerKey","Name":"Email_PartnerKey","FieldType":"Text","MaxLength":"64","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"EmailSendDefinition_CustomerKey","Name":"EmailSendDefinition_CustomerKey","FieldType":"Text","MaxLength":"36","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"EmailSendDefinition_ObjectID","Name":"EmailSendDefinition_ObjectID","FieldType":"Text","MaxLength":"36","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"SendClassification_CustomerKey","Name":"SendClassification_CustomerKey","FieldType":"Text","MaxLength":"100","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"SendClassification_Name","Name":"SendClassification_Name","FieldType":"Text","MaxLength":"100","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"SendClassification_Type","Name":"SendClassification_Type","FieldType":"Text","MaxLength":"100","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"ExistingUndeliverables","Name":"ExistingUndeliverables","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
{"CustomerKey":"ExistingUnsubscribes","Name":"ExistingUnsubscribes","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
{"CustomerKey":"ForwardedEmails","Name":"ForwardedEmails","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
{"CustomerKey":"FromAddress","Name":"FromAddress","FieldType":"EmailAddress","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"FromName","Name":"FromName","FieldType":"Text","MaxLength":"130","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"HardBounces","Name":"HardBounces","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
{"CustomerKey":"InvalidAddresses","Name":"InvalidAddresses","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
{"CustomerKey":"IsAlwaysOn","Name":"IsAlwaysOn","FieldType":"Boolean","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"IsMultipart","Name":"IsMultipart","FieldType":"Boolean","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"MissingAddresses","Name":"MissingAddresses","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
{"CustomerKey":"ModifiedDate","Name":"ModifiedDate","FieldType":"Date","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"NumberDelivered","Name":"NumberDelivered","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
{"CustomerKey":"NumberErrored","Name":"NumberErrored","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
{"CustomerKey":"NumberExcluded","Name":"NumberExcluded","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
{"CustomerKey":"NumberSent","Name":"NumberSent","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
{"CustomerKey":"NumberTargeted","Name":"NumberTargeted","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
{"CustomerKey":"OtherBounces","Name":"OtherBounces","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
{"CustomerKey":"PartnerKey","Name":"PartnerKey","FieldType":"Text","MaxLength":"64","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"PreviewURL","Name":"PreviewURL","FieldType":"Text","MaxLength":"500","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"SendLimit","Name":"SendLimit","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
{"CustomerKey":"SendWindowClose","Name":"SendWindowClose","FieldType":"Date","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"SendWindowOpen","Name":"SendWindowOpen","FieldType":"Date","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"SentDate","Name":"SentDate","FieldType":"Date","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"SoftBounces","Name":"SoftBounces","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
{"CustomerKey":"UniqueClicks","Name":"UniqueClicks","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
{"CustomerKey":"UniqueOpens","Name":"UniqueOpens","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
{"CustomerKey":"Unsubscribes","Name":"Unsubscribes","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
{"CustomerKey":"CampaignId","Name":"CampaignId","FieldType":"Number","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"0"},
{"CustomerKey":"CampaignCreatedDate","Name":"CampaignCreatedDate","FieldType":"Date","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"CampaignModifiedDate","Name":"CampaignModifiedDate","FieldType":"Date","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"CampaignName","Name":"CampaignName","FieldType":"Text","MaxLength":"50","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"CampaignDescription","Name":"CampaignDescription","FieldType":"Text","MaxLength":"500","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"CampaignCode","Name":"CampaignCode","FieldType":"Text","MaxLength":"36","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"CampaignColor","Name":"CampaignColor","FieldType":"Text","MaxLength":"6","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"CampaignFavorite","Name":"CampaignFavorite","FieldType":"Boolean","IsPrimaryKey":false,"IsRequired":false},
{"CustomerKey":"InsertDate","Name":"InsertDate","FieldType":"Date","IsPrimaryKey":false,"IsRequired":false,"DefaultValue":"getdate()"},
{"CustomerKey":"UpdatedDate","Name":"UpdatedDate","FieldType":"Date","IsPrimaryKey":false,"IsRequired":false},
]});Write("<br>result:"+ Stringify(result))

1
  • I looked at the records and you are correct that the additional records in Jobs Data View all have a TriggeredSendExternalKey, they were all started and completed or failed from a Journey. I'll try the suggested code next week after the long weekend and will update you with my findings. Thanks!
    – JordanGS
    May 20, 2022 at 2:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .