Best way to list the distinct label of the URLs (sourceURL) in email delivery group by Campaign | Community
Skip to main content
C
ckumari12
Level 3
August 18, 2017
Solved

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

  • August 18, 2017
  • 3 replies
  • 2725 views

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?

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Amit_Kumar

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

3 replies

vraghav
Adobe Employee
vraghavAdobe Employee
Adobe Employee
August 19, 2017

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.

vraghav
Adobe Employee
vraghavAdobe Employee
Adobe Employee
August 19, 2017

Line with rowNumber as 1 will be the distinct rows.

Amit_Kumar
Amit_KumarAccepted solution
Level 10
August 21, 2017

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

    Terms & ConditionsAccessibility statement

    Loading footer...