Using SAS Enterprise Guide to Connect Customer Intelligence 360 Data with Offline Data

I've been with SAS Education for a little over a year and half (how time flies) and in that short amount of time I have learned so much about Customer Intelligence 360. I feel honored to be able to bring this solution to customers and show them all the incredible things it can do. It's hard to believe though, that I've only scratched the surface and that there are some incredibly talented and intelligent people working on this solution every day who know it like the back of their hand. I look forward to continuing to learn from them.

With all that said, I'd like to use this blog to continue to bring you little snippets of some exciting things that I have learned along my journey (or at least exciting to me). Some of it may be brand new to you and some of it you may read and say, "of course it does that!" Either way, I hope you can take at least a little bit of information away.

One of the most exciting things I have been doing recently is not only continuing to learn how to use Customer Intelligence 360, but also learning how it can be used along-side other SAS solutions! One of those solutions being SAS Enterprise Guide. So, in this blog I will cover how you can use SAS Enterprise Guide to join Customer Intelligence 360 data with offline data using identity tables. The identity information stored in the identity tables enables you to link data in your base tables and detail tables to offline data that you would like to include in your analysis. In this example, we'll walk through this process using the SESSION_DETAILS table, the IDENTITY_ATTRIBUTES table and the IDENTITY_MAP table to eventually match offline user identification information with user identification values stored in Customer Intelligence 360.

Let's jump into it!

Step 1: Join the SESSION_DETAILS table to the IDENTITY_MAP table and identify the active identity id (Figure 1).

A few things to remember:

The SESSION_DETAILS table contains an identity_id and the IDENTITY_MAP table contains the source_identity_id and the target_identity_id.
Data will only appear in the IDENTITY_MAP table if an unknown user becomes a known user.
When the target_identity_id is identified, the source_identity_id becomes inactive.

Figure 1

There are two sub-steps to this first step.

Figure 2

First, use a left join to join the SESSION_DETAILS table with the IDENTITY_MAP table using the identity_id and the source_identity_id. The left join ensures that the data from the SESSION_DETAILS table persists even if it does not have a match with the source_identity_id. (Figure 2)
Create a new calculated column that will identify the active identity id using the following coalesce function:

Active identity id = (coalesce(IDENTITY_MAP.target_identity_id, SESSION_DETAILS.identity_id))

This function states that the active identity id is the target_identity_id from the IDENTITY_MAP table if the target_identity_id is present, otherwise the active identity id is the identity_id from the SESSION_DETAILS table.

After performing this first step, you'll have a new table with an active_identity_id column.

Step 2: Join the new table with the IDENTITY_ATTRIBUTES table to obtain the user_identifier_value column, like a login ID, from the IDENTITY_ATTRIBUTES table. (Figure 3)

Figure 3

To join the two tables, use the new active_identity_id column and the identity_id column in the IDENTITY_ATTRIBUTES table to do a left join. The left join will retain the session information for unidentified visitors and collect the information for the identified visitors. (Figure 4)

Figure 4

The new table now contains the user_identifier_value.

Step 3: Use the USER_IDENTIFIER_VAL column in the new table, to match to the appropriate column in the offline data. (Figure 5)

Figure 5

Remember, this example used the SESSION_DETAILS table, but you can use this same approach with any of the other detail or base tables, as long as you have the identity_id needed to link to your identity tables.

If you would like to learn more about SAS Customer Intelligence 360 and UDM Tables please visit any one of these sites below: