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:

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. 

2. 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.

3. Verify that the content is displayed as follows:

   

4. 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.

   

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

Constructing the execution layout using Markdown¶

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¶

6. 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.

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

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

   query = '''
   SELECT 
     MASTERCUSTOMERID,
     COUNT(*) AS SCORE, 
     CASE 
       WHEN SCORE > 15 THEN 'HIGH'
       WHEN SCORE BETWEEN 10 AND 15 THEN 'MEDIUM' 
       ELSE 'LOW' 
     END AS VALUE_NAME
   FROM STUDIO_TRANSACTIONSUMMARY
   WHERE SUBTYPE = 'Returned' AND TRANSACTIONTIMESTAMP >= DATEADD(day, -15, GETDATE())
   GROUP BY MASTERCUSTOMERID ORDER BY SCORE DESC;
   '''
   df = client.query_data(query)
   df.head(25)    

   SQL Query Execution 

3. 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

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

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

   client.update_output_name(
       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:

client.add_schedule(
    frequency="daily",
    time="10:00",
    notebook_path="All Custom Cals Test/custom_calculations_return_order_calculation_sample.ipynb",
    schedule_name="sample_return_order_calculation_schedule"
)

 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

3. 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")

4. Add and execute the following code in a new code cell to confirm the schedule removal:

   #check schedule
   client.describe_schedule(schedule_name="sample_return_order_calculation_schedule")

5. 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()
print(read_df)

 ¶

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()
print(tbl_df)

 ¶

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()
print(proj_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()
print(proj_metadata)

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