Criteo to Redshift Simplified: 2 Ways to Load Data Instantly

Today, most organizations are using advertising to promote their products and services. However, since many ads are often irrelevant to users, organizations do not get a better return on investment. To mitigate such problems, Criteo offers AI-enabled products and services to assist organizations in targeting the right audience. With Criteo, organizations also have access to analytics features to obtain meaningful insights. However, organizations can connect Criteo to Redshift, a cloud data warehouse service of AWS, for in-depth analysis. You can connect Criteo to Redshift using third-party ETL (Extract, Transform, and Load) tools, standard APIs, and more.

In this article, you will learn how to connect Criteo data to Redshift using two methods.

Prerequisites

Fundamental knowledge of AWS cloud.

What is Criteo?

Developed in 2005, Criteo is a commerce media platform that helps businesses create advertisements for their brands across the Criteo network. It allows thousands of publishers, brands, and retailers to reach new and existing audiences. Criteo consists of products such as Video advertising, Mobile app advertising, Dynamic retargeting, Web traffic generation, Audience selection, and more. Read about connecting Criteo to BigQuery to get a complete picture of your business health.

Key Features of Criteo

• AI Engine: Criteo consists of an AI engine that analyzes data in real-time from the Criteo Shopper Graph, with insights from 725m daily active users, 4B product SKUs, and 3500 product categories. Since the AI engine learns from real shoppers’ behaviors, it can continuously adapt to the chosen advertisement objective.
• Dynamic Retargeting: Dynamic retargeting in Criteo helps re-engage customers throughout their purchase journey with tailored video and display ads that can deliver across the world’s best publishers and are designed for measurable performance.
• Predictive Bidding: Predictive bidding in Criteo accurately forecasts every customer’s engagement, conversion, and basket value and bids the right price for ad inventory to reach them. This results in a high level of predictive accuracy that helps organizations ensure their budgets and meet their objectives.
• Reach Customers Easily: Criteo is an easy-to-use platform that allows businesses to create, manage, and report campaigns within minutes in one place. As a result, companies can reach customers at all phases of their journey, from discovery to purchase.

What is Amazon Redshift?

Developed in 2012, Amazon Redshift is a fully scalable, popular, and reliable data warehouse. It helps organizations with its column-oriented database, which is mainly designed to connect SQL-based clients with BI tools for in-depth analysis. As a result, many organizations use Amazon Redshift to analyze their business data and make informed decisions.

Key Features of Amazon Redshift

• Flexible Pricing: Despite its size, every organization, from startups to large corporate organizations, can use Amazon Redshift. Amazon Redshift offers a wide range of flexible pricing models for deployments like pay-as-you-go, on-demand, and more.
• AQUA (Advanced Query Accelerator): Amazon consists of a distributed and hardware-accelerated cache known as AQUA. It can speed Amazon Redshift up to 10x compared to other enterprise cloud data warehouses.
• ANSI-SQL: Amazon Redshift is based on ANSI-SQL, which uses industry-standard ODBC and JDBC connections to connect SQL clients and BI tools seamlessly. Amazon allows you to query files such as CSV, JSON, ORC, Avro, Parquet, and more with ANSI-SQL.
• Result Caching: This result caching feature of Amazon Redshift can deliver a sub-second response time for repeated queries. In Amazon Redshift, a query can search the cache to get any search results from earlier queries.

Method 1: Using Hevo Data to Set Up Criteo to Redshift 

Using Hevo Data, you can connect Criteo to Redshift in the following 2 steps:

The following one-time preparations are required for the data transfer from your Criteo account to the Destination location:

• Create an app in Criteo and retrieve the Client ID and Client Secret.
• Assigning permissions to the app to read the data using Criteo’s Rest APIs.
• Create a pipeline in Hevo to transfer data from Criteo to the destination database or data warehouse.

Step 1: Configuring Criteo as a Source

Perform the following steps to configure Criteo as the Source in your Pipeline:

• Step 1.1:On the Select Source Type page, select Criteo.
• Step 1.2: In the Configure your Criteo Source page, specify information according to the image.

Step 2: Configuring Amazon Redshift as a Destination

• Step 2.1: Select Amazon Redshift from the Add Destination page.
• Step 2.2: Set the parameters on the Configure your Amazon Redshift Destination page according to the image.

Here are more reasons to try Hevo:

• 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.
• Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

Method 2: Using Custom Code to Move Data from Criteo to Redshift

You can connect Criteo to Redshift by exporting Criteo data and importing it to Amazon Redshift. 

Step 1: Exporting Criteo Data

• Criteo allows organizations to use the Analytics module in the Management Center to customize reports for analyzing the Criteo data.
• It can also send scheduled reports and help you export data.

From any dashboard, Criteo allows you to click on the Export symbol and choose to share or download the report in CSV, Excel, or PDF options.

• Follow the below steps to export your report.

• From any dashboard, click on the ‘Save view’ button to save the dashboard you want.
• Criteo even allows you to schedule the reports you want to export. You can name the scheduled report, select the frequency of how often the reports should be exported, choose the format and enter the recipient’s email. Reports in Criteo can be scheduled daily, monthly or weekly in CSV, PDF, or Excel format.
• You can review all the reports you have scheduled by accessing your scheduled reports from the Navigation Bar.

Step 2: Importing Data to Amazon Redshift

You can import data to Amazon Redshift by using the COPY command. With the help of the COPY command, you can import data from Amazon Redshift from Amazon S3 buckets. Amazon S3 consists of buckets that include data in the form of documents, videos, texts, photos, and more. You can scale up to 100 buckets in your Amazon S3 bucket.

You can import the Criteo reports in the CSV file in Amazon Redshift using the Amazon S3 bucket. Follow the steps below to load the CSV file to Amazon Redshift.

• Navigate to the CSV file you want to import the Amazon Redshift and load it to the Amazon S3 bucket. 
• When your CSV file is loaded in the Amazon S3 bucket, use the COPY command to load it to the destination table.

COPY <schema-name>.<table-name> (<ordered-list-of-columns>) FROM '<manifest-file-s3-url>' 

CREDENTIALS'aws_access_key_id=<key>;aws_secret_access_key=<secret-key>' GZIP MANIFEST;

• Use the ‘CSV’ keyword in the COPY command to make Amazon Redshift identify the file format, as mentioned below.

COPY table_name (col1, col2, col3, col4)
FROM 's3://<your-bucket-name>/load/file_name.csv'
credentials 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>'CSV;

COPY table_name (col1, col2, col3, col4)
FROM 's3://<your-bucket-name>/load/file_name.csv'
credentials 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>'
CSV
INGOREHEADER 1;

Limitations

Exporting Criteo data and importing it to Amazon Redshift might seem easy, but it does not allow organizations to work with real-time data. Therefore, organizations can use standard APIs to connect Criteo data with Amazon Redshift.
But, to work with standard APIs, you require a solid technical team. As a result, to overcome such challenges, organizations or groups can use third-party ETL tools like Hevo data to enable autonomous and seamless integration between Criteo and Amazon Redshift.

Conclusion

In this article, you learned to connect Criteo to Redshift. Criteo helps businesses in promoting their products by analyzing customer behavior. It provides a platform where companies can retarget their audience and track their performance using Criteo Analytics. Businesses can also load this Criteo data to a data warehouse like Amazon Redshift, which can be used with powerful BI tools to gain meaningful insights.

Hevo Data offers a no-code data pipeline that can automate your data transfer process, allowing you to focus on other aspects of your business, such as analytics, marketing, customer management, etc.

Want to take Hevo for a spin? 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.

FAQs