GA4 SQLite Exporter

A Node.js tool to fetch Google Analytics 4 (GA4) data and store it in a local SQLite database. Supports:

• Custom GA4 queries
• Lowercased and filtered dimensions
• Auto-generated tables
• Large datasets across multiple GA4 properties

📦 Features

• Pulls GA4 data using a service account
• Stores results in SQLite with auto-created schemas
• Supports batching, offsets, filtering, and aggregations
• Works with advanced dimensionExpression (like lowercase)
• Easy integration with analytics dashboards or audits

🚀 Quick Start

1. Install dependencies

npm install

2. Create your GA4 service account

Follow Google’s instructions to create a service account and download the JSON key file: 📖 Set up service account

Ensure your GA4 property has granted Viewer or Analyst access to that service account email.

Place the credentials JSON in your project root (default path: analytics-service-account.json).

3. Configure your query

Create a config file like ./config/total-pageviews.json:

{
  "credentialsPath": "analytics-service-account.json",
  "query": {
    "dateRanges": { "startDate": "365daysAgo", "endDate": "yesterday" },
    "dimensions": [
      {
        "name": "pagePath_lower",
        "dimensionExpression": {
          "lowerCase": { "dimensionName": "pagePath" }
        }
      },
      {
        "name": "hostname_lower",
        "dimensionExpression": {
          "lowerCase": { "dimensionName": "hostname" }
        }
      },
      { "name": "yearWeek" }
    ],
    "metrics": [
      { "name": "screenPageViews" },
      { "name": "totalUsers" },
      { "name": "userEngagementDuration" },
      { "name": "sessions" }
    ],
    "metricAggregations": ["TOTAL"],
    "orderBys": [
      { "dimension": { "dimensionName": "yearWeek" }, "desc": false },
      { "dimension": { "dimensionName": "pagePath_lower" }, "desc": false },
      { "dimension": { "dimensionName": "hostname_lower" }, "desc": false }
    ],
    "dimensionFilter": {
      "orGroup": {
        "expressions": [
          {
            "filter": {
              "fieldName": "hostname_lower",
              "stringFilter": {
                "matchType": "CONTAINS",
                "value": ".gov",
                "caseSensitive": false
              }
            }
          },
          {
            "filter": {
              "fieldName": "hostname_lower",
              "stringFilter": {
                "matchType": "CONTAINS",
                "value": ".state.md.us",
                "caseSensitive": false
              }
            }
          }
        ]
      }
    },
    "metricFilter": {
      "andGroup": {
        "expressions": [
          {
            "filter": {
              "fieldName": "sessions",
              "numericFilter": {
                "operation": "GREATER_THAN",
                "value": { "int64Value": 1 }
              }
            }
          },
          {
            "filter": {
              "fieldName": "screenPageViews",
              "numericFilter": {
                "operation": "GREATER_THAN",
                "value": { "int64Value": 5 }
              }
            }
          }
        ]
      }
    }
  }
}

4. Run the report

node index.js

Or inside a script:

import { runGA4Report } from "@dmwd/ga4-to-sqlite";
const configPath = "./config/pageviews.json";
const dbPath = "./pageviews.db";

try {
  console.time("⏱️ Total time");
  await runGA4Report(configPath, dbPath);
  console.timeEnd("⏱️ Total time");
} catch (err) {
  console.error("❌ Failed to run GA4 report:", err);
}

📁 Output

• SQLite DB file (e.g., ./total-pageviews.db)
• Auto-created table name (inferred from config file name)
• Includes:
  • id, created_date, updated_date, property_id
  • all dimension values
  • all metric values
• fullPageUrl and fullPageUrl_lower (auto-joined from hostname + pagePath)

📚 Helpful Docs

• GA4 Dimensions & Metrics Documentation
• GA4 Dimensions & Metrics Explorer

🛠 Customize

You can modify:

• credentialsPath: to change your key location
• query: any supported GA4 requestBody
• dbPath: for output location or naming

🤝 Contributing

Pull requests welcome! Please:

• Use clear, concise commits
• Include examples or reproducible steps if submitting an issue

📄 License

MIT