Automating e-commerce KPI dashboards: Databox vs custom Python and Google Sheets

Most store KPI reporting starts as someone copying numbers from WooCommerce, Stripe and Search Console into a spreadsheet every Monday. It breaks the week that person is on holiday, and no two managers agree on what "revenue" means. This guide automates it properly. It defines the metrics in code, builds one daily row from all three sources, and compares the two ways to get that row in front of managers: a Python job that writes to Google Sheets, and Databox, a managed dashboard platform.

1. The short answer

2. The architecture

3. Define the metrics first

Every source API answers a slightly different question, so write the definitions down before writing code. These are the ones the script uses:

The last two business metrics are why a custom job exists at all. No single connector knows that a subscriber also buys one-off products in your store. Joining Stripe customers to WooCommerce billing emails shows what a subscriber is really worth.

4. The sync job

"""Daily e-commerce KPIs from WooCommerce, Stripe Billing and Google Search Console, written to a Databox dataset
and/or a Google Sheet. One row per day; each run rewrites the last DAYS days, because Search Console data arrives
two to three days late and refunds change past revenue.

  python kpi_sync.py                      # yesterday and the 3 days before it
  python kpi_sync.py --date 2026-10-19
  python kpi_sync.py --create-dataset     # once: makes the Databox data source + dataset, prints its id

Needs: pip install google-auth requests. Configuration is environment variables (see CONFIG below).
"""
import argparse, datetime as dt, json, os, sys, uuid

import requests

E = os.environ.get
CONFIG = dict(
    wc_url=E('WC_URL'), wc_key=E('WC_KEY'), wc_secret=E('WC_SECRET'),
    stripe_key=E('STRIPE_KEY'), stripe_api=E('STRIPE_API', 'https://api.stripe.com'),
    google_key_file=E('GOOGLE_KEY_FILE'), gsc_site=E('GSC_SITE'),  # e.g. sc-domain:shop.example
    gsc_api=E('GSC_API', 'https://www.googleapis.com'), sheets_api=E('SHEETS_API', 'https://sheets.googleapis.com'),
    sheet_id=E('SHEET_ID'), sheet_tab=E('SHEET_TAB', 'KPIs'),
    databox_key=E('DATABOX_KEY'), databox_dataset=E('DATABOX_DATASET'), databox_api=E('DATABOX_API', 'https://api.databox.com'),
)
DAYS = 4
COLUMNS = ['date', 'woo_revenue', 'woo_orders', 'woo_aov', 'woo_new_customers', 'stripe_mrr', 'stripe_active_subs',
           'stripe_churn_30d', 'stripe_ltv', 'subscriber_store_revenue_30d', 'blended_ltv',
           'gsc_clicks', 'gsc_impressions', 'gsc_ctr', 'gsc_position']
PAID = 'processing,completed'
http = requests.Session()


def get(url, **kw):
    r = http.get(url, timeout=60, **kw)
    r.raise_for_status()
    return r


# ---------- WooCommerce ----------
def woo_orders(start, end):
    """Paid orders created in [start, end) (UTC dates), all pages."""
    orders, page = [], 1
    while True:
        r = get(f"{CONFIG['wc_url']}/wp-json/wc/v3/orders", auth=(CONFIG['wc_key'], CONFIG['wc_secret']), params={
            'status': PAID, 'after': f'{start}T00:00:00', 'before': f'{end}T00:00:00', 'dates_are_gmt': 'true',
            'per_page': 100, 'page': page, '_fields': 'id,total,refunds,date_created_gmt,billing'})
        orders += r.json()
        if page >= int(r.headers.get('X-WP-TotalPages', 1)):
            return orders
        page += 1


def net_total(order):
    return float(order['total']) + sum(float(r['total']) for r in order.get('refunds', []))  # refund totals are negative


def first_order_emails(emails, before):
    """Which of these emails had a paid order before `before`? One search per email: fine for a day's customers."""
    seen = set()
    for email in emails:
        r = get(f"{CONFIG['wc_url']}/wp-json/wc/v3/orders", auth=(CONFIG['wc_key'], CONFIG['wc_secret']), params={
            'search': email, 'status': PAID, 'before': f'{before}T00:00:00', 'dates_are_gmt': 'true',
            'per_page': 1, '_fields': 'id,billing'})
        if any(o['billing']['email'].lower() == email for o in r.json()):
            seen.add(email)
    return seen


# ---------- Stripe ----------
def stripe_subscriptions():
    subs, after = [], None
    while True:
        params = {'status': 'all', 'limit': 100, 'expand[]': 'data.customer'}
        if after:
            params['starting_after'] = after
        page = get(f"{CONFIG['stripe_api']}/v1/subscriptions", params=params,
                   headers={'Authorization': f"Bearer {CONFIG['stripe_key']}"}).json()
        subs += page['data']
        if not page['has_more']:
            return [s for s in subs if s['status'] not in ('incomplete', 'incomplete_expired')]  # never paid
        after = page['data'][-1]['id']


MONTHS = {'day': 12 / 365, 'week': 12 / 52, 'month': 1, 'year': 12}


def monthly_cents(sub):
    # ponytail: list price x quantity; coupons and multiple currencies are ignored. Use invoices if you need net MRR.
    return sum(i['price']['unit_amount'] * i.get('quantity', 1)
               / (MONTHS[i['price']['recurring']['interval']] * i['price']['recurring']['interval_count'])
               for i in sub['items']['data'])


def live_at(sub, ts):
    """Was this subscription paying at timestamp ts? (started, not in trial, not ended)"""
    return (sub['start_date'] <= ts and (sub.get('ended_at') or float('inf')) > ts
            and not (sub.get('trial_end') and sub['trial_end'] > ts))


def stripe_metrics(subs, day):
    end = int(dt.datetime.combine(day + dt.timedelta(days=1), dt.time(), dt.timezone.utc).timestamp())
    start = end - 30 * 86400
    live = [s for s in subs if live_at(s, end)]
    base = [s for s in subs if live_at(s, start)]
    churned = [s for s in base if s.get('ended_at') and start < s['ended_at'] <= end]
    mrr = sum(monthly_cents(s) for s in live) / 100
    churn = len(churned) / len(base) if base else None
    arpu = mrr / len(live) if live else 0
    return {'stripe_mrr': round(mrr, 2), 'stripe_active_subs': len(live),
            'stripe_churn_30d': round(churn, 4) if churn is not None else None,
            'stripe_ltv': round(arpu / churn, 2) if churn else None,
            'emails': {(s['customer'].get('email') or '').lower() for s in live} - {''}}


# ---------- Google ----------
def google_token():
    from google.oauth2 import service_account
    from google.auth.transport.requests import Request
    creds = service_account.Credentials.from_service_account_file(CONFIG['google_key_file'], scopes=[
        'https://www.googleapis.com/auth/webmasters.readonly', 'https://www.googleapis.com/auth/spreadsheets'])
    creds.refresh(Request())
    return {'Authorization': f'Bearer {creds.token}'}


def gsc_by_day(auth, start, end):
    site = requests.utils.quote(CONFIG['gsc_site'], safe='')
    r = http.post(f"{CONFIG['gsc_api']}/webmasters/v3/sites/{site}/searchAnalytics/query", headers=auth, timeout=60,
                  json={'startDate': str(start), 'endDate': str(end), 'dimensions': ['date'], 'type': 'web'})
    r.raise_for_status()
    return {row['keys'][0]: {'gsc_clicks': row['clicks'], 'gsc_impressions': row['impressions'],
                             'gsc_ctr': round(row['ctr'], 4), 'gsc_position': round(row['position'], 2)}
            for row in r.json().get('rows', [])}


# ---------- rows ----------
def build_rows(last_day):
    days = [last_day - dt.timedelta(days=i) for i in range(DAYS - 1, -1, -1)]
    orders = woo_orders(days[0] - dt.timedelta(days=30), last_day + dt.timedelta(days=1)) if CONFIG['wc_url'] else []
    subs = stripe_subscriptions() if CONFIG['stripe_key'] else []
    auth = google_token() if CONFIG['google_key_file'] else None
    gsc = gsc_by_day(auth, days[0], last_day) if auth and CONFIG['gsc_site'] else {}
    rows = []
    for day in days:
        todays = [o for o in orders if o['date_created_gmt'][:10] == str(day)]
        net = sum(map(net_total, todays))
        emails = {o['billing']['email'].lower() for o in todays if o['billing'].get('email')}
        returning = first_order_emails(emails, day) if emails else set()
        row = {'date': str(day), 'woo_revenue': round(net, 2), 'woo_orders': len(todays),
               'woo_aov': round(net / len(todays), 2) if todays else None, 'woo_new_customers': len(emails - returning)}
        if subs:
            s = stripe_metrics(subs, day)
            subscribers = s.pop('emails')
            window = [o for o in orders if str(day - dt.timedelta(days=29)) <= o['date_created_gmt'][:10] <= str(day)]
            store = sum(net_total(o) for o in window
                        if o['billing'].get('email', '').lower() in subscribers)
            row.update(s, subscriber_store_revenue_30d=round(store, 2))
            # Lifetime store spend of a subscriber: their monthly store spend x expected lifetime in months
            if s['stripe_ltv'] and s['stripe_active_subs']:
                row['blended_ltv'] = round(s['stripe_ltv'] + store / s['stripe_active_subs'] / s['stripe_churn_30d'], 2)
        row.update(gsc.get(str(day), {}))
        rows.append({c: row.get(c) for c in COLUMNS})
    return rows, auth


# ---------- sinks ----------
def to_databox(rows):
    h = {'x-api-key': CONFIG['databox_key'], 'Content-Type': 'application/json'}
    body = {'records': [dict(r, date=r['date'] + 'T00:00:00+00:00') for r in rows]}
    # Same rows -> same key: a retry within 24 hours is answered from Databox's cache instead of ingested twice
    key = str(uuid.uuid5(uuid.NAMESPACE_URL, CONFIG['databox_dataset'] + json.dumps(body, sort_keys=True)))
    r = http.post(f"{CONFIG['databox_api']}/v2/datasets/{CONFIG['databox_dataset']}/data", json=body, timeout=60,
                  headers=dict(h, **{'Idempotency-Key': key}))
    if r.status_code >= 400:
        sys.exit(f'Databox {r.status_code}: {r.text[:300]}')
    return r.json()['data']['ingestionId']


def create_dataset():
    h = {'x-api-key': CONFIG['databox_key']}
    src = http.post(f"{CONFIG['databox_api']}/v2/data-sources", headers=h, timeout=60,
                    json={'name': 'Store KPIs', 'timezone': 'UTC'})
    src.raise_for_status()
    schema = [{'id': c, 'dataType': 'datetime' if c == 'date' else 'number'} for c in COLUMNS]
    ds = http.post(f"{CONFIG['databox_api']}/v2/datasets", headers=h, timeout=60, json={
        'name': 'Daily KPIs', 'dataSourceId': src.json()['data']['id'], 'primaryKey': ['date'], 'schema': schema})
    ds.raise_for_status()
    print('DATABOX_DATASET=%s' % ds.json()['data']['id'])


def to_sheet(rows, auth):
    base = f"{CONFIG['sheets_api']}/v4/spreadsheets/{CONFIG['sheet_id']}/values"
    tab = CONFIG['sheet_tab']
    existing = get(f'{base}/{tab}!A:A', headers=auth).json().get('values', [])
    where = {v[0]: i + 1 for i, v in enumerate(existing) if v}  # date -> 1-based row number
    updates, new = [], [] if existing else [COLUMNS]
    for r in rows:
        values = ['' if r[c] is None else r[c] for c in COLUMNS]
        if r['date'] in where:  # rewrite the day in place, so reruns never duplicate rows
            updates.append({'range': f"{tab}!A{where[r['date']]}", 'values': [values]})
        else:
            new.append(values)
    if updates:
        http.post(f'{base}:batchUpdate', headers=auth, timeout=60,
                  json={'valueInputOption': 'RAW', 'data': updates}).raise_for_status()
    if new:
        http.post(f'{base}/{tab}!A1:append', headers=auth, timeout=60, params={'valueInputOption': 'RAW'},
                  json={'values': new}).raise_for_status()
    return len(updates), len(new)


def main(argv=None):
    p = argparse.ArgumentParser()
    p.add_argument('--date', type=dt.date.fromisoformat, default=dt.datetime.now(dt.timezone.utc).date() - dt.timedelta(days=1))
    p.add_argument('--create-dataset', action='store_true')
    a = p.parse_args(argv)
    if a.create_dataset:
        return create_dataset()
    rows, auth = build_rows(a.date)
    if CONFIG['databox_key'] and CONFIG['databox_dataset']:
        print('Databox ingestion', to_databox(rows))
    if CONFIG['sheet_id'] and auth:
        print('Sheet: %d rows updated, %d appended' % to_sheet(rows, auth))
    print(json.dumps(rows[-1], indent=1))


if __name__ == '__main__':
    main()

Configuration is all environment variables; put them in a file only the job's user can read:

# /opt/kpi/kpi.env
WC_URL=https://shop.example
WC_KEY=ck_xxx
WC_SECRET=cs_xxx
STRIPE_KEY=rk_live_xxx            # restricted key: read subscriptions + customers
GOOGLE_KEY_FILE=/opt/kpi/sa.json
GSC_SITE=sc-domain:shop.example
SHEET_ID=1AbC...                  # optional
DATABOX_KEY=xxx                   # optional
DATABOX_DATASET=123456

# crontab: 03:15 UTC every night
15 3 * * * cd /opt/kpi && set -a && . ./kpi.env && venv/bin/python kpi_sync.py >> kpi.log 2>&1

Setup notes:

• Google access. Create a service account, download its key, add its email as a user on the Search Console property (restricted access is enough), and share the sheet with it as an editor. The script asks for both scopes with one token.
• Databox. Create an API key under Profile, Password & Security (admins only), then run python kpi_sync.py --create-dataset once. It creates a data source and a dataset whose schema is the column list, with date as the primary key, and prints the dataset ID. API ingestion is included on Databox's current Analyst, Team and Agency plans.
• Stripe. A restricted key with read-only access to subscriptions and customers is all it needs.

Design choices worth knowing:

• It rewrites the last four days on every run. Search Console data usually arrives two to three days late, and refunds change past revenue. Days without Search Console data yet are left empty, not written as zero, so a chart doesn't show a fake drop.
• Retries are safe. Databox's v2 ingestion accepts an Idempotency-Key and answers a repeat within 24 hours from cache. The script derives the key from the rows themselves, so re-running with unchanged data is a no-op. In the sheet, rows are found by date and overwritten, never appended twice.
• Check how Databox treats a re-sent date. Databox's docs don't say whether a record with an existing primary key replaces the old row. After your first rerun, compare a day's values in the dataset view, and look at GET /v2/datasets/{id}/ingestions. If rows are duplicated, purge the dataset and send only closed days.
• Don't use Databox's old Push API. push.databox.com (v0) is deprecated and no longer accepts new data sources. v1 still runs, but new features land only in v2.
• Limits. Databox allows 10,000 requests an hour, 10 per second and 500 rows or 10 MB per request; four daily rows are nowhere near. The WooCommerce returning-customer check makes one search per buyer email, which is fine for hundreds of orders a day; past that, keep a table of first-order dates instead.

5. Google Sheets: what the free path costs

A sheet is a perfectly good destination for a small team: it's free, everyone can open it, and a chart on top takes minutes. The costs show up over time:

• Silent failures. If the cron job fails (an expired key, an API change, a full disk), the sheet simply stops updating and nobody notices until a meeting. Add a heartbeat check, such as a cron monitor that expects a ping after each run.
• No goals or alerts. Thresholds ("MRR dropped 5% week over week") are formulas and conditional formatting you build and maintain.
• Everything is your code. Every new metric is a schema change in the script and the sheet.

6. Databox: what the managed path buys

• Native connectors for WooCommerce, Stripe, Google Search Console, Google Sheets and Shopify, among many others, with prebuilt metrics. For standard numbers (revenue, orders, clicks) you don't write code at all.
• Hourly sync on paid plans (daily on Free), goals, alerts, scheduled reports, and dashboards for different audiences.
• Datasets for your own data. The v2 API ingests rows into a dataset with a typed schema, and you build metrics from its columns in the app.

Monthly billing costs about 20% more (the FAQ lists $249 and $399 for the Team plans). An Agency plan with client account packs is also available, and paid plans have a 14-day trial without a card (pricing page, October 2026).

7. Test results

8. Our opinion: trade-offs

• Use Databox's connectors for everything standard, and push only the metrics that need a join. That keeps your code to one small script.
• Start with Sheets if the budget is zero, but add a heartbeat monitor on day one; a dashboard that is silently stale is worse than none.
• Write the definitions down in code. Whether revenue is net of refunds and which day an order belongs to matter more than which tool draws the chart.
• Feed it from a ledger when you can. If you record paid orders server-side (see the related analytics guide), revenue in the dashboard matches the bank, not the browser.

Related: privacy-first WooCommerce analytics with a server-side purchase ledger and building an affiliate program on Stripe subscriptions.

9. Tools

Top pick

Tool	Role	Why it fits
Databox	Managed KPI dashboards	Native WooCommerce, Stripe and Search Console connectors with hourly sync, goals and alerts, plus a v2 dataset API for the custom metrics this script computes. Free plan, and 14-day trials on paid plans.

Alternatives: free tools and references

We don't earn anything from these links.

• Metabase: open-source BI you host yourself; point it at a database the script writes to.
• Grafana: open-source dashboards and alerting, strongest for time series.
• Looker Studio: Google's free dashboards on top of a Google Sheet or Search Console.
• Databox API v2 docs: datasets, ingestion and limits.

10. Pitfalls

• Writing zero for missing Search Console days, which draws a fake traffic crash every morning.
• Counting gross revenue, so refunds never show up in the dashboard.
• MRR from list prices when you discount heavily. Switch to invoice amounts if coupons are common.
• Building on Databox's deprecated Push API instead of v2 datasets.
• A cron job nobody watches. Monitor it like any other production job.
• Mixing timezones. The script uses UTC days; set Databox's data source timezone to match, or convert in one place.