Acquia CDP

Using Custom Calculations

This page provides information about how to use custom calculations for the following scenario:

Data analysts want to create data calculations to enhance the marketing strategy. The focus is on creating custom calculations to track customer returns over the past 15 days and save this data as scores to improve marketing decisions. The aim is to apply insights to Campaigns, Metrics, and 360 Profiles.

Checklist for using custom calculations

Complete the following tasks to use custom calculations for the given scenario:

S.No.TaskAdditional information
1Create a custom calculation fileCreating a custom calculation file
2Design the layout the custom calculation fileDesigning the layout the custom calculation file
3Construct the execution layout using MarkdownConstructing the execution layout using Markdown
4Add execution code for Markdown stepsAdding execution code for Markdown steps
5Verify the newly added custom attributeVerifying the newly added custom attribute
6Schedule calculation executionScheduling calculation execution
7Python commandsEssential Python commands

Creating a custom calculation file

  1. Sign in to your CDP user interface.
  2. Click CDP Studio.

    The CDP Studio landing page only displays the features that you can access.

  3. Click Launch Custom Calculations.

    The application displays the Jupyter notebook landing page.

  4. Click the New dropdown menu and select Folder.

  5. Click the checkbox to select the folder. 

  6. Click Rename and change the name from Untitled Folder to All_Custom_Cals_Test.

  7. Click All_Custom_Cals_Test.

  8. Click the New dropdown menu and select cdp-studio.

    The application creates the Untitled.ipynb calculation file on the Jupyter notebook landing page and opens it in a new tab.

  9. Click Untitled.ipynb and change the name from Untitled.ipynb to custom_calculations_return_order_calculation_sample.ipynb.

Designing the layout the custom calculation file

  1. In the custom calculation file, click the default cell and change it from Code to Markdown

  1. Copy the following content in the cell:

    # Return Orders Calculation
    In this notebook we will go through an example of how to use the CustomCalculationsClient to perform a simple custom calculation for return orders in last 15 days.
    ## Description
    The goal of this notebook is to compute the number of **return orders per customer in the last 15 days** using CDP Customer data, and **store the scores** in CDP for further integrations.
  2. Verify that the content is displayed as follows:

  3. Click Run or use the keyboard shortcut Shift+Enter to run the cell.

    The application displays the content in Markdown and also displays a new cell.

  4. Double-click the cell to edit its content.

Constructing the execution layout using Markdown

  • Using Markdown to create descriptive cells helps organize your notebook with a clear, structured layout. This improves visual clarity and makes each step easier to understand.
  • Select Markdown to convert the cell into a text box.

To perform custom calculations, follow these steps and execute the given text at each stage:

  1. Create Project: Begin by initializing the SDK and create a new project in the data model for your calculation. Execute the following command:

    ### STEP 1 - Import the CustomCalculationsClient and libraries <a class="anchor" id="step-1"></a>
    This step involves importing the A1 `CustomCalculationsClient` and initializing it.
  2. Write SQL Calculation: Write the SQL statement, execute the query to retrieve the results, and verify they align with the business logic.

    ### STEP 2 - Prepare the SQL query and retrieve the data <a class="anchor" id="step-2"></a>
    This step involves preparation of SQL query and it's execution to retrieve the desired data from CDP.
  3. Save and Write SQL Results: After verifying the SQL results, save and integrate them into the Snowflake data model for the new calculation project.

    ### STEP 3 - Saving scores to the CDP<a class="anchor" id="step-3"></a>
    In this step, we save the dataframe of scores back to the CDP.
  4. Publish in CDP UI: While performing step 3, step 4 automates the publishing process, distributing your custom calculation to Actions, Metrics, and 360 Profiles for integration.

    ### STEP 4 - Surface Scored Data to the CDP<a class="anchor" id="step-4"></a>
    * The model scores will still be surfaced to all modules of the application after the CDP pipeline refresh is complete.
  5. CDP UI Display: Finalize a display name and description for your calculation, which clarifies the purpose and benefits for users managing campaigns.

Cells before execution

Cells after execution

  1. Schedule Calculation Execution: Set your custom calculation to synchronize and run daily, ensuring consistent data updates with the latest information from your orchestration pipeline.

Adding execution code for Markdown steps

Insert a code for each step. To execute a cell, click Run or press Shift+Enter, and proceed in sequence from steps 1 to 5.


Select Code to convert the cell into a code environment.

  1. Create a code cell and enter the following Python code to import libraries:

    from a1mlclient import CustomCalculationsClient
    client = CustomCalculationsClient(project_name="return-orders-calculation")

    Import Libraries

  1. Create a code cell and enter the following SQL query:

    query = '''
      COUNT(*) AS SCORE, 
        WHEN SCORE > 15 THEN 'HIGH'
        ELSE 'LOW' 
    df = client.query_data(query)

    SQL Query Execution 

  1. Create a code cell and enter the following code to save data:

    # Write the data to CDP
    client.write_output_data(df = df, description = 'ML Studio Simple Custom Calculation')

    Saving Scores to CDP

  1. Code is not required to surface CDP data. This step is automated.    

  2. Create a code cell and add the following code:

        display_name="ML Studio Custom Calculation Demo",
        description="ML Studio Custom Calculation Demo Description"

    Update Display Names for Surfaced Models in CDP

Verifying the newly added custom attribute

  1. Log in to the CDP user interface and create a new campaign

  2. In Include customers who, click the Add Group icon.
    The system displays the Add Rule page.
  3. Search the ML Studio Custom Calculation Sample filter.

    You can see the newly added custom attribute ML Studio Custom Calculation Sample calculation filter.

Scheduling calculation execution

  1. Create a new markdown cell and execute the following step:

    ### Step 7(Optional): Schedule Calculation ExecutionCreate a New Code Cell.
  2. Create a new code cell and execute the following step:

    notebook_path="All Custom Cals Test/custom_calculations_return_order_calculation_sample.ipynb",

 Frequency Setting: Choose from daily, weekly, or monthly intervals.

 Time Setting: Specify the time in 24-hour HH:MM format, UTC (e.g., 18:00 = 6 PM UTC).

 Notebook Path Setting: Define the path for the notebook to execute as per your chosen frequency and time.

 Schedule Name Setting: Assign a name to your schedule for easy identification


Locate the notebook path parameter after notebooks/ in your browser's URL. Copy and paste the value All_Custom_Cals_Test/custom_calculations_return_order_calculation_sample.ipynb into the notebook_path field of the Python object.

  1. Insert and execute the following code in a new code cell to stop the schedule:

    #delete the schedule
    client.delete_schedule(notebook_path = 'All_Custom_Cals_Test/custom_calculations_return_order_calculation_sample.ipynb')
    client.delete_schedule(schedule_name = "sample_return_order_calculation_schedule")
  1. Add and execute the following code in a new code cell to confirm the schedule removal:

    #check schedule
  1. The following message indicates that the schedule has been removed:


Essential Python commands

For more Python command functions, see Custom Calculations Developer Documentation.

Reading current calculation output

To examine the output data, use the following command to load the current calculation results into a DataFrame for inspection:

#Reading Current Calculation Output
read_df = client.read_output_data()


Listing available tables

To identify available tables for SQL queries, execute the command below. It returns a DataFrame listing all accessible tables:

#Listing Available Tables
tbl_df = client.list_available_tables()


Viewing Projects in Use

You can view a list of active projects and retrieve the project names using the following command. This helps you understand the scope and potential project interactions:

#Viewing Projects in Use
proj_names = client.list_project_names()


Checking Project Metadata

To gain insights into a specific project and check for previous outputs and their metadata, initialize a CustomCalculationsClient with the project name and use describe_project() to view its metadata.

#Checking Project Metadata
Client_view = CustomCalculationsClient(project_name="YourProjectName")
proj_metadata = Client_view.describe_project()

Replace "YourProjectName" with your project's actual name to better understand its structure and computational history.