[New feature] Spend X Get Y – now with tiered rewards! ⭐️

Track Your Shopify Ad Keywords Performance per Store with Google Analytics and HubSpot/(CRM)

5 minutes

Do you run Shopify Ads for your game changer application? Then, you know how hard to track keyword performance due to Shopify’s limitations. For example, how do you map a search keyword to a paying merchant?

However, by using this methodology, you can learn a more comprehensive view of your Shopify Ads’ performance. If you are ready to learn which customer installed your application by searching a specific keyword, let’s dig in.

Why You Should Track Visitors' Behavior?

In today’s world, data is the most powerful tool to manage your business. Understanding each visitor’s intent, and the followed path from the source to the end gives many opportunities to convert visitors into customers.

Additionally, when you can match your visitors with the Shopify Ads analytics, you will be able to understand the quality of the keywords you are bidding on in this competitive environment. For instance, you are bidding on a specific keyword in Shopify Ads and getting many installs for a good CPI. However, you realize many of these potential customers are uninstalling your application after a short time.

Since Shopify Ads does not allow you to know which customer installed your application through search, you can not understand the quality of installs for a specific keyword.

What are you Getting?

You will get an automated tool that you’ll be able to update on a daily/weekly/monthly basis to easily understand the performance of your Shopify ad keywords and match them with your real customers. This tool will help you to see the full journey of each of your leads or customers, and for free.

Get Pre-Made Templates

Step-by-Step Guide: How to Explore Search Terms of Your Shopify Ads & Organic Installers

As explained above, Shopify does not allow applications to match with search queries to the exact person. In other words, when a merchant installs your application, you can not know how they found your application in the Shopify App Store. At PickyStory, we have developed the closest approach to understanding the customer funnel for Shopify App Store installs.

In this guide, we will explain how you can analyze Shopify ads keyword performance by matching visitors’ visit time on your Shopify app listing and visitors’ conversion time in your CRM/Partner account. We are going to follow these 6 steps:

1. Prepare a List of Installs with the Date
2. Exporting Page Visits from Google Analytics
3. Formatting the Time and Hour of the Sessions
4. Matching Installers with the Sessions
5. Extracting the Exact Keyword from the URL
6. Analyze your Search Terms and Search Types

Previous required knowledge: Excel/Google Spreadsheet and Google Analytics

Step 1) Prepare a List of Installs with the Date

The first step of this analysis starts with the list of all of your installers, in other words, customers. There are different alternative ways to prepare this list such as using any CRM tools like HubSpot, SalesForce, or manually downloading the list directly from Shopify Partners.

All you need to do is prepare a list in any CRM tool where you can see:
– First name
– Last name
– Company name
– Create a date with a date and time stamp

If you prefer to use Shopify Partners, go to your app and click on the “Export” button after you choose the period you want to analyze.

Step 2) Exporting Page Visits from Google Analytics

Google Analytics is the most comprehensive web tool for understanding your visitors’ behavior. Additionally, you can set your goals and create your acquisition flows. If you have not integrated your Google Analytics to your Shopify App page, you can learn how here.

First, you need to prepare a new custom report to find all visited pages with a specific date and time stamp. Go to your Google Analytics where your Shopify App is connected, and click on the “Customization” section on the left. Under the “Custom Reports” tab, you can find the “New Custom Report” button. Here are the steps to prepare the report:

1. Give a title for your custom report
2. Set your report tab name
3. Set “Flat Table” as a type
4. Choose the following dimension in this order: Date – Hour – Minute – Page
5. Choose “Sessions” as a metric
6. Click on the “Save” button

After you create this custom report, choose the date range you want and export. In the file, you will have four columns as chosen above: Column A is Date, Column B is Hour, Column C is Minute, and Column D is Page.

Step 3) Formatting the Time & Hour of the Sessions

Since it requires using the same format in both lists to analyze, after exporting the Google Analytics report, we need to adjust the format. The export file has columns as explained above from A to D. To create DD-MM-YYYY HH: MM format we use DATEVALUE and TIME formulas.

1. Call your Column E as “Date of Install” (Write a title to E1 cell)
2. Use this formula for the rest of the cells in the Colum E

				
					=DATEVALUE(left(A2,4)&"-"&mid(A2,5,2)&"-"&right(A2,2))

This formula takes the year part (First 4 digits), month part (2 digits in the middle), and the day part (The last 2 digits) to create a time format.

3. Choose Column E and set the data type as “Date & Time” format.
4. Call your Column F as “Time of Install” (Write a title to F1 cell)
5. Use this formula for the rest of the cells in Column F

				
					=TIME(B2,C2,0)

This formula takes the hour from Column B and minute from Column C, then combines them in HH: MM format.

6. Call your Column G as “Date & Time of Install” (Write a title to G1 cell)
7. Use this formula to combine your Install Date and Install Time values

				
					=E2+F2

Automatically, you will get the required format type on your Column G.

Important Note: Before you start the calculations, ensure that two of the data sources have a common time zone, otherwise, you must add the required hour difference by using the TIME formula.

=Source cell + TIME(required number of hours to add, 0, 0)

Get Pre-Made Templates

Step 4) Matching Installers with the Sessions

So far, we have prepared two different lists where one list has the installers (Name and Date) and the second list has the sessions (Page and Date). As you can derive, the date will be the key to matching corresponding names to the pages.

After you have prepared these lists in different tabs on Microsoft Excel or Google Spreadsheet, open a third tab where you will be able to match them with the formula. Since it is expected to have some time between the page visit and install time, we need to find the closest ones. Therefore, using VLOOKUP or EQUALS formulas will mostly fail. The best alternative way is using MATCH/INDEX formulas together.

The first column of the third tab (calculation) should be the list of the installers. The second column should have the MATCH/INDEX formula with ArrayFormula.

Here is the formula you need:

				
					=ArrayFormula(index(List of the Pages exported from Google Analytics, match(Min(ABS(List of the times exported from Google Analytics-List of the times exported Shopify/CRM)), ABS(List of the times exported from Google Analytics-List of the times exported Shopify/CRM),0)))

By using the ABS (absolute) formula, we are ensuring the time difference is always a positive number since time can not be a negative value.

This formula compares each install’s date with the session times one by one by finding the absolute differences and returns the closest time’s page. As we should consider, this is not the optimal way to find the page, however, this approach gives us the most feasible solution.

After these four steps, you will be able to see the page for each install, however, some of the pages might be “(not set)” due to Google Analytics privacy policy restrictions.

Step 5) Extracting the Exact Keyword from the URL

After this point, the rest of the steps are optional but will help you to see keywords much easier. Since the search terms are already in the URLs, you just need to extract them alone into another cell.

Here, we will show how to extract the search term, and the click type which can be either Shopify Paid Ads or Organic. If you are familiar with the Shopify URL Structure, you know that the search term is written after “surface_detail=” and the type is written after “surface_type=”.

1. Create another tab in the same sheet called “Keywords” or give another name.
2. Fill Column A with the exact URLs you have obtained from step 4.
3. In Colum B, substitute the “surface_detail=” with a unique character such as asterisks “*”.

				
					=SUBSTITUTE(A2,"surface_detail=","*")

4. In Column C, you can delete all the characters until the unique letter. (“*” for our case)

				
					=IFERROR(RIGHT(B2,LEN(B2)-FIND("*",B2)),"N/A")

5. Then, you also need to remove everything from the last part of the URL until it comes to the search term. In Column D, you can delete everything until the “&” letter since the URL contains different parameters combined with “&”.

				
					=IFERROR(LEFT(C2,FIND("&",C2)-1),"N/A")

6. To find click type, we use the same strategy as keywords. Substitute “surface_type=” with a unique character such as “*” in Column E.
7. Then finally, in Column F, you can remove everything until the URL hits the unique character (in our case, it is “*”)

				
					=IFERROR(RIGHT(E2,LEN(E2)-FIND("*",E2)),"N/A")

Since the type is written at the end of the URL, you don’t need to remove any other text.

Step 6) Analyze your Search Terms and Search Types

When you successfully follow the first five steps above, Column D will have the search term, and Column F will have the search type.

Take these values somewhere else to work on a clean page and look at their installers. Now, you have everything you need to match the corresponding installer to the search term and search type.

Now, you can evaluate each keyword not with the quantity but also with the quality level.

Download the Free Keyword Matching Tool

Enter your name & email address, and we will deliver the file to your inbox!

Grow your sales with high-performing deals.

Best 10 Back-in-Stock Apps for Shopify: Never Lose Any Sales Again!

Shopify Capital: Review & Eligibility (Pros & Cons)

Published on August 2, 2022
Topics: Inside PickyStory

[New feature] Spend X Get Y – now with tiered rewards! ⭐️

Why PickyStory?

Discover PickyStory conversion platform

Get inspired from industry leaders

Connect your tools

Main tools

Create custom high-converting bundles

Make irresistible offers at key moments

Intelligent cart to boost revenue

One-click upsell to increase every order

Read how PickyStory helped industry leaders

By use case

Increase your Average Order Value

Customize your customers’ journey

Sell while auto-syncing inventory

By category

See PickyStory in action

Discover

See PickyStory in action

Learn how to increase sales

Get inspired from industry leaders

Check the news from PickyStory

Get support & connect

Read product documentations

Enable your merchants with PickyStory

Ultimate guides

Get insight on how to create high converting bundles

Reveal the power of an intelligent cart in your store

Start offering in checkout pages with One-Click Upsell

The ultimate bundles guide

Track Your Shopify Ad Keywords Performance per Store with Google Analytics and HubSpot/(CRM)

Table of Contents

Why You Should Track Visitors' Behavior?

What are you Getting?

Step-by-Step Guide: How to Explore Search Terms of Your Shopify Ads & Organic Installers

Step 1) Prepare a List of Installs with the Date

Step 2) Exporting Page Visits from Google Analytics

Step 3) Formatting the Time & Hour of the Sessions

Step 4) Matching Installers with the Sessions

Step 5) Extracting the Exact Keyword from the URL

Step 6) Analyze your Search Terms and Search Types

Download the Free Keyword Matching Tool

Grow your sales with high-performing deals.

MOST POPULAR

Platform

Main tools

By use case

By industry

Resources

Company

PickyStory is the complete e-commerce upsell platform. PickyStory enables e-commerce brands to make personalized offers by helping customers to discover more products.

Not enough sales?

Capture every drop of revenue with PickyStory's complete upsell platform.

See the MAGIC Live

Frequently Bought Together

Present bundles of products that are frequently purchased together, simplifying the decision-making process for your customers

What is Frequently Bought Together?

It is a recommendation scenario that suggests additional products to the shopper, which are often purchased by other customers in conjunction with the item they are currently viewing or considering purchasing.

Why are brands using it?

How Zenbivy uses Frequently Bought Together

www.zenbivy.com

Create Frequently Bought Together

Build Your Own Bundle

Offer shoppers the ability to create their own bundle of products by selecting from a range of available options

What is Build Your Own Bundle?

Build Your Own Bundle scenario provides shoppers with the flexibility to select specific items or features that best meet their individual needs and preferences. The selection is based on a pre-curated set of products by the merchant.

Why are brands using it?

How Prodigy uses Build Your Own Bundle

Prodigy enables its shoppers to build their own bundle of golf discs, fully personalizing the shopping experience. Shoppers get an easy way to select their favorite discs while Prodigy consistently increases its AOV.

www.prodigystore.eu

Create Build Your Own Bundle

Bundle as Product

Combine multiple individual products into a bundle and sell them as a single unit

What is Bundle as Product?

Bundle as Product scenario involves bundling multiple individual products together and selling them as a single unit. This strategy offers shoppers a curated selection of items that work together or complement each other.

Why are brands using it?

How Gravity Fitness uses Bundle as Product

Gravity Fitness knows that it's not an easy task for shoppers to collect all the required pieces for a complete home gym. That's why they have created pre-made home gym bundles for an easy shopping experience. Now they have fewer SKU issues, a higher AOV, and happier customers.

www.gravity.fitness

Create Bundle as product