Print

Import Clickbank Data to Google Sheets

In this guide, we’ll walk through how to pull affiliate marketing data from the Clickbank API directly into Google Sheets, using the API Connector add-on for Sheets. We'll first get our API keys from Clickbank, and then set up a request to pull in information from Clickbank to your spreadsheet.

Before You Begin

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

Part 1: Get Your Clickbank Clerk API Key

  1. While logged into your Clickbank master account (https://accounts.clickbank.com/), click the Users icon on the left-hand menu, and then the Create New User button.
    clickbank-img1
  2. After creating the user, click the Manage Permissions link
    clickbank-managepermissions
  3. Set permissions and copy your Clerk API key. We'll need this soon.
    clickbank-permissions
  4. Scroll down and select the APIs this user should have access to
    clickbank-3rd party
  5. Click Save

Part 2: Get Your Clickbank Developer API Key

  1. Still in the Master Account, click the Account icon on the left-hand menu, and then on the name of your linked account.
    clickbank-img4
  2. Click on Account Settings
    clickbank-img5
  3. Scroll down the page to the Developer API Keys section and click Edit
    clickbank-img6
  4. Click Create New Developer Key.
    clickbank-img7
  5. Give your key a simple description and click Save.
    clickbank-img8
  6. You'll now see your developer API key. Copy this down as we'll need it in a moment.
    clickbank-img9

Part 3: Pull Clickbank API Data into Sheets

For this example, we'll get a list of orders. Clickbank authorizes by combining the developer API key with the clerk API key, so replace your_developer_api_key and your_clerk_api_key with the developer and clerk API keys you created above (make sure to separate them with a colon).

  1. Open up Google Sheets and click Extensions > API Connector > Open > Create request.
  2. In the request form enter the following:
    • ApplicationCustom
    • MethodGET
    • Request URLhttps://api.clickbank.com/rest/1.3/orders2/list
    • Headers:
      1. Key = Accept, Value = application/json
      2. Key = Authorization, Value = your_developer_api_key:your_clerk_api_key
  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 order statistics printed into your sheet.
    clickbank-response

Part 4: More Example API URLs

  1. Check the documentation for the full list of available API requests. For example:
    • get summary stats (account nickname, date, sales, refunds, chargebacks)
      https://api.clickbank.com/rest/1.3/quickstats/list
    • retrieve information from the analytics API (substitute in your account nickname)
      https://api.clickbank.com/rest/1.3/analytics/affiliate/vendor_product_sku?account=ACCOUNT_NICKNAME
    • filter the analytics API for specific fields (again, substitute in your account nickname)
      https://api.clickbank.com/rest/1.3/analytics/affiliate/vendor?select=net_sale_amount&select=hop_count&select=net_sale_count&account=ACCOUNT_NICKNAME
  2. By default, some of the Clickbank API endpoints will return data that gets parsed into separate columns. You can select API Connector's compact or grid report styles for a more compact output with fewer columns.

Part 5: Handle Pagination

Clickbank's API returns just 100 rows at a time, as described here.
clickbank-img13

Clickbank uses an unusual form of pagination that increments pages through the header. This is currently not supported by API Connector's automatic pagination handling feature. Therefore, to fetch additional pages of data, manually set the page number in the header, like this:
clickbank-img14

Part 6: API Documentation

Official API documentation: https://support.clickbank.com/hc/en-us/articles/220376527-ClickBank-APIs

Interactive request builder for the Analytics API: https://api.clickbank.com/rest/1.3/webjars/swagger-ui/index.html

2 thoughts on “Import Clickbank Data to Google Sheets”

  1. Hi!

    I have a question about this API. I've noticed that only my 30 recent orders' days are included when I make the request. There are indeed, parameters "startDate" and "endDate" to filter the period on the documentation. However, I've tried them and they did not work for me — I still can't request orders older than 30 days. Can you guys help me with this?

    I'd appreciate a lot! Thanks!

    Reply
    • Hey Roberto, I just tried https://api.clickbank.com/rest/1.3/orders2/list?startDate=2021-04-01&endDate=2021-04-03 and it worked fine for me, so I'm not sure what the problem could be. Maybe it's related to pagination?

      Reply

Leave a Comment

Jump To