Problem Statement: Business Leaders can’t use customer data to make core business decisions.

Sketchy’s Data Problem…Situation. ⏳

Context: Sketchy.com builds training and educational products for medical school students.

Problem: Team confidence in their data infrastructure and understanding of the customer’s product was low. Marketing used one dataset. Sales used another. Product, a third.

Opportunity: Each team thought they could see the complete view of the customer. How can you trust your data if you have three definitions for gross revenue?

Sketchy’s CPO wanted to know:

• What’s the customer trying to accomplish?
• How do the customers’ goals align with the company’s goals?
• What customer actions drive trial account conversion?

Desired Outcome: Maturing a growing company’s data strategy and infrastructure to scale with them delivers more than building a better stack.

Sketchy Starting State.

I was brought in as an outside data architect to pick up the work from previous firms. The below system was fragmented with data quality issues from Postgres, multiple and duplicate schemas of product analytics information, and no central data model.

Insight and stakeholders…Insight. 💡

The core opportunity to unlock product growth and trust in data…Integrating these three views of data from Marketing, Finance, and Product teams:

-> Remove vendor data silos and replace them with unified data access.

-> Deliver single-source-of-truth so all business stakeholders can collaborate on customer analytics.

-> New platforms/vendors must be capable of efficient integration without requesting Eng support.

Data maturity journey.

I view building a data layer for a new client as following the maturity journey. It’s not a statement of company size aligned to which stack is needed. It’s a statement that you have to work through a Starter Stack before you can build a Growth Stack and so forth. My goals are to drive impact, take the time to architect a stack that will grow to meet the company’s needs, and avoid data pitfalls as the models and functions become more sophisticated.

Where Sketchy wanted to be.

Project Goals = Starter Stack. 🥅

Sketchy wanted that solid foundation that would support the fast-growing company they already were. They wanted experimentation and eventually machine learning, but first they wanted a strong, stable foundation that could scale with them. Their primary tenants where

1. Self-service for all non-technical and business teams.
2. A single data modeling layer controlling all business metric definitions.
3. DataOps to add QA and validate data at source = BI dashboards.
4. Plan for hiring, reduce infrastructure costs, and formalize contract between Engineering, and Data.

My approach for Sketchy. 🛬

Stage one: integrating existing product/web data with ad and revenue datasets.

Stage two: building a data warehouse and metrics system for sharing customer insights.

Stage three: validating all datasets and training the team in self-service.

Stage four: evangelize best practices and define a hiring plan.

Project implementation Stages.

Stage one:

• Build LookML data relationships from the base of web events.
• Build from scratch data model for the product, marketing, CS, and finance teams.

Stage two:

• Build a central metrics layer for the business in Looker to unify metric definitions.
• Move data modeling from LookML to DBT by building defining SQL transformation layer.

Stage three:

• QA and add DBT tests to every dataset.
• Deliver product, marketing, and company dashboards.

Stage four:

• Complete infrastructure rebuild plan.
• Cost-saving initiatives.
• Optimize all queries and switch to incremental builds.
• Ownership strategy for infrastructure.
• Move to Airbyte and Snowflake for more control of their new backend RDS infra.
• One-year hiring and team development plan.

Project outcome. 🚀

Sketchy reported executives trusted their new data platform. They had cross-company, customer analytics that worked.

Self-serve analytics worked for Sketchy. Their customer insights from the platform drove a six-figure revenue lift in the first year by correctly identifying and removing account sharing.

They had…

1. Trust in data.
2. Adding custom ad hoc requests now only requires a single new dashboard or SQL file added to the data model.
3. Centralized reporting drives better collaboration and quicker customer insights.
4. Save 100K in year one from analytics insights.

Next steps with Atadataco.

Achieved: Sketchy complete analytics stack and data pipeline rebuild.

Next Steps: Sketchy opted for more control and ownership of their data stack.

The first on the roadmap was Airbyte + Snowflake to replace Fivetran + BigQuery for more flexibility and cost savings.

The second was to implement end-to-end integration testing.

The third was a formal experimentation engine to drive faster growth.

Now they had a collaborative, scalable Starter Stack. Now they could build their Growth Stack with confidence.

Trusted by high-growth companies and founders like Sketchy. 🤝

Our managed data layer and building methods work whether you are starting from scratch or growing into a bigger data stack. We scale to match your needs and your business goals. Let us collaborate with you in every stage of development.

Our specialty is the startup space. We have built complete, trusted analytics platforms for many verticals including AdTech, e-Commerce, Fashion, FinTech, MarTech, Medical, Recruiting, SAAS, Sales, and Security.

Schedule a time for us to review your business needs and recommend a managed data stack sized perfectly for your company:

Atadataco | Contact

Today’s entrepreneurial marketplace is a hotbed of competition for analytics professionals. Many young companies and upstarts are realizing the need to hire fresh analytics engineers from the pool of job applicants that apply. In order to stand out from the crowd, start-ups are actively engaging the talent marketplace by offering jobs that go beyond traditional job descriptions: offering unique opportunities for advancement in the company or interesting, specialized roles. There is a tremendous amount of demand to fill positions in analytics and many are not well advertised. As the analytics marketplace continues to evolve, these organizations must improve their hiring and retain the best analytics professionals in the industry.

Startups Must Press Hard for More Experienced Talent 📥

For startup companies or relatively new organizations, they must compete with larger firms for top talent. These firms typically have an existing relationship and rapport with analytics professionals and have established relationships with consulting firms, agencies, and recruiters. The best advice for these firms is to be aggressive in their quest to recruit experienced and talented analytics professionals. They are also willing to invest heavily in technology and other assets to promote their own company image.

The Old Paradigm: hire on Upwork⏰

There are many ways for start-ups to tap into the world of analytics professionals. One way is to build the team with only freelancers and a product manager for oversight. In this scenario, the start-up company can source the analytics engineers on a freelance basis and pay only for those employees and time that can make a significant impact on the business. Additionally, smaller analytics firms may be willing to provide on-the-job training and mentoring from a more senior engineer. And don’t underestimate the value a junior hire will receive from working for and with you as an executive. Your skillset and ability to simply convey the business problem will be super valuable to them in the future.

Try This Instead as a Startup Founder

Recruiting professionals who have the skillset and knowledge necessary to become effective leaders and managers can be challenging for many organizations. However, with the right type of support from a consulting firm, an organization can attract and hire the best analytics professionals without having to pay exorbitant full-time salaries. Today, many recruiting agencies offer analytics job recruiting services such as ours. These services target smaller firms in the analytics industry. In addition to offering recruitment services, these recruitment agencies can also provide mentoring and on-the-job training. This type of collaboration can prove to be very helpful for small businesses looking to build their own team of analytics engineers.

In conclusion, the future of analytics is vast and promising. If this idea of building your team with remote talent appeals to you, reach out to us at hello@atadataco.com. We will treat you as more than just a vendor at Atadataco.com. Our vision is to deliver the Modern Data Stack (MDS) to startups around the world. We build using a proven design and delivery process to give you a best-in-class data stack that can deliver more than customer analytics: customer delight and action.

Originally published at atadataco.com/blog on January 15th, 2022.

As data practitioners, we need to ensure that data is accurate in order to build trust in the analytics we deliver. There are many ways to identify these exceptions, but we need a scaleable approach when working with large amounts of data. We need a simple approach where a data practitioner can quickly analyze large datasets and identify these exceptions quickly. This is where dbt comes in.

‍dbt, also known as data build tool, is a data transformation tool that leverages templated SQL to transform and test data. It was developed by dbt Labs, formerly known as Fishtown Analytics. Data build tool is part of the modern data stack and helps practitioners apply software development best practices to data pipelines. Some of these best practices include code modularity, version control, and continuous testing via its built-in data quality framework. In this article, we will focus on how data can be tested with dbt via built-in functionality and with additional extensions. In future articles, we will dive deeper into each of these areas.

Standard dbt tests

In dbt there are two categories of tests: Generic tests (formerly known as schema tests and Bespoke tests (formerly referred to as data tests). The difference between the two are that Generic tests are reusable functions for which you do not have to write the SQL query while Bespoke tests are custom tests you write when a generic test does not cover your use case. Regardless of the type of test, behind the scenes the process is the same, dbt will compile code to SQL and execute that against your database. If any rows are returned by the query, that indicates a failure.

Generic (Schema) tests

dbt Core ships with four basic tests:

unique: is a test to verify that every value in a column (e.g. customer_id) contains unique values. This is useful for finding records that may inadvertently be duplicated in your data.

not_null: is a test to check that the values for a given column are always present. This can help you find cases where data in a column suddenly arrives without being populated.

accepted_values: this test is used to validate whether a set of values within a column is present. For example, in a column called payment_status, there can be values like pending, failed, accepted, rejected, etc. This test is used to verify that each row within the column contains one of the different payment statuses, but no other. This is useful to detect changes in the data like when a value gets changed such as accepted being replaced with approved.

relationships: these tests check referential integrity. This type of test is useful when you have related columns (e.g. product id) in two different tables. One table serves as the “parent” and the other is the “child” table. This is common when one table has a transaction and only lists a customer_id and the other table has the details for that customer. With this test we can verify that every row in the transaction table has a corresponding record in the dimension/details table. For example, if you have orders for customer_ids 1, 2, 3 we can validate that we have information about each of these customers.

Generic tests were enhanced in dbt version 0.20. Now a where clause can be added to a generic test to focus the test on a particular range of rows. This can be useful on large tables by limiting the test to recent data. Another improvement is the ability to define severity based on the number of exceptions. This would allow a test to pass with a warning below a certain threshold and become a true error above that threshold.

Bespoke (Data) tests

Bespoke/custom tests allow you to create tests when the generic ones(or the ones in the packages discussed below) do not meet your needs. These tests are simple SQL queries that express assertions about your data. An example of this type of test is that sales for one product should be within +/- 10% of another product. The SQL simply needs to return the rows that do not meet that condition.

Tests in dbt-utils package

In addition to the genetic tests that can be found within dbt Core, there are a lot more in the dbt ecosystem. These tests are found in dbt packages. Packages are libraries of reusable SQL code created by members of the dbt community. We will briefly go over some of the tests that can be found in these packages.

The dbt-utils package is a library that was created by dbt Labs. It contains special schema tests, SQL generators, and macros. Some of the tests in the dbt_utils package include:

not_accepted_values: this test is the opposite of the accepted_values test and is used to check that specific values are NOT present in a particular range of rows.‍

equal_rowcount: this test checks that two different tables have the same number of rows. This is a useful test that can assure that a transformation step does not accidentally introduce additional rows in the target table.‍

fewer_rows_than: this test is used to verify that a target table contains fewer rows than a source table. For example, if you are aggregating a table, you expect that the target table will have fewer rows than the table you are aggregating. This test can help you validate this condition.

There are several additional tests available in the dbt-utils package which can be found in the documentation.‍

Tests in dbt-expectations‍

Another package that can accelerate your data testing is dbt-expectations. This package is a port of the awesome Python library Great Expectations. For those not familiar, Great Expectations is an open-source Python library that is used for automated testing. dbt-expectations is modeled after this library and was developed by Calogica so dbt practitioners would have access to an additional set of pre-created Generic tests for dbt testing. Tests in dbt-expectations are divided into seven categories:

• Table shape
• Missing values, unique values, and types
• Sets and ranges
• String matching
• Aggregate functions
• Multi-column
• Distributional functions

We will focus on the tests in each category in a future article.

Conclusion

Getting started with dbt testing is simple thanks to the predefined tests found within dbt Core and the additional tests found in dbt-utils and dbt-expectations, These can be used to assure various aspects of data quality using dbt. In all, dbt has 4 test, the dbt-utils package has 12 tests and dbt-expectations package has a total of 58 tests for a total of 74 tests we don’t need to write. When we need to deviate from those tests, we can create our own using Bespoke tests and if they are reusable, we can create reusable macros which we can share within our organization or with the dbt community. These tests can help you increase trust in your analytics by alerting you of error conditions before your users notice. To find out more about dbt and its capabilities, dbt Labs has a course that can introduce you to the basics of dbt for free here.

References

https://discourse.getdbt.com/t/creating-an-error-threshold-for-schema-tests/966

https://docs.getdbt.com/docs/building-a-dbt-project/tests

https://docs.getdbt.com/docs/building-a-dbt-project/tests#bespoke-tests

https://docs.getdbt.com/faqs/custom-test-thresholds

STOP COMPETING IN KAGGLE COMPETITIONS

Don’t get me wrong, Kaggle.com is great. But put yourself in the shoes of the hiring manager. Competitions do not show you can set up an environment or that you can handle any other file format besides a CSV of cleaned data. Do one or two competitions. Then focus on getting projects that are as close to the daily tasks of an analytics engineer as possible. I say this because your primary goal is to have 2–3 projects on your resume that demonstrate that you can tackle a problem from start to finish. Completing a ‘full-stack’ data project will always impress me more than demonstrating one’s ability to apply a model. I will give my opinion on the single best place to get this experience, but first, let’s talk about what is needed for a good resume.‍

SHOW YOU CAN CLEAN DATA FIRST

Hireability (or a good resume) comes down to proof of experience. I don’t need to see how you tried 10 overtrained models to handle this one training dataset. In fact, think about this in production. When would you ever expect to build a model that isn’t retrained or used beyond a single run? This single-run model development is more like a Proof-Of-Concept (POC) project than anything else, which is prone to creating overfitted models that would never survive on real data. Everywhere I look, everyone says data cleaning is the biggest part of data science, so I know I’m not overstating it here. Ask someone farther along in the industry than you: “What are the core Analytics Engineering skills I would need in my first 90 days at startup?”‍

Take the advice of experienced people and layout the skills you would need for a project. For example, if I were trying to land a job at a SAAS B2C company, I would know they are likely to have a set of needed skills.

1. Pulling together onboarding and marketing data and building retention funnels
2. Creating subscription or Life-Time-Value (LTV) analysis in cohort form
3. Communicating requirements for landing page tracking and a/b testing
4. Creating technical requirements from business stakeholders

When I look at a resume and see that someone is just starting out in the field, I do not consider this a red flag against them. I immediately look at their school projects, internships, or alternate learning opportunities to see if they would be a good fit. I will always suggest two projects for the candidate applying for their first analytics engineering job: a data cleaning project and a data storytelling project.

‍BEST OPTION: BUILD A PORTFOLIO WITH CONSULTING PROJECTS

I’ve tried so many types of projects and presentations of project summaries on resumes over the years. My experience has taught me that the best experience can be found in consulting jobs. They are often already divided into that perfect, 1–6 weeks timeframe. Consulting projects seek to solve an actual business problem with an actual business mess (helps a lot when writing about it on a resume). Additionally, they are partially constrained by the fact that the project manager had to spend some time thinking about the definition and steps of the project.

My advice is to try to find 2 to 3 consulting projects/externships while looking and applying for jobs. As you finish up each individual stage of the project, stop to write down a little summary. Think about what skills a hiring manager would be looking for and craft a couple of sentences that show you understood the business problem and know how to technically address it. By the time you have a couple of these summaries, you will have a complete project section for your resume and your new job. Reach out to tell me how it goes at hello@atadataco.com. And if you want to join our freelancer network to get your first job, visit us at atadataco.com.

The name, north star metric, is used by many companies like Amplitude, Mixpanel, Lean Analytics to name a few. The concept is simple: pick a North Star metric that’s most predictive of a company’s long-term success. I want you to think about it as your company’s leading indicator of revenue, creating customer value and measurable/actionable.

Why is a North Star metric important?

Let’s look at how other people have implemented it first for their own customer analytics platforms.

Mixpanel

“Teams use North Star metrics to get everyone in a company focused on one goal. Ninety percent of the world’s data has been created in just the past few years and the profusion of analytical possibilities allow every department, every team, and even every contributor to chase their own metrics. If each team defines goals differently, they can work against each other and duplicate effort. When startup investor Sean Ellis coined the term “North Star metric,” he intended it to reduce administration, simplify meetings, and align teams around the singular goal of growth. The term North Star metric — drawn from the common name for Polaris, the star that lies directly above the Earth’s Northern pole — is mostly rhetorical. Companies with complex business models can have multiple North Stars, and any given North Star metric is composed of sub-metrics anyway. Any company that literally foreswore all metrics in favor of just one, such as recurring revenue, would almost certainly fail. The North Star metric is, simply, an exercise in simplifying the overall company strategy into terms all can remember, understand, and apply. North Star metrics are not to be confused with the acronym OMTM, or one metric that matters, a term popularized by the authors of Lean Analytics. There’s a subtle yet meaningful difference. OMTM is intended to mean “one metric that matters right now” and is a leadership tactic for fixing a short-term problem, whereas a North Star metric is intended as a long-term guide. Though, just like the real North Star, it too is impermanent. When the Egyptians built the pyramids, the Earth had a different North Star — Thuban — but it’s crept out of alignment, just as Polaris will in time. Companies should feel equally free to reevaluate their North Star metrics to make sure they still point the right direction, and amend them when they prove flawed.” (mixpanel.com/topics/north-star-metric/)

Amplitude

“A north star metric is the key measure of success for the product team in a company. It defines the relationship between the customer problems that the product team is trying to solve and the revenue that the business aims to generate by doing so.

This serves three critical purposes in any company:

It gives your organization clarity and alignment on what the product team needs to be optimizing for and what can be traded off.

It communicates the product organizations’ impact and progress to the rest of the company — resulting in more support and acceleration of strategic product initiatives.

Most importantly, it holds the product accountable to an outcome.

In most companies, product teams are measured by how much they ship, not on the impact they have on the business. Without an impact driven culture in product, you can’t influence the destiny of your business. Without a north star, you can’t have a product-led company.” (https://amplitude.com/blog/2018/03/21/product-north-star-metric)

Examples of North Star metrics (suggestions from Mixpanel)

E-commerce

• Number of weekly customers completing their first order
• Value of daily purchases
• Lifetime value

Consumer tech

• Number of daily active users (DAU)
• Number of messages sent per day (habit metric)
• Retention

B2B SaaS

• Number of trial accounts with over 3 users in their first week
• Percentage retention

Media

• Signups and retention
• Number of daily active visitors
• Total read time
• Total watch time

Fintech

• Total assets under management
• Number of daily active users

Deciding What’s Right for You

To find their North Star metric, companies must decide what is truly essential to the business. Companies are complex and succeed and fail for lots of reasons. But what are the pillars to the business that are, as an architect might say, load-bearing? That if they alone failed, would ruin the company? For many teams, that’s making customers happy, generating profit, and measuring progress toward those goals. A metric that simply makes money without satisfying customers will fail in the long run, as will a company that satisfies customers without being profitable. And a metric that doesn’t measure progress in a way that allows teams to act on its insights and change their behaviors isn’t useful. A North Star metric must reflect all three factors, tailored to each business. To find your North Star metric:

1. Ask, what is essential to the business’ functioning? Prioritize a list.
2. Ask, what KPIs and metrics measure the top few, key factors?
3. Ask, what metric encapsulates all of the above?
4. Build a metric hierarchy, with the North Star metric on top of the pyramid

Like a seed, North Star metrics need fertile ground to grow. Companies that select a North Star need the right culture and infrastructure. Without cross-silo relationships and a willingness to prioritize the company good above the team good, some employees may reject the North Star metric, especially if they must change their behavior significantly, or if, like many sales teams, their compensation structure presents a conflict of interest. Companies also need the right analytics tools to measure progress toward their North Star metric and sub-metrics. Without user-friendly analytics that teams can access at a whim, companies can’t tell whether they’re succeeding, and can’t course correct. Most teams find user analytics vital to measuring their North Star metric. User analytics provide user-level insights that most analytics platforms — especially free ones — don’t capture. User analytics:

• Track individual users across platforms
• Generate reports and dashboards
• Use machine learning to detect anomalies in the data
• Offer a user-friendly interface so the whole team can access insights

North Star metrics can be an effective strategy for aligning all teams around a singular goal, provided they’re not taken too literally, are supported by a flexible culture, and measured with analytics tools that helps teams tell whether it’s still guiding the way.

For me working in the fintech space, my north star metric was a habit metric. Lot of companies have them. They are usually retention metrics. For Pinterest, they are trying to drive weekly repins to drive usage. This retention metric leads to the habit metric of 4 days pinning out of the first 28 days. I picked my fintech north star to be a similar time frame since users must wait for their card to arrive. For me 28 days worked best and allowed me to define my key performance metric as 3 transactions in the first 28 days with the leading indicator metric as number of days to first funding. This way, if I can drive a user to fund their account quicker, I can unlock their ability to purchase and show the value of my fintech platform.

Example Diagrams

Contact Us

If you would like help defining your north star metric for your startup, schedule a time to review your retention metrics and pick the best one to define your success and growth with our Analytics Consulting Offering!

North Star image courtesy of: https://unsplash.com/@_louisreed

“Why did the Facebook/Google/etc install conversion tank on Friday?” Have you ever been asked this question as a data team? I have and everyone I know in San Francisco data science has as well. This question asks for historical context into an abstract user behavior problem. You need to make it your team’s goal that this question is already answered automatically and presented to the business stakeholder before the end of day Friday. I’m not suggesting bringing in machine learning to detect any possible question. No, I’m suggesting your data team should build self-service and data products into the core functionality/actions of the company.

There is one thing that separates a successful data org from one constantly building dashboards and reactively fixing reporting on old data. I am not saying I do not build dashboards and occasionally need to put out fires or deliver low impact, last-minute critical business concerns that tomorrow won’t be important or remembered. I’m talking about building the data best practices, design and core infrastructure. I’m saying data teams must be building data products. My point is you need to get your org to the level on the data science maturity curve that you are building products that can be consumed by the rest of the company instead of quick-fix dashboards and analytics. To put it one more way before moving on to what this looks like, focus on building predictive tools and data insights as opposed to building infra that only reports explanations of what happened last week.

Courtesy of Gartner.

What are Data Products?

Data products refers more to how your team builds insights, models or infrastructure projects than who sees the result. Data products usually aid other internal teams with well-designed and executed product verses only ad hoc analytics.

• Documenting them to allow for future repeatability and versioning in some common metadata store like Github.
• Building a unified data model that has defined datasets, single source of data and summary tables.
• Setting up flexible, self-serve BI tools and follow up data training so most questions are answered with existing dashboards.

Founding Analytics can jump start your first analytics platform build and set up a powerful analytics database to answer the most pressing business questions!

Start here or connect by email: https://www.upwork.com/o/profiles/users/~01aa6846782e36bd02/

Inspired by: https://dataform.co/blog/great-data-teams-build-products

#datascienceconsulting #analytics #consulting #sql #snowflakedb