Cross-session response offer matching

Matching by treatment code

The SQL to match by treatment code must return all the columns in the XSessResponse table for this audience level plus a column called OfferIDMatch. The value in the OfferIDMatch column must be the offerId that goes with the treatment code in the XSessResponse record.

The following is a sample of the default generated SQL command that match treatment codes. Unica Interact generates the SQL to use the correct table names for the audience level. This SQL is used if the Interact > services > crossSessionResponse > OverridePerAudience > AudienceLevel > TrackingCodes > byTreatmentCode > SQL property is set to Use System Generated SQL.

select   distinct treatment.offerId as OFFERIDMATCH, 
			tx.*,
            dch.RTSelectionMethod 
from     UACI_XSessResponse tx 
Left Outer Join UA_Treatment treatment ON tx.trackingCode=treatment.treatmentCode 
Left Outer Join UA_DtlContactHist dch ON tx.CustomerID = dch.CustomerID 
Left Outer Join UA_ContactHistory ch ON tx.CustomerID = ch.CustomerID 
AND treatment.cellID = ch.cellID 
AND treatment.packageID=ch.packageID 
where   tx.mark=1 
and       tx.trackingCodeType=1

The values UACI_XsessResponse, UA_DtlContactHist, CustomerID, and UA_ContactHistory are defined by your settings in Unica Interact. For example, UACI_XsessResponse is defined by the Interact > profile > Audience Levels > [AudienceLevelName] > crossSessionResponseTable configuration property.

If you have customized your contact and response history tables, you may need to revise this SQL to work with your tables. You define SQL overrides in the Interact > services > crossSessionResponse > OverridePerAudience > (AudienceLevel) > TrackingCodes > byTreatmentCode > OverrideSQL property. If you provide some override SQL, you must also change the SQL property to Override SQL.

Matching by offer code

The SQL to match by offer code must return all the columns in the XSessResponse table for this audience level plus a column called TreatmentCodeMatch. The value in the TreatmentCodeMatch column is the Treatment Code that goes with the Offer ID (and Offer Code) in the XSessResponse record.

The following is a sample of the default generated SQL command that match offer codes. Unica Interact generates the SQL to use the correct table names for the audience level. This SQL is used if the Interact > services > crossSessionResponse > OverridePerAudience > AudienceLevel > TrackingCodes > byOfferCode > SQL property is set to Use System Generated SQL.

select   treatment.treatmentCode as TREATMENTCODEMATCH, 
			tx.*, 
dch.RTSelectionMethod 
from     UACI_XSessResponse tx 
Left Outer Join UA_DtlContactHist dch ON tx.CustomerID=dch.CustomerID 
Left Outer Join UA_Treatment treatment ON tx.offerId = treatment.offerId 
Left Outer Join 
			(
			select   max(dch.contactDateTime) as maxDate, 
						treatment.offerId, 
						dch.CustomerID 
			from     UA_DtlContactHist dch, UA_Treatment treatment, UACI_XSessResponse tx
			where  tx.CustomerID=dch.CustomerID 
			and tx.offerID = treatment.offerId 
			and dch.treatmentInstId = treatment.treatmentInstId 
			group by dch.CustomerID,  treatment.offerId
			) dch_by_max_date ON tx.CustomerID=dch_by_max_date.CustomerID 
				and tx.offerId = dch_by_max_date.offerId 
where   tx.mark = 1 
and       dch.contactDateTime = dch_by_max_date.maxDate 
and       dch.treatmentInstId = treatment.treatmentInstId 
and       tx.trackingCodeType=2 
union 
select   treatment.treatmentCode as TREATMENTCODEMATCH, 
			tx.*, 
			0 
from UACI_XSessResponse tx 
Left Outer Join UA_ContactHistory ch ON tx.CustomerID =ch.CustomerID
Left Outer Join UA_Treatment treatment ON tx.offerId = treatment.offerId 
Left Outer Join 
			( 
			select   max(ch.contactDateTime) as maxDate, 
						treatment.offerId, ch.CustomerID
			from UA_ContactHistory ch, UA_Treatment treatment, UACI_XSessResponse tx 
			where  tx.CustomerID =ch.CustomerID
			and tx.offerID = treatment.offerId 
			and treatment.cellID = ch.cellID 
			and treatment.packageID=ch.packageID 
			group by ch.CustomerID,  treatment.offerId
			) ch_by_max_date ON tx.CustomerID =ch_by_max_date.CustomerID
			and tx.offerId = ch_by_max_date.offerId 
			and treatment.cellID = ch.cellID 
			and treatment.packageID=ch.packageID 
where   tx.mark = 1 
			and       ch.contactDateTime = ch_by_max_date.maxDate
			and       treatment.cellID = ch.cellID 
			and       treatment.packageID=ch.packageID 
			and       tx.offerID = treatment.offerId 
			and       tx.trackingCodeType=2

The values UACI_XsessResponse, UA_DtlContactHist, CustomerID, and UA_ContactHistory are defined by your settings in Unica Interact. For example, UACI_XsessResponse is defined by the Interact > profile > Audience Levels > [AudienceLevelName] > crossSessionResponseTable configuration property.

If you have customized your contact and response history tables, you may need to revise this SQL to work with your tables. You define SQL overrides in the Interact > services > crossSessionResponse > OverridePerAudience > (AudienceLevel) > TrackingCodes > byOfferCode > OverrideSQL property. If you provide some override SQL, you must also change the SQL property to Override SQL.

Matching by alternate code

You can define an SQL command to match by some alternate code of your choice. For example, you could have promotional codes or product codes separate from offer or treatment codes.

You must define this alternate code in the UACI_TrackingType table in the Unica Interact runtime environment tables.

You must provide SQL or a stored procedure in the Interact > services > crossSessionResponse > OverridePerAudience > (AudienceLevel) > TrackingCodes > byAlternateCode > OverrideSQL property which returns all the columns in the XSessResponse table for this audience level plus the columns TreatmentCodeMatch and OfferIDMatch. You may optionally return the offerCode in place of OfferIDMatch (in the form of offerCode1, offerCode2, offerCodeN for N part offer codes). The values in the TreatmentCodeMatch column and OfferIDMatch column (or offer code columns) must correspond to the TrackingCode in the XSessResponse record.

For example, the following SQL pseudo code matches on the AlternateCode column in the XSessResponse table.

Select m.TreatmentCode as TreatmentCodeMatch, m.OfferID as OfferIDMatch, tx.*
From MyLookup m, UACI_XSessResponse tx
Where m.customerId = tx.customerId
And m.alternateCode = tx.trackingCode
And tx.mark=1
And tx.trackingCodeType = <x>

Where <x> is the tracking code defined in the UACI_TrackingType table.