Building custom SAP HANA View for SAP Marketing Cloud to Segment customers based on Agreement Type count

In this blog post I will explain how to extend SAP Marketing Cloud Segmentation application in order to segment customers based on their Agreement Type count, creating a Custom SAP HANA 2.0 Calculation View in SAP Web IDE.

Typically Utility companies have several Agreement Types for their services, such as ‘Residential Contract Account’, ‘Commercial Contract Account’, where single Consumer can have multiple agreements of each type:

Agreement Type attribute

Starting from 2008 version of SAP Marketing Cloud, you can use blocks for Agreement Bundles in email templates and emails to send customers information about their Agreement Bundles and Agreement Members. This functionality is specially very useful to the Utility Industries since it helps to add information of Contract Accounts and Contracts underneath them in these agreement blocks. Currently the solution supports 1:n relationship between Agreement Bundle and Agreement Bundle Member and it requires each Agreement Bundle to have a distinct Agreement type.

The exact amount of Agreements for each consumer per Agreement Type is not transparent in Segmentation application (sample: only one Contract Account of Residential Type and one Contract Account of Commercial Type). This can be easily achieved by developing Custom Calculation view and assigning it to Segmentation.

Part I – Prerequisites

1. Set-up SAP Web IDE development environment for SAP HANA Calculation view in Cloud Foundry
2. Export whitelisted content from your SAP Marketing Cloud tenant into SAP Web IDE.

Part II – Create new Custom Calculation View

In the ‘MKT_metadata’ tree find ‘src’ folder and below create a new folder ‘Custom View’:

New Folder

Right-click on newly created ‘Custom View’ folder and choose New > Calculation View:

New Calculation View

Fill in View name, label and data category as CUBE:

Calculation View Name and Category

Newly created Custom Calculation view will open in graphical designer automatically:

New Custom Calculation View

On the right-hand side panel of the View graphical editor I have multiple options, like create a projection, add join, rank, union and others. Select the Projection option from the left panel of the View editor section and click on the graphical designer work area of the View, it will create a new ‘Projection_1’ afterwards select ‘+’ to add a data source

Projection_1

As a data source I will use agreement root table from SAP Marketing Cloud, which has all the necessary data like: Interaction contact key, agreement type, external agreement id. Once you press on ‘+’ you will see a pop-up, in a search field type ‘CUAND_AGR_ROOT’, select the table and press ‘Finish’:

CUAND_AGR_ROOT source table

(NOTE: if you don’t see the table in the list, check that you manage to import marketing medata zip file successfully – prerequisite step 2)

After selecting corresponding data source ‘Projection_1’ will look like this:

Projection with data source

Now I will make required data fields from source table visible as output columns of my projection. Select the projection.On the right-hand side, additional property settings will appear, if it’s still not visible for you by default, try to extend the property work area by pulling marked tab to the left and then stay at the first tab ‘Mapping’.  Select the fields from data source (left side) and drag-and-drop them to ‘Output Columns’ on the left side:

Projection Mapping

Fields to be pulled for output: SAPCLIENT; MKT_AGREEMENTEXTERNALID; MKT_AGREEMENTISBUNDLE; CONTACTORIGIN; INTERACTIONCONTACTUUID; MKT_AGREEMENTTYPE. (order of the fields is not fixed)

I will apply filters at the hdbtable level, such as client and contract account by writing SQL expression at the last tab of projection ‘Filter Expression’:

("SAPCLIENT" ='$$client$$') AND ("MKT_AGREEMENTISBUNDLE" ='X')

Projection Filter

Next, I will link my ‘Projetion_1’ to above aggregation node by clicking at projection node and using arrow:

Link Projection to Aggregation node

At aggregation node again I will add required fields (Interaction contact key, agreement type, agreement id) as output column in ‘Mapping’ tab:

Aggregation mapping

Now it’s time to create a counter for agreements, by going to ‘Calculated Column’ tab at aggregation node:

Create counter

Drill down into properties of created counter:

Counter

Make sure that column type is set as ‘Measure’ and aggregation type is ‘COUNT_DISTINCT’. At the Counter column section select ‘MKT_AGREEMENTEXTERNALID’ as the source field for contract account count:

Counter column

Proceed to Semantics node and mark ‘INTERACTIONCONTACTUUID’ as key field (to map our view in SAP Marketing Cloud) and select hidden checkbox for ‘MKT_AGREEMENTEXTERNALID’ as shown below (otherwise this will lead to different count result):

Semantics

I’m done with the View build, now I need only to activate it. Right-click on the view, select Build > Build Selected Files:

Activate Custom Calculation View

Part III – Import new Custom Calculation View into Marketing Cloud

After successful Custom view build, I will export the View from SAP Web IDE, by right click on the View>Export:

Export Custom View from SAP Web IDE

Before loading the view into SAP Marketing Cloud, I must rename extension of the file from “.hdbcalculationview” to “.xml” and save.

Login to SAP Marketing Cloud and run ‘Add Custom View’ app under the Extensibility and Adaptability for Marketing tab. (Note: if you can’t find the app, make sure you have required authorization rights for your role). You will get a pop-up message informing you about upgrade to SAP HANA 2.0, just proceed to second tab ‘Import View Definition’, set view usage type as ‘Segmentation’, browse for your view and press Import View:

Import Custom Calculation View into Marketing Cloud

Now I need to assign my new Custom Calculation View to segmentation. Go to ‘Marketing Extensibility’ app and scroll down till Custom View section and select ‘Assign to Segmentation’:

Assign Custom View to Segmentation

Pres on ‘+’ sign at the left bottom side of the screen to add new View and fill in the details as displayed below:

New view assignment

Go to ‘Segmentation Configuration’ app > Segmentation Objects and Attributes > Consumers and scroll to Assigned Data Sources list, searching for ‘_SAP_CV_AGR_COUNT’ and click on it. Once inside let’s configure attribute visibility and conditions for key figure. , go to ‘Edit’ mode and adjust checkboxes as below:

Configure Assigned Data Source Attributes

By marking Agreement type ‘visible as dimension’ in segmentation configuration will allow me to use that attribute as condition for Agreement type count measure.

Finally let’s check how does it look in ‘Segmentation model’ app. Attributes of the newly added custom view by default will be at the end of all existing marketing attributes and groups (if you have not changed this in segmentation configuration) or type attribute name ‘Agreement Count’ in search field and then drag-and-drop it to ‘All consumers’ node, entering the count equal ‘1’ and at conditions from the drop down menu select ‘Agreement type’ as ‘001 – Residential Contract Account’, press keep:

Consumers with single Residential Agreement Account

Now we have a first branch which stores all Consumers who has exactly one Residential Contract Account. Repeat the same operation, selecting single contract account of type ‘002 – Commercial Contract Account’, which will generate a second branch. Afterwards select the first node and drag-and-drop it over the second node, you will get a combine segments pop-up, choose ‘intersect’ option:

Combine Segments: Intersect

At the end I got a Segment node which has Consumer ID, who has exactly one Residential and exactly one Commercial Contract Account:

Final node for Target Group

With this new measure attribute ‘Agreement count’ I can easily create different segmentation nodes for my marketing campaigns and offers, based on exact amount of agreement type consumer has and be more efficient in targeting the right audience.

For more information about why you should integrate SAP S/4HANA Utilities with SAP Marketing Cloud, you can read about how utilities can leverage the Segmentation application to identify target groups and how utilities can enrich their SAP S/4HANA integration with SAP Marketing Cloud by using custom fields.