How to Manipulate data for multiple visits per person?

Question

I have a query to solve. I have data regarding customers and number of visits done to them. These are in two tables. So I want to join two table and create different features so that I can find better/lagging customers from the data based on scoring.

• customer_dataset:
  1. id(unique id for customer)
  2. name
  3. assigned_user_id(user assigned)
  4. company_id(company_which user works for)
  5. expected value(revenue expected)
  6. status(whether a lead/customer([0,1]categorical))

visit_dataset

• customer_id(unique_id)
  2. user_id
  3. company_id
  4. date(visit date done)
  5. schedule_date(actually scheduled)
  6. next_action_date(if any next action)
  7. status(visit done or not)

So I need to score lead/customer on the basis of these data and find a list of top user. Also from the number of visits done, I need to figure out how many average/sum of visits done per customer so that I can find next forecast of visit(In case he doesnt go or forget about lead).

I'm new in manipulating data with pandas. How can I;

1. find total visit done per customer and other features from dates?
2. any resource that I can follow for how to work with dates data in pandas?
3. I'm trying to use featuretools to get the features.(any other advise)?
4. also I'm trying to automate this feature. So I'll feed in last 30 days visits as data and then I have to figure out the next visit date(prediction)Important (I so don't know how will i do that but trying ;P)

Sorry If i missed any info, please give me feedback if any issue.

Thanks a lot.

Answer 1

So, here you have a couple of questions:

1. Find total visits per customer by date

To do this bit, you have simply do a pandas Dataframe groupby, like so:

customer_id.groupby(['user_id', 'date']).size()

2. Resources for working with dates in pandas

https://pandas.pydata.org/pandas-docs/stable/user_guide/timeseries.html This is official documentation from pandas, so should hopefully give you all the tools you need.

3. Unfortunately cannot answer the question on feature tools, so I look forward to seeing answers on this.
4. How to model the next customer visit date given their previous visits.

One way to do this would be that if customer visit frequently, then one idea would be to model this data with a Recurrent Neural Network (RNN) or LSTM (A video on the principles behind RNNs: https://www.youtube.com/watch?v=UNmqTiOnRfg), where it takes the sequence of inputs (dates when particular customer has visited) and return an output date after observing the sequence.
Before this though, might be useful to do customer segmentation and treat observe visiting patterns over customer groups than as individuals. One way to do this any clustering algorithm like agglomerative clustering.