ArsDigita Archives
 
 
   
 
spacer

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
spacer