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

Load data from Shopify to Redshift

shopify to redshift

Shopify is one of the easiest and simple to use managed e-commerce platforms. How can you combine your Shopify data with other sources to gain new insights. Let’s see how to load data from Shopify to Redshift. This post will help you define a pipeline, for getting your e-commerce related data from Shopify and load it into Amazon Redshift for further analysis. We will see how to access and extract your data from Shopify through its API and how to load it into Redshift.

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 Redshift 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 Amazon Redshift and Shopify.
  • Extract your data from Shopify and Shopify API (the hard way)
  • Prepare your Shopify Data for Amazon Redshift
  • Load Data from Shopify to Amazon Redshift
  • The best way to load data from Shopify to Amazon Redshift (the easy way)

About Shopify

Load data from Shopify to Redshift

Load data from Shopify to Redshift

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. 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 personalised. Although a hosted platform, it can be easily customised 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.

About Amazon Redshift

Load data from Shopify to Redshift

Load data from Shopify to Redshift

Amazon Redshift is one of the most popular data warehousing solutions which is part of the Amazon Web Services (AWS) ecosystem. It is a petabyte scale, fully managed data warehouse as a service solution that runs on the cloud. It is SQL based and you can communicate with it as you would do with PostgreSQL, actually you can use the same driver although it would be better to use the drivers recommended by Amazon. You can connect either through JDBC or ODBC connections.

Extract your 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

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 Redshift

magento to redshift

Magento is one of the most popular e-commerce platforms. How can you combine your Magento data with other sources to gain new insights. Let’s see how to get the data we have on Magento to Redshift. This post will help you define a process or pipeline, for getting your e-commerce related data from Magento and load it into Amazon Redshift for further analysis. We will see how to access and extract your data from Magento  through its API and how to load it into Redshift. 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 Magento to Redshift 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 Amazon Redshift

Load data to Amazon Redshift

Load data to Amazon Redshift

Amazon Redshift is one of the most popular data warehousing solutions which is part of the Amazon Web Services (AWS) ecosystem. It is a petabyte scale, fully managed data warehouse as a service solution that runs on the cloud. It is SQL based and you can communicate with it as you would do with PostgreSQL, actually you can use the same driver although it would be better to use the drivers recommended by Amazon. You can connect either through JDBC or ODBC connections.

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 Magento to SQL Data Warehouse

Magento to SQL Data Warehouse

How may I load data from Magento to SQL Data Warehouse? The purpose of this guide is to help you define a process or pipeline, for getting your data from Magento and load it into SQL Data Warehouse for further analysis. Information will be given on how yo access and extract your data from Magento through its API and how to load it into SQL Data Warehouse, 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 SQL Data Warehouse

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 Microsoft Azure SQL Data Warehouse

Load data to Azure SQL Data Warehouse

Load data to Azure SQL Data Warehouse

SQL Data Warehouse is the data warehousing solution that you can use if you are a user of Microsoft Azure. It’s an elastic data warehousing as a service solution, emphasising it’s enterprise focus. It also speaks SQL like the previous two solutions and it supports querying both relational and non-relational data.  It offers a number of enterprise-class features like support for hybrid cloud installations and strong security. It’s probably the less mature solution compared to the two others though, it’s still in “Preview” mode although accessible to existing Azure subscribers.

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

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 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 Shopify to SQL Data Warehouse

Shopify to SQL Data Warehouse

Shopify is one of the easiest and simple to use managed e-commerce platforms. How can you combine your Shopify data with other sources to gain new insights. Let’s see how to get the data we have on Shopify to SQL Data Warehouse. This post will help you define a process or pipeline, for getting your e-commerce related data from Shopify and load it into SQL Data Warehouse for further analysis. We will see how to access and extract your data from Shopify through its API and how to load it into SQL Data Warehouse . 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.

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. 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 personalised. Although a hosted platform, it can be easily customised to meed your brand.
Load data from Shopify to SQL Data Warehouse

Load data from Shopify to SQL Data Warehouse

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 Microsoft Azure SQL Data Warehouse?

SQL Data Warehouse is the data warehousing solution that you can use if you are a user of Microsoft Azure. It’s an elastic data warehousing as a service solution, emphasising it’s enterprise focus. It also speaks SQL like the previous two solutions and it supports querying both relational and non-relational data.

Load data to Azure SQL Data Warehouse

Load data to Azure SQL Data Warehouse

It offers a number of enterprise-class features like support for hybrid cloud installations and strong security. It’s probably the less mature solution compared to the two others though, it’s still in “Preview” mode although accessible to existing Azure subscribers.

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

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

How to Load data from Mandrill to SQL Data Warehouse

How to load data from Mandrill to AzureSQL

Mandrill is a transactional email API for Mailchimp Users and it is ideal for sending data-driven emails, including targeted and personalised 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 SQL Data Warehouse? This guide is going to provide you with a clear picture about how to load data from Mandrill to SQL Data Warehouse.  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 SQL Data Warehouse 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 AzureSQL

How to load data from Mandrill to AzureSQL

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.

Continue reading

Load data from Trello to Redshift

trello to redshift

Trello is one of the most popular colaboration and project tool. But it gets filled with data. How you may analyse the data you have on Trello and do that on Amazon Redshift? The purpose of this post is to help you define a process or pipeline, for getting your project management related data from Trello to Redshift for further analysis. We will see how to access and extract your data from Trello  through its API and how to load it into Redshift. 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 Trello to Redshift you can use products like Blendo that can handle this kind of problems automatically for you.

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 Amazon Redshift,  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 Redshift for further analysis.

About Amazon Redshift

Load data to Amazon Redshift

Load data to Amazon Redshift

Amazon Redshift is one of the most popular data warehousing solutions which is part of the Amazon Web Services (AWS) ecosystem. It is a petabyte scale, fully managed data warehouse as a service solution that runs on the cloud. It is SQL based and you can communicate with it as you would do with PostgreSQL, actually you can use the same driver although it would be better to use the drivers recommended by Amazon. You can connect either through JDBC or ODBC connections.

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

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 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