4 Replies Latest reply: May 15, 2014 10:00 AM by Nancy10 _ RSS

    Market Basket Report

    Nancy10 _

      I'm using Monarch Pro 9.01.  I'm trying to develop what I call a market basket report.  It's probably simplest to explain by example.


      This is for a retail store.  We have a sales history report showing the items purchased on every sales ticket.  We'd like to know all the items on a ticket if item X was purchased. 


      Ticket #101:

      1 shirt

      1 candy bar

      1 hat

      1 can tennis balls


      Ticket #102

      1 can tennis balls

      1 pr. shoes

      1 sleeping bag


      Ticket #103

      1 can tennis balls

      1 pr. socks


      Ticket #104

      1 hat

      1 pr. shoes


      Let's say I want to find out everything purchased on a ticket where the customer bought tennis balls.  It's easy to find all the tickets where tennis balls were purchased.  But how can I get Monarch to show me all the other items purchased on that same ticket?  Is this even possible?  I've been experimenting with different possibilities and have come up empty so far.


      Thanks for your help.



        • Market Basket Report
          Grant Perkins

          Hi Nancy,


          What an interesting challenge - I can think of a few questions but maybe too much detail for your purposes. Not sure yet.


          OK. Two initial thoughts.


          1. Make this a 2 pass process.


          I assume you already can extract the details split as you want them.


          If you were to create a summary with two fields


          a.  Sales product - tennis Balls, Cap, Sweets and so on (I would guess you actually have a fixed number of those with known names)


          b. Ticket number (and whatever else might be required to ensure it is unique - date for example).


          This would then give you the option filter using the sales item and do an external lookup to your exported master table based on the ticket number and pulling in all the sales lines for the ticket. I am making some assumptions about how you have extracted the separate lines.



          Option 2.


          If you already have a table with Ticket number and a list of sales items each in their own fields you could (maybe if the possible numbers of items on a ticket will allow) create a compound filter that would accept a user input at run time (Run Time Parameter field) and return any records with that parameter in one of the fields.


          Option 2a.


          A variation on the theme above.


          As well as the separates sales item field could you keep a block memo field containing all of the sales lines?  If so you could create a filter to search for the chosen sales item (as a text string in the field) and then return the details of all tickets which qualify. In fact you could possibly base the entire extraction on a block memo field using the TEXTLINE function in Search mode to create a columns for each possible sale product and then recording the quantity and/or sales value in the associated column. Filtering could then be based also on the existence of a value in the column for the appropriate sales item.


          You may need to be careful that you will not have duplicate descriptions that would affect the analysis success rate. You may need more than one filter string for example. If the idea becomes too complicated due to duplication than almost any method based on the description is likely to have the same problems, in which case you may need to look again at the data source to see what additional information could be added to help clarify the filtering.


          This might involve importing more information from another source file for example, but that is easy enough to do with V9 Pro. (Assuming you can find a source!).


          Some thoughts that I hope you can use in some way.


          Do let us know how you get on. If you are stuck just bring us up to date with what you have got and we can take it from there.






            • Market Basket Report
              Nancy10 _



              I think I'm more likely to be able to wrap my brain around Option 1.    I understand where you're headed with this, and I think I've got it figured out.


              Yes, I can easily extract the data from the report to a table.  Each record contains:




              Ticket #

              Item #

              Item Description

              Quantity Sold

              Unit Price


              Of course, the number of records per ticket varies.  I filtered on a particular Item # and exported the records to Excel.  Then I changed the filter back to no filter so I was looking at all the records.  Then I constructed an external lookup to the filtered records, and proceeded through the warning message that the source columns don't form a unique key to the external table.  This seems to have done the trick.


              Thank you for your brilliant idea!



                • Market Basket Report
                  Grant Perkins

                  Hi Nancy,


                  Yep, sounds like you got to the core of it to work. It could be refined BUT unless you need to roll this out for use by others (in which case you could prompt for the code on the way into the process, etc.) there may not be much added value in doing that right now. On the other hand you could use the summary facilities to make some standardized ticket list by key products available for instant export to your external lookup table(s). Just be careful with the way filtering is applied at each stage.


                  However if you have a manageable total number of possible sale items such that you could predefine a column for each of them for example, or perhaps just for those that are likely to be of most interest in the analysis, then you could look at the data slightly differently.


                  If you had one record per ticket rather than one per sales line you could possibly build a single record with a field for each sales line type with, say, the quantity from that line. That then provides a filter option of seeking records where the field for the target product has a qty. greater than zero.


                  If you send me a PM with an email address that will accept attachments I will put together a simple sample model, based on your sample, and send it to you. Just for interest and future reference.


                  Have fun.



                    • Market Basket Report
                      Nancy10 _

                      Hi, Grant.


                      I'm sure your Option 2 approach in your first message to me would be more elegant than what I've done, but at the moment, it's over my head.  The person for whom I'm developing this is a capable Monarch user, so I'm not too concerned about multiple steps. 


                      Oh, what a good idea to prompt the user for the key item at the outset.  Thank you!  I haven't worked with that feature; no time like the present, I suppose!  That will eliminate one step of what the user must presently do.


                      >>if you have a manageable total number of possible sale items <<


                      Unfortunately, I don't.  There are 30,000 items carried in the store.  In one week, there are 35,000 ticket line items.  I'd guess that most tickets have fewer than ten items, but I haven't analyzed it.


                      I'll send you a PM with my e-mail address.  Many thanks for your interest in this project.