Calculated Metric: IF Greater Than using a rolling 12 month look back to measure engaged visitors | Community
Skip to main content
G
gbolanis
Level 2
June 1, 2022
Solved

Calculated Metric: IF Greater Than using a rolling 12 month look back to measure engaged visitors

  • June 1, 2022
  • 1 reply
  • 1600 views

Customer Metric:  Return Engaged Customers 
Identify total number of visitors and visits from return engaged customers defined as visitors that have visited our site 4 or more times over the last 12 months

 

How can I do this?  

 

What I did that isn't working

I created a calculated metric using the following general logic

  • If over the last 12 complete months (segment) visits are greater than 2 (greater than function)
    • (if true) unique visitors
    • (if false) "0"

I can just create a reporting panel where the reporting range is 12 months and then apply a segment to my measures requiring visits to be GTE 4 BUT I want to find a way that I can use this metric in any reporting range so I could do YTD reporting and compare that against pervious year and periods.

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Jennifer_Dungan

No worries 🙂 

 

Let me try and explain with some examples:

 

Basically, there is an order of operation in the Workspaces.. the segments process first. Before the Selected Workspace Data Range (either the one at the panel level, or the ones dragged into the columns).

 

So when you create a segment at the "Visitor" level, the rules placed there are for "all time" for that user. So when trying to get information for "At least 4 visits in the last 12 months", you need to add that date range right into the segment so that the visits is constrained by that range... but now.. that date range is "baked" into the segment.

 

 

You lose the ability for this segment to adjust to your selected Workspace Date Ranges. This is what I was referring to, the segment definition is time locked, which is what you need... as per your requirement, you needed to see 4 or more visits in the last 12 months... (if you don't add the date here, it will be 4 or more visits of all time, regardless of your report date range)

 

If you create a segment which is just "Visits greater than or equal to 4", your segment will find all users who have visited your site at least 4 times for all time. Then the date range on your report will just show you the user count of the users who fit into the first criteria, who visited your site within your report range. You will get vastly different results.

 

 

When I tested this myself, I found I didn't really need to create a calculated metric, I ended up with the same value if I just applied by segment stacked with UV metric

 

 

(I made sure that "Last 12 Month" was part of the name so that I wouldn't forget that this was locked).

 

To do a comparison to the "Previous Comparable Range", I can create an identical segment using the new custom date range as I posted before, then I would make sure to name the segment appropriately (something like "At Least 4 Visits - Previous 12 Months", or something like that).

 

This means that both columns will be locked to specific date ranges... it won't matter if you change your report time frame (you can even test this by changing to This Month, This Week, Today, etc... the values in your column will stay constant based on the rolling calculation.

 

Doing this trick of course means you can just right click on a column and use the "add time period column" to compare to another date range.. but you can create your own calculated metric to do a comparison easily. 

 

Select the two columns, right click, create metric from selected, and divide, or percent change.. whatever you need.. (just make sure you edit the metric so it actually does the divisions in the correct order of operation, and rename them to something easier on the eyes).

 

 

I hope this helps... if you need more clarification, please let me know 🙂

1 reply

Jennifer_Dungan
Community Advisor and Adobe Champion
Jennifer_DunganCommunity Advisor and Adobe Champion
Community Advisor and Adobe Champion
June 1, 2022

The one problem with segments in Adobe, is that if you need the actual segment to be within a time frame, you actually have to add that date range right into the definition... it's not really something that can is flexible... 

 

That said, if you are building a report for doing things comparing to a specific set time range, like say "Last 12 Full Months" to "Previous 12 Months", you can create a custom date range and create a second segment using the custom date range, then compare your Last 12 Weeks and Previous 12 Weeks columns....

 

You can create a rolling custom date range for "Previous 12 Months" like this:

 

It would be really nice to have rolling "flexible" segments... but that's currently not available.

G
gbolanisAuthor
Level 2
June 1, 2022

Thank you for the prompt reply!

I just want to confirm my understanding of your response: "if you need the actual segment to be within a time frame, you actually have to add that date range right into the definition."   

  • which definition does the above refer?  is it the calculated metric definition or the free form table definition?
  • I think you are saying the latter but I wanted to be 100% certain

Thanks again for all your help!

Jennifer_Dungan
Community Advisor and Adobe Champion
Jennifer_DunganCommunity Advisor and Adobe ChampionAccepted solution
Community Advisor and Adobe Champion
June 1, 2022

No worries 🙂 

 

Let me try and explain with some examples:

 

Basically, there is an order of operation in the Workspaces.. the segments process first. Before the Selected Workspace Data Range (either the one at the panel level, or the ones dragged into the columns).

 

So when you create a segment at the "Visitor" level, the rules placed there are for "all time" for that user. So when trying to get information for "At least 4 visits in the last 12 months", you need to add that date range right into the segment so that the visits is constrained by that range... but now.. that date range is "baked" into the segment.

 

 

You lose the ability for this segment to adjust to your selected Workspace Date Ranges. This is what I was referring to, the segment definition is time locked, which is what you need... as per your requirement, you needed to see 4 or more visits in the last 12 months... (if you don't add the date here, it will be 4 or more visits of all time, regardless of your report date range)

 

If you create a segment which is just "Visits greater than or equal to 4", your segment will find all users who have visited your site at least 4 times for all time. Then the date range on your report will just show you the user count of the users who fit into the first criteria, who visited your site within your report range. You will get vastly different results.

 

 

When I tested this myself, I found I didn't really need to create a calculated metric, I ended up with the same value if I just applied by segment stacked with UV metric

 

 

(I made sure that "Last 12 Month" was part of the name so that I wouldn't forget that this was locked).

 

To do a comparison to the "Previous Comparable Range", I can create an identical segment using the new custom date range as I posted before, then I would make sure to name the segment appropriately (something like "At Least 4 Visits - Previous 12 Months", or something like that).

 

This means that both columns will be locked to specific date ranges... it won't matter if you change your report time frame (you can even test this by changing to This Month, This Week, Today, etc... the values in your column will stay constant based on the rolling calculation.

 

Doing this trick of course means you can just right click on a column and use the "add time period column" to compare to another date range.. but you can create your own calculated metric to do a comparison easily. 

 

Select the two columns, right click, create metric from selected, and divide, or percent change.. whatever you need.. (just make sure you edit the metric so it actually does the divisions in the correct order of operation, and rename them to something easier on the eyes).

 

 

I hope this helps... if you need more clarification, please let me know 🙂

Terms & ConditionsAccessibility statement

Loading footer...