Twitter Ads to Snowflake Migration: 2 Easy Methods

Twitter Ads provides an effective advertising channel that allows businesses to reach out to their potential customers through Twitter’s vast network. It enables this by providing an accessible medium to run and manage Targeted Ad Campaigns in engaging and visual forms.

Analyzing the data generated through these Campaigns can help businesses understand how to fine-tune and effectively optimize their Marketing efforts. As a result, many businesses choose to export data from Twitter Ads to Data Warehouses such as Snowflake in order to gain mission-critical insights. This article will help you understand the two methods that can help you transfer data from Twitter Ads to Snowflake.

Introduction to Twitter

Twitter is a Social Networking Microblogging website using which people can communicate with each other in the form of short messages called Tweets. A Tweet can be defined as a short message that can be seen by anyone who follows you on Twitter, with the hope that your words are useful and interesting to someone in your audience.

The primary purpose of Twitter is to connect people to each other and allow them to share their thoughts with a vast audience. Twitter now allows users to discover stories regarding the biggest events and news across the world, follow people or companies that post content they enjoy consuming or communicate with friends. 

Additionally, Marketers also use Twitter to increase brand awareness and delight their audience. They can do so either using their Twitter account or by leveraging Twitter Ads. Twitter Ads are seen as the perfect component of the organic content strategy within a business. Twitter Ads allows businesses to promote their content by creating Marketing Campaigns that will appear as Promoted Ads or Follower Ads. This content is then exposed to a broad audience for increased exposure and visibility.

More information about Twitter can be found here, and Twitter Ads can be found here.

Understanding the Key Features of Twitter Ads

The key features of Twitter Ads are as follows:

• Performance-Based Pay: Users promoting their content on Twitter only pay once they’ve achieved their Marketing objective. Whether the goal is Twitter Feed Engagement or getting the audience to visit the official website, users only pay as people take that action. Along with that, any additional engagements or organic impressions are considered as free bonuses.
• Tweet Engager Targetting: Tweet Engager Targeting is a popular way to reach your audience in a remarkably targeted manner. With this form of Advertising, users remarket their content to people who recently saw or engaged with their content on Twitter.
• Low Cost Per Click: Currently, advertisers find it very cheap to market their content on Twitter because of the lack of advertising competition. Since the Cost Per Click (CPC) is auction-based, advertisers do not have to pay much to promote their content.

Introduction to Snowflake

Snowflake is one of the most popular enterprise-grade Cloud-based Data Warehouses that brings simplicity to its users without reducing the features being offered in any way. It is capable of automatically scaling resources up and down based on data requirements to ensure that users get the right balance of performance and cost.

Snowflake is now widely used by a large number of businesses primarily because it separates Compute Nodes from Storage Nodes. This is considered to be one of the most significant advantages of using Snowflake since all other databases, or Data Warehouses offer Compute and Storage Nodes together as a single bundle. Earlier, businesses had to acquire resources based on the largest workload and incur its cost. But with Snowflake, they can choose to pay only for the resources they use.

More information on Snowflake can be found here.

Understanding the Key Features of Snowflake

The key features of Snowflake are as follows:

• Advanced Scalability: Snowflake houses functionalities that allow users to practically create an unlimited number of Virtual Warehouses with each one running its workload against the data in its database.
• Robust Security: Snowflake ensures that all data stored in its Data Warehouses is secure by implementing a wide variety of industry-leading and powerful security features, such as Multi-factor Authentication, Automatic 256-bit AES Encryption, etc. Snowflake is also compliant with numerous enterprise-grade data security standards such as PCI DSS, HIPAA, SOC 1, and SOC 2.
• Automated Performance Tuning: Snowflake offers its users an Automatic Query Performance Optimization mechanism backed by a robust Dynamic Query Optimization Engine in their Cloud Services Layer.

Methods to Set up Twitter Ads to Snowflake Migration

Users can set up Twitter Ads to Snowflake Migration by implementing one of the following methods:

• Method 1: Manual Twitter Ads to Snowflake Migration
• Method 2: Twitter Ads to Snowflake Migration Using Hevo Data

Method 1: Manual Twitter Ads to Snowflake Migration

The steps involved in executing a data migration process from Twitter Ads to Snowflake database are:

• Step 1: Executing Twurl Authentication Command
• Step 2: Fetching List of Twitter Ads Entities
• Step 3: Fetching Metric Values from Twitter Ads
• Step 4: Loading Twitter Ads Data into Snowflake

Step 1: Executing Twurl Authentication Command

• Assuming Twurl is already installed and set up, execute the following command to authenticate your Twitter Ads account:

twurl authorize --consumer-key twitter_key --consumer-secret twitter_secret

• Once the above command is executed, you will see a URL in the shell.
• Copy this URL and paste it into the Web Browser.
• It will generate a Pin Code that has to be copied from the Web Browser and pasted in Twurl shell to complete the process.

Step 2: Fetching List of Twitter Ads Entities

Before executing the next step, you need to understand the basics of how the Twitter Analytics API works. This API works on the basis of a hierarchy of Entities. The first step in fetching metrics of any Entity is to have information about the identifier of that Entity. The Entity could be a Promoted Tweet, a Promoted Account, or even Creative Media content.

An Entity Identifier is usually obtained when an Entity is created. You can easily get a list of all the Entities that were changed in the last 24 hours for your understanding. The API’s start_time and end_time parameters will help you get this. Execute the following command to get the list of Entities:

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

In the above command, the API is trying to fetch the list of Promoted Tweets, for which engagement metrics have changed in the 24 hours from 8 April 2020 to 9 April 2020.

The result will be in JSON format similar to the following:

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

In the resultant JSON, you will be can see two Entity Identifiers – 2r1byn and 4z41qy. You can now fetch the engagement metrics for these Entities.

Step 3: Fetching Metric Values from Twitter Ads

You can now use a command-line utility called jq to parse this JSON and extract the relevant metric values. In this case, only impressions, likes, and clicks are being extracted. The following command will access the API, pipe the output through jq and create a CSV file that can be loaded to Snowflake:

twurl -H https://ads-api.twitter.com/7/stats/accounts/18ce54d4x5t?entity=LINE_ITEM&entity_ids=2r1byn,4z41qy&start_time=2020-04-08&end_time=2020-04-15&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" >> tweet_engagement.csv

In the above step, the API is fetching the engagement metrics for 7 days from 8 to 15 April 2020.

The result of this command will be a CSV file similar to the following:

"2r1byn",106,19,41
"4z41qy",1008,3,22

Step 4: Loading Twitter Ads Data into Snowflake

You can load CSV data into Snowflake by creating a Snowflake Stage and loading data from that stage into the Snowflake table. You can easily complete this process by implementing the following steps:

• Create a Snowflake stage by executing the following query:

create or replace stage twitter_ads_stage;

• Create a file format using the FILE FORMAT command to describe the format of the file to be imported by executing the following query:

create or replace file format twitter_ads_format type = 'csv' field_delimiter = ',';

• Upload the CSV containing the Twitter Ads data using the PUT command by executing the following query:

-- For Windows
put file://C:test/Twitter_Ads.csv @twitter_ads_stage;

-- Linux/Mac
put file:///tmp/data/Twitter_Ads.csv @twitter_ads_stage;

• Create a table in Snowflake in which all Twitter Ads data will be stored using the CREATE command by executing the following query:

create or replace table twitter_ads (
  id varchar(100),
  impressions integer,
  metrics integer,
  clicks integer
)

• Load data from the Snowflake Stage into the table using the COPY INTO command by executing the following query:

copy into test.twitter_ads from @twitter_ads_stage;

Limitations of Manual Twitter Ads to Snowflake Migration

The limitations of manually setting up Twitter Ads to Snowflake Migration are as follows:

• The Analytics API returns results only for a maximum of 20 Entities at one time. If there are more than 20 Entities, the application logic will have to execute this in a phased manner. 
• The API is rate-limited. So the application logic should have a provision to keep track of Twitter Rate Status API and spawn requests without interfering with Twitter Rate Limits.
• Analytics API returns result synchronously only for smaller time windows. For time windows of more than seven days, asynchronous APIs have to be used. 
• The most typical requirement for businesses is to have this data continuously synced to their databases. This means a production execution should have scheduling capabilities as well as logic to handle updates, duplicates, etc. 
• The learning curve in implementing this is steep. The developer needs to have a complete grasp of all the Twitter Ads API and their complex JSON structures to complete this.
• The above code helps you achieve a straightforward data export process. However, if you need to clean or transform the data before loading to Snowflake, this is not achievable using the above approach. You will need to write additional code for the same.

Method 2: Twitter Ads to Snowflake Migration Using Hevo Data

Hevo helps you directly transfer data from Twitter Ads and various other sources to Snowflake, Business Intelligence tools, Data Warehouses, 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.

Sign up here for a 14-Day Free Trial!

Hevo takes care of all your data preprocessing needs required to set up Twitter Ads to Snowflake Migration 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. 

The following steps can be implemented to set up Twitter Ads to Snowflake Migration using Hevo:

• Configure Source: Connect Hevo Data with Twitter Ads by providing a unique name for your Pipeline, along with details about your Twitter Ads account such as the API Key, API Secret Key, etc.

• Integrate Data: Complete Twitter Ads to Snowflake migration by providing information about your Snowflake database and its credentials such as database name, username, and password, along with information about Schema associated with your Snowflake database.

Conclusion

This article provided you with a step-by-step guide on how you can set up Twitter Ads to Snowflake Migration manually or using Hevo. However, there are certain limitations associated with the manual method. If those limitations are not a concern to your operations, then using it is the best option but if it is, then you should consider using automated Data Integration platforms like Hevo.

Visit our Website to Explore Hevo

Hevo helps you directly transfer data from a source of your choice to a Data Warehouse, Business Intelligence, or desired destination in a fully automated and secure manner without having to write the code. It will make your life easier and make data migration hassle-free. It is User-Friendly, Reliable, and Secure.

Details on Hevo’s pricing can be found here. Sign Up for the 14-day free trial today.