Recovering 300% More Revenue by Matching Online Activity to Offline Sales

When setting up e-commerce conversion tracking, you’ll often find that most of the events worth tracking are online such as add to cart, checkout sessions or online transactions.  

However this conversion tracking model doesn’t work well with bespoke, high cost e-commerce shops. I quickly discovered this whilst working with william walter, an antique silver e-commerce business that has over 80% of their conversions as either:

  • Form submissions.
  • Mailto:clicks.
  • Tel:clicks.

In this type of scenario, I decided that the best way to overcome this was to focus on improving the attribution of the form submissions (as it had the highest number of conversions and it’s the easiest to solve).

Currently the client is running Google Ads, Microsoft Ads, SEO and Social Media (organic) and email marketing as acquisition channels. Therefore we setup UTM links for each channel which included:

  • utm_source
  • utm_medium
  • utm_campaign
  • utm_term

I.e. A visitor coming from Google Ads would be tagged as: https://williamwalter.co.uk/?utm_source=GoogleAds&utm_medium=cpc&utm_campaign=GoogleAdsCampaign&utm_term=GoogleAdsKeyword

Furthermore it is possible to dynamically setup the utm tracking and extra values in both Google Ads / Microsoft Ads campaigns. 

For Google Ads:

  • Click on a single campaign
  • Click settings
  • Click additional settings

Here is the final UTM automated out for all links through a Google Ads campaign: {lpurl}?utm_source=google&utm_medium=cpc&utm_campaign={campaignid}&utm_term={product_id}&adtype={adtype}&merchant_id={merchant_id}&product_channel={product_channel}&product_country={product_country}&product_partition_id={product_partition_id}&shop_code={shop_code}&device={device}&devicemodel={devicemodel}&loc_physical_ms={loc_physical_ms}&loc_interest_ms={loc_interest_ms}

Okay, so we’ve automated Google Ads/Microsoft Ads, however for the other campaigns this was achieved manually. 

Now that all as many channels are actively being decorated with utm_values, we created a client side GTM container tracker service using localStorage to store the UTM data and HTTP referrer data (this is useful for attributing SEO, if the previous referrer was google.com/google.co.uk).

You can access the JavaScript client side tracking service here (remember to change https://williamwalter.co.uk/ to your desired website)

So at this point we have all of the marketing channels decorated with UTMs and some client side javascript to capture this and store it within localStorage.

However we then needed to create hidden form fields to push this data into FireStore. (note that if you want to use the trackerService.js, you will need to add the relevant names to your existing forms, i.e. <input name=’utm_source’ /> ):

So What Are Hidden Form Fields?

Hidden form fields are a special type of input that allows you to store information that is not displayed to the user. This information is typically used to process the form submission on the server-side. Hidden form fields are not visible in the form, but the data is sent to the server when the form is submitted. 

Hidden form fields are a great way to store information that you need to process on the server, but that you don’t want the user to see. For example, you might want to store a product ID in a hidden form field so that you can look up the product on the server when the form is submitted. Or, you might want to store a security token in a hidden form field to make sure that the form submission is coming from a trusted source. Hidden form fields are easy to create in most web development frameworks. 

In HTML, you can create a hidden form field by using the <input> tag with the type=”hidden” attribute. For example, the following code creates a hidden form field with the name “product_id” and a value of “12345”: 

<input type="hidden" name="utm_source" value="12345"> 

When the form is submitted, the data from the hidden form field will be sent to the server just like any other form field. You can access the data on the server using the name of the field. In the example above, the field would be accessed as “product_id” on the server. 

Why Are Hidden Form Fields Useful?

Hidden form fields are a useful tool for storing information that you need to process on the server. However, you should be careful about what information you store in hidden form fields. Hidden form fields are not secure, so you should never store sensitive information in hidden form fields. For example, you should never store a password in a hidden form field.

The Technology Stack and Data Flow:

Here’s a loom video describing the whole data architecture: https://www.loom.com/share/a4128df409ca4405aa5cba5b765d4fa5

https://www.loom.com/share/a4128df409ca4405aa5cba5b765d4fa5

There was a significant amount of data engineering that went into building this data attribution system. So let’s look at the sequential data flow all the way from an initial marketing channel(s) to the database:

Marketing Channels → Website → GTM (trackerService.js) → Hidden Form Fields → Form Submission → Create a webhook → Send form data to Cloud Run FastAPI server → Store data in FireStore noSQL database.

As you can see from above, we decided to create a webhook architecture using WpWebhooks which sends the form data into an API and finally the data is stored in FireStore.

An individual record in the FireStore database has the following keys (some values have been removed to anonymise the data):

Matching The Form Data In FireStore With The Client’s CRM:

I created a python script to automatically download the relevant data from firestore.

import firebase_admin
from firebase_admin import firestore
import json
import pandas as pd
pd.options.mode.chained_assignment = None
 
if not firebase_admin._apps:
     cred = firebase_admin.credentials.Certificate('./credentials/service-account.json')
     firebase_admin.initialize_app(cred)
db = firestore.client()
 
leads = db.collection('accounts').document('37429704-54d3-433d-89f6-602677f3764c').collection('leads').stream()
df = pd.DataFrame([lead.to_dict() for lead in leads])
df = df[df['utm_medium'] == 'cpc']
df = df[['first_name', 'last_name', 'email', 'message', 'date']]
df.to_csv('google_ads_leads.csv', index=False)

Also, notice above how the data is being filtered on utm_medium == ‘cpc’ and at the time of this analysis I was only investigating Google Ads performance.

In terms of the matching, with some clients they will be able to match against email, however in this case the client could only match in their CRM by first_name and last_name

Pro Tip: So if you collect more data upstream within your lead generation forms (i.e. first name and last name rather than just email), then you have a better chance of matching the data against your client’s CRM.

What Were The Outcomes For The Client?

From initially looking at the ROI of Google Ads, it was negative (£5k spent with £3k revenue gained). However after downloading the relevant data from FireStore and matching the leads with a utm_source=’google_ads’, we found that £12k came from offline!

It just goes to show that if you have the vast majority of your conversions happening offline but you’re still running an e-commerce business, then it’s well worth investing in additional tracking techniques. 

Scroll to Top