A godsend for marketers: how to create reports without analysts and SQL
Vlada Malysheva, Creative Writer @ OWOX
Reporting is a real pet peeve for any marketer. Mainly, most analytical tools focus on the needs of analysts and developers and prohibit the marketer from working with the data himself. This article explains why this happens and how we see a solution to this problem.
The importance of reporting for business
The goal of each business is to make a profit. However, to understand how the company’s development goes and in what direction it’s worth moving on, companies need to track and monitor all organization areas. In other words, reporting is an essential tool for decision-makers.
Not to mention that in a state of the pandemic economy, when marketers are required to achieve more with fewer resources (fewer budgets and less time for implementation), it cannot be done without reporting.
Over the past year, companies have become confident that you can achieve success in various ways, both offline and online. There are many options to reach customers in digital ecosystems (not to forget about evolving voice and audio spaces thanks to natural language processing). Any of these channels bring new data volumes. You can use this information both to communicate with customers and learn more about your target audience, which is helpful for business development.
In 2021, when the whole world has been operating online for more than a year, it’s difficult to imagine marketing in the old way without using Big Data. Modern marketing experts are well aware of the importance and necessity of data-driven strategies. High demands, expectations, and, of course, instant results for customers require informed decisions and actions based on specific data.
What challenges marketers face
The business has a market in which it operates, goals, and data. Any company wants three things: earn more, spend less and do it faster.
The marketer helps the business achieve what it wants. He has dozens of different services, goals from the CMO, and expertise in marketing. This is what is available to him in most cases.
What the marketer lacks and what problems he most often faces in his work:
- The data in the reports doesn’t match and has to be constantly monitored. Why? Data can be lost at the data collection stage, duplicated, etc. There is no simple reason; otherwise, it would not be a problem in the market. A marketer needs data that can be trusted to make decisions.
- It’s complicated and takes a lot of time to build a report with all the necessary data. Usually marketers don’t have time to study SQL and don’t want to wait for a response from analysts. The marketer wants to independently make reports in the correct sections and not wait for them from analysts. Another problem is that he never knows in advance what questions will arise and what reports may be needed next time. It’s a rare occasion to have one universal report for all possible circumstances.
- A marketer needs applicable guidance not report. For example, the ad campaign report that shows CPA is less valuable than the best marketing-mix report showing how much you need to increase or decrease the ad budget. Why so? Because the application cost doesn’t answer the question of what to do with this information.
Who and how can help marketers and what can go wrong
Thanks to the development of services such as Google BigQuery, Big Data analysis becomes more accessible for companies of all sizes. But existing analytical products are aimed primarily at the needs of analysts and developers. Marketers are still forced to limit themselves to ready-made reports or wait for analysts to prepare data, missing opportunities and wasting time.
In the figure above, there’s the Gartner magic quadrant with analytical and BI services. In one of the popular chat rooms for analysts, Excel was added to the graph. This figure shows that business users need modern analytical tools until the data they need is delivered to the familiar interface of Excel or Google Sheets.
There is a well-known quote: “data is a new oil." And just as the oil industry in the market for marketing analytics has states regulating access to accumulated value in raw form such as Google, Facebook, Apple, there are oil pipelines like OWOX BI, Supermetrics, Fivetran, StitichData delivering data to refineries like Google Cloud, AWS, MS Azure, Hadoop. But for the end-user, all this doesn’t give significant value until he can refuel his car with suitable energy at the right time and without unnecessary effort.
Today, business users are forced to limit themselves to regular buses, which deliver them to pre-set data sections or overpay for taxis, without knowing the exact address where they need to get to and what report to build in the end.
Why existing analytical tools don’t fully meet the needs of marketers:
- Specialized services don’t contain the required data. Why not just use Google Analytics? There’s no data business needs. It’s impossible to merge all data and build reports according to the logic that corresponds to your business model.
- Even if the business has collected all the necessary data, expertise is needed to merge it: by what keys, in what structure, with what conditions, etc.
- Existing advanced analytics services provide purpose-built solutions, not system ones: you can build a specific report or dashboard, but you cannot create a reporting system that the marketer can work with. For example, you can get an auto-update report in Power BI, Google Data Studio, or Excel. But if you need to change something in the report, add the necessary sections, parameters, and metrics, or change Channel Grouping, the marketer himself cannot do this. You have to contact an analyst every time.
Why is it difficult for a marketer to get the right data in Excel or Google Sheets? Where is the bottleneck among all the stages in working with data?
After the data has been converted into a specific structure (for example, UTM tags are recognized), and before the business user can make a report from them, an iterative process occurs. For example, you select the desired columns, exclude users who haven’t made purchases within a certain period, etc. This process requires communication between analysts and marketers. And if the marketer doesn’t know SQL, this communication cannot be avoided.
Today, analysts build reports that require data consolidation using SQL. Over time, there are more and more such SQL queries, and the logic in them is becoming more complex. Thus, the marketer cannot get the necessary report without the help of an analyst, and the quality of the data is every time in question.
Most analytical products simplify the analyst’s work at one stage but don’t help to merge the data correctly, or the marketer to get the necessary reports without the analyst’s help. Therefore, the analyst remains a weak point.
One of our clients once said: “Our marketers now have two choices: either wait for Excel or learn SQL." The good news is, it’s not the only alternative.
Basically, there is no need to wait for an analyst or learn SQL to build reports. When the data is already modeled up to the company’s business model, the marketer doesn’t need to run to the analyst every time he wants to create a new report.
The analyst merges the data up to the business model once so that it’s easier to make edits later. The marketer can then use this data in modern reporting services that can work with modeled data (e. g. OWOX BI).
The new marketing analytics tools have come to save the day! Yes, marketers need to sort them out, but this is still easier and more convenient than SQL. In exchange, the marketer will receive data that can be trusted, reports in the correct sections, and applicable recommendations.
What do you need to do to make the magic work?
Step 1. Collect data with the help of ETL tools in a single place for subsequent analysis (cloud data storage, particularly Google BigQuery, is an ideal option for marketers).
Step 2. Clean and match the collected data.
Step 3. Merge the data for the business model.
Step 4. Connect the modeled data to the reporting service.
Step 5. Export data to the data visualizing service.
Such tools like Funnel. io, Supermetrics, OWOX BI, Datorama, Improvado, Kissmetrics, etc. are great for data collection. But, to correctly match it, clear, merge, deduplicate, and so on, you need the help of an analyst.
To model data, the marketer also needs the help of an analyst. But, it’s this one time only.
Note! If you have a small project, a pair of data sources (table with events plus expenses), and you need to build 2-4 reports, then the structure of calculating these tables will be pretty simple.
However, if we are talking about a real project with many tables with data from advertising systems, transactions from CRM, user information, extended product data, and all other data that get into Google BigQuery? It’s worth using tools such as DBT/Dataform to avoid 100500 entangled tables, you could make changes once, and they could be applied to the entire model.
Once your data is modeled, you can build a variety of reports to work with. As an example of a modern universal platform that collects and analyzes your marketing data from various sources, allows you to automate reporting, and transfers results to where you want, let’s look at OWOX Smart Data.
Using the Smart Data service, marketers can receive marketing reports without limits and sampling, based on merged raw data from Google BigQuery and export reports into Google Data Studio and Google Sheets.
There are two ways to get a report:
- Use the Report builder.
- Select a ready-made template from the gallery of the most popular reports.
Marketers can quickly gain access to marketing insights of any complexity, using pre-made dashboards or customized for the company’s requirements. For quick tasks, ready-made reports in OWOX BI Smart Data are suitable, whereas for complex ones — the data preprocessing and downloading using SQL queries from Google BigQuery — is needed.
Let’s look at the Report Builder — you need to determine which metrics and parameters you want to see in the report, choose the type of report you want, and the date range. Smart Data generates a SQL query of the required data in Google BigQuery, and a marketer gets a visual report.
The use of reports such as cohort analysis, LTV calculation, ROPO analysis, and others allow marketers to consider all customer touchpoints and monitor marketing campaigns’ effectiveness.
Gain clarity for better decisions without chaos
No switching between platforms. Get the reports you need to focus on campaign optimization
The current approach to working with data is not optimal. Marketers and business users most often cannot build reports on their own because it’s too complicated. To answer their questions, they have to set tasks for analysts and wait for reports. And again, and again. After all, until you answer the first question, you don’t know what the second will be.
In the end, it turns out that the data are used to monitor the current situation and analyze the past, but rarely — to manage the future. Therefore, data will bring much more value if business users have the opportunity to work with them to make decisions independently.