Multiple transaction types, Average Transaction Value, and KPIs

View previous topic View next topic Go down

Multiple transaction types, Average Transaction Value, and KPIs

Post  jon_k on Tue Nov 16, 2010 4:48 am

Hello all

I wonder if any of you have encountered something like this before, and if you have, how you approached it? Apologies if I ramble a bit:

We're a retailer that deals with fairly bulky goods. Depending on the purchase, a lot of customers can't fit the goods in their car, and we take quite a lot of deposits (either 10-50% part payments, or the customer fully pays but collects the goods later).

Historically, because of the way our OLTP system works and reports, we've tracked "sales" as being the point when goods are fully paid for. However, since we've got our hands on the data and looked through the tables, I think we can report on "sales" as the goods being physically collected, which means we'll have a better grasp of stock movements and stock-outs (this would certainly make our Finance Director happier as well).

So for a 100 deposit on a 1000 sale, we'd have potentially four+ transactions (assuming there's no order cancellation or return of the goods)

  1. The initial order, where 100 is paid, but it relates to a 1000 sale
  2. A payment of a portion of the balance, say 400
  3. A payment of the rest of the balance, 500 making the sale "complete" in the historical sense
  4. The physical collection of the goods

Now we'd like to know what our profit is going to be on an order in advance (so get the 1000 from number 1, above). We'd also like to know what physical payments we've taken for cashflow forecasting (so get the 100, 400, 500 from 1-3 above). For continuity and tracking manager performance, we'd like to be able to see fully paid sales (the 1000 from number 3), and of course we want to see collected sales (1000 from number 4).

Now I can do this in a single fact table (it'll just be quite wide when I add in cost of sales, sales less tax, discounts, etc for each of the measures above), and then we can see the measures side by side without "drilling across" - this sounds good as it's intuitive to users and makes drag and drop easy to understand (with appropriate labels). But one of the KPIs our operations team use is Average Transaction Value (ATV). I can get the denominator with a COUNT(DISTINCT transid) (transid is a degenerate dimension on the fact table), but this will give me 1000/4 rather than 1000/1, using the example above.

My question is about how you'd arrange the fact table(s) to meet these requirements? Is it a case of getting the DW sponsor to say "this is the sales measure we're using for ATV, live with it"? Separate fact tables with different summaries?

This is really confusing to me, and I'm not even getting into the fact that one sale can have multiple salespeople, so ATV when slicing by salesperson is different from when you slice by location...

Many thanks in advance for any advice you can give

jon_k

Posts : 4
Join date : 2009-03-16

View user profile

Back to top Go down

re: Multiple transaction types, Average Transaction Value, and KPIs

Post  g8rpal on Mon Dec 06, 2010 11:53 am

Add a column called ATVDenom or TransCompleted.

using your example:
row paid TransCompleted
1 100 0
2 400 0
3 500 1

compute ATV as sum(paid)/sum(transcompleted)

g8rpal

Posts : 10
Join date : 2010-12-03
Location : Jacksonville, FL

View user profile

Back to top Go down

Re: Multiple transaction types, Average Transaction Value, and KPIs

Post  jon_k on Tue Dec 21, 2010 7:43 am

Thanks for the suggestion, but wouldn't this lead me to a situation like (for example) the following? I've put a previous (simple) transaction into the table.

Code:
row---paid---transcompleted
1-----1000---1
2------100---0
3------400---0
4------500---1

ATV will be fine over the whole table, namely 2000/2 = 1000. But if I were to run the ATV after row 3, but before row 4, I'll be left with 1500/1 = 1500 which is clearly not correct.

jon_k

Posts : 4
Join date : 2009-03-16

View user profile

Back to top Go down

Re: Multiple transaction types, Average Transaction Value, and KPIs

Post  BoxesAndLines on Tue Dec 21, 2010 11:13 am

You need an attribute at the sale level (or order level). This value aggregates all the transactions within a given sale. This can be implemented via a dimension or degenerate dimension.


Last edited by BoxesAndLines on Tue Dec 21, 2010 1:33 pm; edited 1 time in total
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Multiple transaction types, Average Transaction Value, and KPIs

Post  rademola on Tue Dec 21, 2010 1:29 pm

I can see a couple of stars here: sales, payments and collections. I believe we can use at least one or two conformed dimensions to "link" them to enable drilling across (see http://www.rkimball.com/html/designtipsPDF/DesignTips2005/DTKU68SimpleDrill-AcrossinSQL.pdf). I dont know what your reporting tool is but I would imagine that a reporting db views layer can handle that nicely.
The Sales fact headline sales transactions at the time it was first captured while the Payment will holds the payments, obviously. A Sales Status dim for example can track the last payment (Incomplete for all payments until the last which becomes Complete)... if that makes sense. The sales fact also holds the original sales amount and other details. It is also useful for calc'ing what cash is outstanding, forecasting cashflow, etc.
Collections is a fact that tracks "completed sales - useful for stockouts, inventory tracking, returns etc.

And to calc ATV, there should be covering degenerate dimension across the entire business process that can tracks the lifecycle of sales to delivery and is therefore present on all the stars above. To my mind, OrderNo/SalesID, etc is a candidate. From what I know, ATV is A KPI measured over periods, so the I would expect to be dividing by count of number of sales for example over certain periods i.e. ATV = Total Completed Payments/Count of SalesIDs or OrderNos... definitions depends on the business.

Hope that helps

rademola

rademola

Posts : 9
Join date : 2010-12-21

View user profile

Back to top Go down

Re: Multiple transaction types, Average Transaction Value, and KPIs

Post  hang on Tue Dec 21, 2010 6:12 pm

I think rademola is definitely down the right track. The point is, don't mix fact granularities in a single table.

Payment fact is similar to Order Header - Order Line fact situation. So you would have a payment header fact and payment line fact separately. Not quite sure about consolidating payment header into sales fact. It really depends on the dimensionality associated with the two facts. The timing of the events and the complication of returned items are ones to watch.

Part from the normal payment transaction amount, I would store a semi-additive measure in the payment line fact to indicate the balance. You may also need a final flag, possibly in a junk dimension with other low cardinality attributes. If time of the day is important for the payment, you may need to store a timestamp degenerate dimension in combination with Datekey.

Collection is a interesting fact, as it may occur at different point of time from payment and you could also have multiple collections for one sale or order. You may not want to count collections to calculate ATV. So a separate collection fact is smart arrangement.

You would have SalesRepKey in both payment header and line facts so that you can have aggregates (e.g. ATV) at different levels. You should be able to drill across between facts through dimension conformance. Create views to consolidate facts from different aggregate levels if necessary.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Multiple transaction types, Average Transaction Value, and KPIs

Post  mrngorickets on Thu Mar 31, 2011 4:41 am

Hi

This topic help me a lot in developing my project. I will contribute more when I finished it.

If you want to get more materials that related to this topic, you can visit: Financial controller KPI

Best regards.


Last edited by mrngorickets on Wed Jun 08, 2011 5:27 am; edited 2 times in total (Reason for editing : Update)

mrngorickets

Posts : 1
Join date : 2011-03-31

View user profile

Back to top Go down

Re: Multiple transaction types, Average Transaction Value, and KPIs

Post  hamburg113 on Fri Apr 22, 2011 10:52 am

Hi

This topic help me a lot in developing my project. I will contribute more when I finished it.

hamburg113

Posts : 3
Join date : 2011-04-22

View user profile

Back to top Go down

Re: Multiple transaction types, Average Transaction Value, and KPIs

Post  hamburg113 on Sun May 01, 2011 8:30 pm

hamburg113 wrote:Hi

This topic help me a lot in developing my project. I will contribute more when I finished it.

If you want to get more materials that related to this topic, you can visit: KPI examples

Best regards.

hamburg113

Posts : 3
Join date : 2011-04-22

View user profile

Back to top Go down

Re: Multiple transaction types, Average Transaction Value, and KPIs

Post  hdblue on Sat May 14, 2011 6:22 am

Dear friends

Thanks for sharing. I likeMultiple transaction types, Average Transaction Value, and KPIs very much.

It's wonderful.

If you have some time, pls visit my blog at: Sales KPI

Rgs

hdblue

Posts : 3
Join date : 2011-05-14

View user profile

Back to top Go down

Re: Multiple transaction types, Average Transaction Value, and KPIs

Post  johnterry807 on Sat Aug 06, 2011 10:58 am

Hi,
I am also very interested in this subject, but the reference is very limited. You can share documents as well as experience? Thanks!

If you want to get more materials that related to this topic, you can visit: Purchasing KPIs
Best regards.


Last edited by johnterry807 on Sun Aug 14, 2011 11:26 am; edited 1 time in total (Reason for editing : Update)

johnterry807

Posts : 1
Join date : 2011-08-06

View user profile

Back to top Go down

Re: Multiple transaction types, Average Transaction Value, and KPIs

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