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: TO_CHAR(timesummary.id)=TO_CHAR(transactionsummary.transactiontimestamp, ‘YYYYMMDD’) TO_CHAR(timesummary.id)=TO_CHAR(transactionsummary.shipdate, ‘YYYYMMDD’) TO_CHAR(timesummary.id)=TO_CHAR(customersummary.FirstTransactionDate, ‘YYYYMMDD’) TO_CHAR(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.	-	-

Acquia Help

Filter by product:

Interactive Queries common questions