Posts

DSC_0104 Zach Doty Cover Photo for Learning LIKE SQL Statement

The LIKE Statement: SQL Statement Fundamentals

Howdy folks! We are overdue for another installation of SQL learning. I’ve slept a few times since the past couple of articles on how to learn SQL. Previously, we talked about the IN Statement, BETWEEN statement, and ORDER BY clause.

In this article, we’ll learn how to execute the LIKE statement in SQL queries. Let’s jump in.

 

About the LIKE Statement & Why it’s Important

Have you ever worked with a data set that’s overwhelmingly large  or complex? Or overwhelmingly large and complex? Sometimes, you need to find data, but can’t recall the exact string or values for a lookup. Or, perhaps, you’re working with a messy data output from say, Google Keyword Planner that groups a range of close variants into one value?

Say you’re looking for values related to designer clothing and designer clothes. Without a better solution, the most probable solution for you is to do a bunch of sorting, filtering, classifying and other data sleuthing at great expense to time and sanity.

The LIKE statement exists to help with the debacle of only having / knowing  part of the lookup criteria you need, courtesy of pattern matching.

PostgreSQL and many other SQL engines/platforms support the LIKE statement, which functions a bit like the below. (Pun not intended.)

 

SELECT keyword, search volume

FROM table

WHERE keyword LIKE ‘cloth%’

 

The above tells pgAdmin / PostgreSQL to get the keyword and search volume columns from table, where the keyword values match values that begin with ‘cloth’ and are followed by anything else, the percent sign. The combination of calling out a text string with an operate is known as a pattern.

When you execute the LIKE statement in a SQL query, pgAdmin will begin reading through the table rows to see if the pattern you’ve specified returns any matches. For the season marketing technology folks, this functionality sure does resemble regex in some ways.

However, there are some differences.

  • Here, instead of the * character being wildcard, the % sign serves as a wildcard matching all characters.
  • If you want to match a single character, the underscore character is used.

 

LIKE Statement Syntax & Examples

Let’s try some examples. Below, we’ll call on the faithful DVD rental practice database, and run a query for customers that have first names like Jen. (Jennifer, Jenny, etc.) Our below code produces the following result.

SELECT first_name,last_name

FROM customer

WHERE first_name LIKE ‘Jen%’;

2017-03-30-001-LIKE-SQL-Statement-Example

 

That being considered, there are other ways we can use the like statement. Above, we used a wildcard to match any endings to a particular string.

Conversely, we could execute a SQL query that specifies a certain ending value, with the wildcard preceding. If we extend that example to such a query below, we should see the following result:

SELECT first_name,last_name

FROM customer

WHERE first_name LIKE ‘%y’;

2017-03-30-002-LIKE-SQL-Statement-Example

 

Above, we’ve flipped the tables so we capture every possible beginning condition under this pattern. Also important to note, the patterns aren’t limited beginnings or ends. You can use this wildcard in the middle, etc. Consider the following:

SELECT first_name,last_name

FROM customer

WHERE first_name LIKE ‘%er%’;

2017-03-30-003-LIKE-SQL-Statement-Example

 

Now, on top of all this awesomeness, realize we’ve been using it as a matching filter. We can also employ the NOT LIKE syntax to exclude values meeting the pattern we’ve specified.

Let’s mix things up a bit. We mentioned a second type of pattern matching character that we haven’t used yet: the underscore.

SELECT first_name,last_name

FROM customer

WHERE first_name LIKE ‘_her%’;

2017-03-30-004-LIKE-SQL-Statement-Example

 

Above, we’ve made a similar ask. However, instead of requesting all possible matches, we’ve mandated SQL only return the ‘er’ string that begins with ‘h’.

To throw you a quick curveball, it’s worth noting the LIKE statement is case sensitive in its matching. Could be bad, could be good, could be neither. However, there’s a way for you to force case insensitivy on the queries. The difference in your statement appears relatively minor. Instead of using a function that calls LIKE or NOT LIKE, you’ll use ILIKE.

 

Wrap-Up

Hope you found this useful! Stay tuned for more SQL learnings and application. If you’re new here, visit the page on how to learn SQL. If you’re interested in more educational material, check out our ongoing series of how to develop Amazon Alexa voice search skills, and getting started with algorithmic trading. Cheers!

DSC_0163 Zach Doty Cover Photo for ORDER BY SQL Clause

ORDER BY Clause: SQL Statement Fundamentals

Welcome back to our SQL learning journey! It’s been a week and a half since my last post on using LIMIT. Work got crazy and I got the flu! 🙁 But we’re back in action. Today, we’ll delving deeper into SQL statements in PostgreSQL: ORDER BY.

Let’s jump in. Why would you need a statement that orders data? Whenever you query data from a table, PostgreSQL will by default return the rows in the order they were inserted into the table. (Read: not the order you want.)

To sort the result set from your SQL query, you can use the ORDER BY clause in the SELECT statement and specify a certain ascending or descending order.

Sample ORDER BY Clause Syntax

Here’s an example of the ORDER BY clause would look like within a SELECT statement.

SELECT column_1, column_2

FROM table_name

ORDER BY column_1 ASC / DESC;

Some important notes for the ORDER BY clause:

  • Specify the column you want to sort by with the ORDER BY clause
    • If you sort the results by multiple columns, use a comma to separate between the two columns
  • Use ASC to sort the results in ascending order
  • Use DESC to sort the results in descending order
  • Should you leave the ORDER BY clause blank, it will use ASC (ascending) by default

ORDER BY Clause Examples in SQL Statements

Alright, first up. We’ll do a basic SELECT statement, adding both ORDER BY and LIMIT clauses. Code and screen shot below. The query should select the first and last name columns from the customer table, ordered by last name ascending (A > Z), returning only the first 10 rows.

SELECT first_name,last_name  FROM customer

ORDER BY last_name ASC

LIMIT 10;

2017-02-16-001-ORDER-BY-SQL-Statement-Example

 

Let’s try another example. What if we want to do an advanced / multiple sort? Let’s try it out, and change it up ever so slightly from the first sample query.

SELECT first_name,last_name

FROM customer

ORDER BY first_name ASC,

last_name DESC;

2017-02-16-002-ORDER-BY-SQL-Statement-Example-Multiple

If we scroll down to the first set of duplicate first names, we’ll see the last name has been presented in descending order (Z > A).

More Details About the ORDER BY Clause

Did you know? In PostgreSQL, you can ORDER BY columns that aren’t explicitly selected within the SELECT statement? E.g. We could only select first and last name, but order by their address_id value. Interesting, yes? Important caveat, other SQL programs, such as MySQL, may not let you do this.

Below:

SELECT first_name FROM customer

ORDER BY last_name ASC;

2017-02-16-003-ORDER-BY-SQL-Statement-Example-Other-Sort

Wrap-Up

Thanks for joining me and hopefully you’ve learned the essentials for the ORDER BY clause. Feel free to check out the entire journey on how to learn SQL, thanks!

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_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.