Expand my Community achievements bar.

How can i link nms:deliveryLogs and nms:trackedUrls (Delivery URL Tracking) Schemas

Avatar

Level 7

Hello Folks,

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).

abc|abc@gmail.com |2019-05-20 |https://www.adobe.com |Visit Site

abc|abc@gmail.com |2019-05-20 |https://www.forums.com |Visit Forums and so on.

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.

Regards,

Sri Bhargav

8 Replies

Avatar

Community Advisor

Hi,

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).

Thanks,

-Jon

Avatar

Level 4

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.

Thanks.

Avatar

Level 7

Thanks Jon,

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:

1756765_pastedImage_3.png

I don't have any records, but I want to fetch below highlighted details

1756796_pastedImage_7.png

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.

1756797_pastedImage_10.png

Ideally i am expecting this information, example:

1756799_pastedImage_14.png

Not below one:

1756801_pastedImage_16.png

Can you please help?

Regards,

Sri Bhargav

Avatar

Level 1

Hi,

 

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.

 

 

Avatar

Community Advisor

Hi Sri,

broadlogRcp is connected to Tracking URL as 1:N. So, create your first query where targeting dimension delivery and  filtering dimension tracking log

1775771_pastedImage_4.png

Hope this will help. Do let me know if this works.

Thanks.

Kapil

Avatar

Level 4

Hi,

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.)

Thanks.