Forum Discussion
Building queries - Monthly or weekly?
Hi all,
With regards to building a Standard Person Query over an extended period, eg. 24 months - can anyone provide some advice with regards to grouping metrics weekly or monthly? I'm asking simply to reduce the size of the dataset, but would like to understand some advantages/disadvantages or if you could share your own experiences, that would also be great.
Hi Dazza,
This is a great question and one that does not have a one-size-fits-all answer. Here are some thoughts that might help make the right choice. When you run a person query, you can chose to aggregate metrics on a daily, weekly, and monthly basis. If you select daily, the output of the query will have one row per person per day, if you select weekly, there will be one row per person per week, and if you select monthly, there will be one row per person per month. If your goal is to reduce the size of the dataset, you should go with monthly.
Your "group by" selection will also depend on the goal of your analysis. Does a monthly aggregation provide you with the right granularity to accomplish your goals? You might also want to consider interpretability of the insights you'll generate with your analysis. Based on my experience, most people find it hard to relate to metrics aggregated on a monthly level, and find it easier to understand weekly metrics. How would you react if I told you that you spend 78 hours a month in meetings vs if I told you you spend an average of 18 hours per week?
I hope this helps!
4 Replies
luhognerMicrosoft
In that case, I would expect the output to be directionally the same, so I'm not surprised that you haven't observed a big difference in your tests. A few things you might want to consider:
- Some months have 4 weeks and some months have 5 weeks. Always dividing the monthly data by 4 might generate a slight error in your numbers.
- Dividing monthly data by 4 assumes that people were active during the entire month, and that might not always be true. For example, let's assume that I'm active during only 1 week in a given month. Let's also assume that I spent 16 hours in collaboration during the week when I was active. Dividing my monthly collaboration hours by 4 means that I will have an average of 4 hours per week in collaboration. This is not entirely true, because I was inactive the remaining 3 weeks, and my true average weekly collaboration hours is 16 hours/week.
Thank you so much luhogner! Will take this into consideration while trying to manage the size of the dataset.
Thanks so much luhogner! Definitely understand the concept of interpretability weekly vs. monthly. I guess my question should have been - if I take the result provided from the monthly query, and divide by 4 - will the output be drastically different from using the weekly query? In my tests - it's been minimal to none...
- luhogner
Hi Dazza,
This is a great question and one that does not have a one-size-fits-all answer. Here are some thoughts that might help make the right choice. When you run a person query, you can chose to aggregate metrics on a daily, weekly, and monthly basis. If you select daily, the output of the query will have one row per person per day, if you select weekly, there will be one row per person per week, and if you select monthly, there will be one row per person per month. If your goal is to reduce the size of the dataset, you should go with monthly.
Your "group by" selection will also depend on the goal of your analysis. Does a monthly aggregation provide you with the right granularity to accomplish your goals? You might also want to consider interpretability of the insights you'll generate with your analysis. Based on my experience, most people find it hard to relate to metrics aggregated on a monthly level, and find it easier to understand weekly metrics. How would you react if I told you that you spend 78 hours a month in meetings vs if I told you you spend an average of 18 hours per week?
I hope this helps!
