Using automation in your channel program can save time by streamlining otherwise manual processes. Channeltivity's integration with Zapier gives you the tools to do just that, making it easy to automate tasks and create custom workflows in your partner relationship management tool. 

Automating simple tasks within Channeltivity using Zapier is straightforward. For channel management automation tasks that are more complex, Zapier can be the right solution, although care it advised, as they require more advanced techniques. 

This article walks you through creating an advanced Zapier automation that certifies a Partner based on completion of required User certifications.

The techniques used in this example can also be used to implement other automations:

• Automatically changing a Partner's tier when certain milestones are reached (e.g. Closed Won Deal volume)
• Automatically issue MDF funds based on previous quarter's Closed Won Deals

Requirements:

Before you get started, make sure to read this entire guide. Also ensure you have all the prerequisites:

• Channeltivity PRM
• Add-on module: Partner Training & Certification
• An understanding of how Certifications and Certification Completions work within Channeltivity
• Zapier Professional or higher (this is a multi-step Zap)
• Google Sheets (Microsoft O365 works as well)

Note: This is an advanced tutorial designed for experienced Zapier users, developers and sales ops professionals. Our example uses external data sources, pivot tables and code. Zapier is easy to use, but as the complexity of your automation grows, testing and maintaining your Zapier automations requires care and expertise.

Use Case

Our fictitious company, WidgetCloud, requires that Partners have trained employees in order to reach the "Certified Partner" certification status. More specifically, the Partner needs to have:

• At least 2 employees with the "Certified Widget Analyst (CWA)" User Certification 
• At least 2 employees with the "Certified Widget Server Professional (CWSP)" User Certification

WidgetCloud wants to automatically award the "Certified Partner" Organization Certification to a Partner when their employees have the required User Certifications.

Using Pivot Tables to Summarize Data

One of the tricky parts of this Zap is that we will need to count the number User Certifications of the two required types in order to award the "Certified Partner" Certification. Since Zapier doesn't have the ability to summarize data the way we need, we'll rely on Google Sheets to store a copy of all Certification Completions in one worksheet:

Then, within the same Google Sheet, we will use pivot tables on two separate worksheets to calculate the following totals:

1. Number of completed User Certification Completions by Partner Org ID, broken down by User Certification. Shows the count of User Certification Completions for the two User Certification types.
2. Number of completed Organization Certification Completions by Partner Org ID - shows which Partners are currently holding the "Certified Partner" Certification.

Zap Steps Overview

Here's an overview of the steps of the Zap:

1. Trigger - Channeltivity Certification Completion Status Change: The Zap will be triggered whenever a "Certification Completion" changes in Channeltivity. Certification Completions are where a User's or Organization's progress toward completing a Certification is tracked. Certification Completions have a "Status" field that can be "In Progress", "Completed", "Expired" or "Voided". 
2. Channeltivity Find Records - Look up User in Channeltivity: When a User completes a Certification Completion, Channeltivity will provide only the User's ID, but not their Org ID. Since we need to summarize Certifications by Org ID (=Partner), we need to look up the User in Channeltivity to get the Org ID of the attached Partner. If the Zap is triggered by updates to a Organization Certification Completion, this step will fail and be skipped.
3. Code by Zapier - Calculate Org ID and whether to continue: We will use JavaScript to take inputs from the first two Steps to calculate two return variables:
   1. Org ID: Since the Zap can be triggered by changes to either a User or Organization Certification Completion, we don't necessarily know the Org ID. Based on the input data, the code sets the Org ID either from the trigger (Step 1) or the Channeltivity User lookup action (Step 2) as a return variable.
   2. Whether to continue: We only want to check whether to award Org Certifications if a User Certification Completion was completed or a Org Certification Completion expired. A second return variable is set to whether one of these conditions are met.
4. Google Sheets Lookup Spreadsheet Row - Find existing Certification Completion: This steps looks up the row number of any existing Certification Completion in the Google Sheet. If it doesn't find it, we'll store the Certification Completion in a new row.
5. Google Sheets Update Spreadsheet Row - Store Certification Completion changes: In this step, we update the existing Certification Completion in the Google Sheet. There's some redundancy between Step 4 and 5 of the Zap in that if a new row was inserted in Step 4 that the Zap will update the same record in Step 5, but this avoids having to create multiple paths with duplicate steps.
6. Filter by Zapier - Stop if the "whether to continue" variable is false: Now that the Certification Completion changes have been stored in Google Sheets, we only want to continue if the "whether to continue" variable from Step 3 is true. 
7. Google Sheets Lookup Spreadsheet Row - Find existing Org Certification Completion: Since we only want to create a new "Certified Partner" Certification Completion when the Partner doesn't have a valid one, we need to look this up in Google Sheets. Specifically, we're going to match the Org ID to a row in the completed Organization Certification Completions pivot table.
8. Filter by Zapier - Stop if the Partner Org has a valid Certification Completion: If Step 7 returned a successful result, then we don't need to create a new "Certified Partner" Certification Completion and can stop the Zap here.
9. Google Sheets Lookup Spreadsheet Row - Look up count of valid User Certification Completions: This Step looks up the number of User Certification Completions for the selected Partner Org in a Google Sheets pivot table. Similar to Step 7, we're going to match the Org ID of the Partner to a row of the User Certification Completions by Partner Org ID pivot table, returning the number of completed User Certifications for the two required Certification types.
10. Filter by Zapier - Stop if Certification requirements are not met: In this step we make sure that the Partner has at least 2 or more valid Certification Completions for each of the two required Certifications. If not, we'll stop the Zap.
11. Channeltivity Create Record - Create Org Certification Completion: This is the last step, where we create a new Organization Certification Completion for the Partner Org. Please note that this will trigger the Zap again.

Detailed Walkthrough

Once you're familiar with the general way this Zap works (see the Zap Steps Overview section above), use the more detailed instructions in this section to complete your Zap. Since this guide is intended for a more experienced audience, it only covers the key areas and doesn't go into full detail. 

Google Sheets Setup

Certification Completions Source Data: We'll start with setting up Google Sheets, where the aggregating and most of the calculations happen. The first step is importing your current Certification Completion data into a brand new sheet with the following columns:

• Id
• Certification
• User Name
• User ID
• Organization Name
• Org ID
• Completion Date
• Valid Until Date
• Status

Use the export functionality within Channeltivity to download and import all your Certification Completions into Google Sheets:

Next up, we'll create two pivot tables based on the Certification Completion data. For both pivot tables, make sure to extend the data range to beyond the current data. Otherwise, any rows that are added won't be included in calculations. Google Sheets automatically extends the pivot table data range when you insert new rows, so you'll only need to do this once.

User Certs by Org Pivot Table: The first pivot table will show the number of valid User Certification Completions by Org ID:

• Rows: Org ID
• Columns: Certifications
• Values: Count by Id
• Filters:
  • Certification = "Certified Widget Analyst (CWA)" or "Certified Widget Server Professional (CWSP)" 
  • Status = "Completed"
  • Valid Until Date = "Date is after today"

Org Certs by Org Pivot Table: The second pivot table will list Orgs with valid "Certified Partner" Certification Completions:

• Rows: Org ID
• Values: Count by Id
• Filters:
  • Certification = "Certified Partner" 
  • Status = "Completed"
  • Valid Until Date = "Date is after today"
  • Org ID is greater than 0

Make sure to set filters for Certification names on the Pivot Tables. That way, if you ever create more Certification types in Channeltivity, your Zap will continue running.

Trigger (Step 1): Channeltivity Certification Completion Status Change

The Zap will be triggered whenever a "Certification Completion" changes in Channeltivity. Certification Completions are where a User's or Organization's progress toward completing a Certification is tracked. Certification Completions have a "Status" field that can be "In Progress", "Completed", "Expired" or "Voided". 

App: Channeltivity

Event: Certification Completion Status Change

Step 2: Look up User in Channeltivity

When a User completes a Certification Completion, Channeltivity will provide only the User's ID, but not their Org ID. Since we need to summarize Certifications by Org ID (=Partner), we need to look up the User in Channeltivity to get the Org ID of the attached Partner. If the Zap is triggered by updates to a Organization Certification Completion, this step will fail and be skipped.

App: Channeltivity

Event: Find Records

Record Type: User

Field to Search: Key

Search Value: 1. Post Changes User Id

Number of Records to Return: Return first matching record

Should this step be considered a "success" when nothing is found: Yes

Step 3: Calculate Org ID and whether to continue 

We will use JavaScript to take inputs from the first two Steps to calculate two return variables:

1. Org ID: Since the Zap can be triggered by changes to either a User or Organization Certification Completion, we don't necessarily know the Org ID. Based on the input data, the code sets the Org ID either from the trigger (Step 1) or the Channeltivity User lookup action (Step 2) as a return variable.
2. Whether to continue: We only want to check whether to award Org Certifications if a User Certification Completion was completed or a Org Certification Completion expired. A second return variable is set to whether one of these conditions are met.

App: Code by Zapier

Event: Run Javascript

Input Data:

• Input 1:
  • Key: UserID
  • Value: 1. Post Changes User Id
• Input 2: 
  • Key: OrgID
  • Value: 1. Post Changes Organization Id
• Input 3:
  • Key: Status
  • Value: 1. Post Changes Status Name
• Input 4:
  • Key: UsersOrgID
  • Value: 2. Fields Organization Id

Code: 

// This code does two things:
// 1. Since the trigger can be changes to either a User or Organization Certification
//   Completion, we don't necessarily know the Org ID. This code sets outputOrgID 
//   to the Org ID either from the trigger or the Channeltivity User lookup action.
// 2. We only want to check whether to award Org Certifications if a User Certification
//   Completion was completed or a Org Certification Completion expired. This code
//   sets outputContinue to 1 if either of these conditions are met.

// Extracting UserID, OrgID, Status and UsersOrgID from the inputData object
const UserID = inputData.UserID;
const OrgID = inputData.OrgID;
const Status = inputData.Status;
const UsersOrgID = inputData.UsersOrgID;

// Initializing variable to hold the output value
var outputContinue = 0;
var outputOrgID = 0;


// Setting the outputOrgID if OrgID is greater than 0
if (OrgID > 0) {
  outputOrgID = OrgID;
}

// Checking if UserID is greater than 0 and Status is "Completed"
if (UserID > 0 && Status == "Completed") {
  outputOrgID = UsersOrgID;
  outputContinue = 1;
}

// Checking if OrgID is greater than 0 and Status is "Expired"
if (OrgID > 0 && Status == "Expired") {
  outputContinue = 1;
}

// Setting the output object
output = { outputContinue : outputContinue , outputOrgID : outputOrgID };

Step 4: Find existing Certification Completion 

This steps looks up the row number of any existing Certification Completion in the Google Sheet. If it doesn't find it, we'll store the Certification Completion in a new row.

App: Google Sheets

Event: Lookup Spreadsheet Row

Worksheet: Certification Completions Source Data

Lookup Column: Id

Lookup Value: 1. ID

Create Google Sheets Spreadsheet Row if it doesn't exist yet: Yes

Create:

• Id: 1. ID
• Certification: 1. Post Changes Certification Id Name
• User Name: 1. Post Changes User Id Name
• User ID: 1. Post Changes User Id
• Organization Name: 1. Post Changes Organization Id Name
• Org ID: 3. Output Org ID
• Completion Date: 1. Post Changes Completion Date
• Valid Until Date: 1. Post Changes Completion Valid Until
• Status: 1. Post Changes Status Name

Step 5: Store Certification Completion changes

In this step, we update the existing Certification Completion in the Google Sheet. There's some redundancy between Step 4 and 5 of the Zap in that if a new row was inserted in Step 4 that the Zap will update the same record in Step 5, but this avoids having to create multiple paths with duplicate steps.

App: Google Sheets

Event: Update Spreadsheet Row

Worksheet: Certification Completions Source Data 

Row: 4. ID

Certification: 1. Post Changes Certification Id Name

User Name: 1. Post Changes User Id Name

User ID: 1. Post Changes User Id

Organization Name: 1. Post Changes Organization Id Name

Org ID: 3. Output Org ID

Completion Date: 1. Post Changes Completion Date

Valid Until Date: 1. Post Changes Completion Valid Until

Status: 1. Post Changes Status Name

Step 6: Stop if the "whether to continue" variable is false

Now that the Certification Completion changes have been stored in Google Sheets, we only want to continue if the "whether to continue" variable from Step 3 is true. 

App: Filter by Zapier

Only continue if: 3. Output Continue is (Number) Greater than 0

Step 7: Find existing Org Certification Completion 

Since we only want to create a new "Certified Partner" Certification Completion when the Partner doesn't have a valid one, we need to look this up in Google Sheets. Specifically, we're going to match the Org ID to a row in the completed Organization Certification Completions pivot table.

App: Google Sheets

Event: Lookup Spreadsheet Row

Worksheet: Org Certs by Org Pivot Table

Lookup Column: Org ID

Lookup Value: 3. Output Org ID

Create Google Sheets Spreadsheet Row if it doesn't exist yet: Yes

Step 8: Stop if the Partner Org has a valid Certification Completion 

If Step 7 returned a successful result, then we don't need to create a new "Certified Partner" Certification Completion and can stop the Zap here.

App: Filter by Zapier

Only continue if: 7. Zap Search Was Found Status (Boolean) is false

Step 9: Look up count of valid User Certification Completions 

This Step looks up the number of User Certification Completions for the selected Partner Org in a Google Sheets pivot table. Similar to Step 7, we're going to match the Org ID of the Partner to a row of the User Certification Completions by Partner Org ID pivot table, returning the number of completed User Certifications for the two required Certification types.

App: Google Sheets

Event: Lookup Spreadsheet Row

Worksheet: User Certs by Org Pivot Table

Lookup Column: COUNT of Id

Lookup Value: 3. Output Org ID

Create Google Sheets Spreadsheet Row if it doesn't exist yet: Yes

Step 10: Stop if Certification requirements are not met 

In this step we make sure that the Partner has at least 2 or more valid Certification Completions for each of the two required Certifications. If not, we'll stop the Zap.

App: Filter by Zapier

Only continue if:

• 9. Certification is (Number) Greater than 1
• 9. COL C is (Number) Greater than 1 *

* Since the column names are on row 2 of the Google Sheet Pivot Table, Zapier doesn't pick them up.

Step 11: Create Org Certification Completion

This is the last step, where we create a new Organization Certification Completion for the Partner Org. Please note that this will trigger the Zap again.

App: Channeltivity

Event: Create Records

Record Type: Certification Completion

Certificationid: [enter the ID of the Org Certification in Channeltivity. You can look this up by going to Training & Certification > Certifications and adding the Id column, or from the URL path of the Certification details page.]

Userid: [leave blank]

Organizationid: 3. Output Org ID

Completiondate: today

Completionvaliduntil: in 365 days *

* You can enter any time period for the Certification to be valid

Final Thoughts

The Zap relies completely on the Certification Completion data within the Google Sheet to be accurate and will produce invalid results otherwise. Be careful when making manual edits to the Google Sheet. If any part of the Zap ever stops working you may want to make sure the latest Certification Completion data is in the sheet by repeating the export from Channeltivity.