MKT810 – Upload Personify360 List into Lyris

On a periodic basis, a specific marketing list may need to be created or updated into Lyris Lists_table. This batch process is used to perform this task and create records in the members_table in Lyris to store the email addresses and names of those who are part of the list. Only members of the Personify list who have email addresses are transferred to Lyris. If you set the Regenerate Flag to “Y,” not only will the process update the information for existing members, but it deletes any members that have opted out of the list since the last run of MKT810.

Report Comments Logic

When the MKT810 process is run and the system generates the report, for members that were not added to Lyris, the following comments may display:

Comment

Description

Same email address was found in Lyris Members_ table but for different Customer

This error will display when the system uploads a customer for with a UserName, Domain, and ListName, and these values already exist in the Lyris's members_ table for a different customer or for same customer with different Sub_customer.

The following query is run:
Update A SET Status_Flag = ''N'', comment = ''Same email address was found in Lyris Members_ table but for different Customer: '' + B.TIMSS_Master_Customer_ID + ''-'' + str(B.TIMSS_Sub_Customer_ID)
 From MKT810_TEMP A INNER JOIN ' + @cv_Lyris_DBLink + 'members_ B ON A.UserName = B.UserNameLC_ AND  A.Domain = B.Domain_ AND A.ListName = B.List_ Where status_flag IS NULL and (A.MASTER_CUSTOMER_ID <> B.TIMSS_Master_Customer_ID OR (A.MASTER_CUSTOMER_ID = B.TIMSS_Master_Customer_ID AND A.SUB_Customer_Id <> B.TIMSS_Sub_Customer_ID))

Duplicate Email Address was found in the list for Customer

This error will display when the system uploads different customers with the same UserName, Domain, and ListName.

The following queries are run:

  1. Update MKT810_TEMP SET Status_Flag = 'N', 
    Comment = CASE WHEN Master_Customer_ID IS NOT NULL THEN 
    'Duplicate Customer was found in the list, Customer: ' + Master_Customer_ID
    ELSE
    'Duplicate Prospect was found in the list, Prospect: ' + Prospect_ID
    END
    From MKT810_TEMP A WITH (NOLOCK) Where ID >Any(Select ID From MKT810_TEMP B WITH (NOLOCK) 
    Where (A.UserName = B.UserName And A.Domain = B.Domain And A.ListName = B.ListName
    AND ((A.Master_Customer_Id = B.Master_Customer_Id And A.SUB_Customer_Id = B.SUB_Customer_Id)
    AND A.Status_flag is null)) 
    UNION ALL
    Select ID From MKT810_TEMP B WITH (NOLOCK) 
    Where A.UserName = B.UserName And A.Domain = B.Domain And A.ListName = B.ListName
    AND A.Prospect_Id = B.Prospect_Id ANd A.Master_Customer_Id IS NULL
    AND A.Status_flag is null)

  2. Update MKT810_TEMP SET Status_Flag = 'D', 
    Comment = CASE WHEN Master_Customer_ID IS NOT NULL THEN 
    'Duplicate Email Address was found in the list for Customer: ' 
    ELSE
    'Duplicate Email Address was found in the list for Prospect: ' 
    END
    From MKT810_TEMP A WITH (NOLOCK) 
    Where id not in (select id from (select UserName,Domain,ListName, Min(id) id 
    from MKT810_TEMP group by UserName,Domain,ListName) a) AND Status_flag is null

Incorrect contact method

This error will display when the Contact_Method_Code <> 'EMAIL'

 

The following query is run:

Update MKT810_TEMP SET Comment = 'Incorrect contact method: ' + Contact_Method_Code, Status_Flag = 'N' Where Contact_Method_Code <> 'EMAIL'

Allow Email/Mail Preferences are not set for this member

This error will display when the Allow_Email_Flag = 'N' for the customer.

Invalid email address

This error will display if the customer's email address does not contain the '@' symbol (i.e., invalid email address).

Primary email address not found

This error will display if there is no email address defined for the customer.

Added New to the Segment

This comment will display if there is a new customer uploaded for the Lyris segment

Duplicate Customer was found in the list, Customer

This error will display if there are duplicate master_customer_ids in the list.

Duplicate Prospect was found in the list, Prospect

This error will display if there are duplicate prospects in the list.

[Null]

No comments will display if the MKT810 process is run with the "Regenerate Flag" parameter is checked but there is no change in the list.

Parameters

Parameter

Description

Required?

Organization

The Organization ID for which you want to run the report.

Read-only

Organization Unit

The Organization Unit ID for which you want to run the report.

Read-only

Run Mode

If uploading a marketing list (from MKT002) into Lyris then Run Mode = MKT002_LIST_CODE. Else if uploading a production list (from MKT006) into Lyris then Run Mode = MKT006_KEY_CODE.

Yes

Personify List/Key Code

Personify360 List to be Transferred: If Mode = MKT002_LIST_CODE, then this is the List Code to be transferred from MKT_List_Master and Detail. If Mode = MKT006_KEY_CODE, then this is the Key Code to be transferred from MKT_Mail_Master and Detail.

Yes

Lyris Template

Select a template list from Lyris which will identify the blank list to be copied to create a new list in Lyris.

Yes

Regenerate Flag

If this checkbox is checked then existing Lyris list members will be updated with Personify360 List members. Else if this flag is not checked then a new list will be created in Lyris with all members in the Personify360 List.

Yes

Sample Report