I-CRM
Sections:
I-CRM E-Mailing
CSC Database Table
CSC E-mail Broadcasts
I-CRM Set up
I-CRM (Intenet 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.
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:
SendSafe global.inc.asa file:
SendSafe storefront.config file:
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:
It is very common for the I-CRM email blast to use the MBOptIn flag. This flag is set to 2 for all customers who will recieve an I-CRM email (i.e. all customers where MBOptIn=1). The MBOptIn flag is reset to 1 as each email is sent out. This management of the MBOptIn flag is controlled by the SQL set 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 recieve 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. |
| 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.
|
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]