This is the first post of a six-part series of posts explaining how we make analyzing Quickbooks data easier by creating cached data sets from complex queries, resulting in simplified tables that anyone at our company is able to easily understand.

• Part 1: Introduction + Shadow Schema Strategy Explanation
• Part 2: The Quickbooks Shadow Schema
• Part 3: Budgets Table
• Part 4: Cash Movements Table
• Part 5: Expenses Table
• Part 6: Revenue Table (You are here)

This is the final post in this series of queries for simplifying Quickbooks data. Note that Credit Memo’s and Refund Receipts count against Revenue, so they are negated by multiply the Line Amount field by -1 in the queries.

If you made it through this series, thank you for reading! I am aware that this is a highly specific guide, but it took me almost two weeks to create these queries, so I hope I can save someone else that time.

(more…)

This is the first post of a six-part series of posts explaining how we make analyzing Quickbooks data easier by creating cached data sets from complex queries, resulting in simplified tables that anyone at our company is able to easily understand.

• Part 1: Introduction + Shadow Schema Strategy Explanation
• Part 2: The Quickbooks Shadow Schema
• Part 3: Budgets Table
• Part 4: Cash Movements Table
• Part 5: Expenses Table (You are here)
• Part 6: Revenue Table

This table summarizes all outgoing financial transactions that would fall under the “Expenses” category in a Quickbooks report.

(more…)

This is the first post of a six-part series of posts explaining how we make analyzing Quickbooks data easier by creating cached data sets from complex queries, resulting in simplified tables that anyone at our company is able to easily understand.

• Part 1: Introduction + Shadow Schema Strategy Explanation
• Part 2: The Quickbooks Shadow Schema
• Part 3: Budgets Table
• Part 4: Cash Movements Table (You are here)
• Part 5: Expenses Table
• Part 6: Revenue Table

This is the Cash Movements table, and the first of the complex tables in this series. It joins many tables together into a single table using JOIN’s and UNION’s.

Notice the differentiation between “In” and “Out” transactions – one will multiply the amount by -1 to show that it is negative cashflow.

(more…)

This is the third post of a six-part series of posts explaining how we make analyzing Quickbooks data easier by creating cached data sets from complex queries, resulting in simplified tables that anyone at our company is able to easily understand.

• Part 1: Introduction + Shadow Schema Strategy Explanation
• Part 2: The Quickbooks Shadow Schema
• Part 3: Budgets Table (You are here)
• Part 4: Cash Movements Table
• Part 5: Expenses Table
• Part 6: Revenue Table

This is the Budgets table, which is a rather simple query that just creates a cleaner table summarizing the budgets for each account in Quickbooks.

(more…)

This is the second post of a six-part series of posts explaining how we make analyzing Quickbooks data easier by creating cached data sets from complex queries, resulting in simplified tables that anyone at our company is able to easily understand.

• Part 1: Introduction + Shadow Schema Strategy Explanation
• Part 2: The Quickbooks Shadow Schema (You are here)
• Part 3: Budgets Table
• Part 4: Cash Movements Table
• Part 5: Expenses Table
• Part 6: Revenue Table

This is the schema creation script for the shadow schema. I’ll outline how I load the data in a separate post and link it here when it is available.

create schema quickbooks_shadow;

create table quickbooks_shadow.accounts (
	name varchar(1000),
	subaccount boolean,
	parentref_value varchar(200),
	fullyqualifiedname varchar(200),
	active boolean,
	classification varchar(200),
	accounttype varchar(200),
	accountsubtype varchar(200),
	currentbalance float,
	currentbalancewithsubaccounts float,
	currencyref_value varchar(200),
	currencyref_name varchar(200),
	domain varchar(200),
	sparse boolean,
	id int,
	metadata_createtime varchar(200),
	metadata_lastupdatedtime varchar(200)
);

create table quickbooks_shadow.creditmemos (
	id varchar(200),
	docnumber varchar(200),
	txndate varchar(200)
);

create table quickbooks_shadow.invoices (
	id varchar(200),
	docnumber varchar(200),
	txndate varchar(200)
);

create table quickbooks_shadow.journalentries (
	id varchar(200),
	docnumber varchar(200),
	txndate varchar(200)
);

create table quickbooks_shadow.refundreceipts (
	id varchar(200),
	docnumber varchar(200),
	txndate varchar(200)
);

create table quickbooks_shadow.salesreceipts (
	id varchar(200),
	docnumber varchar(200),
	txndate varchar(200)
);

create table quickbooks_shadow.deposits (
	id varchar(200),
	docnumber varchar(200),
	txndate varchar(200)
);

create table quickbooks_shadow.creditmemolines (
	parent_id varchar(200),
	id varchar(200),
	linenum int,
	description varchar(1000),
	amount float,
	salesitemlinedetail_itemref_value varchar(200),
	salesitemlinedetail_itemref_name varchar(200),
	salesitemlinedetail_unitprice float,
	salesitemlinedetail_qty float
);

create table quickbooks_shadow.refundreceiptlines (
	parent_id varchar(200),
	id varchar(200),
	linenum int,
	description varchar(1000),
	amount float,
	salesitemlinedetail_itemref_value varchar(200),
	salesitemlinedetail_itemref_name varchar(200),
	salesitemlinedetail_unitprice float,
	salesitemlinedetail_qty float
);

create table quickbooks_shadow.invoicelines (
	parent_id varchar(200),
	id varchar(200),
	linenum int,
	description varchar(1000),
	amount float,
	salesitemlinedetail_itemref_value varchar(200),
	salesitemlinedetail_itemref_name varchar(200),
	salesitemlinedetail_unitprice float,
	salesitemlinedetail_qty float
);

create table quickbooks_shadow.salesreceiptlines (
	parent_id varchar(200),
	id varchar(200),
	linenum int,
	description varchar(1000),
	amount float,
	salesitemlinedetail_itemref_value varchar(200),
	salesitemlinedetail_itemref_name varchar(200),
	salesitemlinedetail_unitprice float,
	salesitemlinedetail_qty float
);

create table quickbooks_shadow.journalentrylines (
	parent_id varchar(200),
	id varchar(200),
	linenum int,
	description varchar(1000),
	amount float,
	journalentrylinedetail_accountref_value varchar(200),
	journalentrylinedetail_accountref_name varchar(200),
	journalentrylinedetail_entity_entityref_name varchar(200),
	journalentrylinedetail_entity_entityref_value varchar(200),
	journalentrylinedetail_entity_type varchar(200),
	journalentrylinedetail_postingtype varchar(200)
);

create table quickbooks_shadow.depositlines (
	parent_id varchar(200),
	id varchar(200),
	linenum int,
	description varchar(1000),
	amount float,
	depositlinedetail_accountref_value varchar(200),
	depositlinedetail_accountref_name varchar(200),
	depositlinedetail_entity_entityref_name varchar(200),
	depositlinedetail_entity_entityref_value varchar(200),
	depositlinedetail_entity_type varchar(200),
	depositlinedetail_postingtype varchar(200)
);


create table quickbooks_shadow.payments (
	id varchar(200),
	txndate varchar(200)
);

create table quickbooks_shadow.purchases (
	id varchar(200),
	docnumber varchar(200),
	txndate varchar(200)
);

create table quickbooks_shadow.bills (
	id varchar(200),
	docnumber varchar(200),
	txndate varchar(200)
);

create table quickbooks_shadow.billpayments (
	id varchar(200),
	docnumber varchar(200),
	txndate varchar(200)
);

create table quickbooks_shadow.vendorcredits (
	id varchar(200),
	docnumber varchar(200),
	txndate varchar(200)
);

create table quickbooks_shadow.paymentlines (
	parent_id varchar(200),
	id varchar(200),
	amount float
);

create table quickbooks_shadow.purchaselines (
	parent_id varchar(200),
	id varchar(200),
	amount float
);

create table quickbooks_shadow.billlines (
	parent_id varchar(200),
	id varchar(200),
	amount float
);

create table quickbooks_shadow.billpaymentlines (
	parent_id varchar(200),
	id varchar(200),
	amount float
);

create table quickbooks_shadow.vendorcreditlines (
	parent_id varchar(200),
	id varchar(200),
	amount float
);

grant all on schema quickbooks_shadow to <USERNAME>;
grant all on all tables in schema quickbooks_shadow to <USERNAME>;

Up Next:

Simplifying Stitch ETL’s Quickbooks Data in Redshift: Budgets Table

Previous Post:

Simplifying Stitch ETL’s Quickbooks Data in Redshift: Introduction

This is the first post of a six-part series of posts explaining how we make analyzing Quickbooks data easier by creating cached data sets from complex queries, resulting in simplified tables that anyone at our company is able to easily understand.

• Part 1: Introduction + Shadow Schema Strategy Explanation
• Part 2: The Quickbooks Shadow Schema
• Part 3: Budgets Table
• Part 4: Cash Movements Table
• Part 5: Expenses Table
• Part 6: Revenue Table

Stitch exports Quickbooks Online data in the way that Quickbooks structures its API – each type of record is its own separate object, and each object has child objects with transaction details. Journal Entries, Invoices, Payments, Receipts, Vendor Credits, Customers, Credit Memos, Bills, Accounts, Deposits, Employees, Items, and Refunds are all broken down into multiple tables each when replicated into your data warehouse using the Stitch connector. Unfortunately, that leaves it up to an analyst to reconstruct the datasets needed to analyze things like revenue, expenses, cash flows, etc.

I’ve spent the countless hours necessary to reconstruct the data into cached data stores that we use to actually analyze and visualize our Quickbooks data. This post demonstrates each of those queries.

(more…)