Twitter Ads to Redshift: 2 Easy Methods

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.

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

Step 2: Configure Amazon Redshift as the Destination

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.

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