I am trying to combine data between Send, ListSend, ClickEvent and Subscribers Objects Subscribers and ClickEvent I use SubscriberKey, ClickEvent and ListSend I use SendID So I am trying to figure out the correct key to combine the data between those and Send.
How do I do this?
These are the fields I get from Send Object
CreatedDate
ClientID
EmailID
SendDate
FromAddress
FromName
Duplicates
ForwardedEmails
InvalidAddresses
HardBounces
SoftBounces
OtherBounces
UniqueClicks
NumberSent
NumberDelivered
NumberTargeted
NumberErrored
NumberExcluded
Unsubscribes
MissingAddresses
Subject
PreviewURL
SentDate
EmailName
Status
EmailSendDefinitionObjectID
ID
From Subscribers Object
ID
CreatedDate
ClientID
EmailAddress
SubscriberKey
UnsubscribedDate
Status
EmailTypePreference
From ClickEvent
Client_ID
CreatedDate
ModifiedDate
SendID
SubscriberKey
EventDate
EventType
BatchID
URLID
URL
I am not 100% sure what ID should I join with what ID to make this work
Send.ID -> ClickEvent.SendID
ClickEvent.SubscriberKey -> Subscribers.SubscriberKey
In terms of order of retrieval, I would start with Send, join to CickEvent, then join to Subscribers.
I think you'll ultimately find that this route for retrieving data from SFMC doesn't scale very well. If this is more than just a one-off, I'd suggest starting with a Tracking Extract and loading the data into an external database.
Also, for SFMC specific questions, I'd recommend posting in salesforce.stackexchange.com.