Graphing conversion rate over time by banner

[opticsplanet]

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!

[gkanapathy · Accepted solution]

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

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.

[opticsplanet]

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

[opticsplanet]

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

[Ayn]

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?