Apps & Integrations

Automate Bettermode Analytics to Google Sheets Using Zapier

A Step-by-Step Guide to Automating Daily Analytics and Reporting for Your Bettermode Community

Tracking daily community metrics over time is essential for understanding growth, engagement, and trends. This guide walks you through building an automated system that pulls real-time analytics from Bettermode via GraphQL, parses the data with JavaScript, and logs it into Google Sheets using Zapier.

Whether you want to track new members, active users, page views, or top posts, this system provides a scalable, modular foundation for historical reporting and visual dashboards.

This guide walks you through building a fully automated workflow that pulls real-time analytics from your Bettermode community via the GraphQL API, processes it using JavaScript, and sends it to Google Sheets using Zapier, all on autopilot.

Even if you're new to APIs or Zapier, this step-by-step tutorial is written to help you understand the core concepts, build your first automation, and expand it over time.

Why This Automation is Powerful

  • Historical Tracking: See daily snapshots of member joins, visits, page views, or any custom metric over time.

  • Customizable: You choose the analytics you want to track. The system supports anything available via the Bettermode Analytics API.

  • Modular and Scalable: Add new metrics anytime by duplicating a Zap.

  • No-Code Friendly: With clear examples and reusable code, you don’t need to be a developer to set this up.

How Bettermode Analytics Works (Before We Begin)

The Bettermode Analytics API uses a special query language similar to SQL, which lets you retrieve metrics like:

  • Number of members joining

  • Page views or post views

  • Visits by guests or members

  • Reactions, comments, and more

You can group this data by day (group by every '1d') and filter by actor type, space, or post type.

Important:

This guide does not limit you to one type of analytics. You can define any metric you want to track using the query format. For example:

  • Want to track new members only? Use count(join) where actor_type != 'guest'

  • Want to track guest visits? Use count(visit) where actor_type = 'guest'

  • Want to track per-space engagement? Add a space filter to the query.

We’ll show you one working example in full detail (e.g., new members), and you can customize the rest from there.

What You’ll Need

  • A Bettermode site with API access (requires the API add-on)

  • Your Bettermode API key

  • A Zapier account

  • A Google Sheet where you want the data stored

  • 30–60 minutes to follow the guide

Step 1: Create Your Google Sheet

Before we start building Zaps, set up a Google Sheet with a structure like this:

| Date       | New Members | Active Members | Page Views | Top Posts |

  • Each row will represent a single day.

  • Each column will represent one metric.

  • You can expand this sheet anytime by adding new metric columns.

Pro Tip: Only one Zap should create new rows. All other Zaps will look up the existing row (based on the Date) and update their specific column.

Step 2: Create a Zap to Run Daily

In Zapier:

  • App: Schedule by Zapier

  • Trigger: Every Day

  • Time: Pick a consistent hour (e.g., 6:00 AM)

This ensures your analytics are pulled at the same time each day.

Step 3: Calculate the Date for Your Query

Bettermode’s API requires Unix timestamps (in milliseconds). We’ll use Zapier’s Formatter steps to calculate this.

Formatter Step 1: Subtract One Day

  • App: Formatter > Date/Time

  • Transform: Add/Subtract Time

  • Input: {{zap_meta_human_now}}

  • Expression: 1d

  • Format: YYYY-MM-DD

  • Output name: dateFormatted

This gives us the previous day’s date (so you're always logging “yesterday’s” stats).

Formatter Step 2: Convert to Unix Seconds

  • App: Formatter > Format Date

  • Input: dateFormatted

  • Format: X (Unix timestamp in seconds)

  • Output name: unixSeconds

Formatter Step 3: Convert to Milliseconds

  • App: Formatter > Numbers

  • Transform: Perform Math Operation

  • Operation: 1000

  • Input: unixSeconds

  • Output name: unixMilliseconds

We’ll use this as both the start and end of our query window — capturing one day’s worth of data.

Step 4: Make a Webhook Request to Bettermode Analytics API

Now we’ll send a custom query using Webhooks.

App: Webhooks by Zapier

Action: Custom Request

Method: POST

URL: https://api.bettermode.com

Headers:

Content-Type: application/json
authorization: bearer YOUR_API_KEY

Body (Raw, JSON format):

{
  "query": "query Analytics($queries: [String!]!) { analytics(queries: $queries) { query records { payload { key value } } } }",
  "variables": {
    "queries": [
      "select count(join) as Members timeFrame from {{unixMilliseconds}} to {{unixMilliseconds}} where network = 'your_network_id' and actor_type != 'guest' group by every '1d' in 'Asia/Dubai' order by timeBucket limit 30"
    ]
  }
}

Explanation of this query:

  • count(join): counts how many people joined

  • actor_type != 'guest': filters out guest traffic

  • group by every '1d': buckets the data by day

  • 'Asia/Dubai': defines your preferred timezone

You can replace the query string with anything from the Bettermode Analytics guide. This is the core place where customization happens.

Step 5: Parse the Webhook Response Using JavaScript

Bettermode returns data in a flat format:

12,"2025-07-15",15,"2025-07-16",...

We need to turn that into structured rows like this:

[
  {
    "Date": "2025-07-15",
    "New Members": 12
  },
  {
    "Date": "2025-07-16",
    "New Members": 15
  }
]

App: Code by Zapier

Language: JavaScript

Input Field: Date and count = response payload string

Code:

let raw = inputData["Date and count"];
if (!raw) {
  return { error: "Missing date/count string in 'Date and count'" };
}

const items = raw.split(",");
const METRICS = 1; // One metric being tracked
const pairsPerMetric = items.length / (METRICS * 2);
if (!Number.isInteger(pairsPerMetric)) {
  return { error: "Unexpected payload length – not divisible by 1 timeline." };
}

const ISO_DATE = /^\\d{4}-\\d{2}-\\d{2}$/;
let rows = [];
let lastDate = null;

for (let i = 0; i < pairsPerMetric; i++) {
  const idx = i * 2;
  const newCount = parseInt(items[idx], 10) || 0;
  const newDate = items[idx + 1]?.replace(/"/g, '').trim();
  const date = ISO_DATE.test(newDate) ? newDate : lastDate;
  lastDate = date;

  rows.push({
    "Date": date,
    "New Members": newCount // Change this label if you're tracking something else
  });
}

return { rows };

To create a Zap for another metric (like Page Views), you’d:

  • Adjust the GraphQL query

  • Update the label in the last line to: "Page Views": newCount

Step 6: Send the Results to Google Sheets

Option A: Create Row (only 1 Zap should do this)

  • App: Google Sheets → Create Spreadsheet Row

  • Map fields: Date + New Members

Option B: Update Row (for all other Zaps)

  1. Lookup Row

    • App: Google Sheets → Lookup Spreadsheet Row

    • Lookup Column: Date

    • Lookup Value: dateFormatted

    • Create row if not found: False

  2. Update Row

    • App: Google Sheets → Update Spreadsheet Row

    • Row ID: from the lookup step

    • Map only your metric field (leave others blank)

Final Output Example

| Date       | New Members | Active Members | Page Views | Top Posts |
|------------|-------------|----------------|------------|-----------|
| 2025-07-15 | 14          | 82             | 465        | 3         |
| 2025-07-16 | 18          | 91             | 502        | 5         |
| 2025-07-17 | 15          | 87             | 478        | 2         |

  • One row per day.

  • Each Zap updates one column.

  • Clean, structured data for analysis and dashboarding.

Scaling the System

Once the first Zap works:

  • Duplicate and customize it for each new metric

  • Change only the API query, JavaScript label, and column in Google Sheets

  • You can track anything the Bettermode Analytics API supports

Advanced Tips

  • Add a Delay step if rows are being updated before they’re created

  • Use a second Google Sheet for charting without affecting raw data

  • Use Zapier Storage if you need to store interim results before inserting

Related articles

Other Resources

Analytics & Reports

Contact Us

Do you still need help? Learn how to get in touch with the Bettermode Team.

Get in touch