I-CRM

Sections:
I-CRM E-Mailing Job
CSC Database Table
CSC E-mail Broadcasts
I-CRM Set up
Broadcasting subsystem

I-CRM (Internet CRM) is a powerful system which automated CRM functions for online businesses. The I-CRM system works hand in glove with the CSC system and e-mail broadcasting.

I-CRM can be used in these ways and more:

Website customization may be required for custom functions.


I-CRM SET UP

Set up requires nothing more than adding I-CRM jobs to the DB and setting up any needed I-CRM e-mail templates in CSC.

Prerequisits is the setup of CSC database table and the following fields in the global.inc.asa files are robot storefront config files:

Performance tuning:

Setting Notes
Anti-spam / anti-relay settings in email server Be sure to set a high enough outgoing message limit on the account(s) that will be used for return addresses. This is not the SMTP login account; it is the account specific in the FROM:
Speed per chunck and burst rate The e-broadcaster sends out emails at a rate to help prevent the legitimate emails from being inappropriately flagged as spam. This rate is approximately 0.5 to 1.3 seconds per email (approximatly 2,769 emails per hour to 7,200 emails per hour). This figure of 3,000 emails/hour is the max burst rate of the system.
Chunck size and max email throughput The e-broadcaster sends out emails at a variable rate to help prevent the legitimate emails from inappropriately being flagged as spam. The e-broadcaster sends out bursts of emails in packets called chunks. The system will send one chunk approximately every 10 minutes. The chunk size is set by the size limit (top *) used in param2 of the brodcasting job. The default chunk size is 250. This effectively limits the maximum system throughput to 1,500 emails per hour. This setting should be maintained below the burst rate to achieve an uneven stream of emails to help prevent inappropriate flagging as a source of spam.

SendSafe global.inc.asa file:

Keyword Description
Application("EMBroadcastEnabled") = true Set true / false to enable and disable the optional function.
Application("EMBDefaultOptIn") = 1 Default state for OptIn flag (0/1). This flag controls whether a new customer is automatically added to the e-mail list of if they must take action to request addition to the list.
Application("EBroadcastSharedEmailAddr") = "broadcast@mysite.com"
For I-CRM only implementations set this value to "n/a"
This is a DISPLAY ONLY data field which is used to show the user which address to email non-I-CRM email broadcasts. This field is not used for I-CRM email.

SendSafe storefront.config file:

Keyword Description
EBROADCAST POP3 USERID = JohnDoe

The SMTP authentication or POP-Before-Send. ESMTP Auth or POP-Before-Send is selected by the EMAIL POP BEFORE SEND.

EBROADCAST POP3 PASSWORD = password

The SMTP authentication or POP-Before-Send. ESMTP Auth or POP-Before-Send is selected by the EMAIL POP BEFORE SEND.

EBROADCAST MAILSERVER = mail.yourbiz.com The domain name of the e-mail server to use for broadcasting.

 


I-CRM EMAILING JOB

This is a very powerful general purpose I-CRM e-mailing tool. This function is used to send almost any type of customized HTML e-mail to customers or potential customers.

The I-CRM E-Mail system works like this:

  1. The Job contains an SQL statement which returns a list of recordIdentifiers, e-mail addresses, and personalization "e-mail merge-able" information
  2. The Job contains an (optional) update SQL clause which can be used to update a DB record in someway which prevents duplicate I-CRM e-mails.
  3. The Job specifies a CSC record which is the template for the e-mail which will be sent out in CSC E-mail Broadcasts
  4. The CSC template can contain tags which are replaced with information from the SQL select statement (i.e. [0], [1], [2]).
  5. Ebroadcast records will be created for each chunk of emails broadcast by the I-CRM job.

While the JOB is very flexible and can be controlled in many different ways, the jobs queued automatically from SendSafe Admin pages will use the following control procedures:

This management of the MBOptIn flag is controlled by the SQL UPDATE clauses entered into param1 and param2 of the job code.

Very important: You must limit the size the SQL queries in the job to 250 records (i.e. TOP 250). Unlimited queries can result in very large e-mails which may clog up some systems. The system will reject SQL which does not include a TOP 250 limit. If you have more than 250 records the system will automatically spawn new jobs to handle each 250 record chunk until all orders have been processed.

Very important: You must sort (order) your queries in a way that will work with processing in 250 record chunks. Failure to order your queries by will result in missing or duplciate entries if you have more than 250 items returned by your query.

This I-CRM function uses the E-Mail broadcast server as configured in the <storefront>.config file.

Additional relational DB tables are often needed as management control blocks for I-CRM functions. These tables are used to control how often or what type of I-CRM e-mail is send out. The following DB columns are also often used for this control function:

The job looks like the example below:



Parameter value
param1

This is the select SQL statement used to pick the customers (or other records) who will receive I-CRM e-mails and a set of merge-able data columns.

The first data columns returned by this query are assigned numbers. These numbers related to variabes used in the update SQL statement and the outgoing e-mail. The first column returned is assigned to the variable [0], The second column returned is assigned to the variable [1], and so forth.

[0] = must always be the primary record locator key.

[1] = must always be the e-mail address to which this I-CRM e-mail will be sent.

An optional semicolon delimited second sub-parameter is the customerId of the person who queued the job. This data will be saved in the email report record (EMBroadcast).

param2

This is an SQL update statement. This parameter can be null. This SQL update statement can use [0],[1], etc. variables from param1 SQL Select statement.

Multiple update SQL statements can be included where each statement is separated by a semicolon.

param3 This parameter contains semicolon delimited values.
  • This parameter contains the CSC recordId from which to build this HTML E-Mail.
  • The second entry in this parameter is the subject line for the email.
  • The third entry in this parameter is the from address for the email.
  • Optional: The forth entry in this parameter contains a chunking code which controls how the job is broken up into multiple jobs i.e. "(CHUNKING)" + job indexkey of the orignal parent that started the emailing job and spawned the first chucking job.

Example of an SQL selection query:

select top 250 lineitemid, addresses.email, date, addresses.name, items.fullname, orderlineitem.orderflags 
from 
orders, customers, orderlineitem, items, addresses 
where 
orders.customerid = customers.customerid and 
orders.transactionid = orderlineitem.transactionid and 
items.itemcodeid = orderlineitem.itemcodeid and 
customers.addressid = addresses.addresscodeid and 
orderlineitem.orderflags not like '%icrmabandoncarttickle%' and 
status like '%{under construction}%' 
order by date desc

Example of an SQL update statement:

update orderlineitem set orderlineitem.OrderFlags = orderlineitem.OrderFlags + ',ICRMABANDONCARTTICKLE' 
where orderlineitem.OrderFlags is not null and LineItemID = [0];
update orderlineitem set orderlineitem.OrderFlags = 'ICRMABANDONCARTTICKLE' 
where orderlineitem.OrderFlags is null and LineItemID = [0]

In the SQL above the primary method of record selection is the replacement of the variable "[0]" with the current record number being processed. This variable (string replacement) works by taking the Nth SQL Query response from the prior query and performing a replacement. In this example [0] would use the 1st value in the Query response; just as [1] would result in the 2nd value in the query string.

Example of an SQL Query statement used for I-CRM Subscription messages:

Select TOP 250 OrderLineItem.LineItemID,email
from Orders, OrderLineItem, OrderLineItemExt, ItemsExt, Customers, Addresses 
where 
Orders.transactionid = OrderLineItem.transactionid 
and Customers.CustomerId = Orders.CustomerId
and Addresses.AddressCodeId = Customers.AddressId
and OrderLineItem.ItemCodeId = ItemsExt.ItemCodeId 
and OrderLineItem.LineItemid = OrderLineItemExt.LineItemid 
and NextOrderDate between CAST( 
                          CAST ( DATEPART(MONTH, DATEADD(day, @TargetDaysFromNow, getdate() ) ) AS char ) + '/' + 
                          CAST ( DATEPART(DAY, DATEADD(day, @TargetDaysFromNow, getdate() ) ) AS char ) + '/' + 
                          CAST ( DATEPART(YEAR, DATEADD(day, @TargetDaysFromNow, getdate() ) )  AS char ) 
                          AS datetime)
                      and CAST( 
                          CAST ( DATEPART(MONTH, DATEADD(day, @TargetDaysFromNow+1, getdate() ) ) AS char ) + '/' + 
                          CAST ( DATEPART(DAY, DATEADD(day, @TargetDaysFromNow+1, getdate() ) ) AS char ) + '/' + 
                          CAST ( DATEPART(YEAR, DATEADD(day, @TargetDaysFromNow+1, getdate() ) )  AS char ) 
                          AS datetime)
and SubscriptionPeriodsLeft = 1 
and SubscriptionEnabed > 0 
and SubscriptionForcedOrderSKU = 'SR-006'
and Orders.ApprovalCode like 'Approved:%' 
and orderlineitem.OrderFlags not like '%ICRM' + rtrim( CAST ( DATEPART(MONTH, getdate() ) AS char ) ) 
                                      + rtrim( CAST ( DATEPART(DAY, getdate() ) AS char ) ) + 
                                      rtrim( CAST ( DATEPART(YEAR, getdate() )  AS char ) ) + '%'

order by NextOrderDate desc

The above SQL works by checking each day for orders which will come due "@TargetDaysFromNow" number of days from today AND have only one refill left in the subscription (SubscriptionPeriodsLeft = 1).

Example of an SQL Query and Update statements used for I-CRM Customer Import Welcome messages:

select top 250 Customers.Customerid,email,name,NTUserName,NTUserPassword 
from customers, customersExt, addresses 
where 
Customers.Customerid = customersExt.Customerid and 
addresses.addresscodeid = customers.addressid and 
MBOptIn = 2 and email is not null 
order by DateCreated desc

update customersExt set customersExt.MBOptIn = 1 where customersExt.MBOptIn = 2 and Customerid = [0]

 

Stopped or aborted I-CRM CSC broadcasts can be restarted by queuing a new job following these instruction. tip: The parameters for the job can be found in the last audit record for this job.

Setting notes
Set Param1 to the standard SQL logic used for all CSC e-broadcasts. select top 250 Customers.Customerid,email,name,NTUserName,NTUserPassword,company,address,city,state,zip,country,phone from customers, customersExt, addresses where Customers.Customerid = customersExt.Customerid and addresses.addresscodeid = customers.addressid and MBOptIn = 2 and email is not null order by DateCreated desc
Set Param2 to the standard SQL update logic used for all CSC e-broadcasts. update customersExt set customersExt.MBOptIn = 1 where customersExt.MBOptIn = 2 and Customerid = [0]
Set Param3 to the following codes to form a string like this:
498726737;This is my subject line;kb@c-prompt.com;(CHUNKING)1726352
  • FIELD #1: The CSC recordId from which to build this HTML E-Mail.
  • FIELD #2: The subject line for the email.
  • FIELD #3: The "from:" address for the email.
  • FIELD #4: The chunking code + either: job indexkey of the orignal parent that started the emailing job and spawned the first chucking job.