topic Re: How to create a month over month, year over year report on web analytics metrics data from CSV files? in Getting Data In

How to create a month over month, year over year report on web analytics metrics data from CSV files?

jrcajide — Wed, 05 Nov 2014 11:42:01 GMT

Hi folks!
This is my first post here. I am new to Splunk although I have been intensively working with it for the last 8 weeks. I would appreciate some help for you to create a report. I have read dozens of posts, even the great Exploring Splunk book by D. Carasso but couldn't get exactly what I am trying to achieve.
I have daily events from csv files with some web analytics metrics. What I want is to generate a report in the following way:

month    users  usersMoM  usersYoY  sessions  sessionsMoM sessionYoY ...
201410  500 10%    -4%      25000     12%       -3% ...

Where MoM compares the metric towards the value from previous month (201409) and YoY towards the value from the same month from previous year (201310)
Is it possible?
Thanks for your help.

Re: How to create a month over month, year over year report on web analytics metrics data from CSV files?

Muryoutaisuu — Wed, 05 Nov 2014 13:10:54 GMT

Hi. Assuming situation from your question:

current month = 201411

month = 201410

previous month = 201409

Try:

 YOURSEARCH -1month@month latest=@month | stats count users as users_month
| append [ YOURSEARCH earliest=-2month@month latest=-1month@month | stats count users as users_MoM ]
| append [ YOURSEARCH earliest=-1y@month-1month latest=-1y@month | stats count users as users_YoY
| eval users_MoM_perc=(users_month / users_MoM) users_YoY_perc=(users_month / users_YoY)
| table month,users_month AS users,users_MoM_perc,users_YoY_perc ...

If you have difficulties defining rigth time definition, click on the timepicker and go to Advanced. Beneath the expressions Splunk will show you the selected date.

Might have typos. Hope this helps nevertheless.

Re: How to create a month over month, year over year report on web analytics metrics data from CSV files?

jrcajide — Mon, 28 Sep 2020 18:05:20 GMT

Thank you Muryoutaisuu
I have been trying you approach but I can get what I'm trying.

* earliest=-2month@month latest=@month | eval month=strftime(_time,"%m") | stats sum(num_users) as users_month by month 
 | append [* earliest=-2month@month latest=-1month@month | stats sum(num_users) as users_MoM  | stats first(users_MoM) as prevoius_month  ] |table month users_month prevoius_month

This returns users_month correctly, and also calculate prevoius_month users but in a new row. Also It doesn't calculate prevoius_month users for previous months, only for the last one.

Any idea? Thanks so much

Re: How to create a month over month, year over year report on web analytics metrics data from CSV files?

Muryoutaisuu — Mon, 28 Sep 2020 18:10:21 GMT

Why are you searching in your first search earliest=-2month@month instead of -1month@month ? Your range now is two months.

In your second search: Is it really necessary to make the stats first(...) as ... ? I don't see the reason.

Quote: "Also It doesn't calculate prevoius_month users for previous months, only for the last one." I thought that was as intended? If you want your search to take a median, you need your subsearch to sum the users from something like append [ YOURSEARCH earliest=-13month@month latest=-1month@month | stats sum(num_users) AS users_MoM | eval previous_months=(users_MoM/12) ]

Re: How to create a month over month, year over year report on web analytics metrics data from CSV files?

jrcajide — Tue, 11 Nov 2014 11:36:53 GMT

But, what I am trying is to get a dataset, a full year to data, each month in a row with a column that is a comparison with prevoius month...
Is that possible?
Thanks

Re: How to create a month over month, year over year report on web analytics metrics data from CSV files?

Muryoutaisuu — Tue, 11 Nov 2014 11:57:05 GMT

Don't know if you can do that generic. But surely you can do it absolutely:

YOURSEARCH earliest=-1month@month latest=@month |eval month_0=strftime(_time,"%m") | stats sum(num_users) as users_0 by month_0
| append [ YOURSEARCH earliest=-2month@month latest=-1month@month | eval month_1=strftime(_time,"%m") | stats sum(num_users) as users_1 by month_1]
| append ....
| eval users_1_0=(users_0/users_1) users_2_1=(users_1/users_2) ...
| table month_0,users_0,month_1,users_1_0, ...

not sure, if I got the code right. Might have typos or fallacies.

I think in your case aggregating values might be a good option.

Re: How to create a month over month, year over year report on web analytics metrics data from CSV files?

Muryoutaisuu — Tue, 11 Nov 2014 14:19:48 GMT

Don't know why I forgot to bring up that exceptional tool:

timewrap: https://apps.splunk.com/app/1645/

download and install it and then make your search something like that:

YOURSEARCH earliest=-1year@month latest=@month
| timechart sum(num_users)
| timewrap m

The you also can make a fancy chart

Re: How to create a month over month, year over year report on web analytics metrics data from CSV files?

wpreston — Tue, 11 Nov 2014 14:21:31 GMT

Have you tried the timewrap app? It adds a new Splunk search command that was specifically created for time over time comparisons. You can get it here: https://apps.splunk.com/app/1645/. Give it a try and see if it works for you.

Re: How to create a month over month, year over year report on web analytics metrics data from CSV files?

wpreston — Tue, 11 Nov 2014 14:22:13 GMT

You're too quick for me 🙂

Re: How to create a month over month, year over year report on web analytics metrics data from CSV files?

Muryoutaisuu — Tue, 11 Nov 2014 14:24:24 GMT

Got me 6 days to remember timewrap... 😉

Re: How to create a month over month, year over year report on web analytics metrics data from CSV files?

jrcajide — Wed, 12 Nov 2014 08:37:10 GMT

What about this approach?

searchstring  earliest=-12month@month latest=-1month@month
| bucket _time span=1mon
| stats sum(users) as users by _time
| eval Month = strftime(_time, "%b %Y")  | addinfo
| appendcols [ search searchstring
| eval earliest_time=relative_time(info_min_time,"-2m")
| eval latest_time=relative_time(info_max_time,"-1m")| where _time>earliest_time and _time<=latest_time| eval ReportKey="previousmonth"| timechart span=1mon sum(users) as prev_month_users  by previousmonth]

I think is the way to get previous month value for every month but relative_time functions or addinfo seems not to be working ...
Thank you again for your help.