> ## Documentation Index
> Fetch the complete documentation index at: /docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Export ClickFlare Data to Looker Studio, Tableau, Power BI and BigQuery

> Connect ClickFlare to BI tools: pull report data from the ClickFlare API into Looker Studio, Tableau, Power BI or BigQuery.

![BI tools and ClickFlare Integration](https://mintcdn.com/clickflare/b2lpDlx8sR69TsUK/images/integrations/covers/export-click-flare-data-to-bi-tools.png?fit=max\&auto=format\&n=b2lpDlx8sR69TsUK\&q=85\&s=39b06750e01de1ca10f95829046c98de)

ClickFlare has no native connector for Looker Studio, Tableau, Power BI or BigQuery. The route into all four is the same: the **ClickFlare REST API report endpoint**. You send it the date range, breakdowns and columns you want, and it returns report rows as JSON, the same numbers you see in ClickFlare's reports.

Data moves one way: from ClickFlare into your BI tool. Nothing in this guide changes your campaigns.

This guide covers the shared part first (API key, the report request, paging, scheduling and the columns that come back), then one section per tool with that tool's own way of loading a JSON API.

***

## At a glance

To get ClickFlare data into Looker Studio, Tableau, Power BI or BigQuery, call the ClickFlare REST API report endpoint with your API key and load the JSON rows into the tool. There is no native connector. The numbers match ClickFlare's own reports, and data only moves from ClickFlare into the BI tool.

| | BI tools + ClickFlare |
| - | - |
| **How it connects** | REST API: `POST https://public-api.clickflare.io/api/report` |
| **Authentication** | API key from **Settings → API Keys**, sent in the `api-key` header |
| **What you get** | Report rows as JSON, one per breakdown combination, plus totals |
| **Timing** | Whenever you refresh: a scheduled script, trigger or dataset refresh |
| **Works with** | Looker Studio, Power BI, Tableau, Google BigQuery, any tool that reads JSON or CSV |
| **Good to know** | Recent days keep changing as conversions arrive. Re-pull the last several days on each refresh. |

***

## How the data flows

```text theme={null}
ClickFlare reports
  ← POST https://public-api.clickflare.io/api/report
     (header api-key: YOUR_API_KEY, JSON body: dates, groupBy, metrics, paging)
  → JSON response: items[] (one row per breakdown combination) + totals
  → Your tool or script:
       Looker Studio  ← Google Sheet filled by Apps Script, or a community connector
       Power BI       ← Power Query Web.Contents (POST)
       Tableau        ← CSV file or database written by a scheduled script
       BigQuery       ← ndJSON file in Cloud Storage → load job
```

| Tool | How it calls ClickFlare | Who runs the refresh | Effort |
| - | - | - | - |
| **Looker Studio** | Apps Script (`UrlFetchApp`) | A time-driven trigger, or the connector on report load | Low with a Sheet |
| **Power BI** | `Web.Contents` with `Content` | Dataset refresh | Low |
| **Tableau** | A script you host | Your scheduler, then an extract refresh | Medium |
| **BigQuery** | A script you host | Your scheduler, then a load job | Medium |

> [!NOTE]
>
> Google renamed Looker Studio back to **Data Studio** in April 2026. The product URL is now `datastudio.google.com`, and `lookerstudio.google.com` redirects to it. Existing reports keep working. This guide uses both names.

***

## What you'll need

* A ClickFlare **API key**: **Settings → API Keys**. See [API overview](/docs/en/api/clickflare-api-overview.md).
* An idea of the report you want: which breakdowns (campaign, offer, country, date…) and which numbers (visits, conversions, revenue, cost…).
* For Tableau and BigQuery: somewhere to run a scheduled script, such as a server, a cloud function or an existing job scheduler.

> [!WARNING]
>
> The API key has the same permissions as your ClickFlare account: anyone holding it can read and change your campaigns. Keep it in a script's server-side settings or in a credential store. Never put it in a shared report, a workbook, a Sheet cell or client-side code.

***

## The report endpoint

All four routes send the same request:

```bash theme={null}
curl -X POST 'https://public-api.clickflare.io/api/report' \
  -H 'Content-Type: application/json' \
  -H 'api-key: YOUR_API_KEY' \
  --data '{
    "startDate": "2026-10-01 00:00:00",
    "endDate": "2026-10-31 23:59:59",
    "timezone": "UTC",
    "currency": "USD",
    "groupBy": ["date", "campaignID"],
    "metrics": ["date", "campaignID", "campaignName", "visits", "clicks",
                "conversions", "revenue", "cost", "profit", "roi"],
    "sortBy": "date",
    "orderType": "asc",
    "page": 1,
    "pageSize": 10000,
    "includeAll": false,
    "conversionTimestamp": "visit"
  }'
```

Run this once from a terminal before you build anything in a BI tool. If it returns rows, the rest is plumbing.

### Request options

Required fields:

| Field | What it does |
| - | - |
| `startDate`, `endDate` | The date range, formatted `YYYY-MM-DD HH:mm:ss` |
| `timezone` | The time zone the dates and the `date` breakdown use, for example `UTC` or `America/New_York` |
| `groupBy` | 1 to 10 breakdowns. You can't repeat one. More breakdowns means more rows. |
| `metrics` | The columns to return. **Every breakdown in `groupBy` must also be in `metrics`.** Names such as `campaignName` or `offerUrl` go here too. |
| `sortBy` | The column to sort by. It must be in `metrics`. |
| `orderType` | `asc` or `desc` |
| `page` | The page number, 1 to 1,000 |
| `pageSize` | Rows per page, up to 50,000 |
| `includeAll` | `true` returns rows with zero statistics too; `false` returns only rows with data |

Optional fields:

| Field | What it does |
| - | - |
| `currency` | The currency for money columns. USD by default. Converted at the rate of the day you run the report. |
| `conversionTimestamp` | `visit` dates conversions by the visit that led to them; `postback` dates them by when they reached ClickFlare |
| `metricsFilters` | Filters rows. Each filter has a `name`, an `operator` (`=`, `>`, `<`, `>=`, `<=`, `in`, `not_in`, `like`, `match_in`, `not_match_in`) and a `value` |
| `workspace_ids` | Limits the report to these workspaces. Leave it out or empty for all. The public workspace is always included. |
| `search` | Full-text search |
| `compare` | Metrics to compare with the previous period of the same length |

### Breakdowns you can group by

The most useful `groupBy` values for BI work:

| Group | Values |
| - | - |
| Time | `date`, `dateTime`, `hourOfDay`, `dayOfWeek`, `startOfMonth` |
| Campaign elements | `campaignID`, `offerID`, `landingID`, `trafficSourceID`, `affiliateNetworkID`, `flowID` |
| Traffic source entities | `ts_entity_level_campaign`, `ts_entity_level_adset`, `ts_entity_level_ad`, `ts_entity_level_site`, and others |
| Visitor | `locationCountry`, `locationRegion`, `locationCity`, `deviceType`, `deviceOS`, `deviceBrowser`, `connectionISP` |
| Your own values | `trackingField1`…`trackingField20`, `param1`…`param20` |

IDs are stable; names can change. **Group by the ID and add the name to `metrics`**, for example `campaignID` in `groupBy` and both `campaignID` and `campaignName` in `metrics`. The same works for `offerName`, `landingName`, `trafficSourceName` and `affiliateNetworkName`.

### Columns that come back

The response has an `items` array with one object per row, and a `totals` object. An example, shortened:

```json theme={null}
{
  "items": [
    { "date": "2026-10-01", "campaignID": "…", "campaignName": "US - Search",
      "visits": 1840, "clicks": 912, "conversions": 37,
      "revenue": 412.5, "cost": 301.2, "profit": 111.3, "roi": 36.95 }
  ],
  "totals": { "visits": 1840, "conversions": 37, "revenue": 412.5 }
}
```

Each row holds the columns you listed in `metrics`, under the same names. Common numeric columns: `visits`, `uniqueVisits`, `clicks`, `uniqueClicks`, `conversions`, `revenue`, `cost`, `profit`, `roi`, `roas`, `ctr`, `cvr`, `epc`, `epv`, `cpa`, `cpv`. Custom conversions come back as `customConversion1`…`customConversion20` and their revenue as `customRevenue1`…`customRevenue20`. Custom metric columns start with `cm_`.

> [!TIP]
>
> Load counts and money (`visits`, `conversions`, `revenue`, `cost`) and rebuild ratios like ROI, CVR and EPC as calculated fields in your BI tool. A ratio summed across rows is wrong; a ratio of sums is right.

### Paging

Start at `page: 1` and keep going while a page comes back full. When a page returns fewer rows than `pageSize`, you have everything. One request can cover at most 1,000 pages of 50,000 rows. If you need more, split the date range.

### Scheduling and re-pulling

ClickFlare answers each request with the data as it is at that moment. Two things change after the fact:

* **Conversions arrive late.** A postback can come in days after the click. With `conversionTimestamp: "visit"`, that conversion lands on the day of the visit, so past days keep changing.
* **Cost can update.** Cost integrations and manual cost uploads can change earlier days.

So don't append only "yesterday". On each run, **re-pull the last several days and overwrite them** in your destination. Keep `timezone`, `currency` and `conversionTimestamp` the same on every run, or days won't line up.

***

## Looker Studio (Data Studio)

Data Studio reads Google Sheets natively, and Apps Script can call any HTTP API with `UrlFetchApp`. There are two ways to combine them.

### Option A: Google Sheet filled by Apps Script

The simplest route. A script pulls the report into a sheet on a schedule, and Data Studio reads the sheet.

**1. Create the sheet and the script**

Create a Google Sheet with a tab named `ClickFlare`. Open **Extensions → Apps Script**.

**2. Store the API key**

In the Apps Script project settings, add a **Script property** named `CLICKFLARE_API_KEY` with your key. The key then stays out of the sheet.

**3. Paste the script**

```js theme={null}
function pullClickFlare() {
  const apiKey = PropertiesService.getScriptProperties().getProperty('CLICKFLARE_API_KEY');
  const tz = 'UTC';
  const end = new Date();
  const start = new Date(end.getTime() - 30 * 24 * 3600 * 1000);
  const fmt = d => Utilities.formatDate(d, tz, 'yyyy-MM-dd');

  const metrics = ['date', 'campaignID', 'campaignName', 'visits', 'clicks',
                   'conversions', 'revenue', 'cost', 'profit'];
  const body = {
    startDate: fmt(start) + ' 00:00:00',
    endDate: fmt(end) + ' 23:59:59',
    timezone: tz,
    groupBy: ['date', 'campaignID'],
    metrics: metrics,
    sortBy: 'date',
    orderType: 'asc',
    pageSize: 10000,
    includeAll: false,
    conversionTimestamp: 'visit',
  };

  const rows = [];
  for (let page = 1; page <= 1000; page++) {
    body.page = page;
    const res = UrlFetchApp.fetch('https://public-api.clickflare.io/api/report', {
      method: 'post',
      contentType: 'application/json',
      headers: { 'api-key': apiKey },
      payload: JSON.stringify(body),
    });
    const items = JSON.parse(res.getContentText()).items || [];
    items.forEach(r => rows.push(metrics.map(m => r[m] ?? '')));
    if (items.length < body.pageSize) break;
  }

  const sheet = SpreadsheetApp.getActive().getSheetByName('ClickFlare');
  sheet.clearContents();
  sheet.getRange(1, 1, 1, metrics.length).setValues([metrics]);
  if (rows.length) sheet.getRange(2, 1, rows.length, metrics.length).setValues(rows);
}
```

The script rewrites the last 30 days on every run, so late conversions are picked up.

**4. Schedule it**

Run `pullClickFlare` once by hand and approve the permissions. Then add a **time-driven trigger** for it, for example every hour or once a day.

**5. Connect Data Studio**

In Data Studio, add a data source with the **Google Sheets** connector, pick the sheet and the `ClickFlare` tab. Set `date` as a date field and the money columns as currency.

### Option B: a community connector

A community connector is an Apps Script project that Data Studio calls directly when a report loads. You write four functions: `getAuthType()`, `getConfig()`, `getSchema()` and `getData()`. In `getData()`, send the same POST as above with `UrlFetchApp`, using the date range Data Studio passes in `request.dateRange`, and map the `items` to your schema. Google's **Build a Community Connector** guide walks through the full structure.

Pick a connector over a Sheet when you want live date ranges in the report, or want to share the connector with other people. Keep the API key in script properties, never in the connector config that viewers fill in.

***

## Power BI

Power Query's `Web.Contents` sends a POST when you give it a `Content` option. Microsoft notes that POST requests can only be made with **Anonymous** access, so the ClickFlare key goes in the request headers, not in Power BI's credentials.

**1. Add the API key as a parameter**

In Power BI Desktop, open **Transform data** and create a text parameter named `ApiKey` with your ClickFlare key.

**2. Create a blank query**

Add a **Blank Query**, open the **Advanced Editor** and paste:

```text theme={null}
let
    Body = [
        startDate = "2026-10-01 00:00:00",
        endDate = "2026-10-31 23:59:59",
        timezone = "UTC",
        groupBy = {"date", "campaignID"},
        metrics = {"date", "campaignID", "campaignName", "visits", "clicks",
                   "conversions", "revenue", "cost", "profit"},
        sortBy = "date",
        orderType = "asc",
        page = 1,
        pageSize = 50000,
        includeAll = false,
        conversionTimestamp = "visit"
    ],
    Response = Web.Contents(
        "https://public-api.clickflare.io/api/report",
        [
            Headers = [#"Content-Type" = "application/json", #"api-key" = ApiKey],
            Content = Json.FromValue(Body),
            Timeout = #duration(0, 0, 5, 0)
        ]
    ),
    Items = Json.Document(Response)[items],
    Result = Table.FromRecords(Items)
in
    Result
```

**3. Choose Anonymous access**

When Power BI asks how to connect to `public-api.clickflare.io`, choose **Anonymous**.

**4. Set types and load**

Set `date` to Date and the money columns to Decimal number, then **Close & Apply**. Refresh the dataset to pull the latest numbers.

Things to know:

* `Web.Contents` times out after 100 seconds by default. The query above raises it to 5 minutes with `Timeout`. For bigger reports, use a smaller date range or more pages.
* For more than one page, turn the query into a function of `page` and combine the results with `List.Generate`, stopping when a page comes back short.
* To make the dates dynamic, build `startDate` and `endDate` from `DateTime.LocalNow()` with `DateTime.ToText`.
* Scheduled refresh in the Power BI service for this query hasn't been tested here. Publish it and run one refresh in the service before you rely on it. If it fails, load the data through BigQuery or a file instead: Power BI reads both.

***

## Tableau

Tableau has no direct route for this endpoint today:

* **Web Data Connectors** 2.x and 3.0 are deprecated, and Tableau says they'll be removed with the Tableau 26.3 release. Don't start a new one.
* Tableau's replacement, the **REST API Connector**, supports JSON and CSV over **HTTP GET**. ClickFlare's report endpoint is a POST with a JSON body, so it doesn't fit.

The dependable route is a **staging script**: it pulls the report on a schedule and writes it somewhere Tableau already reads, such as a CSV file, a database, or BigQuery.

**1. Write the pull script**

A Python example that writes a CSV:

```python theme={null}
import csv, os, requests
from datetime import date, timedelta

API = "https://public-api.clickflare.io/api/report"
KEY = os.environ["CLICKFLARE_API_KEY"]
METRICS = ["date", "campaignID", "campaignName", "trafficSourceName",
           "visits", "clicks", "conversions", "revenue", "cost", "profit"]

def pull(start, end, page_size=50000):
    body = {
        "startDate": f"{start} 00:00:00", "endDate": f"{end} 23:59:59",
        "timezone": "UTC", "groupBy": ["date", "campaignID"],
        "metrics": METRICS, "sortBy": "date", "orderType": "asc",
        "pageSize": page_size, "includeAll": False,
        "conversionTimestamp": "visit",
    }
    for page in range(1, 1001):
        body["page"] = page
        r = requests.post(API, json=body, headers={"api-key": KEY}, timeout=300)
        r.raise_for_status()
        items = r.json().get("items", [])
        yield from items
        if len(items) < page_size:
            break

end = date.today()
start = end - timedelta(days=30)
with open("clickflare.csv", "w", newline="") as f:
    w = csv.DictWriter(f, fieldnames=METRICS, extrasaction="ignore")
    w.writeheader()
    w.writerows(pull(start, end))
```

**2. Schedule it**

Run it with the scheduler you already use: cron, a cloud scheduler, or your data team's orchestrator. Keep the key in an environment variable or secret store.

**3. Connect Tableau**

Point Tableau at the CSV (as a text file) or at the database table the script writes. Create an extract and refresh it after each script run.

If you also load ClickFlare into BigQuery, connect Tableau to BigQuery instead and skip the file.

***

## Google BigQuery

BigQuery loads JSON from Cloud Storage only as **newline-delimited JSON** (ndJSON): one object per line. ClickFlare's `items` are already one object per row, so the script writes each item on its own line.

**1. Prepare Google Cloud**

Create a BigQuery dataset and a Cloud Storage bucket. Give the identity that runs the job permission to write to the bucket and to run BigQuery load jobs.

**2. Write ndJSON**

Reuse the `pull()` function from the [Tableau](#tableau) section and write one line per row:

```python theme={null}
import json
with open("clickflare.ndjson", "w") as f:
    for row in pull(start, end):
        f.write(json.dumps(row) + "\n")
```

Upload the file to your bucket, for example `gs://your-bucket/clickflare/2026-10-31.ndjson`.

**3. Load it**

Load with `bq load` and `NEWLINE_DELIMITED_JSON`:

```bash theme={null}
bq load \
  --source_format=NEWLINE_DELIMITED_JSON \
  --autodetect \
  --replace \
  your_dataset.clickflare_daily \
  gs://your-bucket/clickflare/2026-10-31.ndjson
```

`--replace` overwrites the table with the re-pulled window. For a long history, write each day to its own partition instead of replacing the whole table, or load into a staging table and `MERGE` on `date` and `campaignID`. Once the table exists, supply a fixed schema instead of `--autodetect` so column types don't drift.

**4. Schedule it**

Run the pull and upload on your scheduler. Google recommends the **BigQuery Data Transfer Service** for scheduled loads from Cloud Storage, so the load step can run there on its own schedule.

Once the data is in BigQuery, Data Studio, Tableau and Power BI all have BigQuery connectors. Many teams load once into BigQuery and point every BI tool at it.

***

## Verify the export

**1. Compare one day**

Pick one finished day. Open the same report in ClickFlare with the same breakdowns, time zone and currency, and compare visits, conversions, revenue and cost with your BI tool.

**2. Check the conversion date setting**

If conversions differ, check `conversionTimestamp` against **Settings → General Settings → Conversion registration time reporting** in your account. Use the same choice in the request: `visit` for **Visit Timestamp**, `postback` for **Postback Timestamp**.

**3. Check paging**

Compare the row count in your destination with a ClickFlare report of the same range. Fewer rows usually means the loop stopped after the first page.

***

## Troubleshooting

> [accordion] 401 unauthorized
>
> The `api-key` header is missing or the key is wrong. The header name is `api-key`, not `Authorization`.

> [accordion] The request is rejected
>
> * A field in `groupBy` is missing from `metrics`, or a breakdown appears twice.
> * `sortBy` names a column that isn't in `metrics`.
> * Dates aren't in `YYYY-MM-DD HH:mm:ss` format.
> * A required field is missing: `startDate`, `endDate`, `timezone`, `groupBy`, `metrics`, `sortBy`, `orderType`, `page`, `pageSize`, `includeAll`.

> [accordion] Numbers don't match ClickFlare
>
> Check `timezone`, `currency` and `conversionTimestamp` against the report you compare with. Then check you rebuilt ratios from sums instead of summing ROI or CVR.

> [accordion] The request times out
>
> Use fewer breakdowns, a shorter date range or a smaller `pageSize`, and raise your client's timeout. Breakdowns such as `clickID` or `visitID` return one row per click or visit.

> [accordion] Yesterday's conversions keep changing
>
> That's expected with `conversionTimestamp: "visit"`. Re-pull and overwrite the last several days on every run.

***

## FAQ

> [accordion] How do I connect ClickFlare to Looker Studio?
>
> Use Apps Script to call the ClickFlare report endpoint and write the rows into a Google Sheet on a time-driven trigger, then use that Sheet as the Looker Studio data source. See [Looker Studio](#looker-studio-data-studio).

> [accordion] How do I connect ClickFlare to Power BI?
>
> In Power Query, call the report endpoint with `Web.Contents`, sending the JSON body as `Content` and your key in the `api-key` header. Dataset refresh then pulls new data. See [Power BI](#power-bi).

> [accordion] How do I get ClickFlare data into Tableau or BigQuery?
>
> Run a scheduled script that calls the report endpoint. For Tableau, write the rows to a CSV file or a database Tableau reads. For BigQuery, write newline-delimited JSON to Cloud Storage and load it with a load job. See [Tableau](#tableau) and [Google BigQuery](#google-bigquery).

> [accordion] Is there a native connector for any of these tools?
>
> No. The REST API report endpoint is the supported route for all four.

> [accordion] Can I export raw clicks or conversions?
>
> You can group by `clickID` or `visitID` to get one row per click or visit. These reports get large fast, so page through them and keep the date range short.

> [accordion] Can I limit the export to one client's workspace?
>
> Yes. Pass `workspace_ids` with that workspace's ID. The public workspace is always included.

> [accordion] Can I push data from ClickFlare instead of pulling it?
>
> For single events, yes: the [Custom Webhook integration](/docs/en/integrations/custom-webhook-integration.md) sends visits, clicks and conversions to any URL as they happen. For report totals and cost, pull from the API.

> [accordion] Can an AI assistant query the same data?
>
> Yes. The [ClickFlare MCP server](/docs/en/advanced-features/click-flare-ai-integration-mcp.md) lets an AI assistant read your reports in plain language. It's for questions, not for scheduled exports.

***

## Related

> [card] [API overview](/docs/en/api/clickflare-api-overview.md)
>
> Base URL and API keys

> [card] [Custom webhooks](/docs/en/integrations/custom-webhook-integration.md)
>
> Push events out

> [card] [MCP server](/docs/en/advanced-features/click-flare-ai-integration-mcp.md)
>
> Ask your data


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.