array(3) {
["ua"]=>
array(13) {
["code"]=>
string(2) "ua"
["id"]=>
string(2) "66"
["native_name"]=>
string(20) "Українська"
["major"]=>
string(1) "0"
["active"]=>
int(0)
["default_locale"]=>
string(2) "uk"
["encode_url"]=>
string(1) "0"
["tag"]=>
string(2) "uk"
["missing"]=>
int(0)
["translated_name"]=>
string(9) "Ukrainian"
["url"]=>
string(55) "https://roman.ua/ua/case/software-end-to-end-analytics/"
["country_flag_url"]=>
string(48) "https://roman.ua/wp-content/uploads/flags/ua.png"
["language_code"]=>
string(2) "ua"
}
["ru"]=>
array(13) {
["code"]=>
string(2) "ru"
["id"]=>
string(2) "45"
["native_name"]=>
string(14) "русский"
["major"]=>
string(1) "1"
["active"]=>
int(0)
["default_locale"]=>
string(5) "ru_RU"
["encode_url"]=>
string(1) "0"
["tag"]=>
string(2) "ru"
["missing"]=>
int(0)
["translated_name"]=>
string(7) "russian"
["url"]=>
string(52) "https://roman.ua/case/software-end-to-end-analytics/"
["country_flag_url"]=>
string(79) "https://roman.ua/wp-content/plugins/sitepress-multilingual-cms/res/flags/ru.png"
["language_code"]=>
string(2) "ru"
}
["en"]=>
array(13) {
["code"]=>
string(2) "en"
["id"]=>
string(1) "1"
["native_name"]=>
string(7) "English"
["major"]=>
string(1) "1"
["active"]=>
string(1) "1"
["default_locale"]=>
string(5) "en_US"
["encode_url"]=>
string(1) "0"
["tag"]=>
string(2) "en"
["missing"]=>
int(0)
["translated_name"]=>
string(7) "English"
["url"]=>
string(55) "https://roman.ua/en/case/software-end-to-end-analytics/"
["country_flag_url"]=>
string(79) "https://roman.ua/wp-content/plugins/sitepress-multilingual-cms/res/flags/en.png"
["language_code"]=>
string(2) "en"
}
}
NULL
We were approached by Skylum (ex. Macphun) to set up end-to-end analytics. In addition to bringing information from all sources into one database, we analyzed and set up the ROI calculation process.
This allowed the company to reallocate money for higher ROI products, channels and campaigns.
Photo editing application development
Macphun is a developer of photo editing applications primarily for the Mac OS X operating system. A perennial winner of the Apple Awards.
The largest product during the collaboration is the Creative Kit. Also, together with partners, the company developed Aurora HDR, a program for editing HDR photos.
In 2018, as part of a rebrand, Macphun changed its name to Skylum.
There were 3 main sources leading to the website:
Once on the site, the user could:
Thus, we had 3 scenarios of software purchases:
We were faced with the task of collecting information, combining it into one database for all sources, analyzing and calculating the return on investment (Real ROI).
Often, to track the effectiveness of application sales, developers prepare separate builds (custom builds) of programs for each partner or advertising campaign, where the name of the partner or campaign is stored inside the installer.
Thus, after installing the trial version of the product, the user is labeled as having downloaded the trial from a certain resource.
For Macphun this was not an option because:
Another Client of ours solved the problem with trials and custom build like this:
SourceBuster
Having analyzed the software purchase scenario, we had to record and track user actions at each stage. To do this, a mechanism was prescribed for recording user data in the process of buying software.
What was used: a script for determining the sources of attracting visitors to the SourceBuster, site, transferring sources of attracting visitors to the MySQL database, then combining data with FastSpring processing and technology for transferring data to GA via the Measurement Protocol and creating reports in the database.
We used SourceBuster to track purchases when a user left the site on the Mac App Store. In the Apple affiliate link, we “sewn up” the following parameters for each departure to MAS: application | campaign | medium | source.
As a result, we received statistics on sales from ads that led to the site in the Mac App Store partner account:
This helped us understand whether it makes any sense at all to lead the user from advertising to the site, and to take some of these users from the site to the Mac App Store, whether it is profitable or not.
Cons of selling software through the Mac App Store:
We took the solution from MacPaw. They wrote an SDK for Google Analytics and implemented events inside the application.
Using the SDK, we tracked which types of files are most often used for photo editing. We moved them to the top in the app description so that users could quickly find popular formats.
As mentioned earlier, we had several conversion options: conversion to trial, conversion to instant purchase, conversion from trial to purchase; and variables: average check, traffic price, trial cost, etc.
To understand what products and indicators you need to work with, we built a unit economy.
Thus, we were able to see the relationship between the metrics and clearly understand how, by changing one of the metrics, we affect the final result:
When we started working with the project, it turned out that the geotargeting of campaigns was quite wide, because software was sold all over the world.
Having compiled a portrait of the “Ideal Buyer”, we found patterns (using data from buyers and advertising systems) that boosted the increase in PSV.
1. When performing an action (download trial, subscribe to a newsletter, purchase), the user provides contact information (email)
2. Using the Sourcebuster a record is created in the database with the following fields:
Users used different browsers for the trial and for the purchase. Thus, from the point of view of Google Analytics 1, a user could have multiple clientID.
After receiving the data, we faced the task of merging users.
If the user already had our cookie, we did not update the data by source and date, but supplemented, if necessary, data by clientID and email.
If the user did not have our cookie, but we found the userID by email or clientID, we hung up the found cookie and updated the data in the database by email/_ga if necessary.
Thus, we could supplement user data and recognize the same user who may have come from multiple campaigns. For example, they first downloaded the program on their computer and then opened the link in an email from a mobile device.
While using the trial version of the product, the user received various follow-ups to the specified mail. The user could open the link on another device or in another browser, and we would get different clientIDs, and one user could be duplicated in the database.
We recognized the user from the email through the get-parameter mark in the letters. To guarantee “recognition” of the visitor, a personal parameter was added to each link in the letter, which helped us to “recognize” the user.
If, when following the link, we did NOT see our cookie on the user, we restored it using the mail address passed in the URL and wrote a new clientID to the database.
This logic allowed us to recognize on the site the user who downloaded the program on a computer and then opened a link in an email from a mobile device, for example
Further on, we merged transactions from processing.
1. The moment when the sale took place, the processing sent a “web hook” to our servers, which transmitted transaction, number, amount, goods, coupons, net profit and email.
2. We placed GTM in a stripped down form on the processing pages, which called:
After the purchase, user data was recorded in the database, which was also tied to a specific userID.
We had one feature: instead of the “Thank you for your purchase” page (which was not there), we used the processing page.
Very often, users left different postal addresses when ordering a trial/purchase, and in order for the user not to be duplicated in the database, it was necessary to constantly read our clientID, check for the presence of userID, plus read the data from the current source (for further analysis).
In order to get all the data, we opened an iframe on the “Thank you for your purchase” page with a link to our server. Then, we passed the transaction number and clientID in the get-parameters (just in case), thus gaining access to “our” cookies, and took from the server cookies the source / medium /campaign from SourceBuster.
Additional difficulties that we encountered:
At the time of purchase, we did a check by clientID (if our userID cookie was not there) and recorded a transaction in a separate table with data on sources, time / date and binding to clientID.
After receiving the webhook, an additional check was carried out against the userID database by email. If we found a user, we connected the purchase with an existing userID, if not, we created a new one and, if possible, immediately “hung” our cookie on the buyer (or did it on their next visit).
For repeated payments (purchases by installments or by subscription), the income amounts were tied to the userID by searching using the ID of the original (first) transaction. In addition, information about the payment was sent via the measurement protocol to GA (using the clientID and the sources of the first transaction).
On the “thank you” page, the processing has clientID and transaction ID — these are the keys.
On the iframe page from our server, in addition to the clientID, there is a source, medium, campaign …, by which the user cameь.
The webhook has a transaction ID and the composition of the transaction, the amount of earnings and the user’s email.
Using all these keys, we supplemented the data and glued them together in the database. In total, the database contained clientID, transactionID, Sourcebuster data, what the user bought, how much we earned, email and userID. In this way, we could build links between the site, the processing page and the final sale in the database.
To calculate the real ROI, we used the Power Query MS Excel add-in.
The data was pulled from two sources:
Expenses were imported into GA using OWOX BI Pipeline
Income was imported into GA using the Measurement Protocol (Revenue & Refunds), and into the database using a webhook from processing.
After processing, we received the following summary table:
“acquisition month” – “acquisition source” – “costs” – “number of new users” – “number of purchases by these users” – “revenue” – “profit” – “ROI”
Further on, we expanded and supplemented the table to a cohort by the date of attraction or to analyze the effectiveness of “converting” sources.
We had several data sources to be merged:
To combine data, we used Microsoft Power Query в Excel:
1. We create the key date, source, medium, campaign, for example: 01609_google_cpc_sale-30
2.We connect the tables and take into account 4 scenarios:
Here is what the data looked like
As a result, we managed to build a summary
We set up the Real ROI calculation process.
It turned out that ROI with PPC advertising is positive, but not as high as we would like it to be. This allowed the company to reallocate money into higher ROI products, channels and campaigns.
A quote from Forbes
«Our special likes go to the data analytics which allows every member of the team, including designers, to measure success down to the smallest detail. We want to know why something worked or didn’t work, and data analysis is the key!»
It’s always a pleasure to collaborate with Roman.
Roman has an eye for detail and is very result-oriented.
We know how to calculate marketing ROI
We have worked with 281 Clients and helped them enhance online marketing and earn more.
Why did we choose Roman.ua?
Because they are geeks.
We are productivity fans.
Your and our time is valuable and we want to make the most of it. We would be happy to make an appointment, put it on the calendar and talk in person or online.
But we will need to prepare for the meeting. To do that, we'll start communicating via email and send you a brief.
English
