[New product] Announcing PickyStory's next product -- Subscriptions 🔄

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.