Twitter Ads to PostgreSQL: 2 Easy Methods

Companies advertising their products and services through Twitter often need to keep track of the way people engage with their campaigns. To make this possible, Twitter Ads provides a Visual Analytics Dashboard inside their platform. However, often, businesses would look to extract this data from Twitter Ads and load it onto a Database such as PostgreSQL. They can now combine this data with data from many other sources to derive deep, meaningful insights.

This article explains 2 step by step methods of connecting Twitter Ads to PostgreSQL. It will first provide you a brief introduction of Twitter Ads and PostgreSQL and then the article will elaborate on the 2 methods. Read along to find which method suits you best for setting up Twitter Ads to PostgreSQL.

Prerequisites

• Twurl should be installed on your system.
• You should have the access to consumer keys and consumer secrets to authorize your Twitter app and Twitter account.
• A successfully set up PostgreSQL database.

Introduction to Twitter Ads

Before building a custom module to fetch the data from Twitter Ads, you will need to have an understanding of the basics of Twitter Ads API. Twitter Ads API contains four major sets of APIs: 

• Analytics APIs: Analytics APIs help the advertising account to measure the Retweets, Likes, and Impressions. This API will be the focal point of discussion in this article. 
• Audience APIs: Audience APIs help the developers programmatically manage the target audience.
• Campaign Management APIs: This APIs help in managing the creative contents of the campaign. 
• Measurement APIs:  Measurement APIs are used to track the conversions i.e the number of users who decide to use or subscribe to your product or service. 

All the above APIs are rate-limited. Twitter Ads work based on a hierarchy of entities. The topmost object is the Advertising Account, below which comes the funding instruments that the account owners can use to set up various campaigns. Each funding instrument can have multiple campaigns. Each campaign has got line items – which are nothing but advertising groups to logically segregate the activities. Line items constitute Promoted Tweets, Promoted Accounts, Media Creatives, and Targeting Criteria.

Introduction to PostgreSQL

PostgreSQL is a powerful open-source Object-Relational Database. It not only uses the SQL language but also adds many features to it, allowing you to store and expand very complex data workloads. It has a reputation for proven Architecture, Data Integrity, Reliability, and Scalability. Powerful features and commitment to a rich open source community are some aspects of this Database.

Due to its Robustness and Feature-Rich Query Layer, PostgreSQL is very popular in use cases that traditionally require strict table structure. It has been developed for more than 30 years and most of the major problems have been solved. This fact makes people confident in using it in a business environment

To learn more about PostgreSQL, visit here.

Methods to Set up Twitter Ads to PostgreSQL Integration

You can implement the following two methods to set up your Twitter Ads to PostgreSQL Integration:

• Method 1: Manual ETL Process to Set up Twitter Ads to PostgreSQL Integration
• Method 2: Using Hevo Data to Set up Twitter Ads to PostgreSQL Integration

Method 1: Manual ETL Process to Set up Twitter Ads to PostgreSQL Integration

Following are the steps of implementing a custom code for data migration from Twitter Ads to PostgreSQL database:

• Step 1: Authorise your Twitter Account
• Step 2: Fetch a List of Twitter Ads Entities
• Step 3: Fetch Metric Values from Twitter Ads
• Step 4: Load Data from Twitter Ads to PostgreSQL Database

Step 1: Authorise your Twitter Account

The first step in Twitter Ads to PostgreSQL is to use Twitter APIs. Twitter APIs use OAuth as the authentication protocol. This means the developers have to implement the three-legged web UI-based protocol to get access. We will be using a utility provided by Twitter called Twurl to manage the OAuth flow. This utility helps us to access the API without having to build a web browser redirect, but it does involve manual effort. 

Using Twurl, execute the below command:

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

The above command will output a URL that the user needs to paste into the web browser and follow directions to authenticate with Twitter. The result of this process will be a 7 digit pin which is to be entered into the shell where Twurl command was executed. Successfully entering the pin will complete the authentication process for Twurl.

Step 2: Fetch a List of Twitter Ads Entities

The next step is to use Twurl to access Analytics APIs. As mentioned in the beginning, Twitter API works based on entities. Entities are nothing but promoted tweets or creative content. Knowing the ids of entities is critical to access the Impression, Likes, and Retweets of individual entities. Let’s first get a list of promoted tweets for which analytics metrics have changed for a specific time window of 24 hours. 

twurl -H ads-api.twitter.com "/6/stats/accounts/234o4564d45t/active_entities?entity=PROMOTED_TWEET&start_time=2020-04-02T00:00:00Z&end_time=2020-04-03T00:00:00Z"

Please note that the start_time and end_time are adjusted to reflect a 24-hour window and entity type is provided as PROMOTED_TWEET. Also, note the advertising account identifier – 234o4564d45t that is part of the URL. 

The response will be a JSON as shown below:

{
  "request": {
    "params": {
      "account_id": "18co4564d45t",
      "entity": "PROMOTED_TWEET",
      "start_time": "2020-04-02T00:00:00Z",
      "end_time": "2020-04-03T00:00:00Z"
    }
  },
  "data": [
    {
      "entity_id": "2r3xyn",
      "activity_start_time": "2020-04-02T21:55:20Z",
      "activity_end_time": "2020-04-03T04:43:56Z",
      "placements": [
        "ALL_ON_TWITTER"
      ]
    },
    {
      "entity_id": "4z31fy",
      "activity_start_time": "2020-04-03T09:11:18Z",
      "activity_end_time": "2020-04-03T15:42:59Z",
      "placements": [
        "ALL_ON_TWITTER",
        "PUBLISHER_NETWORK"
      ]
    }
  ]
}

The JSON output indicates that two entities have changed in the time frame we have considered – 2r3xyn and 4z31fy. We will be using these entity identifiers in the next request. 

Step 3: Fetch Metric Values from Twitter Ads

The next step is to use the above entity identifiers to fetch the Impressions, Retweets, and Likes. As in the above step, the API returns a complicated JSON. In this step, we will use a command-line utility called jq to process this JSON and convert it into a CSV file. Use the below command to execute this.

twurl -H https://ads-api.twitter.com/7/stats/accounts/18ce54d4x5t?entity=LINE_ITEM&entity_ids=2r3xyn,4z31fy&start_time=2020-04-02&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" >> twitter_metrics.csv

The above command uses Twurl to access the API, pipes the output to jq which parses the JSON, and extracts Impressions, Likes, and Clicks into CSV. The CSV will look as below:

"2r3xyn",196,13,45
"4z31fy",1311,1,28

Step 4: Load Data from Twitter Ads to PostgreSQL Database

Assuming a PostgreSQL table named ‘metrics’ is already created with columns for Entity_id, Impressions, Likes, and Clicks, use the below command to load the Twitter Ads data to PostgreSQL.

COPY metrics(entity_id,impressions,likes,clicks)
FROM 'home/user/twitter_metrics.csv' DELIMITER ',';

That concludes the set of steps. It is to be noted that the above set of steps is a simplified version of what actually needs to be done to implement such a load process in production. The above approach is meant to be taken as a starting point to build such an application. 

In reality, there will be several challenges in implementing this as a stable process. Let’s look into some of them below.

Challenges with the Manual ETL Process

• The first challenge to overcome is the rate limits imposed by Twitter. The application should keep track of the remaining requests using rate status API and schedule the fetches accordingly.
• Analytics API only supports up to 20 entity ids in one request. So the application has to execute this in a phased manner if there are more entities.
• Analytics API does not support the synchronous execution of requests for larger time windows. In such a scenario, the application should spawn a job through the API and keep track of the job status and then fetch the results.
• In most cases, organizations will need continuous sync of Twitter API data in their Database. To accomplish this, additional logic to handle updates and duplicate entries have to be built.
• The developer needs to be proficient with Twitter APIs complex JSON structure for a multitude of APIs as well as how synchronous and asynchronous APIs are implemented to complete this process. The learning curve here is very steep.

An alternative to finding solutions to all the above problems is to use a completely managed platform like Hevo, that gets the same job done without writing any code. Hevo is tailor-made to implement the data load process from Twitter Ads APIs to a variety of destinations.

Method 2: Using Hevo Data to Set up Twitter Ads to PostgreSQL Integration

Hevo Data, a No-code Data Pipeline, helps you directly transfer data from Twitter Ads and 100+ other data sources to Databases such as PostgreSQL, SQL Server, Data Warehouses, BI tools, or a destination of your choice in a completely hassle-free & automated manner. Hevo is fully managed and completely automates the process of not only loading data from your desired source 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. It helps transfer data from Twitter Ads to a destination of your choice for free.

Hevo Data takes care of all your Data Preprocessing needs and lets you focus on key business activities and draw a much powerful insight on how to generate more leads, retain customers, and take your business to new heights of profitability. It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. 

Hevo Data, an official PostgreSQL partner, offers you seamless data migration from Twitter Ads to PostgreSQL in two very simple steps: 

• Authenticate Source: Authenticate and configure your Twitter Ads as the data source.

• Configure Destination: Connect the PostgreSQL Database as the destination.

Advantages of Using Hevo Data

There are several reasons why Hevo Data has to be your one-stop destination for all data integration needs. Some of them are listed below.

1. Minimal Setup: The time spent setting up Hevo is much less compared to building the setup yourself.
2. Automatic Schema Detection and Mapping: Hevo automatically scans the incoming Twitter Ads schema. Hevo smoothly incorporates schema changes in PostgreSQL, should there be any changes.
3. Fully Managed Solution: Hevo is a fully managed data solution that relieves users from all burdens of maintenance.
4. Automatic Real-time Recurring Data Transfer: Data is synced in real-time from Twitter to PostgreSQL on Hevo. This ensures you always have the most recent data in your database.
5. No Data Loss: The risk-tolerant architecture of Hevo ensures that the data loss is nil while moving from Twitter Ads to PostgreSQL.
6. Added Integration Option: In addition to Twitter Ads, Hevo integrates with several Databases, Sales and Marketing Applications, Analytics platforms, etc. This makes Hevo the right platform to cater to your growing data integration needs.
7. Strong Customer Support: Hevo team guarantees you have 24×7 support over call, email, and chat.
8. Ability to Transform Data: Hevo lets you transform your data both before and after transferring it to PostgreSQL. This ensures that your data in PostgreSQL is always ready for analysis.

Conclusion

This article introduced you to the features of Twitter Ads and PostgreSQL. Furthermore, it explained the 2 methods using which you can connect Twitter Ads to PostgreSQL. While implementing the manual ETL method you will face certain challenges which are also explained in the article.

Visit our Website to Explore Hevo

Hevo Data provides a graphical user interface to easily move data free of cost from Twitter Ads(among 30+ free sources) to PostgreSQL in real-time. It enables the lowest time to production without having to write a single piece of code, allowing you to focus on core business decisions. With Hevo’s out-of-the-box integration with Twitter and PostgreSQL, you can achieve data migration seamlessly. It helps transfer data from Twitter Ads to a destination of your choice for free.

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 method to move data from Twitter Ads to PostgreSQL? Leave your thoughts in the comments section below.