Spreadsheet Time Frame Filters
Within spreadsheet mode there are 3 different ways to view your data, Totals, Daily Averages, or Time Series, each with their own purpose that will help you get out of Excel and into Ambition Analytics!
How do I use the totals option?
How do I use the daily averages option?
How do I use the time series option?
Totals and Time Frames
Staying true to its name, totals will compile a total for the time period selected.
The data that is displayed in a totals spreadsheet and how it is aggregated over time will be dependent upon the metric type.
For count and sum type metrics, ex. Call Count or # of Closed Won Deals, Ambition will compile a sum value.
If you are viewing an average type metric, ex. Average Closed Won Revenue or Average Talk Time, the total displayed will be a daily average instead. Since the data was inherently computed as an average, it will not be transformed into a count or sum.
↳ What about combining two Average metrics together?
Through the formula builder integration, you are able to combine metrics through simple math operators (+,-,x,/). Due to this flexibility, you can create a sum type metric off of two average type metrics.
Example: Two average metrics, Average Inbound Call Time and Average Outbound Call Time, are added together to create a Total Average Call Time metric.
Average Inbound Call Time + Average Outbound Call Time = Total Average Call Time.
Total Average Call Time will be treated as a sum type metric since it's the sum of Average Inbound + Average Outbound Call Time.
The values for Average Inbound Call Time and Average Outbound Call Time will represent a record-based average calculation (sum of the respective call duration, inbound or outbound, divided by the total amount of records) across the selected time frame.
Staying true to the formula builder set up of Average + Average = Sum, the Total Average Call Time metric will act as a sum type metric and sum the calculated daily averages for each metric together.
Note: Activity and Objective Scores are considered average type metrics.
When you access the spreadsheet, the progress bars + values shown for Activity and Objective Score are average values if viewed on a time frame different from how they are calculated. Not a running Total.
Ex. A daily Activity Score viewed across a week time frame will be an average value, not each day summed together.
In order to understand how your data is being aggregated, you will need to know the time frame that you would like to analyze.
What are my Time Frame options?
Custom, Today, Yesterday, Last 7 Days, Last 30 Days, Last 90 Days, This Week, Last Week, This Month, Last Month, This Quarter, Last Quarter, This Year, Last Year.
The Include Weekends toggle, found in the filters pane, is On by default. When toggled On, Ambition will include data from the weekends.
If the toggle is off, Ambition will not include weekend data.
Totals Example
Ambition will aggregate totals based on the length and time frame you have chosen.
- Time Frame = 1 Week (last 7 days) ending Today.
- Metric = Calls, a count type metric.
- Include Weekends = On (excluding weekends will not impact the last 7 days count i.e. excluding weekends will not move the last 7 days 2 days forward.)
With the above setup, Ambition will display the totals number of Calls made for all users in the organization for a Week ago until Today.
Since today is Thursday, January 19th, 2023, a week ago, 7 days, would be Friday, January 13th, 2023.
The aggregation starts at 12:00:00 AM 1 week before today and continues until the minute that the report was pulled.
The data will be aggregated from Friday, January 13th, 2023 - today, Thursday, January 19th, 2023.
The values for the time range can be verified through the custom time frame selector as well.
Start Date = January 13th, 2023 and End Date = January 19th, 2023.
For a more granular breakdown of how your data is being aggregated over your unique time selections, see the table below:
Length | Time Frame | Ending | Range |
n | Day | y |
Starts at 12:00:00 AM n days before y through 11:59:00 PM on y Ending Time. |
n | Week | y |
Starts at 12:00:00 AM n weeks before y through 11:59:00 PM on y Ending Time, where a week is 7 calendar days. |
n | Month | y |
Starts at 12:00:00 AM n Months before y through 11:59:00 PM on y Ending Time. |
n | Quarter | y |
Starts at 12:00:00 AM n Quarters before y through 11:59:00 PM on y Ending Time. |
n | Year | y |
Starts at 12:00:00 AM n Years before y through 11:59:00 PM on y Ending Time. |
Note: UTC is used as the time standard when utilizing time frame selections. To view data in your respective time zone or across several time zones, we recommend using the "Custom" option.
Daily Averages and Time Frames
Daily averages is great if you are wanting to look at an average of a sum or count type metric over time.
The Time Frame options are the same as totals.
Daily Average Example
Revenue is typically a sum type metric because most want to see a collective sum of revenue.
Daily averages would come into play if you are wanting to see the average revenue the company and/or a user is bringing in over time.
- Time Frame = 1 Week (last 7 days) ending Today
- Metric = Revenue, a sum type metric.
- Include Weekends = On (excluding weekends will not impact the last 7 days count i.e. excluding weekends will not move the last 7 days 2 days forward.)
With the above setup, Ambition will display the average revenue for all users in the organization for a week ago (last 7 days) until today.
The aggregation starts at 12:00:00 AM 1 week before today and continues until the minute that the report was pulled.
Since today is Thursday, January 19th, 2023, a week ago would be Friday, January 13th, 2023.
The averages for the time range can be verified through the "Custom" time frame selector as well.
Start Date = January 13th, 2023 and End Date = January 19th, 2023.
To further understand how your data is being aggregated over your unique time selections, reference the table above.
If there is no revenue for a user on a day(s) within the time frame, a zero(s) will be factored into the average.
Daily Average Example
Suzy had contributed revenue on the following dates:
User | Date | Revenue |
Suzy Smith | 1/10/2023 | $40,000 |
Suzy Smith | 1/11/2023 | - |
Suzy Smith | 1/12/2023 | - |
Suzy Smith | 1/13/2023 | $50,000 |
Suzy Smith | 1/14/2023 | $90,000 |
Suzy Smith | 1/15/2023 | - |
Suzy Smith | 1/16/2023 | $30,000 |
Suzy's manager wants to see the average revenue Suzy has contributed over the last week, ending on 1/16/2023 at 4:30 PM.
($40,000 + $50,000 + $90,000 + $30,000) / 7 = $30,000
Since we received nulls values for the other 3 days, they will be included as zeros, as shown above.
Time Series and Time Frames
Time Series is a great way to see your employee's data aggregated up day by day, week by week, etc.. From wanting to see your team's daily Activity Score to pulling total revenue by month, the time series option will cover all of your data desires!
What are my Time Frame options?
The Time Frame options are the same as totals.
You are limited to selecting 1 metric per time series report.
Time Series Example
Ambition will aggregate totals based on the length and time frame you have chosen.
- Time Frame = 4 Weeks ending Today.
- Metric = Connects, a count type metric.
With the above setup, Ambition will display the weekly total of connects for all users in the organization for 4 Weeks ago until Today.
Note: A "Week" in Ambition is Monday - Sunday.
Since this time series report was generated on a Thursday, you'll notice the current week, 1/16/2023 has lower values.
To further understand how your data is being aggregated over your unique time selections, reference the table above.
Time Series Helpful Hints!
Activity/Objective Scores: You can only pull a time series for the Activity/Objective Score time frames and longer. For example, if you are utilizing a weekly Activity Score, you will not be able to pull a daily time series.
If you export a Time Series, Ambition will add a 0 if no data exists. If you are wanting to take the average over time to compare with daily averages, be sure to exclude the 0s unless you are setup to send Ambition 0s in your data.
Keep in mind when viewing Activity and Objective Scores for groups as a whole vs users within a group that the total numbers may be different. Since Ambition creates a new score for rolled-up groups, we display the new score when viewing data at a group level, not the user hierarchy level.
Comments
0 comments
Please sign in to leave a comment.