I want to list all the labels of my campaigns launched in present month.
Label of the URL Campaign Name
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?
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:
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:
Note: No one query is perfect for all scenarios, you need to adapt and learn what suits your business purpose.
Line with rowNumber as 1 will be the distinct rows.
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.