How to compare values in one column, to another columns, and return only the difference?

[Gregory.Burkhead]

We are sending order numbers into Analytics from Business Transactions.  We are sending the order numbers at various stages in the order flow.  We are sending order numbers as NEW orders, and sending the same order numbers when they reach a stage of completion, ASSIGNED.

The values are coming in from 2 different business transactions, so they show up in different columns in Analytics.

We are struggling to write a query to return only the order numbers that show up in NEW, that don't show up in ASSIGNED, to alert us to orders that may be stuck.

My sql guy tried:

select segments.userData.'NEW' from transactions where segments.userData.'NEW' is not null minus select segments.userData.'ASSIGNED' from transactions where segments.userData.'ASSIGNED' is not null

... but ADQL doesn't use 'minus' and also doesn't seem to allow two 'select' commands in a single query.

Any ideas?

[Mario.Morelli]

Hi Gregory

I am not sure regarding the ADQL query itself from the raw data, however you can use the Business Journey Function to accomplish this, which you can set 2 steps.

Step 1 - Event key would be the order number I would assume. With Criteria where you can use Status = "NEW "As starting point.

Step 2 - Event key would be order number, with criteria Status = "Assigned"

This would then show you how many have started , and finished, along with average time between these steps.

Hope this helps to get to what you require:)

Ciao

[Gregory.Burkhead]

Thanks.  I may look into that.  But from the way you describe it, it will not meet our needs.  We are looking to identify specific order numbers, that have not made it in the flow, from NEW, to ASSIGNED.

We already have a dashboard that simply uses Information Points that accomplishes much of what you describe, in that it shows the volume of orders, in each of those steps:  NEW, ASSIGENED.

What we are doing now doesn't show timings, except for the time selector in the dashboard.

[Mario.Morelli]

Hey Gregory

Apologies, should have elaborated a bit more to what I was referring to:)

When you create the Business Journey I proposed, AppDynamics creates a custom analytics schema with a subset of the analytical data for that specific Business Journey.

This subset of data has a field name called "completed" You can then just do a simple select query on that subset of data for any journeys that have not been completed yet. This will output all Orders that have started but not finished yet with the "Assigned" status.

I hope I am understanding your requirement:)

Ciao

[Gregory.Burkhead · Accepted solution]

This sound  much more promising.  I have never done a Business Journey before, but I have a video queued up that promises to teach me about them.

I'll let you know how it turns out.  Thanks for the recommendation!

[Gregory.Burkhead]

Hey,

Wanted to let you know, that the Business Journey worked like a charm!  It took me awhile to get it working, but once I did, it worked great!

 But now, I have another question!  

What we are doing here, is sending a NEW order status and an order number, and an ASSIGNED order status and the order number, from 2 different places in the order flow.

The new Business Journey we created, has only those 2 milestones... NEW, and ASSIGNED.

Occasionally, there are orders that get stuck, between NEW, and ASSIGNED.  And with this Business Journey, we are able to identify the order numbers, of the stuck orders, because they don't complete the Business Journey, so the 'complete' column shows 'false' for those orders.

But it takes an average of 2 minutes, under normal circumstances, for the order to flow from NEW, to ASSIGNED.

So we tried to write a query that would return 'completed'= 'false' (no problem, there), but then additionally, we wanted to add a condition to the query that would say 'don't return any orders that are not at least 10 minutes old.'  

There is a 'NEW_Orders.eventTimeStamp' value, but so far, we can figure out a way to say...   AND 'New_Orders.eventTimeStamp' is older than 10 minutes.

We figured out a way to bucket them, but I need to return a single value as a count, so I can use the search to create a metric that I can use in a health rule.

Here is the first part of the query... SELECT * FROM magento_order_flow WHERE completed = false

Thanks for turning me on to the Business Journey thing.  It was a tremendous help.

Thanks,

greg

[Mario.Morelli]

Hi Gregory

That's great to hear:)

Regarding your follow up questions, there is another field called "timeTaken", which you can use to check the time between the steps is more than 10 minutes.

Hope this solves your problem:)