Import CJ Affiliate (Commission Junction) Data to Google Sheets

CJ Affiliate (Commission Junction) is an affiliate marketing network. In this guide, we’ll walk through how to pull affiliate marketing data from the CJ Affiliate 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.

• 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 API Data

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 follow the CJ Affiliate API documentation to 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

We can now enter all our values into API Connector and import CJ Affiliate data into Google Sheets.

1. Open up Google Sheets and click Add-ons > API Connector > Create New API Request.
2. Choose POST from the Method drop-down menu.
3. In the Create Request interface, enter the Request URL we just created
   
4. 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:
   

5. 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      }    }  }}"
   }

   

6. 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.
   
7. Create a new tab. You can call it whatever you like, but here we’ll call it ‘CommissionDetail’. While still in that tab, click ‘Set’ to use that tab as your data destination.
8. Name your request. Again we’ll call it ‘CommissionDetail’
9. Click Run and 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 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