topic Re: Creating a 28 day conversion report over time? in Reporting

Creating a 28 day conversion report over time?

johanlieu — Fri, 11 Apr 2014 18:34:20 GMT

I have the following query which works (in pseudo-query with annotations):

# 1. This first search returns the total number of free account signups from 28 days ago.

    index=site 
    source="/source/signup.log" 
    conversion="signup"
    earliest=-28d@d 
    latest=-27d@d
    plan="free" |
    stats count as NewSignUps |

    # 2. This appended search looks for all upgrade events that occurred in the past 28 days.

    appendcols [
    search
    index=site 
    source="/source/upgrade.log" 
    status="upgrade"
    earliest=-28d@d
    latest=-0d@d

    # 3. This subsearch returns all of the profileIds of those users who created a free account from 28 days ago. The same search in #1 above. This subsearch pipes those profileIds into the search above.

    [ search 
    index=site 
    source="/source/signup.log" 
    conversion="signup"
    earliest=-28d@d 
    latest=-27d@d 
    plan="free" | 
    table profileId ] |

    # 4. This returns the number of free accounts created 28 days ago and then converted in the 28 day period since.

    stats count as NumberOf28DayUpgradeConversions ] |

    # 5. This evaluates the conversion rate.

    eval 28DayConversionRate = round(100*NumberOf28DayUpgradeConversions/NewSignUps,2)."%" |

    # 6. This displays the conversion rate.

    table 28DayConversionRate

The query works fine but since the date parameters are hardcoded, it only works on the day that you run the query. I would love to be able to run this query but for a time range so that you can see on any arbitrary date what that 28 day conversion rate looks like.

Is it possible to modify this query to parameterize the date attributes so I can create this report over an arbitrary time period?

Re: Creating a 28 day conversion report over time?

somesoni2 — Fri, 11 Apr 2014 19:22:05 GMT

Thanks for the brain teaser question. Please try the following (disclaimer, performance not guaranteed), you can run this for any period.

|stats count | addinfo | eval time=info_max_time."#".info_min_time | table time | eval time=split(time,"#") 
| mvexpand time| rename time as _time | timechart span=1d count | makecontinuous | rename _time as Date | table Date 
| map search="search index=site source=\"/source/signup.log\"  conversion=\"signup\" plan=\"free\" 
[|stats count | eval earliest=relative_time(\"$Date$\",\"-28d@d\") | eval latest=relative_time(\"$Date$\",\"-27d@d\") | table earliest, latest]
| stats count as NewSignUps 
| appendcols [ search index=site  source=\"/source/upgrade.log\"  status=\"upgrade\"  
[|stats count | eval earliest=relative_time(\"$Date$\",\"-28d@d\") | eval latest=relative_time(\"$Date$\",\"0d@d\") | table earliest, latest]
    [ search index=site source=\"/source/signup.log\" conversion=\"signup\" plan=\"free\" 
    [|stats count | eval earliest=relative_time(\"$Date$\",\"-28d@d\") | eval latest=relative_time(\"$Date$\",\"-27d@d\") | table earliest, latest]
    | table profileId ] 
    |stats count as NumberOf28DayUpgradeConversions ] 
| eval 28DayConversionRate = round(100*NumberOf28DayUpgradeConversions/NewSignUps,2).\"%\" 
| table 28DayConversionRate"

Re: Creating a 28 day conversion report over time?

johanlieu — Fri, 11 Apr 2014 20:16:14 GMT

This query returns 0 events when I run it. Tried it for today and over a time period. I noticed that you escaped the quotes of the field=\"value\"; I did that when I replaced my real fields but wasn't sure if that was needed or not.

I tried removing the first pipe from the first "|stats count" as well, but it returns an error that map could not find the Date attribute. Any ideas?

Re: Creating a 28 day conversion report over time?

somesoni2 — Fri, 11 Apr 2014 21:06:12 GMT

Check if the command before map returns data for you. Also, when you paste the whole command, remove extra spaces/newline from the value of '|map search=".....', they create issues sometime. Another troubleshooting step would be run only the first portion of the query (before appendcols) in the map and see if that works.