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. A Promoted Trend will show up as one of the first spots under the “Trends for You” section. This can be seen both in the Explore tab and in the timeline.
• 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. These are labeled as “Promoted” and include a follow button for prospects.
• Promoted Tweets: Promoted Tweets are different from regular tweets since an advertiser is paying to display the content to prospective clients on Twitter. These tweets will be labeled as an “ad” and there will be a little “Promoted” text in the bottom left-hand corner.

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. Data Compression, Columnar Storage, and Zone Maps considerably reduce the amount of I/O needed to perform queries.
• 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

There are two methods that you can leverage to establish a connection from Twitter Ads to Redshift in a seamless fashion. These methods are as follows:

• Method 1: Using Twitter Ads API to connect Twitter Ads to Redshift
• Method 2: Using Hevo Data to connect Twitter Ads to Redshift

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

Twitter Ads APIs are a set of APIs to enable organizations to programmatically manage their advertising campaigns. Understanding the hierarchy of related entities is a prerequisite for making sense of the Twitter Ads API. The highest entity here is the advertising account. Each advertising account has funding instruments, which are different sources of funding that the advertiser has set up for campaigns.

Below funding instruments come campaigns. Campaigns consist of line items, which are basically advertising groups. Advertising groups or line items can be used to logically segregate advertising campaigns under the same account. Line items contain targeting criteria, promoted accounts, promoted tweets, and media creatives. 

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.

Since this is based on OAuth, the user will have to allow access to the application by pasting a URL in the browser and then logging in there. Let’s first go through this process. Assuming you have twirl already installed in your instance, execute the below command.

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. The first step in getting analytics data is to get a list of active entities for which engagement metrics changed in the relevant time window. For this execute the below command using twurl.

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. The process involved multiple convoluted steps to authenticate, get the relevant active entities, and usage of multiple utilities to fetch the JSON and then process it. And it does not end there. Taking this to production will be an even more herculean task because of the other complications involved. Let us take a look at the typical challenges in using this approach to implement a data load to 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.
• Developing such an application will require the developer to be familiar with the complexities of Twitter Ads APIs as well as Redshift knowledge.
• Transforming data before inserting it into a data warehouse is a usual practice in such workflows. We will need additional steps to cover this. 

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 a variety of sources in a matter of few clicks. 

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

Hevo is an automated data pipeline that provides an easy-to-use, cost-free User Interface with the ability to copy data from Twitter Ads to Redshift without writing any code. Hevo enables the lowest time to production for such copy operations, allowing developers to focus on their core business logic rather than waste time on the configuration nightmares involved in setting these up.

Get Started with Hevo for free

Hevo can help you move data from Twitter Ads to Redshift in 2 easy steps:

• Step 1: Set up and configure your Intercom platform by providing a suitable Pipeline Name and a few tokens and keys that you created, mainly Consumer API Key, Consumer API Secret, Access Token, and Access Token Secret to move data from Hevo Data to Intercom.

• Step 2: Load data from Intercom to Redshift by providing your Redshift databases credentials like Database Port, Username, Password, Name, Schema, and Cluster Identifier along with the Destination Name.

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. You need to edit the event object’s properties received in the transform method as a parameter to carry out the transformation. Hevo also offers drag and drop transformations like Date and Control Functions, JSON, and Event Manipulation to name a few. These can be configured and tested before putting them to use.
• Connectors: Hevo supports 100+ integrations to SaaS platforms, files, databases, analytics, and BI tools. It supports various destinations including Google BigQuery, Amazon Redshift, Snowflake Data Warehouses; Amazon S3 Data Lakes; and MySQL, MongoDB, TokuDB, DynamoDB, PostgreSQL databases to name a few. 
• Real-time Data: Hevo’s real-time streaming architecture ensures that you move data from Twitter ads to Redshift instantly, without any delay. This allows you to gain meaningful insights into your advertising data in real-time.
• 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.

Sign up here for a 14-day Free Trial!

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. Hevo is fully automated and hence does not require you to code. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Visit our Website to Explore Hevo

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

What is your preferred approach to move data from Twitter Ads to Redshift? Let us know in the comments.