Zoho CRMのCOQLで重複データを探せない？Zoho Analyticsのクエリテーブルで解決した方法

Zoho CRMを使用していて、
基幹システムのテーブル上は一意で運用しているカラムがZOHO CRMでは一意指定されていないので
ZOHO上に複数登録されてしまうという不具合が発生したのでZOHO CRM内で「同じ売上IDが複数登録されていないか確認したい」という状態になりました。

最初はZoho CRMのCOQLを使って取得しようとしたのですが、
COQLではHAVINGが使用できないという仕様があり、どうにかこねくり回していたんですが上手くいかず
最終的にZoho Analyticsのクエリテーブルを使って重複データを抽出する方法で上手く行ったので紹介します。

今回やりたいこと

ZOHO CRM上で同じ売上IDが2件以上登録されているものだけを取得する

売上テーブル

理想

通常のSQLならHAVINGで簡単に取得できる

PostgreSQLなどのSQLであれば、GROUP BYとHAVINGを使うことで簡単に取得できます。

SELECT
    売上id
   ,COUNT(*) AS record_count
FROM 売上テーブル
GROUP BY 売上id
HAVING COUNT(*) > 1;

最初はCOQLで取得しようとした

最初は、通常のSQLと同じ考え方をそのままCOQLで実現しようとしました。
COQLでもGROUP BYやCOUNTを使用することはできますが、HAVINGが使えないため
売上IDが2件以上あるという条件式を作るのが難しいという結論になりました。

Zoho Analyticsのクエリテーブルを使うことにした

今回解決に使用したのが、Zoho Analyticsのクエリテーブルです。
Zoho Analyticsのクエリテーブルでは、Analytics内のデータに対してSQLのSELECT文を使ってデータビューを作成できます。
複数の言語に対応しているので、普段SQLを書いている場合、COQLよりもこちらの方が違和感なく扱えます。

Zoho AnalyticsにCRMデータを連携する

Zoho CRMのデータをZoho Analytics側に連携した状態でクエリテーブルを作成します。
Zoho Analyticsでクエリテーブルを新規作成し、以下のSQLを入力します。

SELECT
    "売上id"
   ,COUNT(*) AS "record_count"
FROM "売上テーブル"
GROUP BY "売上id"
HAVING COUNT(*) > 1;

結果

今回やりたかったことが、通常のSQLとほぼ同じ書き方で実現できました。

作成したクエリテーブルは通常のテーブルのように使える

Zoho Analyticsのクエリテーブルは、SQLを実行するだけではなく、その結果を新しいテーブルのように扱えます。

今回作成したクエリテーブルを「重複商談一覧」という名前にしておけば他のクエリテーブルから「重複商談一覧」を参照することも可能です。
単発でSQLを実行して結果を見るだけでなく、継続的な重複チェックにも使えるのが便利です。

SELECT
  "売上id"
 ,"record_count"
FROM 重複商談一覧
ORDER BY "売上id";

COQLとZoho Analyticsのクエリテーブルの違い

COQLはCRM API経由で必要なデータを取得するときには非常に便利です。
Zoho Analyticsは集計に特化しているので欲しいレコードの出力等に向いています。

COQLでは使えないコマンドがあったりするので「複雑な集計」をしたい場合は、Zoho Analyticsのクエリテーブルの方が向いているケースがあります。

今回の結論

今回欲しかったのは集計結果だけだったのでCOQLで無理に複雑な処理をするよりも、
確実に使えるZoho Analyticsの方が早く達成できました。
最初はHAVINGが使えないことに気付いて四苦八苦しながらSQLを組んでいたんですが、
クエリテーブルでやった方が早いと気付いてからはすぐでした。

注意点としてはZoho Analyticsはリアルタイム同期ではなく定期同期なので最新のデータは反映されないということですね。

普段SQLを書いているならZoho Analyticsのクエリテーブルはかなり馴染みやすい機能だと思います。