DigitalRhoads
New Member
- Joined
- Jan 28, 2013
- Messages
- 4
We are trying to count the number of leads (segmented by Lead Score) that came in through our internet forms overnight that the call center will have to respond to on the next morning when the call-center opens. Our call center is open Monday-Thursday (7am-7pm) & Friday (7am-5pm); it is closed on Saturday & Sunday. We not only will want to be able to run this report on the morning of (in the future), right away we need to be able to export a previous month or quarter of data (Sheet 2) for analysis (the analysis formulas will be on Sheet 1).
I will give 2 examples of what I need:
Example #1:
On Tuesday-Friday mornings (I will just use Tuesday in this example) we want a count of all of the internet leads that came through between Monday 7pm and Tuesday 7am. The count will need to be broken out by Lead Score (-1,0,1,2,3,4).
Example #2:
On Monday morning, we want a count of all internet leads that came in on Friday after 5pm, plus all leads on Saturday, Sunday and those that came in on Monday before 7am (when the call center opens). The count will need to be broken out by Lead Score (-1,0,1,2,3,4).
It has been requested that the data be broken out in a few different ways:
The Database Exported will be place on 'Sheet 2', and an example of the Data layout is as follows:
If it will help, I can add the following columns to the Exported Data, if it will help make the formulas simpler (I will label with column & rows in case you want to use them in your recommended formula):
I will give 2 examples of what I need:
Example #1:
On Tuesday-Friday mornings (I will just use Tuesday in this example) we want a count of all of the internet leads that came through between Monday 7pm and Tuesday 7am. The count will need to be broken out by Lead Score (-1,0,1,2,3,4).
Sample:
A1:Date
>>>A2:3/5/2013
B1:Score -1
>>>B2: 191
C1:Score 0
>>>C2: 152
D1:Score 1
>>>D2: 134
E1Score 2
>>>E2: 95
F1:Score 3
>>>F2: 63
G1Score 4
>>>G2: 38
A1:Date
>>>A2:3/5/2013
B1:Score -1
>>>B2: 191
C1:Score 0
>>>C2: 152
D1:Score 1
>>>D2: 134
E1Score 2
>>>E2: 95
F1:Score 3
>>>F2: 63
G1Score 4
>>>G2: 38
Example #2:
On Monday morning, we want a count of all internet leads that came in on Friday after 5pm, plus all leads on Saturday, Sunday and those that came in on Monday before 7am (when the call center opens). The count will need to be broken out by Lead Score (-1,0,1,2,3,4).
It has been requested that the data be broken out in a few different ways:
- by date
(leads that came in on the previous day/date after 7pm through the current date at 7am) - by day of the week
(on Tuesdays, we would want to see internet leads from Mondays 7pm through Tuesday 7am; ~ this is to get averages on days of the week for forecasting over time from Quarterly exports) - by weekdays & weekends
(weekends begin Friday 5pm and end Monday 7am)
The Database Exported will be place on 'Sheet 2', and an example of the Data layout is as follows:
- A2: 3/1/2013 1:48:44 AM
(Date & Time Stamp) - K2: 2
(Lead Score: scores include the following -1,0,1,2,3,4)
If it will help, I can add the following columns to the Exported Data, if it will help make the formulas simpler (I will label with column & rows in case you want to use them in your recommended formula):
- N2: 14:40:28 (Time Only)
- O2: 3/7/2013 (Date Only)
- P2: 2013 (Year Only)
- Q2: 3 (Month Only)
- R2: 26 (Day Only)
- S2: Thursday (Day of Week)
Last edited:
