Automate your QR code creation and tracking
Google Sheets is more than a place to store your QR code files, it’s also a great place to generate them and track their use!
Summary
1) Prepare your URLs for QR Code Creation
Use ENCODEURL function in Google Sheets to encode URL and maintain its integrity
2) Adding Tracking to your URLs
Add UTM parameters to URLs to track clicks and organize them in Google Analytics
Create a table in Google Sheets listing required UTM parameters for Google Analytics and define values
Use CONCATENATE function in Sheets to combine URL and parameters
3) Generating QR Codes from your Tracked Links
Replace encoded URL in cell A1 with concatenated, tracked URL containing UTM parameter
Use the provided formula (below) to generate QR codes with tracking which calls Google's API using IMAGE function in Google Sheets
SAVE TIME:
Fill out the form to access a Google Sheet I’ve already built for you - create tracked QR codes ASAP!
The blog post continues below the form.
QR Codes are Back and Popularity is Growing
QR codes have made a comeback! Just when we thought they’d been put to rest, we see them everywhere again. Here’s why:
Commonly we have multiple devices within reach. This makes it easy to scan a QR code with your phone while it’s being shown on your laptop during a Zoom meeting.
QR codes can facilitate payment. A painter could sell their work at a community fair by connecting a QR code to Zelle or Venmo, for example.
You can bridge online and offline advertising. Imagine a QR code on your booth at a trade show. Users scan that QR code and their activity will be tracked once they reach the site your QR code is connected to.
QR codes help facilitate contactless interactions as a touch-free way of accessing information which was necessary during the pandemic.
Step 1 - Preparing your URLs for QR Code Creation
Does your business require you to generate and manage many QR codes? Well, Google Sheets is more than a place to store your QR code files, it’s also a great place to generate them!
The following argument prompts Google Sheets to encode the URL to maintain its integrity (i.e.: translating unprintable or special characters to a universally accepted format by web servers and browsers).
ENCODEURL(A1))
In the above argument, the A1 field refers to the URL you need to encode (prepare). Place your URL in cell A1 and use the above formula.
Step 2 - Adding Tracking to Your URLs - aka UTM Parameters
A huge benefit of QR codes is their ability to bridge offline and online advertising. To capitalize on this, you can add UTM parameters to your URLs which are small snippets of code that fall at the end of the link. By adding UTM parameters, you’re telling Google Analytics to log the click and organize it into the “Campaigns” report of Google Analytics.
Since you’re already working in Sheets - let’s stay there! It’s true you don’t need to leave your Google Sheet file to generate a tracked QR code.
Your first step is to create a table that lists the following UTM parameters on the left:
Website URL* (full URL)
Campaign Name*
Campaign Source*
Campaign Medium*
Campaign ID (for Google Ads Campaign)
Campaign Term (if Google Ads)
Campaign Content (if Google Ads)
Use an asterisk to denote required UTM parameters for Google Analytics to log the activity. You can mimic the table above. On the right side of those parameters, add blank fields. You will then use those fields to actually input and define parameters (i.e. Campaign Name may be “summer_2023_trade_show).
Next, use the CONCATENATE function in Sheets to pull together the URL and all its parameters. In this example, the parameters I’ve defined are contained in the C column in rows 7-13.
=CONCATENATE(C7,"?","utm_source=",C9,"&utm_medium=",C10,"&utm_campaign=",C8,"&utm_id=",C11,"&utm_term=",C12,"&utm_content=",C13)
The output will look like this:
https://www.example.com?utm_source=Exhibit_Booth&utm_medium=QR_Code&utm_campaign=Summer_2023&utm_id=9999&utm_term=enterprise_packages&utm_content=logolink
So now that you’ve appended the UTM parameters to the end of your URL with the above CONCATENATE formula, any user who follows the link will have that link-follow logged in Google Analytics “Campaigns.” In addition, skilled Google Analytics users can track much more than the source of the link-follow; they can design workflows to also see where users navigated to and whether they converted.
Step 3 - Generating QR Codes from your Tracked Links
Now you understand how the argument ENCODEURL(A1) maintains URL integrity and your links have tracking intact due to UTM parameters. This means you’re ready to call Google’s API to actually generate the QR code right in Sheets.
You will add the following argument to your formula:
=IMAGE("https://chart.googleapis.com/chart?chs=250x250&cht=qr&chl=”
Pulling the function together:
=IMAGE("https://chart.googleapis.com/chart?chs=250x250&cht=qr&chl="&ENCODEURL(A1))
You are now telling sheets to call Google’s API to generate a QR code and place your encoded URL from cell A1 behind it.
Remember in the “Preparing Your URLs” section we labeled A1 as the field that refers to the URL you need to encode (prepare) to generate the QR code?
Now replace your encoded URL with the concatenated, tracked URL by pasting it into the A1 field via right-clicking to select “Paste Special” then “Values Only.” In other words, you’ll now use the URL with UTM parameters (from the parameters table) to generate a QR code with tracking.
Final Suggestion
I recommend following the above steps to recreate this tool in Google Sheets or use the Sheet I already made for you - just make a copy and get started ASAP! Access the sheet via the form below.
Use my Sheet - Make a Copy to Get Started ASAP
Use the form to access a Google Sheet that’s already ready for you. Make a copy and generate QR codes ASAP.
