I am trying to establish a link between nms:broadLog (Recipient Delivery Logs) and nms:trackedUrls (Delivery URL Tracking) schemas together. My requirement is to identify whom all delivery is targeted and what are the URL's that are getting tracked for that particular Email Delivery. My query filter would be on the field Delivery Internal name.
Ultimately My output should be like:
Recipient ID|Recipient Email Address|Contact Date (From Recipient Delivery Logs) and Source URL|Label (From Tracked URL's).
Could someone please help me on how can i accomplish this, it would be great if you let me know how it can be implemented using a workflow.
Use a query activity on nms:trackingLogRcp. From there you'll have access to all the tables and fields in your list via 'Add data...' (select clause).
That's correct. Since the tracking log recipient (TrackingLogRcp) is the lowest level, you query activity node has to be set at that level so you can pull attributes/columns from other higher level tables such as BroadLogRcp, Delivery, etc.
I got it, but since you're querying nms:trackingLogRcp schema, there might be the case where no recipient had acted on the email (like Opens, Clicks etc.), so in that case there won't be any records, how will you fetch the other linked schema details then?
Let's say i have queried like below:
I don't have any records, but I want to fetch below highlighted details
Also point to make it clear, i am not looking at tracking (Recipient clicked on which URL or Opened etc.), i just want to know what are the URL's that are getting tracked for the delivery i sent, which will be available in below schema.
Ideally i am expecting this information, example:
Not below one:
Can you please help?
What if I want to fetch delivery details and address (basically email) from nms:trackingLogRtEvent and intersect them with some details from nms:broadLogRtEvent.
I want to achieve customer ( through email id) level report on real time events.
broadlogRcp is connected to Tracking URL as 1:N. So, create your first query where targeting dimension delivery and filtering dimension tracking log
Hope this will help. Do let me know if this works.
There is the joining/linking path
BROADLOGRCP --> TRACKINGLOGRCP (via IBROADLOGID)
TRACKINGLOGRCP --> NMSTRACKINGURL (via ITRACKINGURLID/IURLID)
BROADLOGRCP --> DELIVERY (via IDELIVERYID)
TRACKINGLOGRCP --> DELIVERY (via IDELIVERYID)
You should be able to review/export the desired data (people who were promoted, sent an email, open/click, what they clicked on, etc.)