forecasting projection--how to approach this?

Question

forecasting projection--how to approach this?

dandenise316

Mr or Mrs. 500

Points: 574
More actions
August 19, 2015 at 1:52 pm

#310998

select
mailers
,offer
,responses
,applications
,SUBSTRING((CASE WHEN SUM(responses) = 0 THEN NULL ELSE (COUNT(applications)::float / SUM(responses)::float) END),1,6) App_Rate
from
mailtable n
WHERE
n.campaign_date >= '2015-01-01'
and n.datemailhitsbusiness < 2015-08-12
I'm trying to build out a forecasting methodology for mail pieces that we send out.
Based upon the mailers (first field listed) and offer (second field), I'd like to be able to project a weighted average projection of how many responses and applications we can expect to receive. I'd then like to expand that into the case when statement.
In other words, I'd like to say based upon us mailing 1 million people using a preapproved offer, based upon our sends of a volume of 1 million to a preapproved offer population, we can expect X responses, x applications, etc.
Thoughts on this? I'm not even sure where to get started to be honest. All the data is houses in one table and I have historical data up until the start of the year to base this upon.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Sounds like a data mining problem. That subsystem has been included with SQL Server since, what, SQL Server 2000? And almost no one is using it!! Not using that is the biggest shortcoming there is I think within the SQL Server ecosystem!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

dandenise316 Mr or Mrs. 500 Points: 574 More actions · Answer 2

Let's assume that's not an option, and the forecasting I'm looking for isn't anything overly sophisticated.

How would one approach this?

Based upon the attributes in 2 fields (volume of mail and type of mail sent) I want a weighted projected value for a couple of different conversion values we have.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

dandenise316 (8/20/2015)
Let's assume that's not an option, and the forecasting I'm looking for isn't anything overly sophisticated.
How would one approach this?
Based upon the attributes in 2 fields (volume of mail and type of mail sent) I want a weighted projected value for a couple of different conversion values we have.

Well, it data mining is an option since it is free with the product.

But for projections you simply analyze past data and determine a number(s) with which to multiply expected future activity, right? That is simple averaging for the most part.

I don't understand you mention of "weighted" though. Your initial description doesn't speak to what the weighting factor is based off of, at least not that I saw.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

dandenise316 Mr or Mrs. 500 Points: 574 More actions · Answer 4

Correct sir, but how to calculate that multiplier?

I'm interested in finding out what the average responses / applications, etc are based upon the mailer volume and mailer type fields. I do have historical data, just not sure how to write the syntax to get this started.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

I can't possibly help you with syntax for a query without table create script, sample data and expected output.

in general it will look like this though I think:

select field1, field2, ...

sum or count reponse field / sum or count application field (or * for all)

from mytable

group by field1, field2, ...

You may need NULL handling and divide by zero handling logic. You may need to have different numbers for different date ranges or classes of mailings, etc.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 6

dandenise316 (8/20/2015)
Correct sir, but how to calculate that multiplier?
I'm interested in finding out what the average responses / applications, etc are based upon the mailer volume and mailer type fields. I do have historical data, just not sure how to write the syntax to get this started.

It's not easy but it is possible. It's called "curve fitting" and here are a couple of decent posts on the subject. Note that I've not tried any of them and so don't take this as any kind of a recommendation on my part, but they look mostly correct.

http://www.sqlservercentral.com/Forums/Topic872361-392-1.aspx

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77311

Of course, no listing of URLs would be complete without the lookup for Google.

https://www.google.com/?gws_rd=ssl#q=best+fit+curve+sql+server

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)