tracking customer activity

Hi,

I'm working on a model where the business wants to keep track of customer activity. An active customer is one who purchased goods within the last 30 days - otherwise they're classed as an inactive customer.

The business wants to track how many customers went from being active to inactive over time and vice versa (i.e. went from being inactive to active).

In order to do this I thought I'd create a fact_customer_movement table where each row represents the movement of one customer in either direction. Something like this:

customer_key

date_moved_key

direction_key

direction_key would link to either "Moved from Active to Inactive" or "Moved from Inactive to Active".

Does this approach seem reasonable? Or is there a different/better way to approach this type of requirement?

Thanks,

I'd probably call the Direction Dimension something like Event dimension.

You are basically capturing the events of the Customer, except for the very first event - when they became a customer. I figure they would want to know the total number of Customer, number of new customers, active customers, and inactive customers.

I'd just capture active customer in the customer dimension. You could even do it as a mini dimension if the volume is high.

Thanks for the reponses.

I like the idea of an event dimension.

I did think of capturing active customer in the customer dimension but the business would want to know how many new customers we got in March 2010 or any other period in history so having it in the dimension would only show how many active customers we have right now.