Analyze customers that have purchased a specific product

[Jason C. Howlin]

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

[comment]

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

I am somewhat hazy regarding the exact form your report should take. Perhaps an example would help.

[Jason C. Howlin]

Certainly!

[comment]

Is this a correct interpretation of your example:

There are 10,000 customers that own ALL products of the title "Oi Gong for Seniors". Within this group of 10,000 customers, there are 1,000 customers that also purchased ALL products of the title "Oi Gong for Beginners."

[Jason C. Howlin]

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.

But yes, i see what you are saying. I think if you change the above ALL to ANY it would be correct. Because they could own the title through a variety of products: a product that contains just that title, or a number of different products that are "bundles" that contain that title.

[comment]

I think if you change the above ALL to ANY it would be correct.

I'm still a bit confused regarding what exactly do those numbers mean, though. For each title, you can tell how many customers had bought it by looking at =

Count ( Customers::CustomerID )

from the context of Titles (this is the number of distinct customers, so if someone bought the same title twice, they will be counted only once).

If, starting from a selected title, you do two GTRR moves in a series:

Go to Related Record [ from Customers; Show related only; Match current record ]

Go to Related Record [ from Titles; Show related only; Match found set ]

you'll end up with a list of all titles bought by the customers that bought the selected title. No quantities at this point, though.

Customers -< Orders -< LineItems >- Products -< ProdTitles >- Titles

[Jason C. Howlin]

Ok, as to what they mean...

In the mock up report, take the second title being analyzed:

For "Qi Gong for Beginners" 5,000 (out of 35,000) of my customers own that title. So over time they've purchased some product that contains that title.

Now, of those 5,000 they also own the following titles:

Qi Gong for Seniors 4,000

Qi Gong for Beginners 5,000 (this will always be 100% of the sub-group of customers I'm analyzing here)

Qi Gong for Deep Sleep 1,000

Qi Gong for Stress 1,000

Qi Gong for Kids 200

So being able to see this data would allow me to see that, based on purchase history of my customer base, customers that own Qi Gong for Beginners are also VERY LIKELY to own Qi Gong for Seniors. They are NOT very likely to own Qi Gong for Kids.

This would allow me to perhaps tailor a segmented promotion marketing "Qi Gong for Seniors" to the 1000 customers that own Qi Gong for Beginners but don't already own Qi Gong for Seniors.

And I can perform the finds and include and omit and constrain found sets and all of that on my customer table. But it is getting this insight that is proving to be the difficult part.

I'm going to try your suggestions now and will report back shortly!

Thanks

[Jason C. Howlin]

From tables, count (customers:customer id) works, and it seems to provide the same results as what I've been doing, which is to use a summary field in customers that counts ids and place it next to a title name in a summary part when sorted by title name on a report layout based on the title table.

Wish I knew what the "best practice" was in this case, or does it make a difference?

Now it seems your two GTRRs are producing the desired results of titles! But as you said, the numbers... I have to try and wrap my head around what sets of records this GTRR is passing... Is GTRR something that can be replicated manually?

[Jason C. Howlin]

I feel like if I could set up a table occurrence where the relationship was predicated on owning a particular title I want to analyze, that would prevent all customers from coming through and being included in my counts.

But the problem is I need there to be a fully functioning relationship there to see if the customer owns the title!

I mean, am I crazy or is there something fundamentally flawed here!