how can i create cohorts in domo ?

Options

Comments

  • guitarhero23
    guitarhero23 Contributor
    Options

    Can you be a little more specific? I'm not sure I understand what you mean.


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • AS
    AS Coach
    Options

    Does your cohort labels not exist in the data already as columns?  That would certainly be quickest for Domo to digest and use in cards.

    If not, you can bring your data into a SQL dataflow and generate some classifications based on whatever you want.  Then you can use those classifications like customer type or gender or whatever as filters in cards to see how the varying groups' data responds to different analyses.

    Not sure if that answers your questions.  If not, perhaps you can provide some more specifics about your situation.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • jhl
    jhl Member
    Options

    Hi,

     

    we use cohorts for tracking customer retention in a table card that looks pretty much like the first graph here. So what we want to see is

    - month (start of cohort action, in our case becoming a customer)

    - number of customers at the beginning 

    - number of customers retained for the next period (column)

    Example:

    Cohort / #of customers in cohort / Month 0 / Month 1 / Month 2 /...

    January17 / 500 / 100% / 99.6% / 98.3% / ...

    February17 / 400 / 99.9% / 99.8% / 99.6% /... 

    and so on

    Here is a rough overview of how you can do it with a MySQL dataflow.

    What you will need (whether you want to track retention or not):

    1) A dataset that consists of the dates you want to build your cohorts on (an excel file with one column with months or quarters will suffice)

    2) a column with the start date of the action you want to track

    3) column with the end date of the action (not all will have that but thats fine)

     

    This is what you do then: 

    -LEFT JOIN the dataset from 1) (there are ways to automatically generate date columns, but it is much easier to do this) on 2)

    - select the COUNT() of the customers whose start date is in the first cohort (this is your number of the cohort and repeat per all cohorts)

    - take the COUNT of those with who started in cohort 1 (January) and quit in the same month (then month+1, +2 etc) and compare to the total count in the cohort (we just calculate 1-(quitters/starters)) and repeat for the each cohort - be aware in your calculations that those who will not have a quitting date will have a NULL value there.

     

    (The trick for all of these here is nested SELECT statements)

     

    Alternatively, you can also add up all the sales/visits/actions for such a cohort if that is what you are interested in, but you will also need that data in there.

     

    Add some coloring rules to your table as desired and there you go ? We just display the entire dataset in a table, but you have other graphing options as well.

     

    There is a full PostGRE example here  (I think the month_diff function doesnt exist in MySQL, so use period_diff) that should be relatively easily transferable to Domo. Just remember that you cant just run all of it at once, but will need to break it up into smaller chunks.

     

    HTH

     

    Another (somewhat primitive) way of visualizing is explained here using just a stacked bar chart for the cohorts.

Categories