Trying to design Sales to Promos to Sales Relationship help Please.

Hi,

I'm struggling to solve seemingly simple thing.

The tables we're getting now from outside Provider are 3 large tables:

DimCustomers: CustomerID, CustonerName, etc.
SalesFacts: CustomerID, PromoCode, SaleAmt, SaleDate, etc.
DimPromos: CustomerID, PromoCode (not unique combination- sometimes send the same promo to the same customer twice).

The actual scenario is that in reality it seems to be non trivial to me (many to many Relationship), in other words.

1. Customers might make a Purchase without any Promo at all or respond to one or Many Promos 0 or many times.
2. Same Promo (for some reason sometimes) might be sent to the same Customer (prospect) more than once.
If it's sent more than once we don't know to which promo attribute this sale.

I need to produce correct counts in terms of:
Number of Sales, Sales by Promos, Number of Promos, Promos by Customer, etc.

What should we do?

Thank You

S

Would you have a product dimension as well? So basically you may have the following dimensions:

DimCustomer
DimProduct
DimPromotion

Note the DimPromotion should only contain attributes that describe the promotion, such as promotion name, start and end date etc. Your DimPromos does not look like a dimension but rather a Pomontion_Customer coverage fact table, and normally you should have a Product-Promotion coverage fact table as well.

Therefore, your SalesFacts would consists of CustomerSK, ProductSK and PromotionSK plus date key and measures. I think with these dimensions and fact(less) tables in place, you should have a reasonable base to cater for your queries.

Because it looks to me that there are many to may between Sales and promos.
I need to produce correct counts in terms of:
Number of Sales, Sales by Promos, Number of Promos, Promos by Customer, etc.

We do not care about Products at this point.

Thank You

s

sergioza wrote:I need to produce correct counts in terms of:
Number of Sales, Sales by Promos, Number of Promos, Promos by Customer, etc.

If that's all you want, queries against your sales fact should achieve that.

sergioza wrote:Because it looks to me that there are many to may between Sales and promos.
s

Yes it is. Given any two fact tables (i.e. sales and promotions sent) it is always a many to many relationship. But, there is a basic pattern to resolve this. Each fact is queried separately and aggregated on shared dimensions, the aggregated sets are then joined on those dimensions (one-to-one).

Thank You and please forgive my ignorance,

Now Promo Table is following and for a small percentage Promos sent to the same customer

PromoCode DropDate Description

a 1/1/2011

a 1/10/2011

b 3/12/2011

c 4/12/2011

and on the other End SalesTable has only PromoCode and no drop date, so it's impossible to know to which Promo to attribute some Sales.
--------------------------------------------------------------------------------------------------------------------------------
Does it mean that in order to produce correct counts I need to say using SSAS,

just link Promos and Sales by PromoCode or
break Promotable into 2 (DimPromo and FactsPromo) and link Sales to DimPromo and DimPromo to PromoFacts?

or there is something else needs to be done in terms of aggregation to produce correct counts?

Thank You

S

The promo dimension table should only describe a promotion. Actions relating to a promotion are reflected in fact table(s).

In the case of sales, the promotion FK references the promotion that was associated with the sale of that item. Sending a promotion announcement to a customer is another business event and is reflected by another fact table (offering), such a table may have date, expiration date, promotion, and customer as dimensions.

Assuming the sales fact has transaction date, customer, and promotion as dimensions, it should be fairly easy to attribute a promotion to a purchase. You can measure the response rate of a promotion by summing the number of customers sent a promotion against the number of those customers who responded. You can also determine if sending the notice more than once improved (or hindered) the level of response.

As far as actually counting the number of promotion notices sent, its your (well, actually the business's) call. You either count the number of messages sent or count the number of distinct customers who were sent messages.

Now, if you run the same promotion at different points in time, they need to be treated as separate promotions and assigned separate dimension rows. If the business does not want to assign new promo codes for each instance, treating the promo dimension as type 2 will at least allow you to keep things separate in the DW. Promotion start and end dates should be part of the promotion dimension, ensuring new rows are added (in a type 2) when these dates change.

Finally, if this is a long standing promotion based on prior actions of the customer (such as, they buy a TV and you follow up with offerings of service plans or discounts on cables or whatever) then it becomes a business call to determine which message to attribute to the sale (if it really matters). Also, since the offering fact contains an expiration date, you can bound identification based on that.

If such precise attribution is a really big deal for the business, and you can't do it with the information you have, the business needs to tighten their process. For example, the can include a printable coupon in the email with a bar-coded unique ID that the customer must present to get the deal. Of course, this means enhancements to the POS system and the data flow. The business can decide if such precision makes sense and if they are willing to spend the money to implement it.

Hi,

Thank You and just to tell you more.

We don't have full fledge DW and trying to keep solution simple.
Also we have limited access to the data. We essentially receive these 3 full replacement files monthly from outside vendor we can't control.
Sending the same promo happens for different reasons, sometimes because address changed or by mistake more than once.

Tables: Customers, Promos and Sales.

Should we just arbitrarily treat/mark one of "duplicate" promos as "unknown promo" or maybe persummarize this table in SQL first by doing arbitrarily something like:

Select PromoCode, Count(*) CountPromos,Max(DateSent),
Group by Promo

Thank You

S

I was editing my response while you were responding (added a couple of paragraphs at the end). It all boils down to what information you have and how important it is to improve the information. You can only work with what you got. If the business wants more it becomes a matter of cost vrs benefit.

As far as working out what to do in your situation, you've listed some alternatives. Discuss them with the business and let them choose.

Makes perfect sense.

Many, Many Thanx for your Time and Support.

S