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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...