Share via

Prepare input data for Frequently bought together

  • Article

The Frequently bought together model is a part of the Microsoft Cloud Retail solution. It helps you identify product associations and cross-selling opportunities based on customer transactions. To run the model, you need to provide some input data that follows the Retail Data Model schema.

The input data for the model is derived from the Retail Data Model schema available of Azure Synapse Analytics.

Required tables for notebook execution

Some tables and fields are mandatory for the proper execution of the model. As a part of the deployment, we prepopulate the input tables with sample data to ensure a smooth customer onboarding experience. Retailers can also customize and point the notebook into a different Lakehouse or update the existing tables.

Note

The input tables / entities are expected to be in the Delta format. Parquet format isn’t supported.

The following tables are required for the model to run:

TransactionLineItem

Description: The components of a Transaction broken down by Product and Quantity, one-per-line item.

Required fields:

  • TransactionLineItemId – PK, LongType
  • TransactionId, FK to Transaction, LongType
  • ProductId, FK to RetailProduct, LongType
  • TransactionLineItemCompletedTimestamp, TimestampType
  • TransactionLineItemTypeID, FK to TransactionLineItemType, IntegerType
  • Quantity, DecimalType
  • ProductListPriceAmount, DecimalType
  • IsoCurrencyCode, FK to Currency, StringType

Transaction

Description: The lowest level of executable work or customer activity.

Required fields:

  • TransactionId, PK, LongType
  • StoreId, FK to Store, IntegerType
  • OrderId, FK to Order, LongType

TransactionLineItemType

Description: The type of Transaction Line Item. A record with TransactionLineItemTypeName="purchase" is expected here.

Required fields:

  • TransactionLineItemTypeID, PK, IntegerType
  • TransactionLineItemTypeName, StringType

Store

Description: A retail/channel location where products, goods, and services are sold to consumers.

Required fields:

  • StoreId, PK, IntegerType
  • OperatedbyPartyId, FK to Party, LongType

Visit

Description: A visit between two parties. The visiting party can be a party, employee, or customer. The visited party can be a party, channel, or store.

Required fields:

  • VisitId, PK, IntegerType
  • VisitStartTimestamp, TimestampType
  • StoreId, FK to Store, IntegerType

Order

Description: A document or commission by the customer to order products.

Required fields -

  • Ordered, PK, LongType
  • VisitId, FK to visit, IntegerType

Required tables for Power BI report

Store

Description: A retail/channel location where products, goods, and services are sold to consumers.

Required fields:

  • StoreId, PK, IntegerType
  • StoreName, StringType
  • GrossFlorArea, IntegerType
  • LocationId, FK to Location, IntegerType
  • OperatedByPartyId, FK to Party, LongType

Party

Description: A party is an individual, organization, legal entity, social organization, or business unit of interest to the business. Party is a concept that enables individuals, organizations, legal entities, social organization, and business units to be related or defined with the context of roles, events, and relationships.

Required fields-

  • PartyId, PK, LongType
  • PartyName, StringType
  • PartyTypeId, FK to PartyType, IntegerType

PartyType

Description: A categorization of parties based upon common characteristics or similar criteria. It's expected to have one record with PartyTypeName = "Retailer"

Required fields:

  • PartyTypeId, PK, IntegerType

  • PartyTypeName, StringType

Location

Description: A location is a physical point that can be defined as a single latitude / longitude. A location can be used to describe the address of a physical structure, the location of a business or service, the location of a component or the delivery location for a shipment or mail.

Required fields:

  • Locationid, PK, IntegerType
  • LocationAddressLine1, StringType
  • LocationAddressLine2, StringType
  • LocationCity, StringType
  • LocationZipCode, StringType
  • CountryId, FK to Country, IntegerType

Country

Description: The Country definition is based upon ISO 3166 Country Codes.

Required fields -

  • CountryId, PK, IntegerType
  • IsoCountryName, StringType
  • Iso2LetterCountryCode, StringType

Retailer

Description: A merchant whose main business is selling directly to the ultimate consumer.

Required fields:

  • RetailerId, PK, IntegerType
  • RetailerName, StringType
  • LocationId, FK to Location, IntegerType
  • PartyId, FK to Party, LongType

RetailProduct

Description: A product is anything that can be offered to a market that might satisfy the wants or needs of potential customers. That product is the sum of all physical, psychological, symbolic, and service attributes associated with it.

Required fields:

  • ProductId, PK, LongType
  • ProductName, StringType

Currency

Description: The definition of Currency is based on the ISO 4217 standard.

Required fields:

  • IsoCurrencyCode, PK, StringType
  • CurrencyName, StringType
  • CountryId, FK to Country, IntegerType