Skip to content

Instantly share code, notes, and snippets.

@jbrahy
Created March 19, 2024 17:50
Show Gist options
  • Save jbrahy/7f1cb3a1c07ffbe619566a8f8114632e to your computer and use it in GitHub Desktop.
Save jbrahy/7f1cb3a1c07ffbe619566a8f8114632e to your computer and use it in GitHub Desktop.
function get_message_summary_by_range($start_date, $end_date)
{
// Convert the date to start and end for MongoDB ISODate format
$start_date = new UTCDateTime(new \DateTime($start_date . ' 00:00:00', new DateTimeZone('UTC')));
$end_date = new UTCDateTime(new \DateTime($end_date . ' 23:59:59', new DateTimeZone('UTC')));
// get the date range correct
$match_stage = [
'$match' => [
'date_sent' => [
'$gte' => $start_date,
'$lt' => $end_date,
],
],
];
// Define the aggregation pipeline
$pipeline = [
$match_stage,
[
'$addFields' => [
'formatted_date' => [
'$dateToString' => ['format' => '%Y-%m-%d', 'date' => '$date_sent'],
],
],
],
[
'$lookup' => [
'from' => 'clickhistory',
'localField' => 'tracking',
'foreignField' => 'click_id',
'as' => 'click_info',
],
],
[
'$unwind' => [
'path' => '$click_info',
'preserveNullAndEmptyArrays' => TRUE,
],
],
[
'$lookup' => [
'from' => 'conversionhistory',
'localField' => 'tracking',
'foreignField' => 'click_id',
'as' => 'conversion_info',
],
],
[
'$unwind' => [
'path' => '$conversion_info',
'preserveNullAndEmptyArrays' => TRUE,
],
],
[
'$addFields' => [
'has_click' => [
'$cond' => [['$gt' => ['$click_info', NULL]], 1, 0], // Add field to indicate if there was a click
],
],
],
[
'$group' => [
'_id' => [
'drop_date' => '$formatted_date',
'drop_id' => '$drop_id',
'sender_id' => '$sender_id',
'list_id' => '$list_id',
'vendor' => '$vendor',
'campaign_id' => '$campaign_id',
'carrier_group' => '$carrier_group',
'carrier' => '$carrier',
'template_id' => '$template_id',
'status' => '$status',
],
'total_messages' => ['$sum' => 1],
'total_payout' => ['$sum' => ['$toDouble' => '$conversion_info.payout']],
'total_clicks' => ['$sum' => ['$cond' => [['$gt' => ['$click_info.click_id', NULL]], 1, 0]]],
'unique_clicks' => ['$sum' => '$has_click'],
],
],
[
'$project' => [
'_id' => 0, // Exclude the _id field
'drop_date' => '$_id.drop_date',
'drop_id' => '$_id.drop_id',
'sender_id' => '$_id.sender_id',
'list_id' => '$_id.list_id',
'vendor' => '$_id.vendor',
'campaign_id' => '$_id.campaign_id',
'carrier_group' => '$_id.carrier_group',
'carrier' => '$_id.carrier',
'template_id' => '$_id.template_id',
'status' => '$_id.status',
'total_messages' => 1,
'total_payout' => 1,
'total_clicks' => 1,
'unique_clicks' => 1,
],
],
[
'$sort' => ['drop_date' => 1],
],
];
try {
// Execute the aggregation
$result = $this->collection->aggregate($pipeline);
} catch (\MongoDB\Driver\Exception\Exception $exception) {
exit('Exception: ' . $exception->getMessage());
}
// Collect and return the results
$summary = [];
foreach ($result as $doc) {
$summary[] = $doc->getArrayCopy();
}
return $summary;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment