Creating a segment based on TimeStamp UTC

[Patrick_Flanagan]

Hey folks, very much a NOOB here, and a little lost. I have a Prop where I stick the TimeStamp converted to Universal Time code (UTC) of the hit into. So if I were to say "sort" the data set on this value, I can see the history of when all the users were doing their thing. What I am trying to do now, is set up a segment that will show me how many users have recorded data within the same 5 minute time frame. From there I just need either the Adobe Visitor ID count, or from another prop, the SessionID count. The intent is to see how many different users have data points recorded for the 5 minute increment, over the course of a day, which hopefully gets me to a week. 

Right now I am doing this from an exported Data Warehouse report of every hit, with its TimeStamp UTC, Session ID, and Visitor ID. I have an elaborate set of Excel Macros to sort the data by timestamp, look at the five minute increment, tag all the unique Session IDs with a "one", then do it again for the next five minute increment, repeat until the week is over. The report takes about 20 minutes to process, and has to be fired of manually to import the Data Warehouse Data sent via email. I do not have the option of saving to an FTP server due to company security. 

Is there any way to create a Segment or Calculated Metric that could do this? Or some kind of Segment that can tell me for a five minute timeframe, here are all the Visits that are active? Thanks in advance. 

[Jennifer_Dungan]

Unfortunately, both props and eVars will store the timestamp as text.. plus you need to look at each rolling period, which Workspace isn't really equipped for...

While you can break down data by Minute in workspace, you can't create a custom time frame based on that small a granularity... Day is the smallest...

But let's use Day for this example... 

Suppose you were tying to do this with "hits within 5 days",

In order to achieve this, you would have to set up many many segments that deal with rolling days:

[start of current day minus 5 days - end of current day]

and

[start of current day minus 6 days - end of current day minus 1 day]

and

[start of current day minus 7 days - end of current day minus 2 day]

etc....

It's not really a feasible solution....(and that still supposes you could do this with minutes, which you can't at this time)

Do you have access to SQL? Maybe creating an automated API pull of data that stores the data into a DB, then processed the data that way... it could be fully automated at least? But might require support from a DBA...

Maybe someone else has a suggestion.. but at the moment, given the complexity of what you are trying to do, I don't believe it's possible to do so in Workspace at this time.

[Patrick_Flanagan]

Thanks Jennifer, was thinking that may be the case, but better to ask those that know way more than I do first. Have a great day.

[Jennifer_Dungan]

You too..

However, that would be a really cool use case, to have Adobe be able to calculate out a rolling period of activity...  You could always post it as an idea as something to consider.. it might be too complex for workspace, but maybe it will kick off something of an advances Analytics "workshop"... we can dream anyway  

[Pablo_Childe]

Hi Patrick I am curious what is actual business goal here you are trying to understand?

Number of users within a 5  min span so is it concurrent users at a given hour/time of day?

What is service/product you are selling these users?

[Patrick_Flanagan]

Pablo,

Yes, we are looking at technically Concurrent Users. The original ask for this was to give the server folks a "checks and balance" for their data capture of concurrent logins to make sure the server was not being overburdened. It has now shifted to the Business wanting to know how many users are hitting the application throughout a given day. When are the peaks and valleys? We have explained that the Hourly Date Range is probably just what they want, but I keep getting push back because "we already can report in 5 minute increments" it is just a pain. So in an effort to automate, I am taking a real hard look into what we can do in Adobe itself.

Now I seriously need to THANK YOU for asking this, it got me back into the data, and what I need is actually already there when I hit on the MINUTE dimension. Each record in the Data Suite has that recorded, and also has it recorded with conversions to the time zone, or UTC. So if I set a Workspace Project to report for Last Week and get me all the Visits by Minute, I get the 10,080 minutes in a week. So for any given minute I can see the total Visits at that point in time, as time. But ...

... the graphic visualization cannot get me a view that I need, which is a bar or line, that encompasses the Week, but has the individual data points of the minute graphed out, but a right click, download items as CSV and Excel can easily take it from there. Guess I need to ask the Adobe Engineers for some better graphing capabilities.

 

It will still be a manual process but I think you just shaved about 17 minutes off of my 20 minute weekly report. A little macro coding in Excel, and I bet I can get that down to about 15 seconds per report now.

[Jennifer_Dungan]

Ah, yes, if I had known that you were just looking for active traffic at a minute level granularity (as opposed to concurrent use within 5 mins span) I would have mentioned that!

I wasn't sure if maybe you had a section on your site that had section with a time out counter, and you needed to know if people were actively interacting with the site in those scenarios.

It's too bad that Workspace can't show a full day when broken down by minute... 

Good luck!

[Patrick_Flanagan]

Jennifer, You are correct, originally I was looking at the five minute increment because of what we did to set this up about four years ago. The Excel file doing the calculations and graph would fail with ALL the data from the Warehouse report, so the first step in the Excel process was removing the duplicate visits for the 5 minute section, and only count that as 1. Well before Workspace was available. Now that that engine is firing, getting the totals per minute is easy and downloads to CSV really fast. Thanks for the help. 

[Jennifer_Dungan]

Right.. I forget that the manual export can get more than 400 rows.. I think the automated CSV is still limited... 

And in workspace itself, you are limited to 400 rows. But you are right, you can export from here and use in Excel, it's still a little manual, but likely not as bad as before.

[Pablo_Childe]

ok glad I got you closer...

In my past I have worked with IT depts to get theoretical volumes for concurrency and what I do is look at last year.

Pick the single greatest volume day. Visits as it will give a bit more than Unique visits.

On the big day I break down by hour pick the greatest hour.

Now using that day and that hour you could have a sample hour of max usage any IT could then take that and lets say for buffer do a mx of 300%. Set that as its cap for concurrency and voila for 1 year they should be good to go. I find trying real time to track etc is poor return on effort as IT departments don`t typically change concurrency levels during the day they will pick a max ideal top and set it there.

Hope all goes well!