Posted: June 23, 2021

Updated: July. 22, 2022


Google Sheets and apps scripts use cases for digital marketing projects.


Separate URLs into their Components

If you're working in SEO, at some point you will have a need to separate URLs into their components, such as for analyzing a sitemap or site structure. Go to the data menu in google sheets → split text to columns, and then choose "/" as the separator. Or use the split formula: =SPLIT(A1,“/”,true,true)


Check Your Users' Browser Window Widths

This is useful when creating a new landing page. Get the browser size (both width and height) in Google Analytics, and then export that data to Google Sheets to see the distribution of browser window widths. I first learned about this in a blog post from 2018, and have used it regularly. It's a bit old but still works the same: How to make a chart of your users' window sizes.


Ad Spend Tracker

When managing PPC campaigns, I usually setup a quick Google Sheet to track the monthly ad spend and make sure that I'm spending the budget evenly.

=DAY(TODAY())/DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,0)) 

If I have enough accumulated data, I also create a few simple formulas to project the total monthly conversions, to make sure I'm on track to meet my goals.


This is also possible with with Google Data Studio, as explained in this Medium article. However, Google Data Studio has been a bit finicky for me these days especially if you bring in blended data, so I prefer keeping my analysis in sheets if possible.


Organic Traffic Forecasting

As a prerequisite to performing this function, you'll need to prepare a competitive research of your site and competitors. Include basic metrics such as target keywords, domain rating, Google ranking, and site traffic. Then, use a multi factor forecasting method to create a formula for predicting site traffic based on domain rating and Google ranking.


Google Ads add-on

There is a built-in Google Ads add-on in Google Sheets that can pull campaign data. I've found this tool to be a bit buggy so I don't use it to automate regular reporting, and my view is that you should be checking your Google Ads account itself anyway. But the add on is useful for quickly pulling data for one-time analyses, especially for larger accounts.


Google Analytics add-on

Similarly, there is also a Google Analytics add-on in Google Sheets. I recently started using it after talking to colleague, and had an overall better experience than with the Google Ads add-on. You can automatically populate Google Sheets with analytics data, modify it where needed (fix historical data, make any customizations, etc.) and use that to import into Data Studio.


Identify SEO Issues

If you're an SEO professional, you are probably already using tools like ahrefs or SEMrush. But in an emergency, you can also quickly identify SEO issues with Google Sheets by importing the html of pages in your site, then checking for things like missing h1, title, meta tags, image alts, etc.


Use the IMPORTXML function to scrape structured data from webpages, then use XPath query to specify which part of the XML to extract.


Some simple XPath queries:

  • //h1 - to scrape h1s

  • //title - to scrape titles

  • //@href - to scrape links

Other common XPath queries for SEO:

  • //meta[@name='description']/@content - to scrape meta description

  • //link[@rel=’canonical’]/@href - to scrape canonicals

For JavaScript elements, something like this works.

  • //script[contains(.,'gtm.js')] - to scrape (check for) GTM code

RegEx

RegEx (regular expression) is a sequence of characters to define a search pattern, often used for cleaning and manipulating data. It is available in Google Sheets find and replace, as well as many programming languages.


For example, Google BigQuery is based on SQL but supports RegEx as well, allowing users to write cleaner code and avoid complicated subqueries.


In standard SQL, the query below extracts the substring between two parentheses.

substr (column, instr (column, '(')+1,instr (column, ')') instr(column '(')-1)

In ReGex, you can rewrite it as follows.

regex_extract (column, r'/((.*)')---

---


The next few examples use Google Apps Script, which mostly runs on JavaScript 1.6, with some additions from 1.7 and 1.8. So you’ll need some JavaScript knowledge to edit the script and link it to Google properties, but not to the level of app or web development.


My GitHub repository for Google Apps Scripts for SEO includes scripts for retrieving html server response status codes, and destination URLs for redirected pages. I also have a separate general repository for Google Apps Scripts for automating everyday workflows such as email alerts and calendar events.


Confirmation Emails

Although Google Forms already has an option to send a standard confirmation email, Google Apps Script allows for further customization - send different confirmation emails depending on the user's responses, add external links, etc. This post by Google Workspace for Developers that explains in depth how to do this.


Site Migration

Use Google Apps Script to make sure all pages are accounted for properly during a site migration. You can write a script to keep an eye on the status code of all pages before and after the migration, or determine the redirects chain if any. Another use case is checking which pages have been indexed.


Scrape SERPs

Tracking your own site is not enough; you need to be aware of competitors to succeed in SEO. Fortunately, you can write a script to retrieve Google search results and capture data that interests you: url, title, meta description, ranking position, etc., all according to pre-selected criteria such as language and keyword.


For more resources, keywords in sheets has free scripts for SEO. There are different templates for tasks such as getting old webpages from wayback machine, SEO forecasting and clustering.

Posted: May 22, 2021

Updated: July 12, 2022


If you've recently taken new ownership of a Google Ads account, or simply haven't audited your existing account in a while, this could be a good time for a check up. Regular audits help ensure that your campaigns are still running as efficiently as they were when you launched them. Even if your ROI is positive, you can always find areas for improvement.


This post explains how to audit a Google search ads account, find areas for improvement, and implement changes.


Keyword Bids

Start by checking the overall health of the account, including keyword bids. There are several metrics to use as a guide. First, the Lin-Rodnitzky (L/N) ratio, a metric introduced by a 3Q Digital, is defined as follows: divide the overall account CPA by the CPA of all keywords with at least one conversion. A ratio between 1.5-2.0 is generally good. If it's below 1.5, you might be too conservative, potentially missing out on converting keywords. If the ratio is above 2.0, you might be wasteful, over spending on non-converting keywords.


Next, check the broad match ratio. What proportion of your budget is spent on broad match keywords? Broad match tends to be less efficient than phrase or exact match, so you generally want to keep the ratio around 20%.


Finally, check specific settings in the historical data using filters. For keywords with at least 3~5 conversions, you can pretty much rely on the CPC and CPA data that was accumulated. If there are less than 3 conversions, the data is still volatile - one additional conversion could greatly affect the CPA, so keep an eye on it and make adjustments as needed.


If there any keywords that had high spend but zero conversions over several months, consider cutting your losses and stopping them. I generally use $100 as the cutoff but it depends what is "high" for your account and business.


Once the account has accumulated several months of data, 30+ conversions monthly, and proper conversion tracking setup, consider automated bidding for maximum conversions, if that fits your business goals.


Campaign Structure

It's important to check the high-level campaign structure when you conduct a Google Ads audit. Create a custom report and check that all eligible ad groups have at least one ad, and non-dynamic ad groups have 1~20 keywords. If an ad group has too many keywords, the ads might not be targeted to user search queries, and consider splitting it up into two or more ad groups.


For most campaigns, the best practice will be to use STAGs (single theme ad groups). However, if you have a large budget (over 1k) and high conversions (30+) you can try SKAGs - single keyword ad groups. SKAGs gives you control over showing unique ads for every single keyword, but it can get complicated to maintain the campaigns and make bulk changes when needed.


Even with the new dynamic ads, Google still recommends including 1 responsive and 2 expanded text ads per ad group. Finally, use negative keywords to prevent overlapping and inner competition within your account.


Attribution Model

The default attribution model is last click, and that works fine for many businesses. However, in some cases you could be over or under reporting a campaign's performance by using the wrong attribution model. If you're spending over 10k per month or getting traffic from different sources (display campaigns, retargeting, organic traffic, etc.) one of the other available models might be a better fit. There is a model comparison tool in Google Ads so you can check.


IP Exclusion

Avoid wasting budget on clicks from your own team. You can access IP exclusion settings in the left sidebar. However, with many people working from home now, IP exclusion is more complicated. In some cases it might not be worth the hassle, but you can try using a VPN, Chrome plugin, or extension.

Targeting

Targeting depends heavily on your business goals, but some common oversights across all industries include not targeting specific areas, excluding the search partner network without having data to prove poor performance, and only checking English in the language targeting settings, which limits your reach to users with English in their browser settings. If you have a decent lead nurturing pool, it may also be worth experimenting with remarketing strategies such as Customer Match.


Average CPC, CPA, and CVR

Average performance metrics across all industries, according to WordStream

​

Search

Display

Avg CPC

$2.69

$0.63

Avg CPA

$48.96

$75.51

Avg CVR

3.75%

0.77%

Note that this is just a benchmark, since the metrics depend on many many other factors,such as your brand strength and SEO to name a few. However, it's something to keep in mind and see how you compare.


Conversion Tracking

Assuming you are already tracking your main conversion, you could gain additional insights by tracking secondary conversions. might gain insights from tracking other events as well. For example, if your primary conversion is a contact form submission, you might add page as a secondary conversion. For CPA bidding and conversion data purposes, only the primary conversion should be included in conversions.


Visit the conversions tab to confirm that everything is being properly tracked. The status should ideally be recording conversions. No recent conversions and tag inactive status doesn't mean that your tracking is broken - it could be that you simply haven't gotten any conversions in the past 7 days. New conversion events will first have an unverified status that should change once Google Ads sees an initial conversion, and you can do a manual conversion test to speed up the process.


It can take up to 24 hours for conversion data to appear in Google Ads, even when everything is working perfectly. If after 24 hours conversions still are not showing up, you might start troubleshooting. Note that if you have some conversions reported, then conversion tracking is probably set up correctly (i.e. the tracking either works or it doesn’t, in terms of setup). However, it’s still possible to have missing conversions. Here are a few things

to consider.

App Tracking Transparency (ATT)

Apple released iOS 14.5 in April 2021, which included app tracking transparency (ATT), impacting conversion tracking on iOS across many platforms including Google. Even if Google is still able to track your conversion, you can now expect up to 5 day delay in reporting. Meanwhile, you might be able to check your missing conversion in Google Analytics to see it was from paid and on mobile, which will give you a clue as to whether the problem is related to ATT.

Ad Blockers

Sometimes if the user has a third-party tool such as an ad blocker installed, it can prevent conversion tracking. According to research by Moz, ad blocker usage can be in the 15–25% range depending on region, but many of these installs will be default setups of AdBlock Plus, which does not block tracking. While you may be missing some specific conversions due to ad blockers, it shouldn’t significantly impact campaign performance. And since all advertisers suffer this problem equally, it is a level playing field across the Google network.

Auto-Tagging

If you are collecting conversion data in Google Analytics and sending it to Google Ads, you must have auto-tagging enabled. This setting is disabled by default, so you’ll need to change it in your Google Ads account settings.


Auto-tagging works by adding the gclid (Google Click Identifier) parameter to the URLs people click through from your ads. For example, if someone clicks on your ad for www.example.com, the final URL will look like www.example.com/?gclid=123xyz. Depending on what service your website is hosted on, you may need to add a bit of code either directly or via Google Tag Manager, to successfully pass gclid to your landing pages.


By default, auto-tagging overrides and manual UTM parameters that you may have set up. If you would like to have UTMs take priority, you can do that by enabling "Allow manual tagging to override Auto-Tagging" in Google Analytics, under property settings in the admin section. Google Ads will still pass any values that are not overwritten to Google Analytics, so you won’t lose the tracking details from gclid.

So what can you do about missing conversions?


Import Offline Conversions

One solution is to manually import them back into Google Ads as “offline conversions.” You can simply enter the conversion details in an Excel or Google Sheet and upload it to Google Ads. This help page has step-by-step instructions and templates. Please note that you needed to have auto-tagging enabled at the time of the conversion, because the gclid needs to be included in your upload.


Ad Extensions

There's a lot to consider when optimizing Google Ads - bid strategy, campaign structure, conversion tracking, etc. Ad extensions are often an afterthought, but they actually have a ton of benefits. Not only do they increase your SERP real estate, but they can also get you more qualified leads.


Google has directly stated that ad extensions will increase ad rank, and that extensions are favored because they allow Google to offer a better variety of ad formats and include relevant information for users. All of these factors can increase your CTR, lower your CPC, and cut costs in your ad account overall.


Google Ad extensions do exactly as the name implies - they extend your ad. Ad extensions append additional information about your business to the main body of your ad.


Sitelink Extensions

Sitelink extensions add additional links under your text ad in the SERPs. These links direct users to different pages on your site, other than the landing page.


Examples of common sitelink extensions:

  • Plans & Pricing

  • Success Stories

  • Contact Sales

  • Download Brochure

Callout Extensions

Callout extensions are 25-character text snippets describing the strong qualities of your services. Examples of common callout extensions:

  • Free Shipping & Delivery

  • 3 Convenient Locations

  • Free 30 Day Trial

Structured Snippet Extensions

Structured snippets are lists of specific services, amenities, locations, etc. Select a header from the drop down list of headers, and include at least 3 items for each one.


Lead Form Extensions

Note that your site requires a privacy policy page in order to use this extension type. Lead form extensions are a relatively new feature in Google Ads, as they were released in beta in 2019. They allow the user to fill out a form directly from the ad, without ever clicking into your website. This can be advantageous for BtoC companies looking to increase their lead pools. On the other hand, in my experience the user probably won't change their behavior just because the lead form is available. Depending on your business, it could be better to direct users to your website, where they can learn more about your offering before filling out a form.


Sitelink, callout, and structured snippet extensions are universal extensions, meaning that Google recommends every advertiser to use them. Other extension types such as call, app, lead form, etc. are more situational for each business.


Task Automation

Fortunately, Google offers many automation tools for managing your ads account efficiently. If you have custom needs that aren't covered, you can always use Google Ads Script and API to build your own tool.


Automated Rules

Google Ads has automated rules to save time and catch potential issues as soon as they occur. For example, you can receive an email notification when the CPC exceeds a certain amount, or automatically pause a campaign when the CPA exceeds a certain amount. You can also combine rules - for example, pause a campaign when the CPA exceeds $100 and the total cost is over $300. This way, the rule won't apply to new campaigns that might still be in the learning phase.


Google Ads Editor

For bulk editing, use Google Ads Editor to export your ads to Excel, make changes, and import again to publish to live. This is also useful if you require approval from a client or superior before publishing your ads. For more advanced campaign builds, you can automatically build a campaign from a spreadsheet.


Google Ads Scripts

Even if you aren't a developer, Google makes it easy to just copy and paste the whole source code to your Google Ads scripts. Some useful scripts to get started are the link checker, account anomaly detector, and ad customizer. There might be different source codes for single and manager accounts, so make sure you use the correct one for your account.


Google Data Studio

Google Data Studio is a great tool for automated reporting and sharing with your client. Obviously it's part of the G suite, so you'll get a smooth native connection to Google Ads.


If you're looking at third party tools beyond G suite, Tableau is an advanced tool similar to Data Studio, but with more powerful features for analytics.


Ads Disapproved

One of the most common reasons for disapproved ads is "destination not working," which means that the landing page can't be reached by Google's crawlers, due to some issue such as a broken link or redirect. Unfortunately ,Google does not provide the exact reason why your ad was disapproved, so you'll need to figure out the cause and solution. Here are some common possible reasons for ads disapproved due to "destination not working," and how to fix them.


Server Response Code

There are several ways to check the status code - developer tools in your browser, Google sheets, extensions, or SEO tools. Although Google does allow ad links to be redirected, too many redirects can cause slow page load speed and result in poor user experience. An http 200 response code works best. A common discrepancy is omitting www. from the destination URL, resulting in a 301 redirect to the www. subdomain. Also, note that the display URL does not need to exactly match the destination URL.


CNAME Record

Google ads can be disapproved if your landing page doesn't appear on your registered domain. This issue can arise if you are using different landing page building services. Parked domains are not allowed, and subdomains can get flagged although they are allowed. By creating a CNAME (canonical name) record, you can link your landing page to your root domain.


Accessibility

Even if your ads are only targeting a specific location, Google's policy is that your website should be universally accessible. You should have a robots.txt file granting googlebot explicit permission. A possible solution for this issue is to redirect foreign traffic to a splash page that says "this website is not available in your country."


Dynamic Tracking URLs

If you have enabled auto-tagging in Google Analytics, then Google will pass the gclid along with the URL. However, if your website can't handle URL parameters, then it can cause an error. In some cases, just enabling auto-tagging in Google Analytics was enough to get the ads approved. Although Google disapproves many ads, mistakes do happen - if you believe that your ad was mistakenly disapproved, you can try making a minor edit and resubmitting. If you still can't get the ads approved, go ahead with the appeal. Appeals usually go through within a few business days, and you can check the status in your policy manager. You can also try contacting Google Support.


Credits, Coupons, and Promo Codes

Finally, if you are running Google Ads campaigns, perhaps you were fortunate enough to receive free credits, coupons, or promo codes. The good news is that your account could be eligible for more.


Google

Google offers free ad credits to help new users getting started, and sometimes existing users as well. If you are new to Google Ads, or haven't advertised in a while, Google will give you 100$ in ad credit after you spent 25$.


Since the COVID19 pandemic, Google also gave existing SMB advertisers up to 1,000$ in free ad credits. There was no application process - Google simply inserted the ad credits to qualifying accounts. This campaign had a few requirements, specifically, the account had to be active 10 out of 12 months in the past year to be eligible. Google also has a grant program for registered nonprofits - learn more here.


Website Providers

Website hosting services often offer Google Ads credits for new users of their premium plans - Bluehost, Squarespace, and Wix, just to name a few. Also, check any marketing tools and software that you are already using.

Posted: Jan 30, 2022

Updated: June 25, 2022


This post outlines some common and useful marketing automation solutions on Pardot and Salesforce.


Syncing Pardot and Salesforce

Only one CRM connector can be active simultaneously in Pardot, and for most clients that's Salesforce. The Pardot integration user is an automatically provisioned user that connects to Salesforce to sync data. Pardot is the only app that can log in to Salesforce via the integration user, and only after a Salesforce admin has finished the Pardot setup.


Pardot checks for changes in Salesforce in almost real time - every two minutes to be exact. There are default activities that trigger a sync from Pardot to Salesforce, but you can override them manually or disable syncs if you'd prefer to keep some data only in Pardot. By default, Pardot creates new contacts in Salesforce as Leads, but Pardot Support can change this setting for you and enable the reverse sync feature.


Many of my clients are using the Salesforce-Pardot connector (Salesforce connector), with the marketing team using Pardot to manage their pipeline, and once the leads are warm enough, moving them to Salesforce for the sales team. You can connect Pardot to a Salesforce sandbox or production environment, using email as the unique identifier unless "allow multiple prospects with same email address" is enabled.


Custom objects are useful for syncing Salesforce contacts or leads to Pardot, so you can use that data in segmentation and automations. You can create and sync a custom object from anything that is linked to a contact, lead, or account in Salesforce, to Pardot. Most fields including formula fields, dependent fields, and picklists can be synced bidirectionally in Pardot and Salesforce, but there are some exceptions such as lookups.

Sandboxes create copies of your Salesforce org in separate environments. Use them for development, testing, and training, without compromising the data and applications in your production org.

Lead Qualification

Pardot offers three main ways to qualify your leads: scoring, grading, and Pardot Einstein. The method that I see being used most often is lead scoring, which measures the buying intent of your prospects. The scores will increase or decrease depending on the prospects' interactions on your site. Here is the default scoring system, but this can be customized at Pardot Settings > Automation Settings > Scoring. Depending on your marketing and sales strategy, you might want to give higher scores for certain interactions such as webinar signups or specific page views. Then, you can come back and focus on the prospects with highest scores, which are your warmest leads. For example, you can use an automation rule to create a Salesforce task when a prospect submits a form and has a score above a certain threshold. To reset the score for a select group of prospects, create an automation rule with the desired criteria, and add the rule action to adjust the scores to 0, which is the lowest possible score.


Lead grading is based on prospect profiles. Set your desired letter grade adjustments (1, 2/3, or 1/3) at Home > Marketing > Segmentation > Profiles, then create automation rules to match each criteria. Letter grades start at D by default, and can range from F to A+

Automation rules allow you to perform certain marketing and sales actions based on criteria that you specify. For example, you may set the system to automatically assign all prospects with a country value of Canada, the United States, or Mexico to your North American sales representative. Although similar to segmentation rules, completion actions and prospect table actions, automation rules have unique functionality and are best used in certain scenarios.

Pardot Advanced and Premium users have access to Einstein lead scoring, an AI tool that provides insights on your most engaged prospects and effective campaigns. Einstein creates a predictive model that refreshes lead data and scores every 10 days, so you won't miss new trends as they occur.


Pardot Forms and Form Handlers

Pardot forms are built and managed directly on Pardot. For example, you can build a Pardot form on a Pardot landing page.


Form handlers integrate with third-party platforms such as Wix or WordPress, and funnel the form submission data into Pardot. This is the recommended method if you need to maintain the current lead flow from an existing lead generation strategy. Regardless of whether you use Pardot forms or form handlers, the other features are still available. For example, you can create a completion action on the form to notify sales reps via email or send an autoresponder email using email template.


Completion actions allow you automatically trigger actions (assign to queue, set profile, increment prospect field value, etc.) after another action is completed - for example, when a prospect completes a form, visits a page or downloads a file. Completion actions are available within forms, form handlers, files, custom redirects, emails and page actions.


Completion action notes:

  • Completion actions are not retroactive.

  • Completion actions cannot be applied directly on a landing page.

  • Most completion actions execute every time they are triggered. However, completion actions based on email link clicks only execute once per prospect.

  • "Adjust score” completion action executes once per prospect per unique custom redirect in a 24 hour period.

  • Completion actions only execute for prospects, not visitors.

  • Completion actions don’t execute on image file downloads or on filtered activities.

  • To trigger conditional completion actions based on responses on a Pardot form - create identical form handlers for each response, and change the completion actions accordingly.

PPC Conversion Tracking

Find the Pardot tracking code from campaigns > your campaign name > view tracking code in Pardot, and implement it on your pages. The Pardot tracking code is pretty much like any other tracking codes for analytics or paid ads - just copy and paste it into your web page html before the </body> tag. Back in Pardot, the tracking code is also useful for seeing prospect's click path through your website's content, and triggering page actions to occur when a prospect visits a certain URL.


To track where a prospect originates when they visit your website, create a custom redirect for each ad, and select a Pardot campaign for each custom redirect.


Custom redirects allow you to track when your prospects click on those pages. This can be integrated with Google Analytics parameters as well during setup. The custom redirect statistics table, located in the custom redirects tab and within the content report, is where you can find the results such as how many unique clicks the redirect has generated.


Generally, prospects are assigned to the Pardot campaign associated with their first touchpoint, and that does not change going forward. However, you can set the completion action for the custom redirect to change the campaign.


UTM Parameters in Pardot Form Submissions

There are several different ways to pass UTM parameters from your PPC campaigns and landing pages, and capture them in Pardot form submissions. All of them are basically doing the same thing - add custom code to your webpage that will get the UTM parameters and submit them as hidden fields in the form.


Probably the most common method is adding the scripts directly in the body html of your webpages. You'll need to implement two scripts to get the UTM parameters from your PPC campaigns and pass them through Pardot form submissions. The first script should be added to every page, to grab the query parameter and push it into sessionStorage. The sessionStorage object stores key/value pairs in the browser, such as the following example.

sessionStorage.setItem("lastname", "Smith");
sessionStorage.getItem("lastname");

The second script should be placed only on the or page, to grab the sessionStorage value and insert a hidden field with it into the form.


Note on combining this method with auto-tagging in Google Ads. This method still works even if you are using auto-tagging to sync Google Ads and Analytics. Just make sure that UTM override is not turned on in Analytics. That way, Google Analytics will continue to prioritize auto-tagging by default, while Pardot and Salesforce will have the UTM parameters.


Other Solutions to Pass UTM Parameters to Pardot

However, GTM can be blocked by some ad blockers or other third-party extensions, so using the custom JavaScript variable is not widely recommended.