![]() ![]() For example, if I want to examine the overlaps between multiple sites, then I’ll need at least two of these files. The short answer is I’m going to get lots of these files and put them all in the same table. The underlying motivations or goals of users searching for a particular keyword or set of keywords, such as informational, transactional, or navigational.Īt this point, you might be wondering, “ if you’re just working with a 10k line CSV, why use SQL instead of a spreadsheet?” Special elements or features that appear in search engine result pages for a particular keyword, such as featured snippets or video results. Patterns or changes over time in search volume, keyword difficulty, or other metrics related to the keywordĪ record of the date and time when the ranking was recorded The total number of search engine results that contain a particular keyword The level of competition among advertisers or publishers for a particular keyword The estimated cost of driving the same amount of traffic to a page through Google Ads The percentage of overall site traffic to the URL that comes from a given Keyword to a given page. Uniform Resource Locator, the address of the pageĮstimated volume of traffic to the URL for the corresponding Keyword The estimated number of times a particular keyword is searched for on GoogleĪ score or measure of how difficult it is to rank a page in Google search results page for the Keyword The ranking of a page on a search engine results page (SERP) for a given Keyword in the previous month. The ranking of a page on a search engine results page (SERP) for a given Keyword. In this case, I’m using data from SEMRush, so the table will have the following columns: Word In this case, each row will represent a URL’s ranking for a specific keyword and contains the corresponding data about that ranking.Ī column, sometimes referred to as a field, represents a specific attribute or piece of information about the data being stored in the table. Database tables, just like the one shown in the image above, are made up of columns and rows.Ī row, sometimes referred to as a record, represents a single instance of the data that is being stored in the table. If you’ve ever used SQL, you know that all the data you want to analyze must be stored in a table (or tables). Once you’ve got Postgres up and running, you’ll need to create a table in your database that will hold your keyword data. Head over there and get Postgres running on your local machine (aka computer.) This is postico. It’s on my previous tutorial about setting up Postgres on Mac. Surprise! The first step of this tutorial actually isn’t on this tutorial. That does, of course, limit the size of the data that I can work with, but the fact is, it’s over 100x the amount of data I could work with in a spreadsheet. I like to use Postgres for this stuff because it’s easy to set up and can run on my laptop, and because I can run it on my laptop, it’s completely free. Let’s get the setup out of the so we can get to the analytics! Step 1. It’s not going to show the most interesting stuff-I’ll save that for another post. It should help just about anyone start running SQL queries to provide meaningful answers about their keyword data. This post is a beginner’s intro to SEO analytics in SQL. ![]() Now, with the timing of Google’s announcement that Google Search Console data be ingested into Google BigQuery, I figured it would be a great time to share some tips and tricks that I’ve picked up across Postmates, Panoply, Census, and a number of different projects. I find it immensely satisfying to work with SQL for a couple of reasons: 1) it gives me a sense of validation for all the time I spent learning SQL, and 2) it’s just a better, more scalable, less error-prone way to do analytics. When I come across these types of projects, I use the same workflow: bring all the data into a database and run the analyses in SQL. I’ve had a few SEO consulting projects lately that have given me a chance to work with a lot more keywords than anybody should be working with in a spreadsheet. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |