Using skydb for cohort analysis

[abhi]

Is it possible to use sky db for cohort analysis.

For example, let us say that I have the following events in sky db:

Id: "id0"
2014-02-14T16:31:40Z - {"amount":43,"userId":"id0"}
2014-02-13T16:20:40Z - {"amount":27,"userId":"id0"}

Id:"id1"
2014-02-15T16:07:40Z - {"amount":78,"userId":"id1"}
2014-02-14T16:56:40Z - {"amount":61,"userId":"id1"}

Using a query along these lines:

FOR EACH SESSION DELIMITED BY 10 DAYS
  FOR EACH EVENT
    SELECT sum(amount) GROUP BY userId
  END
END

I expect:

userId0 - 70
userId1 - 913

As each user is executing a subsequent txn within 10 days

Using a query along these lines:

FOR EACH SESSION DELIMITED BY 1 MINUTES
  FOR EACH EVENT
    SELECT sum(amount) GROUP BY userId
  END
END

I expect:

Session0:
userId0 - 27

Session1:
userId0 - 43

Session2:
UserId1: 61

Session3:
UserId1: 78

As each user is not executing a subsequent txn within 2 minutes.

[Ben Johnson]

Is it possible to use sky db for cohort analysis.

Sorry for the delay in getting back to you. Sky doesn't have full cohort analysis in place right now. It has something called Temporal Loops which allow you to iterate over a fixed time period (e.g. 1 month) for X number of times.

For example, this query will return the number of active customers starting from each customer's first event

WHEN action == "signup" THEN

  FOR i EVERY 30 DAYS WITHIN 180 DAYS

    FOR EACH EVENT

      WHEN is_active == true THEN    

        SELECT count() GROUP BY i

      END

    END

  END

END

But that would only show data for one row of a traditional cohort report. You could technically just a bunch of ranges queries on sign up date and probably make a full cohort report but it's not something that's natively built into Sky.

-- 
Ben