Generating High Quality Available .com Domain Names for a Specific Industry

In my last post I detailed how to extract all of the available .com domain names from the .com zone file. In this post I’m going to show you how to do something very useful with the result: finding a great available domain name for a business in a specific industry.

For example, we’re going to find great business names that can fill in the blanks for the industry of your choosing:

  • ____________Marketing.com
  • ____________Consulting.com
  • ____________SEO.com
  • ____________Data.com
  • ____________Media.com
  • ____________Systems.com
  • ____________Law.com

The big idea: Check for keywords that are registered for other industries, but not registered for yours

Consider this: what if we looked at all of the registered domains that end with advertising.com, figure out the keyword, and then check whether the corresponding marketing.com domain is available? For example, imagine we check and see that the domain HightowerAdvertising.com is registered (we’ll refer to Hightower as the keyword here). We can then check to see if HightowerMarketing.com is registered. Because someone already registered the keyword for the advertising industry, there’s a good chance that the keyword is meaningful and worth checking for the marketing industry as well.

We can take this a step further by checking for common keywords in multiple industries. For example, we check all the domains that end in advertising.com, all that end in media.com, see which keywords they have in common, then check which of those are not registered for marketing.com domains.

The fewer industies we check for common keywords, the more results we’ll have, but the lower the quality. The more industries we check, the fewer the results, but the higher the quality.

Getting your command line on

If you went through my last post, you should have wound up with a domains.txt file that has about 108M registered .com domain names:

$ wc -l domains.txt 
 108894538 domains.txt

With a little bit of command line magic, we can extract all of the domains that end in ADVERTISING (like HIGHTOWERADVERTISING), then remove the trailing ADVERTISING word to get just HIGHTOWER, then sort those results and save it to a list:

$ LC_ALL=C grep ADVERTISING$ domains.txt | sed 's/.\{11\}$//' | sort -u > tmp/advertising.txt

Which will generate a list such as:


$ head tmp/advertising.txt
A
AA
AAA
AAB
AAC
AADIT
AADS
AAGNEYA
AAHAA

Then we do the same for MARKETING domains:

$ LC_ALL=C grep MARKETING$ domains.txt | sed 's/.\{9\}$//' | sort -u > tmp/marketing.txt

And finally, we figure out which domains are in the advertising list but not in the marketing list:

$ comm -23 tmp/advertising.txt tmp/marketing.txt > results/marketing.txt

If we want to find common keywords registered in multiple industries, we need to add an extra step to generate that list of common keywords before figuring out which ones are available in ours:

$ comm -12 tmp/advertising.txt tmp/media.txt | comm -12 - tmp/design.txt | sort -u > tmp/common.txt
$ comm -23 tmp/common.txt tmp/marketing.txt > results/marketing.txt

The resulting marketing.txt list will have the common keywords in the other industries that are likely not registered in yours:


AANDG
AAS
ABILENE
ABRASIVE
ACCOMPLICE
ACENTO
ACTIONSPORTS
ADAGE
ADAIR
ADAY
ADCOM
ADDO
ADITHYA
ADJACENT
ADJECTIVE
ADLIB
ADOBE
ADONAI
ADONE
ADSPACE

The way to interpret this is that for a keyword like Adspace, those domains are registered in the other industries (AdspaceAdvertising.com, AdspaceMedia.com), but not registered for ours (AdspaceMarketing.com). Again, the more similiar industries you check for common keywords, the higher the quality of results you’ll have. We could add three or four more industries to get a short, very high quality list.

By the way, the reason I say likely not registered is because once a domain loses its name servers – for example, if it’s way past its expiration date – it will drop out of the zone file even though the name isn’t available to register yet. Therefore some of the results might actually be registered, but a quick WHOIS check will confirm if it is or not:

$ whois blueheronmarketing.com

No match for domain "BLUEHERONMARKETING.COM".

Or you could just use this Ruby script

Because it’s a pain to run all of these commands while searching for available domains in an industry, I put together this small Ruby script to help:

https://github.com/mattm/industry-domain-name-generator

There are instructions in the README explaining how to set the industry and similar industries in the script. If all goes well, it will run all of the necessary commands to generate the list of results:

$ ruby generator.rb 
Finding available domains for marketing...
Generating industry name lists...
Searching for domains that end with 'advertising'...
  LC_ALL=C grep ADVERTISING$ domains.txt | sed 's/.\{11\}$//' | sort -u > tmp/advertising.txt
Searching for domains that end with 'media'...
  LC_ALL=C grep MEDIA$ domains.txt | sed 's/.\{5\}$//' | sort -u > tmp/media.txt
Searching for domains that end with 'design'...
  LC_ALL=C grep DESIGN$ domains.txt | sed 's/.\{6\}$//' | sort -u > tmp/design.txt
Searching for domains that end with 'marketing'...
  LC_ALL=C grep MARKETING$ domains.txt | sed 's/.\{9\}$//' | sort -u > tmp/marketing.txt
Finding common names in industries...
  comm -12 tmp/advertising.txt tmp/media.txt | comm -12 - tmp/design.txt | sort -u > tmp/common.txt
Finding names not registered for marketing...
  comm -23 tmp/common.txt tmp/marketing.txt > results/marketing.txt
Done, results available in results/marketing.txt

And with a little luck, you’ll find a great domain in the list to use for your new business.

Extracting a List of All Registered .com Domains from the Verisign Zone File

Back in the day when I worked on Lean Domain Search I got a lot of experience working with Verisign’s .com zone file because that’s what Lean Domain Search uses behind the scenes to check whether a given domain is available to register or not.

I still get a lot of emails asking for details about how it worked so over a series of posts, I’m going to walk through how to work with the zone file and eventually explain exactly how Lean Domain Search works.

What’s a zone file?

A zone file lists all registered domains for a given Top Level Domain (like .com, .net, etc) and the name servers associated with the domain. For example, because this blog is hosted on WordPress.com, the zone file lists the WordPress.com name servers for it:

MATTMAZUR NS NS1.WORDPRESS
MATTMAZUR NS NS2.WORDPRESS
MATTMAZUR NS NS3.WORDPRESS

How do I get access to the zone file?

Anyone can fill out a form, apply, and get access. There are details on this page. I detailed in this old post on Lean Domain Search how I filled out the form, though it has changed since then so you’ll need to make some adjustments.

What happens after I apply for access?

Verisign will provide you details to log into the FTP to download the zone file:

Screen Shot 2018-05-18 at 1.07.14 PM.png

The zone file is that 2.91 GB com.zone.gz which unzipped is 11.47 GB currently.

What’s in the zone file?

It begins with some administrative details, then begins listing domains and their associated name server. Note that registered domains without a name server (such as ones that are close to expiring) are not included in this list.


; The use of the Data contained in Verisign Inc.'s aggregated
; .com, and .net top-level domain zone files (including the checksum
; files) is subject to the restrictions described in the access Agreement
; with Verisign Inc.
$ORIGIN COM.
$TTL 900
@ IN SOA a.gtld-servers.net. nstld.verisign-grs.com. (
1526140941 ;serial
1800 ;refresh every 30 min
900 ;retry every 15 min
604800 ;expire after a week
86400 ;minimum of a day
)
$TTL 172800
NS A.GTLD-SERVERS.NET.
NS G.GTLD-SERVERS.NET.
NS H.GTLD-SERVERS.NET.
NS C.GTLD-SERVERS.NET.
NS I.GTLD-SERVERS.NET.
NS B.GTLD-SERVERS.NET.
NS D.GTLD-SERVERS.NET.
NS L.GTLD-SERVERS.NET.
NS F.GTLD-SERVERS.NET.
NS J.GTLD-SERVERS.NET.
NS K.GTLD-SERVERS.NET.
NS E.GTLD-SERVERS.NET.
NS M.GTLD-SERVERS.NET.
COM. 86400 DNSKEY 257 3 8 AQPDzldNmMvZFX4NcNJ0uEnKDg7tmv/F3MyQR0lpBmVcNcsIszxNFxsBfKNW9JYCYqpik8366LE7VbIcNRzfp2h9OO8HRl+H+E08zauK8k7evWEmu/6od+2boggPoiEfGNyvNPaSI7FOIroDsnw/taggzHRX1Z7SOiOiPWPNIwSUyWOZ79VmcQ1GLkC6NlYvG3HwYmynQv6oFwGv/KELSw7ZSdrbTQ0HXvZbqMUI7BaMskmvgm1G7oKZ1YiF7O9ioVNc0+7ASbqmZN7Z98EGU/Qh2K/BgUe8Hs0XVcdPKrtyYnoQHd2ynKPcMMlTEih2/2HDHjRPJ2aywIpKNnv4oPo/
COM. 86400 DNSKEY 256 3 8 AQOz+iBqxZtCKBBqKsO/i9JVchZ2Z1pFCWnj+pFHJi3uPWiYWsAMvtMpInRPfV1Ot9m+8nHPxSkvOL2+bttj4jEK6uUfTarET4wAMSh2k9rX2h+9kVQDjcuRwfFXV5bAmFd3j57hic7FEYVSxXtNUVU7BPaFRHuFr3OrQHQXaR4IeQ==
COM. 86400 NSEC3PARAM 1 0 0 –
COM. 900 RRSIG SOA 8 1 900 20180519160221 20180512145221 36707 COM. Jh63KZtaFJwB86dM+r65iDaGDNWbLsMi7tP/Kf9dYHdILkGpPfO4HOVkKKvMKQpGcrIyl7LPwwfA2VfvISFsWszcqD7SNfP82rHCf8Y1U6JXRS4v23x+0zeaq4LLAaHsejursS8b5W/PsufbXoWgs6oTuCdNEhzit5ql2s2JtUY=
COM. 86400 RRSIG NSEC3PARAM 8 1 86400 20180516044717 20180509033717 36707 COM. eHouT12OKthPi++n+0hgvafEopsN3Q6iCBNVpyvckt3+29ReGd3XugZrx9qASl0Z+sYd8icxHHG2JIMs/ZqrknQIngP24hkmQrRYBAEkNggUzbjxp1CRqdnyeaJ8c8X8WjiFzLk2y7ic4fpxvHcB2MCAIkIRDWlDYjznNaIbsNI=
COM. RRSIG NS 8 1 172800 20180516044717 20180509033717 36707 COM. nmXBe6F4losM2dmCryGopjjJLlhQmYscNgHqvIQ3zbHm59UHe87T6FmHTdtdujmh3D8rW6g2vx2rzWPxLQigd7xh1KyIfCGZODaUB4TPAxadtGCfvu1h00dieCIf/+UIumg5iJBPjlQdCdpAweh1Zw9KUvbWlkRrXLz03jmJ/xg=
COM. 86400 RRSIG DNSKEY 8 1 86400 20180522182533 20180507182033 30909 COM. F7/1eje9GeHOQcuokqfTHeLYxVznTnkF10YAlMTKi7aJiCySWMVwC/0I/om/EvE+Z4AMG+3B/gFy94PpGnOjpaZcimW1syTKJOPPsGXdQD6F1bxnKCD1r+r9HrSIKTe+lzXI7kzakHNZx3zsdYO4aFifr/hiR/YV/wirJjiXxgOFCtUquSlIOeZ7rv8wTf34onLrf2mYk447ByqUWrXJvqJ16pW+ISUFzyroHqgXFluzrMUqlWVJl8mtnQ5ChCk98zZTGCQJc60HDeYWSY3Mbpji2VZS2uQVDTzO3AeEv5GIoLF8jC+UCAeYDiQhZ5HaEn5HSLh/jYe3TOuIm0tOiw==
KITCHENEROKTOBERFEST NS NS1.UNIREGISTRYMARKET.LINK.
KITCHENEROKTOBERFEST NS NS2.UNIREGISTRYMARKET.LINK.
KITCHENFLOORTILE NS NS1.UNIREGISTRYMARKET.LINK.
KITCHENFLOORTILE NS NS2.UNIREGISTRYMARKET.LINK.
KITCHENTABLESET NS NS1.UNIREGISTRYMARKET.LINK.
KITCHENTABLESET NS NS2.UNIREGISTRYMARKET.LINK.

view raw

com.zone.txt

hosted with ❤ by GitHub

How can I extract a list of just the domains?

Glad you asked! It takes a little bit of command line fu.

If you’d like to follow along, here are the first 1,000 lines of the zone file. You can download this and use the terminal commands below just like you would if you were working with the entire 317,338,073 line zone file.

1) First, we’ll grab a list of just the domains:

$ awk '{print $1}' com.zone > domains-only.txt

For a line like this:

KITCHENEROKTOBERFEST NS NS1.UNIREGISTRYMARKET.LINK.

This command will return just KITCHENEROKTOBERFEST.

This will also return some non-domains from the administrative section at the top of the zone file, but we’ll filter those out later.

Here’s what domains-only.txt should look like.

2) Next, we’ll sort the results and remove duplicates:

$ sort -u domains-only.txt --output domains-unique.txt

This is necessary because most domains will have multiple name servers, but we don’t want the domain to appear multiple times in our final list of domains.

Here’s what domains-unique.txt should look like.

3) Last but not least, we’ll ensure the results include only domains:

$ LC_ALL=C grep '^[A-Z0-9\-]*$' domains-unique.txt > domains.txt

There are a few things to note here.

First, make sure to use gnu grep, which is not the default on Macs. GNU grep is fast.

The LC_ALL=C forces grep to use the locale C, which tells grep this is an ASCII file, not a UTF-8 file. More details here. While not important for this 1,000-line file, it significantly reduces how much time grep takes on the full 300M+ line zone file.

The ^[A-Z0-9\-]*$ regular expression here looks for lines that are made up of letters, numbers, and dashes. The reason we use a * (0 or more characters) vs + (1 or more characters) is simply because the grep command doesn’t support +.

Technically this regex will match strings that are longer than domains can actually be (the max is 63 characters) as well as strings that start or end with a dash (which isn’t valid for a domain) but there aren’t any of those in the zone file so it’s not a big deal and grep will run faster this way. If you really wanted to get fancy, you could match proper domains, but it will take longer to run: ^[A-Z0-9]([A-Z0-9\-]{0,61}[A-Z0-9])?$

Here’s what domains.txt should look like.

Note that this does include some domain-like strings from the administrative section like 1526140941 which isn’t actually a domain. Depending on what you’re using the zone file for you could remove these lines, but it’s never been a big deal for my use case. Because Lean Domain Search is limited to letters-only domains, it actually just uses  ^[A-Z]* for the regex.

Here’s some actual code from Lean Domain Search with these steps above:

Screen Shot 2018-05-18 at 1.43.18 PM.png

If you run into any trouble or have suggestions on how to improve any of these commands, don’t hesitate to reach out. Cheers!

Removing Query Parameters and Fragments from URLs with SQL

If you’re working with Mixpanel data, it’s important to keep in mind that current_url property represents the the full URL including query parameters and fragements. Here are a few examples, screenshots courtesy of the Chrome Mixpanel Debugger Extension:

https://www.helpscout.net/blog/

Screen Shot 2018-05-15 at 9.04.24 AM.png

https://www.helpscout.net/blog/?utm_source=example

Screen Shot 2018-05-15 at 9.04.59 AM.png

https://www.helpscout.net/blog/?utm_source=example#fragment

Screen Shot 2018-05-15 at 9.05.38 AM.png

You get the idea: The current_url property is the exact URL as displayed in the browser, query parameters and all.

This is an issue because if we’re analyzing the data to determine the number of unique visitors to a page, the query paramters and fragments shouldn’t have an impact. These should all be treated as the same page:

Without adjusting the query to take into account that these are all the same page, we’d wind up undercounting the number of visitors to it because we’d only wind up counting the version without query paramters or fragements.

Take a look at just a fraction of the URLs our visitors had when viewing a single blog post (this Mixpanel data is in BigQuery thanks to Fivetran’s Mixpanel connector):

Screen Shot 2018-05-15 at 9.23.46 AM.png

Thanks to our marketing efforts, we actually had more visitors to the post with query parameters than there were to the same post without URL parameters.

Fortunately, removing the URL parameters and fragment is relatively straightforward using Standard SQL’s STRPOS AND SUBSTR:


SELECT
current_url,
CASE
WHEN STRPOS(current_url, "?") > 0 THEN SUBSTR(current_url, 0, STRPOS(current_url, "?") 1)
WHEN STRPOS(current_url, "#") > 0 THEN SUBSTR(current_url, 0, STRPOS(current_url, "#") 1)
ELSE current_url
END AS url
FROM (
SELECT 'https://www.helpscout.net/blog/beacon-preview-ui/' AS current_url
UNION ALL SELECT 'https://www.helpscout.net/blog/beacon-preview-ui/?utm_content=1234&utm_medium=social&utm_source=twitter' AS current_url
UNION ALL SELECT 'https://www.helpscout.net/blog/beacon-preview-ui/#example' AS current_url
);
+——————————————————————————————————-+—————————————————+
| current_url | url |
+——————————————————————————————————-+—————————————————+
| https://www.helpscout.net/blog/beaconpreviewui/ | https://www.helpscout.net/blog/beaconpreviewui/ |
| https://www.helpscout.net/blog/beaconpreviewui/?utm_content=1234&utm_medium=social&utm_source=twitter | https://www.helpscout.net/blog/beaconpreviewui/ |
| https://www.helpscout.net/blog/beaconpreviewui/#example | https://www.helpscout.net/blog/beacon-preview-ui/ |
+——————————————————————————————————-+—————————————————+

With that adjustment made, you can easily count the number of page views or unique visitors to a given page, regardless of URL parameters or fragements.

Tracking Daily Unique Visitors to Recently Published Blog Posts with Looker, Fivetran, Mixpanel, and BigQuery

If you work at a company that publishes a lot of content, it’s important to understand how well that content is performing. Not just in terms of page views and unique visitors, but whether it converts visitors into trials, etc.

At Help Scout we have a Looker dashboard to help us track all of these things and more. In this post, I’ll walk you through how we track the daily unique visitors to our recently published blog posts. For example, on May 2nd we published April’s Release Notes – how many people viewed that post on the day it was published? How about the day after? And how does that compare to our other recently published content?

Overview

Big picture, we fire a Viewed Page Mixpanel event on every marketing page. We then use Fivetran to get that event data into BigQuery, where we analyze it in Looker. You can read more about the setup here: Tracking What Pages Your Visitors View Prior to Signing Up Using Mixpanel, Fivetran, BigQuery, and Looker.

Querying for Recently Published Posts

With this data in hand, we need to figure out a way to determine what the recent blog posts were so that we can limit our analysis to them.

Here’s the query we use:


SELECT
current_url,
MIN(TIMESTAMP_TRUNC(time, DAY)) AS published_at
FROM mp.event
WHERE
current_url like "https://www.helpscout.net/blog/%"
GROUP BY 1
HAVING
COUNT(*) > 200 AND
MIN(time) >= "2018-04-04"
ORDER BY 2 DESC

For Help Scout blog URLs (ie, URLs that begin with https://www.helpscout.net/blog/), we need to determine when the post was published. That’s the same as the first day it was viewed. However, because we launched Mixpanel page view tracking on April 3rd, this would make it look like every one of our posts was published on April 3rd or sometime after that. That’s why we limit the results to April 4th or later. Also, we want to limit it to posts that received at least a certain number of visitors that first day, otherwise the results will wind up including low traffic posts that were first viewed after April 4th.

This query gets us a list of those recently published posts:


+———————————————————————-+——————————–+
| current_url | published_at |
+———————————————————————-+——————————–+
| https://www.helpscout.net/blog/customer-intimacy/ | 2018-05-03 00:00:00.000000 UTC |
| https://www.helpscout.net/blog/april-2018-release-notes/ | 2018-05-02 00:00:00.000000 UTC |
| https://www.helpscout.net/blog/data-security-breach/ | 2018-04-26 00:00:00.000000 UTC |
| https://www.helpscout.net/blog/beacon-preview-ui/ | 2018-04-19 00:00:00.000000 UTC |
| https://www.helpscout.net/blog/customer-service-employee-engagement/ | 2018-04-05 00:00:00.000000 UTC |
| https://www.helpscout.net/blog/march-2018-release-notes/ | 2018-04-04 00:00:00.000000 UTC |
+———————————————————————-+——————————–+

Modeling the Data in Looker

Over in Looker, we’re going to create a derived table with these results so that we can determine whether a given Mixpanel event URL is a recently published blog post:


view: mp_new_blog_content {
derived_table: {
sql:
SELECT
current_url,
MIN(TIMESTAMP_TRUNC(time, DAY)) AS published_at
FROM mp.event
WHERE
current_url like "https://www.helpscout.net/blog/%"
GROUP BY 1
HAVING
COUNT(*) > 200 AND
MIN(time) >= "2018-04-04"
ORDER BY 2 DESC ;;
sql_trigger_value: FORMAT_DATETIME("%Y-%m-%d-%H", CURRENT_DATETIME()) ;;
}
dimension: current_url {
type: string
sql: ${TABLE}.current_url ;;
hidden: yes
}
dimension: is_new_blog_content {
label: "Is New Blog Content?"
type: yesno
sql: ${blog_post_publish_date} IS NOT NULL ;;
}
dimension_group: blog_post_publish {
type: time
timeframes: [
date,
week,
month,
quarter,
year
]
sql: ${TABLE}.published_at ;;
}
}

The reason we have the is_new_blog_content dimension here is because we’re going to LEFT JOIN all Mixpanel events on this derived table by the URL. Not all URLs will have a match in this table, so this dimension will let us limit the analysis to just events that were a recently published blog post.

Here’s how we model the relationship between our main Mixpanel events model and this derived table:


explore: mp_events {
view_label: "Mixpanel"
label: "Mixpanel"
join: mp_new_blog_content {
view_label: "Mixpanel"
type: left_outer
relationship: many_to_one
sql_on: ${mp_new_blog_content.current_url} = ${mp_events.current_url} ;;
}
}

One other key piece of this is that we model how to calculate unique visitors in the main Mixpanel events view:


view: mp_events {
sql_table_name: mp.event ;;
#
measure: unique_visitors {
type: count_distinct
sql: ${distinct_id} ;;
}
}

Creating the Chart in Looker

With these foundations in place, we can then create the chart we set out to.

We want to use that Is New Blog Content dimension to limit the results to recently published posts, then pivot the daily unique visitor count on the URL:

looker-recent-posts-explore.png

Then it’s just a matter of setting up the chart in Looker and voila, there we have it:

Screen Shot 2018-05-11 at 1.51.34 PM.png

Going forward, without anyone having to log into Google Analyics, we’ll be able to track the popularity of our new blog posts and track the trends over time.

By the way, that spike on April 19th is from our CEO’s Beacon 2.0 Preview: The User Interface post, all about Help Scout’s soon-to-be-released live chat & improved self service tool. If you’re interested in getting notified when it launches, you can sign up here.

Happy querying!