How to load data from Pipedrive to Google BigQuery

How to load data from Pipedrive to BigQuery

Pipedrive is a sales management tool designed to help small sales teams manage intricate or lengthy sales processes successfully. What if I want to get more data-driven and analyse the CRM data you have on Pipedrive to my BI or custom analytics stack or to my data warehouse like BigQuery? How can I analyse the data generated with Pipedrive as part of your CRM customer data? This guide is going to provide you with a clear picture about how to load data from Pipedrive to Google BigQuery.  We will use Pipedrive’s API to access and extract customer related data and load it into Google BigQuery for further analysis.

You will need to write the code to get the data and make sure that this process will run every time new data are generated. Alternatively, you can use products like Blendo that can handle this kind of problems automatically for you.

Continue reading

How to Load data from SendGrid to Google BigQuery

How to load data from SendGrid to BigQuery

Continuing with our view on the transactional email services, today we are going to see Sendgrid. SendGrid is a transactional email service. But what if I want to get more data-driven and gather all my transactional data from SendGrid to my BI or custom analytics stack or to my data warehouse like BigQuery? How can I analyse the data generated with SendGrid as part of your transactional email campaigns? This guide is going to provide you with a clear picture about how to load data from SendGrid to Google BigQuery.  We will use SendGrid’s API to access and extract e-mail related data and load it into Google BigQuery for further analysis.

Continue reading

How to Load data from Mandrill to Google BigQuery

How to load data from Mandrill to BigQuery

There’s a continually growing usage of services like Mandrill. Mandrill  is a transactional email API for Mailchimp Users and it is ideal for sending data-driven emails, including targeted and personalized one-to-one messages to your customers. But that traffic creates more data. How can you analyse the data you generate with Mandrill as part of your transactional email campaigns, on BigQuery? This guide is going to provide you with a clear picture about how to load data from Mandrill to Google BigQuery.  Alternatively, you can use products like Blendo that can handle this kind of problems automatically for you.

ETL your Mandrill data into your Data warehouse

We will use Mandrill’s API to access and extract e-mail related data and load it into Google BigQuery for further analysis. You will need to write the code to get the data and make sure that this process will run every time new data are generated.

What is Mandrill?

Mandrill is a transactional email API for Mailchimp Users. Although in the past, Mandrill was perceived as a different product than Mailchimp, right now it is offered as a Mailchimp plugin. Mandrill is reliable, powerful and ideal for sending data-driven emails, including targeted and personalised one-to-one messages to your customers. You might wonder what are the differences between Mandrill and Mailchimp, as both of them handle the delivery of emails to your customers. Some key differences between the two are the following:

  • Mandrill is designed for developers. If you are not comfortable with writing code then it would be better if you find someone before you start using it. On the other hand, Mailchimp is designed mainly for marketeers so no technical skills are required to use it.
  • Mandrill focuses more on transactional e-mails, which are different than the promotional/campaign based emails that marketeers send using Mailchimp. Transactional e-mails are more tailor made for cases like one to one messages to your customers, like resetting passwords, welcoming them etc.
  • Mailchimp offers richer reporting, but with Mandrill it is easier to have access to all the raw events related to your e-mails as they happen, so you can run your own analytics if you wish. In general, raw data from Mandrill are much more accessible than in Mailchimp.
  • If you wish to run complicated campaigns with Mandrill you will have to implement the logic behind running them, with Mailchimp you can do that using the drag-n-drop environment that it offers.
How to load data from Mandrill to Google BigQuery

How to load data from Mandrill to Redshift

In general, you can perceive the two services as complementary, Mailchimp allows your marketing department to easily and fast execute their marketing strategies, while with Mandrill you have access to a very flexible and rich environment where you can build complex products on top of e-mail services, but it requires the involvement of your R&D team.

Mandrill, as an email API, exposes all its functionality through it. So apart from being possible to execute a number of operations like sending emails based on specific events, we can also use this API to pull out data or setup our infrastructure in such a way that Mandrill will send to us data over time they are generated. In this article, we will see in detail the different ways of pulling data from Mandrill that can be useful for running our analytics.

Continue reading

How to Load data from Square to Google BigQuery

How to load data from Square to Google BigQuery

Square is a simple and powerful POS software for businesses. What if I want to get more data-driven and gather all my data from Square to my BI or custom analytics stack or to my data warehouse like BigQuery? How can I analyse the data generated with Square? This guide is going to provide you with a clear picture about how to load data from Square to Google BigQuery.  We will use Square’s API to access and extract e-mail related data and load it into Google BigQuery for further analysis.

Continue reading

How to Load data from Shopify to Google BigQuery

shopify to bigquery

How may I load data from Shopify to Google BigQuery for further analysis? The purpose of this post is to help you define a pipeline, for getting your subscription related data from Shopify and load it into Google BigQuery for further analysis. We will see how to access and extract data from Shopify through its API and how to load it into Google BigQuery.

This process requires from you to write the code to get the data and make sure that this process will run every time new data are generated. Alternatively, in order to load your data from Shopify to BigQuery you can use an ETL as a service product like Blendo that can handle this kind of problems automatically for you.

What we will see:

  • An Intro to Google BigQuery and Shopify.
  • Extract your data from Shopify and Shopify API (the hard way)
  • Prepare your Shopify Data for Google BigQuery
  • Load Data from Shopify to Google BigQuery
  • The best way to load data from Shopify to Google BigQuery (the easy way)

What is Shopify?

Shopify is one of the easiest and simple to use managed e-commerce platforms. It offers an easy way for anyone to create an online store and start selling goods. It natively integrates with services like Stripe and Paypal to make things even easier for potential users of the platform. Shopify also offers a very rich app marketplace, where its users can find powerful extensions and plugins that can enhance their online shops.

Load data from Shopify to Google BigQuery

Load data from Shopify to Google BigQuery

They have a quite flexible pricing model that can cover the needs of very small to large online shops, you can start with the lite version and as your business grows quite easily you can jump to a larger plan. Some of the benefits of Shopify are:

  • Easy to start. Both the experience the platform offers and the pricing model, makes it extremely easy for anyone to setup and run an online shop.
  • No technical skills required. As a self-managed SaaS product, Shopify requires minimum technical skills from its users.
  • It is hosted. Which means that you don’t have to care about where to host your online shop, they offer all the infrastructure and services needed without requiring from you to delve into technical details.
  • It can be personalized. Although a hosted platform, it can be easily customized to meed your brand.

Additionally, Shopify has succeeded in creating a unique ecosystem of developers who build added value services and extensions on their platform. A large number of plug-ins are available that help Shopify users to personalise even more their e-shops while developers can generate a good income by selling their applications and extensions. Of course such a success was built on top a well designed API that exposes the whole platform to the developers. This API will be also used in this article to show how we can pull out valuable data from Shopify.

What is Google BigQuery?

BigQuery is the data warehousing solution of Google. It’s part of the Google Cloud Platform and it also speaks SQL like Redshift does. Queries are executed against append-only tables using the processing power of Google’s infrastructure.

Load data from Shopify to Google BigQuery

Load data from Shopify to Google BigQuery

It is also fully managed and is offered as a service over the cloud. You can interact with it through its web UI, using a command line tool while a variety of client libraries exist so you can interact with it through your application.

How to Extract my data from Shopify?

Shopify exposes its complete platform to developers through their API. It is used by thousands of developers to create applications that are then sold through the Shopify marketplace.

Continue reading

Load data from Magento to Google BigQuery

magento to bigquery

How may I load data from Magento to Google BigQuery for further analysis? The purpose of this post is to help you define a process or pipeline, for getting your subscription related data from Magento and load it into Google BigQuery for further analysis. We will see how to access and extract data from Magento through its API and how to load it into Google BigQuery. This process requires from you to write the code to get the data and make sure that this process will run every time new data are generated. Alternatively you can use products like Blendo that can handle this kind of problems automatically for you.

About Magento

Load data from Magento to Redshift

Load data from Magento

Magento is an e-commerce platform built on open source technology which provides online merchants with a flexible shopping cart system, as well as control over the look, content and functionality of their online store. Magento offers powerful marketing, search engine optimisation, and catalog-management tools. Some of its main characteristics are the following:

  • Feature rich. Magento is very rich in functionality and offers an in-depth and powerful platform.
  • Powerful SEO. Magento is well known for its SEO capabilities, it offers one of the, out of the box, SEO optimisation for the stores hosted on it.
  • Magento, ensures that your store can seamlessly grow along with your business.
  • Flexibility. Its template based architecture allows you to pretty customise everything.
  • Magento community edition is open source and free to use.
  • Security. Magento is build with security in its core.
  • User friendly. The administration area exposes a simple back end with intuitive navigation and well organised store management features.
  • Community. Being open source, guarantees a healthy ecosystem to support and further develop the platform.

Magento offers three different versions of its platform:

  • Community edition. It’s the open source and free version of Magento.
  • Enterprise edition. Magento Enterprise Edition is designed to empower merchants to rapidly innovate and deliver engaging experiences to customers across all channels and devices.
  • Enterprise cloud edition. The Enterprise Edition of the platform as a service delivered over the cloud.

About Google BigQuery

Load data from Magento to Google BigQuery

Load data from Magento to Google BigQuery

 

BigQuery is the data warehousing solution of Google. It’s part of the Google Cloud Platform and it also speaks SQL like Redshift does. Queries are executed against append-only tables using the processing power of Google’s infrastructure. It is also fully managed and is offered as a service over the cloud. You can interact with it through its web UI, using a command line tool while a variety of client libraries exist so you can interact with it through your application.

Extract your data from Magento

Magento exposes its platform through both a REST and a SOAP interface. Both can be used to pull data from it, which is also the scope of this article, but also to interact with the platform. By using these interfaces, developers create rich applications and plugins for Magento. In this post we will use the REST version of the Magento platform.

Continue reading

Load data from Braintree to Google BigQuery

braintree to bigquery

How may I load data from Braintree to Google BigQuery for further analysis? The purpose of this post is to help you define a process or pipeline, for getting your subscription related data from Braintree and load it into Google BigQuery for further analysis. We will see how to access and extract data from Braintree through its API and how to load it into Google BigQuery. This process requires from you to write the code to get the data and make sure that this process will run every time new data are generated. Alternatively you can use products like Blendo that can handle this kind of problems automatically for you.

About Braintree

Load data from Braintree to SQL Data Warehouse

Load data from Braintree

Braintree is a full-stack payments platform that makes it easy to accept payments in your app or website. Our service replaces the traditional model of sourcing a payment gateway and merchant account from different providers. It offers a simple and robust way to accept payments or enable buying from almost anywhere, in your mobile app or online. Braintree gives access to multiple payment methods:

  • Credit / Debit cards: you can accept cards of any type.
  • Apple pay: support for Apple wallet.
  • Android Pay: Accept payments from Android Pay.
  • Venmo: Simplified mobile buying.

It offers simple pricing:

  • First $50K are free of fees
  • There are no minimum or monthly fees
  • After the first $50K the cost is 2.9% + $.30 per transaction

And top notch security:

  • AVS: Helps to verify the provided address.
  • CVV: Ensures that the verification numbers are always validated
  • Risk Threshold: Configure rules to detect fraud

And finally together with all the above, world-class support.

A payment platform like Braintree holds a large number of data related to your company and your customers that are extremely valuable for your business. With data coming from your payment system, you can calculate important KPIs like your revenues and your churn and if you can get access to all the available data your analysts can do wonders. Fortunately Braintree exposes a rich ecosystem of tools and APIs that you can use to get the most out of your payment data.

About Google BigQuery

Load data from Braintree to Google BigQuery

Load data from Braintree to Google BigQuery

 

BigQuery is the data warehousing solution of Google. It’s part of the Google Cloud Platform and it also speaks SQL like Redshift does. Queries are executed against append-only tables using the processing power of Google’s infrastructure. It is also fully managed and is offered as a service over the cloud. You can interact with it through its web UI, using a command line tool while a variety of client libraries exist so you can interact with it through your application.

Extract your data from Braintree

Braintree, as it is common with payment gateways, exposes an API which can be used to integrate a product with payment services. The access to this API happens through a number of clients or SDKs that Braintree offers:

Instead of a public REST API, Braintree provides client libraries in seven languages to ease integration with our gateway. This choice is deliberate as Braintree believes that in this way they can guarantee:

  1. Better security
  2. Better platform support. And
  3. Backward compatibility

The languages they targeted with their SDKs cover the majority of the frameworks and needs. For example, with the Java SDK they can also support the rest of the JVM languages like Scala and Clojure.

Braintree API Authentication

To authenticate against the Braintree API and perform either transactions or pull data, the following credentials are required.

Continue reading

Load data from Trello to Google BigQuery

trello to bigquery

How may I load data from Trello to Google BigQuery for further analysis? The purpose of this post is to help you define a process or pipeline, for getting your subscription related data from Trello and load it into Google BigQuery for further analysis. We will see how to access and extract data from Trello through its API and how to load it into Google BigQuery. This process requires from you to write the code to get the data and make sure that this process will run every time new data are generated. Alternatively you can use products like Blendo that can handle this kind of problems automatically for you.

About Trello

Load data from Trello to Redshift

Load data from Trello

Trello is a collaboration tool that organises your projects into visual boards. In one glance, Trello tells you what’s being worked on, who’s working on what, and where something is in progress. Trello is simple but flexible enough to allow you to organise your boards using any methodology that you like, for example many people use Trello to run Kanban.

Trello is simple on the surface, but cards have everything you need to get stuff done. You can post comments for instant feedback. Upload your files from Google Drive, Dropbox, Box, and OneDrive. Add checklists, labels, due dates, and more. Notifications make sure you always know when important stuff happens.

It offers a very simple pricing scheme:

  • Free: this first tier might cover the majority of users. You have access to all the basic functionalities that Trello
  • Business Class & Enterprise. Charged per seat and per month. the main difference between the two are the number of teams that are supported. Also app integration, team overview, increased file size allowed, file encryption, better support, restricted membership and enterprise grade security is provided compared to the Free

As more and more teams rely on Trello to run and track their projects, there is valuable data to be pulled from it that can help you to better understand the productivity of your company. For example by pulling data out from Trello and store it into Google BigQuery,  you can calculate numerous metrics about your sprint, like its current burndown rate. Identify projects with problems and figure out potential bottlenecks. In this article, we will find out how we can pull data from Trello to Google BigQuery for further analysis.

About Google BigQuery

Load data from Braintree to Google BigQuery

Load data from Trello to Google BigQuery

 

BigQuery is the data warehousing solution of Google. It’s part of the Google Cloud Platform and it also speaks SQL like Redshift does. Queries are executed against append-only tables using the processing power of Google’s infrastructure. It is also fully managed and is offered as a service over the cloud. You can interact with it through its web UI, using a command line tool while a variety of client libraries exist so you can interact with it through your application.

Want to save hours on your data managment tasks?
Blendo can help! Request your Invite now!

Extract your data from Trello

Trello exposes a very rich API to developers. It is the same API that is used internally to build the web and mobile Trello apps that we all use and love. It is possible to build a completely new application on top of the API using the different components and resources that it exposes, or just use it to pull out data as we plan to do in our case.

Continue reading

Load data from Hubspot to Google BigQuery

hubspot to bigquery

How may I load data from Hubspot to Google BigQuery for further analysis? The purpose of this post is to help you define a process or pipeline, for getting your subscription related data from Hubspot and load it into Google BigQuery for further analysis. We will see how to access and extract data from Hubspot through its API and how to load it into Google BigQuery. This process requires from you to write the code to get the data and make sure that this process will run every time new data are generated. Alternatively you can use products like Blendo that can handle this kind of problems automatically for you.

About Hubspot

Load data from Hubspot to SQL Data Warehouse

Load data from Hubspot

Hubspot is a suite of software offering two different customer related products:

  • Hubspot CRM. For Customer Relations Management.
  • Hubspot Marketing. Marketing platform, focusing mainly on inbound marketing.

Hubspot CRM offers a CRM platform that is connected to all the different channels and touchpoints where the modern sales process takes place. Channels like:

  • Landing pages
  • email
  • phone calls
  • social media

By using Hubspot CRM it is possible to control your content, channels and marketing performance from one single platform, making it much easier to have a birds eye view of your sales process. You can create customised views, it offers deal and task dashboards and it connects directly with Hubspot Marketing so you can effortlessly capture, score and hand off leads to your sales team.

Hubspot Marketing which was also the first product of Hubspot, offers the complete marketing funnel to its user, from attracting users to closing customers with less hassle and more control of the whole process. It focuses mainly on inbound marketing techniques related to the following:

  • Blogging
  • SEO
  • Social Media
  • Landing pages
  • Email campaigns

It allows you to easily perform lead management, it supports marketing automation like triggering mails when specific events happen on your landing page. It offers rich analytics and of course it integrates seamlessly with Hubspot CRM.

It is clear that the Hubspot products generate a large number of valuable data related to your business, product and customers. While it offers analytic services, you might want to run some more engaged analysis with your Hubspot data, or merge these data with other sources like your ticketing system, your transaction database and logs or with user generated events from your product. Hubspot exposes a rich ecosystem of tools and APIs for interacting with its platform and it is possible by using these APIs to pull the data that are valuable to you from it.

About Google BigQuery

Load data from Hubspot to Google BigQuery

Load data from Hubspot to Google BigQuery

 

BigQuery is the data warehousing solution of Google. It’s part of the Google Cloud Platform and it also speaks SQL like Redshift does. Queries are executed against append-only tables using the processing power of Google’s infrastructure. It is also fully managed and is offered as a service over the cloud. You can interact with it through its web UI, using a command line tool while a variety of client libraries exist so you can interact with it through your application.

Extract your data from Hubspot

The Hubspot APIs are following the REST architecture, that can be accessed through HTTP. As a RESTful API, interacting with it an be achieved by using tools like CURL or Postman or Apirise or by using http clients for your favourite language or framework.

Continue reading

Load data from Zendesk to Google BigQuery

zendesk to bigquery

How may I load data from Zendesk to Google BigQuery for further analysis? The purpose of this post is to help you define a process or pipeline, for getting your subscription related data from Zendesk and load it into Google BigQuery for further analysis. We will see how to access and extract data from Zendesk through its API and how to load it into Google BigQuery. This process requires from you to write the code to get the data and make sure that this process will run every time new data are generated. Alternatively you can use products like Blendo that can handle this kind of problems automatically for you.

About Zendesk

Load data from Zendesk to SQL Data Warehouse

Load data from Zendesk

Zendesk provides a cloud-based customer service platform, that includes ticketing, self-service options, and customer support features. Zendesk focuses on improving the communication between your customers and your company. It brings all your customer communication into one place. The supported communication channels are:

  • Mail. Zendesk helps to organise all the emails you receive from your customers.
  • Social. You can connect your Facebook and Twitter accounts with Zendesk.
  • Voice. Take customer calls from within Zendesk.
  • Chat. Zopim Chat allows you to communicate with your customers from within your product.

Zendesk’s help desk software helps streamline customer support with time-saving tools like triggers and automations. And it’s intuitive, built with the experience of customer service and support desk agents in mind. Some important features of the ticketing system that Zendesk offers, are:

  • It helps to Solve tickets better with teammates through a collaborative environment where information can be shared.
  • Get access to relevant information across teams. Zendesk comes with an internal Knowledge Base that allows agents to quickly refer to information and processes without losing their place.
  • Make everyone inside your company a support agent. With light agents, everyone inside your company can view tickets and make private comments.

Additionally, Zendesk offers a suite of analytics tools that will help you to get closer to your user through data. With these tools you can:

  • Gain visibility into customers interactions
  • Measure your team’s performance
  • See the business impacts of great service

It is possible to track a large number of metrics related to your customers, support teams and your business.

But in case that you would like to run some more engaged analysis with your Zendesk data, or fuse the customer support related data with data from other sources like your transactional database and logs, Zendesk exposes a rich ecosystem of APIs and tools that you can use to access and pull your data among other functionalities.

About Google BigQuery

Load data from Zendesk to Google BigQuery

Load data from Zendesk to Google BigQuery

 

BigQuery is the data warehousing solution of Google. It’s part of the Google Cloud Platform and it also speaks SQL like Redshift does. Queries are executed against append-only tables using the processing power of Google’s infrastructure. It is also fully managed and is offered as a service over the cloud. You can interact with it through its web UI, using a command line tool while a variety of client libraries exist so you can interact with it through your application.

Extract your data from Zendesk

Zendesk APIs are not specific to pulling data, Zendesk provides more than a hundred different APIs for you to integrate with. So you can easily manage your users, enhance your team’s productivity and create seamless integrations. You can create integrations or even enrich Zendesk with data from external sources. Zendesk API is a RESTful API that can be access through HTTP. As a RESTful API, interacting with it an be achieved by using tools like CURL or Postman or by using http clients for your favourite language or framework.

Continue reading