Twitter Ads to Snowflake: Easily Export Data in Minutes

on Data Integration, Tutorials • May 19th, 2020 • Write for Hevo

Twitter Ads to Snowflake

Twitter Ads is one of the most widely used social advertising tools that help businesses promote their products and services online. It helps organizations reach out and target customers according to business needs. However, to use this information and gain meaningful insights, a deeper analysis has to be run on the obtained data points. For this purpose, moving data to a centralized data warehouse such as Snowflake is a step in the right direction. This blog post is aimed at introducing you to two approaches, that can be used to move data from Twitter Ads to Snowflake.

Methods of Moving Data from Twitter Ads to Snowflake

Various methods can be used to export data from Twitter ads to Snowflake. In this article, you will explore the following approaches:

  1. Building a Custom Application using Twitter Ads API:
    You can choose to employ this approach if there are developer resources with working knowledge of RESTful APIs are at your disposal. Twitter provides access to a set of APIs for data extraction. Firstly, you need to authorize your Twitter Ads account. Next, you would need to fetch a list of entities and metric values using API calls. Finally, the data is transferred to Snowflake with the help of appropriate commands.
  2. Using a No-code Data Pipeline, Hevo:
    Hevo, an official Snowflake Partner, allows seamless transfer of data within minutes from Twitter Ads to Snowflake. You would not need to invest in engineering bandwidth or work with custom APIs. Hevo provides a hassle-free user experience without having to write a single line of code. Explore a 14-day free trial here.

This blog post includes a detailed explanation of the above approaches. Here is a summary of what the post is going to cover.

  1. Understanding Twitter Ads
  2. Understanding Snowflake
  3. Building an Application with Custom Code using Twitter Ads API
  4. Challenges Involved in Building a Custom Application
  5. Loading Data using a No-code Data Pipeline

Understanding Twitter Ads

Twitter Ads is an advertising platform owned and operated by Twitter Inc.  It allows you to connect with an audience globally and get action-driven results with a goal to add value to your business. You can create objective-based campaigns, analyze your performance and reach the right target audience using Twitter ads. The ads can be categorised into three types:

  • Promoted trends – This is a topic sponsored on top of the trending news section. It is supposed to be the most discussed topic. 
  • Promoted accounts – These are accounts put on top of the suggestion box. The main goal is to gain more followers on Twitter. 
  • Promoted tweets – These are tweets that appear first in a search result.

To learn more about Twitter ads, click here.

Understanding Snowflake

Snowflake is a SaaS (Software as a Service) data warehouse that is easy to use, fast, and flexible than the traditional databases. It uses a new database engine designed for the cloud. Snowflake allows you to query any standard SQL datatype such as VARCHAR, TIMESTAMP, etc. Apart from having many similarities to enterprise data warehouses, it has many unique functionalities and capabilities. 

Snowflake runs on public cloud infrastructure. Its architecture is a hybrid of traditional shared disk database and shared-nothing architectures. So, it uses a central data storage that is accessible from all nodes in the warehouse. 

Read more on Snowflake here.

Method 1: Building an Application with Custom Code using Twitter Ads API

The steps involved in implementing the Twitter Ads to MySQL database migration process are:

  1. Identifying the Prerequisites
  2. Step 1: Fetching Data from Twitter Ads
  3. Step 2: Transforming and Preparing Twitter Ads Data
  4. Step 3: Loading Twitter Ads Data into Snowflake

Prerequisites

To successfully perform the data transfer from Twitter Ads to Snowflake, you would need the following:

  1. Working knowledge of RESTful APIs.
  2. Access to consumer key and consumer secret to authorize your Twitter app and Twitter account. This can be obtained by entering the details of the application here: https://dev.twitter.com/apps/new
  3. A successfully set-up Snowflake data warehouse.

Step 1: Fetching Data from Twitter Ads

The Twitter Ads API program allows you to create, run and manage Twitter Ads programmatically. One of the rich features of Twitter API is the reporting system. The API allows you to retrieve information about the current account. This includes data on:  

  1. Campaigns.
  2. Features.
  3. Promoted tweets and promoted accounts.
  4. Funding instruments, and much more. 

You also need to understand the following when dealing with Twitter API:

  • Rate Limits: There is no restriction for concurrent API calls but there is a 15 minutes window restriction of API calls per endpoint. 
  • Authentication: OAuth is used to authenticate Twitter Ads.
  • Pagination: Some sources have pagination ability for retrieving data with a page count varying from 200 – 1000. This depends on specific endpoint resources. In some sources, there is a sorting method for retrieving data.

The endpoint below helps you to fetch promoted tweets.

https://ads-api.twitter.com/7/accounts/:account_id/promoted_tweets

The response is in JSON format. This is shown below.

{
  "request": {
    "params": {
      "promoted_tweet_ids": [
        "1efwlo"
      ],
      "account_id": "18ce54d4x5t"
    }
  },
  "next_cursor": null,
  "data": [
    {
      "line_item_id": "96uzp",
      "id": "1efwlo",
      "entity_status": "ACTIVE",
      "created_at": "2017-06-29T05:06:57Z",
      "updated_at": "2017-06-29T05:08:46Z",
      "approval_status": "ACCEPTED",
      "tweet_id": "880290790664060928",
      "deleted": false
    }
  ]
}

Source: https://developer.twitter.com/en/docs/ads/campaign-management/api-reference/promoted-tweets

Step 2: Transforming and Preparing Twitter Ads Data

Create a data schema to store Twitter Ads data. Snowflake supports different data types such as INTEGER, BOOLEAN, DATETIME, etc. Build a table to receive each value in the API response data. If the response data is in JSON format, load the data directly into Snowflake. 

Step 3: Loading Twitter Ads Data into Snowflake

Data is usually loaded into Snowflake in a bulky way using the COPY INTO command. The files containing JSON data are stored in the local file system or in the amazon s3 bucket. Data is then copied into the Snowflake by invoking the COPY INTO command 

Also, you can upload Twitter ads data into a data stage such as Amazon s3 or Microsoft Azure or GCS from where Snowflake can access them directly. 

Building a Custom Application using Twitter Ads API: Limitations and Challenges

  1. Infrastructure Maintenance: Twitter has a rich set of APIs. These APIs may be updated at any given time. So, you will need to invest in a team of engineers to constantly monitor and maintain the ETL code. 
  2. Real-time Data: We have successfully created a program that loads data from Twitter Ads to Snowflake. However, the program does not load data in real-time. To solve this challenge, you will need to write additional code.
  3. Inability to Transform Data: 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: Loading Data using a No-code Data Pipeline, Hevo

Hevo, an official Snowflake Partner for Data Integration, provides a simpler and hassle-free approach to load data from Twitter Ads to snowflake. Since it is a fully managed platform, Hevo eliminates the need to constantly monitor the transfer of data. Hevo will take care of all the grunt work, allowing you to devote your resources to other prioritised tasks. With Hevo, the data load from Twitter Ads to Snowflake can be achieved in two easy steps.

Step 1: Authenticate and Configure your Twitter Data Source

Twitter Ads configuration

Step 2: Connect the Snowflake Data Warehouse to Move Data

That seems like a piece of cake, isn’t it? You can now relax and witness Hevo take care of the data transfer for you.

More Reasons to Try Hevo

  1. No Data Loss: Data is moved reliably using the Hevo platform. It has a fault-tolerant architecture that ensures no data is lost. 
  2. 100’s of Out of the Box Integrations: In addition to Twitter ads,  Databases, SDKs and Cloud Applications are also transferred into Snowflake. So, all your growing data needs will be solved by the Hevo platform.
  3. Minimal Setup: Set up is easy and requires very minimal effort. 
  4. Automatic Schema Detection and Mapping: Hevo scans the schema of incoming Twitter ads data. When changes are detected, Hevo handles them and incorporate the changes in Snowflake. 
  5. Exceptional Support: A 24/7 technical support over call, email, and chat so that you always have someone to resolve your queries at the hour of need.

Hevo has a solution to all your data integration needs. Would you be willing to give it a whirl? Get started by signing up for a 14-day free trial here.

All your customer data in one place.

Continue Reading