Interactive Queries

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.

-

-