Data Over Time Query

building semantic layer
1

Hello,

I am looking at designing my first Universe which will look at order details and customer details for an online webstore and I’m wanting to track changes to the customers profile but I’m unsure how to go about it. Getting the data out is not a problem but how I report on changes over time is confusing me.

As a specific example: We receive the customer data when they open their account online. We then receive an update when they make a change to their account. We would like to report on the effectiveness of a promotion, say subscribe to the newsletter. If we were to overwrite the customers file when they subscribed to the newsletter then we would an accurate number of the current subscribers but have no historical record changes. If we time stamped the action then I believe we would end up over counting the number of subscribers. For example, below a customer registers with us and takes the newsletter then decides they don’t like it. We run a promotion and they decide they like to register again so resubscribe.

CustID_ Newsletter_ Date
123_______ 1 _____ 01/01/09
123________0 _____ 07/01/09
123 _______ 1 _____ 14/01/09

In my (very) basic understanding, if I was to run a query on total number of subscribers based on that data I would return 2 when we only have one.

Any advice on how best to structure the data tables and universe would be much appreciated.

cashflagg (BOB member since 2009-11-12)

2

You would use a subquery on the table to get the number of current subscribers, if thats what you are after.

Your count object could be

Sum(NewsletterTable.Newsletter)

Your Subquery would be something like:-

NewsletterTable.Date = Select (Max(a.Date) from NewsletterTable a Where a.CustID = NewsletterTable.CustID)

You can put this in as a condition in the universe.

Mak 1 :uk: (BOB member since 2005-01-06)

3

Thanks for that, your help is appreciated.

cashflagg (BOB member since 2009-11-12)