3 Replies Latest reply on Mar 31, 2016 12:39 PM by Sho Fujiwara

    LoD or Window calc? (Effect of Coupon on successive orders)

    Sho Fujiwara

      Edited to include Attachment. Also edited the title to hopefully be more enticing...

      _____

       

      Hi all-

       

      I appreciate any help thinking through this problem that I have.

       

      I have a database of orders made in a marketplace, where each member has an opportunity to order once a week. In this database, I also have a field 'Coupon', which indicates the type of Coupon used (there are potentially many types).

       

      This table shows the example dataset:

      MemberWeekCoupon
      A1X
      A2
      A7
      A8
      A9
      B1X
      B4
      B6Y
      B7
      B9
      B10

       

      To track the impact of each Coupon on successive orders, I want to be able to count the number of orders made by each member in the 3 weeks after they use a Coupon (including the week of the Coupon, so this count could take values between 1 and 4). In other words, orders made within 3 weeks of a Coupon are treated as having been impacted by that Coupon and I want to count these.

       

      If each member could only use one Coupon, I can solve this problem with simple LoD. I can create a Fixed LoD at the [Member] level and calculate the [Week] that they used the Coupon. Then, any orders that came in between the Coupon [Week] and [Week] + 3 would be assigned some value to indicate that it was impacted by that Coupon.

       

      However, like Member B in the example, some members could use multiple coupons, so I can't use a [Member] specific LoD calculation. I tried LoDs at the [Member] + [Coupon] level, but quickly found that I couldn't directly use them to find orders that don't have [Coupon] values but are impacted by a Coupon.

       

      I also attempted something with Lookup(), but I need to aggregate these results and can't necessarily have it in the table form I have above.

       

      I was thinking that a potential solution would involve creating some other field ('CouponEffect') in the table below, that would check whether the same Member had some coupon within the last 3 weeks, and assign the value of the Coupon if so. For example, in the Table below, for Member B, Week 9, since Member B had used Coupon Y within the last 3 weeks (at Week 6), it assigns it a value of Y to 'CouponEffect'. I could then used this 'CouponEffect' field in my calculations. However, I'm having trouble building the logic for this.

           

      MemberWeekCouponCouponEffect
      A1XX
      A2X
      A7
      A8
      A9
      B1XX
      B4X
      B6YY
      B7Y
      B9Y
      B10

       

      Any and all help is appreciated!

       

      Sho

        • 1. Re: Conditional Formula based on another row
          Sho Fujiwara

          I looked into Window Calculations and discovered that they get me closer, somewhat, to a solution.

           

          I created a new Calculated Field with the following mess of a formula:

           

          if WINDOW_MAX(sum([Week]),-3,-3) = sum([Week])-3

          AND NOT ISNULL(WINDOW_MAX(attr([Coupon]),-3,-3))

          Then WINDOW_MAX(max([Coupon]),-3,-3)

          elseif WINDOW_MAX(sum([Week]),-2,-2) >= sum([Week])-3

          AND NOT ISNULL(WINDOW_MAX(attr([Coupon]),-2,-2))

          Then WINDOW_MAX(max([Coupon]),-2,-2)

          elseif WINDOW_MAX(sum([Week]),-1,-1) >= sum([Week])-3

          AND NOT ISNULL(WINDOW_MAX(max([Coupon]),-1,-1))

          Then WINDOW_MAX(attr([Coupon]),-1,-1)

          elseif NOT ISNULL(attr([Coupon])) THEN attr([Coupon])

          Else ''

          END

           

          Basically, it's looking at the previous 1-3 rows and checking if it's within 3 weeks of the current row week. If it is, it checks for a Coupon Value, and takes that Coupon Value if one exists.

           

          The resulting table looks like what I had asked for in my first post. Now, I can identify which Weeks were impacted by a Coupon, and what that Coupon Value was.

           

          However, by definition, the Window_Calc only works when the table is set up as is. If I want to aggregate this data - say, look into the average number of orders that are influenced by Coupon X or Y, I can no longer use the 'Calculation1' field. I could export the table and reimport it as a datasource, but that is cumbersome and manual.

           

          If anyone has ideas on how to proceed, I'd very much appreciate it!

           

          Sho

          • 2. Re: LoD or Window calc? (Effect of Coupon on successive orders)
            Brian Coggon

            Hi Sho -

             

            Do you need your CouponEffect to distinguish between Coupon X, Y, or Z? 

             

            If not, you can make this a calculated field. Use "if ISNULL([Coupon])then 0 else 1 END" to assign a value.  Change this dimension to a measure that can be summed or more importantly you can use a Table Calc to have a moving calculation that will pick-up the last 2 weeks (or 3 or 4) and this value can be used to evaluate the effect of your coupons...

             

            A question I have about week number is are you using the week number as a data point or is this actually a date field? If it is a date field you can simply bring out the date field and use this in your Table Calc.

             

            Let me know if this helps...

             

            Brian

            • 3. Re: LoD or Window calc? (Effect of Coupon on successive orders)
              Sho Fujiwara

              Thanks for the reply!

               

              Yeah, I need to distinguish between Coupon Values, since all of them serve different purposes and we want to capture those nuances.

               

              Also, the Week values are actual fields, independent of a calendar date that we also have as a field (but is irrelevant for this calculation)

               

              Unfortunately for me, my data is more nuanced than the example you gave. I can't do a running_sum or count based on some number of rows before the current, since my data often skips Weeks. Therefore, the previous row might have a coupon, but it may have been for an order 10 weeks before - in this case, I would not want the current row to take on that value. That's why I had to use the Window Calculations in my quasi-solution to find the value of the [Week] field in previous rows and check that it was within 3 weeks of the current value.