Cross-session response offer matching

By default, cross-session response tracking matches on treatment codes or offer codes. The crossSessionResponse service uses SQL commands to match treatment codes, offer codes, or a custom code from session data to the Campaign contact and response history tables. You can edit these SQL commands to match any customizations you make to your tracking codes, offer codes, or custom codes.

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. 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 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. 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 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 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, … offerCode N 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.