0

I have a data structure like so:

[
    {"Day": "12-06-2021", "ROAS": 17.3, "ConversionRate": 0.1472, "VisitRate": 0.0488},
    {"Day": "12-07-2021", "ROAS": 14.79, "ConversionRate": 0.1251, "VisitRate": 0.0555},
    {"Day": "12-08-2021", "ROAS": 17.12, "ConversionRate": 0.1358, "VisitRate": 0.052},
    {"Day": "12-09-2021", "ROAS": 14.95, "ConversionRate": 0.1391, "VisitRate": 0.0537},
    {"Day": "12-10-2021", "ROAS": 19.85, "ConversionRate": 0.1394, "VisitRate": 0.0853},
    {"Day": "12-11-2021", "ROAS": 25.03, "ConversionRate": 0.1565, "VisitRate": 0.1033},
    {"Day": "12-12-2021", "ROAS": 24.58, "ConversionRate": 0.1528, "VisitRate": 0.1172},
    {"Day": "12-13-2021", "ROAS": 14.2, "ConversionRate": 0.1517, "VisitRate": 0.0481},
    {"Day": "12-14-2021", "ROAS": 14.32, "ConversionRate": 0.1564, "VisitRate": 0.0488},
    {"Day": "12-15-2021", "ROAS": 12.54, "ConversionRate": 0.1436, "VisitRate": 0.0473},
    {"Day": "12-16-2021", "ROAS": 14.47, "ConversionRate": 0.1574, "VisitRate": 0.0442},
    {"Day": "12-17-2021", "ROAS": 18.2, "ConversionRate": 0.1283, "VisitRate": 0.0729},
    {"Day": "12-18-2021", "ROAS": 72.93, "ConversionRate": 0.1366, "VisitRate": 0.2267},
    {"Day": "12-19-2021", "ROAS": 72.29, "ConversionRate": 0.1638, "VisitRate": 0.1715}
]

What I'm trying to do seems impossible for me as I struggle with date math a lot, so I'm hoping one of you geniuses can help me out.

What I am trying to do is aggregate this daily data into an object that gives me week-over-week averages for each of the three attributes. What I was coming up with was a mess of nested foreaches that I couldn't keep straight in my head.

I'm more than happy to use any npm modules as well. Anything to make this easier

S16
  • 2,963
  • 9
  • 40
  • 64
  • Which part are you having trouble with? Knowing what math to do, or just getting the data in front of you, to begin with (ie fetching the objects from the database, etc)? – Maiya Jan 05 '22 at 05:26
  • Use the day information to [`getWeekOfYear`](https://stackoverflow.com/a/39502645/4543207) and group your objects by that week number accordingly. Then calculate averages in each group. – Redu Jan 05 '22 at 05:36
  • I have the object and am looping through it, using moment.js to get the week and trying to create an object where I am grouping by week number, but that's where I'm getting lost. – S16 Jan 05 '22 at 05:36
  • @Redu That is what I am trying to do right now, but the syntax is really confusing me. – S16 Jan 05 '22 at 05:37

3 Answers3

2

So here is a stolen code from a previous answer to obtain week number from a date.

function getWeek(date) {
  if (!(date instanceof Date)) date = new Date();

  // ISO week date weeks start on Monday, so correct the day number
  var nDay = (date.getDay() + 6) % 7;

  // ISO 8601 states that week 1 is the week with the first Thursday of that year
  // Set the target date to the Thursday in the target week
  date.setDate(date.getDate() - nDay + 3);

  // Store the millisecond value of the target date
  var n1stThursday = date.valueOf();

  // Set the target to the first Thursday of the year
  // First, set the target to January 1st
  date.setMonth(0, 1);

  // Not a Thursday? Correct the date to the next Thursday
  if (date.getDay() !== 4) {
    date.setMonth(0, 1 + ((4 - date.getDay()) + 7) % 7);
  }

  // The week number is the number of weeks between the first Thursday of the year
  // and the Thursday in the target week (604800000 = 7 * 24 * 3600 * 1000)
  return 1 + Math.ceil((n1stThursday - date) / 604800000);
}

Whic gives you the week number of a date. Now all you need is to group your array into weeks accoding to the week number.

var data = [
    {"Day": "12-06-2021", "ROAS": 17.3, "ConversionRate": 0.1472, "VisitRate": 0.0488},
    {"Day": "12-07-2021", "ROAS": 14.79, "ConversionRate": 0.1251, "VisitRate": 0.0555},
    {"Day": "12-08-2021", "ROAS": 17.12, "ConversionRate": 0.1358, "VisitRate": 0.052},
    {"Day": "12-09-2021", "ROAS": 14.95, "ConversionRate": 0.1391, "VisitRate": 0.0537},
    {"Day": "12-10-2021", "ROAS": 19.85, "ConversionRate": 0.1394, "VisitRate": 0.0853},
    {"Day": "12-11-2021", "ROAS": 25.03, "ConversionRate": 0.1565, "VisitRate": 0.1033},
    {"Day": "12-12-2021", "ROAS": 24.58, "ConversionRate": 0.1528, "VisitRate": 0.1172},
    {"Day": "12-13-2021", "ROAS": 14.2, "ConversionRate": 0.1517, "VisitRate": 0.0481},
    {"Day": "12-14-2021", "ROAS": 14.32, "ConversionRate": 0.1564, "VisitRate": 0.0488},
    {"Day": "12-15-2021", "ROAS": 12.54, "ConversionRate": 0.1436, "VisitRate": 0.0473},
    {"Day": "12-16-2021", "ROAS": 14.47, "ConversionRate": 0.1574, "VisitRate": 0.0442},
    {"Day": "12-17-2021", "ROAS": 18.2, "ConversionRate": 0.1283, "VisitRate": 0.0729},
    {"Day": "12-18-2021", "ROAS": 72.93, "ConversionRate": 0.1366, "VisitRate": 0.2267},
    {"Day": "12-19-2021", "ROAS": 72.29, "ConversionRate": 0.1638, "VisitRate": 0.1715}
];

function getWeek(date) {
  !(date instanceof Date) && (date = new Date());
  var nDay = (date.getDay() + 6) % 7;
  date.setDate(date.getDate() - nDay + 3);
  var n1stThursday = date.valueOf();
  date.setMonth(0, 1);
  (date.getDay() !== 4) && date.setMonth(0, 1 + ((4 - date.getDay()) + 7) % 7);
  return 1 + Math.ceil((n1stThursday - date) / 604800000);
}


var groups = data.reduce( function(r,d){
                            var week = getWeek(new Date(d.Day));
                            r[week] = r[week] ? r[week].concat(d)
                                              : [d]
                            return r;
                          }, {}),
    result = Object.keys(groups)
                   .map(week => groups[week] = groups[week].reduce((a,o) => ( a.ROAS           += o.ROAS / groups[week].length
                                                                            , a.ConversionRate += o.ConversionRate / groups[week].length
                                                                            , a.VisitRate      += o.VisitRate / groups[week].length
                                                                            , a
                                                                            ) ,{"Week":week, "ROAS":0, "ConversionRate": 0, "VisitRate":0}));
console.log(result);
Redu
  • 25,060
  • 6
  • 56
  • 76
  • This is fantastic. I made it much smaller by using moment.js as well. Thank you! – S16 Jan 05 '22 at 06:27
  • I'm running into issues with this code because of the way that weeks are calculated. You can end up with with the last day of one year being week 01 of the next year for instance, and that messes everything up. A better solution would be to be able to provide the start and end days that I'd like to key on (say Sun-Sat or Mon-Sun) and the resulting "Week" attribute would be a string like "12/26-2021 - 1/1/2022". I'm working on this now, but I'm getting pretty confused. – S16 Jan 06 '22 at 19:06
  • @S16 I just tried `getWeek(new Date("12-31-2021"))` and it gives `52`. Could you please state the date you tried..? – Redu Jan 06 '22 at 19:50
1

You can make use of reduce to group each data item into weeks, and map through the data again to calculate averages.

Here's my attempt, hopefully it's something similar to what you had expected:

function getWeek(date) {
  var onejan = new Date(date.getFullYear(), 0, 1);
  var millisecsInDay = 86400000;
  return Math.ceil(
    ((date - onejan) / millisecsInDay + onejan.getDay() + 1) / 7
  );
}

const data = [
  { Day: "12-06-2021", ROAS: 17.3, ConversionRate: 0.1472, VisitRate: 0.0488 },
  { Day: "12-07-2021", ROAS: 14.79, ConversionRate: 0.1251, VisitRate: 0.0555 },
  { Day: "12-08-2021", ROAS: 17.12, ConversionRate: 0.1358, VisitRate: 0.052 },
  { Day: "12-09-2021", ROAS: 14.95, ConversionRate: 0.1391, VisitRate: 0.0537 },
  { Day: "12-10-2021", ROAS: 19.85, ConversionRate: 0.1394, VisitRate: 0.0853 },
  { Day: "12-11-2021", ROAS: 25.03, ConversionRate: 0.1565, VisitRate: 0.1033 },
  { Day: "12-12-2021", ROAS: 24.58, ConversionRate: 0.1528, VisitRate: 0.1172 },
  { Day: "12-13-2021", ROAS: 14.2, ConversionRate: 0.1517, VisitRate: 0.0481 },
  { Day: "12-14-2021", ROAS: 14.32, ConversionRate: 0.1564, VisitRate: 0.0488 },
  { Day: "12-15-2021", ROAS: 12.54, ConversionRate: 0.1436, VisitRate: 0.0473 },
  { Day: "12-16-2021", ROAS: 14.47, ConversionRate: 0.1574, VisitRate: 0.0442 },
  { Day: "12-17-2021", ROAS: 18.2, ConversionRate: 0.1283, VisitRate: 0.0729 },
  { Day: "12-18-2021", ROAS: 72.93, ConversionRate: 0.1366, VisitRate: 0.2267 },
  { Day: "12-19-2021", ROAS: 72.29, ConversionRate: 0.1638, VisitRate: 0.1715 },
];

const aggregatedData = data
  .reduce((acc, val) => {
    const date = new Date(val.Day);
    const weekNum = getWeek(date);
    const weekYear = `${weekNum}-${date.getFullYear()}`;
    const lastWeekYear = acc[acc.length - 1]?.weekYear;
    if (lastWeekYear === weekYear) {
      acc[acc.length - 1].data.push(val);
      return acc;
    } else {
      return [...acc, { weekYear, data: [val] }];
    }
  }, [])
  .map(({ weekYear, data }) => {
    const [roasCount, roasSum] = data.reduce(
      (acc, val) => [acc[0] + 1, acc[0] + val.ROAS],
      [0, 0]
    );
    const roasAverage = roasSum / roasCount;
    const [conversionRateCount, conversionRateSum] = data.reduce(
      (acc, val) => [acc[0] + 1, acc[0] + val.ConversionRate],
      [0, 0]
    );
    const conversionRateAverage = conversionRateSum / conversionRateCount;
    const [visitRateCount, visitRateSum] = data.reduce(
      (acc, val) => [acc[0] + 1, acc[0] + val.VisitRate],
      [0, 0]
    );
    const visitRateAverage = visitRateSum / visitRateCount;
    return {
      weekYear,
      averages: { roasAverage, conversionRateAverage, visitRateAverage },
    };
  });

console.log(aggregatedData);

If you are already using a library such as lodash, this could definitely be cleaned up to be a smaller amount of code.

Ari Seyhun
  • 11,506
  • 16
  • 62
  • 109
  • I am using lodash and moment.js. I can run this snipped here and it works fine, but when I try to run it in Node I get an error at `const lastWeekYear = acc[acc.length - 1]?.weekYear;` with a pointer at the dot between ? and weekYear. – S16 Jan 05 '22 at 06:12
  • Try to replace it with: `const lastWeekYear = acc[acc.length - 1] ? acc[acc.length - 1].weekYear : undefined;` – Ari Seyhun Jan 05 '22 at 06:13
  • You are my hero, that worked. Thank you so much. I would love to learn from this more if you're willing to help though. I would love to see what this looks like using lodash and moment as I am trying to get better with those libraries. – S16 Jan 05 '22 at 06:18
  • I don't have lodash & momentjs functions fresh in my head so I'm not sure from the top of my head what would be a good replacement. But I'm sure momentjs has a replacement for my `getWeek` function. – Ari Seyhun Jan 05 '22 at 06:20
0

Here's my attempt with dayjs (can be easily changed to moment) https://jsfiddle.net/wajsc548/7/

So what I do here is loop the data, convert the date to week and use it as a key to store the total of the values + a counter to determine how many values has been added

Then I loop it again to get the average.

let data =[
    {"Day": "12-06-2021", "ROAS": 17.3, "ConversionRate": 0.1472, "VisitRate": 0.0488},
    {"Day": "12-07-2021", "ROAS": 14.79, "ConversionRate": 0.1251, "VisitRate": 0.0555},
    {"Day": "12-08-2021", "ROAS": 17.12, "ConversionRate": 0.1358, "VisitRate": 0.052},
    {"Day": "12-09-2021", "ROAS": 14.95, "ConversionRate": 0.1391, "VisitRate": 0.0537},
    {"Day": "12-10-2021", "ROAS": 19.85, "ConversionRate": 0.1394, "VisitRate": 0.0853},
    {"Day": "12-11-2021", "ROAS": 25.03, "ConversionRate": 0.1565, "VisitRate": 0.1033},
    {"Day": "12-12-2021", "ROAS": 24.58, "ConversionRate": 0.1528, "VisitRate": 0.1172},
    {"Day": "12-13-2021", "ROAS": 14.2, "ConversionRate": 0.1517, "VisitRate": 0.0481},
    {"Day": "12-14-2021", "ROAS": 14.32, "ConversionRate": 0.1564, "VisitRate": 0.0488},
    {"Day": "12-15-2021", "ROAS": 12.54, "ConversionRate": 0.1436, "VisitRate": 0.0473},
    {"Day": "12-16-2021", "ROAS": 14.47, "ConversionRate": 0.1574, "VisitRate": 0.0442},
    {"Day": "12-17-2021", "ROAS": 18.2, "ConversionRate": 0.1283, "VisitRate": 0.0729},
    {"Day": "12-18-2021", "ROAS": 72.93, "ConversionRate": 0.1366, "VisitRate": 0.2267},
    {"Day": "12-19-2021", "ROAS": 72.29, "ConversionRate": 0.1638, "VisitRate": 0.1715}
];

let average = {};


data.forEach(day => {
  let week = dayjs(day.Day, "MM-DD-YYYY").isoWeek();
  let averageWeek = average[[week]];
  if (averageWeek) {
    average = {...average, [week]: {"ROAS": day.ROAS + averageWeek.ROAS, "ConversionRate": day.ConversionRate + averageWeek.ConversionRate, "VisitRate": day.VisitRate + averageWeek.VisitRate, count: averageWeek.count + 1}};
  }
  else {
    average = {...average, [week]: {"ROAS": day.ROAS, "ConversionRate": day.ConversionRate, "VisitRate": day.VisitRate, count: 1}};
  }
  
});

let newData = [];
Object.entries(average).forEach(
    ([key, value]) => {
        newData.push({"Week": key, "ROAS": value.ROAS/value.count, "ConversionRate": value.ConversionRate/value.count, "VisitRate": value.VisitRate/value.count})
    }
);

console.log(newData);
crimson589
  • 1,238
  • 1
  • 20
  • 36