couchdb-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gijs Nelissen <g...@prezly.com>
Subject Email statistiscs : using reduce for uniques
Date Fri, 10 Oct 2014 14:37:21 GMT
Hi,

I have a couchDB view with about 20 million very simple events:

key: [1,1,1,1,'deliver'] { email: "john@example.net", ip: "..."}
key: [1,1,1,1,'open'] { email: "john@example.net", ip: "..."}
key: [1,1,1,1,'click'] { email: "john@example.net", ip: "..."}
key: [1,1,1,2,'deliver'] { email: "john@example.net", ip: "..."}
key: [1,1,1,2,'open'] { email: "john@example.net", ip: "..."}
key: [1,1,1,2,'open'] { email: "john@example.net", ip: "..."} <- second
open by user
key: [1,1,1,2,'open'] { email: "john@example.net", ip: "..."} <- third open
by user

Now i want to do very mailchimp/campaignmonitor like summary per campaign
(key[3}) that show nr of unique delivers, nr of unique opens, nr of unique
clicks.

I have been trying different approaches to achieve this by using a custom
map and reduce function.

//map
function(doc) {
   emit([doc.license.id,10, doc.release.id, doc.email.id, doc.contact.id,
doc.type], null);
}

//reduce
function(keys, values, rereduce){
    if (rereduce){
        var result_rereduce = {
            contacts: values[0].contacts,
            open: values[0].open,
            click: values[0].click,
            bounce: values[0].bounce,
            unsubscribe: values[0].unsubscribe,
            unopened: values[0].unopened
        };

        for(var i=1,e=values.length; i<e; ++i) {
            result_rereduce.contacts = result_rereduce.contacts +
values[i].contacts;
            result_rereduce.open = result_rereduce.open + values[i].open;
            result_rereduce.click = result_rereduce.click + values[i].click;
            result_rereduce.bounce = result_rereduce.bounce +
values[i].bounce;
            result_rereduce.unsubscribe = result_rereduce.unsubscribe +
values[i].unsubscribe;
            result_rereduce.unopened = result_rereduce.unopened +
values[i].unopened;
        }

        return result_rereduce;
    }

    var unique_contacts = {};
    var unique_opens = {};
    var unique_clicks = {};
    var unique_bounce = {};
    var unique_unsubscribe = {};
    var unique_openorclick = {};

    for(var x=0,y=keys.length; x<y; ++x) {
        log(keys[x][0][4]);

        if (keys[x][0][4] == "email_delivered") {
            if(!unique_contacts[keys[x][0][3]]) {
                unique_contacts[keys[x][0][3]] = true;
            }
        }

        if (keys[x][0][4] == "email_open") {
            if(!unique_opens[keys[x][0][3]]) {
                unique_opens[keys[x][0][3]] = true;

                log('inserting open id ' + keys[x][0][3]);
            } else {
                log('not inserting open id ' + keys[x][0][3]);
            }

            log(unique_opens);
            log("open counter" + Object.keys(unique_opens).length);

            //used for unopened
            if(!unique_openorclick[keys[x][0][3]]) {
                unique_openorclick[keys[x][0][3]] = true;
            }
        }

        if (keys[x][0][4] == "email_click") {
            if(!unique_clicks[keys[x][0][3]]) {
                unique_clicks[keys[x][0][3]] = true;
            }

            //used for unopened
            if(!unique_openorclick[keys[x][0][3]]) {
                unique_openorclick[keys[x][0][3]] = true;
            }
        }

        if (keys[x][0][4] == "email_bounce") {
            if(!unique_bounce[keys[x][0][3]]) {
                unique_bounce[keys[x][0][3]] = true;
            }
        }

        if (keys[x][0][4] == "email_unsubscribe") {
            if(!unique_unsubscribe[keys[x][0][3]]) {
                unique_unsubscribe[keys[x][0][3]] = true;
            }
        }
    }


    var result = {
        contacts: Object.keys(unique_contacts).length,
        open: Object.keys(unique_opens).length,
        click: Object.keys(unique_clicks).length,
        bounce: Object.keys(unique_bounce).length,
        unsubscribe: Object.keys(unique_unsubscribe).length,
        unopened: Object.keys(unique_contacts).length -
Object.keys(unique_openorclick).length
    };


    return result;

}


Now this works fine right until the rereduce part. The uniqueness detection
only works where reduce=false.

Any tips on how to achieve this ?

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message