Best way to list the distinct label of the URLs (sourceURL) in email delivery group by Campaign

[ckumari12]

I want to list all the labels of my campaigns launched in present month.

Label of the URL           Campaign Name

shopping                           A

medicines                         A

food                                  A

travel                                B

medicines                        B

grocery                            B

medicines might be in both campaigns A & B and I want a result in which medicines are present in both the campaign.

I have fetched present month email clicks in an query and selected label & campaign name in edit additional data and in advanced settings, I have checked "remove duplicates" option and also checked "donot enable primary key in targeting dimension". It is returning me results. I want to check, is this the correct way or best approach to achieve this. Can we somehow use group by functionality? If yes, then how?

Also, can somebody explain me the significance of all the additional settings option with examples so that it can be understood very easily?

[Vipul_Raghav]

You can make use of Windowing functions of the Expression editor.

RowNum ( PartitionBy (URLLabel), OrderBy(CampaignName))

This will result in ranking the commen URLs ont he basis of Campaign name. Later you can split to fetch rows with rowNumber greater than 1, thus signifying that same label was used in multiple campaigns.

[Vipul_Raghav]

Line with rowNumber as 1 will be the distinct rows.

[Amit_Kumar · Accepted solution]

Hi,

What you have done is right and I can confirm you are getting the right results but this may not efficient.

To give you the best approach, can you answer the following question:

• Are you trying to get email click per campaign?
• where and how are you going to use this information?
• Are you using this in a campaign workflow?
• Are you trying to retarget people who clicked on these links?

Yes, you can use group by functionality.

You have to fetch present month email clicks in a query(Select correct targeting dimension based on business need, this can be nms: delivery, nms: operation, NmsTrackingUrl, tracking logs etc) from and select URL source and campaign name. group by option  select both like below query and you will get results.

Generated query: SELECT   T0.sSource, O2.sInternalName FROM NmsTrackingUrl T0 JOIN NmsDelivery D1 ON (D1.iDeliveryId = T0.iDeliveryId) JOIN NmsOperation O2 ON (O2.iOperationId = D1.iOperationId) GROUP BY T0.sSource, O2.sInternalName HAVING (O2.sInternalName IS NOT NULL) ORDER BY O2.sInternalName LIMIT 201

To learn how to use all options in query read following:

Examples

Implementation

Note: No one query is perfect for all scenarios, you need to adapt and learn what suits your business purpose.

Regards,

Amit