Do you run your marketing campaigns through Linkedin Ads? Are you interested in moving data from Linkedin Ads to Snowflake, so as to gain actionable insights from it? If your answer is yes, then you’ve come to the right place! In this blog post, you will be introduced to two methods of data transfer from Linkedin Ads to Snowflake.
This blog discusses both of these approaches in detail, so you can critically assess their benefits and drawbacks.
Introduction to Linkedin Ads
Image Source: WinAtLinkedIn
Linkedin Ads is a self-service advertising platform that enables you to reach out to Linkedin’s professional audience (over 575 million users) with your marketing campaigns. Linkedin Ads enables you to take advantage of reaching out to customers based on specific skill sets, job roles, and organizational affiliations, thus enhancing your targeting efforts. Linkedin Ads currently provides support for a wide variety of ads including text ads, dynamic ads, sponsored content, etc.
Introduction to Snowflake
Image Source: Metal Toad
Snowflake is a cloud-based, Software-as-a-Service(Saas) Data Warehouse. Since Snowflake is fully managed, users do not have to invest time in installation or maintenance activities as these are handled by Snowflake. Snowflake enjoys a lot of popularity in the market, owing to its speed, scalability, and ease of use. Snowflake also separates its compute and storage layers. This enables the independent scaling and pricing of both layers.
Understanding the Key Features of Snowflake
1. Low Maintenance
Snowflake requires no software or hardware installations because of its Cloud architecture that allows scalability, thus you do not have to dedicate many resources towards infrastructure management.
Image Source: One Six Solutions
2. Speed
Queries that are already running in Snowflake are not affected by scaling. Thus, running speed remains unaffected.
3. Cloning Functionality
Snowflake allows users to create instant copies of objects that save a lot of space since these copies are not deep copies.
4. Separate Storage and Compute Layer
Snowflake has separate storage and compute layers that make it possible for you to scale one up or down without affecting the other.
Now that you are familiar with the basics, let’s move on to study the two approaches to move your data from Linken Ads to Snowflake in depth.
Methods to Move your Data from LinkedIn Ads to Snowflake
You will now learn about 2 methods that will help you move your data from LinkedIn Ads to Snowflake:
Method 1: Building Custom Code to Load Data from LinkedIn Ads to Snowflake
Writing a custom code for data load from Linkedin Ads to Snowflake broadly involves the following:
Prerequisites
The following points have to be noted while attempting the data transfer from LinkedIn Ads to Snowflake.
- Working knowledge of REST APIs and Snowflake data warehouse.
- A LinkedIn Ads account authenticated with the OAuth 2.0 protocol. This guide will help you with this process and more.
- A full set-up Snowflake Data Warehouse.
Step 1: Extracting your LinkedIn Ads Data
Linkedin Ads provides a rich set of REST APIs that you can interact with by making requests to it. You can interact with it directly, or through a client like Curl or Postman. The API gives you access to a lot of resources like conversion values, the number of clicks and likes, etc.
A sample of the same is given here.
GET https://api.linkedin.com/v2/adAnalyticsV2?q=statistics&pivots[0]=CAMPAIGN&dateRange.start.day=1&dateRange.start.month=1&dateRange.start.year=2017&timeGranularity=DAILY&campaigns[0]=urn:li:sponsoredCampaign:1234567
The response to the API call would look like this.
{
"elements": [
{x
"actionClicks": 0,
"adUnitClicks": 0,
"clicks": 177,
"comments": 0,
"companyPageClicks": 15,
"conversionValueInLocalCurrency": "0.0",
"costInLocalCurrency": "244.85000",
"costInUsd": "244.85000",
"dateRange": {
"end": {
"day": 20,
"month": 3,
"year": 2018
},
"start": {
"day": 12,
"month": 10,
"year": 2017
}
},
"externalWebsiteConversions": 0,
"externalWebsitePostClickConversions": 0,
"externalWebsitePostViewConversions": 0,
"follows": 0,
"impressions": 54494,
"landingPageClicks": 67,
"leadGenerationMailContactInfoShares": 0,
"leadGenerationMailInterestedClicks": 0,
"likes": 8,
"oneClickLeadFormOpens": 0,
"oneClickLeads": 0,
"opens": 0,
"otherEngagements": 1,
"pivot": "CAMPAIGN",
"pivotValue": "urn:li:sponsoredCampaign:134704654",
"pivotValues": [
"urn:li:sponsoredCampaign:1234567"
],
"reactions": 0,
"sends": 0,
"shares": 0,
"textUrlClicks": 0,
"totalEngagements": 186,
"viralClicks": 0,
"viralComments": 0,
"viralCommentLikes": 0,
"viralCompanyPageClicks": 0,
"viralExternalWebsiteConversions": 0,
"viralExternalWebsitePostClickConversions": 0,
"viralExternalWebsitePostViewConversions": 0,
"viralFollows": 0,
"viralImpressions": 86,
"viralLandingPageClicks": 0,
"viralLikes": 0,
"viralOneClickLeadFormOpens": 0,
"viralOneClickLeads": 0,
"viralOtherEngagements": 0,
"viralReactions": 0,
"viralShares": 0,
"viralTotalEngagements": 0
}
],
"paging": {
"count": 10,
"links": [],
"start": 0
}
}
Step 2: Preparing and Transforming LinkedIn Ads Data
Firstly, you must create a schema for your tables to receive the data that is being imported from LinkedIn. As the data being imported from LinkedIn is in JSON format, it may be helpful to also create additional tables if some of the data is nested, to ensure that it is flattened.
You also have to ensure that the data types from your LinkedIn Ads data correspond to those from Snowflake. Snowflake provides support for a wide variety of data types. Further information on Snowflake data types can be found here.
Step 3: Loading Data from LinkedIn Ads to Snowflake Data Warehouse
You can use the COPY INTO SQL statement to load your data into Snowflake. This is done via SnowSQL, Snowflake’s Command Line Interface. You can load your data files from any of Snowflake’s external locations or from Snowflake’s stages.
Data can be ingested and loaded into the Snowflake Data Warehouse from two locations. This section of the post aims to help you achieve the following:
- Loading Data from a Snowflake Stage
- Loading Data from an External Storage Location
1. Loading Data from a Snowflake Stage
We will use the PUT command to ingest the data into Snowflake. After that, we will use the COPY INTO command to load it. The following commands will help you load the data from their respective stage types.
User Stage Type:
COPY INTO TABLE1 FROM @~/staged
file_format=(format_name=’json_format’)
Created Internal Stage:
COPY INTO TABLE1 FROM @Stage_Name
Table Stage Type:
COPY INTO TABLE1 file_format=(Type=’JSON’
Strip_outer_array=”TRUE”)
2. Loading Data from an External Storage Location
This section will help you load data from the Amazon S3, Microsoft Azure, and GCS storage locations respectively.
Amazon S3
COPY INTO TABLE1 FROM s3://bucket
CREDENTIALS= (AWS_KEY_ID='YOUR AWS ACCESS KEY' AWS_SECRET_KEY='YOUR AWS SECRET ACCESS KEY')
ENCRYPTION= (MASTER_KEY = 'YOUR MASTER KEY')
FILE_FORMAT = (FORMAT_NAME = JSON_FORMAT)
Microsoft Azure
COPY INTO TABLE1 FROM azure://your account.blob.core.windows.net/container
STORAGE_INTEGRATION=(Integration_name)
ENCRYPTION= (MASTER_KEY = 'YOUR MASTER KEY')
FILE_FORMAT = (FORMAT_NAME = JSON_FORMAT)
GCS
COPY INTO TABLE1 FROM 'gcs://bucket’
STORAGE_INTEGRATION=(Integration_name)
ENCRYPTION= (MASTER_KEY = 'YOUR MASTER KEY')
FILE_FORMAT = (FORMAT_NAME = JSON_FORMAT)
Challenges and Limitations Faced while Building a Custom Code to Load Data
- Time-consuming Process: Manually writing code for ETL is a tedious and time-consuming process that will require a lot of bandwidth from your engineering team.
- Constant Maintenance: You will have to consistently monitor the Linkedin Ads API since it will return faulty data if the API experiences any issues.
- Hard to Perform Data Transformations: Fast transformations such as time standardizations and currency conversions are hard to perform under this method.
- Real-time Limitations: Using this method has limitations to bring data in real-time, as you will have to write a lot of additional code and configure cron jobs to achieve this.
Method 2: Using Hevo’s No-code Data Pipeline to Move Data from LinkedIn Ads to Snowflake
Image Source: Self
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!
It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. It allows you to focus on key business needs and perform insightful analysis using various BI tools such as Power BI, Tableau, etc.
Hevo focuses on two simple steps to move your data from LinkedIn Ads to Snowflake:
- Configure Source: Connect Hevo Data with Linkedin Ads by providing a unique name for your Pipeline, along with details about your authorised Linkedin Ads account. You can also choose the historical sync duration for your Linkedin Ads data.
Image Source
- Integrate Data: Complete Linkedin Ads to Snowflake migration by providing your destination name, account name, region of your account, database username and password, database and schema name, and the Data Warehouse name.
Image Source
Check Out What Makes Hevo Amazing:
- Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects schema of incoming data and maps it to the destination schema.
- Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
- Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
- Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
- Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
- Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Conclusion
In this blog, you have learned about LinkedIn Ads and Snowflake. You now know how to move your data from LinkedIn Ads to Snowflake using 2 simple methods. The conventional method needs you to have the technical expertise to configure the data transfer from LinkedIn Ads to Snowflake. But with Hevo you don’t need these confusing configurations.
Visit our Website to Explore Hevo
Hevo is a No-code Data Pipeline and has awesome 100+ pre-built integrations that you can choose from. Hevo can help you integrate your data from numerous sources and load them into a destination to analyze real-time data with a BI tool such as Tableau. It will make your life easier and data migration hassle-free. It is user-friendly, reliable, and secure.
Want to take Hevo for a spin? Sign Up for the 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!
What is your preferred approach to move data from LinkedIn Ads to Snowflake? Do let us know in the comments section below.
Rashid is passionate about freelance writing within the data industry, and delivers informative and engaging content on data science by incorporating his problem-solving skills.