Posts

Getting Started with Alexa Development 02: Signing Up to Alexa Development Portal

Welcome back to our journey in learning how to program Amazon Alexa Skills via Echo voice search. In the previous article, we walked through the process of setting up an Amazon Web Services (AWS) account. Today, we’ll set up an account at the Alexa Development portal, a distinct entity from the AWS portal.

 

Without further ado, let’s jump in. Go to https://developer.amazon.com/public/solutions/alexa.

2017-02-03-001-Alexa-Developer-Home-Screen

You should land on something like the above screen. Click on the “Sign In” button, you can create a new account from this screen if you need.

Important Note: If you already have an Amazon.com account (regular old Amazon shopping account), use those credentials to log in.

Obviously, if you’re a returning Alexa Development Portal user, you can skip the account creation process shown below. If you’re creating a new account, you’ll need to fill out a screen that will likely resemble the below, and click “Save and Continue” when you’ve finished.

2017-02-03-002-Alexa-Developer-Account-Creation

Next, you should be presented with an App Distribution and Services Agreement screen. Be sure to give it a quick read. If you want to use the services, then you’ll need to agree by clicking save and continue. 🙂

The final registration step addresses payments and whether you plan to monetize the apps you develop. For the purposes of my usage, and this learning, I checked “No” to both options before proceeding.

2017-02-03-003-Alexa-Developer-Account-Creation-Monetization

Once you finish that step, you should find yourself in the Amazon Developer Console! Good thing we got the hard material out of the way first, huh?

This should wrap up a pretty quick introductory section for setups. Feel free to visit my previous article on getting set up with AWS, or go to my learning home page on how to start developing Alexa Skills. Thanks and see you at the next article!

bluebonnets Zach Doty cover photo for SQL COUNT Function

SQL Statement Fundamentals: The COUNT Function

 

Hello, hello, hello SQL fans! (Or gracious friends and family perusing the site. 🙂 ) The journey into learning SQL continues, and today we’ll cover the COUNT function. Jumping right into it, here’s our working definition of the SQL COUNT function:

The COUNT function should return the number of input rows that match a specific condition of a query.

Rather, this would appear to work similarly in concept to the COUNTIF(s) formula(s) in Excel.

COUNT Statement Syntax Examples

Here’s what a simple COUNT SQL statement might look like:

1. Basic COUNT (*) FROM SQL Statement

SELECT COUNT (*) FROM table;

Breaking it down a bit, the COUNT () function returns the number of rows returned by a SELECT clause. When you apply the COUNT () statement to the entire table, pgAdmin/PostgreSQL will scan the entire table in a sequential manner.

Additionally, you can specify a certain column count in your COUNT statement for better readability:

2. COUNT (column) FROM SQL Statement

SELECT COUNT(column) FROM table;

Similar to the COUNT(*) function, the COUNT(column) function returns the number of rows returned by a SELECT clause. However, if you have empty or NULL values, the COUNT function will not take those into account.

3. COUNT (DISTINCT column) FROM SQL Statement

If we do a bit of application from our past learnings, we can make a COUNT with DISTINCT SQL statement:

SELECT COUNT(DISTINCT column) FROM table;

Applying the COUNT SQL Function

Alright, so let’s work our way toward applying what we’ve learned. To start, let’s do the traditional probe of the table before diving in, to familiarize ourselves. Below, we’ve done a basic,

SELECT * FROM address;

2017-02-01-001-SELECT-ALL-Starting-SELECT-COUNT

As we get familiar with the table, we can scroll down and see this particular table has 605 rows in it. This will be a reference point as we continue.

Moving forward, we’ll execute a basic SELECT COUNT (*) FROM address; SQL query. Below, you’ll see a slightly different result was returned. Inst3ead of 605 rows, 603 was returned. At this point, kindly reference our note about empty / NULL  values being excluded from the COUNT function.

2017-02-01-002-SELECT-ALL-COUNT-1

We’ve established a general proof of concept for the SELECT COUNT statement, considering the reduced load on the server and yourself, for a quick count. Let’s now try calling specific columns. In our first exploration of the address table, we saw a number of columns, including the district. Let’s say we want to get a count for how many districts/states our customer base covers.

2017-02-01-003-SELECT-COUNT-DISTINCT-1

Above, a count of 378 distinct district values has been returned. Wow, what coverage!

A quick aside for future usage, you can also nest the column reference in its own set of parentheses, as shown and returned below.

2017-02-01-004-SELECT-COUNT-DISTINCT-Nested

Wrap-Up

There you have it! We’ve learned a bit about the COUNT function, what it does and how to use it. It will likely come in handy for future articles, particularly when we delve into group-by excercises. If you missed it, here’s the previous article on learning how to use SELECT WHERE and another recent article on learning how to use SELECT DISTINCT. Also, to start from the beginning, here’s my running list of articles on how to learn SQL. Cheers!

Using the SQL SELECT clause with the DISTINCT Keyword

Introducing the SELECT DISTINCT SQL Statement

Alright, welcome back to our journey with SQL! If you’re just tuning in, we:

In this article, we delve slightly deeper into SQL queries, with the consideration that SQL databases and tables can have a lot of duplicate data, and you might not always want that duplication! This is where today’s subject comes in: using the SELECT clause with the DISTINCT keyword.

Sometimes when you’re managing a database or table, you only want unique (distinct) values when executing SQL queries. Thus, you can get around a large number of duplicate values using the DISTINCT keyword.

The Basic SELECT DISTINCT Syntax

Here’s the general format of what a SELECT DISTINCT query might look like:

SELECT DISTINCT column_1,column_2  FROM table_name;

Next, we’ll take a look a very simplistic example of why you might want to only pull unique values from a database.

Why Use the DISTINCT Keyword?

So we’ve generally been working from a popular public SQL “sample” or “sandbox” database that deals with DVD rentals. In one of the tables, “film”, there are a number of columns containing a wide range of information. For example, we can see below querying the release_year column of the film table, a few films were released in 2006. If we’re looking for a unique list, this is not a good start!

2017-01-30-001-SELECT-DISTINCT-Displaying-Duplicate-Table-Values

Using the SELECT DISTINCT Query in pgAdmin

Working off our example above, we want to see if 2006 is the only release year. To accomplish this, let’s try the below query:

SELECT DISTINCT release_year FROM film;

Below, the query in action through pgAdmin, after hitting F5 to execute and refresh. We can see 2006 is the only unique release year in this table. Zoinks!

2017-01-30-002-SELECT-DISTINCT-Displaying-Unique-Table-Values

Let’s try another example. Perhaps we’re interested in gathering pricing information for some revenue forecasting and analysis. Below is a query we would use to get the distinct rental prices from the film table:

SELECT DISTINCT rental_rate FROM film;

2017-01-30-003-SELECT-DISTINCT-Displaying-Unique-Rental-Rate-Table-Values

We see that there are only 3 price points in this table. What fun in simplicity!

Wrap-Up

Thanks for joining! Next post, we’ll be looking at the SELECT WHERE statement. Check the SQL learning & education page for a running list of articles. Cheers!

 

Restoring a SQL Database with Table Schema Only in pgAdmin x PostgreSQL

Howdy! Welcome back to our shared journey of learning SQL. Last time, we learned about creating, deleting and completely restoring SQL databases in pgAdmin. Today, we’ll learn about how to restore a database, but only its table schema.

Specifically, we’ll restore the table names and preferences for types of data within those tables. However, the actual data itself won’t be ported in.

Think of it as taking your house or apartment, and recreating, but you don’t move in with the furniture. (Maybe a sibling instead? 🙂 ) According to the material I’m working through, this method of database restoration is very common and is something we should have down pat.

 

Database Table Schema-Only Restoration, Method 1

An easy method to start this is to right click on the Databases header near the top of the file tree, and to select “Create” > “Database”. With a fresh new database, we’ll have flexibility to do some more management on the “front” side of things. (Knowing I may be abusing terminology a bit, but makes sense to me while writing this.)

 

2017-01-21-001-pgAdmin4-PostgreSQL-Restore-Database-Table-Schema-M1-P1

For this example, I’ve finished the new database creation by naming (“OnlySchema”) and saving a new database. Below, you can see the new database in the file tree. Also, if we click through the tree as such, “Schemas” > “public” > “Tables”, we’ll notice there are no tables! (Compare that path exploration to the dvdrental database tables, where you can find 15 tables.)

 

2017-01-21-002-pgAdmin4-PostgreSQL-Restore-Database-Table-Schema-M1-P2       2017-01-21-003-pgAdmin4-PostgreSQL-Restore-Database-Table-Schema-M1-p3

Anyway, let’s get on with restoring table schema only!

  1. Right click on “OnlySchema” and select “Restore”
  2. Select the “Custom or tar” option for the Format field
  3. Select the file via the dialogue, or paste in your file path
    • Up to this point, you should notice we’ve taken the exact same steps for a full database restore. However, #4 is where things are slightly different. Pay attention!
  4. As shown below, click “Restore Options”, and activate the radio button for “Only schema” to yes
  5. Click “Restore” and refresh!

 

2017-01-21-004-pgAdmin4-PostgreSQL-Restore-Database-Table-Schema-M1-p4

After the refresh, we should see something like below, where if we select the tables option and view “Properties”, we’ll see 15 empty named tables.

 

2017-01-21-005-pgAdmin4-PostgreSQL-Restore-Database-Table-Schema-M1-p5

 

Method 2: Schema-Only Restoration onto a Database with existing tables, data

What if we’re working with a database that contains existing tables and data? And suppose we need to restore only the schema? Perhaps an error was committed in formatting / management and must be corrected.

Fortunately, this method of restoration is extremely similar to Method 1. For this scenario, there’s one added step toward the end:

  1. Right click on “OnlySchema” and select “Restore”
  2. Select the “Custom or tar” option for the Format field
  3. Select the file via the dialogue, or paste in your file path
  4. Click “Restore Options”, and activate the radio button for “Only schema” to yes
    • Here’s our jump off point, the next step is what differs from Method 1.
  5. Scroll down the dialogue box slightly. You should see a field and radio button for “Clean before restore”. Activate the radio button to “Yes”
  6. Click “Restore” and refresh!

 

2017-01-21-006-pgAdmin4-PostgreSQL-Restore-Database-Table-Schema-M1-p6

After running the restore job and refreshing, we can verify the Schema Only and data clean prior to restoration occurred correctly. Below, we can click through the file tree down from “dvdrental” > “Schemas” > “public” > “Tables” and check the table properties to see that there are zero (0) rows of data.

 

2017-01-21-007-pgAdmin4-PostgreSQL-Restore-Database-Table-Schema-M1-p7

 

Wrap Up

I do hope these articles are serving a useful introduction to navigating pgAdmin, and getting familar with the foundations of PostgreSQL and databases. In the forthcoming articles, we’ll start learning about basic SQL syntax. To keep track of all the shared SQL posts and learnings, visit the SQL Education page for a list of articles to date. Cheers!

DSC_0034 Zach Doty Cover Photo How to Learn SEO in Five Minutes

How to Explain SEO in 5 Minutes

Today, you’re going to learn how to explain SEO to anyone in five minutes! Is it a comprehensive, end-all, be-all example? No. Will it help someone quickly understand the basics of a complex practice? Yes. Let’s jump in. This is, after all, a five minute guide!

Starting Point: SEO is not a black box.

For context, I often hear that SEO is a “black box” and it’s anyone’s guess as to how you rank in search engines. I’m always happy to hear that! It means we have a chance to explain what SEO is really about. Google isn’t a black box. Rather, its algorithms and search engines are so complex at a large scale, it’s difficult to comprehend on a daily basis.
With introductions out of the way, here’s how you do it:
A Job Application
Consider SEO like a job application. You have a resume, references, you deal with a recruiter and a hiring manager. And you want a great career!
In SEO, you have a website, backlinks, the search engine and the user. You want to be found and loved!

Your Resume
Your resume is your website. You control the content and tailor the experience to each opportunity. You balance who you are and what you do.
Your website balances your brand story and products or services. You target keywords to pages of your site, just like your jobs are targeted to certain functions and skill sets.
Which resume would you choose to interview for a Marketing Director?

A. “I’m Leroy, I’m awesome. I’m really cool, I enjoy long walks on the beach and like Ethiopian food.”

B. “Kevin is a seasoned marketing professional who has managed and implemented effective marketing promgrams.”

By the same token, which website would you do business with? One with “high quality products and synergistic solutions”? Or a site that speaks in plain, compelling English?
Your References
Your references are your backlinks, or 3rd party websites linking to your own. You can influence these endorsements and relationships, but not necessarily control them. On both sides of this comparison, quality trumps quantity.

Which of the following references holds the most weight for a candidate?

A. “Little Michael is my favorite nephew, he eats a lot at Thanksgiving, you should hire him.”

B. “Jake paid me some money to recommend him. I guess he’s cool.”

C. “Ryan took our marketing department to the next level with smart strategy. He works well with the team implementing our campaigns.”

Ryan is the best choice. A bunch of low quality or fake recommendations will do a job seeker little good if not harm. By the same token, it’s better to have a few excellent backlinks than a bunch of spammy or fake endorsements.
The ultimate goal
When you’re a job seeker, interviews are great. But you really want the career! Likewise, it’s great to rank well in the search engines, but you want new, loyal customers that convert on your website.

Above all else, optimize your website for your users. The search engine results will follow.

Where to next?

That’s how to explain and understand SEO in five minutes! Obviously, there’s much, much more to learn. However, this will get you on the right path. Dive deeper into SEO with a fantastic Beginner’s Guide from Moz.
DSC_0265 Zach Doty Cover Photo How to Win SEO Long Term

Winning Long Term SEO for Years

A few years ago, the average CMO tenure was 23 months. Yikes. That’s barely long enough to get acquainted and jamming with your staff. Happily, a recent indicates a CMO’s average tenure now sits at 45 months. It’s no secret that personnel and vendors come and go with the executive. For agencies and in-house digital marketers, burgeoning attention and spend in digital means unprecedented opportunity. Naturally, there is tremendous potential for fruitful, long term, agency-marketer-executive relationships. Yet success seems anything but simple, we can’t just ride off into the sunset. (Darn, I like Westerns.) Long term digital marketing engagements still seem precarious at best, especially for Search Engine Optimization (SEO). The “quick wins” and short-sighted quarterly business environment is still firmly entrenched. With a bit of work and love, long-term client-marketer-executive relationships can be profitable foundations that bolster your agency or brand for the next 10 years.

Succinctly, winning SEO for years is a bit like a strong friendship. It’s not perfect, but an active exchange of trust and vulnerability makes something great. It’s not always “up and to the right”. Every friendship is different, but there are guiding principles and helpful tidbits all can apply.

1. Be a Good Wingman: No Paid Links, period. Just don’t do it. More profound material that follows, but an astounding number of folks still buy crappy links. The alleged 3 month benefit isn’t worth your 5 or 10+ year relationship. Consider the latency of SEO: bad backlinks bought today probably won’t help much this quarter. However, they could be indexed in 1-2+ years and derail your campaign at a crucial moment. Because you’re a star marketer, you may find yourself explaining this to other. Less enlightened (don’t tell them that!) colleagues, staff and supervisors need to know that optimizing for the user, not the search engine, brings the win home. Provide an enchanting user experience, and results will follow. Bonus: If you want to scope out your link situation, check out Link Detox or use a great SEO software platform to audit your link profile quality.

2. Be a master of reporting and storytelling. A common agency story runs like this, stop me if you’ve heard this one before: 3 months into an engagement, the client loves the work, everyone is excited! 6 months: communication tapers off and so does the work. At 7-8 months, an email arrives, “Can we get an audit of our billings and work?” Next month, “We’re not seeing the value…can we talk?”

Learning to communicate and report intelligently will help you avoid the account/campaign death spin and 9at least) double the life of your engagement. How you say and show success is the lifeblood of your agency’s (or brand’s marketing) future. In agencies, you’ll often hear, “We don’t have time to educate the client.” True, but you surely can’t afford to leave them in the dark. Build small bits of education into your reporting at each step. Your client (or executive) should be educated enough that they could dispose of you, but so satisfied that they’ll never want to.

Reporting over a long time horizon is tricky. Before you have a full year of data, seasonality can make your work look bad if you’re uninformed on market trends. (What if your 6 month evaluation is in a demand trough?) Furthermore, it’s not realistic to primarily report on the same metric month after month. (E.g, keyword rankings) The client or executive may deem organic traffic the determining KPI at your campaign start. However, fascination is fleeting. Revenue or return on investment will likely be your KPI in 12 months’ time.

Use a consistent mix of metrics to paint a rich picture of your phenomenal digital marketing campaign. When you forecast, it’s common to use upper and lower bounds (like economists and weathermen!) to ensure you don’t look like too big of an idiot in any case. Similarly, use “upside metrics” such as revenue generated from organic and ROAS/ROI and “downside metrics” like cost per acquisition and results decay analysis if your client or executive stopped working with you. Assuming sound communication, your reporting should become easier over time. When showing CMO’s and presidents year-over-year, 3, 5 year trends of their performance, we don’t have to do much selling.

3. Learn to live through redesigns. The shelf life of a website is (and should be, to a degree) these days. Blanket statements are a great way to get in trouble. However, many sites (looking at you, Fortune 500 companies) are on the cusp of major changes for mobile friendliness. If you’re around for more than a couple of years, you will see the client’s site go through a redesign. Ideally, you will start the redesign process small pieces at a time. When you present conversion improvement data, mash up clickstream analytics with heatmapping data for compelling improvements.

However, if you’re not regularly testing, you should start. If you approach a redesign without aforementioned experience, here are a few things to keep in mind.

  • You must exercise discretion and attention to detail. Choose carefully which battles to fight. Web design & development projects are notorious for being past schedule, over budget and largely dissatisfying on the whole. Don’t be the unnecessary logo critic and save your clout for things that matter to SEO.
  • Here’s what you do need in a redesign: a seat at the table, the power to veto content, technical matters and the ability to test after completion. Conventional wisdom in design is at best, a starting point. Best practices will not transfer from one client to the next.
  • Here’s what you don’t need: Approval on typography, creative direction of promo tiles and so on. Did we mention you should pick your battles?

A redesign done well can bolster traffic, interest and conversions. A poor redesign can break a site, its rankings and the business behind it. Mind your technical details such as URL aliases, file extensions, redirects, robots files, sitemaps and more. Don’t know what I just rattled off? Send me a message, I’m happy to explain.

4. Visible Progress. Related to reporting, what tangible value can you demonstrate on a regular basis? Clients and executives often refer to digital marketing as “black box” and “murky”. Their perception is understandable. However, many facets of digital marketing are so precise and complex at scale that simple explanations are very difficult in quick meetings. What pieces of content or site changes can you point to? Given that digital is complex, your client or executive must have concrete, simple wins they can shop as their own.

5. Deep involvement in the client’s business. Don’t get left out in the cold. Many agencies suffer volatility because they enter late in the marketing game with a client. Brand managers suffer irrelevance by thinking small, too late. When a client or executive says sales or down, don’t nod and make a cursory note in your moleskin. Unleash a barrage of intelligent questions about their customer intelligence, acquisition, retention and sales process. Your ability to engage on a deep business level (down to P&L and EPS) will be a large determining factor in your long term success.

This is not an all-inclusive guide to winning SEO long term. However, it’s my hope that this will a jump off point for your team as it conquers its next digital marketing success.