Unlock the Power of Data Transforms: How to Pivot your Data from Rows to Columns

Data Transforms are your secret weapon for preparing and refining your Data Cloud objects. They empower you to easily join data, perform complex calculations, and filter out unneeded records. When you’re done building a data transform, you can either run it manually or set it up to run at regular intervals. The results can then be used in places like identity resolution, segmentation, or calculated insights.

In this blog post, we’ll do a deep dive into one transform technique: pivoting data. This technique allows you to convert row values into column values, opening up more ways that the data can be used. We’ll show you how you can transform retail banking data from Financial Services Cloud (FSC) into actionable insights for Marketing Cloud segmentation.

Retail Banking in Financial Services Cloud

Imagine a bank using FSC to manage its clients’ financial activities. Within FSC, they have a comprehensive view of each client’s accounts, like savings, checking, and credit card accounts. Julie Morris, in the example below, has multiple savings and checking accounts, alongside a credit card account.

Now, let’s delve into a specific scenario. The bank is gearing up to launch a targeted marketing campaign for their brokerage services, aiming to reach clients who haven’t yet opened mutual fund accounts. This data resides in the Financial Account object (FinServ__FinancialAccount__c), which stores account information for each client, as illustrated in the sample table below.

However, the data in its current form cannot be used to create our segment. We can’t use Financial Account Type as a segmentation attribute to identify clients without mutual fund accounts and have opened a savings account. There’s no way to create a segment condition like that with Financial Account Type. What we need are attributes like HasMutualFundAccount or HasSavingsAccount that indicate when a person has a particular account type. Then, we can use them to easily create conditions for defining the segment.

This is where data transforms come into play. We can use a data transform to pivot the Financial Account Type row values into column values, creating a new column for each account type. Let’s take a look at how we can do this in Data Cloud.

Data Transforms in Data Cloud

Use the CRM connector to create a data stream that brings the FSC data into Data Cloud. Once the Financial Account object is available in Data Cloud as a data lake object, it can be used as an input object for a data transform.

In a data transform, create a new multiple-row formula that uses collect_set to accumulate all the values in the Financial Account Type column. Partition the data by the account owner. collect_set scans the records for each partition from top to bottom and accumulates the values from a column. The example below shows the accumulated account type values for each owner. For example, the first person has five account types.

Notice that the Account Types column contains the same value for each person. We only need one row for each person. To remove the duplicate rows, create another formula that generates a row number, and then create a filter that only keeps the first row. Now, we have one row per person, along with each person’s account types.

From here, create a new boolean column that indicates if a person has a checking account (HasCheckingAccountFormula). And then do the same for the other account types: savings, brokerage, mutual fund, and CD. In the end, we have five new columns that we can use as attributes to define a segment.

When you’re ready, run the transform.

After running the transform, create a relationship between the output object and the standard model. In this example, we have a relationship created based on ContactId values from the output object, matched with Individual Id from the Individual object. This makes the output object available for use in segmentation.

Segmentation in Marketing Cloud

Now we’re ready to go into Marketing Cloud to create the segment. To find people that have at least one savings account and no mutual fund accounts, define a segment using conditions based on HasSavingsAccount and HasMutualFundAccount.

After creating the segment, we can launch an email campaign by creating a segment-triggered flow. For the start of the flow, use the segment that we just created. And then add an email element to send the segment members a marketing message.

That’s it, you’re done!

That’s a wrap

In this blog post, we’ve seen how to integrate FSC, Data Cloud, and Marketing Cloud to deliver relevant content to a target audience. We started with data originating from FSC, and then used data transforms in Data Cloud to manipulate the data, and finally acted on the data using flows in Marketing Cloud.

Written by