Information for: DEVELOPERS   PARTNERS

Table Information

Table Name Table Description Primary Key JOIN Relationships
customersummary Individual master-customer records after Customer Data Platform (CDP)’s de-duping of raw child-customers. mastercustomerid
  • customersummary.mastercustomerid=transactionsummary.mastercustomerid
  • customersummary.mastercustomerid=mastercustomer.mastercustomerid
  • customersummary.mastercustomerid=customerfirsttransactionproductcategory.mastercustomerid
  • customersummary.mastercustomerid=customerlasttransactionproductcategory.mastercustomerid
  • customersummary.FirstPromoID=promotiontypesummary.id
  • customersummary.FirstOfflinePromoID=promotiontypesummary.id
  • customersummary.closeststoreid=organizationsummary.id
  • customersummary.primarystoreid=organizationsummary.id
transactionsummary Transaction records at the line-level across all sales channels. id
  • transactionsummary.mastercustomerid=customersummary.mastercustomerid
  • transactionsummary.mastercustomerid=mastercustomer.mastercustomerid
  • transactionsummary.customerid=customer.id
  • transactionsummary.productid=productsummary.id
  • transactionsummary.organizationid=organizationsummary.id
  • transactionsummary.TransactionLastMarketingTouchID=promotiontypesummary.id
  • transactionsummary.TransactionLastOfflineMarketingTouchID=promotiontypesummary.id
  • transactionsummary.TransactionLastOfflineMarketingTouchIDHousehold=promotiontypesummary.id
  • transactionsummary.transactionid=event.transactionid
productsummary List of products. id
  • productsummary.id=transactionsummary.productid
  • productsummary.id=productcategoryxref.productid
  • productsummary.id=productsummary.ParentProductID
  • productsummary.id=event.productid
productcategorysummary List of product categories. id
  • productcategorysummary.id=productcategoryxref.productcategoryid
  • productcategorysummary.id=customerfirsttransactionproductcategory.productcategoryid
  • productcategorysummary.id=customerlasttransactionproductcategory.productcategoryid
  • productcategorysummary.id=event.productcategoryid
productcategoryxref Cross-reference between products and product categories. id
  • productcategoryxref.productid=productsummary.id
  • productcategoryxref.productcategoryid=productcategorysummary.id
customerfirsttransactionproductcategory Cross-reference between master-customers and their first transaction product categories. id
  • customerfirsttransactionproductcategory.productcategoryid=productcategorysummary.id
  • customerfirsttransactionproductcategory.mastercustomerid=customersummary.mastercustomerid
customerlasttransactionproductcategory Cross-reference between master-customers and their last transaction product categories. id
  • customerlasttransactionproductcategory.productcategoryid=productcategorysummary.id
  • customerlasttransactionproductcategory.mastercustomerid=customersummary.mastercustomerid
organizationsummary List of sales organizations and stores. id
  • organizationsummary.id=transactionsummary.organizationid
  • organizationsummary.id=customersummary.closeststoreid
  • organizationsummary.id=customersummary.primarystoreid
event List of events such as emailOpens, web productBrowsed or custom events for your tenant. id
  • event.customerid=customer.id
  • event.customerid=mastercustomer.customerid
  • event.productid=productsummary.id
  • event.productcategoryid=productcategorysummary.id
  • event.transactionid=transactionsummary.transactionid
mastercustomer Cross-reference between child-customer and master-customer records. Multiple child-customer records could roll up to one master-customer record id or customerid
  • mastercustomer.mastercustomerid=customersummary.mastercustomerid
  • mastercustomer.customerid=customer.id
promotiontypesummary List of online/offline marketing touches or promotions that influenced transactions. id
  • promotiontypesummary.id=customersummary.FirstPromoID
  • promotiontypesummary.id=customersummary.FirstOfflinePromoID
  • promotiontypesummary.id=transactionsummary.TransactionLastMarketingTouchID
  • promotiontypesummary.id=transactionsummary.TransactionLastOfflineMarketingTouchID
  • promotiontypesummary.id=transactionsummary.TransactionLastOfflineMarketingTouchIDHousehold
timesummary Fiscal calendar mapping for each calendar date. id (calendar day in YYYYMMDD format)

Any datetime attribute can be joined into the timesummary table to find the corresponding fiscal calendar date. Here are a few examples of how this can be achieved:

  • timesummary.id=TO_CHAR(transactionsummary.transactiontimestamp, ‘YYYYMMDD’)
  • timesummary.id=TO_CHAR(transactionsummary.shipdate, ‘YYYYMMDD’)
  • timesummary.id=TO_CHAR(customersummary.FirstTransactionDate, ‘YYYYMMDD’)
  • timesummary.id=TO_CHAR(customersummary.LastTransactionDate, ‘YYYYMMDD’)
customer (optional) Individual child-customer records before CDP’s de-duping. id
  • customer.id=mastercustomer.customerid
  • customer.id=transactionsummary.customerid
  • customer.id=event.customerid
  • customer.id=customeraddressxref.customerid
address (optional) List of address records for child-customers. id
  • address.id=customeraddressxref.addressid
customeraddressxref (optional) Cross-reference between child-customers and their addresses. id
  • customeraddressxref.customerid=customer.id
  • customeraddressxref.addressid=address.id
audiencehistory (optional) Cross-reference between cohorts and master-customers used to perform Cohort Analysis. ID
  • customersummary.mastercustomerid = mastercustomer.mastercustomerid
  • mastercustomer.customerid = audiencehistory.mastercustomerid
any client-specific entities (optional) Any client-specific tables configured for your account. - -