Create Measure - Unique Clicks

[former_member536943]

Hello Experts,

I am working with the Custom CDS View and Custom Analytical Query Apps to generate a new Report for different analysis.

Therefor I need to create measures:
The measure that challenges me most is the "NumberOfUniqueClicks" measure.

Can anybody give me a hint how to create this measure using the "I_Mkt_Interaction" - Master Data Table?

The help.sap page just gives me an information what the measure is about. That's what I already know. But I do know not what "statement" in the calculated element needs to be done to get the correct calculation.

https://help.sap.com/viewer/0f9408e4921e4ba3bb4a7a1f75f837a7/2011.500/en-US/1d37919ca25f4f20b270d1f6...

Thank you for you help.

BR Christof

[xin_he2 · Accepted solution]

Hello Christof,

The NumberOfUniqueClicks is achieved by an aggregation with an appropriate "group by".

{
max(I_Mkt_Interaction.InteractionTimeStampUTC) as LatestInteractionTimeStampUTC,
I_Mkt_Interaction.CampaignID,
I_Mkt_Interaction.CampaignVersion,
I_Mkt_Interaction.MarketingOrchestrationID, 
I_Mkt_Interaction.RootInteraction,
I_Mkt_Interaction.InteractionContact,
I_Mkt_Interaction._MKT_CampaignExecutionRun.CampaignAutomationActionUUID,
I_Mkt_Interaction.CommunicationMedium,
@DefaultAggregation: #SUM
 cast (1 as abap.int4(10) )  as NumberOfAnyClicks
 } 
where I_Mkt_Interaction.InteractionType = 'CLICK_THROUGH' 
and I_Mkt_Interaction.CampaignID <> ''       
group by I_Mkt_Interaction.CampaignID, I_Mkt_Interaction.CampaignVersion, 
I_Mkt_Interaction.MarketingOrchestrationID, I_Mkt_Interaction.RootInteraction, I_Mkt_Interaction.InteractionContact, 
I_Mkt_Interaction._MKT_CampaignExecutionRun.CampaignAutomationActionUUID, I_Mkt_Interaction.CommunicationMedium 

Hope this is helpful.

Xin

[former_member536943 · Accepted solution]

Hello xin.he2,

Thank you for your comment.

I am working with the Custom CDS View App in der Marketing Cloud and as far as I know, doing a "Group By" is not possible in the Calculation Element.
This function is not provided.
Furthermore it is not possible to use "{" or "}".

This means using this statement doese not work for me.
Is there anything I oversee here?
Can you provide me further help on that?

BR Christof

[maik_bosch]

HI christofblaickner ,

maybe you can use I_MKT_CampaignSuccessCube which contains number of unique clicks?

BR

Maik

[former_member536943]

Hello maik.offerle,

I also thought about using the I_MKT_CampaignSuccessCube - but in this cube if i remember correct, there are parameters and filters definined that causes problems.

It would be great if SAP Experts just could provide that calculation of the measure?

BR Christof

[xin_he2]

Hello Christof,

I do not know how to solve such a problem without using the "group by" concept.

I found an old thread asking the similar question you asked. https://answers.sap.com/questions/12743174/aggregation-in-cds-views.html. The suggestion was to use the Analytical Queries. I have a doubt if the analytical query is good enough for you to achieve your goal. Maybe you could make a try-out?

Thanks,

Xin

[former_member536943]

Hello xin.he2 ,

thank you for your information. It is not possible to "group by" in the analytical query.

I searched for a view without any parameters but containing the measures.
The view I used was I_MKT_AssgdCmpgnSuccessData - that worked.

BR Christof

[qiuwen_li]

Hello Christof,

While you can always calculate your own NumberOfUniqueClicks from interactions, I want to let you know that this measure is available in the standard operational report -

• Campaign Success for Messages

Please check if this report can already fulfill your needs before creating your own.

Regards,

Qiu Wen

[former_member536943]

Hello qiuwen.li ,

when you take a look at my above conversation with Maik Offerle, I have already explained that the Campaign Success for Message (Campaign Success Cube) is not applicable for us.

BR Christof

[qiuwen_li]

Hello Christof,

Can you provide a definition of the "NumberOfUniqueClicks" measure you try to achieve? Is it the number of email recipients who clicked a link in an email?

Regards,

Qiu Wen

[former_member536943]

Hello qiuwen.li,

Yes exactly this is our understanding.
It is not the SUM it is the number of users who clicked at least 1x.

Can you help me?

BR Christof