Migrating Community Metrics to Orbit using n8n 📈

It has been more than two years since I have been involved in the community as an organizer. My journey started as a GitHub Campus Expert. My love for privacy and the open internet helped me become a Mozilla Representative, and I am now an Auth0 Ambassador too. This journey is teaching me new skills and pushes me to do better. This is the aim I have for the community as well. Every member should get the opportunity to grow, showcase their skills, and achieve their goals.

To know what everyone was up to, I started keeping records of the activities of the community members using Google Sheets. But with an ever-growing community, it became difficult. Every time a community member contributed, I manually added these records to a Google Sheet. The members of the community are very active, and it became difficult to keep up with the pace. Structuring data in Google Sheets for community metrics was also a painful task. The biggest challenge was making sense of these metrics. So I started using Orbit and connected my Google Sheets to it using n8n to transfer data automatically.

What is Orbit?

The Orbit model is a framework for building communities. Orbit makes it easy for community leaders and Developer Relations (DevRel) Leadership to easily keep track of the DevRel metrics and quantify the business impact.

I started at n8n as a Developer Advocate Intern and was excited to learn that we use Orbit for DevRel metrics. I had read about Orbit and was eager to know more about it. I explored Orbit for a while and ended up creating an n8n workflow that moved the metrics from Google Sheets to Orbit.

Like me, if you too manage DevRel metrics in Google Sheets (or Airtable), follow along the article to learn how you can move these metrics to Orbit using n8n.

Prerequisites

Orbit account

You can request access to Orbit by filling a form on their website. Once you have access to Orbit, create a workspace for your community.

Google account

I used Google Sheets to keep a record of the metrics in this spreadsheet. You will need a Google account to create a Google Sheet. Sign in to your Google account and open this sheet. Click on the File and select ‘Make a Copy’ from the dropdown list to copy the sheet in your account.

Note: If you change the name of the columns or the name of the sheet, make sure to make the changes in the n8n workflow accordingly.

n8n

We will connect Google Sheets to Orbit using n8n. Follow the instructions mentioned in the documentation to install and spin-up an n8n instance on your machine. You can also sign-up for n8n.cloud to get access to our hosted service (in beta).

Quickstart

If you don’t want to get into the details and have a bit of experience with n8n, you can follow the quick-start instructions below to get to the endgame. If something is unclear or you want to learn more about how it works, feel free to dig deeper into the sections that follow.

Access your n8n instance, and copy and paste the workflow from the workflow page. Configure the credentials for the following nodes:

Google Sheets node : configure the OAuth credentials and make sure you enter the correct Sheet ID in the Sheet ID field.

• Get Members : This Google Sheet node fetches the data from the Members sheet. If your sheet has a different name, modify the Range field accordingly. If you’re using the Google Sheet shared earlier, enter the following range: Members!A:F.
• Get Activities : We fetch the activities of our members from the Activities sheet. If you are using a different name for your sheet, modify the Range field accordingly. For the spreadsheet I shared earlier, enter the following range: Activities!A:D.

Orbit node : configure the credentials and select your workspace from the Workspace field.

Save and execute the workflow.

Moving data from Google Sheets to Orbit automatically

Our workflow will fetch the data of our community members and their activities from a Google Sheet and add the information in our Orbit workspace automatically. The workflow uses the following nodes:

1. Get Members (Google Sheets node)
2. Add Members (Orbit node)
3. Get Activities (Google Sheets node)
4. Get all members (Orbit node)
5. Merge
6. Add Activities (Orbit node)

1. Get Members (Google Sheets node)

We are storing our members’ information like their name, GitHub username, Twitter handle, etc. in a Google Sheet. We will use the Google Sheet node to fetch this information.

The following image shows the Members sheet in my Google Sheet.

Follow the instructions mentioned in the documentation to configure the credentials for the Google Sheets node. Enter your Sheet ID in the Sheet ID field and enter a range in the Range field. If you’re using the Google Sheet shared earlier, enter the following range: Members!A:F.

Rename the node to Get Members and click on Execute Node to run the node.

The following image shows the output of the Get Members node.

2. Add Members (Orbit node)

We will use the members’ data that we received from the previous node and pass it on to the Orbit node. To configure the credentials of the Orbit node, follow the instructions mentioned in the documentation.

Select the ‘Upsert’ operation and your workspace in the Orbit node. We are using the GitHub identity to add the members. Select ‘GitHub’ from the Source dropdown list and ‘Username’ from the Search By dropdown list. In the username parameter of the node, use the following expression: {{$json["GitHub"]}}.

In our Google Sheet, we also have information like Location, Tags, Name, and T-shirt size. We will add this information to Orbit as well. You can add this information by selecting them from the Add Field dropdown list. Enter the following expression in the Expression Editor of the respective fields.

• Location : {{$json["Location"]}}
• Name : {{$json["Name"]}}
• Tags to Add : {{$json["Tags']}}
• T-Shirt : {{$json["T-Shirt Size"]}}

Rename the node to Add Members and click on Execute Node to run the node.

The following image shows the output of the Add Members node.

3. Get Activities (Google Sheets node)

Keeping only the data of the community members is not that useful. We also need to keep records of their activities. This record helps us understand the contributions the community members make, the type of content they are involved with, and other critical insights.

The following image shows the Activities sheet in my Google Sheet.

We will get the records of the activities of our community members from the Google sheet and add them to Orbit.

Select the credentials that we used in the Get Members node. Our Google Sheet uses the members’ GitHub username to associate them with their activities. We will use the ‘Lookup’ operation to get this information. Enter the following in the expression editor for the Sheet ID parameter: {{$node["Get Members"].parameter["sheetId"]}}. Enter the range for our Google Sheet in the Range field. If you’re using the Google Sheet shared earlier, enter the following range: Activities!A:D.

Enter ‘GitHub Username’ in the Lookup Column field. If you are using a different column name, enter that instead.

In the Lookup Value field, we pass the GitHub username of all our members that we got from the Get Members node. Enter the following expression in the Lookup Value field: {{$node["Get Members"].json["GitHub"]}}. Toggle Return All Match to true. This will check for the activities of all the members and return all their activities if it exists.

Rename the node to Get Activities and click on Execute Node to run the node.

The following image shows the output from the Get Activities node.

4. Get all members (Orbit node)

To add the activities that we got from the previous node, we need the ID of the members that were generated by Orbit. This node will get the ID of the members along with other information.

Create a new Orbit node and select the credentials that we used in the Add Members node. Select ‘Get All’ from the Operation dropdown list. Select your workspace and toggle Return All to true.

Rename the node to Get all members and click on Execute Node to run the node.

NOTE: Depending on the amount of data we have, the Orbit node might take some time to add the new members to the workspace.

The following image shows the output from the Get all members node.

5. Merge information Google Sheets and Orbit

We now have all the activities of our members from Google Sheet and their member ID from Orbit. We need to merge this information to map the activities to their respective member IDs.

We are using the Merge node to merge this information. Connect one input of the node with the Get Activities node and the other input with the Get all members node.

Select ‘Merge By Key’ from the Mode dropdown list. This option will merge the information using the GitHub username as the key. Enter ‘GitHub Username’ in the Property Input 1 field and ‘attributes.slug’ in the Property Input 2 field.

Click on Execute Node to run the node.

The following image shows the output from the Merge node.

6. Add Activities (Orbit node)

The Merge node returns all the necessary data that we need. Using the Add Activities node, we will add the activities of our community members to Orbit.

In our new Orbit node, select the credentials that we used for the Get all members node. Select ‘Activity’ from the Resource dropdown list and ‘Create’ from the Operation dropdown list.

In the expression editor for the Member ID field, enter the following expression: {{$json["id"]}}. Enter the following expression in the expression editor of the Title field: {{$json["Title"]}}.

We will also add a description and a link (if they exist) for the activities. Add the Description and Link fields from the Add Field dropdown list. Enter the following expression in the Expression Editor of the respective fields.

• Description : {{$json["Description"}}
• Link : {{$json["Activity Link"]}}

Rename the node to Add Activities and click on Execute Node to run the node.

The following image shows the output from the Add Activities node.

What’s next?

The current workflow shows how we can migrate our data from Google Sheet to Orbit. But we might be using Airtable or a CRM tool to manage these records. We can migrate these records to Orbit using n8n in less than 10 mins.

We can build another workflow using the Cron node and the Orbit node to get the information about members with the highest activity in a month. Appreciate their contributions in the community using the Mattermost node or the Slack node (or whatever your community uses), and tweet about their work using the Twitter node.

I previously wrote an article on how you can use the Slash command in Slack and integrate it with n8n to get community members’ information and give a shout-out to them. We can combine these workflows. Use the Orbit node to get the data (email, address, t-shirt size) of the members, the Shopify node to place an order for swags. If you use Printfection, you can use the HTTP Request node to place the order on Printfection. Let us recognize the contributions of the community!

Summary

Community metrics are yet not well defined and is a challenge for a lot of us. But the Orbit model streamlines these metrics and gives us a more efficient output. With this workflow, we now have our community metrics in our Orbit workspace. Orbit displays the data more sensibly and helps us understand the metrics. We can now make sense of these metrics and plan future activities for our community more efficiently.

I’d love to hear about how you extend the workflow using n8n and manage your communities! If you run into any issues while following the tutorial, feel free to reach out to me on Twitter or ask for help on our forum 🧡

---

This post originally appeared on the n8n.io blog.