• MarTech Today
  • Sections
    • Ads
    • Marketing
    • Content
    • Sales
    • Analytics
    • Management
    • Resources
    • More
    • Home
  • Follow Us
    • Follow
  • MarTech Today
  • Ads
  • Marketing
  • Content
  • Sales
  • Analytics
  • Mgmt
  • Resources
  • More
  • Events
    • Follow
  • SUBSCRIBE

MarTech Today

MarTech Today
  • Ads
  • Marketing
  • Content
  • Sales
  • Analytics
  • Management
  • Resources
  • More
  • Events
  • Newsletters
  • Home
Martech: Management

A DIY marketing data warehouse recipe

Vote for this entry in the 2017 Hackie Awards by sharing it on Facebook, Linkedin or Twitter by May 1.

MarTech Today on March 13, 2017 at 9:30 am
  • More

This article is a guest post by Alex Sirota of NewPath Consulting. It was entered into The Hackies essay contest for the upcoming MarTech conference. Like it? You can register your vote in the contest by sharing it on social media, especially LinkedIn, Facebook, and Twitter.

Let’s use Amazon Web Services Relational Database Service (AWS RDS) to create a cloud based relational database system!

AWS RDS is a cloud platform that makes it easier to set up, operate, and scale a relational database in the cloud. AWS RDS provides cost-efficient, resizeable capacity for an industry-standard relational database and manages common database administration tasks.

AWS RDS currently supports these databases:

  • Oracle on Amazon RDS
  • MySQL on Amazon RDS
  • Microsoft SQL Server on Amazon RDS
  • PostgreSQL on Amazon RDS
  • Aurora on Amazon RDS
  • MariaDB on Amazon RDS

For large data warehousing needs, Amazon Redshift is also available and supported by Fivetran. Here’s a tour of Fivetran.

Below is a recipe for getting going with your own data warehouse to start building powerful business dashboards with tools like Looker.

Ingredients:

  1. Amazon Web Services RDS — free for the first 12 months (certain restrictions apply, see the RDS pricing page).
  2. Setup a trial account at Fivetran.com.
  3. Install pgAdmin or SQL Workbench/J (with JDBC drivers for your operating system).
  4. Need some working knowledge of SQL to grant permissions on your data warehouse to Fivetran.

Recipe:

  1. Setup an Amazon RDS instance using PostgreSQL. Amazon Redshift and Snowflake are higher-end enterprise tools that are also potential data warehouses, but we haven’t tried them out yet. You can use your amazon.com account to login to AWS and setup PostgreSQL on AWS RDS.
  2. Once AWS RDS is setup you will be able to connect to the instance from your machine only (the default access control for a new AWS RDS).
  3. Modify access control groups for AWS RDS to enable access for Fivetran to access your database.
  4. Create necessary user for Fivetran using your administrative account and grant privileges for your database to the Fivetran account (SQL). Make sure you commit your changes!
  5. Setup data sources in Fivetran, authenticate and start the data pipeline (Google Analytics and Stripe are interesting ones to start with).
    1. You will need to setup a schema (a collection of tables).
    2. You will need to specify which target table will be populated.
  6. Use your SQL Workbench or pgAdmin to verify tables are being created in your data warehouse.
  7. The data pipeline will continue to populate data as new data is created in the data sources. Any changes in the schema will be reflected in your data warehouse but no data will ever be deleted. Changes will be marked in the target tables.

Time to bake:

Probably 3-5 hours depending on how quickly you can click the UIs!

Alternate prep techniques:

The functionality of tools like Zapier should not be confused with tools like Fivetran. Zapier is great at integrating two or more cloud apps that do not have built in integrations.

So, for example, using Zapier you can have a form filled out in Formstack, trigger an event in a Slack channel. Formstack does not support integration with Slack, but Zapier bridges this gap so that data can trigger an event in a remote cloud app. Zapier is sort of a “crazy glue” to connect disparate cloud apps that don’t have a native integration.

Have fun integrating data and building data pipelines!

About the author

Alex Sirota is a pioneer in using the cloud to meet the needs of small and medium sized business (SMBs). He has a BSc in computer science from the University of Michigan and has worked as a product manager at two Internet startups. He has co-authored a book on Oracle SQL and maintains a Project Management Professional (PMP) certification. He acquired knowledge of management of small businesses and entrepreneurship while working with the Ontario ministry of economic development. Alex is a co-organizer of the WordPress community in Toronto, and the president of the University of Michigan Alumni Club of Toronto.

What did you think of this article as an entry in The Hackies essay contest for the upcoming MarTech conference? If you liked it, you can register your vote in the contest by sharing it on LinkedIn, Facebook, and Twitter.

Have a marketing/technology/management “hack” that you want to share with the world? Consider entering The Hackies yourself — we’d love to learn from your experience and insight!


Opinions expressed in this article are those of the guest author and not necessarily MarTech Today. Staff authors are listed here.



About The Author

MarTech Today
MarTech Today is a daily publication covering digital marketing industry news, martech trends, strategies and tactics for marketing technology professionals. Special content features, site announcements and occasional sponsor messages are posted by MarTech Today.

Related Topics

Channel: Martech: ManagementDMP: Data Management PlatformsMarTech Conference

Subscribe to receive daily martech news and expert insights. See terms.


We're listening.

Have something to say about this article? Share it with us on Facebook and Twitter.

Get the daily newsletter digital marketers rely on.
See terms.

ATTEND OUR EVENTS

MarTech 2021: March 16-17

MarTech 2021: Sept. 14-15

MarTech 2020: Watch On-Demand

×

Attend MarTech - Click Here


Learn More About Our MarTech Events

White Papers

  • A Beginner’s Guide to Omnichannel Marketing using Marketing Automation
  • The Top Five Objections That Hold Companies Back From Doing SEO
  • How To Optimize SEO With UGC
  • Email Tune-Up: A 5-Point Inspection to Get Your Program in Gear
  • Digital Marketing Report Q4 2020: Benchmarks and Insights for 2021
See More Whitepapers

Webinars

  • The Secret Behind SEO Success: Predict Rank with the Power of Data Science
  • How to Avoid the Digital Transformation Trap
  • How to Build a Marketing System of Record
See More Webinars

Research Reports

  • Local Marketing Solutions for Multi-Location Businesses
  • Enterprise Digital Asset Management Platforms
  • Identity Resolution Platforms
  • Customer Data Platforms
  • B2B Marketing Automation Platforms
  • Call Analytics Platforms
See More Research

Receive daily martech news and analysis.
Martech Today
Download the Martech Today app on iTunes
Download the Martech Today App on Google Play

Channels

  • Advertising
  • Marketing
  • Content
  • Social
  • Commerce
  • Sales
  • Analytics
  • Management
  • Home

Our Events

  • MarTech
  • SMX

Resources

  • White Papers
  • Research
  • Webinars
  • MarTech Conference

About

  • About Us
  • Contact
  • Privacy
  • Marketing Opportunities
  • Staff
  • Connect With Us

Follow Us

  • Facebook
  • Twitter
  • LinkedIn
  • Newsletters
  • Instagram
  • RSS
  • iOS App
  • Google Play

© 2021 Third Door Media, Inc. All rights reserved.

Your privacy means the world to us. We share your personal information only when you give us explicit permission to do so, and confirm we have your permission each time. Learn more by viewing our privacy policy.Ok