I'm new to power pivot and somewhat new to excel. I think Power Pivot is what I need to help me out though as we don't have a formal CRM at our office. I'm creating a task list, actually a call list for customer service. First, it needs to provide customer service with a list of the people they need to call for the day as well as contact info and financial information from their application that may be necessary for conversation. At the end of the day, when the task is complete, this needs to be saved into a contact log.
I am trying to determine how to organize my data. Until now, we have been combining customer applicant (initial lead capture) information which includes status of the lead (Active, Not Interested, Not Qualified, etc.), contact information with many dates of interactions (introductory email, 1st email by customer service, most recent email, most recent call, etc.), totals of the number of touches (# emails and calls separately and combined), and even time to touch calculations to comprise Table #1. This results in a relatively wide table of about 40 columns. In addition, we need Table #2 to be a log/task list of our touches which includes the type of touch (call/email), the result of the interaction, the date it was scheduled, and the date it was completed. After completion, it needs to schedule a new task (the next phone call/email - I'm planning to use a macro), as well as update the count of touches, date of most recent call/email, change in status, etc in Table #2. Currently, we are just producing a Task List using a power query of Table #1 and manually updating Table #1. My thought is to use the data model concept to connect these two tables with a key.
I feel like we need a clear flow of data, but I’m getting confused about how to implement this in regards to creating a Task List (report?) that updates our current information as well as creates new Tasks. I am worried that I am going to create a spiderweb of connections.
First of all, should I make my first table smaller and create a few narrower tables linked by an applicant ID or is one wide table better? (Do I also use power pivot's calculated tables instead?
Secondly, should I create a separate table (Table #3) that is the Task List which pulls data from all of the above tables and then updates each one after customer service completes the tasks for the day? Customer service would hit an Update button at the end of the day to log all their activity and create a new task list. I’m not even sure if this is supposed to be a new table or essentially a query or report. My big question is if this is poor data modeling/database organization – to combine data from 2-4 tables/sources and then update them all from this one table. Or can I create a pivot table task list, edit it, and then update the data?
Note that the Task List will still have almost all the info (name, contact info, even total # of calls and emails) from Table 1 as it gives customer service the info they need in one place to have a conversation with the potential applicant.
Thank you so much for your help! I've been running around in circles and reading/watching lots of videos and not been able to figure out how best to design this!
I am trying to determine how to organize my data. Until now, we have been combining customer applicant (initial lead capture) information which includes status of the lead (Active, Not Interested, Not Qualified, etc.), contact information with many dates of interactions (introductory email, 1st email by customer service, most recent email, most recent call, etc.), totals of the number of touches (# emails and calls separately and combined), and even time to touch calculations to comprise Table #1. This results in a relatively wide table of about 40 columns. In addition, we need Table #2 to be a log/task list of our touches which includes the type of touch (call/email), the result of the interaction, the date it was scheduled, and the date it was completed. After completion, it needs to schedule a new task (the next phone call/email - I'm planning to use a macro), as well as update the count of touches, date of most recent call/email, change in status, etc in Table #2. Currently, we are just producing a Task List using a power query of Table #1 and manually updating Table #1. My thought is to use the data model concept to connect these two tables with a key.
I feel like we need a clear flow of data, but I’m getting confused about how to implement this in regards to creating a Task List (report?) that updates our current information as well as creates new Tasks. I am worried that I am going to create a spiderweb of connections.
First of all, should I make my first table smaller and create a few narrower tables linked by an applicant ID or is one wide table better? (Do I also use power pivot's calculated tables instead?
Secondly, should I create a separate table (Table #3) that is the Task List which pulls data from all of the above tables and then updates each one after customer service completes the tasks for the day? Customer service would hit an Update button at the end of the day to log all their activity and create a new task list. I’m not even sure if this is supposed to be a new table or essentially a query or report. My big question is if this is poor data modeling/database organization – to combine data from 2-4 tables/sources and then update them all from this one table. Or can I create a pivot table task list, edit it, and then update the data?
Note that the Task List will still have almost all the info (name, contact info, even total # of calls and emails) from Table 1 as it gives customer service the info they need in one place to have a conversation with the potential applicant.
Thank you so much for your help! I've been running around in circles and reading/watching lots of videos and not been able to figure out how best to design this!
