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 for obtaining meaningful insights. However, organizations can connect Criteo to Redshift, a cloud data warehouser 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 connecting 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.

Criteo uses technology like an AI engine, creative bidding, and product recommendation to increase targeting with unique ads for customers. It allows marketers to target customers using various criteria like browsing history, website visits, app installations, etc.

Criteo follows the retargeting approach that leverages user behavior-based cookies and provides meaningful insights to marketers. This process helps marketers target the appropriate set of customers with immaculate customer-centric ads. For example, if a customer has once seen the marketer’s website, Criteo will show them advertisements from the same website.

Key Features of Criteo

• AI Engine: Criteo consists of an AI engine, which 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. The AI engine of Criteo uses 120+ shopper intent signals to create unique ads for every user to get the highest engagement. It can also analyze the context of every ad to get the best ads for customers at every stage of their journey.
• 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 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.

As Amazon Redshift is a scalable data warehouse, organizations do not have to worry about administrative tasks like memory management, resource allocation, configuration management, etc.

Amazon Redshift can be used with a set of nodes called Amazon clusters. The Amazon Clusters can be handled by Amazon Redshift Console or Amazon Command Line Interface. However, you can also programmatically manage clusters using Amazon Redshift Query API or the AWS Software Development Kit.

Key Features of Amazon Redshift

• Flexible Pricing: Despite its size, every organization can use Amazon Redshift, from startups to large corporate organizations. 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.
• Robust Security: Amazon Redshift allows users to secure their data warehouses without additional costs. You can configure firewalls to control network access with a specific data warehouse cluster. Amazon Redshift enables users to train the column and the row-level security control to ensure that they can only view the data with authorized access.
• Fast Performance: Amazon Redshift provides fast performance due to massively parallel processing, result caching, columnar data storage, query optimizer, complied code, data compression, and more.

Steps to Connect Criteo to Redshift

Businesses use many strategies to bring customers to their websites or apps. Criteo enables companies to get customers to their sites and analyze their behavior with reports and dashboards. Although it has analytics capabilities, they are limited to a few metrics. Consequently, importing data into a centralized repository like Amazon Redshift can help you perform better analysis and apply machine learning techniques for obtaining insights.

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

Hevo provides an Automated No-code Data Pipeline that helps you move your Criteo to Redshift. Hevo is fully-managed and completely automates the process of not only loading data from your 100+ data sources(including 40+ free sources)but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

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:

• Creating an app in Criteo and retrieving the Client ID and Client Secret.
• Assigning permissions to the app to read the data using Criteo’s Rest APIs.
• Creating a Pipeline in Hevo for transferring 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: Click PIPELINES in the Asset Palette.
• Step 1.2: Click + CREATE in the Pipelines List View.
• Step 1.3:On the Select Source Type page, select Criteo.
• Step 1.4: In the Configure your Criteo Source page, specify the following:
  • Pipeline Name: A unique name for your Pipeline.
  • Client ID: The client ID provided in the file you downloaded in Step 4 of the section, Retrieving the Client ID and Client Secret to enable Hevo to read data from your Criteo account.
  • Client Secret: The Client Secret is provided in the file you downloaded in Step 4 of the section, Retrieving the Client ID and Client Secret.
  • Timezone: The timezone as per which the Adset Transaction Report data must be replicated. Default timezone: UTC.
  • Currency: The currency in which the transaction values in the Adset Transaction Report must be replicated. Default currency: USD.
  • Historical Sync Duration: The duration for which the past data must be ingested.
• Step 1.5: Click TEST & CONTINUE.
• Step 1.6: Continue setting up the Destination and arranging the data ingestion.

Step 2: Configuring Amazon Redshift as a Destination

• To set up Amazon Redshift as a destination in Hevo, follow these steps:
  • Step 2.1: In the Asset Palette, select DESTINATIONS.
  • Step 2.2: In the Destinations List View, click + CREATE.
  • Step 2.3: Select Amazon Redshift from the Add Destination page.
  • Step 2.4: Set the following parameters on the Configure your Amazon Redshift Destination page:
    • Destination Name: A unique name for your Destination.
    • Database Cluster Identifier: Amazon Redshift host’s IP address or DNS.
    • Database Port: The port on which your Amazon Redshift server listens for connections. Default value: 5439
    • Database User: A user with a non-administrative role in the Redshift database.
    • Database Password: The password of the user.
    • Database Name: The name of the destination database where data will be loaded.
    • Database Schema: The name of the Destination database schema. Default value: public.
  • Step 2.5: Click Test Connection to test connectivity with the Amazon Redshift warehouse.
  • Step 2.6: Once the test is successful, click SAVE DESTINATION.

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.

Try Hevo Today!

SIGN UP HERE FOR A 14-DAY FREE TRIAL

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 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 below steps for loading the CSV file to Amazon Redshift.

1. Navigate to the CSV file you want to import the Amazon Redshift and load it to the Amazon S3 bucket. 
2. 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 for connecting 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.

Visit our Website to Explore Hevo

Hevo Data offers a No-code Data Pipeline that can automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Marketing, Customer Management, etc.

This platform allows you to transfer data from 100+ sources (including 40+ Free Sources) such as Criteo and Cloud-based Data Warehouses like Snowflake, Google BigQuery, Amazon Redshift, etc. It will provide you with a hassle-free experience and make your work life much easier.

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.