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:

• Automatic QOS follow up e-mails i.e. "Did you receive your order? Are you pleased?"
• Automatic "tickle" e-mails to customers who have not purchased in N months.
• Abandon shopping cart follow up: automatic e-mails to customers who have items sitting in their shopping cart for N months or weeks and not purchased them.
• Automated "reminder" e-mails sent to employees when follow up is due
• Subscription renewal reminder "Your subscription renewal is coming up. To avoid renewal hiccups, please make sure your cc# and exp date are correct."

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:

SendSafe global.inc.asa file:

SendSafe storefront.config file:

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:

• The MBOptIn is set to 2 for all customers who will receive an I-CRM email (i.e. all customers where MBOptIn=1).
• The MBOptIn flag is reset to 1 as each email is sent out.

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:

• OrderLineItem.orderflags often set to include flags like: ICRMABANDONCARTTICKLE
• OrdersPostProc.ProcessStep
• Customers.orderingpreferences

The job looks like the example below:

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]