161. Introduction to mobile apps

In February, 2018 Bindle successfully launched their iOS app. 📱

It was an important strategic move to step into a mobile market. It’s so clear nowadays that people read not just in front of their laptop but also during commute, while waiting in a line etc.

So far when we talked about product analytics we’ve talked mostly about Bindle’s website. The world of mobile apps has a set of drastic differences from the web:

  • one need to go to the AppStore. It’s a whole different website and we can’t simply use our own web analytics there.
  • one need to download the app to use it. It doesn’t guarantee a signup though ⚠
  • apps don’t really have a concept of URL. That’s a fundamental difference between web pages and app screens.

These are huge differences ☝. The mobile signup funnel is at least a couple of steps longer than on the web. That’s totally OK though, creativity comes from limitations and now, as we’ve learned the rules of the mobile world, let’s start our first analysis.

Users with mobile app

Remember, in the very beginning of the course, when Bindle launched their website, the first question we asked ourselves was “How many signups do we have?”. Same thing comes to mind when we launched the mobile app. How many users are actually using our app?

To answer this question we need to look at a brand new table in Bindle’s warehouse – devices.

SELECT *
FROM devices

To understand the data model behind this table let’s make a thought experiment. Imagine someone signs up via the Bindle website. When user clicks a “Signup” button the backend of Bindle website will always know that this user is using a browser to signup.

During the signup process a new record inside users table will be created. Additionally, we’ll create a new record inside devices table with device_type equals to browser and connected_at (datetime field) equals to the timestamp of a signup. In case of a browser signup the version column will contain a value of browser’s User Agent.

If a web user installs the iPhone app – a new record will be added to devices table with device_type iphone and connected_at field equals to the timestamp of a login via the app (this is where we know the user_id). The version column for iPhone device contains an iPhone version and iOS version separated by comma.

⚠ For each user there’s at least one record in devices table, added when user signs up. Later if user “connects another device” (installs the app, for example) new record with later connected_at column will be added.

Anatoli Makarevich, author of SQL Habit About SQL Habit

Hi, it’s Anatoli, the author of SQL Habit. 👋

SQL Habit is a course (or, as some of the students say, “business simulator”). It’s based on a story of a fictional startup called Bindle. You’ll play a role of their Data Analyst 📊 and solve real-life challenges from Business, Marketing, and Product Management.

SQL Habit course is made of bite-sized lessons (you’re looking at one atm) and exercises. They always have a real-life setting and detailed explanations. You can immediately apply everything you’ve learned at work. 🚀

“well worth the money”

Fluent in SQL in a month

Master Data Analysis with SQL with real life examples from Product Management, Marketing, Finance and more.
-- Type your query here, for example this one -- lists all records from users table: SELECT * FROM users
LIMIT 500
Loading chart... ⏳