How to model sales by #prospects by 'age of prospect' at time of sale?
Page 1 of 1
How to model sales by #prospects by 'age of prospect' at time of sale?
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?
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

» Data model for Sales Order and Sales
» Sales DW Model
» How best to model Timesheet facts against Sales Order facts
» How to model comparible store sales for retail data
» Modeling a Sales Order to Billing to Shipping consolidated data model
» Sales DW Model
» How best to model Timesheet facts against Sales Order facts
» How to model comparible store sales for retail data
» Modeling a Sales Order to Billing to Shipping consolidated data model
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|