Creating a report determines values by mv / transaction fields

[brettcave]

I have a report that allows me to get a list of certain values per user:

data:

visitorID: A, category: foo, SomeMetric: 32
visitorID: A, category: foo, SomeMetric: 27
visitorID: A, category: bar, SomeMetric: 17
visitorID: A, action: someAction, actionValue: 3
visitorID: A, action: someAction, actionValue: 0

In above, fields are extracted. The first 3 events are MetricEvents, and the last one is an ActionEvent. Query:

eventtype="MetricEvent" OR eventtype="ActionEvent | stats first(SomeMetric) as average_field by visitorId category | stats sum(average_field) as total_metric by VisitorID

Result: visitor: A, total_metric: 49

I can also categorize visitors based on action events:

eventtype="MetricEvent" OR eventtype="ActionEvent | transaction visitorId | eval isActionValueVisitor=if(eventtype="ActionEvent" AND mvindex(ActionValue,mvcount(ActionValue)) > 0, 1, 0) | table visitorId isActionValueVisitor

I now can get a table with a list of users that have completed action with actionvalue > 0, vs visitors that have not completed action. But I would like to combine the 2 to get a report that shows the average of total_metric by isActionValueVisitor (from there I can run through stats to see how many users there are within each category). However, I can't get the 2 queries to work together, because of the use of stats / transactions in the 2 queries. (I could work it out if mvindex returned the natural order, mvindex / mvfind with a transaction sorts the field alphanumerically, so I have no way of getting the equivalent of "first()" from stats ).

How could I go about getting the value of visitors that are categorized based on transactions? (or a completely different approach?)

desired result:

visitor: A, total_metric: 49, isValueVisitor: 1
visitor: B, total_metric: 23, isValueVisitor: 0

Thanks
Brett

[brettcave]

There must be a simple way to pivot the results more easily than what I am doing...

From:

actionVisitor       average_total_metric
0                   33
1                   18

To:

Metric           Action Visitors        Non Action Visitors
Average Met      18                     33

This is what I have, I know there must be a simpler way to do this.

 .... | eval Metric="Average Met" | eval ActionVisitorAvg=if(actionVisitor==1,avg_total_metric,0) | eval NonActionVisitorAvg=if(actionVisitor==0, avg_total_metric,0) | stats sum(NonActionVisitorAvg) as NonActionVisitorAvg sum(ActionVisitorAvg) as ActionVisitorAvg by Metric

[brettcave · Accepted solution]

Figured it out, using a subsearch:

eventtype="MetricEvent" [ search eventtype="ActionEvent" ActionValue>0 | dedup VisitorID | fields + VisitorID  ] 
  | stats first(SomeMetric) as average_field by VisitorID category
  | stats sum(average_field) as total_metric by VisitorID 
  | eval actionVisitor=1 
  | append [ search eventtype=MetricEvent NOT [ search eventtype="ActionEvent" ActionValue>0 | dedup VisitorID | fields + VisitorID ]
    | stats first(SomeMetric) as average_field by VisitorID category 
    | stats sum(average_field) as total_metric by VisitorID 
    | eval actionVisitor=0 
  ] 
  | stats avg(total_metric) by actionVisitor