Refine output details stored in CDM?

[HeidiDT]

Hi All

I need a way to link 2 campaigns together using the CDM. The 2nd campaign uses the contact history of the first campaign to select customers (i.e. the very first selection node is just that: select from contact history where the campaign code=campaign code of the first campaign).

The second campaign also contains a refine node that is used in the communication nodes, and this refinement is based on the communication code used in the first campaign - there is only one communication node. 

My question is this: is there anywhere in the CDM that the diagram details (such as selection criteria / refine node details) are stored? I can't find it but am hoping against hope that it is there somewhere...

[pcapazzi]

Hello. No, selection criteria is not stored in the CDM. How would you want to use it if it was? If you want to use it to plug into another Select node you may want to look at Calculated Items. Select nodes can get values from Calculated Items so if both of your diagrams are using the same Calc Items in their Select nodes you can manage the selection in one place without touching a diagram.
Also, as a suggestion for how you have linked the two diagrams. Instead of using contact history couldn't you just set the cell node(s) going into the Communication node of the first diagram to be linkable? Then in your second diagram use a Link node to pull in the selection?
If the 1st campaign is a recurring campaign how do you limit the 2nd campaign to pull the most recent occurrence from the 1st campaign? Using the Link node as suggested above would address this (if it's an issue).

[HeidiDT]

Hello

The campaigns are not recurring. The campaign builders use the first campaign to allocate vouchers to customers - they are selected and split into groups based on certain criteria and then each group gets a specific type of voucher. No communication is sent to the customers, though. The final node in this campaign is a stored proc that writes the customers' keys and the voucher details for each (including the voucher number that is allocated to them) to an Oracle table. The communication code is also added to this table as this is used to select the correct voucher in the second campaign.

The second campaign is the one where communication is sent to the customers: they are selected from the contact history of the first campaign and split into relevant groups based on the marketing cell codes from the first campaign. The communication node then selects the voucher numbers and details from the Oracle table, using a refine node that looks for the communication code of the first campaign.

What I now need to do, is select all voucher communications to customers, together with the channel that was used to send the voucher details to the customer. So my starting point is the Oracle table that contains the voucher details, and then I have to use the communication code to determine the channel. The problem is that the communication code is of course the communication code of the FIRST campaign, where no communication is sent and therefore the channel is not the actual one used. So now I need to somehow find a link between the 2 campaigns...

I hope all this makes sense!!

[Dmitry_Alergant]

This is a fairly typical task and typical setup. Congratulation on moving in the right direction with the two-staged campaign and intermediate coupon storage table.

What you actually need is that 2nd communication to store somewhere what coupon IDs were communicated for each customer.  This way you could link all facts together using coupon ID.

If SAS MA supported dynamic variable-based treatment attributes (like SAS RTDM does), there would be a natural and beautiful solution - you assigned a Treatment with a dynamic attribute ("coupon code") that you'd populate using the coupon code exported variable. And it got stored to the dynamic_treatment_attribute_ext table.  Unfortunately, this is not the case as SAS MA still lacks this feature (treatment attributes in SAS MA can not be truly dynamic based on a variable)

What you are supposed to do is to Export the data from your communication (customer id, response tracking code, today's date, communication code etc - it's optional as you can derive it from the response tracking code) + coupon code and store it in a new custom table. You can use a separate (2nd) Export Definition (Table - append) in addition to the one which is already there. Or you can rely on what export definition is already there - exporting to the channel (is it a file or a table?) - and use a Post-Process that picks up this piece of data and stored it for long-term usage in the table you need.

If you only export one coupon code per contact, then some people would write a post-process node that stores the coupon code to the EXTERNAL_INFO_ID1 column of CI_Contact_History table - thus avoiding the need to create a separate table. This is an actually better solution as it makes easier for you to maintain data consistency - whenever you delete records from CI_Contact_History for any reason, the coupon code will go with them.  

[pcapazzi]

I'm making an assumption that each 2nd campaign diagram is for one channel. If it's more than one channel you have more than one Comm node correct?

So if there's one comm node in the 2nd diagram wouldn't you join to the CDM from your oracle table as such:

select

a.subject_id,

a.voucher_cd,

c.channel_cd,

e.channel_nm,

e.channel_desc

from {your oracle table} a

inner join ci_cdm.ci_subject_hist b on a.subject_id=b.subject_id

inner join ci_cdm.ci_cell_package c on b.cell_package_sk=c.cell_Package_sk

inner join ci_cdm.ci_communication d on c.communication_sk=d.communication_sk and communication_cd='{comm cd from 2nd diagram}'

inner join ci_cdm.ci_channel e on c.channel_cd=e.channel_cd

I haven't tested this.. but to get this out of CIS you would need the Channel CD added to the infomap if it's not be default. I think you use a select node to get subjects from the 2nd diagram's comm node... export using a refine to the comm node from the 2nd diagram as well in the export node. The channel_cd could be appended to the export then.

Hope that helps.