How to Mirror Prod Database to PostgreSQL Database

Max Classic
1

Hey everyone, I am working on building a solution which involves mirroring the production instance of Keap Max Classic to a PostgreSQL database on an hourly cadence for SQL data mining. Does anyone have guidance on systems or tools which are designed for this process?

2

Hi Andrew,

Novak Solutions have built a Data Warehouse solution for Keap Max Classic (Infusionsoft) which has been around for several years now. It stores the data in a MySQL Database.

(UPDATED - 2022-11-09 - Unknown Status of this Product)
https://pages.novaksolutions.com/data-warehouse/

If that is not a solution, then you will need to have one developed for you as PostegreSQL is not commonly used here. I understand it is a capable database, but it will be harder to find developers with knowledge about it here. In principle the SQL would be mostly the same as MySQL, so it is the job of understanding how the database connection works, its tools, etc.

The process of mirroring the data is quite simple. You need to create the Database Tables that match the data being returned from Keap API. Then have an automated job (Cron Job) that pulls the data every so often during the day. Once the data is in the Database it makes it far easier to query things.

Hope that helps.

3

Thanks, Pav. I reached out to Novak by phone and email. I did not get a response either way.

I also attempted to inset the Contacts (Exported) into a MySQL database and found there is a byte limit with attempting to use a flat table.

I have found ETL pipelines, such as HevoData, to make the API calls for us, however, I ran into one huge issue where the Keap API does not return the list of tags on a Contact if I call the Contacts in bulk. This only works if the contact is pulled individually. Given out 100k+ contacts, this would be very resource intensive.

For reference, Keap blocks the tags on a mass Contact pull because of resource issues on their end.

4

In response to what you have mentioned about Novak Solutions, I have updated my original reply as that has become an unknown about the product. It was created about 5+ years ago.

I have attached Novak’s Database Schema SQL file for reference, although they have designed it for their own product, so it does not match entries from the API Tables.

infusionsoft_datawarehouse_schema.sql.txt (37.5 KB)

If you want to get the Tags for the Contacts, then you can use the XML-RPC API instead.
There is two ways you can do it, but the first one depends if there is a lot of Tags being assigned to Contacts.

You will need to use the “Data Service Query” API Method as shown below.
https://developer.infusionsoft.com/docs/xml-rpc/#data-query-a-data-table

The first way would be to query the “Contact” table.

The “Groups” field contacts a comma separated list of the Contact Tag Ids. Not sure if there is an upper limit of maybe a 1000 Tag Ids. If it is only 30 or so, then that would be fine.

The second way, which would be preferable would be to query the “ContactGroupAssign” API table.

The “GroupId” field is the Tag Id. Note, there could be tens or hundreds of thousands of entries here. But if you store this in your own Database then you would have a proper mapping.

The “ContactGroup” Table contains the list of Tag Names.
https://developer.infusionsoft.com/docs/table-schema/#ContactGroup

The “ContactGroupCategory” Table contains the list of the Tag Categories.
https://developer.infusionsoft.com/docs/table-schema/#ContactGroupCategory

I cannot comment if the services you use support XML-RPC API.

Hope that helps.

1 Like