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: Create your API Request URL
• 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: Create Your API Request URL

We’ll first access near real-time commission data.

CJ Affiliate offers both a standard REST API and a GraphQL API. For this first example, we'll use their GraphQL API, since the REST API version of the Commission Detail API is deprecated.

This is the URL for all GraphQL requests to CJ Affiliate's Commission Detail API:

https://commissions.api.cj.com/query

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

Now let's enter our values into API Connector.

1. Open up Google Sheets and click Extensions > API Connector >Open
2. In the Create tab, choose POST from the Method drop-down menu.
3. Enter the Request URL we just created
   
4. We don't need OAuth authentication so just leave that setting as None.
5. Under Headers, enter the following two sets of key-value pairs, like this:
   

   Authorization	Bearer YOUR_API_TOKEN
   content-type	application/json

   Substitute in your own API token where it says YOUR_API_TOKEN. It should look like this:
   

   
6. In the POST body, paste in the following. Note that special characters are escaped with a backslash (\).

   { "query": "{publisherCommissions(forPublishers: [\"5424919\"], sincePostingDate: \"2020-04-01T00:00:00Z\", beforePostingDate: \"2020-04-09T00:00:00Z\") {    count    payloadComplete    records {      actionTrackerName      websiteName      advertiserName      postingDate      pubCommissionAmountUsd      items {        quantity        perItemSaleAmountPubCurrency        totalCommissionPubCurrency      }    }  }}" }

   

7. Edit the highlighted areas above (date and publisher ID). Where it says 5424919, substitute in your own publisher ID. You can get your ID by checking the top-right corner while logged into the Members dashboard of your CJ account.
   
8. Create a new tab, give it a name, and click Set to use that tab as your data destination.
9. 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
   • POST 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/