Re: Tracking Customers

["Chris Travers" <..hidden..>]

On 1/25/07, Joshua D. Drake <..hidden..> wrote:
> Hello,
>
> One of the things we have decided to try and do for (likley) 1.3 is to
> have some basic customer relationship management. I have been thinking
> alot on this and have come up with some rough ideas on a model.

To me CRM includes good contact management, appointments, comments,
and TODO items.  Other functions might be added, but that would
qualify as basic CRM to me.  Of these, I think that appointments is
one area that is somewhat difficult to do properly.  While I expect
1.3 to be on the road to CRM, I wonder if it may take a few more
revisions to make this reach a basic lebel.  But this is largely a
semantic/marketing quibble and nothing of substance :-)

However, my experience suggests a few changes to the proposed db
schema for the customers and companies.

> company:
> ----------------+--------------------------+--------------------------------------------------------------
>  id             | integer                  | not null default
> nextval('company_id_seq'::regclass)
>  name           | character varying(150)   | not null
>  type_id        | integer                  | not null
>  status_id      | integer                  | not null
>  created        | timestamp with time zone | not null
>  referred_by_id | integer                  |
> nextval('company_initial_id_seq'::regclass)
>  revision       | integer                  | not null
>  tgrm_index     | tsvector                 |
>
>
> id is just an easy way to manipulate the record
>
> name is the primary key

Hmmm.....  Wonder if this is safe.  Suppose I have retail customers.
Two share the same name.  How would this work?  Would it not be better
(and less redundant) to use id as the primary key since that is what
we use it as anyway?
> type_id refers to a company_type column such as vendor,customer,lead,etc...

Ouch.  This is going to break one of the things I was hoping to
accomplish (i.e. being able to set up a supplier as a customer without
multiplying records, which would become impossible if the name was the
primary key).  Better to use a pivot table.

One might also add a GIFI field so that income or expense (as
appropriate for the type) might be automatically posted with a certain
GIFI attached (I know some locales require something like this).
> status_id refers to a company_status table, active, inactive, dead

Would it be better to have a start and end date so that one can
essentially say "no more invoices after today" while still
back-entering old invoices?
> referred_by_id refers to another company or person from which the
> company was referred by (thus relationship)

Good idea.  All for that.
> created date the record was created
>
> revision fairly optional but used if we want to track all changes to the
> record

I would suggest letting revision control go for now unless there are a
number of people who want/need it.  Revision control on databases
isn't that hard to bolt on with triggers and archive tables.
> tgrm_index used for fast partial word searching instead of ~ or ILIKE
I would add a few more fields:

Tax ID (FEIN or SSN for those in the US), something of the SIC type or
an equivalent.

As I think about it, it might be useful to have a phone field too for
main corporate numbers.  But this could be added as a custom field.

comments field (text)

> company_alias

I like this idea.  I would add a text comments field.
> -----------------------+--------------------------+--------------------------------------------------------------------
>  id                    | integer                  | not null default
> nextval('company_alias_id_seq'::regclass)
>  company_id            | integer                  | not null
>  company_alias         | text                     | not null
>  description           | text                     | not null
>  created               | timestamp with time zone | not null
>  revision              | integer                  | not null
>
> company_id refers to the company record
>
> company_alias is an alias for the company. This comes from my own
> experience where a customer will call and have three different names. I
> know them as foo.com, accounting knows them as Acme Corp. That kind of
> thing.
>
> description is a short note about the company
>
>
> I have a ton more but thought I would throw this out there including
> people/contacts, addresses etc..

Here are a few more possibities (note that custom fields can always be
added, so I figure I am just stating the basic ones).

id (primary key)   SERIAL
surname VARCHAR(16)
given_name VARCHAR(16)
address1 VARCHAR(32)
address2 VARCHAR(32)
city VARCHAR(16)
state VARCHAR(16)
zipcode VARCHAR (16)
type_id INT references contact_type_id (i.e. billing, support,
customer_contact, shipping, returns, etc).
BIC text
IBAN text
start_date
end_date
email
telephone
email
backup_contact_id INT references contacts (id) (i.e. who to contact
when this individual is unavailable).
comments (text)

I don't think I am up to do much regarding appointments or generic
TODO items.  The former has a number of fun corner cases which are
difficult to solve (and we have to determine what is desirable
behavior which may vary from customer to customer), and the latter is
going to take some time to hash out the exact requirements.  There is
also some question that I am sure will be discussed whether these
should be core or add-on modules.

Hope this helps.
Chris Travers