Customer Relationship Management

part of the ArsDigita Community System by Jin Choi

Admin directory: /admin/crm
data model: /doc/sql/crm.sql
procedures: /tcl/crm-defs

The Big Picture

Publishers want to track a relationship with a customer over time by classifying them by various metrics such as site activity or buying activity. This module provides a way to specify states that a user may be in, and a way to specify state transitions based on any metric which can be expressed in SQL. This models the progression of a relationship better than a static numeric worth value. These states can then be used to target actions at particular classes of users.

An example: an e-commerce site might define the following states:

raw user: just registered, hasn't really done much yet
good prospect: has bought one or more thing in the last month
solid customer: has bought more than 3 things in the last month
great customer: has bought more than 10 things in the last month
slipping customer: formerly a solid or great customer, but has fallen under threshold in the past month
dead user: has not done anything for the last three months

The transitions might be

from raw user to good prospect or dead user
from good prospect to solid customer or dead user
from solid customer to great customer or slipping customer
from great customer to slipping customer
from slipping customer to dead user
from dead user to good prospect

Under the Hood

A user's current state and the date it was entered is stored as part of the users table:


crm_state               varchar(50) references crm_states,
crm_state_entered_date  date, -- when the current state was entered

The allowable states are listed in


create table crm_states (
        state_name      varchar(50) not null primary key,
        description     varchar(1000) not null -- for UI
);

Allowable state transitions are stored in


create table crm_state_transitions (
        state_name      not null references crm_states,
        next_state      not null references crm_states,
        triggering_order        integer not null,
        transition_condition    varchar(500) not null,
        primary key (state_name, next_state)
);

The transition_condition field specifies a SQL fragment which will get called as


update users
set user_state = **next_state**, crm_state_entered_date = sysdate
where user_state = **state_name**
and (**transition_condition**)

Periodically (as defined by the parameter UpdatePeriodHours in the [ns/server/servername/acs/crm] section and defaulting to 24 hours), each transition_condition fragment will be run as above, in the order specified by triggering_order.