How to Monitor Multiple Business KPIs in a Single Dashboard: Success Story of MatahariMall

About MatahariMall

Mataharimall.com — is a sister company of a major department store in Indonesia, providing a wide range of fashion products and allowing customers to complete their head-to-toe look, from daily outfits to special occasion rig out. Founded in 2015, the company was the first one in Indonesia to create brand awareness and attract customers online, to drive them to the offline stores. Moreover, with the strict quality control, MatahariMall obtained customers’ trust and devotedness providing a free 100-day product return if not satisfied with the purchase.

Goal

With gigabytes of data in daily Google Sheets tables, MatahariMall’s analysts were looking to combine the data on web behavior and from the app, along with the ad cost data and completed orders from the CRM system, in a single automated marketing report. This would let MatahariMall’s specialists conduct a well-timed analysis of all the marketing initiatives, correct the bids in ad services and properly re-allocate their budget.

Challenge

MatahariMall needed a certain combination of multiple custom metrics and dimensions, to be combined in one report. To be more precise, there are about 30 metrics necessary, like ROAS on two types of attribution models, CPC, CTR, and others.

With little left to doubt, the amount of data and the number of required metrics were gigantic and definitely wouldn’t fit into a Google Analytics report. Another glaring issue was that managing and refreshing the reports on the multiple channels was done manually. It was truly exhausting and time-taking: about 4 burden hours a day.

Solution

To see all of the necessary metrics in one convenient report, the specialists from MatahariMall and OWOX BI decided to bring together all the data in a single cloud data warehouse, Google BigQuery. This would allow analysts to merge all the information for calculations and visualize it in a report. Moreover, MatahariMall would have access to the necessary data without the need to maintain any additional servers, saving valuable resources that can be used for other projects. That’s why the specialists from both companies slapped together the following plan:

• Collect all the behavior data in Google BigQuery, using the BigQuery Export for Google Analytics 360 to get web data.
• Get behavior data from the app in GBQ, via the OWOX BI Pipeline from AppsFlyer to Google BigQuery.
• Set up cost data import from ad services to Google BigQuery using OWOX BI Pipeline, for further calculations of the real efficiency of ad campaigns.
• Import CRM data with order history to Google BigQuery via a custom connector, to get the proper information on the revenue brought by marketing efforts.
• Build a ML Funnel Based attribution model and send the calculation results to Google BigQuery, within the OWOX BI interface. This way the company will see how all the channels contribute to the conversion.
• Match the data from different sources in Google BigQuery, to calculate all the necessary metrics.
• Automate calculations and send the results from Google BigQuery to Data Studio and Google Sheets, for building reports.

Below is the data flowchart for MatahariMall:

Let’s have a gander at every step in more detail.

Step 1. Collect Web Behavior data in Google BigQuery

MatahariMall collects behavior data from the website and application. Since the company is a GA360 license owner, MatahariMall uses the native integration with Google BigQuery to get the unsampled user behavior data from the website on a daily basis.

Step 2. Collect App Behavior data in Google BigQuery

The company uses AppsFlyer to collect and analyze behavior data from the app. To get the whole picture of the user behavior, MatahariMall’s analysts decided to send the AppsFlyer data to Google BigQuery via OWOX BI Pipeline.

Step 3. Import cost data

Google Ads has got a native integration with Google Analytics, therefore you can see the data from this ad service in GA reports. To add the information from non-Google advertising systems like Facebook, Instagram, Criteo, etc., MatahariMall uses OWOX BI Pipeline. This way the company can properly measure ROAS, cost per order and the revenue from each of the channels, to appropriately evaluate the advertising efficiency.

Step 4. Import the CRM data

To accurately know the revenue received by the company from the offline purchases, as well and to save more time on manual routine, the OWOX BI team helped create a custom connector for daily uploads from MatahariMall’s CRM system to Google BigQuery.

Step 5. Build a ML Funnel Based attribution model

Standard attribution models don’t evaluate the contribution of each session that leads to a conversion. To properly evaluate the input of each of the company’s channels to conversions and compare it with the results from the Last Non-Direct model, the OWOX BI specialists built a ML Funnel Based attribution model for MatahariMall. Now the company can compare the efficiency of their online campaigns, using the calculation results from both, ML Funnel Based and Last Non-Direct models.

Step 6. Match the data from different sources

MatahariMall has terabytes of data in different systems and in different formats. Therefore it took a while to match all the information for building reports. After clearing the roadblocks and combining all data in BigQuery, OWOX BI analysts created an SQL script to calculate all the metrics needed. This way MatahariMall’s C-level specialists obtained a report providing fresh data on a daily basis, to make instant data-driven decisions.

Thus, OWOX BI specialists formed two separate tables for web and for app data with a full set of required metrics for further monitoring.

Step 7. Build reports

After bringing together all the data in BigQuery, the OWOX BI team wrote an Apps Script query to automatically run calculations for the previous day and to add results to the main table in GBQ.

The OWOX BI specialists also added the following part to our Apps Script query: first, they check whether there are all days from the last 7-day period present in the final table. If not, the query adds the missing day as well, to make sure that nothing goes wrong. Yep, the company’s specialists can now feel sure that nothing’s going to be missed out from their terabytes of information.

Thus MatahariMall scored an automated dashboard in Google Data Studio along with the Google Sheets tables containing raw unsampled data:

As you can see, the report provides a variety of the metrics, allowing to compare the ROAS for all of the campaigns and to enable or disable some of them if necessary. This way MatahariMall is in full control of their marketing budget. Please note, that the numbers in the screenshot were changed for confidentiality purposes.

All the metrics are calculated on daily basis using Apps Script, to provide the most relevant information for data-driven decision making.

‘‘

It is always a great pleasure to work with the market leaders. But at the same time, it is also a great responsibility. It was quite a challenge to work on combining data from all the different systems and building a single dashboard to monitor the main marketing KPIs. But Our collaboration with the colleagues from MatahariMall was truly fruitful, so we achieved our goal.

Ekaterina Ganopolskaya,

Digital Analyst at OWOX BI

Results

Having combined all the data, MatahariMall obtained an automated single report for all their business KPIs. This added up a set of advantages for the company:

• Now MatahariMall can control the bids for the ad campaigns in near-real time and ensure the marketing budget is not going down the drain.
• The company obtained an automated marketing report to accurately measure the performance of all the advertising channels, across different platforms.
• With ML Funnel Based attribution model, MatahariMall’s specialists can compare its calculation results with the Last Non-Direct model. This already let the company find out that some ad campaigns were given three times more or two times less credit, and saved MatahariMall a fair amount of money.
• The report automation now allows the company to save 4 burden hours a day and reduces the factor of human error.

Thanks to all of the aforementioned, MatahariMall’s analysts currently spend their precious time on other important tasks instead of the same-type routine reports, and the C-level specialists finally feel in control of the marketing efforts and results.

We’ll keep you updated on the further results of our cooperation with MatahariMall, so stay tuned. Oh, and don’t hesitate to drop a line in the comment section if you have any questions. In case you’d like OWOX BI to help you with your marketing routine, we’d be happy to help!