oh sorry, here. I would like to create variables for “Entry Date” and “Source” fields
[
{
$match:
/**
* query: The query in MQL.
*/
{
Source: "Clientx",
"Entry Date": {
$gte: ISODate(
"2024-06-01T04:00:00.000+00:00"
),
$lte: ISODate(
"2024-07-01T04:00:00.000+00:00"
),
},
},
},
{
$facet:
/**
* outputFieldN: The first output field.
* stageN: The first aggregation stage.
*/
{
TotalClicks: [
{
$group: {
_id: "$Source",
TotalClicks: {
$sum: 1,
},
TotalCost: {
$sum: "$Cost",
},
},
},
],
UniqueIps: [
{
$group: {
_id: "$IP Address",
},
},
{
$count: "UniqueIps",
},
],
},
},
{
$unwind:
/**
* path: Path to the array field.
* includeArrayIndex: Optional name for index.
* preserveNullAndEmptyArrays: Optional
* toggle to unwind null and empty values.
*/
{
path: "$TotalClicks",
},
},
{
$unwind:
/**
* path: Path to the array field.
* includeArrayIndex: Optional name for index.
* preserveNullAndEmptyArrays: Optional
* toggle to unwind null and empty values.
*/
{
path: "$UniqueIps",
},
},
{
$project:
/**
* specifications: The fields to
* include or exclude.
*/
{
Source: "$TotalClicks._id",
Total_Cost: "$TotalClicks.TotalCost",
Total_Clicks: "$TotalClicks.TotalClicks",
Unique_Clicks: "$UniqueIps.UniqueIps",
Duplicate_Rate: {
$concat: [
{
$toString: {
$multiply: [
{
$round: [
{
$divide: [
{
$subtract: [
"$TotalClicks.TotalClicks",
"$UniqueIps.UniqueIps",
],
},
"$TotalClicks.TotalClicks",
],
},
4,
],
},
100,
],
},
},
"%",
],
},
},
},
]