Automated Identification and Graphing of SQL Dependencies

Over time, your company’s data model becomes increasingly complex. Visualizing your data model can help you understand how to optimize your ETL processes, but requires time and effort to code into a graph. Here’s a way to do it in an automated fashion by using Python, GraphViz, and some clever RegEx

Read More
How ZeroCater Made Our Lunch Data-Driven: Survey Analysis and the IoT

Here at Periscope we absolutely love data, so I couldn’t help but notice when our catering company, ZeroCater, brought in an amazing device to collect survey data about our lunches in real time. ZeroCater has always delivered us a great meal, but for a data driven analyst like myself this this was too interesting to ignore...

Read More
SQL Text Analysis with Donald Trump’s Tweets

There were many unique aspects of the 2016 U.S. presidential election, with one of the most interesting being Donald Trump’s use of Twitter. As luck would have it, tweets happen to be very easy to extract and put into relational database format.

Read More
Net Promoter Score and Survey Analysis in SQL

Introduced by Fred Reichheld in 2003, Net Promoter Score (NPS) is a simple method for measuring the likelihood your customers will recommend your product or service. It requires that you survey respondents and ask one simple question: “How likely are you to recommend [Company/Product/Service] to a friend or colleague?”

Read More
Interactive Analytics: Redshift vs Snowflake vs BigQuery

Every day customers ask us what the best data warehouse technology is for handling the workload of their growing analytics department: they want to run queries at interactive, real-time speeds at a price-point that fits in their budget.

Read More
Tracking KPIs using SQL for Effective Revenue Growth Analysis

Tracking revenue is crucial to understanding the health of your business. Whether your long-term growth is declining, has plateaued, or is heading upwards helps you define your long term strategy and goals.

Read More
Valuing User Subscriptions Over Time with SQL

We recently launched the instant-classic underwater warfare game Scope: Battle for the High Seas, and our investors are excited to see how the game’s subscriptions are growing.

Read More
Safety in San Francisco: A Numbers Game

When I first moved to San Francisco, my mother warned me “not to go looking for trouble”, but this can be difficult when you do not know the city well.

Read More
Creating Ranked Position Time Series with SQL

The Los Angeles Lakers were the top NBA team in 2008 but the last in 2016. We wanted to compare their downfall to the rise of other teams in the league, so we built a comparison of each team’s rank in the standings over the past ten years.

Read More
IMDb vs RottenTomatoes Ratings with SQL Trendlines

In this post, we’ll explore how to calculate trendlines from a scatter plot of data. Sometimes the trend is obvious, but other times not so obvious.

Read More
Feature Analysis with Mean-Time-Between-Usage Calculation

In Periscope Data, you can apply filters to charts in your dashboard. Filters allow you to change certain values in the underlying SQL, such as date range. Users can also create a default set of filters they want to apply to the dashboard, so that whenever they access the dashboard, the charts will first display with your default filters applied.

Read More
Cohorted Engagement with Comparable Time Windows in SQL

Cohort analysis is a powerful tool for evaluating the behavior of groups of users over time. One of the most common ways to cohort users is by when they started using your product or playing your game.

Read More
Common Salesforce Reports in SQL

At Periscope Data, we depend on our sales team to bring in revenue from the leads our marketing team generates. Like many sales teams, ours uses Salesforce to manage the processes involved in making a sale.

Read More
Predicting Churn using Hypothesis Testing

When dealing with massive quantities of customer data, it can be difficult to answer simple questions like: is a customer going to churn or not?

Read More
Calculating Money Growth using PL/pgSQL

PL/pgSQL for-loops are a tool that we like to use at Periscope Data. To demonstrate their use, we’re going to determine the real value of $1000 in our savings account after 15 years.

Read More
Analyzing Disparate Data with Segment Sources

Collecting data across all the ways your customers interact with your product makes valuable analysis possible. The challenge with performing the analysis is finding a way to query data from multiple sources without a messy and complicated process.

Read More
Simple Lead Scoring with Enrichment

How can a sales team prioritize which leads to focus on? How can a marketing team measure the quality of the leads they produce?

Read More
Impact or No? -- Simple Event Study in SQL Part I

Timeseries charts can be quite powerful for depicting changes of a metric over time.

Read More
Analyzing Your Email with SQL

Ever since I first heard the phrase Inbox Zero I have been aggressively pursuing it with careful inbox management and quick email response times. I wanted to see how I was doing, so I downloaded a data dump of my gmail data. ​

Read More
Rolling Classifications by X Previous Aggregations

Rolling averages are useful for comparing a specific point with those around it. However, in some cases we might want to instead classify and group a period’s data based on its contemporary periods.

Read More
Managing a Six-Figure Ad Budget with SQL

At Periscope Data, one of our main marketing channels is advertising. We have to be very careful with advertising, because costs can quickly add up to outstrip the revenue we receive from our ad campaigns.

Read More
Portfolio Risk Analysis in SQL

Any investment involves some amount of risk. Different people have different amounts of risk that they’re willing to take on when investing. We will do risk analysis of an investment portfolio in SQL in this post.

Read More
Bigram Frequencies in Pure SQL

Basic text analysis on unigram and bigram frequencies can be helpful when digging into datasets of unstructured text.

Read More
Calculating Expected Value vs. Actual Results for Super Bowl Contenders

With the Super Bowl quickly approaching, what better time than now to explore expected value and results in SQL!

Read More
Binomial Options Pricing in SQL

Trees are a great way to visualize different states of a process when measuring uncertainty and making predictions.

Read More
Finding Nearest Neighbors in SQL

It has been almost twenty years since Major League Baseball added a new team. Now there’s talk of one being added in the West.

Read More
Funnel Metrics - Step-over-Step Drop Rates

Funnels are an extremely flexible analysis tool that can provide clear and actionable insights. You can apply them to any situation with a notion of linear progression and where per step drop-off is important.

Read More
Creating a Histogram in Redshift

One of the more popular questions our customers ask is how to make histograms in SQL.

Read More
Marketing Attribution in SQL

One of the most difficult marketing challenges today is attribution. Give too much credit to one marketing channel and you might mask its underperformance. Don’t give enough credit to another, and you could hide your most valuable channel.

Read More
Calculating Year-over-Year Metrics

One of the most common questions analysts ask is “Have our metrics improved over time?”.

Read More
Analyzing Your Olark History with SQL

​ A strong Customer Success team should be highly engaged with customers, which is why we use Olark to enable live chat and email support directly within the product.

Read More
Calculating Significance of A/B Tests in Redshift

“A/B test” and “statistically significant” have quickly become part of the standard business vocabulary as running tests has become more popular in growing businesses.

Read More
Cash Flow Modelling in SQL

People love financial modelling in Excel. It’s easy, fast and flexible, and Excel’s functions allow you to quickly tweak your financial model. However, when your data volume starts to get very large, a spreadsheet can quickly break down.

Read More
Game Revenue Metrics — What Matters, and When

You’ve put a ton of work into your game. Your engaging story, carefully balanced bosses, and beautiful art are paying off and bringing in revenue.

Read More
International Analytics In SQL

As word of Periscope has spread, our international customer base has grown. In order to capture more international customers, we now run marketing campaigns targeting companies outside the U.S.

Read More
Building Your Own User Analytics System In SQL

We love analyzing data, but found some of the existing web analytics solutions like Google Analytics too constraining.

Read More
Thinking Critically About Mobile Game Retention

Many of our customers produce mobile games and use Periscope to track their growth metrics. Since a mobile game that doesn’t retain its players will never recoup the costs of getting those players, it’s not surprising that retention is one of their top metrics.

Read More
Understanding Website Response Time with SQL

At Periscope we are constantly tracking performance statistics for our computing infrastructure, database query times, and, of course, web servers.

Read More
Extrapolating Data with Day-of-Week Effects

At Periscope, we need to plan ahead to make sure we have enough server capacity. This means predicting future server loads.

Read More
Daily, Weekly and Monthly Charts On The Same Graph

Depending on your data, looking at the same metric with daily, weekly or monthly aggregations can yield startlingly different results.

Read More
Analyzing Our Long Term Blog Metrics With SQL

At Periscope, our blog is a key channel for bringing in leads, getting our name out in front of data analysts, and positioning ourselves as experts on SQL.

Read More
SQL for Marketers — Your Most Profitable Channels

Knowing how your customer acquisition costs compare to your customer lifetime value is a vital part of marketing. When split across channels, this data allows you to decide whether to double down on a channel or take resources away.

Read More
$3000 Data Warehouse — Redshift vs. Postgres

You have $3,000 a month to spend on a data warehouse. Should you upgrade your read replica or switch to Redshift? If you value fast queries, the answer is Redshift.

Read More
SQL for Marketers — 3 Ways to Slice Your Acquisition Data

Acquisition metrics are a well covered blog topic. It’s easy to figure out the general metrics you should follow. The only issue is that the authors assume you’re using an analytics tool like Google Analytics or Mixpanel.

Read More
SQL for Marketers Part 3 — Our Most Valuable Traffic Source

Another foggy day in San Francisco, another SQL for Marketers post. For round three, we’re going to determine which traffic source has driven the most revenue for us.

Read More
Day-Over-Day Changes Without Window Functions

A couple of months ago, we showed you how to compute day-over-day changes using window functions. For high-growth companies, the growth rate is at least as important as the absolute value of any metric, so this technique is very handy.

Read More
SQL for Marketers Part 2 — Users over the Last 30 Days

Welcome back to SQL for Marketers. Last time we covered basic SQL terms and the SQL commands select, from, where and join.

Read More
Comparing Hourly Revenue in SQL

For many businesses, understanding revenue per hour is as important as revenue per day. It changes throughout the day with the number of people engaging with the product.

Read More
SQL for Marketers

In a break from our usual posts about advanced SQL techniques, we’re bringing it back to the very beginning.

Read More
Selecting Only One Row Per Group

Sometimes you just want to graph the winners. Were there more iOS or Android users today? Grouping and counting the daily usage per platform is easy, but getting only the top platform for each day can be tough.

Read More
Use Subqueries and Window Functions to Compute Running Averages

By now, we’re all familiar with the ubiquitous Daily Active Users metric. It’s a wonderful, concrete way to get a sense of your engagement.

Read More
Using Self Joins To Calculate Your Retention, Churn, And Reactivation Metrics

Most sophisticated user analysis looks at repeat usage: How many users come back? How many don’t? How many come back after an extended absence?

Read More
How To Optimize Lifetime Distinct Counts Using Window Functions

Lifetime metrics are a great way to get a long-term sense of the health of business. They’re particularly useful for seeing how healthy a segment is by comparing to others over the long term.

Read More
Predicting Exponential Growth with SQL

SQL is great at grouping and counting the data you already have, and with a little help from regressions, SQL can help you project that data into the future.

Read More
How To Calculate Cohort Retention in SQL

‍Losing users sucks. Losing customers really sucks. If you’re a startup you know that Retention is King.

Read More
Counting Conditionally in SQL

When preparing a report on the number of customers you have, it can be helpful to split out the premium customers — i.e., those who spend more than $100/month — from the rest of the group.

Read More