A case study of how to use Integrate.io to leverage multiple, disjointed data sources and reporting to create a unified view across clients. Learn how this marketing agency did it.
Union Street Media went from enduring disjointed data and reports to unifying their client data and simplifying their customer-facing reports, thus allowing them to show their true value to clients--and build more products as well.
Software Engineer Lally Boright delivers her first-person account of the challenges presented by the need to create a comprehensive reporting strategy across multiple data sources. She goes on to explain how Integrate.io helped them transition to a data-first organization. Boright shares how Integrate.io’s ease-of-use and minimal need for oversight has freed up the data team’s time to focus on leveraging data to provide better customer service and to build products.
This presentation is ideal for any Salesforce marketing director who needs to merge multiple data sources into a single reporting view. You’ll leave inspired, knowing that getting multiple data sources under control is possible.
[00:00:00] Welcome to another X-Force data summit presentation. They have Lally Boright. She's a software engineer at Union Street Media, and even though she's a software engineer, she's an Integrate.io, and she's going to tell us today how she was able to have fewer software engineers on the payroll by using Integrate.io.
[00:00:31] So here's Lally.
Yeah. Hi, everybody. I'll just jump right in. A little bit about Union Street Media before I start. We are a digital marketing company for real estate. What that means is we host websites for real estate agents and brokerages. We provide a variety of digital marketing services [00:00:57] that drive leads to our clients. Then we also have reporting and analytics [00:01:00] solutions that give insight into how our digital marketing efforts are working, how websites are performing, that sort of thing. And that's in my wheelhouse. That's where I work. And, I'm going to be discussing how
[00:01:16] over the past two and a half years after integrating with Integrate.io, we've transitioned to a data-focused company by connecting all of our different data streams to provide value. So going back to those two and a half years ago, where our data team started; we were two engineers. That was our data team.
[00:01:41] And we didn't have specific data engineering experience, but we did have a lot of domain knowledge about our clients and our company and we saw a lot of disjointed data, and an opportunity to combine all of that data and provide value internally, to our teams and also, to our clients in the form of products and things like that.
[00:02:08] So I'm gonna be telling that story about how our very small team was able to turn Union Street Media into a more data-focused company by leveraging tools like Integrate.io in Salesforce and Amazon web services to do that. A little bit about the problem we were facing two years ago. As I mentioned, we had a lot of different data sources, so we had things like, we had client information in Salesforce, so we have client information in Salesforce, and it has
[00:02:39] all the details about the real estate agents and brokerages that we provide services for. And then we have tracking information. So we have like Google analytics, Google search console data, and we have ad platforms like Google ads and Facebook, listing property data throughout the United States;
[00:02:59] the list goes on. We have custom lead activity from our websites and content management settings. And so we had this endless list of data sources and we didn't have an [00:03:10] efficient way to use that data internally or present it to our clients. We couldn't combine it and report on aggregated data sources in an easy way, so we couldn't get the full picture of our client performance and we couldn't leverage our data effectively for new products.
[00:03:10] We needed a way to combine the data sources. And, because Union Street Media is a private, smaller company; we had financial limitations and our R&D initiatives really need to provide value and have an impact relatively quickly. Anything we plan, it has to have a product that we can sell in mind or it has to save internal teams time and it has to happen on a relatively quick timeline.
[00:04:07] So with that problem in mind, we settled on this solution that simplified this. In this [00:04:16] illustration on the left, we have all those different data sources that I was just talking about that we needed to connect. And we selected our processing tool, primarily Integrate.io. I'll come back to that in a moment.
[00:04:29] We had our storage that we selected, which was Amazon Redshift, as our primary warehouse. And we also use three buckets for file storage for data that we don't need to access as frequently. We stage data there. And then on top of that we were able to deliver that data to our clients in a variety of different ways.
[00:04:54] Backing up to the processing bit, we selected Integrate.io, because, well, for a variety of reasons. There were two big ones for our team. One, really simple to use. Going back to our team, we were two software engineers, not a lot of data engineering experience, and the
[00:05:19] interface is very intuitive. ETL best practices are just built into the product. And we were honestly able to test things and learn as we were using the tool. So that was one big thing. Another big pro for the tool was how easy it was to authenticate with our data sources. Specifically because of the way we organize our data.
[00:05:44] So for our company, all of our clients have, let's say for Google Analytics, each client will have their own Google analytics account, and those will all roll up to one master Union Street Media Google Analytics login. With Integrate.io we could authenticate with that one master login and it just automatically pulls all the data for all of our Google Analytics clients.
[00:06:10] It wasn't that easy to authenticate with some of the other tools that we've added. So that was a big one for getting up and running quickly. The other decision that we made is to use Salesforce as our data connector. Everything ties back; [00:06:29] all of our different data sources tie to Salesforce in our warehouse.
[00:06:36] We did that by creating custom fields in Salesforce that have account IDs that correspond to the data sources. That's allowed us to one, connect all the different data sources but also, for creating reporting experiences. We can have some settings in Salesforce and custom fields that just flow into the warehouse and many into our reporting experiences without having a custom content management system for our reports and things like that.
[00:07:10] I'm going to jump into just a few examples of how we were able to provide value really quickly after integrating with Integrate.io. By quickly, I mean, probably within, definitely within the first quarter, so like three months. After integrating with Integrate.io, we were able to release a few key products that allowed us to immediately start adding value to our company.
[00:07:38] I'll kind of give you the before and after view of what it was like pre-warehouse and after warehouse. One is client facing reports. These are really important to our company because they help our clients understand the impact of the services that we provide. Like I mentioned before, we had disjointed data.
[00:07:59] We had disjointed reporting experiences. So we had a couple automated reports that existed that were custom, but they only included Google Analytics data at the time because when they were built 5 to 10 years prior, that's the only data source that we needed. We didn't have all of our paid advertising and things like that
[00:08:23] then sending him a full picture. We also had a variety of reports that our internal teams would compile for high value clients and they would actually like manually going into each of the different data sources, copy and paste information, put it in a spreadsheet, put it in the slide deck, present to the client.
[00:08:47] It was time consuming. We had a couple monthly reports that we used a third-party tool. It did combine data sources, but, we didn't have a lot of control about what was in the reports. And it was price per client. We couldn't reuse the data. It wasn't a great solution. So lots of different experiences.
[00:089:07] not a lot of consistency, time-consuming for our internal teams. Because it was so time consuming, we couldn't provide the reports to all of our clients. It was only high package clients, high paying clients, and the data couldn't be leveraged in any other way internally. It was only in those reports or presentations when they were given to the clients.
[00:09:33] After we integrated with Integrate.io and created our warehouse, we were able to really quickly create this new reporting experience that replaced all those old reporting experiences. And it was just really simple [00:09:48] weekly and monthly reports that are sent directly to our clients’ email inboxes.
[00:09:54] We're able to combine all of the relevant data sources and by easily pulling from the warehouse and it's cost and time effective. So there's some initial setup in Salesforce. Like I alluded to before, our internal teams will put in some custom fields for ad IDs, Google analytics IDs, they'll set the package type for the client, and then we ingest that along with all of our other data sources.
[00:10:28] And we automatically compile these reports from then on every week and month. We're able to do it for all of our clients. Now our internal teams are able to focus on optimization and client communication rather than just focusing and spending a lot of their time compiling reports and copying and pasting data.
[00:10:54] Another example that we completed early on is our cost per lead reporting. So cost per lead is an important value for our company and especially for our clients. It's a measure of how our client's website or marketing services are performing. It's easy for them to digest and understand the value we add for them.
[00:11:17] And it is a great example of something that combines lots of different data sources into one simple metric. Monthly recurring revenue comes from Salesforce. We have ad costs from all the different platforms. Then leads come from ad platforms. They come from our websites, they come from phone calls that we can attribute to our different channels.
[00:11:41] And, the before situation, again, our teams were manually compiling the data in spreadsheets. They were only doing it once a quarter because it was really time consuming. And, it was even error prone because it was a manual process. We weren't really able to leverage the metric as much as we want to do.
[00:12:07] After integrating with Integrate.io, we actually do the calculation in one of our Integrate.io packages every day. It's really simple and we're now able to serve it on internal reports so that our teams can reference it. We display it on those client-facing reports that I was just talking about so that our clients can see it weekly and monthly, see how they're doing.
[00:12:30] And we also have some automated alerts in place so that our internal teams can know if there's been a negative change and they need to investigate something that might've changed with the client, or, even if it's a positive change then they can do some client outreach and celebrate the wins. So that was big for us.
[00:12:50] And those are just two specific examples that I felt illustrated how quickly we were able to add value after integrating with Integrate.io. Now we’re two years later, both of those solutions were probably released within the first three to four months after integrating, and now we really consider ourselves to be a data-first company.
[00:13:20] Our client-facing teams are now able to focus on optimization and client outreach and our data pipeline. It's evolved, but it's still managed by two software engineers still, myself and my coworker. We don't manage it full time. We can also build products on top of it.
[00:13:44] We have time to do that. We have added two engineers to our data team, but they don't focus on managing the data pipeline. They are really focused on building APIs on top of the warehouse so that we can expose data to our other engineering teams so then we can build products for our websites that utilize the data, so that we can leverage the data in ad templates.
[00:14:11] That's another way that we've streamlined our internal team’s workflow. We were really able to do this by investing in the right tool set early on, and getting our warehouse and our data moving really quickly. And then we didn't have to grow our team and we're just able to focus on product rather than growing our team exponentially.
[00:14:46] Great. Thanks Lally. I've got a few questions. So when you say that the reports are delivered to the client via email is it like an HTML email that has all the information in it or do they have to click and go to like their login to your website?
Boright: So there is an HTML view of the report
[00:15:08] we use. An emailing tool called SendGrid and populate templates with our data. Then they can also click out to a web view that they don't have to log in. It's just a static view that has a little more information for them. But the bulk of the information is in the email body.
[00:15:31] Yeah. So do to the clients is some of this data used to populate a dashboard within your tool that the clients can look at and see where their campaigns are and where their ROI is and things like that.
Boright: There are only a few clients that are really interested enough in the data to use internal dashboards honestly. We've turned to solutions like even Google Data Studio for those clients, but we're still pulling from the warehouse and that's a little more dynamic. It lets them look at the information. We also use a reporting tool called Redash internally and on client calls if a client is interested in the data, they will share that with them.
[00:16:28] So they don't actually have login credentials for that, but they have the ability to look at more information and provide them with that.
[16:38] Q: So I used Redash a few years ago when I first got Redshift up and running and then at another place, at another job. Has it gotten better? I mean, it could, it could push out reports, you know, you could write SQL and get reports. Has it been developed? Is Redash a little more richer product now?
[17:02] Boright: We've been using it for two years, so I don't know when you started using it.
[17:14] I mean, it's definitely a simple tool. There's not a lot that somebody who doesn't know SQL can do to change your reports once they're there, but it does serve our internal purposes. So a lot of what we do, when creating those dashboards is work with our internal teams to … rather than giving them every single possible data point, we are really intentional about like the charts that we show them, so that they can do their jobs efficiently. We really haven't run into any issues, and they actually have some nice alerting that they have built in. It's really served us well and they are constantly iterating on it, so it has improved a bit, I would say.
[00:18:02] Q: Good. Yeah. So do you and Samarra miss writing code using Integrate.io. Was there a sad day when you realized you don’t have to churn out thousands of lines of code to do that?
Boright: It's funny. You know, I think we do love writing code, and that's what's nice about Integrate.io in some senses, is that we still get to write code. Because like, sure about those first two months when we were really setting up our pipelines, it was a lot of, dragging and dropping and writing, some SQL.
But now, you know, we're building APIs. We're able to do some other things and Integrate.io kind of frees up that time for us to do that.
Moderator: That sounds good. Well, lovely. I'm glad to hear you're still writing code.
Boright: Yeah. Thanks.
Moderator: Thanks so much for your time and for letting us know how you're using Integrate.io at Union Street Media.
Boright: Yeah, of course. Thanks for having me.