Apple Search Ads to Snowflake: 2 Easy Ways

As a data engineer, you hold all the cards to make data easily and timely accessible to your business teams. Your marketing & sales teams requested an all-new Apple Search Ads to Snowflake connection on priority to quickly replicate their campaign data. We know you are already overloaded with maintaining existing custom pipelines. With that in mind, you also don’t wanna keep your data scientists and business analysts waiting to get critical business insights. 

As the most direct approach, you can go straight to getting data from Apple Search Ads APIs via a custom code and uploading data as JSON files to Snowflake. Or, hunt for a No-Code Tool that fully automates & manages data integration for you while you focus on your core objectives.

Well, look no further. With this article, get a step-by-step guide to effectively connecting Apple Search Ads to Snowflake and quickly delivering data to your marketing team in 7-nifty minutes.

What is Apple Search Ads?

Apple Search Ads is a platform that allows developers and marketers to promote their apps directly in the App Store. It enables advertisers to target users based on relevant keywords, app category, and demographics, helping them increase app visibility and drive downloads.

Key Features of Apple Search Ads

• Keyword Targeting: Optimize ads for specific keywords to reach the right audience.
• Ad Customization: Create customized ads to reflect app branding.
• Analytics & Reporting: Gain insights through detailed metrics on ad performance.

What is Snowflake?

Snowflake is a cloud-based data warehousing platform that allows organizations to store, manage, and analyze data at scale. It offers high performance and flexibility for data analytics and supports various data types and workloads.

Key Features of Snowflake

• Support for Semi-Structured Data: Handle diverse data formats like JSON, Avro, and Parquet seamlessly.
• Separation of Compute and Storage: Scale resources independently for cost efficiency.
• Data Sharing: Easily share data in real-time with external partners.

How to connect Apple Search Ads to Snowflake?

Replicating data from Apple Search Ads to Snowflake can be a walk in the park, depending on the use case. This article provides you with 2 approaches that simplify the Apple Search Ads to Snowflake data replication process. Without any further ado, let’s get right on it.

Method 1: Manually Loading Data via API and JSON Files

Apple Search Ads Platform provides APIs to retrieve the reporting data from campaigns. You will use these APIs to load data from Apple Search Ads to Snowflake. The first step is to sort out the search ads authentication mechanism. Search Ads uses the OAuth 2 protocol and requires one to download an access token after registering the app to facilitate API access. You can follow the below steps to get started with the Apple Search Ads to Snowflake data replication process:

Step 1: Use the private & public keys

• Create a private key from the system you will access the API and retrieve the public key for that keyset. Head to Account Settings and click on API. In the public key section, paste the public key so that Apple can verify the authenticity. Note down the client id, team id, and key id.

openssl ecparam -genkey -name prime256v1 -noout -out private-key.pem
openssl ec -in private-key.pem -pubout -out public-key.pem

Step 2: Generate the client secret and use it to request an access token

• Use the below Python code snippet to generate a client secret that can be used to request an access token.

import jwt
import datetime as dt

client_id = 'YOUR_CLIENT_ID'
team_id = 'YOUR_TEAM_ID'
key_id = 'YOUR_KEY_ID'
audience = 'https://appleid.apple.com'
alg = 'ES256'


issued_at_timestamp = int(dt.datetime.utcnow().timestamp())
expiration_timestamp = issued_at_timestamp + 86400*180

# Define JWT headers.
headers = dict()
headers['alg'] = alg
headers['kid'] = key_id

# Define JWT payload.
payload = dict()
payload['sub'] = client_id
payload['aud'] = audience
payload['iat'] = issued_at_timestamp
payload['exp'] = expiration_timestamp
payload['iss'] = team_id

KEY_FILE = '<Path to the generated private key file>'

with open(KEY_FILE,'r') as key_file:
    key = ''.join(key_file.readlines())

client_secret = jwt.encode(
payload=payload, 
headers=headers,
algorithm=alg, 
key=key
)

with open('client_secret.txt', 'w') as output:
    output.write(client_secret.decode("utf-8"))

• You can now use the client secret to request the access token through the below CURL command.

curl -X POST \
-H 'Host: appleid.apple.com' \
-H 'Content-Type: application/x-www-form-urlencoded' \
'https://appleid.apple.com/auth/oauth2/token?grant_type=client_credentials&
client_id=<client_id&client_secret=<client_secret>

Save the access token received as the response to the above request.

Step 3: Get data from Apple Search Ads as a JSON file

• You can now use the reporting API to fetch the details of a campaign. Apple Search Ads reporting API returns campaign-level metrics. The request must contain a start time, end time, and a selector parameter that specifies the sorting criteria and pagination details. Use the below request to retrieve the result as JSON.

curl "https://api.searchads.apple.com/api/v4/campaigns" \
-H "Authorization: Bearer {access_token}" \
-H "X-AP-Context: orgId={orgId}" \
-d '{
"startTime": "2021-04-08",
  "endTime": "2021-04-09",
  "selector": {
    "orderBy": [
      {
        "field": "countryOrRegion",
        "sortOrder": "ASCENDING"
      }
    ],
    "conditions": [
      {
        "field": "countriesOrRegions",
        "operator": "CONTAINS_ANY",
        "values": [
          "US",
          "GB"
        ]
      },
      {
        "field": "countryOrRegion",
        "operator": "IN",
        "values": [
          "US"
        ]
      }
    ],
    "pagination": {
      "offset": 0,
      "limit": 1000
    }
  }
}'  

Step 4: Load the JSON file to Snowflake

• You will now upload the JSON file to a Snowflake table. Since Snowflake supports JSON loading and querying, you can use a single-column Snowflake table to load the raw JSON file. For this, first, head to the Snowflake Web console and create a table with the below Query.

CREATE TABLE METRICS_JSON (RAW_JSON VARIANT);

• Once the table is created, head to the databases section in Snowflake and click ‘Load Data’. Select the JSON file you generated using the CURL request and click Next.

You should now see the data reflected in your Snowflake database. That completes the process of replicating data from Apple Search Ads to Snowflake by writing python scripts. 

Calling APIs via a python code and uploading data to Snowflake as JSON files is an excellent option in the following scenarios:

• Little to No Transformation Required: The above method does not cover complex data preparation and standardization tasks. Performing these additional tasks will require more time and effort. Hence, this approach is a great choice if your Ad campaign data is already in an analysis-ready form for your business analysts.
• One-Time Data Transfer: At times, business teams only need this data quarterly, yearly, or once when looking to migrate all the data completely. For these rare occasions, the manual effort is justified.
• Few Reports: Downloading and uploading only a few JSON files is fairly simple and can be done quickly.   

A problem occurs when your business teams require fresh data from multiple reports every few hours. It becomes vital to clean and standardize the data for people to make sense of it when it is available in multiple formats. As a result, you soon find yourself devoting a sizable amount of your engineering bandwidth to building new data connectors.

To guarantee a transfer with no data loss, you’ll have to also check for any changes in these connectors and repair data pipelines as needed. These on-priority tasks can easily eat up 40-50 % of the time needed to complete your core engineering duties.

So, is there a simpler yet effective alternative to this? You can…

Method 2: Automating the Integration using Hevo a No-Code ETL Tool

Going all the way to write custom python scripts for every new data connector request is not the most efficient and economical solution. Frequent breakages, pipeline errors, and lack of data flow monitoring make scaling such a system a nightmare.

You can streamline the Apple Search Ads to Snowflake data integration process by opting for an automated tool. To name a few benefits, you can check out the following:

• It allows you to focus on core engineering objectives while your business teams can jump on to reporting without any delays or data dependency on you.
• Your marketers can effortlessly enrich, filter, aggregate, and segment raw Apple Search Ads data with just a few clicks.
• The beginner-friendly UI saves the engineering team hours of productive time lost due to tedious data preparation tasks.
• Without coding knowledge, your analysts can seamlessly standardize timezones or simply aggregate campaign data from multiple sources for faster analysis.
• Your business teams get to work with near-real-time data with no compromise on the accuracy & consistency of the analysis.

To know the comfort of such an effective automated tool, let’s see how a cloud-based platform like Hevo effortlessly connects Apple Search Ads to Snowflake in 2 easy steps:

• Step 1: Configure Apple Search Ads as a source by providing your Apple credentials. 

Note: You must authorize Hevo to access data from your Apple Search Ads account using the API keys. 

• Step 2: To complete the process to replicate data from Apple Search Ads to Snowflake, you can start by providing your Snowflake credentials. 

After following the above 2 simple steps, Hevo will quickly create the pipeline for replicating data from Apple Search Ads to Snowflake based on your inputs while configuring the source and the destination.

The pipeline will automatically replicate new and updated data from Apple Search Ads to Snowflake every 6 hours (by default). However, you can also adjust the Apple Search Ads to Snowflake data replication frequency per your requirements.

Data Replication Frequency

Default Pipeline Frequency	Minimum Pipeline Frequency	Maximum Pipeline Frequency	Custom Frequency Range (Hrs)
6 Hrs	1 Hr	24 Hrs	1-24

Hevo is fully-managed and completely automates the process of not only loading data from your 150+ plug and play connectors(including 40+ free sources)but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. The data is handled securely and consistently with zero data loss with fault-tolerant Hevo’s architecture.

What will you achieve by migrating data from Apple Search Ads to Snowflake?

Here’s a little something for the data analyst on your team. We’ve mentioned a few core insights you could get by replicating data from Apple Search Ads to Snowflake, does your use case makes this list?

• Know your customer: Get a unified view of your customer journey by combing data from all your channels and user touchpoints. Easily visualize each stage of your marketing & sales funnel and quickly derive actionable insights.   
• Supercharge your ROAS: Find your high ROAS creatives on which you should be spending more money, thereby boosting your conversions. Identify the different creatives and copy that work best for your customer segment. 
• Analyze Customer LTV: Get a competitive edge with near-real-time data from all your marketing channels and understand how different targeting, creatives, or products impact your customer LTV.  

Putting It All Together

Writing a python script to extract Apple Search Ads data as JSON files is going to be an absolute breeze for you if this is a one-time request from your marketing team. But what if the data replication from multiple sources needs to happen every few hours?

Real-time marketing ROI and campaign performance monitoring are crucial for your business teams. Your business team might also request you to prepare and clean the data on an ad-hoc basis. You would eventually end up spending months creating & maintaining data connectors. Or you can brush off all your worries by hoping for a smooth automated ride with Hevo’s 150+ plug-and-play integrations.

Saving countless hours of manual data cleaning & standardizing, Hevo’s pre-load data transformations get it done in minutes via a simple drag n drop interface or your custom python scripts. No need to go to your data warehouse for post-load transformations. You can simply run complex SQL transformations from the comfort of Hevo’s interface and get your data in the final analysis-ready form. 

Frequently Asked Questions