Marketing and ETL: ETL is the BI analyst’s no-code solution
If you need to get data into your marketing data warehouse immediately and you can’t wait for someone to write code to make it happen, Singular ETL might just be your best friend. Extract, transform, and load might not sound super-exciting to the average marketer … but using marketing ETL to get the right data in the right place quickly and easily?
Well, that’s pretty exciting.
Or at least the results you can generate with that data are exciting.
Which is why ETL tools can be a saving grace for the modern marketer or BI analyst. Because no code means fast. And fast means quicker insights.
ETL can be even quicker, in fact, than a real-time API. (Read on to discover why.)
It’s not just because full-scale data integration with multiple data sources is a huge challenge even for well-funded business intelligence and marketing technology organizations. It’s because Singular ETL does more for you than an API ever could.
What’s old is new again
It’s not often you see a concept devised almost 50 years ago — yes, ETL originated in the 1970s — used in modern technology stacks. Our conception of a data warehouse or data lake has changed a lot over the years. Hadoop and Spark didn’t exist back then, and real-time data — or even unstructured data — weren’t really issues.
The cloud didn’t exist, Amazon Redshift wasn’t even a sparkle in any engineer’s eye, almost no vendor described itself as a data integrator.
But good ideas stand the test of time, and whether your dataset is in flat files, a semi-random data source, a data warehouse, or an analytics tool, extracting data via ETL can help.
Even if an API integration is something you eventually decide you need, integrations take time that even great well-funded companies don’t always have. Plus, there’s often significant internal process to work through before even starting an API integration.
So how can do extract, transform, and load help you?
What we’ll talk about:
- ETL in a nutshell
- Who should use ETL … and why
- Extract transform load: How Singular ETL works
- Singular ETL does (almost) everything
- How marketers save time with ETL
- ETL and API: total BFFs?
- ETL FTW
ETL in a nutshell
Even if you’re not into data, you probably know that ETL is short for “extract, transform, load.”
EXTRACT is taking data out of a database or multiple data sources. TRANSFORM is converting the extracted data from whatever form it’s currently stored in to the specific format you need it in. And, it can include enriching the data: adding rows according to rules or from other sources to supplement the original data with more details. LOAD is simply inserting the data into a new database … the database that you are planning to use for further business or marketing operations.
In other words, marketing ETL is pretty simple. At least in concept: extraction, transformation, insertion.
That’s a good thing, because extracting data and manual data integration is almost no developer’s — or marketer’s — idea of a good time.
You get data, you change data, and you load data, often using tools like Informatica, Boomi, InfoSphere DataStage, Oracle’s data integration platform, Talend, or JasperSoft.
As with most things, however, the complexity can arrive when you implement it.
ETL Tools are one aspect. Transportation of the actual data, and overlaps in the theoretically clean and separate three stages are others. Plus, the ability to do data transformation in place with tools like Amazon Redshift and BigQuery add other options.
In its base form, however, ETL is pretty clear.
Who should use ETL … and why
Most people who use Singular fall into a few different buckets. There’s marketers, business intelligence analysts, marketing technologists, growth engineers, creatives, and executives, with a few other categories sprinkled around.
Marketers often use Singular’s front end: the web-based user interface.
Marketing technologists, or growth engineers, often implement the Singular API, extracting data from Singular in real-time. They can then integrate it into a flow of other data sources — proprietary data — in Tableau or Looker, or a custom system designed to process marketing data and present insight for growth or analysis.
Both are great.
But Singular ETL gets your the data you need to the system you want in the specific format that works for you. (Try getting that from an API!)
And that’s helpful for customers, including business intelligence analysts, who need more analytics data than the front-end user interface can easily provide. They might be processing that data in another platform, using it for machine learning, or doing some other form of data profiling. And now they can get everything they need via Singular ETL without spending any engineering time.
Plus, while some BI analysts are super-technical, they might not have the access or all the knowledge that they need to implement an API 100% accurately. If your growth engineers want API access eventually, that’s totally doable. It will require some engineering time and effort, but it is totally available.
Here’s where Singular ETL comes in: Many clients are spending tens to hundreds of thousands of dollars a day in mobile marketing. Waiting weeks or months for engineers or a data integrator to become available could have significant costs.
So Singular ETL helps BI analysts get on track quickly and easily.
You can create a replica of your marketing data in your own data warehouse. You can directly connect Tableau to that data. You can build custom reports or dashboards on top of it. You join sensitive or proprietary data to your marketing data within your safe and secure internal infrastructure.
Ultimately, you can power up your analytics, data science, and marketing technologist teams with more data and more access.
Extract transform load: How Singular ETL works
ETL is extract, transform, load, right? Well, not shockingly, that’s pretty much how Singular ETL works.
It starts with defining parameters on the extraction of the data you want, generally in either the Singular marketing data or (coming soon) Singular attribution logs. You set up a predetermined report with configured fields and, potentially, filters, and the report will automatically be generated on a regular basis.
That “regular basis” is can be scheduled multiple times daily by default. In other words, it’s not exactly real-time data, but it can be on a schedule that works for you.
Then instead of you having to set up a data schema for how you want to receive your data — the transform stage, remember — we deduce the target scheme directly from a set of fields that you’ve already configured. That includes the columns you want, their types, and more. Later, we’ll determine whether you want flat files or to insert the data into Amazon Redshift, or another location.
In other words, it’s data warehousing made simple.
ETL software can work with unstructured data as well as structured data, and some ETL jobs can be tougher than others, but Singular ETL will get you the data you need in the format you need it in.
Finally, you set up a destination for the data, which can be either a database like Amazon Redshift or a file-based endpoint like Amazon S3, or an SFTP location. In other words, the data can go straight into a database, or be stored in flat files. Additional destinations include BigQuery and Snowflake.
Over time, we may even add Google Drive or Google Sheets if there’s enough demand.
Once you have the data where you need it, BI analysts, data scientists, and marketers can access the data, analyze the data, and further transform or enhance it. And they can do that in the tools and platforms that they’re most comfortable with, enabling significant efficiency gains.
In addition, since you have the data optionally in a file or a database, data scientists can perform some ETL magic — extract, transform, load — if they choose. In other words, they can transform the data or data model before it comes to rest in whatever platform they eventually want to store it in.
Singular ETL does (almost) everything
Basically any marketer can benefit from using Singular ETL.
And there’s the huge benefit that your data comes into your systems — whatever they are — in the format that you need. That’s a massive time-saver.
If you have the engineering time and focus to be able to build a solid integration with Singular’s API, and if you need real-time attribution data, that’s also a great way to go. But it will require extra effort.
Going that route will give you real-time access to attribution data as it flows, which is clearly better for circumstances that require immediate or at least quick reaction. On-demand and automated data that you can get from the Singular API enables quick shifts in strategy if you see low-ROI activity. Data integrity is 100%, and you can do any data processing you wish.
Plus, it allows you to quickly bring marketing data into engagement platforms if you want to be able to respond to users’ or customers’ installs or purchases in near real-time. It gives you full control, and you don’t have to share access to your database.
Mobile changes fast, and for attribution, the API is the best way of keeping up with that. For more aggregated marketing data, particularly around costs, real-time is not such a major concern.
But even if you’ve already implemented our API, Singular ETL offers more.
Now you can stay up-to-date automatically with all new Singular innovation: all new data types available instantly without any integration effort. You’ll save devops and engineering resources by offloading your marketing data pipeline to Singular. You might save money on servers. And you just might have more peace of mind knowing that your marketing data pipelines are being constantly monitored around the clock by Singular.
How marketers save time with ETL
ETL is a huge time-saver for marketers, because you strip out the wasted overhead of data interpolation. Just think of the different ways disparate systems can conceive of a single type of marketing data such as location:
- City, state, and country (in any order)
- Region (like EMEA or APAC)
- GPS coordinates
- Latitude and longitude (non-decimal)
Your data lake or marketing data system of record might use any one of these. And the data you need from your marketing sources, partners, and ad networks, might be completely different. But a good ETL system can handle and translate many different kinds of location data in ways that your target database understands, saving you time, hassle, inefficiency, or, in the worst case, the utter inability to generate insights for growth thanks to bad or malformed data.
And we haven’t even talked about time yet:
- February 10, 2020
- Feb 10, 2020
- Monday, February 10, 2020 … and so on …
What Singular ETL provides is the ability to get the data you want into a target system in a way that it was designed to operate. Instantly. Now you’re up and running instantly, generating insights to save money or grow faster or serve customers better … not wasting time spinning your wheels to fix data formats.
Or, worse, proceeding with incompatible data without knowing it, and making poor decisions as a result.
ETL and API: total BFFs?
And that is a totally valid scenario. Both Singular ETL and the Singular API have their appropriate uses: you add the API for what it brings if you need it, and use Singular ETL for what it offers.
The Singular API gives you timely attribution data that powers insights at the speed of your customers. And it’s granular per-device data.
Singular ETL gives you regularly-scheduled aggregated data extraction on campaigns which you can put wherever you want, such as open source solutions like Hadoop and Spark.
Now you’ve got the best of both worlds. You don’t have to do the work yourself to transform the granular data to aggregate, and you still have the real-time data for attribution to enable quick response to marketing changes or quick engagement with new users and cohorts.
In addition, if you have a need for deep historical context on trends, you have aggregated data for all your data sources from which you can extract that information. (Note: you can also use Singular’s web user interface!)
And, since different teams may use different forms of data for their own distinct needs, everyone is getting what they want.
ETL for the win: getting started
Right now, you won’t see details in the typical Singular user interface about setting up ETL destinations, as the feature is in public beta.
So currently, to get started, you’ll need to talk to your Singular contact first. That will also be a good time to review current system capabilities, which are continually growing, and double-check your integration solutions.
Once you do, you’ll see new options appear and be able to work through Singular ETL, getting any necessary support along the way. ETL jobs will simply happen as they need: flowing right into your data warehousing infrastructure.
Ready to go?
Simply contact your Singular representative.
We’ll get you set up and started, and be available to answer any questions you might have about your data integration platform and strategy. We can also provide suggestions about third-party data integration, data quality, and data transfer details.