Data Sync is an add-on product teams can use to move their Pi data to a SQL database or data warehouse, allowing you to integrate with other data and analyse, transform, and visualise. Its goal is to be one less obstacle to achieving a marketing reporting stack that includes all channel data.
Data Sync connects to your SQL database and then automatically adds rows of data when available straight into the selected database, completely removing the need to build API integrations with Pi data.
Where to find Data Sync
Data Sync can be found in the Data and setup area of the left-hand navigation:
What is a connection?
A connection is a destination SQL database that you wish Pi to populate with your selected data. Data Sync currently supports three SQL database vendors:
MySQL
Microsoft SQL Server
PostgreSQL
Data Sync requires the following details to connect to your Database:
Host
Database name
Database vendor
Username
Password
You also need to ensure that the username provided has the ability to write to the server from all IP addresses.
Permissions
Sync only requires read-and-write access to your database. Sync only uses SELECT and INSERT.
Server location
For the best performance, we recommend hosting the database on a European server.
What is a job?
A job is where you select the data you want to push to your connection. Historically, you can select the previous 5 tracking periods to backfill data. For example:
Daily = 5 days
Weekly = 5 weeks
Monthly = 5 Months
If you require a larger backfilling operation, contact your Customer Success Manager, who can organise this for you.
Data types
Currently, there are four data types available in Data Sync. Site position data, bulk search results, Search Volume, and Share of Voice.
Site position data will pull back all positions in the selected ranking mode for the specified workspaces and sites.
Bulk search results will pull back the top 100 results for the selected ranking mode for a single workspace.
Search Volume will bring back Google Keyword Planner data for search terms within workspaces. The first sync will come with 36 months worth of history.
Share of Voice periodically brings back the share of voice data from a Market Intelligence set up on an account.
SQL table verification
During the job setup process, Data Sync will verify there is a valid table within your database. If there isn’t a valid table, Data Sync will provide the SQL query needed to create this as below. Simply copy and paste into your database to create your table.
For better syncing performance we highly recommend hosting your database in Europe.
Syncing
After setting up a job, it will be queued to sync. Under the jobs, you can view the status of the syncs.
Learn how to add a connection and create a job, here.