I am working in a large store and we keep track of all our customers'
purchases, i.e. when, how much and what items they buy.

Currently this database has billion of records. However, we are having
problem analysing it based on the following criteria:

1. Loyal Customers - customers who purchase a certain brand x > 60% of
the time over z period.
2. Switcher - customers who purchase a certain brand x < 60% of the time
3. Competitive - only purchase from a competitive brand y but not brand
x
4. Related - customers who purchase from "related" categories only (for
example, if brand x is toothpaste, these customers only purchse mouth
wash)
5. Bypasser - customers who do not purchase even from related
categories.

I am at lost how to come up with the proper measures - for example, how
do I know for a certain brand x how many loyal customers or switchers
over a period z?

My basic fact table contains: customer_id, product_id, purchase_date,
purchase_price, purchase_quantity (among others)

and my product dimension (in hierarchy) contains:
category, related_category, competing_group, product_id.

Can someone please help?

Jeff.


*** Sent via Developersdex http://www.developersdex.com ***