Multiple transaction types, Average Transaction Value, and KPIs

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

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)

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.

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.

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

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.

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.

Hi

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

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.

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

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.