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:



      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.




      Any and all help is appreciated!



        • 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 ''



          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!



          • 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...



            • 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.