CiviCRM Community Forums (archive)

Hi xavier,

I asked Dr. Shailesh Kumar about co-mentoring this project, but unfortunately he had his own commitments. Right now, I'm not able to find any other person who would be willing to co-mentor this project.

Please let me know if you find a name, so that I can discuss the project and get familiar with him.

Thanks
Mohit

Hi Xavier,
I have successfully installed CiviMail and configured it. I'm able to send mails and have understood the basic structure.

It would be great if you could provide the dump now so that I can start working on the next steps.

Thanks
Mohit

Yes I did find them.

The civicrm_mailing_event_opened table stores the opens.
The civicrm_mailing_event_trackable_url_open table stores the clicks.

Also one more thing, I found that whenever a recipient opens a mailing multiple times, each open  gets stored in the civicrm_mailing_event_opened table separately,  but it is not reflected in the Mailing Reports on the web interface. It shows only the number of unique opens in the 'Tracked opens' field. Is there any field in the Report that indicates this? If not, I think it would be great if we also add a field (in the Mailings Report) that indicates the total opens for a particular mailing.

Hi Emily,

Sorry for the late reply.

I'm write now working on a dump of mailings that contains tables having information about the mail opening rates, click rates and so on. I'm trying to find out patterns so as to devise a function that could make some basic predictions related to mailings.

I've got familiar with the table structure and am using python to do data analytics. I'm right now focused on finding the min, max, mean and median time for opens/clicks for different mailings.

The project is quite interesting and challenging, and I'm getting to learn a lot by working on this project.

Thanks
Mohit

I realized that I made some wrong assumptions about the mailings.  After making the correction, I have come up with revised analytics data.  Attached is the file.

This involved working on civicrm_mailing_event_delivered, civicrm_mailing_event_opened, civicrm_mailing_event_queue and civicrm_mailing_job tables in order to find the time between sending and opening of mails, and compute min, max and average for each mailing.

I soon aim to do the same for clicks and then see how I can apply the linear regression techniques.

Here is the link to the github repository for this project:

https://github.com/mohit-agarwal/Data-Mine-GSoC-Project

I tried taking all the 3 factors(sent_time, week_day, email_id/mailing_id) into account and build a linear regression model to predict the open_time(here I am taking open_time as time since 00:00 hrs).

I got he following results:

Model 1
Independent variables : sent_time(in minutes), email_id, day
Dependent variables : open_time(in minutes)

Coefficients:
(Intercept)    sent_time     email_id          day
  888.98032      0.05877     -0.02989      0.71612

Residuals:
    Min      1Q  Median      3Q     Max
-954.04 -177.38   29.97  235.56  517.03

Positive Residual Mean = 230.8751 (~ 4 hrs)
Negative Residual Mean = -271.0273 (~ 4.5 hrs)

Model 2
Independent variables : sent_time(in minutes), mailing_id, day
Dependent variables : open_time(in minutes)

Coefficients:
(Intercept)    sent_time   mailing_id          day
  859.95509      0.08822      4.27276    -14.70451

Residuals:
    Min      1Q  Median      3Q     Max
-975.44 -187.02   31.98  225.68  516.58

Positive Residual Mean = 233.8716 (~ 4 hrs)
Negative Residual Mean = -265.854 (~ 4.4 hrs)

Observations
Total tuples :  2153

Min sent_time : 654
Max sent_time : 1150
Avg sent_time : 1004

Given that sent_time(in train data) varies from 654 to 1004, I get the predicted open_times also in a certain small range(they mainly revolve around the intercept). Due this this, I get extreme min and max residual values.

The mean positive and negative residual means are for the training data. The predicted open_time values show an error of +- 4hrs. I'm not sure how good/bad is the current error rate. Also it is for the training data (one which I used to build the model). I expect greater error rate when I test it on new data. How close do we want the prediction to the real data?

I did some analysis on the data and found that it contains only 25 unique sent_times (implying most of the mails are sent at same time, here I'm ignoring the date) due to which this factor is not able to significantly affect the predicted open_time, and hence I get nearly the same open_time for most of the test cases. Also it contains only 4 unique days and 15 unique mailing_ids. I think having a larger dump will improve the results.

I've written a Php script which you can host on your local server, and use it to build your prediction model and test it on the new data. It asks the user to decide on the independent and dependent variables and accordingly uses R libraries to build the prediction model. I aim to add more functionalities in this module (like give user an option to decide on what data to learn, etc). I've not put much emphasis on the UI right now. Please let me know your views.

I've pushed the code in the github repo. Necessary instructions are mentioned in the Readme file. Here is the link -

https://github.com/mohit-agarwal/Data-Mine-GSoC-Project