How to Transfer Twitter Ads Data into Google BigQuery
With an audience of over 330 million monthly active users, Twitter is a huge social platform that offers a surprising level of niche targeting of its users. Digital advertisers looking to reach highly specialized audiences in real time as they discuss their interests, trending news, etc. need look no further than Twitter Ads.
With four out of every 10 users reporting they purchased an item after seeing it advertised on Twitter, Youtube, or Instagram (Digital Marketing Institute, 2019), it makes sense that many digital advertisers are recommending that their clients allocate budget to Twitter ads as well.
Twitter’s ad platform is relatively straightforward but limited in some areas such as reporting. For agencies looking for additional tools and technology to manage Twitter campaign ad performance at scale, BigQuery provides a great solution for large-scale data analysis.
Although Google BigQuery doesn’t offer a first-party connector to automatically (and regularly) import Twitter Ads data, it is possible for any digital advertiser to launch their own Twitter Ads data warehouse. Detailed below are three ways any digital advertiser can get Twitter Ads data into BigQuery:
- 1. Manual Twitter Ads Data Upload
- 2. Data Pipelines and Connectors
- 3. Shape’s Advertising Data Infrastructure
Manual Twitter Ads Data Upload into BigQuery
While very manual, it is possible for digital advertisers to build their own Twitter Ads data warehouses without engineering resources or expansive software budgets. Google BigQuery allows you to manually import data from a CSV file directly. This means that any digital advertiser that can download a Twitter Ads report can upload it to their very own data warehouse.
The cost of building this solution manually is affordable. Uploading Twitter Ads data is free although costs accumulate if a digital advertiser suprasses Google’s data querying and storage thresholds. However, manually updating Twitter Ads data regularly across multiple different report types can get cumbersome. Likewise, when manually uploading Twitter Ads data, digital advertisers want to be sure that their reports contain the same data schema (metrics, headings, and data formats) to prevent incomplete or missing data from quickly destroying the usefulness of their data warehouse.
Directions:
1. Generate a Google Cloud Project (free trial available).
2. You’ll be taken to a “Basic Checklist.” (You’ve already completed steps 1 and 2 listed.)
3. Navigate to Google BigQuery and find your new project. Note: a generic project name is created for you. You can edit the project name in the Settings tab.
4. Login to Twitter Ads and navigate to the client you wish to analyze.
- a. Choose the level at which you’d like to see your data (campaign, ad group, ad, or audience)
- b. Choose from preset “reports” using the Metrics menu or customize the metrics you wish to see.
- c. Set your date range and implement any additional filters you’d like to add.
- d. Click Export.
- e. On the Export screen, confirm your report settings. Set a time breakdown and add any additional metrics or audiences you’d like to include in the report.
- f. Click Download and save this file to your computer.
- g. **The report will download as an Excel .XLSX file. Convert this to a .CSV.**
5. Navigate back to Google BigQuery. Ensure your project is selected at the top of the screen and then click your project ID in the left-hand navigation.
6. Click “+Create Dataset”
7. Name your dataset. This should be representative of the entire dataset you plan to include (ex: “client_account_twitter_ads”).
8. Ensure an encryption method is set then click “Create dataset.”
9. Click the name of your new dataset in the left-hand navigation and click “+Create Table”.
10. Navigate to the Source section.
- a. Create table from: Upload
- b. Select file: Find your Twitter Ads report you saved using the browse button
- c. File format: CSV
11. Navigate to the Destination section.
- a. Select “Search for a project.”
- b. Find your project name from the dropdown. Then select your dataset name (created in step 7).
- c. Enter a name for your table. This should be representative of the table of data you are uploading (ex: campaign_data_segmented_by_month)
12. Navigate to the Schema section.
- a. Click the checkbox to allow BigQuery to auto-detect schema OR
- b. Click “Edit as text” to manually name schema, set type (string, boolean, date, etc.), and mode (nullable, required or repeated).
13. Navigate to the Partition and cluster settings section.
- a. Choose “No partitioning” or “Partition by ingestion time” based on your needs. Partitioning divides your table into smaller segments that allow smaller sections of data to be more quickly and affordably queried.
14. Navigate to Advanced Options:
- a. Set Field delimiter: Comma
- b. If your data includes a header row, enter 1 in the “Header Rows to skip” field.
15. Click “Create table.” Your data warehouse will now begin to populate with Twitter Ads data. Check your Job History for the status of your data load.
16. Navigate to Google BigQuery and click your Dataset ID.
17. You can now start writing SQL queries against your Twitter Ads data in Google BigQuery, or export your data to Google Data Studio and other third-party tools for further analysis.
18. Repeat this process for all additional Twitter Ads data sets you wish to upload.
19. Repeat this process on a regular basis to ensure fresh data is available when necessary.
Google offers fantastic guides on manually uploading data to Google BigQuery or using JSON, as well as extensive support documentation.
Using Data Pipelines/Connectors to Get Twitter Ads Data into BigQuery
Another alternative to populate a Google BigQuery data warehouse with Twitter Ads data is to use a third-party data pipeline or connector. Several solutions exist such as Supermetrics, Funnel, and Improvado. With each of these solutions, the advertiser is responsible for launching, managing, and paying for the data warehouse. The connectors/pipelines transfer Twitter Ads data to the data warehouse on a preset basis. Depending on the solution, pricing may fluctuate based on the number of Twitter Ads accounts and/or data sources linked, the frequency or size of data transfers, or the level of support needed.
Although we can’t cover spin up directions for each connector individually, we’ve selected SuperMetrics for BigQuery given that Supermetrics is used by many PPC advertisers.
Directions:
1. Sign up for a free trial of Supermetrics for BigQuery (note: there is a free trial but access may not be granted immediately).
2. Add your Twitter Ads data sources to Supermetrics.
3. Generate a Google Cloud Project (free trial available).
4. You’ll be taken to a “Basic Checklist.” You’ve already completed steps 1 and 2. Click step 3: find “Product + API.” This takes you to the Google Cloud Platform Marketplace.
5. Search/select “Twitter Ads by Supermetrics.” Click Enroll.
6. Select the name of the project you generated in steps 3 & 4. Note: a generic project name is created for you. You can edit the project name in the Settings tab.
7. Navigate to BigQuery and click your project name on the left-hand navigation.
8. Click “Create Dataset.” Fill out a dataset ID and location as needed. Save.
9. Using the left-hand navigation again, click Transfers. Select +Create Transfer.
10. You’ll be asked to configure the transfer form.
11. Set the Source to “Twitter Ads by Supermetrics”
12. Set Transfer Config Name = Name this something that indicates the job being run. (Ex: Client Name Twitter Ads Transfer)
13. Set Schedule Options.
- a. Select what time you’d like data refreshed (Start Now or Start at set time)
- b. Select if you want data refreshed on a schedule or on-demand
- c. Select the start date and run time when data will be refreshed
14. Enter a Dataset ID = Choose (or create) a name for your dataset displayed in BigQuery.
- a. If creating a new Dataset ID, follow the steps on the “Create Dataset” form to add an ID name, data location, table expiration time, and encryption key. Give a clear indication of what’s included in the dataset (ex: Client Name Twitter Ads).
15. Under the Third-Party Connection section, click “Connect Source”. Follow steps to authorize Supermetrics as a third-party connector.
16. Using the Supermetrics pop up modal, sign in to Supermetrics for BigQuery. Authorize the Twitter Ads login as needed.
17. Select the ad accounts you wish to transfer data from and submit.
18. Your data warehouse will now begin to populate with data. Once the data has successfully been run, you can navigate back to BigQuery to access your data.
19. IMPORTANT: To backfill historical data, you’ll need to follow additional steps outlined in Supermetrics support documentation.
Using Shape’s Advertising Data Infrastructure to get Twitter Ads Data into BigQuery
We designed the Shape ADI to be the fastest and easiest way for digital advertisers to generate and populate a new BigQuery data warehouse with Twitter Ads data. Shape manages the Google Cloud Platform and dataset spinups, the data transfer, and even backfills data for 30 days (more can be requested). Likewise, Shape’s ADI makes it easy to access and analyze cross-client data as opposed to uploading and querying each client individually.
Everyone who spins up an ADI gets access to five campaign-level data views for free, and Shape handles the cost of data storage and queries. If advertisers want to enable premium views, pricing scales based on the number of views enabled (instead of on the number of data sources or ad accounts needed which can get expensive fast for agencies).
Directions:
2. Add your Twitter Ads data sources during the onboarding process.
- a. Optional: Create Clients and Budgets in Shape. You can combine campaigns into unique groupings to track spend and metrics in whichever combinations you choose.
3. On the main dashboard, click ADI. On the ADI page, click “Enable Warehouse.”
4. Shape will automatically generate a BigQuery data warehouse for you and enable five always-free campaign-level data views.
5. Once your data warehouse has been generated and 30-days worth of data backfilled, you’ll be taken to the Data Warehouse that lists all available data views.
6. Select a View Name to open that view directly in BigQuery OR click the “Open in BigQuery” to access your entire BigQuery data warehouse.
7. Your data warehouse is already populated and backfilled with data. You can query your data in BigQuery or export it to tools such as Google Data Studio and other third-party tools.
8. Optional: If you want to enable premium views, click the checkbox next to a premium view. Click the pencil that appears at the top of the table.
- a. On the “Edit View Status” screen, change the Status to “Enabled.”
- b. Click into the tables tab and use the pencil icon to edit the view. You can change the Updaters to how frequently Shape refreshes data and the length of data the view includes in the refresh.
- c. Click “Save Settings.”
For additional detail on Shape’s ADI including data warehouse support, check out Shape’s Help Center.
Getting Started with Shape’s Managed PPC Data Warehouse
We believe that every digital advertiser should have a powerful data PPC warehouse at her or his fingertips. To help make it easier to access and analyze your data, we have made campaign-level warehousing and API access free.
Looking for more data views or interested to learn more about what you can build with a data warehouse? Don’t hesitate to schedule a free consultation with our engineering team today before diving in.