2

Creating a Year Over Year Performance Summary Dashboard

Looking at year over year performance can be really helpful in determining how successful your account is performing long term. Executing this type of analysis, can assist you in determining what metrics experienced growth/declines due to your direct efforts. The following article will help you create an easy to design year over year historical performance summary dashboard.

Time: 20 minutes

Skill: Beginner

Ingredients:

This dashboard will help you generate 4 queries that will give the following information:

  • A 4 week summary of performance for the year of 2015 and 2016.
  • A 3 month summary of performance for the year of 2015 and 2016.

Creating a Year over Year Weekly Performance Summary Dashboard

The first step in this process is to create a dedicated dashboard. This will house all of your information. You can pick any colors you like or design accordingly. I prefer to use a large label over each query that clearly indicates what year it is.

Creating your current year (2016) weekly performance summary

We will start by building a query that will showcase 2016 data by week. This query will show you several performance based metrics and how they are doing over the past 4 weeks. It is important to note what dates you are setting this query to pull. If you want to understand your weekly performance you should ensure you have an appropriate time range. In this example we will start our week on a Monday and end it on a Sunday.

How to create a formatted way to display weekly data

  1. In the query builder, click the Calculation engine.
  2. Select Standard calculated attribute.
  3. Name it a detailed and descriptive name ( I used formatted week as a naming convention)
  4. Input the following formula into the formula section: MONTH_NUMERIC(START_OF_WEEK_ISO([DATE(Date)]))+"/"+DAY(START_OF_WEEK_ISO([DATE(Date)])) +" - "+ MONTH_NUMERIC(END_OF_WEEK_ISO([DATE(Date)]))+"/"+DAY(END_OF_WEEK_ISO([DATE(Date)]))
  5. Save this standard calculated attribute.
  6. Add it to your query by adding it under the Rows section.
  7. Select the Result manipulation.
  8. Select Sort.
  9. Set this formula to sort Z-A so that your current date will be the first line item of your query.
  10. Flowing these steps, your dates will be shown as 8/29 – 9/4, etc. and be shown with the oldest time period being at the bottom of the query.

Adding in your performance metrics

Once we have the dates set, we can gather the performance metrics we will monitoring over this time period.

  1. Under the measures section press the (+).
  2. Add any metric you wish to evaluate into the section.

Setting the correct reporting period

The final step to creating this query is to set the proper reporting time period. To create a query that looks only at the past 4 weeks:

  1. Select the Date period.
  2. Set the date period From the beginning of 4 weeks in the past.
  3. Then set it To the end of 1 week in the past.

Once you have completed the query, save it and place it in your dashboard.The end result should look like this. 

Creating your current year (2016) monthly performance summary

The second query will look at the past three months of this year.

Creating your new query and adding in your time metrics

  1. First we must create a new query.
  2. Add MONTH(date) to the Rows section on the left hand side of the screen.

Adding in your performance metrics

Once we have the dates set, we can gather the performance metrics we will monitoring over this time period.

  1. Under the measures section press the (+).
  2. Add any metric you wish to evaluate into the section.

Setting the correct reporting period

The final step to creating this query is to set the proper reporting time period. To create a query that looks only at the past 3 months:

  1. Select the Date period.
  2. Set the date period From the beginning of 3 months in the past.
  3. Then set it To the end of 1 month in the past.

Once you have completed the query, save it and place this in your dashboard.The end result should look like this. 

Understanding Your 2015 Data

The next two queries will look at the same date ranges except they will be for 2015 as opposed to 2016. We will start by looking at the same week range one year in the past. It is important to note that we’re not looking at the same exact day range (as in 8/1/2016 – 8/7/2016 vs 8/1/2015 – 8/7/2015). What we are looking at is something closer to what is the 32nd week of 2016 vs the 32nd week of 2015. Which may end up looking like 8/7/16-8/7/16 vs 7/30/15 - 8/5/15.

Creating your prior year (2015) weekly performance summary

This third query will look at the last year’s weekly performance.

Creating your new query and adding in your time metrics

  1. First we must create a new query.
  2. Add our previously made Standard calculated attribute “formatted week” to the Rows section on the left hand side of the screen. (If you forgot about this formula please see the creating your current year (2016) weekly performance summary section).

Adding in your performance metrics

Once we have the dates set, we can gather the performance metrics we will monitoring over this time period.

  1. Under the measures section press the (+).
  2. Add any metric you wish to evaluate into the section.

Setting the correct reporting period

The final step to creating this query is to set the proper reporting time period. To create a query that looks at the same weekly ranges but in 2015:

  1. Select the Date period icon.
  2. Set the date period From the beginning of 56 weeks in the past.
  3. Then set it To the end of 53 week in the past.

Once you have completed the query, save it and place it in your dashboard. The end result should look like this. 

Creating your prior year (2015) monthly performance summary

The fourth query will look at the past three months of last year.

Creating your new query and adding in your time metrics

  1. First we must create a new query.
  2. Add MONTH(date) to the Rows section on the left hand side of the screen.

Adding in your performance metrics

Once we have the dates set, we can gather the performance metrics we will monitoring over this time period.

  1. Under the measures section press the (+).
  2. Add any metric you wish to evaluate into the section.

Setting the correct reporting period

The final step to creating this query is to set the proper reporting time period. To create a query that looks at the same 3 months we have in 2016 but in 2015:

  1. Select the Date period.
  2. Set the date period From the beginning of 15 months in the past.
  3. Then set it To the end of 13 months in the past.

Once you have completed the query, save it and place this in your dashboard. The end result should look like this. 

4 comments

Please sign in to leave a comment.