Support Article

Query on table(s) information for email/SMS response results

SA-30826

Summary



Bussiness would like to know where the response results are stored in database for further business processing of user responses.
 

Error Messages



Not Applicable


Steps to Reproduce



Not Applicable


Root Cause



Not Applicable

Resolution

On the outset, response models can be built into campaigns to enable target audiences to receive follow-up treatments to suit their previous response. The response analysis process within a campaign can also identify non-responders so that they can either be by-passed or re-targeted in later stages of the campaign.
 
It is necessary to run the inbound process to read responses from target audience and store them into the database. These responses are stored in following tables, based on the type of campaign (SMS/Email):
 

  • WIRELESS_RES: Contains details of wireless communication (SMS/MMS) responses that satisfy the rules for a Wireless Centre and are populated with the respective values
  • EMAIL_RES: It is populated by the email inbound process by applying the email response rules on incoming mails. If an incoming email satisfies a processing rule, that is, it contains one of the specified text strings, the response processor inserts a predefined value into the table

 
Also, note that the “CAMP_RES_DET” table contains campaign response stream segment details associated with campaign nodes where the object type is “Response Stream”.
The column “PROJ_PCTG_OF_RES” represents the projected percentage of total response associated with the respective response stream, while the “PROJ_REV_PER_RES” column can be used to calculate the total projected revenue of the campaign.
 
Further, the “RES_MODEL_HDR” table contains details of the response model tree that is defined independently of a campaign, and subsequently attached to any campaign, and the “RES_MODEL_STREAM” table contains details of the streams within the response model including the order in which they should be executed.

The “execute” and “count” options are not available for Response Criteria, Score Criteria and Derived Value Criteria as these are executed within appropriate modules.
 
This means that a response criteria can be added to a Response Model and that in turn can be added as a campaign node, which when executed, will send campaign communications to appropriate target audiences who satisfy the response criteria.
The output of response criteria are computed in an intermediate stage of the campaign and are taken as inputs again for campaign communication phase and the output of this campaign is stored in CAMP_COMM_OUT_DET table.
 
However, the output of the response criteria itself is not recorded in any table of the database.

‘Customer table mappings’ has to be done in order to import the data of an existing customer table (where the responses of the customers are recorded manually) into the CMD client for further business processing. That is, mapping the external table which hold response from customer (recorded manually) with CMD schema. 
 
The structure of CMD schema consists of 3 sets of tables:

  • Customer Tables: The set of tables that make up the organization’s marketing or customer database. The content and structure of this set of tables will vary between organizations. A description of this structure must be held within the Mapping Tables.
  • System Tables: The fixed set of proprietary tables comprising the CMD system for controlling the segment channel modules. This set includes the CMD Mapping Tables and Campaign Communication Tables.
    • Mapping Tables: The Mapping Tables describe the Customer Tables to CMD. CMD utilizes the Mapping Tables throughout the application in order to generate queries on the Customer Tables.
  • Dynamic Tables: The tables that are created dynamically by CMD when user defined processes are executed. They hold data from, and form links between, Customer Tables and the System Tables. Examples that create dynamic tables are those for campaigns, segments and derived values.
 
Mapping Tables include the following CMD system tables:
  • CDV_HDR: Customer data view header records
  • CUST_TAB_GRP: definitions of Customer Table groups
  • CUST_TAB: definitions of the entities in the customer date model to be references by CMD
  • CUST_JOIN: definitions of complex joins
  • VANTAGE_DYN_TAB: definitions of dynamic tables, maintained by the CMD system
  • VANTAGE_ALL_TAB: database view containing definitions of all entities by a union of CUST_TAB and VANTAGE_DYN_TAB

 
Setting up the Mapping Tables requires creating the correct data in these tables to enable CMD to work with the customer data model.
 
There are a number of areas that need to be considered before population of CUST_TAB and CUST_JOIN. The “Preparation of Mapping” sub-section of Chapter 2, in the document “Marketing_Director_Customer_Mappings.pdf”, provides the necessary mapping details required to access the relevant customer data model entities.
 
To make appropriate entries to CUST_TAB System Table with respect to the user-defined Customer Table, please refer to the “Customer Data Model Entities” sub-section of Chapter 2.
 
Once the external table is mapped, create segment (in query designer module) to fetch data from mapped table and generate Data view for the corresponding segment. 

 

Published November 25, 2016 - Updated December 1, 2016

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.