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. Most social networks allow access to the data generated by campaigns through their APIs. Having such data on your data warehouse expands the horizon of derived information since the data can then be combined with the organization’s own data to form opinions.

A typical architecture is to have a separate data warehouse that holds the other relevant data as well as the metrics generated from social media campaigns. In this post, we will deal with the methods to get data from Twitter Ads to Redshift, a very popular data warehouse service.

Introduction to Twitter Ads

Twitter has distinguished itself as a social networking and microblogging service on which users can interact with and post messages called “tweets”.

You can use Twitter Advertising for your brand to promote its products and reach new users who might be interested in what your brand has to offer. Twitter provides various simple Twitter Ad formats and no minimum advertising budget to boost your marketing campaign and bolster your growth by improving your Lead Conversion Rate.

Here are a few types of Twitter Ads to get you started:

• Promoted Trends: Trending Topics on Twitter is a high-turnover list on Twitter’s right-hand side. This depicts a collection of the most popular hashtags and topics being used in real-time.
• Promoted Moments: These are the equivalent of Twitter story ads. These can be created on the desktop and are a curated collection of similar tweets that tell a story.
• Promoted Accounts: This type of advertising focuses on promoting your brand’s entire Twitter Account instead of individuals tweets. These accounts are displayed in the potential followers’ timelines.
• Promoted Tweets: Promoted Tweets are different from regular tweets since an advertiser is paying to display the content to prospective clients on Twitter.

Introduction to Redshift

Amazon Redshift is one of the most widely used Cloud Data Warehouses in today’s marketplace. Amazon Redshift can integrate with your Data Lake with considerable ease and offer a 3x better price-performance compared to its competitors. Amazon Redshift allows you to combine and query exabytes of Semi-structured and Structured data across your operational database, Data Warehouse, and Data Lake using standard SQL.

Here are a few salient features of Redshift:

• Redshift ML: Redshift ML simplifies creating, training, and deploying Amazon SageMaker models using SQL for database developers and Data Analysts.
• Federated Query: The federated query allows you to reach into your operational, relational database. You can now query live data across one or more Aurora PostgreSQL and Amazon RDS databases to get instant visibility into the end-to-end business operations without the need for data movement.
• Materialized Views: This Redshift feature allows you to achieve faster query performance on datasets ranging in size from gigabytes to petabytes.
• Limitless Concurrency: Amazon Redshift provides fast performance consistently, even with thousands of concurrent queries, whether they query data directly in your Amazon S3 Data Lake, or in your Amazon Redshift Data Warehouse.

Understanding the Methods of Connecting Twitter Ads to Redshift

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

Twitter Ads API is segregated into five groups broadly – Analytics, Campaign Management, Audiences, Creative, and Measurement. Analytics API helps the account owners to get information on metrics like retweets, likes, clicks, etc. Audience APIs are for managing the target audience and creatives are for programmatically managing the uploaded content like video, pictures, etc. Measurement API helps to get access to conversion tracking metrics. For our attempt, we will be using the Analytics API.

Now that we know enough about Twitter Ads API, let’s go through the steps to load data from Twitter Ads API to Redshift:

• Step 1: Authorizing Twitter’s OAuth Application
• Step 2: Accessing Analytics API of Twitter Ads
• Step 3: Creating a CSV File
• Step 4: Moving the CSV File to an S3 Bucket
• Step 5: Loading Data 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.

This command will return a URL which we should paste into the browser. The page returns a pin once you authenticate with Twitter. This pin is to be entered at the twirl terminal to complete the authorization process. Once the authorization is complete, you are free to access the APIs through the twurl command-line utility.

Step 2: Accessing 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 below format.

{   "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 a JSON, parses it using a command-line utility called jq, and extracts 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 to 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 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 are 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 rather 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 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 to Redshift data warehouse, in a hassle-free fashion, in real-time with no cost.

Here are a few salient features of Hevo:

• Simplicity: Hevo has a point and clicks interface that is super intuitive to use. With Hevo, you can start loading data from any data source to Redshift in a jiffy without any engineer assistance.
• Reliable and Consistent Data Load: Hevo’s fault-tolerant architecture ensures that your data is loaded consistently and reliably without any data loss.
• Transformations: Hevo provides preload transformations through Python code. It also allows you to run transformation code for each event in the pipelines you set up.
• Connectors: Hevo supports 100+ integrations to SaaS platforms, files, databases, analytics, and BI tools.
• Real-time Data: Hevo’s real-time streaming architecture ensures that you move data from Twitter ads to Redshift instantly, without any delay.
• Scalability: Hevo is built to handle data of any scale. Additionally, Hevo can bring data from a wide range of data sources (sales and marketing applications, analytics applications, databases, and more). Both these aspects ensure that Hevo can help you scale your data infrastructure as your data needs grow.

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