Using Custom Calculations

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:

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:

   copy

   # 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

Use Markdown to organize your notebook with clear and structured sections for ease of understanding. Select the cell and change it from Code to Markdown. 
To perform custom calculations, follow these steps and execute the given text at each stage:

1. Initialize SDK and create a new project in the data model. 

   copy

   ### 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 the SQL statement and run the query to retrieve the results.

   copy

   ### 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. Verify the SQL results to ensure that they align with the business logic.

4. Save and integrate the results into the Snowflake data model for the new calculation project.

   copy

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

   The system publishes and distributes your custom calculation to Actions, Metrics, and 360 Profiles for integration.

   copy

   ### 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. Provide a display name and description for your calculation.

   Cells before execution:

   

   Cells after execution:

   

6. Set your custom calculation to synchronize and run daily to ensure consistent data updates.

Adding execution code for Markdown steps

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

   copy

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

   Import Libraries

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

   copy

   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:

   copy

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

    Saving Scores to CDP

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

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

   copy

   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.

2. Run the following text:

   copy

   ### Step 7(Optional): Schedule Calculation ExecutionCreate a New Code Cell.

3. Create a new code cell.

4. Run the following code:

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

5. Insert and run the following code in a new code cell to stop the schedule:

   copy

   #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")

6. Insert and run the following code in a new code cell to confirm the schedule removal:

   copy

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

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

You can examine the output data. The following command loads 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

You can identify available tables for SQL queries. The following command 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 identify the scope and potential project interactions. The following command returns a list of active projects and retrieves the project names: 

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

Checking Project Metadata

You can gain insights into a specific project. The following command returns previous outputs and 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 understand its structure and computational history.

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:

   copy

   # 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

Use Markdown to organize your notebook with clear and structured sections for ease of understanding. Select the cell and change it from Code to Markdown. 
To perform custom calculations, follow these steps and execute the given text at each stage:

1. Initialize SDK and create a new project in the data model. 

   copy

   ### 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 the SQL statement and run the query to retrieve the results.

   copy

   ### 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. Verify the SQL results to ensure that they align with the business logic.

4. Save and integrate the results into the Snowflake data model for the new calculation project.

   copy

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

   The system publishes and distributes your custom calculation to Actions, Metrics, and 360 Profiles for integration.

   copy

   ### 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. Provide a display name and description for your calculation.

   Cells before execution:

   

   Cells after execution:

   

6. Set your custom calculation to synchronize and run daily to ensure consistent data updates.

Adding execution code for Markdown steps

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

   copy

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

   Import Libraries

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

   copy

   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:

   copy

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

    Saving Scores to CDP

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

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

   copy

   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.

2. Run the following text:

   copy

   ### Step 7(Optional): Schedule Calculation ExecutionCreate a New Code Cell.

3. Create a new code cell.

4. Run the following code:

 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

5. Insert and run the following code in a new code cell to stop the schedule:

   copy

   #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")

6. Insert and run the following code in a new code cell to confirm the schedule removal:

   copy

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

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

You can examine the output data. The following command loads the current calculation results into a DataFrame for inspection:

Listing available tables

You can identify available tables for SQL queries. The following command returns a DataFrame listing all accessible tables:

Viewing Projects in Use

You can identify the scope and potential project interactions. The following command returns a list of active projects and retrieves the project names: 

Checking Project Metadata

You can gain insights into a specific project. The following command returns previous outputs and metadata:

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