Companies that advertise through social networks like Twitter have a tough time when it comes to running custom analyses over the data generated from their campaigns. The reason is that the data resides with social networking sites. Many businesses face challenges migrating data from Twitter Ads to Redshift due to inconsistent data formats and the limitations of Twitter’s API. These may result in delayed data syncing, duplicates, or even errors during the migration.

In this post, we will deal with the methods to get data from Twitter Ads to Redshift. You can tackle the migration manually with Twitter’s API or opt for a no-code solution like Hevo. Let’s dive into how you can make this data migration smooth, efficient, and hassle-free.

Understanding the Methods of Connecting Twitter Ads to Redshift

Method 1: Using Twitter Ads API to connect Twitter Ads to Redshift

Step 1: Authorizing Twitter’s OAuth Application

The first step is to get past Twitter’s OAuth application flow and authorize the application. Fortunately, Twitter has made a utility called ‘twurl’ available to execute this three-step process through the command line.

twurl authorize --consumer-key consumer_key --consumer-secret consumer_secret

The key and secret mentioned above can be obtained by accessing: https://dev.twitter.com/apps/new  and creating an entry for the app we are building.

The command returns a URL, open it in your browser, authenticate with Twitter, and enter the returned PIN in the terminal to complete authorization. You can then use the APIs via twurl.

Step 2: Accessing the Analytics API of Twitter Ads

In this step, we will be using the authorized twurl application to access the Analytics API of Twitter Ads.

twurl -H ads-api.twitter.com "/6/stats/accounts/18co4564d45t/active_entities?entity=PROMOTED_TWEET&start_time=2020-04-05T00:00:00Z&end_time=2020-04-06T00:00:00Z"

In the above command, 18co4564d45t is the account ID of the advertising account. Here we are trying to get all the promoted tweets for which engagement metrics have changed in the 24 hours from 5 April to 6 April. The response will be JSON in the format below.

{   "request": {     "params": {       "account_id": "18co4564d45t",       "entity": "PROMOTED_TWEET",       "start_time": "2020-04-05T00:00:00Z",       "end_time": "2020-04-06T00:00:00Z"     }   },   "data": [     {       "entity_id": "2r1yww",       "activity_start_time": "2020-04-05T20:55:20Z",       "activity_end_time": "2020-04-06T03:43:56Z",       "placements": [         "ALL_ON_TWITTER"       ]     },     {       "entity_id": "2r31fn",       "activity_start_time": "2020-04-06T08:11:18Z",       "activity_end_time": "2020-04-06T14:42:59Z",       "placements": [         "ALL_ON_TWITTER",         "PUBLISHER_NETWORK"       ]     }   ] }

From the response, it is evident that two entities have changed: 2r1yww and 2r31fn.

Loading data from Twitter Ads to Redshift

Using Hevo, you can directly extract and load data from various sources like Twitter Ads into your Data Warehouse, such as Redshift or any Database.

Why Hevo is the Best:

  1. Integrate data from 150+ sources (60+ free sources).
  2. Get round-the-clock support, live monitoring, and instant notifications about your data transfers.
  3. Use a drag-and-drop transformation feature — a user-friendly way to perform simple data transformations.

Choose Hevo and see what Whatfix says, with secure integration from 5 sources and 2 months saved in engineering time, Hevo helped boost their data accuracy by 1.2x.

Sign up here for a 14-day Free Trial!

Step 3: Creating a CSV File

Let’s now use the entity ID to fetch the engagement metrics for the last 5 days and create a CSV to be loaded into Redshift.

twurl -H https://ads-api.twitter.com/7/stats/accounts/18ce54d4x5t?entity=LINE_ITEM&entity_ids=2r1yww, 2r31fn&start_time=2020-04-01&end_time=2020-04-06&granularity=TOTAL&placement=ALL_ON_TWITTER&metric_groups=ENGAGEMENT | jq -r ".data[] | [.id,.id_data[].metrics.impressions[0],.id_data[].metrics.likes[0],.id_data[].metrics.clicks[0]] | @csv" >> eng_metrics.csv

The above command uses twurl to fetch the engagement metrics as JSON, parses it using a command-line utility called jq, and extracts the impressions, likes, and clicks count of each entity ID into a CSV file. Note that we have used the entity identifiers fetched in the earlier steps in the entity_id parameter of the current request. The resultant CSV will be something like below.

"2r1yww",163,10,43 "2r31fn",1233,1,58

Step 4: Moving the CSV File to an S3 Bucket

Let us now move the generated CSV to an S3 bucket to stage it before copying it to Redshift.

aws s3 cp /metrics/eng_metrics.csv s3://metrics_bucket/

Step 5: Loading Data to Redshift

The final step remaining is to load the data into Redshift. Assuming a table is already created, let’s use the COPY command to accomplish this.

copy contacts from 's3://metrics_bucket/eng_metrics.csv'  iam_role 'arn:aws:iam::0123456789012:role/User_RedshiftRole'  csv;

That completes the process of getting Twitter Ads data into Redshift.

Limitations of using the Twitter Ads API to connect Twitter Ads to Redshift

  • Twitter Ads API is rate-limited to prevent overuse, and hence the application logic will have to make requests by continuously tracking the rate limit status API for Twitter Ads.
  • Data for larger time windows is made available by the API through asynchronous requests, which means the application can only spawn jobs and will need to keep track of the job status to fetch the data when the job is complete. 
  • The above approach may work for a one-off load, but in real life, when executed as a continuous syncing process, additional logic to handle duplicates and data updates has to be implemented. 
  • Redshift’s default automatic data type assignment can create problems if simple COPY commands are used. In reality, we will need configuration JSONs to explicitly map fields to specific columns for type safety.
  • The number of entities that can be fetched in a single API request is limited to 20. So the application has to implement logic to request information in a phased manner.

If all of the above seems too much to handle and you want the developers to focus on the core business problems, a great alternative to solve this problem will be to use a service like Hevo. Hevo can execute data loads from Twitter Ads API to various sources in a matter of a few clicks. 

Method 2: Using Hevo Data to connect Twitter Ads to Redshift

Step 1: Configure Twitter Ads as Source

    Twitter Ads Source Configuration Page image

    Step 2: Configure Amazon Redshift as the Destination

      Amazon Redshift Destination Configuration Page image

      That is it. Hevo takes care of all the heavy-weight lifting to ensure that the data from Twitter Ads is transferred to the Redshift data warehouse in a hassle-free fashion, in real-time with no cost.

      Integrate Twitter Ads to Snowflake
      Integrate Twitter Ads to PostgreSQL
      Integrate Twitter Ads to BigQuery

      Conclusion

      This blog talks about the two methods you can use to connect Twitter Ads to Redshift. It starts with a brief introduction to Twitter Ads and Redshift before diving into the two methods that can be used to connect Twitter Ads to Redshift.

      Extracting complex data from a diverse set of data sources can be a challenging task, and this is where Hevo saves the day! Hevo offers a faster way to move data from Databases or SaaS applications into your Data Warehouse to be visualized in a BI tool. Try a 14-day free trial to explore all features, and 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

      1. Is it possible to customize the Twitter Ads data before loading it into Redshift?

      Yes, with the transformation step of the ETL process, you can filter, aggregate, or modify data based on your analysis needs before loading it into Redshift.

      2. Can I combine Twitter Ads data with other data sources in Redshift?

      Yes, Redshift can blend Twitter Ads data with other marketing channels, customer data, and sales data, which lets you analyze channels across the board comprehensively.

      3. Do I need coding knowledge to integrate Twitter Ads with Redshift?

      Not necessarily. Most ETL platforms offer no-code or low-code solutions to make the process of transferring data from Twitter Ads to Redshift easy configurations.

      Suraj Poddar
      Principal Frontend Engineer, Hevo Data

      Suraj has over a decade of experience in the tech industry, with a significant focus on architecting and developing scalable front-end solutions. As a Principal Frontend Engineer at Hevo, he has played a key role in building core frontend modules, driving innovation, and contributing to the open-source community. Suraj's expertise includes creating reusable UI libraries, collaborating across teams, and enhancing user experience and interface design.