Marketing Data Pipelines and Data Warehouse - Extra Space Storage

 
Project Type:  Data Warehouse  |  Data Pipelines
 
 
ess-logo.png

Extra Space Storage is a Utah based, publicly traded provider of self-storage units. Since 1977, Extra Space Storage has grown to become the second largest self-storage provider in the US.

 
 
 
 

Summary of Results

  • Extra Space Storage’s data science team is able to incorporate Google Analytics 360 data in its analysis and models.

  • Google Analytics 360 data is available in Google BigQuery, as well as Extra Space Storage’s existing on-premises data warehouse.

  • This work lays the foundation for Extra Space Storage to incorporate BigQuery and Analytics 360 into other use cases.

 

The Challenges

Extra Space Storage purchased Google Analytics 360 for improved web and mobile app analytics. They had a time sensitive need to use Google Analytics 360 data for various analytical needs across the company. Extra Space Storage engaged Ternary Data to help design, architect and build a data pipeline to move the Analytics 360 data into Extra Space’s existing on-premises SQL Server data warehouse.

 
 
 
“Ternary came to us at a critical time and helped us achieve a tight deadline. Their expertise of the platform and familiarity with our use case was a tremendous benefit. I appreciated their approach to knowledge transfer and ensuring we were successful long-term. ”
— Jeff Stott, VP Software Engineering at Extra Space Storage
 
 
 

The Requirements

Extra Space Storage engaged Ternary Data to help design and build a greenfield data platform that meets the following requirements.

  • The data science team needs Google Analytics data to build various models.

  • Google Analytics data must be made available in the existing on-premises data warehouse.

  • The new data pipelines must scale as data and analytics requirements grow in the future.

  • The system should be cost effective and serverless wherever possible. (Avoid undifferentiated heavy lifting.)

 

The Solution

Because of the available connections from Google Analytics 360 to BigQuery, we chose to leverage tools on Google Cloud Platform.

Ternary Data developed the following solution for Extra Space Storage’s marketing data ingestion platform.

By leveraging the BigQuery Data Transfer Service, we set up export of an Analytics 360 View into BigQuery. Analytics 360 data is exported in a daily batch -  which includes one intraday table, and one table from yesterday - into a date partitioned BigQuery table. This is where some BigQuery data pipelines would end. But Extra Space’s existing data warehouse meant that we had to make the Analytics 360 data available there as well.

When a new Analytics 360 table is created in BigQuery, Stackdriver logs its creation. Using Second-Party Triggers with Stackdriver, the creation event is forwarded to a Pub/Sub topic. This topic triggers a Cloud Function that initiates an extraction of the newly created BigQuery table. Another Pub/Sub listens to the table extraction events, and triggers a different Cloud Function issue query to BigQuery to unnest and flatten the Analytics 360 data. BigQuery then exports this flattened data into a time partitioned Google Cloud Storage bucket.

Once the flattened Google Analytics 360 data is available for the day, Extra Space Storage’s on-premises systems pull it into a staging area. From there, the data is ETL’d into their SQL Server data warehouse where it can be used across the organization for various analytics and data science needs.

 
Extra Space Storage - Marketing Data Pipeline Infrastructure

Extra Space Storage - Marketing Data Pipeline Infrastructure

 
 
 
 

About Ternary Data

Ternary Data is a specialty data architecture and consulting firm based in Salt Lake City, Utah. Ternary Data advises and coaches companies on discovering the value in their data through cloud services and best practices.

Ternary Data is partnered with Google Cloud and many more best in class data technology companies.