How to model sales by #prospects by 'age of prospect' at time of sale?

View previous topic View next topic Go down

How to model sales by #prospects by 'age of prospect' at time of sale?

Post  2by4 on Wed Feb 27, 2013 10:22 am

The current basic model has the following dimensions:

Date, Product, Customers/Prospects, Promotion (the two types are sales promotion & prospect acquisition promotion e.g. pay-per-click).
and a Sales fact (grain one per $ transaction by prospect/customer) & Prospect acquisition fact (grain one per every prospect added).
(the two facts are related via the customer/prospect table)

The dimensions are pretty standard, but here are the relevant attributes of promotion
Promo_type (sales promotion, prospect acquisition)
Promo_Theme (this comes from marketing, many promotions will share a theme so they can be grouped)
cost,
startdate,
enddate

The problem I am facing is this: For each prospect acquisition promotion I would like to calculate the total number of prospects acquired (easy) and also the total amount of customer sales that those prospects subsequently made (sales fact) and group that by promotion to calculate the promotion profit ratio (sales/cost) & also the promotion profit per name (sales / # of prospects). Lastly, I would like to qualify the sales by "age of the prospect at the time of sale in days" which would need to calculate the days elapsed between acquiring the prospect
(date from prospect_fact table vs date from the sales_fact table)

The problem is that the above calculations need to use cost which is currently an attribute of promotion, is this frowned upon?
Additionally the two facts are at very different grains, so I'm confused. Should I create a new aggregated fact to handle this?




2by4

Posts : 5
Join date : 2012-06-25

View user profile

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