Amazon Ads to Redshift: 2 Easy Steps

Unlock the full potential of your Amazon Ads data by integrating it seamlessly with Redshift. With Hevo’s automated pipeline, get data flowing effortlessly—watch our 1-minute demo below to see it in action!

One of the biggest markets is Amazon. Amazon’s dominant position and the fierce competition among Amazon merchants go hand in hand. As a result, Amazon Ads is expanding, and sellers need to develop a comprehensive and adaptable marketing strategy that will yield the best ROI. You might need to get some insights of Amazon Ads, to do so you would need to transfer it to a Data Warehouse such as Amazon Redshift. 

In this article, you will learn how to transfer data from Amazon Ads to Redshift using Amazon Ads API and JSON file format.

What are Amazon Ads?

Similar to Google’s pay-per-click adverts, Amazon Ads (formerly AMS or Amazon Marketing Services) charges vendors only when customers click on their advertisements (regardless of whether or not the item sells). 

Amazon’s advertising revenue is expanding quickly, especially as the company diversifies its product offerings throughout its ecosystem. Sellers can now target consumers on platforms like Amazon.com, Fire TV Sticks, IMDb.com, Kindles, Freedive, apps, and other Websites, Apps, and websites using the Amazon DSP (Demand Side Platform).

Types of Amazon Ads

• Sponsored Products: The normal product listings that show up in search results and on product detail pages are known as sponsored products. They are keyword-targeted, pay-per-click display adverts for specific products. The product listing page can be reached by clicking the link.
• Sponsored Brand Campaigns: You can advertise several goods using keyword-targeted advertising in Sponsored Brand Campaigns above, below, and next to search results. They are based on an auction-based, pay-per-click pricing mechanism. The customer can be precisely sent to your personalized product page or your Amazon Store.
• Product Display Ads: Pay-per-click advertisements called “product display ads” are designed to upsell or cross-sell your customers. Unlike the other types of inventory, this one is accessible to all advertisers, not only those using the platform to sell their products. This category is divided by Amazon into display, video, and custom.
• Video Ads: Video advertisements can be displayed on websites controlled by Amazon, including Amazon.com and IMDb, as well as on Fire TV devices and other online domains. You don’t need to sell goods on Amazon in order to purchase Amazon video advertisements.
• Amazon Stores: Users’ own multi-page Amazon Stores allow them to advertise their goods or brands. Brands receive an Amazon URL and have access to traffic data.
• Amazon Native Ads: On a brand’s own website, Amazon Native Ads can be displayed. These include bespoke ads, search ads, and recommendation ads.

What is Amazon Redshift?

• Amazon Redshift, is a popular, entirely scalable, and reliable data warehouse. Businesses can start using Amazon Redshift with a cluster of nodes known as an Amazon cluster. 
• The Amazon Command Line Interface or Redshift Console can be used to administer Amazon clusters. Companies can easily analyze data by connecting SQL-based clients like BI tools to an incorporated column-oriented database in Amazon Redshift. 
• Since Amazon Redshift is a fully scalable data warehouse, organizations don’t have to worry about administrative tasks like Memory Management, Resource Allocation, Configuration Management, and more.
• To programmatically manage clusters using Amazon Redshift, businesses can utilize the AWS Software Development Kit or the Amazon Redshift Query API.

Key Features of Amazon Redshift

• Strong Security: Using Amazon Redshift to safeguard data warehouses is completely free for users. Firewalls can be installed by Amazon Redshift users to control network access to a specific cluster of data warehouses. Users of Amazon Redshift can train the column and row-level security control to guarantee that only individuals with authorized access may view the data.
• Result Caching: Amazon Redshift’s result caching functionality can offer a response time of less than a second for repeated searches. Any search results from earlier queries can be retrieved by searching the cache in Amazon Redshift.
• Fast Performance: Amazon Redshift offers quick performance because of its unique features, which include massively parallel processing, columnar data storage, result caching, query optimizer, data compression, produced code, and more.

Method 1: Connect Amazon Ads to Redshift using Hevo

Step 1.1: Configure Amazon Ads as Your Source

Step 1.2: Configure Redshift as Your Destination

With this, you have successfully connected Amazon Ads to Redshift.

Why use Hevo?

• Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
• Smooth Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to your schema in the desired Data Warehouse.
• Exceptional Data Transformations: Best-in-class & Native Support for Complex Data Transformation at fingertips. Code & No-code Flexibility is designed for everyone.
• Quick Setup: Hevo with its automated features, can be set up in minimal time. Moreover, with its simple and interactive UI, it is extremely easy for new customers to work on and perform operations.
• Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.

Method 2: Manually Connect Amazon Ads to Redshift

• Step 1: Exporting Amazon Ads Data as JSON Format using Amazon Ads API
  • Step A: Requesting and Setting up Amazon Ads API Access
  • Step B: Creating Authorization Grant
  • Step C: Generating and Refreshing Tokens for Access
  • Step D: Retrieving Profile ID
  • Step E: Making API Calls and Retrieving JSON Data
• Step 2: Importing Amazon Ads Data in Amazon Redshift in JSON Format
  • Step A: Creating and Uploading JSON File to S3
  • Step B: Creating JSONPath File
  • Step C: Loading the Data into Redshift

You cannot directly export the data from Amazon Ads to Redshift. To export data from Amazon Ads to Redshift, first you will have to export data from Amazon Ads as JSON files and then load the JSON files into Redshift.

Step 2.1: Exporting Amazon Ads Data as JSON Format using Amazon Ads API

The first step in exporting data from Amazon Ads to Redshift is exporting data from Amazon Ads as JSON files.

You can use the REST API provided by the Amazon Ads API to manage the advertising resources connected to an Amazon advertiser. You must apply for access and receive approval before using the Amazon Ads API. All applicants may be direct advertisers, partners, or integrators. A client application that serves as a credential for requests to the Amazon Ads API can be created by approved applicants through Login with Amazon (LwA). 

Step 2.1.1: Requesting and Setting up Amazon Ads API Access

• Make an Amazon Login application. A client application managed by Login with Amazon submits requests to the Amazon Ads API. The creation of the client application is optional, and no consent is needed at this point. Remember to retrieve your Security Credentials.

• Request authorization to use the API. Questions on how your company plans to utilize the Amazon Ads API are included in the application form, along with details on the Data Protection Policy and the Amazon Ads API License Agreement.
• Give your LwA application API access. YYour LwA client application will make API request once your application has been authorized. After you click the Submit button, a confirmation page will appear with your LwA application’s client ID and the scopes it can now use to ask user accounts for permission to access data and services:
  • The most common Amazon Ads API request’s needed scope is advertising::campaign_management.
  • The necessary scope for creating test accounts is advertising::test:create_account.

The advertising::campaign_management API method will largely be used by you to access data and services.

Step 2.1.2.: Creating Authorization Grant

• Create an authorization URL that enables a user account to authorize your client application in order to get an authorization code. There are three steps in this process:
  • Allowing a Return URL: Move your cursor to the “gear” symbol next to Manage on the right side of the screen, select Web Settings from the menu, then pick the security profile you used to apply for API access. Click Edit in the Web Settings panel. In the Allowed Return URLs area, enter a true address and hit Save.
  • Select the URL Prefix for your Region: Log in using the Region, Amazon uses a distinct permission URL prefix for each of the region. Pick the URL appropriate to your area.
  • Set the values for the Required Query Parameters. The required parameters are client_id, scope, response_type and redirect_uri.

• There are two circumstances in which permission may be requested and granted to access advertising data and services linked to a user account:
  • Direct Marketer: To access the advertising information and services linked to your own Amazon account, you wish to use the API. Put the authorisation URL found above in the address bar of your browser. Visit the URL and carry out the easy instructions.
  • Partner: You have been given permission by a third party to access the advertising information and services linked to their Amazon account. Send the third party the authorization URL that was established above along with instructions on how to copy the URL and paste it into their browser’s address bar, go to the URL, and complete the short steps.

You will receive the authorized URL similar to this:

https://www.amazon.com/code=xxxxxxxxxxxxxxxxxxx&scope=advertising%3A%3Acampaign_management

Step 2.1.3: Generating and Refreshing Tokens for Access

• Use the Amazon account you used to establish your Login with Amazon client to log in to Amazon Developer. Locate the client ID and client secret for the security profile to which API access was granted earlier by going to the Login with Amazon panel.
• To request access and to refresh tokens, call the authorization URL. The authorization URL for your region should be chosen in order to retrieve access and refresh tokens. Create a POST request after that to get access and refresh tokens. Following are the search criterias in this request:

For instance, change the values in the following request to retrieve access and refresh tokens using cURL:

curl  \
    -X POST \
    --data "grant_type=authorization_code&code=AUTH_CODE&redirect_uri=YOUR_RETURN_URL&client_id=YOUR_CLIENT_ID&client_secret=YOUR_SECRET_KEY" \
    https://api.amazon.com/auth/o2/token

• When a token request is successful, a JSON object with the following schema is returned:

Example: 

{
    "access_token": "Atza|XXXXXXXXXXXX",
    "refresh_token": "Atzr|XXXXXXXXXXXX",
    "token_type": "bearer",
    "expires_in": 3600
}

The validity of access tokens is 60 minutes. The refresh token can be used at any moment to create a fresh access token. Tokens for refresh are persistent.

Refresh Token: A new access token can be retrieved at any moment using the refresh token after tokens have been retrieved using the permission code. The same URL as determined above can be called using the refresh token, but with different parameters.

Step 2.1.4: Retrieving Profile ID

Accessing the Profile Resources. 

Make a GET call to the /v2/profiles endpoint in an area where the user account administers advertising accounts to get a list of the available profiles.

See API Endpoints for a list of local hosts. View the Profiles OpenAPI specification for technical details.

For instance, the host for North America should receive the request for a user account that oversees an advertising account in the MX marketplace:

https://advertising-api.amazon.com/v2/profiles

Two headers are necessary for the request:

• The LwA client application’s client identifier is Amazon-Advertising-API-ClientId.
• Authorization, the access token with the string “Bearer” appended.

Substitute your values in the following request to make a cURL call to the North American host:

curl  \
    -H "Amazon-Advertising-API-ClientId: YOUR_CLIENT_ID"\
    -H "Authorization: Bearer YOUR_ACCESS_TOKEN" \
    https://advertising-api.amazon.com/v2/profiles

Now your profile response will look something like this:

[
  {
    "profileId": 888888888,
    "countryCode": "MX",
    "currencyCode": "MXN",
    "timezone": "America/Los_Angeles",
    "accountInfo": {
      "marketplaceStringId": "A1AM78C64UM0Y8",
      "id": "ENTITY2Ihjasdjkeru",
      "type": "vendor",
      "name": "Name of the Account",
      "validPaymentMethod": false
    }
  }
]

Passing the ID in Further Requests. 

Requests to the Amazon Ads API can only access resources for one profile, with the exception of the /v2/profiles endpoint. The profile ID provided in the Amazon-Advertising-API-Scope header is what chooses which profile to use. Your client ID, your access token, and a profile ID will be the values for the following three mandatory headers whenever you make a subsequent request to the API:

• Your client ID is specified in the Amazon-Advertising-API-ClientID.
• Authorization, the access token with the string Bearer appended.
• The profile ID for an advertising account in a certain marketplace is defined by the Amazon-Advertising-API-Scope.

Step 2.1.5: Making API Calls and Retrieving JSON Data

The Ads API can be used to manage campaigns, retrieve reporting information, and more. This lesson explains how to use the appropriate GET campaigns endpoint to list all of your active Sponsored Ad Campaigns (Sponsored Products, Sponsored Brands, and Sponsored Display).

API Request

• Based on the region of the profile used in the request, you should provide a prefix when contacting the Amazon Ads API.

• Most requests to the Amazon Ads API need to include certain headers:

Sample Request from cURL:

curl --location --request GET 'https://advertising-api.amazon.com/v2/sp/campaigns' \
--header 'Amazon-Advertising-API-ClientId: amzn1.application-oa2-client.xxxxxxxxxx' \
--header 'Amazon-Advertising-API-Scope: xxxxxxxxx' \
--header 'Authorization: Bearer xxxxxxxxxxxx' \

Make care to enter your own client ID, access token, and profile ID if you’re copying the examples.

API Response

Any of the GET campaign endpoints that receive a successful response produce a 200 response code. Objects from campaigns are contained in a JSON array in the response body.

Sample API Response in JSON Format:

[
    {
        "campaignId": 127519806194475,
        "name": "SdTestCampaign-26/01/2022 15:37:31",
        "tactic": "T00020",
        "startDate": "20220126",
        "state": "enabled",
        "costType": "cpc",
        "budget": 100,
        "budgetType": "daily",
        "deliveryProfile": "as_soon_as_possible"
    }
]

You get a 200 response with an empty JSON array if you don’t yet have any campaigns of a particular ad type established ([]). Log in to the Amazon advertising interface to see if you have any running campaigns. 

For getting a deep understanding of the Amazon Ads API and steps involved in the above process, check out the official documentation. The first step in exporting data from Amazon Ads to Redshift is complete now.

Step 2.2: Importing Amazon Ads Data in Amazon Redshift in JSON Format

The second step in exporting data from Amazon Ads to Redshift is importing JSON data into Redshift. For transferring data from Amazon Ads to Redshift, you have to first transfer data into S3. You can use Redshift’s built-in “COPY” command to transfer data from AWS S3 to Redshift in order to load your data from JSON to Redshift. The data is sent from AWS S3 to Redshift using AWS S3 as the source. CSV, Parquet, JSON, etc., can be moved with the Copy command.

Step 2.2.1: Creating and Uploading JSON File to S3

Let’s look at how to produce and upload the JSON files to S3 so that you can transfer data from Amazon Ads to Redshift and begin the Redshift JSON extraction procedure.

• Make a JSON file called campaign.json and add the campaign records to it. For example,

[
    {
        "campaignId": 127519806194475,
        "name": "SdTestCampaign-26/01/2022 15:37:31",
        "tactic": "T00020",
        "startDate": "20220126",
        "state": "enabled",
        "costType": "cpc",
        "budget": 100,
        "budgetType": "daily",
        "deliveryProfile": "as_soon_as_possible"
    }

    {
        "campaignId": 127519806194476,
        "name": "SdTestCampaign-26/01/2022 16:37:31",
        "tactic": "T00021",
        "startDate": "20220126",
        "state": "enabled",
        "costType": "cpc",
        "budget": 150,
        "budgetType": "daily",
        "deliveryProfile": "as_soon_as_possible"
    }
]

• Using AWS CLI upload the JSON file to AWS S3

Syntax - aws s3 cp <source file> <target S3 path>
Example - aws s3 cp campaign.json s3://test-bucket/json/

Step 2.2.2: Creating JSONPath File

A JSON hierarchical data structure’s single element can be reached using the expression JSONPath. Amazon Redshift only demands the full JSONPath of that element in order to effectively read it; it does not accept any JSONPath features, such as wildcard characters or filter expressions. As a result, sophisticated, multi-level data structures cannot be parsed by Amazon Redshift.

• Make the JSONPath file for the JSON mentioned above. Copy the text below and save it as campaign_jsonpath.json in a file.

{

"jsonpaths": [

"$.campaignId",
"$name",
"$tactic",
"$name",
"$startDate",
"$state",
"$costType",
"$budget",
"$budgetType",
"$deliveryProfile"
]
}

• To upload the file to S3, use the AWS CLI.

Syntax - aws s3 cp <source file> <target S3 path>
Example - aws s3 cp campaign_jsonpath.json s3://test-bucket/jsonpaths/

Step 2.2.3: Loading the Data into Redshift

Now you are in final phase of Amazon Ads to Redshift data transfer. Let’s examine the procedures for loading data into Redshift.

• The table DDL in Redshift must first be created; it will store the JSON schema information.

CREATE TABLE campaign(campaignId INT, name STRING, tactic STRING, startDate DATE state STRING costType STRING budget INT budgetType STRING deliveryProfile STRING  primary key(campaignId))

• Use the Redshift COPY command to load the data into Redshift from AWS S3.

copy campaign
from 's3://test_bucket/json/campaign.json’
credentials 'aws_iam_role=arn:aws:iam::xxxxx:role/Redshift'
region 'eu-west-3'
json 's3://test_bucket/jsonpath/campaign_jsonpath.json';

• When the above mentioned command has completed execution, you will receive a successful message.
• To verify the loaded data, use the command below.

select * from campaign

Voila! Using the COPY command, you were able to successfully load the data from JSON to Redshift. You have successfully done Amazon Ads to Redshift data transfer.

Conclusion

• In this article, you get a glimpse of how to connect Amazon Ads to Redshift after a brief introduction and salient features.
• The methods talked about in this article are using Amazon Ads API and JSON files.
• The process can be a bit difficult for beginners. In case you are looking for an automated solution for transferring data from your desired sources to a destination of your choice, this is where Hevo saves the day!

Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Frequently Asked Questions