+ Reply to Thread
Results 1 to 5 of 5

Call Log Analysis Help

  1. #1
    Registered User
    Join Date
    02-01-2022
    Location
    Midwest, USA
    MS-Off Ver
    365
    Posts
    2

    Lightbulb Call Log Analysis Help

    I have a call log that I need to analyze. It contains account numbers, phone number, call date, call time, contact, and call direction.

    I need to either write a macro or formulas or create a pivot table to calculate the number of call/dial attempts that occur on an account per day, but only those that occur after the first contact call. If all calls on an account are non contact then I don't need to worry about them.

    I also need another set of calculations that expand that from 1 day to 7 days. Calculate the number of calls that occur in a 7 day period AFTER a contact call.

    Attached is a small sample of the spreadsheet that contains 40,000+ calls.
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Call Log Analysis Help

    For single date range, can be accomplished with Power Query

    Please Login or Register  to view this content.
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    02-01-2022
    Location
    Midwest, USA
    MS-Off Ver
    365
    Posts
    2

    Re: Call Log Analysis Help

    Woof. I have only viewed power queries, never attempted to formulate one or input one. Thank you so much.

    I have so many questions, but have attempted to narrow it down to a few:

    1) can you explain what the final data set represents? My actual call log is 30,000 rows or more, and when I ran the query it returned over 11,000 rows. In a given month there are usually only a few dozen instances where a call occurs after contact, so I'm wondering if there is more I need to add to the query? There doesn't appear to be much too it, so I'm wondering where the calculations are.

    2) Does the query utilize only visible data from the table? I have inbound and outbound calls on the complete log, and I only want to concentrate on the Outbounds, if I sort and filter the initial data table, will the query 'get it'?

    3) Just a yes or no - can you use a query within in a macro? (I've got a very simple user form that has 2 buttons - push 1 for data [a macro opens the file explorer, person chooses applicable spreadsheet, then macro imports data into a new sheet] - then push 2 to run test [macro sorts/filters/formulates the data and provides a spreadsheet of results, saves the file as a new document with a custom name, and then advises the employee that the analysis is complete].
    *I'd love to be able to implement the query into the Macro so it runs when they press the test button.


    My mind is blown by this power query business.
    I'm going to be up for days reading into it and learning all about it.

    Thank you again for your help, and thanks in advance for answering any/all/none of my questions.

    ~Sarah.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Call Log Analysis Help

    First, let me explain the step I took in the PQ
    1. When PQ brings data into the PQ Editor it sometimes add a time factor to the date field. I removed the time
    2. I then filtered the data to show only Contact made
    3. I then grouped the rows by the Account Number and Date to count the number of contacts made
    4. Because you only included Outbound calls, there was no need to filter that field (you may need to add that filter prior to grouping)
    5. I added a custom column to subtract one day as you indicated you wished to exclude the first contact.
    6. I then removed the original count field.

    As to hidden rows, I believe that they are included

    I believe that you can use in a Macro, but that is not among my skillset for Power Query.

    My favorite book to learn this feature is by Ken Puls and Miguel Escobar-- https://www.amazon.com/Master-Your-D...rmat=4&depth=1

  5. #5
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Call Log Analysis Help

    I am trying to understand your requirement...Is everything after first contact counted 1's & 0's or only 1's directly after first contact?
    What is the expected result you require as this is not clear...
    30000017 = ?
    30000018 = ?
    30000028 = ?
    ...

    Untitled.png
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 03-06-2014, 11:00 AM
  2. Repeat call count for call center
    By arifmasum in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-08-2013, 10:03 PM
  3. Data Analysis- advanced pivot functions for employee analysis
    By Dsankie in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 05-10-2013, 12:30 AM
  4. How to set call count according to call date and time
    By naveen4pundir in forum Access Tables & Databases
    Replies: 0
    Last Post: 04-27-2012, 02:13 AM
  5. using CALL to call methods mandatory?
    By jerryliang2k in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-05-2008, 06:16 PM
  6. Replies: 2
    Last Post: 06-25-2006, 12:10 PM
  7. [SOLVED] How can I call Fourier Analysis from a Visual Basic macro?
    By Barthog in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-17-2005, 10:05 AM
  8. [SOLVED] Analysis ToolPak installed but no Data Analysis option
    By Eric Stephens in forum Excel General
    Replies: 3
    Last Post: 02-02-2005, 06:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1