Import CJ Affiliate (Commission Junction) Data to Google Sheets

In this guide, we’ll walk through how to pull affiliate marketing data from the CJ Affiliate (Commission Junction) API directly into Google Sheets, using the API Connector add-on for Sheets.

We'll first get our API key from CJ Affiliate, and then set up a request to pull in information from CJ to your spreadsheet.

• Before You Begin
• Part 1: Get Your CJ Affiliate API Key
• Part 2: Get Your Publisher ID
• Part 3: Pull CJ Affiliate (Commission Junction) API Data into Sheets
• Part 4: Other CJ Affiliate API Data
• Part 5: API Documentation

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Get Your CJ Affiliate API Key

1. Navigate to https://developers.cj.com/ and click Sign In from the left-hand sidebar.
   
2. Enter your CJ Affiliate login information and click Login.
   
3. Now click Authentication > Personal Access Tokens from the left-hand menu.
   
4. Add a new token. You can name it anything, we'll name it 'CJ for Sheets' in this example. Click Register.
   
5. Your personal access token will now be displayed. Copy it and keep it safe as it won't be displayed again. Congrats, you now have access to the CJ Affiliate API.
   

Part 2: Get Your Publisher ID

Get your ID by checking the top-right corner while logged into the Members dashboard of your CJ account.

Part 3: Pull CJ Affiliate (Commission Junction) API Data into Sheets

For this example, we'll access near real-time commission data from CJ Affiliate's Commission Detail API. All requests to CJ Affiliate's GraphQL API use the same request URL: https://commissions.api.cj.com/query. Here's an example request configuration, just substitute in your own token where it says your_api_token, your own publisher ID where it says your_publisher_id, and your own dates of interest.

1. Open up Google Sheets and click Extensions > API Connector > Open > Create request.
2. In the request form enter the following:
   • Application: Custom
   • Method: POST
   • Request URL: https://commissions.api.cj.com/query
   • Headers:
     • Authorization: Bearer your_api_token
     • Content-Type: application/json
   • Request body: { "query": "{publisherCommissions(forPublishers: [\"your_publisher_id\"], sincePostingDate: \"2022-04-01T00:00:00Z\", beforePostingDate: \"2022-04-09T00:00:00Z\") { count payloadComplete records { actionTrackerName websiteName advertiserName postingDate pubCommissionAmountUsd items { quantity perItemSaleAmountPubCurrency totalCommissionPubCurrency } } }}" }
     
3. Create a new tab and click Set current to use that tab as your data destination.
4. Name your request and click Run. A moment later you’ll see commission detail statistics printed into your sheet. (This screenshot doesn't contain much data because I'm not an active user, but it shows that the request works).
   

Part 4: Other CJ Affiliate API Data

1. Check the documentation for the full list of available API requests. For example, view promotional properties like this:
   • API Request URL: https://accounts.api.cj.com/graphql
   • Request body: { "query": "{promotionalProperties(publisherId: \"5424919\") { totalCount resultList {name}}}"}
     As before, substitute in your own publisher ID.
2. When entering a graphQL query into the POST body, begin your POST body with a "query" key like this: { "query": . Enter the entire POST body into the value, and make sure it's properly escaped.
   
   You can use this tool (or similar) to escape your POST body: https://www.freeformatter.com/json-escape.html

Part 5: API Documentation

Official API documentation: https://developers.cj.com/