Customer snapshot

View previous topic View next topic Go down

Customer snapshot

Post  grahan007 on Wed May 28, 2014 3:11 am

Hi

We have a situation where we are reporting the churn. We have customers each customer is assigned to an account manager. In the beginning of the year the churn targets are assigned to each account manager. now we want to report per account manager the actual churn vs target.

As the customer can change account manager over time but we want to report the churn as per beginning of the year situation. e.g.

Customer A has an account manager X on 2014/01/01 so at that time the churn target was assigned to account manager X based on his customers. But on 2014/03/31 the account manager of customer A has changed to Y and after two months the customer canceled the contract. so in the contract cancellation request we see account manager Y but for the target calculation we want to assign this churn amount to the account manager at the time when targets were assigned i.e to account manager X.

What would be the good model for this type of requirement. Shall I build two customer dimensions one with monthly snapshots and other with current situation.

Thanks in advance

Regards

Harris

grahan007

Posts : 18
Join date : 2009-05-26

View user profile

Back to top Go down

Re: Customer snapshot

Post  BoxesAndLines on Wed May 28, 2014 7:46 am

How about 2 relationships from one dimension? The first one is static. It is set at create time and never changes. The second one works like a typical type 2.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Customer snapshot

Post  grahan007 on Wed May 28, 2014 8:07 am

BoxesAndLines wrote:How about 2 relationships from one dimension?  The first one is static.  It is set at create time and never changes.  The second one works like a typical type 2.

It will not work as the requirement is to assign the account manager who was associated at the time of target setting. In my example if the cancellation request is created after 2014-03-31 the account manager for that customer is Y but at the time of target setting the account manager was X so I need to assign this cancellation to account manager X instead of Y.

So practically we need both overviews, one with the account manager at the time of creation of request and other with the account manager at the time of target setting.

Regards

Harris

grahan007

Posts : 18
Join date : 2009-05-26

View user profile

Back to top Go down

Re: Customer snapshot

Post  BoxesAndLines on Wed May 28, 2014 9:12 am

Yep, the first FK points to X, the second FK points to Y.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Customer snapshot

Post  nick_white on Wed May 28, 2014 11:42 am

Similar, but slightly different suggestion, to BoxesAndLines' one:
1. Create a (possibly factless) fact table when targets are set. Dims would be Customer, Account Manager, Date, possibly the Target Process Dim (e.g. Name = "1Q2014 Target"; other attributes you may have), possibly "Target Set By" Dim, "Target Approved By" Dim, etc.
2. Create a churn (cancellation event) fact table. Dims would be Customer, Account Manager at time of event, Date, etc.

"Join" the two facts via the Customer Dim to get the churn for the Account Manager assigned when the target was set
Use the Churn fact table to get the churn for the Account Manager assigned at the point of churn

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

as an attribute of customer dimension

Post  krishgenius on Tue Jun 03, 2014 5:16 pm

How about capturing target relationship manager and current relationship manager as 2 attributes of the customer dimension. I understand that manager is a dimension by itself and this relationship would work if we have a bridging table. But in this scenario, it looks like at a point in time, the customer and relationship manager relation is known and there should be not a problem having it this way.

krishgenius

Posts : 3
Join date : 2014-06-03

View user profile

Back to top Go down

Re: Customer snapshot

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum