1 Reply Latest reply on Jan 22, 2019 2:56 PM by Don Wise

    Group users based on transaction history

    Sarah Linkenhoker

      I have a table similar to the following.

       

      Account IDPurchase IDSubscriptionExpired?

      1

      1

      TrialT
      12PremiumF
      13PremiumF
      24TrialT
      25PremiumT
      36TrialT
      37PremiumT
      38PremiumF

      Essentially I want to be able to group each account into a category based on the state of their transactions.

       

       

      If a user has a single Premium subscription active - label them as 'Premium' (User 1)

      If all of a user's subscriptions are in an expired status  - label them as 'Expired' (User 2)

      if a user has 2 or more active Premium subscriptions - Label them as 'Elite' (User 3)

       

      At then end, I want to be able to do a count of distinct account ID's that fall into each category.

      Premium - 1

      Elite - 1

      Expired -1

        • 1. Re: Group users based on transaction history
          Don Wise

          Hi Sarah,

          If I understood your criteria, here's my approach:

           

          I created a subscription flag calc to flag the criteria you were looking for:

          1 Subscription Flag.png

          Created a FIXED LOD to ACCOUNT ID to determine when using the Subscription Flag calc, what their named status would be if Elite:

          2-Elite Status.png

          Created a FIXED LOD to ACCOUNT ID to determine when using the Subscription Flag calc, what their named status would be if Expired:

          3-Expired Status.png

          Created a FIXED LOD to ACCOUNT ID to determine when using the Subscription Flag calc, what their named status would be if Premium:

          4-Premium Status.png

          Created a calc to bring all 3 statuses into one column/field; probably could've done all 3 fixed calc's in one...but gave them to you separately in the event you want to use them elsewhere.

          5-Status to One Column.png

          Final result after removing the extra columns:

          6-Overall Result.png

          Last was to create the worksheet for a count by Status type which is just a simple COUNTD([Number of Records] using the new Status pill:

          7-CountD by Status.png

           

          A 2018.3 workbook is attached. Hope it helps! Thx, Don

          1 of 1 people found this helpful