Acquia CDP

Custom Calculations

The Custom Calculations feature improves data-driven decision-making with new patterns and insights. It connects with main features such as Action, Analytics, and 360 Profile for marketers. To use this feature effectively, you must have SQL and Python skills.

Features¶

Flexibility in calculation: Add new custom calculations to the existing data calculation standards. This enhancement broadens business case applicability to offer a complete solution for various requirements.
Notebook-based framework: Simplify adoption with an easy-to-use Jupyter notebook framework.
This method uses the following:
- SQL queries to fit the scoring model and achieve the required output
- Python commands for quick learning of users
Streamlined deployment: Get a streamlined process that offers a clear, UI-guided, step-by-step pathway for creating queries, reviewing results, and deploying data in real time across key features such as Campaign+, Metrics, and 360 Profiles. This process is streamlined from concept to execution.
Accelerated learning curve: Simplify the adoption process with pre-built demo notebooks, covering basic to advanced custom calculation scenarios to highlight possibilities and inspire user innovation.

Capabilities:¶

Data analysts and scientists can:

Create simple to complex custom calculations in CDP.
- Beginners start with basic analyses such as counting customers' discounted return orders from the past week.
- Advanced users calculate complex metrics such as RFM scores for each customer.
Create or update custom calculations for customer segmentation in campaigns. Rapid code iteration facilitates quick data adaptation and refinement using real-time insights.
Identify KPI opportunities and generate relevant custom calculations. These insights refine marketing strategies, act as dynamic filters for campaign execution, and provide key metrics for reporting. Additionally, they enhance 360 Profiles by providing a deeper understanding of customer behavior and preferences.
Create or update custom calculations and adjust scoring views or data for customer segmentation within campaigns. This rapid cycle of code updates enables quick data adaptation and refinement, using insights to improve results.

Important considerations¶

SQL scope of calculations: Custom calculations are supported only at the master-customer level.
Data type display: Currently, only string columns are displayed. While decimal data types can be calculated, their self-service display is not yet supported.
Expansion of view tables: Acquia does not recommend you to expand the available tables. For exceptions, create a Support ticket. Each request is assessed for feasibility.
Scheduler frequency: The scheduler for calculation refreshes is currently set to operate daily.
Scheduling execution: The scheduler that refreshes more than five to six custom calculations simultaneously might result in delays or other performance issues. For optimized performance, you must keep a gap of 8-10 minutes between the refreshes.
Hardcode name: The system assigns a non-editable hardcoded name to each custom calculation. When you use Interactive Queries or Snowflake data share, the column containing your new calculation values adheres to the standard hardcoded naming convention under ML_OUT_MC_SUMMARY.

Available Entities¶

Entity Name	Join ID
ADDRESS	`customeraddressxref.addressid = address.id`
CUSTOMER	`customer.id = mastercustomer.customerid` `customer.id = transactionsummary.customerid` `customer.id = event.customerid` `customer.id = customeraddressxref.customerid`
CUSTOMERADDRESSXREF	`customeraddressxref.sourcecustomernumber = customer.customerid`
CUSTOMERSUMMARY	`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`
DISPATCH	`dispatch.id = event.messageid`
EVENT	`event.customerid = customer.id` `event.customerid = mastercustomer.customerid` `event.productid = productsummary.id` `event.productcategoryid = productcategorysummary.id` `event.transactionid = transactionsummary.transactionid`
MASTERCUSTOMER	`mastercustomer.mastercustomerid = customersummary.mastercustomerid` `mastercustomer.customerid = customer.id`
MESSAGE	`message.id = event.messageid`
ML_OUT_MC_SUMMARY	`ml_out_mc_summary.mastercustomerid = CustomerSummary.mastercustomerid`
ORGANIZATION	`transactionsummary.organization_id = organization.id`
ORGANIZATIONSUMMARY	`transactionsummary.organization_id = organizationsummary.id`
PAYMENTMETHOD	`transactionpaymentmethodxref.paymentmethodid = paymentmethod.Id`
PRODUCT	`product.id = transactionitem.productid` `product.id = transactionsummary.productid`
PRODUCTCATEGORY	`productcategory.id = productcategoryxref.productcategoryid` `productcategory.id = customerfirsttransactionproductcategory.productcategoryid` `productcategory.id = customerlasttransactionproductcategory.productcategoryid` `productcategory.id = event.productcategoryid`
PRODUCTCATEGORYSUMMARY	`productcategorysummary.id = productcategoryxref.productcategoryid` `productcategorysummary.id = customerfirsttransactionproductcategory.productcategoryid` `productcategorysummary.id = customerlasttransactionproductcategory.productcategoryid` `productcategorysummary.id = event.productcategoryid`
PRODUCTCATEGORYXREF	`productcategoryxref.productid = productsummary.id` `productcategoryxref.productcategoryid = productcategorysummary.id`
PRODUCTSUMMARY	`productsummary.id = transactionsummary.productid`
PROMOTIONTYPESUMMARY	`promotiontypesummary.id = event.messageid`
TIMESUMMARY	Any `datetime` attribute can be joined into the `timesummary` table to find the corresponding fiscal calendar date. For example, `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’)`
TRANSACTION	`transaction.customerid = customer.id`
TRANSACTIONSUMMARY	`transactionsummary.customerid = customer.id` `transactionsummary.mastercustomerid = customersummary.mastercustomerid` `transactionsummary.organization_id = organization.id` `transactionsummary.organization_id = organizationsummary.id` `transactionsummary.productid = productsummary.id`
TRANSACTIONITEM	`transactionitem.productid = product.id` `transactionitem.organizationid = organization.id`
TRANSACTIONPAYMENTMETHODXREF	`transactionpaymentmethodxref.paymentmethodid = paymentmethod.Id`

Acquia Help

Filter by product:

Acquia CDP common questions

If you can't find what you're looking for, try refining your search or contact our Support teamfor further assistance.