dataform_calldrip

Calldrip

Notion: https://www.notion.so/graindata/Calldrip-8c5eb1670b624baab51175fb56bfc38e?pvs=4

Calldrip helps Van Mossel salespeople communicate with the leads and make appointments at a branch to potentially convert the lead to a conversion. As soon as a customer expresses interest via submitting a form on the website for example, a lead is generated and sent to the LEF system. Later, the phone number of the customer is sent to Calldrip, and within 20 minutes Calldrip connects the customer with the salesperson. Each call has a status, whether it is answered, busy, missed, and many more. Depending on the amount of calls answered and number of appointments made Van Mossel can better understand how a salesperson is performing over a period of time.

Dataform pipeline

Green color: This color is used to represent the data sources declared.
Blue color: This color represents tables that are ready to be used in dashboards and/or share with the client.

Pipeline table details

1. calldrip_raw_v2: This is the table name where data imported from the Calldrip API is stored in the Bigquery. We used a cloud function (CF) to import data from the Calldrip API. This CF is triggered at two different schedules. One is that each day we import data from the last 7 days. The other schedule is triggered every 15 minutes to import data from the last 2 days. As the data from few days before might be updated and due to this reason everyday we import the last 7 days data.

2. vestiging_lookup: This is a lookup table that helps to match the raw Calldrip data based on vestiging to get the cluster and clean the vestiging (branch) names. Cluster is a group of car brands linked to a branch. Currently, the "vestiging_lookup" table is created by a custom SQL query in the Dataform using "vestiging_cluster_lookup" lookup table.

3. calldrip_analytics: This is the first table in the flow the takes the raw imported data, transforms it and enriches the vestiging (branch) details with "vestiging_lookup".

4. calldrip_shared_service: This view is created to change the field names such as branch to vestiging, etc and filter out specific clusters that match to the old flow in order to not break the dashboard.