When a coaching business needs a data warehouse

On a client build, the CRM calendar showed a call that had been moved. It held the new time. It did not hold the old one. The appointment kept the same id, the start time was overwritten, a "rescheduled at" stamp was added, and nothing else was saved.

The tool was working as designed. The effect was that, until we added a copy, we could say how many calls had moved and could not say when each was first booked, or how long a prospect had waited for the new slot. The old slot had been erased from the only place it ever lived.

To keep it, we had to copy the appointments table on a schedule and save every change as a dated row. That copy is one small part of what people mean by a data warehouse. It is also the part you cannot go back and build later, because a copy only holds what it was collecting on the days it was running.

This post is for a coach, course creator or agency owner who suspects the answer is yes. It starts with the four signs that say you need a warehouse and what it fixes for each. The shorter part at the end covers the case where it is still too early.

Four signs you need a warehouse

1. A tool overwrites something you will need later. Our reschedule example is one. Tools differ here, and that is the point. Calendly's developer documentation says that when an invitee reschedules, the webhook for the old event reports it as canceled with the rescheduled key set to true, and a second webhook reports the new event (Calendly, webhook payloads for rescheduled events). The same move leaves a record in one tool and erases it in another.

What a warehouse fixes: it copies each tool on a schedule and keeps every copy dated. The old slot stays, so you can see how long a prospect waited and how many calls moved more than once.

2. An answer needs two tools that share no key. "Which setter's booked calls turned into cash" needs the booking tool, the CRM and the payment processor to agree on who a person is. They record different emails, different clocks and different ids. The trail post lists where each one drops the thread. A hand-made match in a sheet works once and breaks the next month.

What a warehouse fixes: the match is written once, as code, and runs every night. A person gets one key that every tool's records hang from, and the cases that cannot be matched are listed instead of dropped.

3. Two reports disagree and neither can be replayed. If you cannot say which rows went into a number, you cannot say which report is right. The show rate post is a case where one rate read 100% twice.

What a warehouse fixes: it keeps the rows and the rule side by side, so a number can be opened and recomputed. When two reports differ, you can find the first row where they part.

4. You want an old number under a new definition. Say you decide that a moved call should leave the show rate, and you want the last six months restated. A sheet that holds only the ratios cannot do it, because the ratio threw away the rows. You need the raw records.

What a warehouse fixes: the definition is a rule applied to the raw rows, so changing the rule restates every past month in one run.

One yes means start saving a dated copy of the tool that forgets, even by hand. Two or more means a warehouse is worth building.

What a warehouse is, in plain words

Google's own definition, which is a vendor describing its own product, calls a data warehouse a system for analysis and reporting of data from multiple sources, designed to give a long-range view of data over time (Google Cloud, what is a data warehouse).

In a coaching business that comes down to three things:

• Copies. Each tool you use sends its records to one place on a schedule. The tools stay where they are.
• Dates. Each copy is kept, so you can see what a record said last Tuesday and not only what it says now.
• Definitions. The rule for show rate, close rate or cash collected is written once, as code, and every report reads that one rule.

The third one matters as much as the first two. It is the reason two reports stop disagreeing, and the dashboard post covers what to agree on.

A worked example

This is a made-up example. It names no client.

Two coaches ask the same four questions.

Coach A has four yes answers and should build. Three of the four would be answered by the same piece of work: copy every tool into one place, keep each copy dated, and write the definitions once. Every month of waiting is a month of moved calls whose first slot is lost.

Coach B has none, and the next section is for Coach B.

Size does not decide it. What decides it is how many tools hold part of the answer and whether any of them forget.

When it is too early

A weekly paste into a sheet is a real system. If you sell from one tool, run one question through it each Monday and the answer is the same every time you ask, you have what you need. A CRM's own reports are the same. They are good at questions inside that CRM: how many deals sit in each stage, who owns them, how old they are. If your calls are booked, held and sold in one place, ask the CRM first.

Neither one fails because of size. A coaching business rarely has enough rows to trouble a spreadsheet. They fail on the four signs above, and until one of them shows up, a warehouse is a system to maintain for questions already answered. Someone has to watch the pipelines, fix a connector when a tool changes its fields, and explain the output to the team.

The one addition for Coach B is a dated export of the bookings each week, saved in a folder, so nothing that changes later is lost. That costs a few minutes and nothing else.

If you get one, own it

A warehouse you rent can be switched off. A warehouse in your own accounts stays when the person who built it leaves. Five things settle which one you have.

1. Whose account it is. The cloud project and its billing should sit in your business's name, under a login that more than one person can reach. Our client handover method opens with this, because if the billing account and project sit on one person's personal Gmail, the whole data stack is hostage to that login.
2. What the project is called. Google's documentation says a project ID is permanent after creation (Google Cloud, creating and managing projects). Leave a brand or product name out of it, so a rebrand later does not leave you with the wrong name for good.
3. Who can read it. Each tool needs the least access that lets it send data, and each person should have a named role. A shared password is not a role.
4. How you get your rows out. Ask where the rows sit and whether you can copy every table out, today, without asking anyone. If the answer depends on a vendor's help desk, you are renting.
5. Where the definitions live. The code that defines each number should be readable and in your hands. In our builds the warehouse sits in the client's own cloud account, and the handover includes the SQL behind every page.

What to do this week

1. Write down every tool that holds part of a number you review. Next to each, write whether it keeps history or overwrites it.
2. For each tool that overwrites, save a dated export of the table every week. Put the date in the file name. It will miss a change that starts and ends between two exports, so run it more often as the business grows.
3. Answer the four signs for your business. Count the yes answers.
4. Name the field that joins a booking to a payment. If you cannot, the trail post is the place to start.
5. If you already pay for any cloud account, check whose name it is in and who else can log in.

If you want a quick read on which gaps are yours, the Revenue Blind-Spot Score takes about a minute.

The calendar on that client build still overwrites a moved call. What changed is that a dated copy of it is now taken on a schedule, so the first slot is no longer lost. Nobody looks at that copy, and it is the one part that cannot be rebuilt afterwards.