Web + app attribution for omni-channel business

The company in question is a shopping club and online furniture and decor store. The company has two main directions of communication with the client. The first direction is the online store, which is represented by the website and its mobile version. The second direction is the club of closed sales with web version and mobile application. Promotions last from 2 to 4 days and are updated every day. To get access to the sales without registration is impossible. Users-clients of the club receive daily mailings of promotions. In fact, these communication models are different business models. At the same time the company's client base is the same, the sales targets are also the same for the two business models.

Thus, the company faced the following problems and limitations:

1. Three ecosystems: Site1, Site2, Apps (iOS, Android). Each of them is connected to the CRM, but the user data is not combined into one path. Google Analytics was used for web analytics, and Adjust was used for the mobile app. Orders are created in the CMS of the site, and then accumulated in the CRM.
2. Lack of confidence in the data, as they clearly did not add up to a common picture to solve marketing and business problems. The data in all three UA/CMS/CRM systems were different from each other. Marketers could not determine the effectiveness of advertising channels in the chain to bring the customer to the target action - which channel, what weight and what value it has. And the business, respectively, could not define which channels are effective in terms of monetization.
3. Cost data was provided by agencies in CSV and then uploaded to Google Cloud Storage (GCS). Often due to manual input errors, the customer value calculations were incorrect.
4. Data was pulled from GA in aggregated form by UTM and summarized in report tables
5. The cohesion of the data along the user path was not respected. The user data was not merged into one path.

The system of analytics, in fact, was built, but the number of tools involved was enormous. For example, data from Adjust was uploaded to Amazon S3, and from there to Google BigQuery, despite the existence of direct connectors between the services.

The first phase of the project was an audit. As a result of the audit, in addition to excessive complexity, a key flaw was revealed - the analysis was based on the principle of comparing tables aggregated at the level of advertising sources. Since the aggregated data does not contain an identifier, it is impossible in principle to combine the user path between APP/Web/CRM. Therefore, it was decided to use raw data containing user IDs, combine them using common keys and only after that aggregate them at the level of advertising sources for visualization.

Thus the system only closed standard cases like landing and ordering on the site with a redemption tracked in the CRM, or, installing the app and ordering in the app, also with a redemption adjustment. More complicated cases could not be tracked. For example, if an ad led to the website of an online store, and then the user came to the club's website from a different device and placed an order. Or if an ad led the user to the website, and then the user clicked "install app" and placed an order in the app, then the order was attributed to the app.

The proposed scheme is standard for working with raw data. All data was loaded and then calculated in Google BigQuery.

The interesting thing about the project was the following: all data was loaded in the context of individual events, i.e. raw site data from Google Analytics 4 was taken as a source and a ready-made hitstream in Google BigQuery was obtained immediately. Adjust was then linked directly to GBQ, and the part of the schema that loaded data from the internal system to GBQ via Fivetran was untouched.

Ad costs from ad accounts were automatically uploaded to GBQ, then the GCP Apps product aggregated the data into sessions, the sessions were cross-devised into a single chain using common keys, the costs and revenue were also attributed to the user level. Raw data occupies a very large volume, so it is aggregated daily by all possible parameters and metrics into a "flat" table before being displayed on the dashboard.

Due to the fact that all expenses and revenues are attributed to the session level, the company’s dashboard displays correct data in any available slices, and the revenues and expenses report always converges (unlike, for example, Google Analytics, where you cannot mix parameters related to the hit, session and user in one report).

The problem of user path interruption when navigating between sites was solved very simply by installing one common counter. Before the project, different counters were installed on the sites, respectively, the user received different cookies and the path was interrupted.
The case of installing the application by clicking on the button on the site was also resolved by passing the Client ID to Adjust and then to GBQ.

Also, even if the user doesn't login to two sites, his path is still linked in a single chain. This is due to the fact that if a user logs in at least once on one site, his cookie is linked to his login, and the data is updated to a depth of 180 days. As a result, the anonymous cookie ceases to be anonymous and the user is identified.

More complex user paths have also begun to be identified. For example, if an ad campaign led a user to one site and then another ad campaign led a user to another site where the user had already placed an order. Thanks to the implementation of feedback mechanics, where each unique communication with a user has its identifier added via SMS-links or Email-links, the user can now be identified by clicking on such a link retrospectively.

One of the open questions was the choice of attribution model. Three models were implemented in company's dashboard: Last Non-Direct Click, First Click, and Multichannel attribution.

The Last Non-Direct Click model allows the marketing team to see at a glance where they can quickly invest their budget and generate revenue quickly. The First Click model shows which channels can be used at the top level of the funnel to sway brand awareness. The multi-channel model allows you to account for all the channels present in the chain and not shut down budgeting for channels that might seem ineffective in other models.

One of the team's wishes was custom attribution, which attributes a result based on the user being at a certain level of the funnel. This is because in terms of ad campaign setup, there is always one optimization element: traffic, or a specific targeted action, sometimes reach, engagement, and sometimes shows. When working with an ad campaign, marketing works on one targeted action in order to guide the user through the entire funnel. Determining this most effective touch will give the marketing team the ability to allocate the budget in a way that will maximize the business benefit of guiding the user through the entire funnel. The common belief that increasing the CPA budget leads to more customers does not work in practice, so the hypothesis was born that if you remove some of the budget from the bottom level of the funnel, and allocate it to the intermediate levels, it will have a greater effect.

Also at the request of the company’s team, an unusual cohort scheme was implemented. For a project with more than 10 years of history, 95% of the revenue comes from repeat purchases, so the ability to distinguish the revenue of current month users from the revenue of the permanent customer base allows you to assess the real effect of marketing, especially since the processes of working with the new and current base in the company are separated. Cohorts are calculated by the month of attraction, as well as by the first registration and the first purchase. Rules have also been defined for how a user moves from one cohort to another For example, reactivating a user after six months of inactivity moves them into a new cohort.  The key difference between the cohorts calculated by the GCP Apps product and the cohorts in GA or Adjust is the availability of costs. For each month's cohort, both initial and recurring engagement costs and all purchases are accumulated. This allows you to draw conclusions about what the payback period of user engagement is, taking into account the cost of re-engagement.