Approach Question - VBA? Formulas?

[ekyles]

I am trying to glean the impact a monthly marketing campaign has on customer behavior and sales. I have two data sets:

1. Customer Purchases

Column A: Customer ID

Column B: Data (MMM/YYYY)

Column C: Purchase Flag (Y/N)

Column D: Revenue ($)


2. Customer Calls

Column A: Customer ID

Column B: Data (MMM/YYYY)

Column C: Call Flag (Y/N)


I need to understand the correlation between purchase behavior and call: do customers that received a call in month n or n-1 purchase more frequently or spend more than other customers. I also would like the flexibility to do sensitivity analyses around n-x month (what's the point where calls stop affecting customer behavior, for example do customers who received a call in month n still buy at higher rates in month n+x)


I would be grateful for any help getting me started. I thought about an array but not sure my VBA is good enough. I did a duck-tape solution, but it is not really scalable or flexible (I concatenated Customer ID and data to create unique records and did a vlookup, but it is slow and cludgy). Would be very grateful for more elegant or efficient solutions. Thank you in advance.


Cheers!

 

[Montrey]

Regression Analysis seems the most appropriate here.

http://www.youtube.com/watch?v=720_5tv_JDs

 

[ekyles]

Thanks Montrey. I apolgize, but I mischaracterized the data ... copied and pasted. In the marketing data, there is no record for customers who did not receive a call only for those who do. So each customer has a different number of (records of) marketing calls but a record for every month regardless of whether they purchased anything. I think that may prevent a regression without manipulating the data first, eh?


Any advice?


cheers!

 

[Hui]

Ekyles


You may want to try regression analysis on the filtered and unfiltered data

It is often this filtering that leads to trends popping out of the data

Filtering by sales type, sales man, time, day, month may also be useful