Expand my Community achievements bar.

Webinar: Adobe Customer Journey Analytics Product Innovations: A Quarterly Overview. Come learn for the Adobe Analytics Product team who will be covering AJO reporting, Graph-based Stitching, guided analysis for CJA, and more!
SOLVED

How to calculate worst hour and best hour of the day by Visits and show them as separate columns?

Avatar

Level 2

Hello Community,

 

I am looking to create a dashboard for our stakeholders where they wanted to see the Worst hour in a day by Visits and the Best Hour in a day by Visits as a separate columns. 

I have below table which shows Visits / Day and looking to have 2 new columns which can show me the worst hour (in terms of visits) and number of visits on that hour, best hour(in terms of visits) and number of visits on that hour. 

vallus_0-1657660320318.png

The output iam looking for can be like below, just sharing it as an example:

 

Visits

Best Hour

Best Hour Visits

Worst Hour

Worst Hour Visits

Day

 

 

 

 

 

May 2, 2022

8,114

11:00 AM 

465

12:00 AM

 3

May 4, 2022

79,018

9:00 AM

1,003

4:00 AM

8

May 5, 2022

65,111

7:00 AM

5,045

5:00 AM 

200

 

Is there any way I can get these metrics in Adobe Analytics. Any help or suggestions would be greatly appreciated.

 

Thank you very much!

Satish

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

I don't believe you can display data natively in Workspace as you are trying to do.... 

 

You can create a "heatmap" showing on average the best and worst times of the day, based on day of the week (I will show that at the end); and you can get the "Max" and "Min" value for the column, but this will just get you the value of the day with the most/least visits, and will show that value on every row (to get by hour, your table would have to have each hour on the row - really not what you want to show).

 

If I were trying to create such a report, I would probably use Report Builder to pull the data in with an hourly granularity, then use Excel Formulas to find the min/max value (all doing this in "hidden tabs") and stitch the final data together into the final requested format in a visible tab.

 

 

What I do know how to do, and I know this isn't exactly what you want... but, I would create a freeform table using the Visits metric, but broken down by "Day of Week":

Jennifer_Dungan_0-1657663592106.png

 

Then use "Hour of Day" in the breakdown:

Jennifer_Dungan_1-1657663643815.png

 

 

Then I would configure the Visits to be "conditional formatting"

Jennifer_Dungan_2-1657663733995.png

 

 

Then finally turn off the values:

Jennifer_Dungan_3-1657663784764.png

 

 

This will create a "heat map" of your traffic high/lows for whatever time frame you select:

Jennifer_Dungan_4-1657663891476.png

 

So it's not the high and low for each specific day, but creates a nice visual of your traffic times for quick consumption (not so colour blind friendly if that is a concern... it would be nice if we could configure 'high", "low" and "mid" range colours.... ) 

View solution in original post

4 Replies

Avatar

Correct answer by
Community Advisor

I don't believe you can display data natively in Workspace as you are trying to do.... 

 

You can create a "heatmap" showing on average the best and worst times of the day, based on day of the week (I will show that at the end); and you can get the "Max" and "Min" value for the column, but this will just get you the value of the day with the most/least visits, and will show that value on every row (to get by hour, your table would have to have each hour on the row - really not what you want to show).

 

If I were trying to create such a report, I would probably use Report Builder to pull the data in with an hourly granularity, then use Excel Formulas to find the min/max value (all doing this in "hidden tabs") and stitch the final data together into the final requested format in a visible tab.

 

 

What I do know how to do, and I know this isn't exactly what you want... but, I would create a freeform table using the Visits metric, but broken down by "Day of Week":

Jennifer_Dungan_0-1657663592106.png

 

Then use "Hour of Day" in the breakdown:

Jennifer_Dungan_1-1657663643815.png

 

 

Then I would configure the Visits to be "conditional formatting"

Jennifer_Dungan_2-1657663733995.png

 

 

Then finally turn off the values:

Jennifer_Dungan_3-1657663784764.png

 

 

This will create a "heat map" of your traffic high/lows for whatever time frame you select:

Jennifer_Dungan_4-1657663891476.png

 

So it's not the high and low for each specific day, but creates a nice visual of your traffic times for quick consumption (not so colour blind friendly if that is a concern... it would be nice if we could configure 'high", "low" and "mid" range colours.... ) 

Avatar

Community Advisor

Thanks

 

I mean I love what they wanted to do and wish we could do natively.

 

 

FYI @vallus here are some Excel Formulas if you need them:

 

Min/Max "Value" in a range:
=MIN(A2:A20)
=MAX(A2:A20)

To get the "corresponding" hour/date whatever that connects to the min/max (but it one column over):
=INDEX(B2:B20,MATCH(MIN(A2:A20),A2:A20,0))

=INDEX(B2:B20,MATCH(MAX(A2:A20),A2:A20,0))

 

(the B range is the hour / date / etc that corresponds to the actual min/max value in the A range)

Avatar

Level 2

@Jennifer_Dungan Thank you very much for the detailed solution! Appreciate it!

I will try this out.