Efficient schema supporting upsell features of a web-based business application

In a database server supporting multiple enterprises served by a web-based business application, each enterprise having a plurality of customers to which one or more items is sold, a method for providing upsell information, comprising:

• maintaining a first table summarizing, for each enterprise and each item sold by said enterprise, a first count of customers of said enterprise having purchased that item;
• maintaining a second table summarizing, for each enterprise and each pairwise combination of items sold by said enterprise, a second count of customers of said enterprise having purchased both of said items;
• receiving a request for a plurality of pairwise, customerwise correlation metrics between an upsell item sold by an identified one of said enterprises and each of the other items sold by said identified enterprise;
• and
• computing, responsive to said request and in real time, said plurality of pairwise, customerwise correlation metrics using said first and second tables.

A method for facilitating upselling in a web-based business application used by a plurality of enterprises, each enterprise having a plurality of customers, each enterprise executing one or more transactions with each customer in which one or more items is sold, comprising:

• receiving a client input for each of said transactions, said client input communicating an enterprise ID and a customer ID associated with each transaction, said client input further communicating, for each item sold in each transaction, an item ID;
• storing said enterprise ID, said customer ID, and a transaction key reference in a single record of a first table of a database, said database being common to at least two of said enterprises including the enterprise associated with said enterprise ID;
• storing said transaction key reference and each of said item IDs for each transaction across a number of records of a second table of said database corresponding to a number of items sold in that transaction;
• processing, at predetermined intervals, said first and second tables of said database to compute third and fourth tables thereof, said third table summarizing, for each enterprise and for each item sold by that enterprise, a first count of customers having purchased that item from that enterprise, said fourth table summarizing, for each enterprise and each possible pairing of items sold by that enterprise, a second count of customers having purchased both such items from that enterprise;
• receiving a first upsell query identifying a first enterprise associated with said database and identifying an upsell item;
• responsive to said first upsell query, computing a plurality of pairwise, customerwise correlation metrics between said upsell item and each other item sold by said first enterprise, said computing being performed in real time using said third and fourth tables of said database previously computed at said predetermined intervals.

A method for facilitating upselling in a web-based business application used by an enterprise, the enterprise having a plurality of customers and a plurality of items, the enterprise further having a plurality of item groups into which at least one of said items is classified, the enterprise executing one or more transactions with each customer in which one or more of the items is sold, comprising:

• receiving a client input for each of said transactions, said client input communicating a customer ID associated with each transaction, said client input further communicating, for each item sold in each transaction, an item ID;
• storing said customer ID and a transaction key reference in a single record of a first table of a database for each of said transactions;
• storing one or more records in a second table of said database for each of said transactions, said second table comprising a transaction key field and an item/group field, said one or more records including, for each item sold in said transaction, (i) a first record containing said transaction key reference in said transaction key field and said item ID in said item/group field, and (ii) if said item belongs to one of said item groups, a second record containing said transaction key reference in said transaction key field and a group ID corresponding to said item group in said item/group field;
• processing, at predetermined intervals, said first and second tables of said database to compute third and fourth tables thereof, wherein said third table summarizes (i) for each item sold, a first count of customers having purchased that item, and (ii) for each item group, a second count of customers having purchased an item from said item group, and wherein said fourth table summarizes, for each appropriate pairing of said items and item groups with each other, a third count of customers having purchased from both members of said pairing;
• receiving a user upsell query identifying an upsell item or an upsell item group;
• and
• responsive to said user upsell query, computing a plurality of pairwise, customerwise correlation metrics between each appropriate pairing of said upsell item or upsell item group with each other item and item group, said computing being performed in real time using said third and fourth tables of said database previously computed at said predetermined intervals.