Print

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

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.
    cj-affiliate-img1
  2. Enter your CJ Affiliate login information and click Login.
    cj-affiliate-img2
  3. Now click Authentication > Personal Access Tokens from the left-hand menu.
    cj-affiliate-img3
  4. Add a new token. You can name it anything, we'll name it 'CJ for Sheets' in this example. Click Register.
    cj-affiliate-img4
  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.
    cj-affiliate-img5

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.
cj-affiliate-img8

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:
    • ApplicationCustom
    • MethodPOST
    • Request URLhttps://commissions.api.cj.com/query
    • Headers:
      • AuthorizationBearer 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 } } }}" }
      cj-affiliate-img9
  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).
    cjaffiliate-response

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/

4 thoughts on “Import CJ Affiliate (Commission Junction) Data to Google Sheets”

  1. Hey Ana,

    Thanks for these detailed instructions.
    I have 2 questions:
    1. is it possible to use a dynamic date, for example, last 7 days? (instead of specifying exact dates)
    2. is there a way to automate/schedule the request, so let's say it would run once a day?

    Thanks!

    Reply

Leave a Comment

Jump To