Best design for a marketing/prospects database

[deekay]

I need some ideas for good robust design for additions to our MS
Access database.
We are marketing/client servicing company but I need to focus on the
marketing side right now.

Its mainly a marketing/CRM database with the main table structure like
the following:
Companies --> Contacts --> Activities

Companies are all companies we have targetted for marketing or are
customers/clients
Contacts are the executives/management and their contact info
Activities are any recorded conversations, emails (stored in separate
linked table) etc.

Now we are constantly looking for new prospects to target and these
could either be:
a) Totally new companies - which then have to be uploaded together
with any contacts
b) Companies on our database - we have tried them before with one
service or former customers

What I need is some design where we can uniquely mark a particular
company as a new prospect either because of some new info we get or
just from looking through past records on the database.

I need to be able to indicate that this is a prospect:
- for say either service A or service B
- chosen as a prospect on dd/mm/yyyy date
- provide a reason that this company was chosen

After this I basically need to filter this info and present to a sales
consultant who will check/qualify prospect and then also provide
feedback accepting/rejecting/delaying prospect.

I really would appreciate some assistance here.

 

[Tony Toews [MVP]]

I need to be able to indicate that this is a prospect:
- for say either service A or service B
- chosen as a prospect on dd/mm/yyyy date
- provide a reason that this company was chosen

After this I basically need to filter this info and present to a sales
consultant who will check/qualify prospect and then also provide
feedback accepting/rejecting/delaying prospect.

With the exception of Service A or Service B I'd just put that extra
fields on the Customer file. Unless you might think they're a
prospect again in a year or three. In which case I'd put all that in
prospects table linked to the Customer table.

BTW I see no reason to not put prospects in your customer table.
There should be a status flag indicating customer, prospect, out of
business, refuse to do business with them, etc, etc.

Now what if they are prospects for multiple services? Is that a free
format note field or a drop down combo box. If the latter and
multiple then I'd put that into a ProspectsServices table.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[deekay]

On Sep 23, 10:13 pm, "Tony Toews [MVP]" <[email protected]>
wrote:

Thanks for the feedback Tony, just to address some of your comments.

With the exception of Service A or Service B I'd just put that extra
fields on the Customer file. Unless you might think they're a
prospect again in a year or three. In which case I'd put all that in
prospects table linked to the Customer table.
BTW I see no reason to not put prospects in your customer table.
There should be a status flag indicating customer, prospect, out of
business, refuse to do business with them, etc, etc.

These companies can again be prospects in say 3 years or sooner
(depending on circumstances)
It does seem simpler if all is in the customer table the REASON field
could be a memo field which indicates in text why they a prospect
again.
There is a 'status flag' type field we have on the contact table (not
company) which at the moment indicates how far into the marketing
process the contact from the company we are speaking to is though --
perhaps it would be more suited the company level(?)

Now what if they are prospects for multiple services? Is that a free
format note field or a drop down combo box. If the latter and
multiple then I'd put that into a ProspectsServices table.

We were thinking combo box with A, B or AB as options if both, is it
still necessary for an additional table.
I'm just asking as I know I will be asked if another table is really
needed as its 'complicating' things.

Thanks

 

[Tony Toews [MVP]]

These companies can again be prospects in say 3 years or sooner
(depending on circumstances)

Then I'd put all the info in a prospects table so you can review the
history next time.

It does seem simpler if all is in the customer table the REASON field
could be a memo field which indicates in text why they a prospect
again.
There is a 'status flag' type field we have on the contact table (not
company) which at the moment indicates how far into the marketing
process the contact from the company we are speaking to is though --
perhaps it would be more suited the company level(?)

Maybe. You'd have to think on that one a bit.

We were thinking combo box with A, B or AB as options if both, is it
still necessary for an additional table.
I'm just asking as I know I will be asked if another table is really
needed as its 'complicating' things.

Yes it will complicate things. You will need another table and a
subform. But what if management decides that we are introducing a
new version of Product A with all these gee whiz, golly gee features.
So now you'd like to go back and hit just those prospects. Much more
difficult with a free format notes field where folks can type in
Prodct A, ProductA, Prod. A and so forth.

And one of my mottos is "You can never have too many tables."

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Jamie Collins]

I see no reason to not put prospects in your customer table.
There should be a status flag indicating customer, prospect, out of
business, refuse to do business with them, etc, etc.

One reason is that you'd end up with lots of nullable columns (I've
seen such Customer tables with 90+ columns, mostly nullable) with
unnecessarily-complex validation rules e.g.

((is_prospect = 'Y' AND chosen_date IS NOT NULL) OR (is_prospect = 'N'
AND chosen_date IS NULL))

I'd recommend a separate table to model prospects with NOT NULL
columns; if a company is not a prospect then it is simply omitted from
this table.

Jamie.

--

 

[FredFred]

I've been running Sales and Marketing for small/medium sized companies for 26
years and have been doing what you are trying to do on databases for 15
years, the last 10 of those on Access. A couple of thoughts.

First, what you are trying to do is available on low cost packages, most
notably Act and Goldmine. I tried those and ditched them because they were
too inflexible / non adaptive on the structural side, and because they are
"black boxes" that became big time wasters trying to figure out / know /
predict exactly what they were doing. I'm sure that Microsoft also has one,
but (with a few 98% exceptions like Access) they are the king of the vague
unpredictable "black box" concept (which is OK/good for some things and bad
for others.) But, for many companies the above packages would be a good
choice..

Next, there are a many variables that make each company's needs unique in
this respect. A big one is the size of the company and the number of people
who will be using this. Another is the nature of their "sales funnel"
which is at the heart of your quesiton of whether to put prospects in a
separate table or to enter them in your main companies table. This topic is
too lengthy to cover in general here. But the underlying question isn't
database structural, it's "when does a propect become worthy of the
investment of time for entry into a database and then the same question for
entry into a high grade / more disciplined database. In our case, bulk low
grade prospects are in seperate temporary tables (usually imported and not
enterd) & lists etc. But as soon as they become worthy of a second phone
call or contact , and when we have enough info to enter them with data
integrity (to avoid vagueness and dupllicates etc.) we enter them into our
main CRM database. From there on out you can just tag them appropriately
with fields (customer's vs prospects etc.)

We have a separate table which lists "ActEvents" which is action items and
a record of things that happened with that company. (because action items
all eventually become history.) Although you could make the case that
these should be linked to people, but we choose not to do so, mostly due to
the transience of people in particular positions at companies. We link these
to the company and just have our people include the name of the person that
the ActEvent relates to in that entry.

In our case, "projects" are also a table attached to the company.

Hope this helps a little.

Fred





tureks
at
ameritech
dot
net