Guideline for tracking analytics

[brettcave]

hi,

I am trying to build some reports for web analytics, and was wondering if there is a guide for building reports around page views?

i have a custom logging configuration in a web app, that logs requests (access logger). I am trying to build a report that shows a ratio of users that hit various points in the application. access logs contain a visitorId, sessionId and pageview, so an example of the type of report i am trying to build would be;

get a count of total distinct visitors: dc(visitorId)
count visitors that hit a certain pageview: pageview="/x" | dc(visitorId)
get a count of visitors that hit pageview="/y" (which would be a subset of x)
and then express it as a ratio.

Visitors    page X     page Y
  1000       58%        26%

I am just looking for a base of commands, i have been reading through the command reference for a while and trying some variations, but don't seem to be able to come up with the correct sequence of commands 😞

[dmaislin_splunk · Accepted solution]

The following search shows a conversion funnel that identifies the number of unique visitors that came to your site, of those visitors how many of them were referred to your site from a search engine, and of those referred from a search engine, how many went directly to the download page.

eventtype=web-traffic-external | dedup clientip | eval stage="stage 1: unique visitors to site" | append [search eventtype=web-traffic-external AND eventtype=search-referer | dedup clientip | eval stage="stage 2: unique visitors from search referrers"] | append [search eventtype=web-traffic-external AND eventtype=search-referer AND uri=*download* | dedup clientip | eval stage="stage 3: unique visitors from search referers that land on download"] | stats count by stage

The first stage searches for all external web traffic, as defined in the web-traffic-external eventtype. We add a field called stage to capture a unique identifier for events that match this stage of the filter. We then append a search for the second stage of the funnel, to search fo all external web traffic that has a search engine as a referrer, as captured by the search-referer eventtype. We set the stage field to uniquely identify events that make it through this stage of the funnel. We then append a third funnel stage that additionally searches for the term download in the uri field. In all these stages, we dedup the clientip field to get a distinct count of visitors. Finally, we perform a count by stage to identify how many unique visitors appear in each stage of the funnel.

The following search shows how many visitors visit the Products page before or after the Download page only. It also calculates how many people visited the products page immediately preceding download and how many did not visit Products at all before or after downloads:

eventtype=web-traffic-external | transaction clientip | table clientip uri_path | makemv uri_path | eval downloadsoffset= mvfind(uri_path, "download") | eval docsoffset= mvfind(uri_path, "product") | eval docsbeforedownload = if(docsoffset =0, 1, 0)| stats sum(docsbeforedownload) as "Visit Docs before Download" sum(docsrightbeforedownload) as "Visit Docs immediately before Download" sum(docsrightafterdownload) as "Visit Docs immediately after Download" sum(nodocs) as "Did Not Visit Docs before or after download"

We use the transaction command to sessionize all the external Web traffic (eventtype=web-traffic-external) based the the IP address (clientip). Then, we use the eval command to look at the URI path (uri_path), a multivalued field where each value is the URI visited by that particular clientip. We then use the mvfind command to identify the offset (sequence number) in uri_path where the downloads page was visited and the offset in uri_path where the products page was visited. Comparing the downloads offset and products offset provides us an ordering of when these pages were visited relative to one another. Lastly, we use the stats command to count the number of clientips that visited the download page only, the products page before or after the downloads page etc.

[brettcave]

... | transaction mvlist=t TransActionField | ...

That will keep in natural order instead of ordering lexigraphically 🙂

[brettcave]

will pop in later, unfortunately the port is blocked at work

[dmaislin_splunk]

Yes, transaction takes everything by SessionID and orders it into individual events by time.

[brettcave]

(its the transaction that does it)

[dmaislin_splunk]

Hard to tell when I don't have your data to try it on. Come join our IRC chat room and say hi. Go to http://chat.efnet.org:9090 and join our #splunk room. We can all talk about it there. You will see me on there as dmaislin.

[brettcave]

I've been playing around with the 2nd query. Does the offset work for you? When I run that in Storm, the uri_path results in the table are sorted alphabetically. I have tried adding in a sort before / after table and makemv commands, but it always sorts alphabetically.

eventtype=analytics | transaction SessionID | table SessionID RequestURI | makemv RequestURI | eval ... | eval ...

sessionId /a
/b
/c

[dmaislin_splunk]

Yes. Just do one thing, set a count with an eval links=step1 statement, then append another search and add another eval links=step2, then append another search and add another eval links=step3 statement, then finish it with a | stats count by links

[brettcave]

thank you. basically using eval and append to build up the data, it looks great.

have also installed the web analyser app and am trying to see if it works for us).

[brettcave]

will see if I can install it when storm comes back online.

Does the app work with custom fields? There are no log files, our application logs directly to splunk and uses its own logging format.

[dmaislin_splunk]

You don't need an app to do any of this. Core Splunk would work fine and and data in any format can be extracted and normalized to match the requirements for the fields used in any app. You should read up on field extraction, commands such as rex and erex, and the UI based field extraction when you select the drop down next to any event. Finally there is also a field extraction app if you need it.

[lguinn2]

You could also install the free version of Splunk on your laptop/desktop/whatever. Then download and install apps locally. I do this all the time to see "how did they do that?"

I learn best by example, and this is a great way to do that.