Empowering Social Impact: The Ad Council’s Journey with a Modern Data Stack
Building a Data-Driven Future: The Ad Council’s Approach to Analytics
Enhancing Social Good: Insights from the Ad Council’s Modern Data Stack Implementation
In the previous blog, we saw how USEReady’s consultants assisted Ad Council in making a well-guided decision to leverage the power of cloud. Migration to the cloud, specifically -SnowflakeTM, a cloud-only data platform with near infinite storage and computing solved the problem of ever-increasing data volumes, resource intensive computations all within budget. Additionally, it helped Ad Council in some of its specific use cases identified by USEReady consultants such as dealing with redundancy in prod data, reprocessing of source feeds, simplified architecture, and most importantly reduction in time taken for daily processing time by almost 90% as compared to SQL server.
Nonetheless the teams realized that just storing data on the Cloud could not help with the seamless processing at the speed and scale a modern cloud data platform offers. Bottlenecks in the data pipeline has to be remediated in order for Ad Council to reap the benefits to a modern data stack.
SSIS, a legacy on-perm ETL tool was able to support data integration and data transformation requirements and other downstream applications. It had many advantages amongst legacy ETL tools, primarily integration with SQL Server.
Exploring the Ad Council’s Modern Data Stack: Tools and Technologies Used
Overcoming Challenges: Lessons Learned from the Ad Council’s Data Journey
Advantages of SSIS:
- Easy Active Directory integration for seamless connectivity to SQL Server and visual studio, hence quicker data transfer
- Ease of use with help of Package Explorers
- Flexibility to work with heterogeneous data transformations
- Standardized data integration
- Reusability with help of Control Flow across Packages by Using Control Flow Package Parts
SSIS certainly had its advantages and seamless connectivity to SQL server but as Ad Council moved to digital mediums for wider reach of its successful campaigns, it was no longer feasible to continue using a legacy tool like SSIS given the speed, scale and complexity of data feeds.
Best Practices for Implementing a Modern Data Stack: Insights from the Ad Council
Leveraging Data for Social Impact: Success Stories from the Ad Council
The accelerated digitization made it vital for organizations to be able to handle complex, large, and near real-time data with ease. In the case of Ad Council, it’s campaigns were heavily dependent social media platforms to reach larger audiences. It becomes vital for the campaign managers, sponsors to analyse data from social media platforms, search engines to understand the trends emerging from these platforms. This data is an important indicator of a campaign’s effectiveness. Data feeds from these social media platforms have to be processed at speed and scale that most traditional ELT tools are not designed to handle.
The need for a modern data stack at Ad Council was immediate and pronounced. Consultants from USEReady worked on identifying a modern data ingestion tool that could help address Ad Council specific data use cases like getting near real-time data from social media platforms, leverage the power of Snowflake.
Harnessing the Power of Data: How the Ad Council Uses Analytics for Social Good
Addressing Data Challenges: Strategies for Building a Robust Data Infrastructure
Driving Change through Data: The Role of Analytics in the Ad Council’s Mission
Collaborating for Impact: Partnerships and Innovations in Ad Council’s Data Initiatives
Amongst the various tools available in the market, Matillion was shortlisted. A cloud-native ELT tool, purpose-built for Snowflake, it aligns perfectly well with Ad Council’s requirements. Matillion can efficiently ingest data from a wide variety of data sources with scale, speed and efficiency not possible with either on-prem legacy tools or other tools available. Access to data in a predictable and timely fashion helps deliver vital analytics to campaign managers, sponsors. Additionally, unlike on-prem ETL tools like SSIS, Matillion leverages the compute of Snowflake, making it an efficient and cost-effective combination to employ.
Benefits of Matillion for Snowflake:
- A cloud native ELT tool, custom built for snowflake.
- Data integration and transformation for cloud data warehouses
- More than 100 pre-built connectors for multiple data sources.
- Simple, GUI based low-code interface to orchestrate and transform data
- Rest API Connectivity to multiple online sources like Google Analytics, Facebook insights.
- Option to create custom connectors to ingest data from RESTful APIs
- Works efficiently with near real-time or click-stream data
- Faster data ingestion
- Additional connectors like ‘CData adaptors’ not required to get data from online sources.
- Option to develop custom code leveraging SQL, Bash, or Python to cater to specific business requirements
- Simple, transparent and cost-effective pricing model
This titled the scales in favour of Matillion, thereby helped sunset SSIS without disrupting operations. It also helped Ad Council to generate insights from multiple data sources like social media, survey info, 3rd party inputs; manage campaigns effectively predictably and with-in budget.
A quick comparison
| SSIS | Matillion |
| On Premise ETL tool | Cloud Native ELT tool |
| Connectivity restricted to RDBMS, flat files. | Connectivity possible with all types of sources: SQL, NoSQL Databases, APIs, Files, Cloud storage, etc |
| CData adaptors for SSIS required additionally to connect with online data sources, increasing costs | Additional adaptors not required, hence no additional licensing costs |
| Limited data transformation options | Can perform complex data transformations like CDC, handling semi-structured data, etc |
| No Sample data for performed transformations | SQL based transformations provide samples, row count and data lineage information |
| Administrative overhead to provision, maintain infrastructure | Cloud native tool, requires minimal administrative support to provision, maintain infrastructure. |
| No python/bash scripting | Matillion empowers developers to use Python3 and Jython to build any custom functionality on top of Matillion. |
In conclusion, this combination of Matillion and Snowflake helped Ad Council meet its objectives of a future-proof data stack with minimal administrative overheads, all with-in a predictable budget backed by credit-based consumption model.
This helped the analytics team focus on generating insights, provisioning newer data sources and helping run campaigns successfully and efficiently. The right data in the right hands at the right time leads to better outcomes, in the case of Ad Council – a better world.
