This topic contains 4 replies, has 2 voices, and was last updated by 
-
Posts
-
I have a Call History Log (excel file exported from a voip phone company) which is a list of phone calls organized by date/timestamp.
I wish to determine which phone calls represent a ‘new’ phone number being called for the first time in the most recent 2 months.
The Log extends back in time for >6 months
I’d like to know if this can be done in either PowerPivot or PowerQuery. I’m still getting my head around when (if ever) either tool can create a uniquely calculated field (column entry) for a row in a table.
If I were to do this in Excel I’d start by sorting the Log with newest calls on top, and then for each row doing a matching downward against the row’s phone number to see if it appears in the last 2 months. I’d have to experiment to see if there is an easy way to have a dynamic range extended backwards in time from a date for each row, but worst case I’d write a VBA script to just laboriously search backwards for a 2 month period. If there were a match for the row I’d mark an added ‘New?’ column with NO. On the other hand, if the row’s phone number didn’t appear in past 2 months then I’d mark it YES. By filtering on YES I would know the first date that a phone number is being called, and that would represent that phone number as being a new addition to a sales person’s Pipeline.
The ultimate goal here is to use phone records to identify how many new prospects are being added to a sales person’s pipeline. In the future I’ll work out the other side of pipeline, where I can detect that a phone number is removed from a sales person’s pipeline because the phone number appears (with a datestamp) in a future followup table.
Call History LogColumns: Time Line Length ContactName ContactPhone Reached User CallResult
Note – the ‘Today 7:10pm’ column is a problem for another day.
<table width=”512″>
<tbody>
<tr>
<td width=”64″>Time</td>
<td width=”64″>Line</td>
<td width=”64″>Length</td>
<td width=”64″>Contact</td>
<td width=”64″>Contact Phone</td>
<td width=”64″>Reached</td>
<td width=”64″>User</td>
<td width=”64″>Call Result</td>
</tr>
<tr>
<td>Today 7:10 PM</td>
<td>(512) 327-00xx</td>
<td>0:01:10</td>
<td></td>
<td>(512) 638-70xx</td>
<td>Inbound</td>
<td>Jim Smith</td>
<td>Answered</td>
</tr>
</tbody>
</table>Hi,
Interesting data.
If you are just asking for a simple yes or no, the answer is yes, it is doable.
If you would like to see one how it could be done (once the time/date column is cleaned up), let me know. The level of effort should be much, much less than in Excel.
A question: Lets say two salesmen (users) are calling the same company for different reasons or different projects, are you interested in for each salesman if they have called that number or if either (anyone) has called that number in the last two months?
Also, how would you define two months? Is 60 days OK?
Tom
Doable is great … hopefully it’s not overly complex 😉
It would be great to get a psuedo code overview to get me started in the right direction, but just knowing its doable is helpful.
It is sufficient to know that any salesperson has started conversations with the customer. The viewpoint I have is from the household, as it is represented by the phone number. Knowing that the household has been entered into the sales pipeline tells me that we have a sales opportunity. If there are multiple sales people calling that’s okay, it could be one is helping the other in some way.
60 days would be fine, it’s just an arbitrary time that might change.
By the way, there is another interesting aspect to associating calls with households, because households often have(two adults with sometimes home, cell1 and cell2 phone numbers
Hi,
For only a few columns, you will gain many awesome insights from your dataset.
I threw about 4 totally fictitious records together and in a few minutes created calculated columns for: “Previous Call to Contact Phone” (date), “Subsequent Call to Contact Phone” (date), “Days Since Previous Call” (integer) and “Should Followup” (contains either “Yes” or blank).
Since in a previous discussion, you mentioned you have Excel 2013, I created a linkback table on the Excel side for rows where “Should Followup” is yes and then linked that result set back into Power Pivot (linkback tables cannot be created in Excel 2010). At that point I had to restrain myself and write this email and attach the workbook…
Best regards,
Tom
Thanks so much Tom. I can see the calculated columns and that there is much awesome-ness in only a few lines. Thanks for showing it in a working example too!
By the way, I did read M is for (data) Monkey, got a lot out of it, and tonight (hopefully) I can tackle the newly released v2 of Rob Collie’s book.
I’m getting very excited about being able to start putting useful data together, so I’ll likely be back here with more (and harder ;-)) questions
The forum ‘Power Pivot’ is closed to new topics and replies.