May 2020 Summaries

Fivetran introduces a new dbt Package for GitHub that helps track the state of code issues and the entire software delivery process. The package uses the Fivetran Github connector to ingest data from the GitHub API, allowing users to join disparate tables for enriching GitHub issues with assignees and time metrics attached to pull requests. This enables organizations to solve common engineering challenges such as disproportionate issue-to-assignee ratios, identifying potential "cliff" timelines for pull requests, high-level pull request completion tracking, and determining average times taken in each stage of a pull request. Fivetran's native GitHub connector simplifies the process by bringing in data about issues, pull requests, and their contextual information in a predefined format, making it easy to start querying the data right away. The dbt package for GitHub can be used as a standalone source or combined with common project tracking software like Jira or Asana to provide insight into the complete software delivery process.

Fivetran introduces a new connector for Twilio, a cloud communications and customer engagement platform that enables companies to manage messaging across SMS, voice, and email channels. The Twilio connector provides insights into message channel management with metrics such as call length, account origin, usage statistics, agent response times, and peak communication times. Fivetran allows users to combine Twilio data with other product and support tools like Github, Jira, and Zendesk for a comprehensive view of customer interactions across various channels. The setup process is straightforward and requires no coding.

Fivetran has introduced dbt packages to simplify the process of building reports on data loaded into a warehouse. These packages handle basic table standardization and joining, allowing users to focus on modeling unique business logic. They also include staging models for column name standardization and filtering out soft-deleted records, intermediate models for joining tables and aggregating data, and data tests to catch changes in source data that could affect reporting. Fivetran's dbt packages are compatible with cloud warehouses like Snowflake, Amazon Redshift, and Google BigQuery, and can be scheduled in dbt Cloud for fresh data updates.

The article discusses the importance of capturing historical data changes over time, also known as a slowly changing dimension (SCD) or type 1 SCD implementation. It explains how current-state tables typically only reflect the latest truths and don't retain previous attribute values. This can lead to frustration for analysts who need to answer questions about past events and trends. The author then introduces Fivetran's new "History Mode" feature, which turns type 1 SCD implementations into type 2 by creating a new row for any changed values. This effectively transforms the data warehouse into a time machine, allowing users to see how attributes have changed over time and providing valuable contextual information for analysis. The author argues that building safeguards against data transience is crucial for businesses in an increasingly unpredictable world, as it enables more reliable and comprehensive aggregation of data for logistics, monitoring, machine learning, and analytics purposes.

Fivetran, an automated data integration service provider, has expanded its presence in the Asia-Pacific region with the opening of its Sydney office. The company aims to simplify access to businesses' data through reliable and efficient data integration services. T.J. Chandler, Fivetran Managing Director for APAC, announced the launch of the first Fivetran data center in Australia, addressing onshore data residency requirements and offering local performance benefits for customers. The Sydney office will provide regional support to customers and promote the company's core mission: making access to data as simple and reliable as electricity.

This glossary provides definitions of key terms related to data integration and analytics. Analytics refers to identifying meaningful patterns in data to inform business decision-making. Data connectors continuously replicate data from a source to a destination on a set schedule, while APIs enable not only data extraction but also the automated, programmatic operation of an application. Big data is often described using Three Vs: Volume, Variety, and Velocity. A cloud function is a small unit of software hosted on a cloud platform that can be used to build custom data pipelines and integrations. Data integration refers to aggregating operational and transactional data from across an organization and then massaging (i.e., transforming) and analyzing it to enable data-driven decisions. A data pipeline is the "EL" portion of the ELT sequence, delivering data to a destination where transformations are performed. ETL stands for extract-transform-load, while ELT stands for extract-load-transform. The former approach was developed at a time when bandwidth, data storage capacity, and on-demand computational power were expensive, but the latter approach is more modern and cost-effective.

Fivetran built its Klaviyo connector by assigning one software engineer to the project under the supervision of an engineering manager and product lead, with another engineer reviewing the code. The process took about six weeks from January to March 2018, during which time they built the connector, ERD, and documentation. They faced challenges due to discrepancies between publicly available API documentation, data model provided by Klaviyo, and accessible data through API endpoints. Some elements of the data model remain inaccessible today. Building a data connector can be complex and time-consuming, highlighting the benefits of purchasing connectors instead.

In a data analysis initiative, Fivetran discovered a significant revenue opportunity by identifying a discrepancy between the number of accounts created in their production database and those recorded in Salesforce, revealing that many accounts were converting from trials to paid without sales team interaction. This gap, attributed to duplicate, partner, and test accounts, as well as overlooked real customer accounts, highlighted potential lost revenue, prompting the team to implement process changes to capitalize on these opportunities. The exercise underscored the importance of leveraging analytics to uncover hidden value and address business uncertainties, with Fivetran emphasizing the role of data tools in facilitating access to valuable insights and driving organizational improvements.

Agencies can use data to demonstrate the success and impact of their marketing campaigns. A modern data stack, consisting of an automated data pipeline, data warehouse, and business intelligence tool, enables quick access to accurate data required for informed decision-making. To stand out from competitors, agencies should provide clients with insights into why campaigns succeed or fail by analyzing trends and nuances within respective data sets. Fivetran offers prebuilt connectors for various data sources, including marketing APIs, databases, and CRM systems, to help uncover the reasons behind campaign performance. While end-to-end platforms may seem appealing, they often have weak connectors, security concerns, and limited reporting capabilities. By implementing a modern data stack, agencies can differentiate themselves by being truly data-driven and providing unique insights to clients.

The Fivetran dbt package for Salesforce provides essential tables for tracking sales performance, including account owner pipeline health, opportunity stages, win-loss metrics, and overall sales health. This package leverages the ability to pull relevant tables from Salesforce and create reporting tables that can be used as standalone components or combined with other marketing sources. Fivetran helps by automating data replication, managing API limits, and generating destination tables for both standard and performance health metrics.

The new Google Ads account connector allows users to track changes to their campaign, ad and creative settings over time, including bidding strategy, channels, and targeted groups. This is crucial for understanding the performance of paid ads and refining bidding strategies. By analyzing historical data, companies can learn from past successes and failures and adjust their spend accordingly. The AdWords API enables programmatic pulling of account information, while the Google Ads account connector simplifies the process of formatting this data for marketing teams. This data source has various applications, such as finance use cases, merging with other applications like NetSuite, and informing product strategy decisions.

The article discusses the need for improved data literacy in organizations and suggests ways to make data training more effective. It highlights that traditional data training programs often fail to deliver long-term benefits due to factors such as lack of practice, changes in data structure or tools, and attendees forgetting the training. To address these issues, the author recommends setting the right tone by focusing on the benefits and opportunities of learning more about data rather than emphasizing deficiencies. Additionally, they suggest setting goals that focus on asking good questions and implementing slow and steady training methods such as weekly lunch-and-learn sessions instead of intensive courses. The article concludes by encouraging companies to invest in making data more accessible and transforming their business culture through effective data literacy initiatives.

Customers of Fivetran have shared their positive experiences using the company's well-designed schemas for data integration. They appreciate that good schemas mean analysts don't need to worry about the underlying structure of the data, and a data integration solution should feature comprehensive, quality schemas. Clean schemas provide a good starting point for derived tables and other transformations by analysts. Consistency in Fivetran managed schemas has been praised as it allows users to focus on analytics instead of engineering and managing schema changes. Additionally, the documented data models are considered an incredible asset that helps add value to their data consulting practice.

The Fivetran dbt Package for Mailchimp enhances your Mailchimp data by adding reporting tables, allowing you to analyze more meaningful data with your preferred BI or visualization tool. This package offers powerful transformations within your data warehouse on Snowflake, Amazon Redshift, or Google BigQuery and can be reused for other target destinations. The resulting tables are easier to join with tables from other applications, providing a comprehensive view of your business. The transformations in this dbt package enrich Mailchimp's list, campaign, member, and segment tables with metrics from campaign and automation activities such as clicks, opens, and unsubscribes. It also creates an activities table that paints a complete picture of activities motivated by a campaign, including dimensions such as elapsed time between campaign launch and specific actions. Additionally, it ties email engagement actions to automations if you're using the Mailchimp automation feature.

The article discusses the native tools offered by major cloud providers for ETL (Extract, Transform, Load) processes. It categorizes these tools into four types: code-based ETL, GUI-based ETL, workflow-based ETL, and automated ELT. Each tool is evaluated based on its purpose, limitations, and ideal use cases within the context of Amazon Web Services (AWS), Google Cloud Platform, and Microsoft Azure. The article also introduces Fivetran as an alternative solution for ongoing, maintenance-free data replication. A summary chart at the end compares these tools across different categories.