Worked out a solution, with big thanks to somesoni2 and sundareshr for their help.
sourcetype=Profile event=profile__create | eval mytime=strftime(_time, "%Y%m%d") | eval _time= strptime(mytime, "%Y%m%d") | stats count by _time | rename count as total_count
| join type=left _time [search sourcetype=Profile event=profile__create | eval mytime=strftime(_time, "%Y%m%d") | eval _time= strptime(mytime, "%Y%m%d") | fields _time user
| join type=inner _time user [search sourcetype=server_log event=RESPONSE | eval mytime=strftime(_time, "%Y%m%d") | eval _time= strptime(mytime, "%Y%m%d")-86400 | dedup _time user | fields _time user] | stats count by _time | rename count AS "day1"]
| eval perc=day1/total_count | bucket _time span=$granularity$ | fillnull day1 perc value=0 | stats sum(total_count) AS "Total Profiles Created" sum(day1) AS "User's that returned on day 1" avg(perc) AS "% of Users that returned on day 1" by _time
Couldn't find the best way to format it for readability, so I apologize for that mess. Basically,
1. I search and get all profile create events and reconfigure the _time attribute to snap to the basic date
2. I left join on _time with a big subsearch that will return only events that match (inner join) the user and _time of a profile creation AND a server response that gets booted back to the previous day (the day the profile was created).
3. Now i have a table of _time by dates, total_count of created profiles, and count of events that match activity the day after the user created a profile
4. I bucket this time on a span that can be configured (1d,7d,1mon) in a dashboard, fill null counts with 0, and table things out in stats
Probably unconventional and unoptimized. for a last 30 day search, it takes about 10 - 15 seconds for the query to crunch. I'm sure i'll revisit this at a later date to refactor, but right now it fits my needs.
... View more