This content, written by Erin Franz, was initially posted in Looker Blog on Jul 1, 2020. The content is subject to limited support.
Good Apple set up shop in 2008 with a mission to help organizations drive successful and scalable marketing programs. From day one, their marketing methodology has been rooted in data-driven decision making. Mark Sturino, Director of Analytics, reaffirms this by noting that Good Apple only recommends media buying strategies to its clients when results are measurable: “We don’t do it if we can’t figure out the outcomes.”
For years, the Good Apple teams relied on spreadsheets for getting data and preparing reports. While measurement, analytics, and media have always been Good Apple’s core skills, the agency lacked sufficient technical resources to do the heavy lifting necessary for gaining deeper insights.
As they acquired more clients, Good Apple found themselves facing issues commonly encountered by many digital media agencies:
Explosion of data
The volume of data was exploding across all channels: paid media ad data from ad servers, Facebook, Amazon, internal client reporting from Google Analytics, Salesforce, Nielsen, and other sources.
Cumbersome report building
Spreadsheets were no longer suitable, and building data connectors via application programming interfaces (APIs) was time-consuming and required specialized technical help.
Customizing customer needs
With every client having different reporting requirements, it became increasingly difficult to manage multiple accounts across the technology stack.
Lack of technical personnel
Qualified technical professionals with the skills to build data processes were in high demand and hard to come by.
The wake-up call
In 2015, Good Apple had an “Aha!” moment. They had a client that required a weekly data-intensive report that took a total of 72 hours to create. This equated to three people working full-time Monday through Wednesday to pull it all together. On an annual basis, Good Apple employees were spending 3,600 hours to create reports for this single client.
With only 21 people on staff, this was a tremendous strain on Good Apple’s resources. In fact, it was a common complaint among employees that they were spending too much time on reporting and not enough time communicating with clients. It was time for a change.
With employee feedback as the catalyst for change, Good Apple started compiling an employee wish list, which included:
- Flexibility: work across the agency’s many different systems, including pre-built systems designed specifically for e-commerce
- Automation: Adopt processes that make the best use of the agency’s resources. For example, let analysts focus on analytics, not on building data pipelines
- Alignment: Bring together people-focused partners that provide responsive support
- Access to accurate data: Improve decision making with high quality, accurate data
- Integrated insights: Bring data together from various sources and allow usage to expand at scale
- More answers: Increase access to data sets that could answer a wide variety of questions, along with more dynamic reporting capabilities to hone-in and ask follow-up questions
Phase 1: building an integrated data platform
Sturino and his team embarked on an initiative to build a proprietary data platform they dubbed “Crisp.” He likens their approach to building a stereo system back in the day, which involved selecting and integrating the best components to create an outstanding audio experience. The Crisp system was created incrementally, but in a way that allowed Good Apple team members to use it while it was evolving, with the first functional version of the platform going live in the fall of 2015.
The ETL data pipeline component was integrated with Snowflake’s cloud data warehouse. The solution made it easy to store data, scale tasks, and integrate sources. Snowflake also met Good Apple’s criteria for great service, easy configuration, and affordable pricing.
The final piece of the puzzle was reporting, which was still done in spreadsheets, fed by Snowflake output files. While Crisp was a vast improvement from what they had before, when it came to reporting and analytics, the agency found that they were still experiencing the same problems: reports were static and often had to be re-run for new breakouts.
“We realized that this was not a long-term solution,” said Sturino. “In addition to the reporting issues, the ELT process was taking 24 hours or more, with the system constantly crashing. We were able to keep things running for a long time, but our solution just wouldn’t scale.”
Phase 2: Integration of Looker, Rivery, and Snowflake
To further streamline and improve data analytics and delivery for their clients, Good Apple decided to make some radical, much-needed changes to the Crisp system. The goal was to make the new iteration faster, scalable, and more flexible, with a single source of data readily accessible by all.
To do this, Sturino and his team evaluated several data pipeline options to find the best fit. The team chose Rivery because the platform is simple to implement and easy to learn, even for nontechnical users. Rivery’s pre-built data connectors immediately integrated most of Good Apple’s data sources with Snowflake. Rivery then built custom data connectors on-demand for the sources that were not already available, saving Good Apple significant dev resources.
With Rivery, Good Apple consolidated 14 different data sources, from dozens of individual clients into a single data management platform. And since Good Apple makes an average of 200 data pulls per day from all of these disparate sources, this significantly simplified the work required to generate reports. Plus, with the ability to easily add new data sources without altering the overall data workflow with Rivery, Good Apple Senior Data Analyst Jean Huang dubs Rivery as their “one-stop shop” for all data needs.
Happy with Snowflake as their data warehouse, the final step was to find an analytics tool that would leverage the speed and power they had with Snowflake to help generate deeper insights. This led the team to Looker. By switching from spreadsheets to a data platform, data analysts were able to track media buys and identify those that produce the best results for their customers with quick, easy to surface insights in clear and simple visualizations.
Achieving a democratized data process
By identifying the need for change, developing the Crisp system, and integrating Rivery, Snowflake, and Looker, the teams at Good Apple were able to meet their initial wish list and democratize their data process. Rivery creates Logic Rivers that run Snowflake SQL queries so that raw data from Snowflake can be pushed into a consolidated, client-facing view in Looker.
Previously, Crisp was managed by one or two data engineers. Today, any of Good Apple’s nine data analysts can log into Rivery and rerun the data or restructure a pull for client reporting. This can be done anytime, even when analysts work from home. The elimination of workflow bottlenecks has resulted in substantial time savings for everyone, from the engineering team, to data analysts, to the media team who optimize client campaigns.
“Thanks to the triumvirate of Looker, Snowflake, and Rivery, reporting is fully automated and can be refreshed daily,” says Jean Huang. “It’s about as close to real time as possible. We’ve reduced time spent on reporting by a factor of nearly 100. Personally, I appreciate the flexibility to be able to tinker with things myself without having to ask for help.”