Тестовое задание
на Revenue Operation Analyst
Recraft

Company Background

You are evaluating a SaaS company, and the product has three subscription tiers: Free, Pro, and Teams. Initially, growth was product-led (users self-serve upgrades from Free to Pro). Now, the company has built a B2B sales process to convert high-value accounts into Business customers. Your task is to analyse the provided data and model revenue outcomes, demonstrating skills in funnel analysis, SQL, and revenue modelling.

Key Context:

• Freemium Funnel: Large volume of Free users, a percentage of whom upgrade to paid plans. Pro is a paid individual plan (self-serve). Teams is a higher-tier plan for organisations (usually via sales).
• New Sales Motion: With a sales team in place, some Free users (especially multiple users from the same company) are being approached or inbound to upgrade to Teams. The company is transitioning to a mix of product-led and sales-led conversions.
• Goal: Understand current conversion metrics, identify revenue opportunities, and forecast future revenue considering both self-serve upgrades and the sales pipeline.

Data Provided

You have access to simulated company data in a spreadsheet with multiple sheets (or database tables). The key datasets include:

Users & Subscriptions Each user’s signup and subscription status.

• Columns: user_id, company_id (to group users by company), sign_up_date, initial_plan (e.g. Free or if they started on a trial), current_plan (Free, Pro, or Teams), upgrade_date (if they became Pro/Teams), and revenue_mrr (the current Monthly Recurring Revenue from that user, $0 if Free).
• Notes: Most users start as Free. An upgrade record means they converted to Pro or were added to a Teams plan. Assume Pro plan = $30 MRR per user, Teams plan = $200 MRR per company (for simplicity). All revenue is monthly recurring. Dates range from the past 12–18 months for trend analysis./

Sales Pipeline: A list of sales opportunities for Teams (business) deals.

• Columns: deal_id, company_id (if tied to an existing free-user company), created_date, close_date (if closed), stage (e.g. Prospecting, Qualified, Proposal, Closed Won, Closed Lost), deal_value (ARR value of the deal, in $), and source (e.g. “Product Qualified Lead (PQL)” for those originating from the user base, or “Outbound” for cold leads).
• Notes: Some deals are already closed (Won/Lost with a close_date); others are open in various stages. This will allow analysis of stage conversion rates and forecasting. There are about 50–100 deals in the dataset, including last quarter’s closed deals and current pipeline.

(All data is simplified and anonymized. You can assume the data is clean and ready for analysis in a spreadsheet or can be imported into a SQL database if desired.)

Using the provided data, perform a comprehensive analysis and answer the following. Each task is meant to simulate what a Revenue Operations analyst might do. You may use Excel/Google Sheets, SQL queries, and other analysis tools, but the final results and explanations should be clear. Aim to spend no more than several hours on this.

Deliverables

• Analysis Workbook: A spreadsheet (Excel or Google Sheets) with your calculations, pivot tables, or any analysis done. If you wrote SQL queries, you can include the query text in a separate sheet or comments. Ensure it's clear where the answers for each task are derived.
• Summary Document: A brief write-up (can be in a Word/Google doc or within the email) summarising your findings for each task and your recommendations (approximately 1-2 pages or a few paragraphs with bullet points). Focus on interpreting the results in plain language for a leadership audience.
• Note: If you prefer, you can combine the summary and analysis by writing your answers in the spreadsheet (e.g., on a summary tab). The key is that your thought process and conclusions are well-communicated.
• No Coding or Presentation Required: You do not need to build any software or elaborate dashboard. The company is interested in your analytical approach and insights. Clean, well-organised spreadsheets and clear explanations are sufficient. Feel free to use charts or graphs if they help illustrate a point, but this is optional.
• Time Expectation: ~3-6 hours. We understand this is a time-boxed assignment, so we are looking for a structured approach, not perfection. It’s okay to state assumptions or simplifications due to limited time. Focus on the high-impact analysis and demonstrating how you think about revenue operations challenges.

Subscription Funnel Analysis:

Calculate key funnel metrics for the self-serve user funnel. Determine the number of Free users who convert to paid plans and the conversion rates:

• Free → Pro conversion rate (what percentage of Free signups eventually upgrade to Pro).
• Free → Teams conversion rate (what percentage of Free signups become part of a Teams subscription via sales).
• Tip: Consider analysing by cohort or timeframe (e.g., users who signed up in Q1: what % upgraded within 3 months). If the dataset includes sign_up_date and upgrade_date, you can derive these metrics. For simplicity, you can also calculate an overall conversion rate to date (total upgrades / total signups).

Monthly Trends: Compute the number of new Free signups per month and how many of those converted to Pro or Teams. This will show if conversion rates are improving or declining over time. (Hint: Group the user data by sign_up month. You may use a pivot table or SQL GROUP BY on MONTH(sign_up_date).)

Churn (if data available): If the data indicates any cancellations (e.g., a user’s current_plan reverting to Free or a cancellation_date), identify the churn rate of Pro or Teams users. (If no churn data is provided, you can skip this step.)

Revenue Calculation & Current ARR:

Using the subscription data, calculate the current Monthly Recurring Revenue (MRR) and Annual Run-Rate (ARR) for the company:

• MRR from Pro users (count of Pro users * $30).
• MRR from Teams subscriptions (count of Teams accounts * $200).
• Total MRR and the equivalent ARR (MRR * 12).

Revenue by Segment: Determine what proportion of revenue comes from self-serve Pro vs. sales-led Teams. This will highlight the importance of the new sales-led motion relative to the base business. (For example, X% of ARR is from Teams vs Y% from Pro.)

SQL Exercise: Provide an example SQL query that you could use to calculate one of the above metrics from the raw data. You don’t need to run the query – just demonstrate how you would approach it.

Revenue Modelling and Forecast:

Using your findings, build a simple revenue projection for the next 2 quarters, incorporating both self-serve growth and sales pipeline:

• Self-Serve Projection: Based on recent trends, assume the number of new Free signups per month and the conversion rate to Pro will continue (or grow modestly). Project how many new Pro subscriptions will be added each month and the associated MRR.
• Sales-Led Projection: Consider the current open pipeline and any expected new pipeline. Using the expected pipeline conversion (from task 3), project how many Teams deals (and how much ARR) could close in the next 2-3 months.
• Combine into Total Forecast: Summarise the total projected MRR/ARR for each of the next 3-6 months, combining self-serve Pro revenue growth and new Teams deals. Clearly state any assumptions you make (e.g., growth rates, seasonality, conversion improvements with the new sales focus, etc.). A simple spreadsheet model is sufficient – focus on the logic and reasoning.
• Scenario consideration (optional): If comfortable, you might present a best-case vs. base-case scenario (for example, if conversion rates improve due to a new marketing campaign or if the sales team ramps up faster than expected). This is not required but could show strategic thinking.

Insights and Recommendations:

Interpret the results of your analysis and provide actionable insights:

• Summarise the health of the funnel. Discuss if these rates seem low or high for the business model, and identify any bottlenecks.
• Highlight where the biggest revenue opportunities are.
• Recommendations: Propose 1-2 strategies or areas of focus for RevOps to improve revenue metrics. This could be operational (e.g., “implement a lead scoring system to identify promising Free users for sales outreach”) or analytical (e.g., “track cohort conversion over time to see if recent product changes improved upgrades”). Show that you can connect the data to business decisions.

Тестовое задание на Revenue Operation Analyst в Recraft. Ознакомьтесь с примерами реальных тестовых заданий, которые предлагаются кандидатам. Узнайте, какие задачи могут встретиться и как они связаны с будущей работой. Это поможет лучше подготовиться к собеседованию в Recraft и понять ожидания работодателя.