subscribers-reach

Config

All queires are run on the directory database.

sum distinct count

db.subscribers_reach_data.aggregate([{group: {_id: 'subscriber_id', count: { sum: 1 } } }]); db.subscribers_reach_data.updateMany({‘clicks_idle_days’: null}, {‘$set’: {‘deliveries_idle_days’: -1}})

sort by count (also finds duplicates)

db.subscribers_reach_data.aggregate([ { $sortByCount{:}^{\prime }$deliveries_idle_days’ }, ])

db.subscribers_reach_data.aggregate([ { match: { clicks_idle_days: {gte: 7} } }, { $sortByCount{:}^{\prime }$clicks_idle_days’ }, ])

find reach data by ranges

db.subscribers_reach_data.find({‘deliveries_idle_days’: {gte: 60}}).count() db.subscribers_reach_data.find({'clicks_idle_days': {gte: 60}}).count()

find unique (subscriber_id) records count

db.subscribers_reach_data.aggregate([{group: {_id: 'subscriber_id', count: { sum: 1 } } }]);

find subscribers with clicks_idle_days=-1

db.subscribers_reach_data.find({'clicks_idle_days': -1}).count()

export mongo query result to csv (with password promt)

mongoexport --db directory -uroot --authenticationDatabase admin --collection subscribers_reach_data --type=csv --fields subscriber_id --out /tmp/subscriber_ids.csv