Integrate.io is a powerful integration tool with a simple drag-and-drop interface.  Many of our customers without any coding experience or skills use it to create powerful integrations without code. But what about software engineers? Surely they would just pull out their keyboard and start slinging code to build an integration, right?  Well, not at Union Street Media, where two software engineers have transformed customer reporting without writing integration code, using Integrate.io.

For more information on our native Salesforce connector, visit our Integration page.

The Company

Union Street Media (USM) is a digital marketing company specializing in real estate professionals. USM builds websites for real estate companies that are integrated with MLS, which means that the sites will show the most up-to-date listings for properties on offer. USM also uses digital marketing, such as Facebook and Google ads, to drive traffic to websites they build for customers. In addition to data stored in their proprietary website databases, USM uses Salesforce as a CRM and a single source of truth for customer information.

The Challenge

When Lally Boright and Smara Fournier, both software engineers, were assigned to the newly-formed data team at USM, neither had any specific data engineering experience. But both were very familiar with USM’s web hosting software, so they were ready and able to automate the reporting process at USM. Prior to the formation of the data team, reports were a labor-intensive, inconsistent process only performed for high-value clients. The goal of the data team was to automate the process so every USM client could receive high-quality, automated reports.

Building a reporting infrastructure from scratch is a big challenge, and at USM, that challenge included integrating five different data sources:

  1. Google Analytics, which stored data about customer engagement.
  2. Google Ads, which stored data about ads used to drive traffic to customer sites.
  3. Facebook, where Facebook ad data was stored.
  4. Salesforce, the source of customer information.
  5. USM’s own web platform.

In order to produce the reports and analytics that USM needed, the data team had to find a destination to unite that data and a tool to extract it.  Then they needed to build customer reports and provide internal staff with the tools they needed to do analytics.

The Solution

The USM team decided to use Amazon’s cloud platform as a data destination -- specifically, the RedShift data warehouse and Amazon’s S3 cloud storage. Redshift serves as the main database for USM’s reporting initiative. S3 is used to stage data before loading to Redshift, and to store data that doesn’t require immediate access. 

For integration and transformation, USM chose to mainly use Integrate.io for two reasons, ease of use and the ability to have a single login to retrieve all data from their advertising services.

“The interface was intuitive. ETL best practices are built into the product, and we were able to test things and learn as we were using the tool,” Lally said.

Lally also explained that each client had their own account at the advertising services, but USM was able to use Integrate.io to log in and get all data for their clients via a single sign-on, which made the integration process easier. 

thumbnail image

USM used Salesforce as a “data connector." In other words, custom fields were added to Salesforce with account IDs for the different services being integrated. Salesforce also stored reporting settings which were replicated into the data warehouse, so USM’s custom reporting solution could use those settings when preparing reports for customers.

Delivering Value

Within three months of setting up the data warehouse, Lally and Smara were able to create reports that replaced some of the automated and manual reporting at USM. These are simple weekly and monthly reports that are delivered via email. Emails are HTML content sent via SendGrid, and clients can click from the email to a landing page that shows the reporting without signing on. In addition, USM was able to replace their time-consuming, manual, quarterly calculation of Cost Per Lead with an automated Xpleny process that allows them to deliver this key metric on demand. 

For internal reporting, USM deployed Redash to provide reporting.  Redash is an open-source RedShift query tool that can be deployed for free if it is self-hosted, or at a low monthly charge if hosted by ReDash. USM also uses Google Data Studio to prepare analytics for customers requiring further data analysis, again using data from RedShift.

Conclusion and Further Information

During the implementation of the data warehouse integration, Lally and Smara spent a lot of time in the Integrate.io drag-and-drop interface building data pipelines.  But don’t worry -- these two are still writing code, now that Integrate.io is handling their data integration.  “Now we’re building APIs and doing some other things, and Integrate.io frees up our time to do that.”

The key takeaway here is that even software developers can be more productive using Integrate.io.  If you want to learn more about USM’s Integrate.io implementation, you can watch Lally’s presentation at our Xforce Data Summit. If you want to know more about how Integrate.io can make your software engineers more productive, we’ll be happy to give you a product demo and a free trial.