BookmarkSubscribeRSS Feed
Datino
Obsidian | Level 7

Hello, so I'm trying to populate the CI_RESPONSE_HISTORY table with external data. I see that the RESPONSE_SK field has a not null constraint, so I'm wondering if it the surrogate key needs to be generated by the ETL process that populates the table, or if I can get Customer Intelligence to generate it, and how to go about it?

 

Thanks in advance.

8 REPLIES 8
pcapazzi
Pyrite | Level 9

Ideally, you would use a unique key that came from the system registering the response or an intelligent key based on the data of the response. CI does not write to that table. CI does produce a response_tracking_cd which is different than the sk.

Dmitry_Alergant
Pyrite | Level 9

Hi,

 

RESPONSE_SK is not a ID of a unique response record (like one could think of). Instead, it's a reference to a dimensions table CI_RESPONSE that contains "response definitions".

 

You define "Response Definitions" manually using SAS CI Studio (Definitions tab - Response Definitions), that populates CI_RESPONSE table.   This way you let the system know WHAT TYPES OF RESPONSES DO YOU HAVE. 

 

There you can also populate a channel-specific "Response Channel Code" for that response definition that tells you (and your ETL) how is this response type defined in a channel source data.  It populates CI_RESPONSE_CHANNEL_X_RESPONSE table.  Like if you define a generic response definition "Customer is Interested", it may have a code "CLICKED_A_LINK" for an Email channel, and just "INTERESTED" in a Customer Care channel.

 

Your ETL then may use CI_RESPONSE_CHANNEL_X_RESPONSE to map to appropriate RESPONSE_SK's based on its knowledge of the channel you are working with and a channel-specific response code. This is a canonical approach. Some customers simplify it and don't use channel response codes. It's not that unusual to just hard-code a mapping to RESPONSE_SK's in your ETL.

 

Hope it helps!

-------
Dmitriy Alergant, Tier One Analytics
SteveMarshall
SAS Employee

The CI_Response table is not updated (new row inserted) by the CI Studio Definition of the Response.  In order to get the CI_Response row for the new response, the Response must be associated with a communication definition and that communication must be used in a campaign where the Response node is shown on the diagram.  And this campaign must be published.  This set of steps only has to happen 1 time after the creation of the new Response.  But at least in the past the Creation of the Response Definition did not populate the CI_Response table.

Dmitry_Alergant
Pyrite | Level 9

Hi Steve, thank you for the clarification. I didn't think of it as the final result is eventually the same, and adding a new response channels doesn't happen this often.  But you are right, there is a chance for a user to get confused while adding a new response definition so this is an important point.   

 

I don't think anything changed on that side. Only ma_publish stored process process is updating CDM reference tables, and without a campaign being published or executed there is no reason for CI Studio to call it. So it's likely still behaves as you described. Thanks!

 

 

-------
Dmitriy Alergant, Tier One Analytics
Datino
Obsidian | Level 7

Thank you for the explanation, although I'm finding it difficult to understand the purpose of RESPONSE_SK if it is not a unique record identifier. I see that the column also has a unique constraint, so if I only have two types of responses I'm only allowed to populate two rows in the CI_RESPONSE_HISTORY table?

 

Some customers simplify it and don't use channel response codes. It's not that unusual to just hard-code a mapping to RESPONSE_SK's in your ETL.

So if I just build the ETL to generate a RESPONSE_SK for each of my response records, will it be fine?

Dmitry_Alergant
Pyrite | Level 9

Hi, I think you did in fact properly understood what RESPONSE_SK is. It's essentially a response type identifier, that refers to the CI_Response dimension table. It's unique there, as a dimension key (in the CI_Response table).

 

At the same time, there is NO unique constraint on RESPONSE_SK in the CI_Response_History table. If you have one - this means somebody has mistakenly created it your schema. It wasn't coming from the original SAS CI CDM deployment script. You should just drop this constraint.

 

Good luck!

 

 

 

-------
Dmitriy Alergant, Tier One Analytics
Datino
Obsidian | Level 7

Thank you Dmitry,

 

Do you think it would be any harm if the  RESPONSE_SK field is used to identify each response in the CI_RESPONSE_HISTORY instead?

Dmitry_Alergant
Pyrite | Level 9

Hard to tell (this may depend on what parts of out-of-the-box SAS CI functionality and reporting you are using), but this field was clearly not supposed to be used this way.

 

Response_SK is (or should be) a foreign key to CI_RESPONSE dimension table and that's it. 

 

You can use an existing column EXTERNAL_RESPONSE_INFO_ID1 / ID2 to store IDs for each response record. If you are using an older SAS CI version where these columns were not there, you can just add a new column yourself. 

 

 

 

 

-------
Dmitriy Alergant, Tier One Analytics
How to improve email deliverability

SAS' Peter Ansbacher shows you how to use the dashboard in SAS Customer Intelligence 360 for better results.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 1691 views
  • 2 likes
  • 4 in conversation