Customer Relationship Management
part of the ArsDigita Community System
by Jin Choi
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
.
jsc@arsdigita.com