1. Create Cloud Function that handles Sigma webhooks

Start by creating new Cloud Function.

Runtime variables

Make sure to add STRIPE_SECRET runtime variable:

index.js

const { Storage } = require("@google-cloud/storage");
const { BigQuery } = require("@google-cloud/bigquery");
const fetch = require("node-fetch");

const storage = new Storage();
const bigquery = new BigQuery();
const bucket = "your-cloud-storage-bucket";

async function uploadFile(fetchPromise, bucket, filename) {
  let result = await fetchPromise;
  return new Promise((resolve, reject) => {
    const readStream = result.body;
    const writeStream = storage
      .bucket(bucket)
      .file(filename)
      .createWriteStream();

readStream.on("error", reject);
    writeStream.on("error", reject);
    writeStream.on("finish", () => resolve(true));

readStream.pipe(writeStream);
  });
}

exports.upload = async (req, res) => {
  if (req.body.type === "sigma.scheduled_query_run.created") {
    const [bq, _project, dataset, table] =
      req.body.data.object.title.split(":");
    if (bq !== "bigquery") {
      return res.status(200).send(`Ignoring ${req.body.data.object.title}. No bigquery: prefix`);
    }

const url = req.body.data.object.file.url;
    const fetchPromise = fetch(url, {
      headers: {
        Authorization:
          "Basic " + Buffer.from(process.env.STRIPE_SECRET).toString("base64"),
      },
    });
    const filename =
      req.body.data.object.id + "/" + req.body.data.object.file.id;
    await uploadFile(fetchPromise, bucket, filename);
    const metadata = {
      sourceFormat: "CSV",
      skipLeadingRows: 1,
      autodetect: true,
      location: "EU",
    };
    try {      
     const [exists] = await bigquery.dataset(dataset).table(table).exists();
     if (exists) {
        await bigquery.dataset(dataset).table(table).delete();
      }
      await bigquery
        .dataset(dataset)
        .table(table)
        .load(storage.bucket(bucket).file(filename), metadata);
    } catch (e) {
      return res.status(500).send(JSON.stringify(e));
    }

return res.status(200).send(req.body.data.object.file.id);
  }
  return res.status(500).send("Failed");
};

package.json

{
  "name": "stripe-sigma",
  "version": "0.0.1",
  "dependencies": {
    "@google-cloud/storage": "^5.11.0",
    "@google-cloud/bigquery": "^5.7.0",
    "node-fetch": "^2.6.1"
  }
}

Deploy this function and copy the trigger URL:

2. Add Webhook endpoint

Go to Webhooks and create event, providing Endpoint URL from previous step and selecting sigma_scheduled_query_run.created event:

3. Create Sigma Scheduled query

Go to Sigma dashboard (https://dashboard.stripe.com/sigma/queries)

Create a new query with any SQL you like (here I’m just loading all the charges data) and assign a name according to the convention: bigquery:project_id:dataset_id:table_name

Schedule this query using “Schedule” button, you can leave out emails so that you don’t get notified in any other way than Webhook.

From now on, the results from this query will be saved under stripe_charges table in segmentation dataset.

Slowing down innovation

Choosing Boring Technology can lead to slowing down innovation. It started recently — in 2015. It may be hard to notice because it’s fresh, but having similar movement going popular 20 years earlier could be disastrous. Many valuable languages and technologies (Ruby, .NET, Rust, to name a few) would have a really hard time becoming popular. Language or technology, no matter how good it is, cannot thrive without adoption and community. Especially early on in its life. Choose Boring Technology manifesto stands in opposition to early adoption and may slow down innovation.

Joy of programming

The choice of some new framework, technology or language can make us slower in delivering value. It can make it harder to maintain the codebase in the future. Fewer developers know it. All of it matters from a short-term business perspective. But there is more to life and work than just maximizing the business value of everything we do. It’s important to keep business in mind and optimize ROI, but it’s too easy to fall into the trap of optimizing too much.

This new exciting thing will make our team learn a lot faster than something well-established. It will give a new perspective to what we already know. It can help to attract talents. It can bring more joy to programming. Sometimes business risk outweighs all those, but it’s rare. We tend to overestimate business risk, and I write it from an entrepreneur’s perspective.

Case in point

I help to build Surfer as co-founder and CTO for over three years now. It started as a side project, with a simple idea of creating better SEO tooling, now it’s a $3M ARR, entirely bootstrapped company, hiring 30 people. Our primary goal for this project was to learn. I was an experienced Ruby developer at that point. Learning JavaScript gave me a completely new perspective on programming and made me a better developer in Ruby and everything else. I knew Choosing Exciting Technology was a way to go.

Elixir became Surfer’s foundation. It wasn’t completely new territory for me — syntax is similar to Ruby, and I knew some functional programming from JavaScript, but it was Exciting, not Boring. Some would say I should have chosen Rails. Three years later, I know choosing Elixir was one of the primary reasons why we thrive. It was an excellent choice with many benefits.

I learned a lot. It kept me motivated through hard times with no customers, as I kept saying to myself if this won’t work, at least I’d learn something. Later on, it was a magnet on great talents which would be hard to acquire otherwise. Technology, especially a niche one, can be a good filter for hiring developers. If someone is interested in Elixir, we can assume they have curiosity, an essential feat for a programmer.

Did it make us slower in the beginning? Sure. But it kept us motivated when it was tough. Is it harder to hire Elixir developer than Ruby developer? Yep. But all of the programmers we interviewed were exceptional.

Don’t follow rules of thumb blindly. If excitement drives you, you are not alone. Join Choose Exciting Technology club.

To set it up, you will need a domain, but if you need an URL shortener you probably have some domain already. We used go.surferseo.com subdomain which can make a good default.

The code to get it working is just a few lines of JavaScript:

const redirects = new Map([
  ["cloudflare-shortener", "https://lucjan.medium.com/free-url-shortener-with-cloudflare-workers-125eaf87b1ec"],
  ["neil-pattel-copied-my-design", "https://lucjan.medium.com/how-neil-patel-copied-my-design-and-im-not-even-mad-ede6e99648c0"],
  ["surfer", "https://surferseo.com"],
])

addEventListener('fetch', event => {
  event.respondWith(handleRedirect(event.request));
})

async function handleRedirect(request) {
  let pathname = new URL(request.url).pathname.replace("/", "");
  let location = redirects.get(pathname);
  return location 
    ? Response.redirect(location, 301) 
    : new Response("Not Found", {status: 404})
}

In this case, whenever I go to go.surferseo.com/cloudflare-shortener, I’d be redirected to this very article. Handling another path is just a matter of adding one line of JS (which could be further simplified into Cloudflare KV store to make non-technical people lives easier).

How to get it working

Once you add your domain to Cloudflare, go to Workers tab:

then Manage Workers:

Create a Worker:

Replace the JS code with the one above and hit Save and Deploy, then confirm:

Your shortener will be available under *.workers.dev domain and you can test if everything works fine.

If it does, go back to the Workers tab in your domain settings and hit Add route just under Manage Workers, typing the subdomain (or naked domain) and selecting the worker you just created.

The last step is to add dummy, proxied (orange cloud) subdomain in the DNS tab, so the worker can pick it up:

If I managed to explain the steps well and you could follow through, you should have your very own, simple and free URL Shortener working!

To add, remove or change routes, just head back to Manage Workers, click the worker you created and then Quick Edit:

After changing the routes and hitting Save and Deploy (don’t forget about comma after each one), it will be available in a few seconds.

Have fun!

Our free SEO tool got excellent traction fast — just after seven months, we have 100k+ people using it for their SEO.

People raved about Keyword Surfer from the day one, and in April 2020, we introduced the second version of the tool.

Is this all a great validation of our work? Yes, definitely.

The fun part is: I was the only one who coded and designed the first version of Keyword Surfer that attracted the first 60k users. Me. Developer at my very core. It looked okay-ish, but you could tell it wasn’t touched by a real designer.

You cannot imagine how shocked, surprised, and kind of amused I was when I noticed that someone took great inspiration from my work and created an extension that could be easily confused with Keyword Surfer… its first version to be specific.

Yesterday I stumbled upon a blogpost announcing Ubersuggest Chrome Extension, and I could hardly believe my own eyes.

I was staring at the screenshot showing the tool, which was literally how Keyword Surfer looked like in its previous version! See it for yourself:

Apart from some visual details (colors, typography), it’s an extremely close look-alike!

Now, don’t get me wrong. I don’t claim that placing data directly in Google results was my idea. There are other extensions out there that enrich Google’s results with data. Many of them were created before Keyword Surfer.

BUT THIS!

A grey bar under each page title in search results with data. Backlink and visibility correlation charts on the right. Turn on and off switch. Settings placement. It looks like a freaking clone!

I can see how, in many ways, Neil Patel’s extension is better than Keyword Surfer 1.0, which proves they didn’t just brainlessly copy our work. (Fortunately, Keyword Surfer 2.0 is better in many ways than Neil Patel’s tool as well 😉)

And while I feel surprised… I am not mad at all.

As I mentioned, I don’t have a lot of UI/UX design experience — I’m a developer who just wears a lot of hats as a startup co-founder. If such a successful entrepreneur releases a tool that closely mimics my design, it means it’s not that bad at all!

In fact, it’s not the first time someone “took inspiration” from something I’ve designed. And I’m okay with that. If anything, it proves that it’s a good design, and I feel really flattered when it happens.

“Is it okay?”

If you asked me if it was okay to copy the look and feel of other tools like this, this would be my answer:

Personally, I wouldn’t do that. Not because it’s fair or not. Those who decided to copy our design, made a business decision basing on fragmented information.

We implemented those designs months ago, when we were a really small team, with no designer on board. We knew we would iterate the interface as soon as it is possible. We collected little to no feedback before the release. As we have been flooded with feedback, Keyword Surfer 2.0 was born — based on data and real needs of real people.

When you copy someone’s design (or work in general), you don’t know the process that led to this design. And truth to be told, our process was focused on fast delivery while having no real designer on board.

So I’m not mad that Neil Patel copied my design. I am just endlessly happy Keyword Surfer 2.0 looks as cool as it does.

If you encounter the problem of WebSockets connection handled with HAProxy being lost, increasing the tunnel timeout to 60s or disabling it using 0 will probably fix the problem. If you’d like to know why does it happen, read along!

Context

First of all, let’s start with how our single-page app communicates with the backend, which is necessary to understand what happened.

Instead of a standard, XHR approach, we use Phoenix channels as a sort of RPC for our backend. When user logs in to Surfer, we establish a WebSocket connection using Phoenix JS library:

this.socket = new Socket(host(), {
  params: { token: this.props.currentUser.token },
  reconnectAfterMs: () => 1000,
})
this.socket.connect()

After that we establish a single channel for given user to handle all the communication:

this.channel =
  this.socket.channel(`room:${this.props.currentUser.id}`)
this.channel.join()

When we need to make a request to the backend , we channel.push the data and receive the response:

this.channel.push('billing:status', {}).receive('ok', billing => {
  this.setState({ billing })
})

Problem

One day we noticed some of the requests seem to fail, ending up with endless loading spinner. After refreshing the page it was clear that responses were successful, so the problem was with receiving the response and not with the request itself. We also noticed that our WebSocket is reconnecting every few seconds. Both problems were visible only on staging environment with production and local being untouched.

After some time scratching our heads and trying different things, we recalled we’ve made some changes in HAProxy configuration recently to support the downtime for a migration we wanted to make in the next few days. The change was simple — we reduced server and client timeout to make sure user won’t wait too long for our custom 503 page to appear:

-  timeout client 50000ms
-  timeout server 50000ms
-  retries 50
+  timeout client 3000ms
+  timeout server 3000ms
+  retries 1

After rolling it back it started to work perfectly again, but we still weren’t 100% sure why. Using the shorter timeout, the channel was reconnecting every 3 seconds and some requests failed to receive the response, but why it wasn’t the case for the 50 seconds timeout?

Cause

In order to keep the WebSocket connection alive, Phoenix Socket library sends a heartbeat message which happens every 30 seconds by default. With 50 seconds timeout for the client/server everything worked fine, reducing the timeout to merely 3 seconds made WebSocket connection to timeout, so that when request took any longer, the frontend was unable to receive the response on time.

Solution

Obvious one was to reduce the heartbeat interval to something less than 3 seconds to make sure we keep the connection alive even with the migration config, but it wasn’t really appealing to spam the server such often.

After some googling we figured out there was another HAProxy timeout setting which is responsible for a tunnel connections:

The tunnel timeout applies when a bidirectional connection is established
between a client and a server, and the connection remains inactive in both
directions
https://cbonte.github.io/haproxy-dconv/1.7/configuration.html#4-timeout%20tunnel

When connection becomes a tunnel (as it happens for WebSockets) this timeout setting supersedes both the client and server timeouts.

Here’s how it looks in the config:

timeout client 3000ms
timeout server 3000ms
timeout tunnel 50000ms

The proper solution was to keep the client/server timeouts low for the period of migration but set the tunnel timeout separately, so that when the app goes back up it will work fine immediately, giving us time to deploy the old HAProxy settings (which are needed to properly handle regular deployments with shorter downtimes, not giving 503 when they happen).