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.
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.
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.
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:
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.
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:
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.
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.
=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.
=TIME(B2,C2,0)
This formula takes the hour from Column B and minute from Column C, then combines them in HH: MM format.
=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.
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.
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=”.
=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")
=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.
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.
Enter your name & email address, and we will deliver the file to your inbox!
PickyStory is the e-commerce conversion platform. It enables merchants to uplift their revenue in store. PickyStory offers a variety of deals like Bundle Products, Combo Products, Bundle Builder, Kits, Looks, and coupons.
© 2022 PickyStory
Schedule a call to learn more about PickyStory's e-commerce conversion platform
By entering your email, you agree to receive marketing emails from PickyStory.
Convert more visitors into buyers with PickyStory's e-commerce conversion platform
By entering your email, you agree to receive marketing emails from PickyStory.
