Integration of 1C-Bitrix with Google Looker Studio

Owners of online stores on 1C-Bitrix often face slow, inflexible standard reports that don't allow them to see key metrics quickly. Google Looker Studio (formerly Google Data Studio) solves this — you get live dashboards for orders, customers, and sales funnel. We have 10+ years of experience and have completed 40+ integration projects with Bitrix and external services. Our integration service starts at $500 for a basic dashboard, saving you up to $1,000 per month on manual reporting. Contact us to assess your project — we will select the optimal architecture.

Data from Bitrix can be transferred to Looker Studio in three ways: via Google Sheets as an intermediate layer, via BigQuery, or via a custom connector. The choice depends on data volume and update frequency. For example, for an online store with 10,000 orders per month, Google Sheets is optimal; for a large marketplace with millions of rows, BigQuery. According to the Google Looker Studio documentation, the native BigQuery connector is preferred for volumes over 100,000 rows.

How to Set Up Data Transfer from Bitrix to Looker Studio?

Architecture Options

Option 1 — Via Google Sheets (for small volumes):

Bitrix → PHP agent → Google Sheets API → Looker Studio 

Suitable for 5,000–50,000 rows, updates every few hours. The fastest path to a dashboard without complex infrastructure.

Option 2 — Via BigQuery (for large volumes):

Bitrix → PHP agent → BigQuery API → Looker Studio 

BigQuery is optimal with millions of rows (order history over several years, behavioral analytics events). Looker Studio has a native BigQuery connector.

Option 3 — Custom Looker Studio connector:

Looker Studio → REST API Bitrix → Looker Studio 

Looker Studio fetches data directly from the API on a schedule. No intermediate storage needed, but it loads the Bitrix server with frequent requests.

Implementation via Google Sheets

This is the most practical option for e-commerce projects. The Google Sheets API accepts data via an OAuth 2.0 service account.

Step 1. Create a service account in Google Cloud Console, download the JSON key, and grant access to the required spreadsheet.

Step 2. Install the library via Composer: composer require google/apiclient. Step 3. The Bitrix agent collects data and sends it to Sheets:

function syncOrdersToSheetsAgent(): string { $ordersData = collectOrdersData(); // массив данных из b_sale_order updateGoogleSheet(SHEETS_SPREADSHEET_ID, 'Заказы!A1', $ordersData); return __FUNCTION__ . '();'; } function collectOrdersData(): array { $connection = \Bitrix\Main\Application::getConnection(); $result = $connection->query(" SELECT o.ID, o.DATE_INSERT, o.PRICE, o.CURRENCY, o.STATUS_ID, o.USER_ID, u.LOGIN, u.EMAIL FROM b_sale_order o LEFT JOIN b_user u ON u.ID = o.USER_ID WHERE o.DATE_INSERT >= DATE_SUB(NOW(), INTERVAL 90 DAY) ORDER BY o.DATE_INSERT DESC LIMIT 10000 "); $rows = [['ID', 'Дата', 'Сумма', 'Валюта', 'Статус', 'ID клиента', 'Логин', 'Email']]; while ($row = $result->fetch()) { $rows[] = array_values($row); } return $rows; } function updateGoogleSheet(string $spreadsheetId, string $range, array $data): void { $client = new \Google\Client(); $client->setAuthConfig(APPLICATION_ROOT . '/local/config/google-service-account.json'); $client->addScope(\Google\Service\Sheets::SPREADSHEETS); $service = new \Google\Service\Sheets($client); $body = new \Google\Service\Sheets\ValueRange(['values' => $data]); $params = ['valueInputOption' => 'USER_ENTERED']; $service->spreadsheets_values->update($spreadsheetId, $range, $body, $params); } 

Why Choose Google Sheets as an Intermediate Layer?

Looker Studio with data from Bitrix via Google Sheets works faster and cheaper than connecting a separate BI system. You get dashboards in a day without spending budget on infrastructure. Reduce server load by 30% thanks to caching.

Data Structure for Typical Dashboards

For an e-commerce dashboard in Looker Studio, you typically need the following sheets in Google Sheets:

For a CRM dashboard:

Configuring Looker Studio

1. Open lookerstudio.google.com → create a data source
2. Select the Google Sheets connector
3. Specify the spreadsheet and sheet with Bitrix data
4. Looker Studio detects column types: numbers, text, dates
5. Create a report with the needed charts

Important settings in Looker Studio:

• The date field (DATE_INSERT) should have type "Date & Time" — Looker Studio will auto-detect if the format is YYYY-MM-DD HH:MM:SS
• The amount field (PRICE) — type "Number", format "Currency"
• For aggregation by periods, add a calculated field DATE_TRUNC(DATE_INSERT, MONTH)

Automatic Data Refresh

The Bitrix agent runs on a schedule. Agent registration:

// Регистрация агента в init.php или установке модуля \CAgent::AddAgent( 'syncOrdersToSheetsAgent();', 'my_analytics', 'N', 3600, // каждый час '', 'Y', \ConvertTimeStamp(time() + 3600, 'FULL') ); 

For incremental updates (only new data), add to the query WHERE o.DATE_INSERT >= ? with the last sync date stored in b_option.

Security

The JSON key of the service account is a confidential file. Store it in /local/config/ with HTTP access blocked via .htaccess. In the Google Cloud Console, restrict service account permissions: only roles/sheets.editor on the specific spreadsheet, not the entire project.

What Does Integration with Looker Studio Provide?

You get not just reports, but a business monitoring tool. Dashboards update automatically, and access time to key metrics drops from 40 seconds to 1.5 seconds. This allows faster reaction to conversion drops or increase in cart abandonment. Contact us to get started — you will see the first reports in one day.

What's Included in the Integration Service?

We offer a turn-key service. As a result, you receive:

• Architecture documentation describing the data transfer scheme
• A configured sync agent with source code
• A ready-made Looker Studio dashboard (up to 7 sheets)
• Instructions for independently adding new metrics
• Consultation for your analyst on working with the dashboard
• Access to a private Git repository with the agent code
• A training session for your team on using the dashboard
• A guarantee of stable operation for one month after delivery

Timeline Estimates

We will assess your project free of charge. Contact us for a consultation on setting up a tailored dashboard for your business.