13 Replies Latest reply on Apr 17, 2012 10:09 AM by philmodjunk

    Analyze customers that have purchased a specific product

      Title

      Analyze customers that have purchased a specific product

      Post

      Hi. I've posted a couple times here this week trying to work through a problem and have received great feedback. Let me try to explain a little more clearly what I'm trying to accomplish, and where I am having difficulty:

       

      I have a fairly standard customers -> orders -> line items - > products - > prod titles - > titles

      database where customers have orders of products that are made up of multiple titles

      and I have been struggling with how to analyze "For customers that have own title a, what other titles do they own?"

      Currently I can run a report based on my titles table, sorted by title name, and displays a summary field in my customers table that counts customers. This gives me a count of customers that own a title.

      It seems a little counter-intuitive to me that this report should be based on the titles table since I really want to analyze customer information, but when I try to run a similar report on customers sorted by titles, it only counts each customer once (according to the first title they purchased).

      Now, I have a customers layout that has portals to other tables all the way down to titles where I can perform a find on a particular title name and get a found set of all customers that own a particular title.

      It seems if I could run my title report against just that found customer set, it would produce the results I want, but I can't figure out how to do it.

      - I've tried "go to related records", but matching a found set of related title records to customer records isn't what I need.

      - I've used a List function in a calculated field in my customer table to drill down and collect all titles the customer owns, so that data is there in a carriage separated list by title name.

      - I've spent a lot of time researching table occurrences trying to build in some parameters to "hard code" the found set query, if you will. 

      Once I figure out the basic technology or methodology to accomplish this, I want to generate a report that looks at each title shows how many customers own it, then below that loops through all the titles and shows of those customers the ownership distribution (if any, besides the 100% for said title).

      Any ideas or help would be greatly appreciated.

      Jason

      please_help.png

        • 1. Re: Analyze customers that have purchased a specific product
          philmodjunk

          The problem with starting a new thread instead of posting follow up comments and questions to the same discussion via the Post A Answer box, (misnameed and very bad grammar!), is that we then don't see the entire discussion.

          - I've tried "go to related records", but matching a found set of related title records to customer records isn't what I need.

          Why isn't that what you need? Before I can suggest an alternative approach, I need to know why that one didn't meet your requirements. This method can produce a found set of products purchased starting from a found set of customers (or their order records) that purchased a specified product. This report can include as much data from the customers and orders table as you need via the relationship that links these tables to Order Line Items.

          To recap the suggestion I made in an earlier post:

          1) Perform a find on customers (or orders) to find all records where a given product was purchased. A date range referencing a date field in orders can be included if that is required.

          2) From the layout and find where this is performe,d (either Customers or Orders ) use Go To Related records with the Match Found Set option to pull up a found set of Order Line Items records. Sort them and use sub summary layout parts, etc to produce a report on all products purchased by this set of customers. If necessary, the found set thus produced can be constrained to further reduce the specific records included in this report.

          - I've spent a lot of time researching table occurrences trying to build in some parameters to "hard code" the found set query

          That will be very frustrating for you as table occurrences are not designed to support such a thing. Closest thing to that would be a portal to a related table with a portal filter being used to filter down the related records to a smaller set of records actually displayed in the porta.

          • 2. Re: Analyze customers that have purchased a specific product

            I am SURE it is my implementation that's faulty Laughing

            I tried what you suggested by I think I am running reports on the wrong tables.

            Should I be basing my report off of the order line items table?

            Because now I have a layout that looks at customers with portals that drill down to orders, products, and titles. I can search for a title in the title portal and get a lfound set of customers that own that title. If I GTRR to my titles table that counts and summarizes customers that own titles, it currently is still showing me counts for the whole table of customers.

            I thought this was because these two layouts were based on two different tables, that was why it was not working.

            But it sounds like perhaps I'm fundamentaly flawed in how I'm trying to look at my data.

            Thanks,

            Jason

            • 3. Re: Analyze customers that have purchased a specific product

              To clarify, the order line items table is really just a join table with Order ID, ProductID, quantity, sold price. I tried building a report and summarizing the data in there and just get a bunch of 1's for counting customers, I guess because one line item is associated with one customer. One line item may be associated with a few titles.

              • 4. Re: Analyze customers that have purchased a specific product
                philmodjunk

                Should I be basing my report off of the order line items table?

                Yes, this is the table on which to base your report if you need info that lists specific items purchased plus data from orders and customers.

                How your structure this layout and employ summary fields--using sub summary layout parts as needed--depends on how you want your report to look and what counts, totals, averages, etc you need to see in it.

                Please ouline what you want to see in your report and then I can suggest how to set up your report layout to get the counts and totals that you want.

                the order line items table is really just a join table with Order ID, ProductID, quantity, sold price.

                That is the typical design for an invoicing or order system and is exactly how I interpreted your screen shot of Manage | database when I posted my previous response.

                • 5. Re: Analyze customers that have purchased a specific product

                  Ok, thanks. So again, products contain multiple titles, and I'm interesting in analyzing title ownership. Many products are "bundles" of titles.

                  So I want to look at an individual title, and then see below that how many customers own each of all the other titles we offer.

                  So it might look like:

                  Title: Star Wars 10,000 customers own

                  ->Empire Stikes Back 2,000

                  ->Star Wars 10,000

                  ->ET 1,000

                  Title: Empire Stikes Back 5,000 customers own

                  ->Empire Stikes Back 5,000

                  ->Star Wars 1,000

                  -> ET 2,000

                   

                   

                  Right now, if i want to do a basic report just counting number of customers that own each title, I have to do it from a report based on the titles table, using a subsummary by title name and a summary field doing a count of my primary key in my customers table. This allows each customer to be counted more than once. I get accurate counts this way, but I have no way of saying "just count customers that own a specific title" (which ever one I want to analyze). As i said earlier, if I can figure out the fundamental mechanics, I can figure out how to change that with a script or multiple fields or whatever.

                   

                   

                  If I try to go the other direction in my relationship graph, from customers to titles, with the same subsummary and counting fields but the report based on my customers table, each customer only gets counted once, according to the first title they purchased.

                  I'm trying to create a report based on the order line items table, but a line item deals with a product, which can deal with many titles. I'm having this recurring problem where I'm only getting back one title as a related record.

                   

                  Jason

                  • 6. Re: Analyze customers that have purchased a specific product
                    philmodjunk

                    I don't think you have understood my suggestion.

                    1) Find all customers who purchased star wars on the customers table--this gives you one record for every customer--not what you want, but it gets us to the found set we need for your report. YOu perform a find on either the customers or the orders table and this works. I suggest orders as it may execute a bit more quickly.

                    2) Use go to related records with the match found set option to pull up a found set of ALL titles purchased for All customers (or their orders) found in step 1.

                    3) then you sort the records by title to get a count of every title purchased by any of the customers that purchased "star wars".

                    • 7. Re: Analyze customers that have purchased a specific product

                      Yes but GTRR needs a report or layout based on the same table to work, right?

                       

                      As I was trying to say, my found set of customers who have purchased a title is based on the customers table. When I go to related records, it wants me to choose a layout or report based on the same table. It won't let me choose a report based on the titles tabel, which is what i need for accurate counts. Otherwise, i keep running into the problem of customers only being counted once.

                       

                      Jason

                      • 8. Re: Analyze customers that have purchased a specific product

                        I follow your suggestion and i get a found count in customers, use a button that go to related record

                         

                        get related records from titles

                        show record using layout "Titles customer ownership" (a report based on the titles table, with subsummary part displaying title name and a count of customers from the customer table).

                        result options: show only related records, match all records in current found set

                        And it gives me counts for everyone in customer table, just like if I ran the report without the found set and button and everything.

                         

                        Jason

                         

                         

                        • 9. Re: Analyze customers that have purchased a specific product
                          philmodjunk

                          OK, I see you have an added complication that one line item can list multiple titles. Use GTRR to go to a layout based on the Join table: ProdTitles--not titles.

                          • 10. Re: Analyze customers that have purchased a specific product

                            But still, the problem is I'm taking a found set of customer records, finding a related set of records in a title table, which will give me a set of title records which are owned by that group, but when the report "loads" and pulls information, it's sorting and counting the "full" table of customer information (albeit just for the titles that are owned by that found set from earlier). At least that's the way I understand it.

                            If I could find a way to take this title report, that uses one subsummary part, a title name field, and a summary count field to give me a count of customers that own each title, and say "only run it against customers that own X title." I even have a field in the customer table that says "I own this title."  

                            Can you think of another way I could accomplish this?

                             

                            • 11. Re: Analyze customers that have purchased a specific product
                              philmodjunk

                              What I have suggested should work, but the layout must be based on ProdTitles, not Titles. This gives you one record for each title purchased by one of the customers found in step 1. Summary fields that count the titles sold should also be defined in this same table.

                              • 12. Re: Analyze customers that have purchased a specific product

                                I wanted to give you a look at the database record counts to see if that could help you determine which table to use? Because prodtitles has only 296 records: it is more of an informational join table, not a table that records transactions. 

                                In other words, it contains the information about what products contain which titles. The things we sell (our products) are DVDs, and sometimes we sell individual DVDs, and sometimes we sell a bundle of them, like a four or 10 pack. So a product is made up of titles

                                Thanks!

                                • 13. Re: Analyze customers that have purchased a specific product
                                  philmodjunk

                                  Hmmm, Sorry, but I definitely was not analyzing correctly here. Embarassed

                                  You can easily get a break down by items ordered, but you can't easily make a further break down by book title due to the way you have structured your tables to handle "boxed set" purchases....

                                  You really can't list all titles purchased as such a table of records doesn't actually exist.

                                  If you flip back through my purchases, I made the comment that you might want to modify your invoicing system so that when a boxed set is purchased, a script adds the individual titles from the boxed set as individual line items. These can have a zero unit price--assuming a boxed set price--so as not to affect the total order, but then you could get the report you wanted from a layout based on OrderLineItems.

                                  The alternative approach that I can think of is to use a looping script that generates one record for each title purchased in a separate table so that you can then base a report on it--which is basically the same approach, but keeps the added records out of lineitems so as not to affect your current invoicing practices.