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
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
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
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