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.
- 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
BEFORE YOU BEGIN
Click here to install the API Connector add-on from the Google Marketplace.
PART 1: GET YOUR CJ AFFILIATE API KEY
- Navigate to https://developers.cj.com/ and click Sign In from the left-hand sidebar.
- Enter your CJ Affiliate login information and click Login.
- Now click Authentication > Personal Access Tokens from the left-hand menu.
- Add a new token. You can name it anything, we’ll name it ‘CJ for Sheets’ in this example. Click Register.
- 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.
- Open up Google Sheets and click Add-ons > API Connector > Open.
- In the Create tab, choose POST from the Method drop-down menu.
- Enter the Request URL we just created
- 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:
- 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 } } }}" }
- 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.
- We don’t need any Authentication so just leave that setting as None.
- Create a new tab, give it a name, and click ‘Set’ to use that tab as your data destination.
- 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
- 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.
- 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
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!
1) Yep, to use a dynamic date you can add your date into a cell (e.g.
=TODAY()-1
) and then reference that cell in your POST body. Just make sure to match the date formatting required by the API.2) Yes, you can schedule requests by clicking the Schedule tab. This article contains more info on how it works: https://mixedanalytics.com/knowledge-base/api-connector-scheduling/
Hi Ana! Can you tell me how I can check my affiliate links and the number of clicks for each link with API requests?
Hey Kate, sorry, I checked through their API docs and couldn’t find anything about this, the closest I see is this: https://developers.cj.com/docs/rest-apis/link-search. Does that cover what you want? If not, your best bet is to reach out to their tech support and ask what endpoints provide that data.