Leveraging Data Sources with Integrate.io

Welcome to another X Force Data Summit presentation. Today we have Lolly Bowright. She's a software engineer at Union Street Media. And even though she's a software engineer, she's an X Plenty user. And she's gonna tell us today how she was able to have fewer software engineers on the industry payroll by using xPlanet. So here's Lolly.

Yeah. Hi, everybody. So I'll just jump right in.

A little bit about Ian Street Media before I start. We're a digital marketing company for real estate. So what that means is we have we host websites for real estate agents and brokerages.

We provide a variety of digital marketing services that drive leads to our clients.

And then we also have reporting and analytics 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 today, I'm gonna be discussing how over the past two and a half years after integrating with xPlanny, we've transitioned to a data focused company by connecting all of our different data streams to provide value.

So going back to two and a half years ago where our data team started, we were two engineers. That was our data team. 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. So I'm gonna be telling that story about how our very small team was able to turn UniStreamMedia into a more data focused company by leveraging tools like xPlani and Salesforce and Amazon Web Services to do that.

So 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 all the details about our real estate agents and brokerages that we provide services for. And then we have tracking information. So we have, like, Google Analytics and Google Search Console data, and we have ad platforms like Google Ads and Facebook, listing property data throughout the United States.

The list goes on. We have custom lead activity from our websites and content management settings.

And so we have this endless list of data sources, and we didn't have an efficient way to use that data internally or presenting it to our clients.

So 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.

So 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 and 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.

So with that problem in mind, we settled on this solution that kind of simplified in this 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 X Plenty.

And then I'll come back to that in a moment. We had our storage that we selected, which was Amazon Redshift as our primary warehouse, and we also use s three buckets for file storage for data that we don't need to access as frequently, and 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.

And kind of backing up to the processing bit, we selected Integrate.io because well, for a variety of reasons, but there were two big ones for our team. So one, really simple to use.

Going back to our team, we were two software engineers, not a lot of data engineering experience, and it was very the interface is intuitive, ETL best practices are just built in to the product, and we were honestly able to, like, 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. So for our company, all of our clients have let's say, 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.

And with xPlanning, we could authenticate with that one master login, and it just automatically pulls all the data for all of our Google Analytics clients.

And it wasn't that easy to authenticate with some of the other tools that we've added. So that was 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.

So everything ties back all of our different data sources tie to Salesforce in our warehouse. We did that by creating custom fields in Salesforce that have account IDs that correspond to the data sources. And that's allowed us to, one, connect all of the different data sources, but also for creating, you know, reporting experiences. We can have some settings in Salesforce in custom fields that just flow into the warehouse and then into our reporting experiences without having, like, a custom content management system for our reports and things like that.

So now I'm gonna jump into just a few examples of how we were able to provide value really quickly after integrating with xPlanny.

And this by quickly, I mean, probably within the definitely within the first quarter. So, like, three months after inter integrating with xPlani, we were able to release a few key products that allowed us to immediately start adding value to our company.

And I'll kind of give you the before and after view of what it was like pre warehouse and after warehouse.

So one is client facing reports. So 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, 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 just because when they were built ten years five, ten years prior, That's the only data source that we needed. We didn't have all of our paid advertising and things like that then. So didn't give a full picture.

We also had a variety of reports that our internal teams would compile for high value clients. And they would actually manually go into each of the different data sources, copy and paste information, put it in a spreadsheet, put it in the slide deck, present it to the client.

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, you know, it was price per client. We couldn't reuse the data. It wasn't a great solution. So, you know, lots of different experiences, 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 when or presentations when they were given to the clients.

So then after we integrated with xPlanny 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 weekly and monthly reports that are sent directly to our clients' email inboxes. They're able we're able to combine all of the relevant data sources 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. And we automatically compile these reports from then on every week and month for the clients, and we're able to do it for all of our clients.

And 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.

And then 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 clients' website or marketing services are performing. It's easy for them to digest and understand the value we add for them. And it is a great example of something that combines lots of different data sources into one simple metric.

You know, the cost, monthly recurring revenue comes from Salesforce. We have ad costs from all the different platforms.

And then leads come from ad platforms. They come from our websites. They come from phone calls that we can attribute to our different channels. 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.

So we weren't really able to leverage the metric as much as we wanted to. But after integrating with xPlanny, we actually do the calculation in one of our xPlanny packages every day.

It's really simple. And we're now able to, you know, 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.

And we also have some automated alerts in place, so that our internal teams know if there's been a negative change and they need to investigate something that might have changed with the client or even if it's a positive change, then they can do some client outreach and celebrate that win. So that was big for us.

And those are just two specific examples that I felt illustrated how quickly we were able to add value with after integrating with XPlani.

But just generally now, or two years later, both of those solutions were probably released within the first, like I said, three to four months after integrating. And now we're really consider ourselves to be a data first company.

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, Samara.

And we don't really have to we don't manage it full time. We can also build products on top of it. 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 really focus on building APIs on top of the warehouse so that we can expose data to our other engineering teams so that we can build products for our websites that utilize the data, so that we can leverage the data in ad templates.

That's another way that we've streamlined our internal team's workflow.

And we're 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 were just able to focus on product rather than growing our team exponentially.

And that's all.

Great. Thanks, Molly. Yeah.

I've got a few questions. So when you say that the 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?

Yeah. So it's it's an h there is an HTML view of the report. We use an emailing tool called SendGrid and populate templates with our data.

And 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.

Yeah.

So and do the do the clients have a is some of this data used to like 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 or?

So most some of our date some of our clients do have we have there are only a few clients that are really interested enough in the data to use internal dashboards, honestly.

And we've we've 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 if a client is interested in the data, they will share that with them. So they don't actually have login credentials for that, but they have the ability to look at more information and get and provide them with that.

So I used Redash a few years ago when I first got Redshift up and running and another play at another job and has it gotten better? I mean it could push out reports, you could write SQL and get reports. Has it been developed? Is it doing is Redash a little more richer product now?

Mean, we've been using it for two years, so I don't know when you started using it.

I can't It about three years ago.

Okay, yeah. I mean, it's definitely a a simple tool. There's not a lot of a lot that somebody who doesn't know SQL can do to change the 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're really intentional about, like, the charts that we show them

So that they can do their jobs efficiently. And we really haven't we really haven't run into any issues. And they they actually have some nice alerting that they have built in.

We yeah. It's really served us well and they are constantly iterating on the tools that has approved a bit, I would say.

Good. Good. So do you and Samara miss writing code using x plenty?

Is is was there a sad day when you realized you couldn't wouldn't have to churn out thousands of lines of code to do That's that's funny.

You know, I think we 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 Next Plenty kind of frees up that time for us to do that.

Alright. That sounds good. Well, Lolly, I'm glad to hear you're still writing code and thanks thanks so much for your time and for letting us know how you're using xPlanet at Union Street Media.

Yeah. Of course. Thanks for having me.

‍