Les Barbusinski’s Answer: The type of customer information stored in a data warehouse or data mart varies with the size and function of the DW and is limited by a) the kinds of products and/or services your company offers and b) the types of customers you have. For example, a financial services company may want to capture information about each customer’s accounts, assets, policies, etc. while a pharmaceutical company may want to capture information about a customer’s orders, shipments, invoices, payments, etc. Furthermore, since the financial services company deals with individuals, it will probably want to trap demographic information about its customers (such age, education level and average annual income) while the pharmaceutical company whose customers are clinics, hospitals and drug stores may want to capture information about its customers’ tax-exempt status.
That said, here are some general categories of customer information you may want to capture in your data warehouse or data mart:
- Type (e.g., individual, company, organization, government agency, etc.)
- Name (e.g., first, middle, last, prefix/suffix, title, company name, aliases, etc.)
- Addresses (e.g., home, mailing, business, billing, etc.)
- Contact Information (e.g., phone/fax/pager numbers, e-mail addresses, etc.)
- Demographics (e.g., age, income level, education level, ethnicity, marital status, retirement status, disabled, occupation, citizenship, etc.)
- Characteristics (e.g., D&B rating, FICO score, political affiliation, industry group, market segment, company type, etc.)
- Relationships/Householding Information
- Holdings (e.g., account balances, policy premiums, etc.)
- Visits (e.g., office/store visits, off-site visits, etc.)
- Sales Activity (e.g., purchases, orders, shipments, invoices, payments, rebates, returns, warranty claims, etc.)
- Contact Activity (e.g., inbound/outbound calls, letters, e-mails, complaints, quotes and RFQs, RFIs, RFPs, etc.)
- Campaign Activity (e.g., mailers, coupons/vouchers, loyalty card usage, etc.)
- Clickstream Activity (e.g., Web site visits, page clicks, session durations, etc.)
Hope this helps.
Larissa Moss’ Answer: A one sentence requirement ("identify all objects relating to a customer") is not much to go by. My immediate reaction would be to model the customer as a data subject area. That would produce a normalized entity-relationship model to include the customer entity, customer-to-customer recursive relationships, customer parent-child hierarchies, customer-dependent entities (other objects directly associated with the customer) and possibly extend it to customer-product relationships (as in CRM), including product hierarchies and customer-organization relationships (organization being "your company"). On the other hand, maybe you are being asked to model a functional subject area, which would be the business activities (mainly monetary) around customer, such as order processing, order fulfillment, sale, shipment, billing, collections, etc. In that case a denormalized multidimensional model would be more appropriate. I'd be happy to discuss this with further, if I could find out more about the requirements. You can contact me at email@example.com or (626) 355-8167.
Clay Rehm’s Answer: This depends on your industry, however I would consider the following to be the basics:
- Demographic information (name, address, e-mail, phone, fax, etc)
- How long they have been a customer
- Buying patterns (what products or services have they bought since they have been a customer)
- Preferred customer info (Frequent Flyer as an example)
- Number of contracts/accounts/etc.
- Revenue derived from each customer
- Expenses incurred from supporting each customer
- Which customers were lost and why