I have numerous sets of values over a time range representing withdrawals per customer. How do I calculate the most representative set from them?

Question

I am new to analysis and I have encountered a problem that I cannot seem to find information to help me. This may be due to not having the knowledge to formulate the question correctly so I will try below. I would be more than happy to research the answers on my own, I just need to be pointed in the right direction.

I have a data set with a large number of transactions representing withdrawals of cash from customer accounts (1,000 customers and transactions). I am trying to find a frequency of these transactions that represent the average frequency across all customers. An example of what the data might look like can be found below.

The date range can span years and can be any date. Not all customers will have a transaction on a given date and the values can vary.

I am trying to create a representation of the average customer in this group and a transaction timeline will be a part of this.

If I calculate the average of the matrix it looks like I will lose information on frequency/infrequency of the transactions (every date would have some value).

Is there anyway to get an average representation to capture the frequency data as well as the values?

Answer 1

The first thing to keep in mind when addressing this sort of problem is that, when you are interested in two different properties of your data (in this case frequencies and amounts), it may require two quantities to accurately describe them. Saying that customer X spent \$6000 in the last year could mean both that customer X spent \$6000 one time or \$500 twelve times, and there is no way to distinguish these cases without reporting further information.

That said, a basic process for analyzing your data, given the information you provided, is as follows:

1. Create (very basic) summary statistics for each row of your data, specifically the frequency of transactions and the mean amount in each row.
2. Make a scatterplot of frequency versus amount for the set of summary statistics.
3. If there is no trend in the scatterplot, report the average of the frequencies and mean amounts as your "average" customer. If there is a trend, report a simple linear relation, describing how the mean amount of transactions increase (or decrease) as transaction frequency varies.