Skip to main content
Skip table of contents

How Timestamp is populated in Offer History States table

Overview

This section describes how the Timestamp column in the Offer History States table is being populated by specific disposition data for specific Redpoint Interaction (RPI) connectors during the Channel Synchronization process.

Channel email connectors

Acoustic channel connector

  1. Export engagement results. Normally, the Acoustic connector will be initiating a SOAP based API endpoint request to export engagement for specific email campaign execution. The API endpoint used is XMLAPI with the following required parameters: EXPORT_FILE_NAME, CAMPAIGN_ID, RPContactID, ChannelExecutionID, and ChannelID.
    On successful execution, the API endpoint request returns a Job ID that is used for monitoring the export job that was initiated. The connector will continue to monitor the export execution until it is completed with the status of COMPLETE. If there is any failure during the monitoring process, the process will fail and will be terminated. In this case, no disposition data is processed.

  2. Download engagement results. Once the export job is completed, the file will be downloaded from the SFTP directory.

  3. Processing CSV file. The CSV file is provided by Acoustic. Each row from the CSV file is processed and ingested into the RPI temporary table called RPI_TempStateImport_<channel name>. The temporary table has the following structure, and the record is mapped with the CSV file header:

Column Name

Data Type

CSV Header

AddressKey

Text

Email

ChannelExecutionID

Number

ChannelExecutionID

FulfillmentState

Text

Event Type

EventName

Text

URL

MetricValue

Number

Timestamp

Date and Time

Event Timestamp

  1. Finally, once the RPI_TempStateImport_<channel name> table is populated with all records from the CSV file, the following is an example of a SQL query that is executed during the Channel Synchronization task processing. On successful execution, all records will be ingested into the Offer History States table.

SQL 
INSERT INTO "dbo"."OfferHistory_States" (
    "RPContactID",
    "ChannelExecutionID",
    "FulfillmentState",
    "EventName",
    "Timestamp"
)
SELECT 
    a0."RPContactID",
    a0."ChannelExecutionID",
    a1."FulfillmentState",
    a1."EventName",
    a1."Timestamp"
FROM 
    "dbo"."OfferHistory" a0
JOIN 
    "dbo"."RPI_TempStateImport_AcousticChannel" a1
    ON a0."ChannelExecutionID" = a1."ChannelExecutionID"
    AND a0."AddressKey" = a1."AddressKey"
WHERE 
    NOT EXISTS (
        SELECT 1
        FROM "dbo"."OfferHistory_States" b0
        WHERE 
            b0."ChannelExecutionID" = a0."ChannelExecutionID"
            AND b0."RPContactID" = a0."RPContactID"
            AND b0."FulfillmentState" = a1."FulfillmentState"
            AND (
                b0."EventName" = a1."EventName"
                OR (b0."EventName" IS NULL AND a1."EventName" IS NULL)
            )
    );

Eloqua channel connector

  1. Processing engagement results. Download email report via API request for each event as follows:

    • EmailSend

    • EmailOpen

    • EmailClickthrough

    • Bounceback

    • Unsubscribe

  2. Each email report via API request will be processed and ingested into a temporary table called RPI_TempStateImport_<channel name>. The temporary table has the following structure, and the record is mapped with the JSON payload returned on each API request:

Column Name

Data Type

JSON Header

AddressKey

Text

EmailAddress

ChannelExecutionID

Number

FulfillmentState

Text

Sourced out from EmailSend, EmailOpen, EmailClickthrough, Bounceback, or Unsubscribe

EventName

Text

EmailClickedThruLink

MetricValue

Number

Timestamp

Date and Time

ActivityDate

  1. Finally, once the RPI_TempStateImport_<channel name> table is populated with all records from the JSON payload, the following is an example of SQL query that is executed during the Channel Synchronization task processing. On successful execution, all records will be ingested into the Offer History States table.

SQL 
INSERT INTO "dbo"."OfferHistory_States" (
    "RPContactID",
    "ChannelExecutionID",
    "FulfillmentState",
    "EventName",
    "Timestamp"
)
SELECT 
    a0."RPContactID",
    a0."ChannelExecutionID",
    a1."FulfillmentState",
    a1."EventName",
    a1."Timestamp"
FROM 
    "dbo"."OfferHistory" a0
JOIN 
    "dbo"."RPI_TempStateImport_EloquaChannel" a1
    ON a0."ChannelExecutionID" = a1."ChannelExecutionID"
    AND a0."AddressKey" = a1."AddressKey"
WHERE 
    NOT EXISTS (
        SELECT 1
        FROM "dbo"."OfferHistory_States" b0
        WHERE 
            b0."ChannelExecutionID" = a0."ChannelExecutionID"
            AND b0."RPContactID" = a0."RPContactID"
            AND b0."FulfillmentState" = a1."FulfillmentState"
            AND (
                b0."EventName" = a1."EventName"
                OR (b0."EventName" IS NULL AND a1."EventName" IS NULL)
            )
    );

LuxSci channel connector

  1. Processing engagement results via API request. If Import via file is unchecked in the LuxSci channel setting, download the email report via API request for each event as follows:

    • Sent

    • Delivered

    • Opened

    • Clicks

    • Feedbackloop

  2. Each email report via API request will be processed and ingested into a temporary table called RPI_TempStateImport_<channel name>. The temporary table has the following structure, and the record is mapped with the JSON payload returned on each API request:

Column Name

Data Type

JSON Header

AddressKey

Text

to

ChannelExecutionID

Number

FulfillmentState

Text

Sourced out from opens, clicks, sent, fbl, delivery, or spam

EventName

Text

url

MetricValue

Number

Timestamp

Date and Time

It can be open_date, click_date, date_sent, complaint_date, or last_updated; otherwise, RPI Server Time if OverrideOHStateTimestampUsingServerTimezone is set to True in RPI tenant’s System configurations

  1. Finally, once the RPI_TempStateImport_<channel name> table is populated with all records from the JSON payload, the following is an example of a SQL query that is executed during Channel Synchronization task processing. On successful execution, all records will be ingested into the Offer History States table.

SQL 
INSERT INTO "dbo"."OfferHistory_States" (
    "RPContactID",
    "ChannelExecutionID",
    "FulfillmentState",
    "EventName",
    "Timestamp"
)
SELECT 
    a0."RPContactID",
    a0."ChannelExecutionID",
    a1."FulfillmentState",
    a1."EventName",
    a1."Timestamp"
FROM 
    "dbo"."OfferHistory" a0
JOIN 
    "dbo"."RPI_TempStateImport_LuxsciChannel" a1
    ON a0."ChannelExecutionID" = a1."ChannelExecutionID"
    AND a0."AddressKey" = a1."AddressKey"
WHERE 
    NOT EXISTS (
        SELECT 1
        FROM "dbo"."OfferHistory_States" b0
        WHERE 
            b0."ChannelExecutionID" = a0."ChannelExecutionID"
            AND b0."RPContactID" = a0."RPContactID"
            AND b0."FulfillmentState" = a1."FulfillmentState"
            AND (
                b0."EventName" = a1."EventName"
                OR (b0."EventName" IS NULL AND a1."EventName" IS NULL)
            )
    );

  1. Processing engagement results via RPDM job.

    1. If Import via file is checked via LuxSci channel setting, RPI will create a request export job to LuxSci. After LuxSci generates the report, it uploads them to the specified Report destination configured in LuxSci configuration channel settings.

    2. RPI will create a request that monitors the export job. It checks whether the jobs status is completed or failed. If the max job is already at limit, RPI will wait 60 seconds to check again if there are available job slots to make the request. Job requests will fail if the number of retries has reached the limit as imposed by Async report retry in the channel config.

    3. After the export job has been completed, RPI will download the LuxSci generated email report from the Report destination to the configure State results folder path.

    4. Once the report is downloaded, RPI will delete the file in the Report destination.

    5. If the RPDM job has completed, delete the used files in the State results folder path. During this process, any report files older than seven days are also deleted. All records are now processed and ingested into the RPI_LuxSci_State_<Import table suffix> temporary table. The temporary table has the following structure, and the record is mapped with the CSV file header:

Column Name

Data Type

CSV Header

EmailAddress

Text

to

OfferTemplateInstanceID

Number

OfferTemplateInstanceID

ChannelExecutionID

Number

ChannelExecutionID

EventDate

Date and Time

It can be open_date, click_date, date_sent, complaint_date, or last_updated

EventType

Text

FulfillmentState

Text

Sourced out from opens, clicks, sent, fbl, delivery, or spam

EventName

Text

url

EventID

Text

EventID

RPContactID

Number

RPContactID

UnsubscribedMethod

Text

Spam

f. Finally, once the RPI_LuxSci_State_<Import table suffix> table is populated with all records from the CSV file, the following is an example of a SQL query that is executed during Channel Synchronization task processing. On successful execution, all records will be ingested into the Offer History States table.

SQL 
INSERT INTO "dbo"."OfferHistory_States" (
    "RPContactID",
    "ChannelExecutionID",
    "FulfillmentState",
    "EventName",
    "Timestamp"
)
SELECT 
    r1."RPContactID",
    a1."ChannelExecutionID",
    a1."FulfillmentState",
    a1."EventName",
    a1."EventDate"
FROM 
    "dbo"."OfferHistory" r1
JOIN 
    "dbo"."RPI_LuxSci_State_ACTIVITIES" a1
    ON (
        r1."AddressKey" = a1."EmailAddress"
        OR r1."RPContactID" = a1."RPContactID"
    )
    AND r1."ChannelExecutionID" = a1."ChannelExecutionID"
    AND r1."OfferTemplateInstanceID" = a1."OfferTemplateInstanceID"
WHERE 
    NOT EXISTS (
        SELECT 1
        FROM "dbo"."OfferHistory_States" s1
        WHERE 
            s1."ChannelExecutionID" = r1."ChannelExecutionID"
            AND s1."RPContactID" = r1."RPContactID"
            AND s1."FulfillmentState" = a1."FulfillmentState"
            AND (
                s1."EventName" = a1."EventName"
                OR (s1."EventName" IS NULL AND a1."EventName" = '')
                OR (s1."EventName" IS NULL AND a1."EventName" IS NULL)
            )
    );

Marigold channel connector

  1. Download engagement results. If there are available CSV files from Marigold’s SFTP engagement export directory, these files will be downloaded and processed by Marigold channel connector based on the configured FTP event export location channel setting. Each CSV file will only be processed with the following:

    • File name format:

      File Name

      Event Type

      <prefix>_open_<yyyyddMMHHmmss>.txt

      Open

      <prefix>_click_<yyyyddMMHHmmss>.txt

      Click

      <prefix>_send_<yyyyddMMHHmmss>.txt

      Send

      <prefix>_unsubscribe_<yyyyddMMHHmmss>.txt

      Unsubscribe

      <prefix>_bounce_<yyyyddMMHHmmss>.txt

      Bounce

    • Valid standard CSV header field names:

      • p_channelexecutionid

      • p_rpcontactid

  1. Processing CSV file. The CSV file is provided by Marigold. Each row from the CSV file is processed and ingested into the RPI temporary table called RPI_TempStateImport_<channel name>. The temporary table has the following structure, and record is mapped with the CSV file header:

Column Name

Data Type

CSV Header

AddressKey

Text

p_rpcontactid

ChannelExecutionID

Number

p_channelexecutionid

FulfillmentState

Text

It can be a CSV file name that contains open, click, send, unsubscribe, or bounce

EventName

Text

link_name

MetricValue

Number

Timestamp

Date and Time

It can be open_time, send_time, click_time, bounce_time, or unsub_time

  1. Finally, once the RPI_TempStateImport_<channel name> table is populated with all records from the CSV file, the following is an example of a SQL query that is executed during Channel Synchronization task processing. On successful execution, all records will be ingested into the Offer History States table.

SQL 
INSERT INTO "dbo"."OfferHistory_States" (
    "RPContactID",
    "ChannelExecutionID",
    "FulfillmentState",
    "EventName",
    "Timestamp",
    "MetricValue"
)
SELECT 
    a1."RPContactID",
    a1."ChannelExecutionID",
    a1."FulfillmentState",
    a1."EventName",
    a1."Timestamp",
    a1."MetricValue"
FROM "dbo"."RPI_TempStateImport_MarigoldChannel" a1
WHERE NOT EXISTS (
    SELECT 1
    FROM "dbo"."OfferHistory_States" b0
    WHERE b0."ChannelExecutionID" = a1."ChannelExecutionID"
      AND b0."RPContactID" = a1."RPContactID"
      AND b0."FulfillmentState" = a1."FulfillmentState"
      AND (
          (b0."EventName" = a1."EventName")
          OR (b0."EventName" IS NULL AND a1."EventName" IS NULL)
      )
);

Salesforce Marketing Cloud channel connectors

This section applies to Salesforce Marketing Cloud Email and Salesforce Marketing Cloud Data Transfer (SFMC) channel connectors.

  1. Processing engagement results via API request. If Import via file is unchecked in Salesforce Marketing cloud Email or Data Transfer channel setting, download email report via API request for each event as follows:

    • OpenEvent

    • ForwardedEmailEvent

    • ForwardedEmailOptInEvent

    • ClickEvent

    • UnsubEvent

    • BounceEvent

  2. Each email report via API request will be processed and ingested into a temporary table called RPI_TempStateImport_<channel name>. The temporary table has the following structure, and the record is mapped with the SOAP XML payload returned on each API request:

Column Name

Data Type

SOAP XML Header

AddressKey

Text

SubscriberKey

ChannelExecutionID

Number

FulfillmentState

Text

It can be OpenEvent, ForwardedEmailEvent, ForwardedEmailOptInEvent, ClickEvent, UnsubEvent, or BounceEvent

EventName

Text

LinkSend

MetricValue

Number

Timestamp

Date and Time

EventDate

  1. Finally, once the RPI_TempStateImport_<channel name> table is populated with all records from the JSON payload, the following is an example of a SQL query that is executed during Channel Synchronization task processing. On successful execution, all records will be ingested into the Offer History States table.

SQL 
INSERT INTO "dbo"."OfferHistory_States" (
    "RPContactID",
    "ChannelExecutionID",
    "FulfillmentState",
    "EventName",
    "Timestamp"
)
SELECT 
    a0."RPContactID",
    a0."ChannelExecutionID",
    a1."FulfillmentState",
    a1."EventName",
    a1."Timestamp"
FROM 
    "dbo"."OfferHistory" a0
JOIN 
    "dbo"."RPI_TempStateImport_SFMCChannel" a1
    ON a0."ChannelExecutionID" = a1."ChannelExecutionID"
    AND a0."AddressKey" = a1."AddressKey"
WHERE 
    NOT EXISTS (
        SELECT 1
        FROM "dbo"."OfferHistory_States" b0
        WHERE 
            b0."ChannelExecutionID" = a0."ChannelExecutionID"
            AND b0."RPContactID" = a0."RPContactID"
            AND b0."FulfillmentState" = a1."FulfillmentState"
            AND (
                b0."EventName" = a1."EventName"
                OR (b0."EventName" IS NULL AND a1."EventName" IS NULL)
            )
    );

  1. Processing engagement results via RPDM job.

    1. If Import via file is checked via SFMC email or data transfer channel setting, RPI will create a request export job to SFMC. After SFMC generates the report, it uploads them to the SFTP directory configured SFMC.

    2. RPI will create a request that monitors the export job. It checks whether the job status is completed or failed.

    3. After the export job has been completed, RPI will download the SFMC email report generated from the SFMC SFTP directory.

    4. RPDM job will process the email reports and store the records into RPI_ET_STATE<Import table suffix>, delete the used files in local temporary directory. The temporary table has the following structure, and the record is mapped with the CSV file header:

Column Name

Data Type

CSV Header

CLIENTID

Number

ClientID

SENDID

Number

SendID

SUBSCRIBERKEY

Text

SubscriberKey

EMAILADDRESS

Date and Time

EmailAddress

SUBSCRIBERID

Number

SubscriberID

LISTID

Number

ListID

EVENTDATE

Date and Time

EventDate

EVENTTYPE

Text

EventType

EVENTNAME

Text

EventName

BOUNCECATEGORY

Text

BounceCategory

SMTPCODE

Text

SMTPCode

BOUNCEREASON

Text

BounceReason

BATCHID

Number

BatchID

TRIGGEREDSENDEXTERNALKEY

Text

TriggeredSendExternalKey

FULFILLMENTSTATE

Text

FulfillmentState

SENDURLID

Number

SendURLID

URLID

Number

URLID

URL

Text

URL

ALIAS

Text

Alias

e. Finally, once the RPI_ET_STATE _<Import table suffix> table is populated with all records from the CSV file, the following is an example of a SQL query that is executed during Channel Synchronization task processing. On successful execution, all records will be ingested into the Offer History States table.

SQL 
INSERT INTO "dbo"."OfferHistory_States" (
    "RPContactID",
    "ChannelExecutionID",
    "FulfillmentState",
    "EventName",
    "Timestamp"
)
SELECT 
    r1."RPContactID",
    l1."ChannelExecutionID",
    a1."FULFILLMENTSTATE",
    a1."EVENTNAME",
    a1."EVENTDATE"
FROM 
    "dbo"."OfferHistory" r1
JOIN 
    "dbo"."RPI_SFMCSendID_Lookup" l1
    ON r1."ChannelExecutionID" = l1."ChannelExecutionID"
JOIN 
    "dbo"."RPI_ET_STATEACTIVITIES" a1
    ON r1."AddressKey" = a1."EMAILADDRESS"
    AND a1."SENDID" = l1."SendID"
WHERE 
    NOT EXISTS (
        SELECT 1
        FROM "dbo"."OfferHistory_States" s1
        WHERE 
            s1."ChannelExecutionID" = r1."ChannelExecutionID"
            AND s1."RPContactID" = r1."RPContactID"
            AND s1."FulfillmentState" = a1."FULFILLMENTSTATE"
    );

SendGrid channel connector

  1. Processing engagement results via Callback Service API. SendGrid email provider will be sending events via RPI callback service. Typically, SendGrid will invoke the callback service URL configured. The URL is in the following format:
    https://<HOST NAME OR IP ADDRESS>/api/PostEvents?channelLabel= SendGrid
    The events posted via callback service API will be sent to the queue path configured in QueueProvider/CallbackServiceQueuePath in the appsettings.json file. Refer to Callback Service Configuration for more details.

  2. The RPI Queue Reader service reads and processes the SendGrid events available in the queue configured in CallbackServiceQueuePath in RPI tenant's System Configuration. Processing of events will continue until there are no events available in the queue.

  3. If the tenant’s main data warehouse is running either Snowflake DB or AWS Redshift DB, the recommendation is to utilize S3 bucket to bulk-load the records from S3 to temp table on the database. To enable bulk-load using S3 bucket, enable the following RPI system configuration options:

    • AWSPerformCOPYFromS3 = true

    • AWSSourceS3BucketName = <ECP Name>\<Bucket Name>

  4. When the RPI Queue reader processes the events from the queue, each SendGrid event is identified as processed, deferred, delivered, open, dropped, spamreport, unsubscribe, bounce, and click. These records are ingested into the SendGridEvents table. The SendGridEvents table has the following structure, and the record is mapped with the JSON payload posted on a queue via Callback Service API call:

Column Name

Data Type

JSON Header

Event

Text

event

Email

Text

email

Category

Text

category

Response

Text

response

Attempt

Text

attempt

Url

Text

url

Status

Text

status

Reason

Text

reason

Type

Text

type

ChannelExecutionId

Number

channelExecutionId

ChannelId

Text

channelId

ClientId

Text

clientId

Timestamp

Date and Time

timestamp

SgEventId

Text

sg_event_id

IpAddress

Text

ip

UserAgent

Text

useragent

SgMachineOpen

Boolean

sg_machine_open

EntryDate

Date and Time

RPI Server Time

  1. Records from the SendGridEvents table will be processed and ingested into a temporary table called RPI_TempStateImport_<channel name> for SendGrid every channel execution.

Column Name

Data Type

SendGridEvents Column Name

AddressKey

Text

Email

ChannelExecutionID

Number

ChannelExecutionId

FulfillmentState

Text

Event

EventName

Text

Url

MetricValue

Number

Timestamp

Date and Time

Timestamp

  1. Finally, once the RPI_TempStateImport_<channel name> table is populated with all records, the following is an example of a SQL query that is executed during Channel Synchronization task processing. On successful execution, all records will be ingested into the Offer History States table.

SQL 
INSERT INTO "dbo"."OfferHistory_States" (
    "RPContactID",
    "ChannelExecutionID",
    "FulfillmentState",
    "EventName",
    "Timestamp"
)
SELECT 
    a0."RPContactID",
    a0."ChannelExecutionID",
    a1."FulfillmentState",
    a1."EventName",
    a1."Timestamp"
FROM 
    "dbo"."OfferHistory" a0
JOIN 
    "dbo"."RPI_TempStateImport_SendGridChannel" a1
    ON a0."ChannelExecutionID" = a1."ChannelExecutionID"
    AND a0."AddressKey" = a1."AddressKey"
WHERE 
    NOT EXISTS (
        SELECT 1
        FROM "dbo"."OfferHistory_States" b0
        WHERE 
            b0."ChannelExecutionID" = a0."ChannelExecutionID"
            AND b0."RPContactID" = a0."RPContactID"
            AND b0."FulfillmentState" = a1."FulfillmentState"
            AND (
                b0."EventName" = a1."EventName"
                OR (b0."EventName" IS NULL AND a1."EventName" IS

Channel SMS connectors

mPulse channel connector

  1. Processing engagement results via Callback Service API. mPulse SMS provider will be sending events via RPI callback service. Typically, mPulse will invoke the callback service URL configured. The URL is in the following format:
    https://<HOST NAME OR IP ADDRESS>/api/PostEvents?channelLabel=mPulse
    The events posted via callback service API will be sent to the queue path configured in QueueProvider/CallbackServiceQueuePath in the appsettings.json file.
    Refer to Callback Service Configuration for more details.

  2. The RPI Queue Reader service reads and processes the mPulse events available in the queue configured in CallbackServiceQueuePath in RPI tenant's System Configuration. Processing of events will continue until there are no events available in the queue.

  3. If the tenant’s main data warehouse is running either Snowflake DB or AWS Redshift DB, the recommendation is to utilize S3 bucket to bulk-load the records from S3 to temp table on the database. To enable bulk-load using S3 bucket, enable the following RPI system configuration options:

    • AWSPerformCOPYFromS3 = true

    • AWSSourceS3BucketName = <ECP Name>\<Bucket Name>

  4. When the RPI Queue reader processes the events from the queue, each mPulse event is identified as MO (Mobile Originated), SUB (Subscribe), UNSUB (Unsubscribe), and BULKUPSERT (Member List Upload Asynchronous Results).

  5. If the response type is MO, the details of the MO event will be ingested into RPI_MPulseEvents table. If the response type is SUB or UNSUB, the details will be ingested into RPI_MPulseSubscription table. Finally, if the response type is BULKUPSERT, the details will be ingested into the following database tables: RPI_MPulseMemberImportStatus, RPI_MPulseAsynchronousResult, and RPI_MPulseAsynchronousFailure.

  6. The mPulse channel synchronization system task process all state data from RPI_MPulseEvents table. Responses with STOP, STOP ALL, HELP, and START are loaded into Offer History States table. Any other response is loaded as Mobile Originated. STOP ALL records are loaded to the Suppression_SMS table or custom suppression table, if configured. The RPI_MPulseEvents table has the following structure, and the record is mapped with the JSON payload posted on a queue via Callback Service API call:

Column Name

Data Type

JSON Header

ShortCode

Text

short_code

MobilePhoneNumber

Text

mobile_phone_number

Content

Text

Content

Carrier

Text

Carrier

ReceivedDatetime

Text

received_datetime

MoId

Text

MoId

ListId

Text

list_id

CampaignId

Text

campaign_id

MessageId

Text

message_id

Action

Text

Action

CorrelationId

Text

correlation_id

ClientMemberId

Text

client_member_id

MemberId

Text

MemberId

ChannelExecutionID

Number

ChannelId

Text

EntryDate

Date and Time

RPI Server Time

  1. Records from the RPI_MPulseEvents table will be processed and ingested into a temporary table called RPI_TempStateImport_<channel name> for mPulse every channel execution. The table has the following table structure:

Column Name

Data Type

RPI_MPulseEvents Column Name

AddressKey

Text

RPContactID or MobilePhoneNumber

ChannelExecutionID

Number

ChannelExecutionID

FulfillmentState

Text

Content

EventName

Text

MetricValue

Number

Timestamp

Date and Time

RPI Server Time

  1. Finally, once the RPI_TempStateImport_<channel name> table is populated with all records, the following is an example of SQL query that is executed during Channel Synchronization task processing. On successful execution, all records will be ingested into the Offer History States table.

SQL 
INSERT INTO "dbo"."OfferHistory_States" (
    "RPContactID",
    "ChannelExecutionID",
    "FulfillmentState",
    "EventName",
    "Timestamp"
)
SELECT 
    a0."RPContactID",
    a0."ChannelExecutionID",
    a1."FulfillmentState",
    a1."EventName",
    a1."Timestamp"
FROM 
    "dbo"."OfferHistory" a0
JOIN 
    "dbo"."RPI_TempStateImport_MpulseChannel" a1
    ON a0."ChannelExecutionID" = a1."ChannelExecutionID"
    AND a0."AddressKey" = a1."AddressKey"
WHERE 
    NOT EXISTS (
        SELECT 1
        FROM "dbo"."OfferHistory_States" b0
        WHERE 
            b0."ChannelExecutionID" = a0."ChannelExecutionID"
            AND b0."RPContactID" = a0."RPContactID"
            AND b0."FulfillmentState" = a1."FulfillmentState"
            AND (
                b0."EventName" = a1."EventName"
                OR (b0."EventName" IS NULL AND a1."EventName" IS NULL)
            )
    );

Twilio channel connector

  1. Download Email report via API request.

  2. Each Record will be processed and ingested into a temporary table called RPI_TwilioStates_<Channel Name>_<ChannelExecutionID>. The table has the following table structure:

Column Name

Data Type

Twilio MessageResource Property Name

AddressKey

Text

UniqueId

ChannelExecutionID

Number

ChannelExecutionID

FulfillmentState

Text

Status or SurveyResponse

EventName

Text

It can be ErrorMessage or a Keyword for Survey Response

Timestamp

Date and Time

RPI Server Time

ResultsTableName

Text

Offer History Table name

  1. Finally, once the RPI_TwilioStates_<Channel Name>_<ChannelExecutionID> table is populated with all records, the following is an example of a SQL query that is executed during Channel Synchronization task processing. On successful execution, all records will be ingested into the Offer History States table.

SQL 
INSERT INTO "dbo"."OfferHistory_States" (
    "RPContactID",
    "ChannelExecutionID",
    "FulfillmentState",
    "EventName",
    "Timestamp"
)
SELECT 
    a0."RPContactID",
    a0."ChannelExecutionID",
    a1."FulfillmentState",
    a1."EventName",
    a1."Timestamp"
FROM 
    "dbo"."OfferHistory" a0
JOIN 
    "dbo"."RPI_TwilioStates_TwilioChannel_234" a1
    ON a0."ChannelExecutionID" = a1."ChannelExecutionID"
    AND a0."AddressKey" = a1."AddressKey"
WHERE 
    NOT EXISTS (
        SELECT 1
        FROM "dbo"."OfferHistory_States" b0
        WHERE 
            b0."ChannelExecutionID" = a0."ChannelExecutionID"
            AND b0."RPContactID" = a0."RPContactID"
            AND b0."FulfillmentState" = a1."FulfillmentState"
            AND (
                b0."EventName" = a1."EventName"
                OR (b0."EventName" IS NULL AND a1."EventName" IS NULL)
            )
    );

Other connectors

Outbound Delivery channel connector

  1. Processing engagement results via Callback Service API. Outbound Delivery provider will be sending events via RPI callback service. Typically, Outbound Delivery will invoke the callback service URL configured. The URL is in the following format:
    https://<HOST NAME OR IP ADDRESS>/api/PostEvents?channelLabel= Outbound%20Delivery
    The events posted via callback service API will be sent to the queue path configured in QueueProvider/CallbackServiceQueuePath in the appsettings.json file.
    Refer to Callback Service Configuration for more details.

  2. The RPI Queue Reader service reads and processes the Outbound Delivery events available in the queue configured in CallbackServiceQueuePath in RPI tenant's System Configuration. Processing of events will continue until there are no events available in the queue.

  3. If the tenant’s main data warehouse is running either Snowflake DB or AWS Redshift DB, the recommendation is to utilize S3 bucket to bulk-load the records from S3 to temp table on the database. To enable bulk-load using S3 bucket, enable the following RPI system configuration options:

    • AWSPerformCOPYFromS3 = true

    • AWSSourceS3BucketName = <ECP Name>\<Bucket Name>

  4. When the RPI Queue reader processes the events from the queue, each Outbound Delivery event will be processed and ingested into the RPI_OutboundDeliveryEvents table. The table has the following structure:

Column Name

Data Type

JSON Property

EVENTID

Text

EventId

CHANNELEXECUTIONID

Number

cxid

STATENAME

Text

statename

STATEDETAILS

Text

statedetails

EVENTDATE

Date and Time

eventdate

SELECTED

Boolean

False

CHANNELID

Text

channelid

ENTRYDATE

Date and Time

RPI Server Time

EMAIL

Text

Email

EVENT

Text

Event

CLIENTID

Text

cid

  1. The Outbound Delivery channel synchronization system task retrieves all state data from the RPI_OutboundDeliveryEvents table, then processes and ingests into the State import table configured in Outbound Delivery channel configuration settings. Here is an example of a SQL query being executed:

SQL 
INSERT INTO [dbo].[OutboundDeliveryEvents] WITH (ROWLOCK) (
    [RPCONTACTID],
    [EVENTID],
    [CHANNELEXECUTIONID],
    [STATENAME],
    [STATEDETAILS],
    [EVENTDATE],
    [SELECTED],
    [CHANNELID],
    [ENTRYDATE],
    [EMAIL],
    [EVENT],
    [CLIENTID]
)
SELECT 
    i.[RPCONTACTID],
    i.[EVENTID],
    i.[CHANNELEXECUTIONID],
    i.[STATENAME],
    i.[STATEDETAILS],
    i.[EVENTDATE],
    i.[SELECTED],
    i.[CHANNELID],
    i.[ENTRYDATE],
    i.[EMAIL],
    i.[EVENT],
    i.[CLIENTID]
FROM 
    [dbo].[RPI_OutboundDeliveryEvents] i
WHERE 
    i.[CHANNELID] = 'd81914f0-cf8f-47a1-a696-efb7e360b956';

  1. Finally, once the State import table is populated with all records, the following is an example of the SQL queries that are executed during Outbound Delivery channel Synchronization task processing. On successful execution, all records will be ingested into the Offer History States table.

SQL 
-- Insert into RPI_CDS_c8d93b2e463241e8b6b8c6
INSERT INTO [dbo].[RPI_CDS_c8d93b2e463241e8b6b8c6] WITH (ROWLOCK) (
    [RPCONTACTID],
    [CHANNELEXECUTIONID],
    [STATENAME],
    [STATEDETAILS],
    [EVENTDATE]
)
SELECT 
    i.[RPCONTACTID],
    i.[CHANNELEXECUTIONID],
    i.[STATENAME],
    i.[STATEDETAILS],
    i.[EVENTDATE]
FROM 
    [dbo].[OutboundDeliveryEvents] i
WHERE 
    i.[SELECTED] = 1;

-- Insert into OfferHistory_States
INSERT INTO [dbo].[OfferHistory_States] (
    [RPContactID],
    [ChannelExecutionID],
    [FulfillmentState],
    [EventName],
    [Timestamp]
)
SELECT 
    r1.[RPContactID],
    r1.[ChannelExecutionID],
    a1.[STATENAME],
    a1.[STATEDETAILS],
    a1.[EVENTDATE]
FROM 
    [dbo].[jboh22] r1
INNER JOIN 
    [dbo].[RPI_CDS_c8d93b2e463241e8b6b8c6] a1 
    ON r1.[RPContactID] = a1.[RPCONTACTID]
    AND r1.[ChannelExecutionID] = a1.[CHANNELEXECUTIONID]
WHERE 
    NOT EXISTS (
        SELECT 1
        FROM [dbo].[jboh22_States] s1
        WHERE 
            s1.[ChannelExecutionID] = r1.[ChannelExecutionID]
            AND s1.[RPContactID] = r1.[RPContactID]
            AND s1.[FulfillmentState] = a1.[STATENAME]
            AND s1.[Timestamp] = a1.[EVENTDATE]
    );

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.

Contact Support Close