
I'm excited to announce that PopSQL has raised a $3.4M seed round led by Gradient Ventures (Google's AI venture fund), with participation from FundersClub, Y Combinator, and many great angel investors: Max Mullen (Co-Founder Instacart), Calvin French-Owen (CTO Segment), Guillermo Rauch (CEO Vercel), Rafael Corrales (Background.vc), Chris Nguyen (Co-Founder LogDNA), Immad Akhund (CEO Mercury), Oliver Jay (Asana), Jeff Chang (Pinterest), Brianne Kimmel (worklife.vc), and others. These people have helped us every step of the way and I'm immensely grateful to them. This round of financing will help us grow the team so we can pursue our vision of creating the best tool for teams to collaborate using data.

## History

I started PopSQL because I was frustrated with the existing tools on the market. My favorite SQL editor at the time was compatible with MySQL only, so I couldn't use it with our data warehouse. I was saving `.sql` files on my computer and copying/pasting SQL to people over Slack. When I had an insight to share, I had to export the query results to CSV, import to Google Sheets, and share the link with a coworker, but it was only a static snapshot of the data, and it was missing the actual SQL behind it. If the query or results changed, I had to repeat the entire tedious process. There had to be a better way.

So I started coding on the side while working as an engineer at Instacart and launched the initial product on Product Hunt. The results were exciting, and major companies like DoorDash, Redfin, and Shipt started to adopt the product. Next, PopSQL was accepted into Y Combinator, raised a seed round, and now, here we are.

Over the last few months, we've added a ton of value to the product:
* **We improved charting**: now you can create and customize charts right in PopSQL. No need to switch to Sheets to visualize your data.
* **Version history**: Restore your query to previous points in time if you make a mistake. Make bulk changes in your git repo and have them sync to PopSQL using our GitHub integration.
* **Scheduling and alerts**: Schedule your queries and dashboards to run every morning and send the results to Slack so you always have fresh data.
* **Dashboards**: Add multiple queries and charts to see your company metrics at a glance.
* **Search**: Instead of re-writing a query, search if someone else has already written it
* **Query variables**: Make it easy for business users to re-run your query with different variables, like user IDs or dates. Analysts can also use conditional logic using "if" statements.
* **Shared connections**: Make it safe and easy for teammates to access the database.
* **Web application**: Access PopSQL without having to download the desktop app at app.popsql.com.

## What's next?

We have big plans to take PopSQL further. We want to make the SQL editor more powerful: better autocomplete, snippets, and more to give data analysts superpowers. We want to take our collaborative features further with better storytelling, comments, and data dictionaries.

## We're hiring!

If you're passionate about data and want to shape the next generation of data tools, we want to hear from you! We're hiring for engineering: frontend, backend, and full stack. Check out <a href="/about">popsql.com/about</a> for more details.

Onwards! 🚀

Rahil<br />
CEO, PopSQL


Announcing PopSQL's $3.4M Seed Round


## GitHub Integration

We now have a GitHub integration! Once you connect a repo to PopSQL, if you make a change in PopSQL, it gets pushed to GitHub, and if you make a change on GitHub, it gets pushed to PopSQL. This gives you more control of your data. For example, if you need to make a bulk change to your SQL queries (ie a table name change), you can easily do a find and replace, push to GitHub, and the changes will be reflected in PopSQL. This feature is available in our Enterprise plan. Please contact us if you'd like to add it to your plan.

## More Metadata to Help You Understand What's Relevant

As you create more queries in PopSQL, it becomes hard to know which queries are relevant/important, and which queries are deprecated/safe to ignore. To solve that, we've added additional metadata to queries that tell you when the query was written, who it was written by, when it was last run, and more.

<img src="/static/images/blog/20200823_social_context.gif" />

## Improved Search

We improved the search experience so that it's faster to find what you're looking for, and we added additional metadata to show you what's relevant and what's not.

When you first open the search modal with `Cmd+K`, you'll see a list of queries you recently opened. As you start typing, we'll do an instant search for **query names** that match your search term. This is useful when you know the exact name of the query you want.

<img src="/static/images/search/fuzzyfinder.gif" />

For Premium Team users, if you want PopSQL to perform a deeper search to search the **body** of your queries, press `Enter`. This is useful when you want to search if a specific table is being used, or search by a comment you know you wrote. In this example, I'm searching for a [CTE](/learn-sql/postgresql/how-to-write-a-common-table-expression-in-postgresql) I know I've used.

<img src="/static/images/search/advanced_search.png" />

## Other Changes in This Release

* You now have the ability to rename dashboard widgets :tada:
* We simplified the `Share` button


GitHub Integration, More Metadata to Help with Relevance, and Improved Search


[Query variables](/docs/query-variables) let data analysts DRY up their SQL without typing the same values over and over again. More importantly, they allow business users to easily re-run queries with different parameters, like when sales wants to pull a report on a specific account, or marketing wants to pull a report on a specific campaign.

We've improved query variables so that you can:
* Specify a type
  * String
  * Number
  * Date
  * Boolean
  * Dropdown
* Set a default value
* Mark the variable required

![](/static/images/query_variables/settings.gif)

Types are important so your teammates know what kind of value to fill in. For example, you can make a dropdown list:

<img src="/static/images/query_variables/dropdown.gif" />

If someone tries to run your query without filling out a required variable:

<img src="/static/images/blog/20200730_query_variable_required.png" class="w-1/2" />

For full details and lots more examples, please see our docs on [query variables](/docs/query-variables). Enjoy! :tada:

Query Variable Improvements


In this update, we've made it easier to analyze your results, switch between queries, and invite teammates.

## Improved query results table

We improved the [query results table](/docs/getting-started/query-results) to feel more like a spreadsheet. You can now:

* Filter columns
* Sort columns
* Hide columns
* Expand a row to read it in a vertical format
* Highlight cells to copy/paste into a spreadsheet

![](/static/images/query_results/table.mp4)

## Automatically switch connections when you switch queries

You might have some queries that are meant to be run against one connection (eg Postgres), and other queries that are meant to be run against another connection (eg Snowflake). PopSQL will now remember which connection you used with each query and automatically switch connections for you :tada:

## Invitation links

We've added a new way for people to join your PopSQL organization: [invitation links](/docs/popsql-for-teams/inviting-teammates#invitation-links).

![](/static/images/inviting_teammates/copy_invitation_link.mp4)


July 2020 Update


Lots of new features and improvements to announce in PopSQL this month! 🎉

## Public dashboards

Now you can make your dashboards public so people don't have to sign in to view your dashboard.

![](/static/images/blog/20200619_public_dashboards.gif)

## Scheduled dashboards

Now you can schedule dashboards to refresh at a set interval. Perfect for having fresh metrics every morning when your day starts.

![](/static/images/dashboards/schedule-refresh.png)

## Slack notifications for scheduled queries and dashboards

When your scheduled query or scheduled dashboard completes, PopSQL can now send a Slack message with a beautiful preview. Visit our [Slack docs](/docs/slack) to connect Slack with PopSQL.

![](/static/images/blog/20200619_scheduled_dashboard_slack.png)

## Share a link to a specific run

Previously, [presentation links](/popsql-for-teams/sharing-a-link-to-your-query-and-results) would show the _latest_ run of your query.
Now, if you click the "link" icon on a run, the URL will be specific to that run. So when you share the link with someone, they'll see the exact same results you see.

![](/static/images/blog/20200619_share_link_to_run.gif)

## Expand rows for better readability

When you hover a row, click the arrow icon to expand the row so you can easily read the row.

![](/static/images/blog/20200619_expand_row.gif)


June 2020 Update


Lots of exciting things to announce today!

## Charts

Since the very beginning of PopSQL, customers have wanted to customize their charts in PopSQL. They liked that we automatically created a chart for them based on their SQL query results, but they didn't like that they couldn't configure that chart, like changing from bar chart to line chart, or choosing which column should be your X-axis.

Today, we're excited to announce that **you can now fully customize your charts in PopSQL!** After running your query, hit the `Chart` button, and you'll be able to create a beautiful chart to your liking. You can choose the chart type (bar, line, pie, etc), choose your X-axis, and choose one or many columns from your SQL query to plot. This saves precious time by eliminating the need to export to Excel just to make a chart.

Learn more about our new charts on our [docs](/docs/getting-more-out-of-popsql/how-to-make-charts-to-visualize-your-data/).

![charts](/static/blog/videos/charts.mp4)

## Draft Queries

When you create a new query in PopSQL, it starts off with the name "Untitled Query" until you give it a name, just like a Google Doc. The problem is, sometimes you're writing an ephemeral query that you have no intention of reusing or sharing, and therefore don't want to give it a name. So you end up with dozens of untitled queries, cluttering your private folder or team folder.

To solve this, **new queries now start off as drafts** and live in a new drafts folder. If you don't care about the query, there's no need to name it. If you do care about the query, give it a name, move it into your folder "My queries," or move it into "Team queries" if you want it to be discoverable by your team. We hope this helps you create a more organized workspace.

## Database Switcher and Draggable Tabs

Two other small but much needed improvements:

If you're connected to a server that has multiple databases, **you can now quickly switch between the databases**:
![database switcher](/static/blog/gifs/20200504_database_switcher.gif)

**You can now drag and rearrange tabs** in PopSQL, just like you can in Chrome:
![draggable tabs](/static/blog/gifs/20200504_draggable_tabs.gif)


New charts, draft queries, and more


New features in 0.9.10 make PopSQL a more robust editor and allow teams to share links to queries with more control.

## Enhanced editor

We've greatly enhanced the query authoring experience. From minimap navigation to clearer error messages, these updates to the editor make query writing smoother and faster. You can customize the experience in your [app preferences](/docs/getting-started/customizing-your-preferences/).

<script src="https://fast.wistia.com/embed/medias/uoibau3ps5.jsonp" async></script></script><script src="https://fast.wistia.com/assets/external/E-v1.js" async></script><div class="wistia_responsive_padding" style="padding:55.94% 0 0 0;position:relative;"><div class="wistia_responsive_wrapper" style="height:100%;left:0;position:absolute;top:0;width:100%;"><div class="wistia_embed wistia_async_uoibau3ps5 videoFoam=true" style="height:100%;position:relative;width:100%"><div class="wistia_swatch" style="height:100%;left:0;opacity:0;overflow:hidden;position:absolute;top:0;transition:opacity 200ms;width:100%;"><img src="https://fast.wistia.com/embed/medias/uoibau3ps5/swatch" style="filter:blur(5px);height:100%;object-fit:contain;width:100%;" alt="" aria-hidden="true" onLoad="this.parentNode.style.opacity=1;" /></div></div></div></div>

## Run all statements

You can now run multiple statements at once. Perfect for faster analysis or chaining together actions (like creating, populating, and analyzing a [temporary table](/docs/getting-more-out-of-popsql/temporary-tables/) in one click).

![run all statements](/static/blog/videos/run-all-statements.mp4)

## Public share links

We have a new `Share` button that lets you get a PopSQL desktop link, web app link, or a presentation link. We also support public links now, so can share your query with people without them having to sign in. Check out our [sharing docs](/docs/popsql-for-teams/sharing-a-link-to-your-query-and-results/) for all the details.

![public share links](/static/blog/videos/public-share-links.mp4)

Happy querying! 🍭


Enhanced editor, run all statements, and public share links


We've added many features in 0.9.5 that make PopSQL a better tool for teams and power-users alike.

## Team-Focused Features

### Slack Integrations

Does your team share a lot of links to PopSQL queries? Get the gist of a query without ever leaving Slack with our [new integration](/docs/getting-more-out-of-popsql/slack-integration/).
![alt text](/static/images/slack/unfurl.gif "A PopSQL link shared in Slack now displays key metadata about the query.")

### Query Descriptions

Introducing query descriptions in PopSQL. Bring all the needed context up top where your teammates can see it.
![alt text](/static/images/blog/20200331_query_descriptions.gif "User adding a description to a query in PopSQL")

### Invite Teammates

The more teammates on PopSQL, the more time you save. Now invite all your favorite colleagues in just a click.
![alt text](/static/images/blog/20200331_invite_teammates.gif "User opens the invite teammates feature in PopSQL and quickly sends multiple teammates an invitation.")

## Editor Improvements

### Improved Temporary Table Experience

Use a lot of temp tables? We’ve added [new preferences](/docs/getting-more-out-of-popsql/temporary-tables/) to give you an even better experience in PostgreSQL and Redshift.
![alt text](/static/images/blog/20200331_temp_table_improvements.png "Screenshot of preferences menu that allows user to specify how temporary tables are handled in PopSQL.")

### Attach a CSV to a Scheduled Query

When you [schedule a query](/docs/getting-more-out-of-popsql/scheduled-queries/) in PopSQL, you can now get a CSV of the query results in the notification email. Save a few steps or power a workflow.
![alt text](/static/images/blog/20200331_scheduled_query_with_attached_csv.gif "User schedules a query in PopSQL and specifies results include a CSV attached to the notification email.")

### Connect to Snowflake with Okta / SSO

For teams who use MFA and/or Okta to sign into Snowflake, we have you covered. [Learn more](/docs/getting-started/connecting-to-snowflake/) about connecting with Okta.
![alt text](/static/images/blog/20200331_snowflake_okta.png "Snowflake and Okta logos, announcing that users can connect to Snowflake with Okta in PopSQL")

### Easy Access Preferences

PopSQL now puts your preferences and other key actions right where you need them.
![alt text](/static/images/blog/20200331_easy_access_preferences.gif "A screenshot showing a new access point to preferences menu in PopSQL")


Slack integration, query descriptions, and more


With dashboards, you can see your key metrics at a glance. Add multiple queries and visualizations to a dashboard to stay informed on key metrics.

Choose whether to view the results as data or an [auto-generated chart](/docs/getting-more-out-of-popsql/how-to-make-charts-to-visualize-your-data/). Then rearrange and resize the results however you’d like. And with [query variables](/docs/query-variables/), you can refresh the entire dashboard with a click (view data by day or by week).

Perfect for weekly reports, product launches, marketing campaigns, sales growth, database health... any metric that matters to your team!

![alt text](/static/images/dashboards/dashboards.png)

This feature is available now for Premium Team users. Please see our [docs on dashboards](/docs/getting-more-out-of-popsql/dashboards/) for full information - enjoy! 📊


Dashboards


PopSQL now saves you time, even when you don't have PopSQL open.

With scheduled queries, PopSQL can alert you on important events (think new orders over a certain size, or no new data in the past day). You can also schedule your queries to run at a specific point in time, or on a recurring basis like every hour, day, or week. No more updating queries fifteen minutes before the next meeting; let PopSQL do it for you.

![alt text](/static/images/blog/20200127_scheduled_queries.png "Image of PopSQL modal for scheduling a query")

This feature is available now for Premium Team users. Please see our docs on [how to schedule queries](/docs/getting-more-out-of-popsql/scheduled-queries/) for full information - enjoy!


Scheduled Queries


PopSQL now supports connecting to BigQuery via OAuth. You can still connect via a Service Account if you'd like, but an OAuth connection is faster and simpler.

Connecting to OAuth is supported on the PopSQL desktop and [web app](https://app.popsql.com).

Read the [full guide on how to connect to BigQuery](/docs/getting-started/connecting-to-bigquery/).


BigQuery OAuth


PopSQL makes it extremely easy for your team to switch between databases. Those databases, though, have differences in syntax that are tricky to remember.

Date and time functions have tripped us up in particular. That's why we created this handy cheat sheet for SQL date and time functions across some of our most popular databases: PostgreSQL, MySQL, Redshift, Snowflake, and BigQuery.

<a href="https://popsql-misc.s3.amazonaws.com/popsql-datetime-cheatsheet.pdf" target="_blank">Download the PDF</a>

<embed src="https://popsql-misc.s3.amazonaws.com/popsql-datetime-cheatsheet.pdf" type="application/pdf" width="100%" height="700px">


SQL Date and Time Functions Cheat Sheet


Free samples are easily my favorite thing about grocery stores.

Free sample datasets, though? Those can be awfully dry.

You're guaranteed to need sample data at some point. Perhaps you need to interview a potential analyst but can't use your company's data. Maybe you're tired of your company's data. It's not the same as when you first met it. And you're dying to rekindle that spark that made you love SQL in the first place.

We've scoured the web for some non-boring datasets so you don't have to:

- <a href="https://github.com/timescale/examples/tree/master/crypto_tutorial/Cryptocurrency%20dataset%20Sept%2016%202019" target="_blank">Historical Bitcoin Prices</a>
- <a href="https://tech.instacart.com/3-million-instacart-orders-open-sourced-d40d29ead6f2" target="_blank">3 Million Instacart Orders (Open sourced)</a>
- <a href="https://movement.uber.com/cities?lang=en-US" target="_blank">2 Billion Uber rides (anonymized)</a>
- <a href="https://pleiades.stoa.org/" target="_blank">Database of the Ancient World</a> _(for history buffs)_
- <a href="https://www.kaggle.com/tmdb/tmdb-movie-metadata" target="_blank">5000 Movies Dataset</a>
- <a href="https://docs.atlas.mongodb.com/sample-data/sample-airbnb/" target="_blank">Airbnb listings Data</a> _(for MongoDB)_
- <a href="https://webrobots.io/kickstarter-datasets/" target="_blank">Data on all Kickstarter projects since 2016</a>
- <a href="https://www.kaggle.com/NUFORC/ufo-sightings" target="_blank">UFO Sightings</a> _(can you believe there have been 80,000 sightings and we still haven't taken a good picture?)_

Most of these datasets will be downloaded as a CSV. If you don't know how to import a CSV into a database, we have guides for that too: [PostgreSQL](/learn-sql/postgresql/how-to-import-a-csv-in-postgresql/), [MySQL](/learn-sql/mysql/how-to-import-a-csv-in-mysql), [Redshift](/learn-sql/redshift/how-to-import-a-csv-in-redshift/).

Need more data? Sign up for Jeremy Singer-Vine's newsletter, <a href="http://tinyletter.com/data-is-plural" target="_blank">Data is Plural</a>. He sends 5 interesting datasets every Wednesday.

Stay querying, my friends.


Cool Sample Data


We've been working hard to build the best SQL editor for teams. With release 0.7.8, the two most highly requested databases are now live on PopSQL: Amazon Athena and MongoDB. Learn more on how to connect to [Amazon Athena](/docs/getting-started/connecting-to-athena/) and [MongoDB](/docs/getting-started/connecting-to-mongodb/).


Announcing Support for MongoDB and Athena


Better autocomplete, auto limiting results, and some performance improvements are all new in version 0.6.6 of PopSQL.

## Better autocomplete

​Now suggestions will be more contextual. We'll suggest columns for tables that are in your query. Works with aliases too!

![alt text](/static/images/blog/20190402_better_autocomplete.gif "GIF of autocompleting an inner join")

## Auto limit

​Sometimes you might forget to include a `limit` clause in your query and end up executing an expensive query. Now PopSQL will automatically include a `limit 100` clause for you. Of course you can disable this whenever you want by unchecking the box, or providing your own `limit` clause.

​![alt text](/static/docs/query/auto_limit.gif)

## Performance improvements and bug fixes

Integer sorting is actually works now, and opening or closing `Explore` doesn't take ages anymore. Enjoy!


Better Autocomplete and Auto Limit


Now PopSQL queries are automatically version controlled. You can view unlimited version history of your query and restore to any point in time. This is useful if you or a coworker has made a mistake on your query and you'd like to go back in time.

![alt text](/static/images/blog/20190203_version_history.png "Screenshot of version history")

Version history is available now for Premium Team members. Please see our [docs on version history](/docs/popsql-for-teams/version-history/) for full information 🎉


Version Control for SQL Queries


SQL queries commonly have a `where` condition for a specific ID or date range. Wouldn't it be nice to extract that into a variable? For example, show me users that belong to `$organization_id`, or users created after `$start_date`. If you're sharing SQL queries with non technical folks, what if they could edit the variable without changing the any code?

Now in PopSQL, you can have <strong>variables</strong> in your SQL queries. To create a variable, simply wrap you variable in double curly braces, like `{{id}}`:

![alt text](/static/images/blog/20190110_query_variables.png "Screenshot of a query variable")

This feature is available now for Premium members. Please see our [docs on query variables](/docs/query-variables/) for full information - enjoy!


Query Variables


A common workflow for is to [create a temporary table](/learn-sql/postgresql/how-to-create-a-table-in-postgresql/) to break down your analysis into smaller pieces.

Before, PopSQL would only maintain PostgreSQL and Redshift sessions for 30 seconds. So if you ran one query to create a temporary table and didn't run another query for 30 seconds, the session would be terminated and your temporary table would be dropped. Bummer!

Now, PopSQL will keep your PostgreSQL and Redshift sessions open for 8 hours. So if you run a query to create a temporary table, you can come back up to 8 hours later and it will still be there for you to continue your analysis.

Enjoy!


Temporary Tables


Has anyone written a query on retention? Does anyone use this table or column? How do you use that function again? These are all questions you can answer using PopSQL's improved search capability, now available for Premium Team subscribers.

You'll find a new search box in the top right of the app:

![alt text](/static/images/blog/20180716_search_box.png "Screenshot of the search box in PopSQL")

Clicking the search box will open the search popup. You can also press Cmd+T for faster access. When the search popup opens, we'll show you <strong>recently opened queries</strong> so you can jump back somewhere if you need to. When you start typing, we'll instantly <strong>search queries by name</strong>. You can use your mouse or arrows keys to quickly navigate to a result.

![alt text](/static/images/blog/20180716_recent_history.png "Screenshot of recently opened queries in PopSQL")

For Premium Team subscribers, we'll also perform a deeper search to find <strong>queries that have matching content</strong>. This is where you can see if anyone is using that table or column, or how to use that confusing function, or search by a comment you know you wrote last week.

![alt text](/static/images/blog/20180716_advanced_search.gif "Screenshot of the searching query contents in PopSQL")

We hope these new search capabilities help you get your data faster!


Search


If you're writing code for hours, you want something easy on your eyes. That's why we created dark mode for PopSQL, by popular demand.

![alt text](/static/images/blog/20180520_dark_mode.png "Screenshot of PopSQL dark mode")

Dark mode is available today for [Premium](https://popsql.com/pricing) users. To turn it on, go to `Preferences > Editor > Theme`, or in your menubar, `View > Dark Mode`.

Enjoy!


Dark Mode

Mixpanel is great, but it's very helpful to have your raw event data in your database so you can use SQL to extract insights. In this post, I'll show you how you can accomplish that. We'll be using Ruby on Rails and PostgreSQL, but the general approach of pulling data from Mixpanel, inserting it into the database, and scheduling a background job on an interval should work with any language and database.



## Overview

Here's what we'll cover:

- A service class that is responsible for consuming the Mixpanel API, parsing the response body into JSON, building a database row for each event, and inserting those events efficiently
- A background job that calls the service class
- A scheduler that runs the background job on an interval (eg every minute)

There are some considerations that make this not so simple:

- How to avoid downloading duplicate events from Mixpanel?
- How to convert Mixpanel event timestamps to UTC?
- What to do if the request to Mixpanel fails?
- What to do if your database is slow and the background job takes longer than the interval?
- How can we do this in a memory efficient way?

## Dependencies

Here are some of the technologies we're using:

- <a href="https://github.com/mperham/sidekiq" target="_blank">Sidekiq</a> for background jobs
- <a href="https://github.com/mhenrixon/sidekiq-unique-jobs" target="_blank">sidekiq-unique-jobs</a> to create a lock while the background job is running
- <a href="https://github.com/nickelser/zhong" target="_blank">Zhong</a> for scheduling jobs
- <a href="https://github.com/ohler55/oj" target="_blank">Oj</a> for fast JSON parsing
- <a href="https://github.com/zdennis/activerecord-import" target="_blank">activerecord-import</a> for bulk database inserts
- <a href="https://www.mixpanel.com" target="_blank">Mixpanel</a>...duh :wink:

## The background job

```ruby
class EventWorker
  include Sidekiq::Worker
  sidekiq_options retry: false, unique: :until_executed, log_duplicate_payload: true
  PT = Time.find_zone!('America/Los_Angeles')

  def perform
    from_date = PT.now.hour < 5 ? PT.yesterday : PT.today
    EventService.ingest_mixpanel_events(from_date: from_date, to_date: PT.today)
  end
end
```

We specify `retry: false` because if the job fails, it's okay, let it try again on the next interval.

The `unique` and `log_duplicate_payload` options are provided by `sidekiq-unique-jobs`. This will create a lock and release it only when the job is finished executing. That way, only one of these jobs can run at a time. If someone tries to enqueue this job and one is still running, it will be logged, and it the duplicate won't be processed.

The math with `from_date` is so that when 12:01am strikes, we don't just ignore yesterday's events, because our systems could still push an event that happened minutes ago, or Mixpanel could do the same if they have a delay in their processing. We're effectively creating a 5 hour buffer here. You can adjust this to whatever you like (eg 1 hour, 8 hours, etc).

## The service class

```ruby
# frozen_string_literal: true

class EventService
  def self.ingest_mixpanel_events(from_date:, to_date:)
    ApplicationRecord.transaction do
      # Blow away all the events in this range
      Time.use_zone(PT) do
        query = <<-SQL.squish
          delete from events
          where time between '%s' and '%s'
        SQL
        sanitized_query = ActiveRecord::Base.send(:sanitize_sql_array, [query, from_date.beginning_of_day.to_s(:db), to_date.end_of_day.to_s(:db)])
        ActiveRecord::Base.connection.exec_query(sanitized_query, "EventService#ingest_mixpanel_events")
        log "Deleted events between #{from_date.beginning_of_day} and #{to_date.end_of_day}"
      end

      # Ingest the events in this range
      (from_date..to_date).each do |day|
        events = []
        jsonl_string = fetch_mixpanel_raw_data(from_date: day, to_date: day)
        log "Fetched JSONL events from mixpanel for #{day}"
        Tempfile.open(['mixpanel', '.json']) do |file|
          file.write(jsonl_string.force_encoding('UTF-8'))
          log "Wrote JSONL to #{file.path}"
          file.rewind
          file.each_line do |line|
            events << convert_line_to_event(line)
          end
        end

        # Perform the inserts
        ActiveRecord::Base.logger.silence do
          Event.import(events, batch_size: 500)
        end
        log "Imported events for #{day}"
      end
    end
  end

  def self.convert_line_to_event(line)
    raw_event = Oj.load(line)
    raw_time = Time.zone.at(raw_event.dig('properties', 'time')) # This is a Pacific unix timestamp, but Rails doesn't know that
    pacific_time = Time.find_zone!('America/Los_Angeles').parse(raw_time.iso8601.gsub(/Z$/, '')) # Tell Rails it's a Pacific timestamp
    raw_event['properties']['time'] = pacific_time.utc # Convert the timestamp to UTC
    build_event_from_raw_event(raw_event)
  end

  def self.fetch_mixpanel_raw_data(from_date:, to_date:)
    conn = Faraday.new do |conn|
      conn.basic_auth(Rails.application.secrets.mixpanel_api_secret, '')
      conn.request :url_encoded
      conn.adapter Faraday.default_adapter
      conn.options.timeout = 60 # 60 seconds
    end
    resp = conn.get('https://data.mixpanel.com/api/2.0/export/', {from_date: from_date, to_date: to_date})
    resp.body
  end

  def self.build_event_from_raw_event(raw_event)
    # Choose whatever Mixpanel event properties you want in your table
    Event.new({
      name: raw_event['event'],
      time: raw_event.dig('properties', 'time'),
      distinct_id: raw_event.dig('properties', 'distinct_id')
    })
  end

  private

  def self.log(msg)
    Rails.logger.tagged(self.name) do
      Rails.logger.debug(msg)
    end
  end
end
```

There's a lot going on here, so let's try and break it down.

`ingest_mixpanel_events` the main method of the class. It's responsible for deleting events in our database in the `from_date..to_date` range, asking Mixpanel for the events in this range, and performing the database inserts. Why do we need to delete events in this range first? The reason is because <a href="https://mixpanel.com/help/reference/exporting-raw-data#export-api-reference" target="_blank">Mixpanel's raw data export API</a> doesn't give you unique identifiers for each event, so you can't do something like "give me all of today's events, but don't insert ones that I already have." You _could_ do this with timestamps, like "give me all of today's events, but ignore ones up to $last_import_time," but then there's a chance you'll miss some events if you or Mixpanel ever insert events _before_ $last_import_time.

We use a `Tempfile` to store the response body from Mixpanel on disk rather than in an Array, which keeps the memory usage of this method low. Then we iterate over each line and call `convert_line_to_event`.

Everything is wrapped in a transaction because without one, if someone ran a query for today's events and the background job just deleted today's events, then the query would return nothing. But if they ran that query again in a few seconds when the background job has a chance to complete the inserts, then it would have results. Using a transaction prevents this confusing experience.

`convert_line_to_event` is responsible for taking `line` (String), parsing it as JSON, converting the timestamp to UTC, and calling `build_event_from_raw_event`. The timestamp work is necessary because <a href="https://mixpanel.com/help/reference/exporting-raw-data#api-details" target="_blank">Mixpanel stores events in your project's timezone</a>, and it's best practise to store timestamps in your database as UTC.

`fetch_mixpanel_raw_data` is responsible for hitting <a href="https://mixpanel.com/help/reference/exporting-raw-data#export-api-reference" target="_blank">Mixpanel's raw data export API</a> and returning the response body.

`build_event_from_raw_event` is responsible for taking `raw_event` (JSON) and creating an `Event` instance (assuming you have an `Event` model and `events` table).

## The scheduler

```ruby
every 1.minute, 'Run EventWorker' do
  EventWorker.perform_async
end
```

At PopSQL, we use <a href="https://github.com/nickelser/zhong" target="_blank">Zhong</a> to schedule jobs. Simply add a few lines to your `clock.rb` file to run the background job every minute:

## Ways to make this better

How can we reduce the memory footprint of the background job? When I used <a href="https://github.com/SamSaffron/memory_profiler" target="_blank">memory_profiler</a> to measure the memory usage, a lot of it was coming from initializing instances of `Event` for each row, and doing the time parsing/math.

<a href="https://twitter.com/andrewkane" target="_blank">@andrewkane</a> pointed out I could pass <a href="https://github.com/zdennis/activerecord-import/wiki/Examples#import-using-columns-and-arrays" target="_blank">activerecord-import arrays of columns and values</a>, which is its fastest import mechanism, rather than creating `Event` instances.

We could also hit the Mixpanel API _before_ opening the database transaction.

## Conclusion

Now that your database has your Mixpanel events up to the minute, you can use PopSQL to extract all kinds of insights. Who are the newest users? Which users activated or need help activating? Who's taking high value actions on the site right now that sales should follow up with? Enjoy, and feel free to chime in in the comments below.

<em>Thanks to <a href="https://twitter.com/octernion" target="_blank">Nick Elser</a> and <a href="https://twitter.com/andrewkane" target="_blank">Andrew Kane</a> for guidance and suggestions.</em>

FactoryFix democratizes data access across their company with PopSQL ->

Updates

Ready for a modern SQL editor?