Splunk Search

Graphing conversion rate over time by banner

opticsplanet
Path Finder

I have data like this:

[2011-04-23T23:59:54-05:00] bannerid=1210 action=view
[2011-04-23T23:59:55-05:00] bannerid=1210 action=view
[2011-04-23T23:59:56-05:00] bannerid=1210 action=click
[2011-04-23T23:59:58-05:00] bannerid=1210 action=view

I need to create a timechart of conversion rate over time. For starters, just for one banner id. But would be nice to do that for top 20 viewed banners during the reporting period.

Seems like standard timechart can't work with that, and I can't find anything similar to this posted in other questions. Would appreciate your help!

Tags (2)
0 Karma
1 Solution

gkanapathy
Splunk Employee
Splunk Employee
... | bucket _time span=1h | stats count, count(eval(action=="click") as conv by _time, banner | eval rate= 100*conv/count | xyseries _time banner rate

That will do all of the banners over all time. Or:

Or:

... | bucket _time span=1h | stats count, count(eval(action=="click") as conv by _time, banner | eval rate= 100*conv/count | eventstats sum(count) as bc by banner | dedup 20 _time sortby -bc,banner |  xyseries _time banner rate

will cut down on the number of banners, though probably will give you something approximately the top 20.

View solution in original post

gkanapathy
Splunk Employee
Splunk Employee
... | bucket _time span=1h | stats count, count(eval(action=="click") as conv by _time, banner | eval rate= 100*conv/count | xyseries _time banner rate

That will do all of the banners over all time. Or:

Or:

... | bucket _time span=1h | stats count, count(eval(action=="click") as conv by _time, banner | eval rate= 100*conv/count | eventstats sum(count) as bc by banner | dedup 20 _time sortby -bc,banner |  xyseries _time banner rate

will cut down on the number of banners, though probably will give you something approximately the top 20.

opticsplanet
Path Finder

And last one... It did not work to start with because I took out bannerid, and it was not present in the data. After I put it back - it worked.

0 Karma

opticsplanet
Path Finder

Oh, got it working... For single banner, and very weird, though 🙂 . . . | eval var="rate" | xyseries _time var rate
Thanks!

0 Karma

opticsplanet
Path Finder

Thanks! So everything up until xyseries works (although you have a missing ")"). But xyseries gives 0 results. I fixed banner to bannerid, as this is the field name. If I replace it with | xyseries _time "rate" rate - I get 10 columns in the table, and only one row with values for each. 😞

0 Karma

Ayn
Legend

So if I understand it correctly, the conversion rate here would be the number of clicks for a certain time period divided by the number of views? How far did you get, where are you hitting problems with timechart?

0 Karma
Get Updates on the Splunk Community!

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...

SignalFlow: What? Why? How?

What is SignalFlow? Splunk Observability Cloud’s analytics engine, SignalFlow, opens up a world of in-depth ...

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...